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

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

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

マクロ（Excel VBA）で CSV ファイルを開いてシートの中に取り込む方法

https://my-tax-nology.com/import-csv-data-to-excel-by-using-excel-vba

会計税務ソフトや IT 機器でデータを CSV 入出力ができるか否かは重要です。データをCSV 出力できれば、そのデータを Excel シート上に取り込んで使うことができます。今日は、選択した CSV ファイルの中身を Excel シート上に取り込むマクロ（Excel VBA）を実例を示しながら紹介します。1、CSV 出力したデータの使い方実例として、「科目コードと科目名」のデータが入った CSVファイルを選択して Excel シートに取り込んで使う場合を考えてみます。シート上に入力した科目コードから科目名を検索できるようにします。シート設計Excel ...

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

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

1、会計ソフトのデータを加工する場合の一例
2、科目、金額などの項目を仕訳データから読み取るときの問題点
1. データの並び
3、VBA コード
1. 全コード
2. コード説明
4、まとめ

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

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

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

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

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

上のクリーム色のセル

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

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

を出力します。

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

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

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

データの並び

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

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

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

並べられている列番号は

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

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

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

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

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

Cells（8,2）

と

Cells（8,5）

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

部門が無い場合↓

部門がある場合↓

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

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

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

3、VBA コード

全コード

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

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

Sub Goukei_Cal()
    
    Dim Hiduke_Col          '日付の列番号'
    Dim Kari_Kamoku_Col     '借方科目の列番号'
    Dim Kari_Hojo_Col       '借方補助の列番号'
    Dim Kashi_Kamoku_Col    '貸方科目の列番号'
    Dim Kashi_Hojo_Col      '貸方補助の列番号'
    Dim Kingaku_Col         '金額の列番号'
    Dim Tekiyou_Col         '摘要の列番号'
    
    Dim Start_Row           'データが開始する行番号'
    
    Dim Uriae_Goukei        '売上高の合計'
    Dim Shiire_Goukei       '仕入高の合計'
    
    Dim i
    Dim j
    
    Worksheets("仕訳データ").Activate
    
    Start_Row = 8
    Uriae_Goukei = 0
    Shiire_Goukei = 0
    
    
    '各項目が何列目にあるか把握する'
    For i = 1 To 200
        
        Select Case Cells(Start_Row - 1, i)
            
            Case "日付"
                Hiduke_Col = i
            Case "借方科目"
                Kari_Kamoku_Col = i
            Case "借方補助"
                Kari_Hojo_Col = i
            Case "貸方科目"
                Kashi_Kamoku_Col = i
            Case "貸方補助"
                Kashi_Hojo_Col = i
            Case "金額"
                Kingaku_Col = i
            Case "摘要"
                Tekiyou_Col = i
            Case Else
            
        End Select
        
    Next i


    j = 1
    
    Do While Cells(Start_Row + j - 1, Hiduke_Col) <> ""    '日付が無くなる行まで同じ処理を繰り返す'
        
        '科目コードが612の場合の金額を合計'
        If Cells(Start_Row + j - 1, Kashi_Kamoku_Col) = 612 Then
            Uriae_Goukei = Uriae_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)
        Else
        End If
        
        '科目コードが712の場合の金額を合計'
        If Cells(Start_Row + j - 1, Kari_Kamoku_Col) = 712 Then
            Shiire_Goukei = Shiire_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)
        Else
        End If
        
        j = j + 1
        
    Loop
    
    Worksheets("メイン画面").Activate
    
    
    Cells(5, 4) = Uriae_Goukei      '売上高合計を出力'
    Cells(6, 4) = Shiire_Goukei     '仕入高合計を出力'
    
End Sub

Sub Goukei_Cal()

Dim Hiduke_Col '日付の列番号'

Dim Kari_Kamoku_Col '借方科目の列番号'

Dim Kari_Hojo_Col '借方補助の列番号'

Dim Kashi_Kamoku_Col '貸方科目の列番号'

Dim Kashi_Hojo_Col '貸方補助の列番号'

Dim Kingaku_Col '金額の列番号'

