ネット上の表をＥｘｃｅｌに取り込んで…

質問

こんばんは、宜しくお願いします。

小さな事務所で、スキル無しで一人で事務をしています。
年数的には初心者ではありませんが、
本を片手に我流でWord、Excelを使っているど素人です。

ネットバンキングを利用しています。
口座の入出金表をExcelに取り込んで
作業の手間を省いて入力ミスを防ごうと思っているのですが、
コピペのままだと計算式が使えず結局数値を入力しなおしています。
通常の通帳のように、文字列と数字の列が混ざっているのが原因かと思い、
文字列を空白にしても出来ません。
数値の前に空白スペースが入れてあります。

具体的な作業の内容としては、
口座取引のなかに、個人用、会社用、預かり金が混在しているので
それぞれを別個の行に分け、さらに項目に分け、項目ごとの合算をだしたいのですが、
そのままの状態では、計算ができません。
入力し直さずに、計算が出来るようにする方法はないでしょうか。

もし既出であれば、
検索しやすい文言を教えて頂ければと思います。

回答No.7

noname#70958 · ベストアンサー

#6です。多少はお役に立てたようで嬉しく思います。

少し補足を。

「文字列に、スペースや\以外の【何か】が含まれているのではないか」
というのが私のアプローチの主眼ですが、
２の数式は、
・【何か】の文字コードが63である。
・【何か】はデータの前方３文字以内にある。
という想定で書いたものです。

エラーになったということは、
【何か】のコードが63ではなかったのかもしれませんし、
【何か】の位置が4文字目以降だったのかもしれません。
また、数値データと文字列データで【何か】の付き方が違った可能性もあります。

いずれにしろ、２の数式はあくまで
実データが手元になく正確な状態がわからない状況で、
成功する可能性が高い数式として捻り出したもので、
もし実データが手元にあれば、あんな変な(笑)数式は書きませんし使いません。

