建設業に特化した会計ソフトの「建設大臣」から仕訳日記帳などの会計データをエクスポートした際に勘定科目欄に補助科目が結合された状態でエクスポートされることがありました。
このデータを「勘定科目」と「補助科目」に別々にわける方法を考えたので記事にしておきます。
建設業に特化した会計ソフト「建設大臣」のデータを弥生会計に移したい
建設業に特化した会計ソフトの「建設大臣」から仕訳データを弥生会計に移す必要があったので、建設大臣側から仕訳データを出力したところ、勘定科目に関して下記のように出力される結果となりました。
見ていただくとわかるのですが、勘定科目と補助科目がそれぞれ区分されておらず、勘定科目と補助科目が１つのデータとして出力されています。勘定科目と補助科目の間に「スペース」が設けられているようです。
で、これだけならまだいいんですが、補助科目がない勘定科目についても無駄に「文字と文字の間」に「スペース」が設けられているのが確認できます。これです↓
なぜスペースがあるのか、なんでこんな仕様にしたのか開発者の意図がまったく理解できないのですが、そういう仕様なので仕方がありません。
ですが、弥生会計にインポートする際には勘定科目と補助科目を区分してインポートする必要があります。
補助科目を設定せずにすべてを「勘定科目」として弥生会計にインポートするやりかたもあるといえばあるのですが、できる限り元の「建設大臣」のデータに近い形で弥生会計で復元したいのです。
Excel関数で空白を目印にして勘定科目と補助科目を分離させようとするも挫折
当初、Excelの「区切り位置」機能やExcel関数を使って勘定科目と補助科目を区分しようとしましたが、挫折しました。
挫折する原因となったのがさきほどのこいつらのせいです↓
補助科目が設定されていないのに、なぜか勘定科目にスペースが入っているヤツです。
Excelの区切り位置機能を使用して「スペース」でデータを区切ってみたところ、これが
こんな感じになりました。。。。。
当座預金など、補助科目が設定されていた科目に関してはこれで問題ないのですが、それ以外の科目はこれではちょっと使えそうにありません。
なのでこのやり方は却下です。
その後、Excel関数のFind関数やらLeft関数やら使ってデータ加工をできないかやってみたのですがこちらの意図したとおりにデータの加工ができませんでした。
Excelマクロで力技で勘定科目と補助科目を分離させる
Excelに搭載されている機能や、Excel関数でもちょっと難しそうなんでマクロを使って力技で加工しようと思います。
で、こんな感じのマクロを作ってみました。
Option Explicit
Sub test()
’■勘定科目と補助科目とを分離させるマクロ
’■変数宣言
Dim i As Long
Dim rng As Range
Dim startrng As Range
Dim word As String
i = Cells(Rows.Count, 1).End(xlUp).Row ’最終行の取得
word = "当座預金" ’←ここに分離させる勘定科目を入力する！
Set rng = Range("a:a").Find(what:=word, lookat:=xlPart, LookIn:=xlValues)
’■処理実行部分
If rng Is Nothing Then
MsgBox word & "という勘定科目はありません"
Exit Sub
Else
Set startrng = rng
rng.Offset(0, 1).Value = Left(rng.Value, Len(word))
rng.Offset(0, 2).Value = Mid(rng.Value, Len(word) + 1)
’■繰り返し処理
Do
Set rng = Range("a:a").FindNext(rng)
If rng.Address = startrng.Address Then
Exit Do
End If
rng.Offset(0, 1).Value = Left(rng.Value, Len(word))
rng.Offset(0, 2).Value = Mid(rng.Value, Len(word) + 1)
Loop
End If
MsgBox "処理完了"
End Sub
このマクロですが、マクロを実行する前に勘定科目が列挙されている列に対して「検索と置換」機能で、空白データをあらかじめ削除しておきましょう。
この状態でマクロの15行目のハイライト行の部分に勘定科目と補助科目を分離したい科目名を記載してマクロを実行すると勘定科目と補助科目が分離された状態でB列とC列に入力されます。
例えばマクロの15行目に「当座預金」と入力してマクロを実行すると
こんな感じになります↓
あとはマクロの15行目の「勘定科目名」を書き換えてマクロの実行を繰り返すだけです。
「普通預金」と「買掛金」に関してマクロを使って分離させた後の画像が下記になります↓
自分で作っておいて何ですが、わざわざマクロに勘定科目を毎回入力するもの面倒臭かったり臭くなかったり。。。。
勘定科目と補助科目を分離させるマクロの内容解説
今回のマクロでポイントは３つです。Len関数、Left関数とMid関数の３つです。
この３つに関して簡単に解説しておきます。
Len関数
Len関数は文字数が何文字あるかを取得することができます。
たとえばこんな感じで
B2セルに「年越しそば」という文字が入力されていて、この文字数を調べる場合にLen関数を使います。
このマクロを実行すると
こうなります。「年越しそば」の文字数は5文字なので「５」が表示されました。
文字を変更して再度マクロを実行すると当然結果が変わります。
これでマクロを実行するとこうなります↓
「年越してしまったそば」の文字数は10文字なので「１０」が表示されました。
今回作成したマクロはこの部分でLen関数を使っています。
具体的には変数「word」に格納されたデータの文字数を取得させるために使っています。
上のマクロでは変数「word」には「当座預金」という文字データが格納されているので、Len（word)とすることで「当座預金」の文字数である「４」が表現されることになります。
もし変数「word」を書き換えて「売掛金」とした場合にはLen（word)とすることで「売掛金」の文字数である「３」が表現されることになるわけです。
Left関数
Left関数は指定した文字に対して左側から指定した文字数だけ取得させるときに使います。
Left関数に関してはExcel関数のLeft関数と同じなので特に説明は必要ないかもしれませんが。。
上のマクロではこの部分で使っています
変数「rng」に格納されたデータに対してLen（word）で指定した文字数分だけ左側から取得させています。
例えば変数「rng」に「当座預金〇〇銀行」というデータが格納されていて、変数「word」に対して「当座預金」というデータが格納されている場合を考えます
この状態で上のようにマクロを書くことで「当座預金〇〇銀行」というデータのうち左側から４文字だけ取得させることができます。「当座預金○○銀行」の左側4文字なので結果として「当座預金」が取得されます。
Mid関数
Mid関数は指定した文字に対して、指定した文字数以降のデータを取得することができます。
Excel関数にもMid関数はありますが、マクロのMid関数のいいなと思う点は最後の引数（何文字を抽出するか）を指定しなくてもいいという点です。
指定しなかった場合は取得開始文字以降のすべての文字を抽出することになります。これが便利です。
今回のマクロの場合にはこの特徴がいい方向に作用することになりました。
この部分で使っています↓
こうすることで変数「rng」に格納されたデータに対して、Len（word）＋１で指定した文字数以降のすべての文字を取得させることができます。
例えば変数「rng」に「当座預金〇〇銀行」というデータが格納されていて、変数「word」に対して「当座預金」というデータが格納されている場合を考えます。
この状態で上のようにマクロを書くことで「当座預金〇〇銀行」というデータのうち５文字目以降の文字を全て取得させることができます。「当座預金○○銀行」の５文字目以降なので結果として「〇〇銀行」が取得されます。
Do~Loopで繰り返し処理
繰り返しの処理に関してはDo～Loopを使用しています。Do～Loopに関してはこちらの記事を参考にしていただければと思います。
今回のマクロでバグが発生するケースについて
今回のマクロですが、下記のような勘定科目を使用している場合にこちらの意図しない結果がでました。
「現金売上高」
この「現金売上高」という科目、補助科目は設定していない勘定科目なのですが、
今回のマクロで例えば「現金」という勘定科目に補助科目を付けている場合（現金の補助科目に「小口現金」と「小口現金２」という２つの補助科目をつけて運用している場合等）で
今回作ったマクロで「現金」に対して勘定科目と補助科目を分離させようとして処理を実行すると上記の「現金売上高」という補助科目が設定されていない科目についても分離されてしまいます。
具体的には「現金売上高」という勘定科目が「現金」という勘定科目と「売上高」という補助科目に分離してしまうバグが発生します。
こういったケースがなければ問題なく意図した結果を得ることができますが、もし同様のケースがある場合には注意が必要です。
まとめ
会計データをCSV形式などに出力すると勘定科目と補助科目が同一セル内に1つのデータとして出力された場合にそのデータを勘定科目と補助科目に分離させる方法を記事にしました。
本来はマクロを使わずに勘定科目と補助科目をわけるのがベストなんですが、自分のスキルではそのやり方を見出すことができませんでした。結果として（バグが発生する可能性がある）マクロで力技でねじ伏せる形になってしまいました。
【本日の近況報告】
国税庁の確定申告書作成コーナーが2021年分の申告に対応したので軽く触ってみました。ブラウザはインターネットエクスプローラーしか使えないようで、昨年と変わってないようですね。
あと申告期限の延長は今年はないみたいですね。
【本日の１曲】
the clash/pressure drop
初期パンクバンド、クラッシュからの１曲。こちらカバー曲でオリジナルはToots and the Maytals
2022年を迎えて最初に聴いたのはクラッシュだったのでこの曲をセレクトしました。オリジナルもクラッシュのカバーもどちらもいい曲ですね。