Dim Tekiyou_Col '摘要の列番号'

Dim Start_Row 'データが開始する行番号'

Dim Uriae_Goukei '売上高の合計'

Dim Shiire_Goukei '仕入高の合計'

Dim i

Dim j

Worksheets("仕訳データ").Activate

Start_Row = 8

Uriae_Goukei = 0

Shiire_Goukei = 0

'各項目が何列目にあるか把握する'

For i = 1 To 200

Select Case Cells(Start_Row - 1, i)

Case "日付"

Hiduke_Col = i

Case "借方科目"

Kari_Kamoku_Col = i

Case "借方補助"

Kari_Hojo_Col = i

Case "貸方科目"

Kashi_Kamoku_Col = i

Case "貸方補助"

Kashi_Hojo_Col = i

Case "金額"

Kingaku_Col = i

Case "摘要"

Tekiyou_Col = i

Case Else

End Select

Next i

j = 1

Do While Cells(Start_Row + j - 1, Hiduke_Col) <> "" '日付が無くなる行まで同じ処理を繰り返す'

'科目コードが612の場合の金額を合計'

If Cells(Start_Row + j - 1, Kashi_Kamoku_Col) = 612 Then

Uriae_Goukei = Uriae_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)

Else

End If

'科目コードが712の場合の金額を合計'

If Cells(Start_Row + j - 1, Kari_Kamoku_Col) = 712 Then

Shiire_Goukei = Shiire_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)

Else

End If

j = j + 1

Loop

Worksheets("メイン画面").Activate

Cells(5, 4) = Uriae_Goukei '売上高合計を出力'

Cells(6, 4) = Shiire_Goukei '仕入高合計を出力'

End Sub

コード説明

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

変数の定義

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

    Dim Hiduke_Col          '日付の列番号'
    Dim Kari_Kamoku_Col     '借方科目の列番号'
    Dim Kari_Hojo_Col       '借方補助の列番号'
    Dim Kashi_Kamoku_Col    '貸方科目の列番号'
    Dim Kashi_Hojo_Col      '貸方補助の列番号'
    Dim Kingaku_Col         '金額の列番号'
    Dim Tekiyou_Col         '摘要の列番号'

Dim Hiduke_Col '日付の列番号'

Dim Kari_Kamoku_Col '借方科目の列番号'

Dim Kari_Hojo_Col '借方補助の列番号'

Dim Kashi_Kamoku_Col '貸方科目の列番号'

Dim Kashi_Hojo_Col '貸方補助の列番号'

Dim Kingaku_Col '金額の列番号'

Dim Tekiyou_Col '摘要の列番号'

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

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

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

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

    Dim Start_Row           'データが開始する行番号'</p>

1	Dim Start_Row 'データが開始する行番号'</p>

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

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

    Dim Uriae_Goukei        '売上高の合計'
    Dim Shiire_Goukei       '仕入高の合計'

1 2	Dim Uriae_Goukei '売上高の合計' Dim Shiire_Goukei '仕入高の合計'

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

    Dim i
    Dim j

1 2	Dim i Dim j

変数への値代入

変数に値を代入します。

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

今回は 8ですね。

    Start_Row = 8

1	Start_Row = 8

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

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

    Uriae_Goukei = 0
    Shiire_Goukei = 0

1 2	Uriae_Goukei = 0 Shiire_Goukei = 0

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

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

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

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

    '各項目が何列目にあるか把握する'
    For i = 1 To 200
        
        Select Case Cells(Start_Row - 1, i)
            
            Case "日付"
                Hiduke_Col = i
            Case "借方科目"
                Kari_Kamoku_Col = i
            Case "借方補助"
                Kari_Hojo_Col = i
            Case "貸方科目"
                Kashi_Kamoku_Col = i
            Case "貸方補助"
                Kashi_Hojo_Col = i
            Case "金額"
                Kingaku_Col = i
            Case "摘要"
                Tekiyou_Col = i
            Case Else
            
        End Select
        
    Next i

'各項目が何列目にあるか把握する'

For i = 1 To 200

