電気代などの毎月口座から引落がされる取引などに関して通帳の摘要欄に「〇〇月分 電気代」などと表示される場合にExcelマクロのMID関数を使用して「〇〇月分」と記載された以降のデータのみに加工するマクロを考えましたので備忘のため記事にしておきます。
目次
今回加工の対象となるインターネットバンキングのデータについて
今回想定しているインターネットバンキングのデータは下記の画像のようなものになります↓
ポイントはD列の摘要部分です。電気代が毎月口座引落されているのですが、ご丁寧に「０４０１（令和4年1月という意味）」の文字が記載がされています。
この部分なんですが、1月に引落がされた場合には「デンキ ０４０１デントウ」と摘要に表示されますが、2月に引落された場合には「デンキ ０４０２デントウ」といった具合に摘要の表示が変化してしまいます。
インターネットバンキングで会計ソフトへインポートするデータを作成する際に、毎回毎回摘要の表示が変わるとデータの加工が難しくなるため、個人的には好きではありません。
クラウド会計ソフトであれば、自動仕訳ルールの登録の際に「部分一致」という条件で登録すればこの問題は簡単に解決するのですが、今回はあくまでCSVインポートをする前提でこのデータを加工していきたいと考えています。
この摘要ですが、毎月毎月変化する部分は「０４０１」の部分、つまり何月分の電気代かという表示をしている部分のみで、この部分より後ろの部分である「デントウ」に関しては1月だろうが、2月になろうが変わらない箇所になります。
なので、この部分だけ残して他の部分は削除してしまえば毎月、電気代が口座から引き落とされたとしても「デントウ」や「デンリョク」といった同じ摘要にすることができます。
これを踏まえて再度上の画像のD列の摘要欄をみると、「デントウ」や「デンリョク」という部分は必ず決まった文字数（今回の場合14文字目）以降に記載がされていることが確認できます。
このように絶対に14文字目以降に記載されるというルールがあるのでこれを利用してデータの加工を行っていけば上手くいくはずです。
このデータ加工に使用するのが「MID関数」です。この関数を使用すると14文字目以降に記載されたデータのみを取得することができます。
指定したキーワードを検索して、特定の文字数以降のデータのみを取得するマクロ
特定の文字数以降のデータのみを取得するためにMID関数を使用して作成したマクロは下記のようなものになります。
変数宣言して、findでセルを検索してデータを格納し、処理を実行。その後同様の作業をDo～Loopステートメントで繰り返し行います。
Option Explicit
Sub test()
'■指定したキーワードを検索して特定の文字数以降のデータのみに加工するマクロ
Dim rng As Range
Set rng = Range("d:d").Find(what:="ﾃﾞﾝｷ", lookat:=xlPart)
If rng Is Nothing Then
MsgBox "「ﾃﾞﾝｷ」という摘要はありません"
Exit Sub
End If
'■「ﾃﾞﾝｷ 0401ﾃﾞﾝﾄｳ」→「ﾃﾞﾝﾄｳ」へ摘要を変換する
rng.Value = Mid(rng, 14)
Do
Set rng = Range("d:d").FindNext(rng)
If rng Is Nothing Then
Exit Do
End If
rng.Value = Mid(rng, 14) 'ここで指定した文字数以降のデータのみに加工する
Loop
MsgBox "done"
End Sub
このマクロを実行すると、最初のデータが
こんな感じになります↓
D列の摘要がスッキリしました。
マクロの解説
ざっくりにはなりますが、今回のマクロの内容を自分なりに解説してみます。
Find関数で「部分一致」で検索する
まずは変数宣言をしておいて変数「rng」に今回加工したいデータを格納する必要があります。
今回は検索する際にFind関数を使用して「部分一致」で検索させています。
マクロのコードの7行目と8行目のハイライト表示にしている部分がそれになります。
Find関数の引数、「lookat」に対して「xlpart」を指定すると部分一致で検索することができます。なので今回の場合には「ﾃﾞﾝｷ」と記載されたデータをＤ列から部分一致で検索し、該当するセルがあればそのセルを変数「rng」に格納するようにコードを書いていることになります。
Mid関数で指定した文字数以降のデータを取得する
ここまでの作業で変数「rng」に対して「デンキ～」というデータが格納されているはずです。
次にこの格納されたデータをMID関数を使用して加工していきます。
MID関数は指定した文字数以降のデータを取得することができる関数で、こんな感じで使用します。
F１セルに「月火水木金土」というデータを準備します↓
この状態で
Sub test()
MsgBox Mid(Range("f1"), 4)
End Sub
というマクロを作成して実行するとこうなります↓
F1セルのデータについて、4文字目以降をメッセージボックスに表示することができました。
こんな感じで使います。
なのでマクロを書き換えて
Sub test()
MsgBox Mid(Range("f1"), 2)
End Sub
「４」を「２」にすることでF1セルのデータについて2文字目以降を取得することができます。
結果としてメッセージボックスに「火水木金土」が表示されます↓
こんな感じです。
Excelの関数と違って、開始する文字数だけ指定するとその指定した文字以降のすべての文字を取得してくれるので便利です。
このMID関数を使って、今回は変数「rng」に格納された「ﾃﾞﾝｷ 0401ﾃﾞﾝﾄｳ」というデータについて14文字目以降を取得させています。
それが上のマクロの18行目のハイライト表示部分になります。
まとめ
指定したキーワードを検索して、特定の文字数以降のデータを取得するマクロを記事にしました。
他にもやりかたがあるとは思いますが、１つのやり方として参考になれば幸いです。
「デントウ」と「デンリョク」で仕訳を区別しないのであれば、Left関数を使用して「デンキ ０４０１デントウ」の「デンキ」部分だけにデータを加工するのも一つの手ですね。
【本日の近況報告】
寒くなったせいか中度のぎっくり腰を発症しています。決まった方向に寝返りをうつと一瞬激痛が走ります。
いつもは飲み薬と湿布で２，3日で直るのですが、今回は痛みが長期化しています。もう長い付き合いなので、「またか。」という感じなんですが。。。
【本日の１曲】
Culture Club/Karma Chameleon
冬のオリンピック、北京オリンピックが開幕しました。開幕前に開始した競技で（多分モーグルだったような）この曲が流れていました。
聴いた瞬間この曲の7インチレコードを中古レコード屋で100円くらいで買った記憶が蘇ってきました。
