スポンサーリンク

マクロ(Excel VBA)で会計ソフトのデータを加工するときの工夫、科目・金額などの項目を検索して列番号を把握する


会計ソフトから CSV 出力したデータを Excel シートに貼り付けてから、そのデータを加工することがあります。

CSV データを Excel シートの中に取り込む方法についてはこちらの記事を参考にして下さい。

さて、こうして Excel シートの中に取り込んだ会計ソフトのデータをマクロ(Excel VBA)を使って加工するに当たり、工夫していることを紹介します。

一言で言えば、元のデータの並びが多少変わっても VBA コードを変更しなくても良いように、汎用性があるコードを書くということです。

スポンサーリンク

1、会計ソフトのデータを加工する場合の一例

さて、説明にあたり具体例を取り上げます。

会計ソフト(JDL)から CSV 出力した仕訳データを Excel シート「仕訳データ」に貼り付けてあります。

Image(6)

この仕訳データを元に VBA を使って計算を行うことにします。

次の「メイン画面」シートの「実行ボタン」をクリックすればマクロが実行して、売上高合計と仕入高合計を計算して出力します。

Image(7)

上のクリーム色のセル

・セル「D5」に売上高合計

・セル「D6」に仕入高合計

を出力します。

マクロを実行したら、一瞬で「仕訳データ」シート内の仕訳データを元に計算を行い結果を表示します。

Image(8)

*VBA を使わなくてもできる計算ですが、今回はあえて VBA で計算しますね!

2、科目、金額などの項目を仕訳データから読み取るときの問題点

データの並び

さて、今回の計算の元となる「仕訳データ」は以下の項目で成り立っています。

・日付
・借方科目
・借方補助
・方科目
・貸方補助
・金額
・摘要

これらの項目は7行目に並んでいます。

Image(9)

並べられている列番号は

・日付      2列目
・借方科目   3列目
・借方補助   5列目
・貸方科目   7列目
・貸方補助   9列目
・金額      11列目
・摘要   12列目

Image(10)

さて、今回の顧問先「デモ1」のデータ(実在のものとは一切関係ありません)の CSV ファイルは上記のようなデータの並びになっているのですが、会計ソフトに登録されている会社によっては例えば部門 No の列が入っているような場合もあります。

その場合、上記の項目の列番号が上に示した Excel シートとは異なるわけですね(部門 の列が入るためずれる)。

Image(11)

そのように顧問先によって複数のデータの並びがあるのが実情です。

一番最初の「日付」データ(8行目)のセルの指定を VBA で書けば

Cells(8,2)

Cells(8,5)

の2種類存在することになりますよね。

部門が無い場合↓

Image(12)

部門がある場合↓

Image(13)

そして、日付、金額などの項目名は上記のとおり7行目に並べられておりますが、ソフトの仕様が変われば行番号が変わる可能性もあります(今のところ変わっていませんが)。

VBA コードを記述する前に考えておきたいこととして、これらのデータの並びが若干変わっても問題なく動作する「汎用性が高い VBA コードにする」ということがあります。

データの並びが複数あるからと言って、会社ごとに VBA コードを分けるのも大変なことです。1つの VBA コードで汎用的に使えるようにしましょう!

3、VBA コード

全コード

さて、今回の売上高合計と仕入高合計を計算して出力する VBA コードを以下に示します。

VBE(エディタ)の標準モジュールに「Goukei_Cal」プロシージャの1つだけを作ります。

コード説明

上記コードの説明をします。

変数の定義

日付、金額などの各項目の列番号を入れる変数を次のように定義しています。

これは先にも説明したように、各項目の列番号がいつも一緒というわけではありません。

部門の有無などにより異なることがあります。

Image(11)[1]

顧問先ごとの設定によって変わるので、まずは変数として定義しておきます。

データが開始する行番号を次のように定義しています。

Image(14)

もし会計ソフトの仕様変更がありデータが開始する行番号が変わったとしても、すぐに置き換えれるように変数で指定しているのです。

売上高合計と仕入高合計を次のように定義しています。

For 文、Do Loop で使う1ずつ増加していく変数 i、j を定義しています。

変数への値代入

変数に値を代入します。

Start_Row は最初のデータの行番号です。

今回は 8ですね。

Image(15)

*会計ソフトの仕様変更により最初のデータの行番号が変わっても、この変数「Start_Row」に代入する値(今回は 8)を変更すれば良いだけです。