Select Case Cells(Start_Row - 1, i)

Case "日付"

Hiduke_Col = i

Case "借方科目"

Kari_Kamoku_Col = i

Case "借方補助"

Kari_Hojo_Col = i

Case "貸方科目"

Kashi_Kamoku_Col = i

Case "貸方補助"

Kashi_Hojo_Col = i

Case "金額"

Kingaku_Col = i

Case "摘要"

Tekiyou_Col = i

Case Else

End Select

Next i

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

上記セルの行番号 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 を入れ込みます。

            Case "日付"
                Hiduke_Col = i

1 2	Case "日付" Hiduke_Col = i

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

同様に

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

            Case "借方科目"
                Kari_Kamoku_Col = i

1 2	Case "借方科目" Kari_Kamoku_Col = i

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

            Case "借方補助"
                Kari_Hojo_Col = i

1 2	Case "借方補助" Kari_Hojo_Col = i

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

            Case "貸方科目"
                Kashi_Kamoku_Col = i

1 2	Case "貸方科目" Kashi_Kamoku_Col = i

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

            Case "貸方補助"
                Kashi_Hojo_Col = i

1 2	Case "貸方補助" Kashi_Hojo_Col = i

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

            Case "金額"
                Kingaku_Col = i

1 2	Case "金額" Kingaku_Col = i

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

            Case "摘要"
                Tekiyou_Col = i

1 2	Case "摘要" Tekiyou_Col = i

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

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

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

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

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

Hiduke_Col = 2

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

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

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

Hiduke_Col = 5

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

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

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

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

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

    j = 1
    
    Do While Cells(Start_Row + j - 1, Hiduke_Col) <> ""    '日付が無くなる行まで同じ処理を繰り返す'
        
        '科目コードが612の場合の金額を合計'
        If Cells(Start_Row + j - 1, Kashi_Kamoku_Col) = 612 Then
            Uriae_Goukei = Uriae_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)
        Else
        End If
        
        '科目コードが712の場合の金額を合計'
        If Cells(Start_Row + j - 1, Kari_Kamoku_Col) = 712 Then
            Shiire_Goukei = Shiire_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)
        Else
        End If
        
        j = j + 1
        
    Loop

j = 1

Do While Cells(Start_Row + j - 1, Hiduke_Col) <> "" '日付が無くなる行まで同じ処理を繰り返す'

'科目コードが612の場合の金額を合計'

If Cells(Start_Row + j - 1, Kashi_Kamoku_Col) = 612 Then

Uriae_Goukei = Uriae_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)

Else

End If

'科目コードが712の場合の金額を合計'

If Cells(Start_Row + j - 1, Kari_Kamoku_Col) = 712 Then

Shiire_Goukei = Shiire_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)

Else

End If

j = j + 1

Loop

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

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

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

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

        '科目コードが612の場合の金額を合計'
        If Cells(Start_Row + j - 1, Kashi_Kamoku_Col) = 612 Then
            Uriae_Goukei = Uriae_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)
        Else
        End If

'科目コードが612の場合の金額を合計'

If Cells(Start_Row + j - 1, Kashi_Kamoku_Col) = 612 Then

Uriae_Goukei = Uriae_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)

Else

End If

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

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

        '科目コードが712の場合の金額を合計'
        If Cells(Start_Row + j - 1, Kari_Kamoku_Col) = 712 Then
            Shiire_Goukei = Shiire_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)
        Else
        End If

'科目コードが712の場合の金額を合計'

If Cells(Start_Row + j - 1, Kari_Kamoku_Col) = 712 Then

Shiire_Goukei = Shiire_Goukei + Cells(Start_Row + j - 1, Kingaku_Col)

Else

End If

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

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

    Cells(5, 4) = Uriae_Goukei      '売上高合計を出力'
    Cells(6, 4) = Shiire_Goukei     '仕入高合計を出力'

1 2	Cells(5, 4) = Uriae_Goukei '売上高合計を出力' Cells(6, 4) = Shiire_Goukei '仕入高合計を出力'

4、まとめ

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

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

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