「目の前にないデータを処理する数式を書く」というのは、
Q&Aサイトの回答者でもなければ必要とされないスキルです。
しかも今回はハズレてますし(苦笑

そういう意味で、２の数式にはあまりこだわらない方が吉かと思います。

以上ご参考まで…というか念のため。

補足

こんばんは、再びのアドバイス有難うございます。
それから、お詫びを。

昨日はMID関数に舞い上がってしまい、Kater_Kurz様の補足要求を完璧に無視していました、すみません。

＞状況チェック用１：　=CODE(TRIM(A1))
は、金額の部分はすべて160で、項目の部分は182とか48とか212とかになっています。
＞状況チェック用２：　=FIND(LEFT(TRIM(A1),1),A1)
は、すべて１です。
今日も出勤したので、63を160にしたりやってみたかったのですが業務が混んでできず残念でした。

＞「目の前にないデータを処理する数式を書く」というのは、
Q&Aサイトの回答者でもなければ必要とされないスキルです。

ほんとに特殊技能だと思います。
しかも、文字情報だけで説明するのはとても大変なことですね。しかも今回相手がかなりのど素人ですし…
Kater_Kurz様をはじめ皆さんとても詳しくて、驚きと尊敬と羨望でいっぱいです。
ありがとうございました、とても勉強になりました。

＞２の数式
は、私にとってプレゼントのようなものです。
マニュアル本を読んでもなかなか頭に入りませんが、今自分に必要なことなら実践しながら勉強することができます。
もう頂いたものはお返ししません。私のものですから、こだわらせていただきます(笑)。
Kater_Kurz様にはハズレでも私には大当たりですから。

再度のアドバイス、重ねてありがとうございました。

回答No.9

marbin

#4,#8です。

補足です。
WEBクエリでは取込めないHPもあります。

補足

ご丁寧に補足有難うございます。
今回は無理だったかも知れませんが、
今後の知識としてとても貴重なアドバイスでした。
自分に必要なことだけを、自分の想像の範囲だけで調べていたので
知識が本当に浅く狭いです。

marbin様のアドバイスはとても刺激になりました。
これまでネット上のものはすべてプリントアウトかコピペだけだっだのです(恥っ

重ねてご親切ありがとうございました。

回答No.8

marbin

#4です。
>取り込みもとのアドレスをコピーして、貼り付けようとしたのですが右クリックがどうしても効きません。
Ctrl+V
で貼り付けることが出来ます。
この方法は右クリックでメニューが出てこない場面の多くで使えます。
同様にコピーしてクリップボードに格納するのは
Ctrl+C
でいけます。

補足

こんばんは、再度のアドバイスありがとうございます。

＞Ctrl+V
今回、これも大きな収穫でした。
(いかに私のレベルが低いのかお分かり頂けたと思いますが、(恥っ…
これまで、これでとても苦労してきました。

marbin様はじめ、ご回答者の皆様のお陰で今回質問内容以外でもいろいろ勉強になりました。
有難うございました。

回答No.6

noname#70958

銀行によって違うかもしれませんが、
取引内容をCSVファイル等でダウンロードする機能あるいはサービスが提供されていないか、
一度確認されてみてはいかがでしょうか？
CSVファイルはExcelで開くことができますし、データもそのままExcelで利用できます。
-------------------------------------------------------
次に、当面の課題についてですが、
おそらく、ペーストしたデータに
「エクセル上で表示されない特殊な文字(コード)」
が紛れているものと思われます。

以下の手順をお試しください。

１．コピーしたデータを新規ブックのA1以下に貼り付ける

２．空き列の1行目に、
　数式
　　=TRIM(SUBSTITUTE(A1,MID(A1,MATCH(TRUE,CODE(MID(A1,{1,2,3},1))=63,0),1),""))
　を入力して右方・下方にフィルする。

　※予想…というか一部憶測で式を立てていますので、
　　もしこの時点でエラーが返るようでしたら、
　　次の数式の結果を補足してください。
　　状況チェック用１：　=CODE(TRIM(A1))
　　状況チェック用２：　=FIND(LEFT(TRIM(A1),1),A1)　　

３．フィルした部分をコピーし、
　　右クリック　＞　[形式を選択して貼り付け]　＞　[値]　＞　[OK]
　　で数式を値に確定。

４．適当な空白セルをコピーし、
　　３の表を選択して
　　右クリック　＞　[形式を選択して貼り付け]　＞　[加算]　＞　[OK]
　　で金額部分を数値に変換。（文字列はそのまま）

これで計算可能な「きれいな」データになると思うのですが…。
----------------------------------------------------------------------
下記もおそらく同様の事例です。
（noname#52504は私の旧アカウントです。
　手前味噌ですみませんが参考に）

■excelの数字の表示について
http://bekkoame.okwave.jp/qa3746058.html
■Microsoft Office Excel (2007)で各セルに入っている空白を消したい
http://bekkoame.okwave.jp/qa3448721.html

以上ご参考まで。長乱文陳謝。

補足

こんばんは、アドバイスありがとうございます

早速過去のご回答も読ませて頂きました。
私にはレベルが高くて理解に自信がありませんが、これだ！という手ごたえを感じました。

＞CSVファイルのダウンロード
に関しては、現在利用しています。
但し、自動引落のデータ交換で銀行とは別会社(元は同じ)での契約になっていますし、外部ファイルのやり取りの場所の管理があちらにとっては面倒なようで、データ交換は2本立てなのにちょっと前まで1つしかなく、油断するとAデータを取らないうちにBデータに入れ替えてあるような状況でした。(地方銀行なのでまだまだサービスが遅れているのかもしれません。

ご教示頂いた１、２をやってみました。
残念ながらエラーで返ってきました。原因を掴もうと提示されている関数の意味を調べてみました。
それでMID関数の単独利用であれば、私にも出来そうでしたので以下の手順でやってみました。
１．数値と文字列の混じった列(出金額、入金元の項目が混在する列)をオートフィルタで昇順で並び替え
　※(文字列,開始位置,文字数)の開始位置を数値の桁で調節しないとスペースと\マークが残ってしまうので、オートフィルで作業を簡略するために同じ桁数のセルを集めました。
２．次にKater_Kurz様のご教示の3,4できれいな数値になりました。
　(やったぁ～、すごく嬉しかったです、ありがとうございました)
３．元の列の数値部分を削除し項目のみの列にしました。
４．MID関数で返した列は削除
５．同様の作業を入金額、出金先の項目の混在する列で繰り返しました。
６．計算可能なきれいな出金額、入金額の列が出来上がったら、日付の列をオートフィルタで昇順に並べ、元の順序に戻しました。

これでなんとかやれました。
数値を入力し直すのに比べれば、全然楽で正確にやれそうです。助かりました。

日常の業務がそこそこ多いので、関数を理解する時間が取れず(時間がないことより、能力の問題が大きそうですが…)Kater_Kurz様の数式を即座に使えこなせなくて残念です。
でも、組み合わされた一つ一つの関数を理解すべくこれから少しずつやってみようと思います。
正直わくわくします。有難うございました。

お礼欄は、Kater_Kurz様に教えて頂いた数式を理解し使えるようになったときのためのご報告の為に、今は空けておきたいと思います。
願掛けの意味も込めて…

回答No.5

imogasi

>コピペのままだと計算式が使えず
エクセルに貼り付けると、A列だけに文字列で張り付いているのではないですか。データー区切り位置ー・・（多分スペースを区切り文字として分けると、うまく項目に分かれると思う）の操作を知りませんか。
それで各項目ごとに別れて、その後計算式はそれ（分離後の列）以外（以右）に入れて計算させることになります。
ーー
＞個人用、会社用、預かり金が混在しているので
これは自動的に分類する手がかりは、＞口座の入出金表、からは得られないと思うので、１行１行質問者が判断して、１・２・３などのコードを別列に振る必要があるでしょう。
その後、フィルタやフィルタオプションの設定や関数（１条件の抜き出し。下記（注）参照）で別シートに振り分けることになると思います。
あるいはピヴォットテーブルの利用が簡便かもしれない。
（注）関数による１条件の抜き出しは、Googleで「imogasi方式」で照会すると、私が答えた同類の抜き出し問題が多数出ます。そこには
私の回答（作業列をやむをえず使う方法）のほかに数種の型（タイプ）のやり方がわかります。

補足

アドバイス、有難うございます。

エクセルに貼り付けるときれいに行、列に一つ一つのデータが入ります。
そのままExcelの表に収まっています。
区切り位置の設定もやってみましたが、変化がありませんでした。

＞自動的に分類する
これについては、また次の機会に質問しようと思っていました。
「置換」で、拾おうとしたのですがなぜかうまくいきませんでした。
コードを振るのはいいやり方だと思いました。
数字だけの入力なら、簡単にミスも少なくやれそうです。
項目別に別シートに振り分けた後なら、数値の入力し直しもすごくやりやすくなります。
ご紹介の“Googleで「imogasi方式」で照会する”は、まだやっていませんが明日のお昼休みに調べます。

いろいろご教示ありがとうございました。
来期の決済時は今年よりスマートに作業が進められそうです。

回答No.4

marbin

WEBクエリを使ってはいかがでしょう？

http://www.fujicom.co.jp/salon/it_info/07webquery/webquery.html

補足

アドバイス、有難うございます。

こんなことが出来るんですね、びっくりです。
ネット上のデータを取り込んでしかも自動更新できるなんて…

早速、やってみました。
今現在の私のやり方は、データ取り込みというよりコピペなので
“取り込め”ば、計算が可能になるのかもしれません。

ただ、結果を先に報告すると、取り込めませんでした。
取り込みもとのアドレスをコピーして、貼り付けようとしたのですが右クリックがどうしても効きません。
それで、苦手なアドレス直接入力を注意深くやってみましたが、開けませんでした。(何度か入力しなおしましたが結果は同じでした)
おそらく、取り込みたいページはログインして入るところなのでログインページからでないと表示できないのだと思います。
残念です。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

取り込み後
データ - 区切り位置
●カンマやタブ～～～～[次へ]
[レ]スペース にチェックして[完了]
としてみてはいかが？

補足

アドバイス、有難うございます。

データはExcelどうしのコピペのように、きれいにExcelの表に列、行が収まり、ひとつのセルにひとつずつデータが入っています。
(ど素人の表現なのでうまく伝えられてないかもしれません…)
アドバイスの区切り位置の設定もしてみましたが、変化がありませんでした。

私の浅識では、状況をうまく伝えられていないようです。

コピペしたあとの
数値の入っているセルは、スペースが10個分近く入って、\マーク、印額が並び、右端揃えになっています。
たとえば、文字列として\マークをつけて入力した数字は数値として認識されないのでしょうか。。。
（…って又質問するのは違反だったでしょうか）

回答No.2

pascal3141

計算できないのは、数値の前にスペースが入っているため、文字として認識しているからだと思われます。そのため、数値のセルは「Trim」関数でスペースを取ってしまえば計算できるようになるはずです。

補足

アドバイス、有難うございます。

このような関数があるのですね
発想もないので、調べてもいず恥かしいです。

早速やってみました。
でも、なぜだか上手くいきません。
スペースがとれているようでもありませんし、「Trim」関数後の計算も出来ません。
どうにも文字列としてしか認識してくれないようです。
セルの書式設定の表示を数値にしても変化ありません。
なにかやり方がまずいのでしょうか。
この関数でしばらく試行錯誤してみようと思います。

利用できる関数をもっと知るべきだと、いまさら思い知りました。
日頃の業務に追われてつい後回しになっていますが、
「知りたいときがわかるとき」(←これって、自分の造語なのか誰かのことばなのかいまだに謎なんですが…)
決算のめどもたちましたので、いい機会なので時間を少し回して勉強しようと思います。
有難うございました。

回答No.1

gyouda1114

口座管理用のフリーソフトを使ってみては
「銀行口座管理ソフト」で検索するとでますよ

補足

アドバイス有難うございます。

早速、検索して調べてみました。
複数のネット口座の管理ソフトのようで、
ネット上の出入金表を帳簿に利用したい私の目的には使えないようです。
(私の思い違いなら、再度ご教示くださればありがたく思います）

でも、現に2つのネット口座を現在しようしていますし、将来的には増える可能性があるので、
後学の為になると思いました。
有難うございました。