売上高合計を計算するための変数 Uriae_Goukei、仕入高合計を計算するための変数 Shiire_Goukei の初期値を 0としておきます。

各項目の列番号を検索する

さて、今回の記事で最も重要なところです。

日付、金額などの各項目名の列番号を変数に入れ込みます。

For 文と Select Case ステートメントを使って各項目名が書いてある行を横方向に検索して、変数に列番号を入れて行きます。

セル「Cells(Start_Row – 1, i) 」に書いてある項目を1つの行 左から右に向かって調べていくということです。

Image(16)

上記セルの行番号 Start_Row – 1 は今回の場合は 7になります。

列番号 i を1から 200 まで変化させて各セルを調べていきます。

項目名の数は 7個なので i を 200 まで変化させるというのは、敢えてかなり余裕を持たせています。

それでもコードの実行に時間はかかりません。

以下のように順番にセルを調べていくことになりますね。

Cells(7, 1)

Cells(7, 2)

Cells(7, 3)

Cells(7, 200)

どのようにして調べるかと言うと Select Case ステートメントを使うのです。

セル「Cells(Start_Row – 1, i) 」に入力してある内容が各項目名と一致するか場合分けして、変数に列番号を入れて行きます。

セル「Cells(Start_Row – 1, i) 」に

「日付」と書いてあるか場合、「日付」の列番号を表す変数「Hiduke_Col」に i を入れ込みます。

これで「日付」の列番号が変数「Hiduke_Col」に入ったことになりますね。

Image(17)

同様に

「借方科目」の列番号を変数「Kari_Kamoku_Col」に入れます。

「借方補助」の列番号を変数「Kari_Hojo_Col」に入れます。

「貸方科目」の列番号を変数「Kashi_Kamoku_Col」に入れます。

「貸方補助」の列番号を変数「Kashi_Hojo_Col」に入れます。

「金額」の列番号を変数「Kingaku_Col」に入れます。

「摘要」の列番号を変数「Tekiyou_Col」に入れます。

さて、今回のポイントはこのように各項目名が書いてある行を検索して変数を決定することです。

※今回、これらの項目名を表す変数の全てを使うわけではありません。

こうしておけば顧問先によってデータの並びが多少変わって各項目の列番号がずれても、コードを変えることなく変数に入れることができますよね。

Image(4)

Image(5)

なお、各項目の列番号を直接代入する方法も考えられます。

「日付」の列番号であれば

Hiduke_Col = 2

とするということですね。

ただ、このように指定してしまうとデータの並びが変わったときに、コードを変更する必要があります。

以下のように、元のデータが変わった場合

Image(18)

Hiduke_Col = 5

と変数に代入する数値を変更しなければなりません。

項目の数だけ変数に代入する数値を変更するのは面倒です。

ですので、For 文と Select Case ステートメントを用いて検索するようにしましょう。

売上高合計、仕入高合計を計算する

Do Loop ステートメントを使って売上高合計、仕入高合計を計算します。

日付の列を示すセル「Cells(Start_Row + j – 1, Hiduke_Col)」を

j を 1 から1つずつプラスして調べて行き、空白でない限り処理を続けます。

Image(19)

売上高合計について Do Loop の中で行なっている計算は、

「貸方科目」を表すセル「Cells(Start_Row + j – 1, Kashi_Kamoku_Col) 」が売上のコード 612 と一致した場合に、変数 Uriae_Goukei に「金額」を表すセル「Cells(Start_Row + j – 1, Kingaku_Col)」を足していくというものです。

Image(20)

仕入高合計も同様にして計算しています。

こちらは「借方科目」を表すセル「Kari_Kamoku_Col) 」が仕入のコード 712 と一致した場合に、変数 Shiire_Goukei に「金額」を表すセル「Cells(Start_Row + j – 1, Kingaku_Col)」を足していくというものです。

売上高合計、仕入高合計を出力する

最後に「メイン画面」シートをアクティブにした後にセル「D5」に売上高合計、セル「D6」に仕入高合計を出力します。

4、まとめ

マクロ(Excel VBA)で会計ソフトのデータを加工するときの工夫を1つお話ししました。

元となるデータが少々変わってもコードを変更しなくても良い仕組みを作りたいものです。

日頃なるべく汎用性が高いコードを書くように意識しているつもりです。