プログラミング

バッチ処理プログラムのパターン 1:N マッチング

プログラミング

こんにちは! 健史(たけふみ)です。

Excelシート上のデータやテキストファイルなどを先頭から順次読み込み処理するバッチ処理のロジックには基本パターンがあります。

順次読み込み処理するロジックは、プログラミングのベースとなるものです。

ファイルを使うバッチの処理パターンは、大きく以下の3つと考えており、殆どのバッチ処理をカバーできます。

1.非コントールブレイク処理
2.コントロールブレイク処理
3.マッチング処理
1).1:1マッチング
2).1:Nマッチング

これらを理解し流用することで、一定の品質・生産性・スピードを確保できます。

この記事では、1:Nのマッチング処理を紹介します。

スポンサーリンク

1:N マッチングの説明

マッチングとは、2つ以上のファイルを順次読み込み、キー項目を比較して処理することです。

「1:N マッチング」とは、商品マスタと売上ファイルのように
・商品マスタは、キーとなる商品コードに重複がなくユニーク
・売上ファイルは、キーとなる商品コードに重複がある
照合処理です。

例えば、商品コード順にソートされた商品マスタと売上ファイルがあり商品コードで照合し、
①.一致する商品コード
②.商品マスタになく、売上ファイルだけに存在する商品コード、
③.売上ファイルになく、商品マスタだけに存在する商品コード
をリストアップする場合です。

Excelであれば、2つのシートに商品マスタと売上ファイルを格納し、[VLOOKUP]関数をそれぞれの表に入力して見つけることでしょう。

データベースを操作するSQLであれば①②③とも、それぞれ数行のコマンドで抽出できるでしょう。

一部重複する部分もありますが、以下の記事に目を通して頂けますと理解しやいと思います。

バッチ処理プログラムのパターン 1:1 マッチング
こんにちは! 健史(たけふみ)です。 Excelシート上のデータやテキストファイルなどを先頭から順次読み込み処理するバッチ処理のロジックには基本パターンがあります。 順次読み込み処理するロジックは、プログラミングのベースとなるも...


2パターンのマッチング処理を紹介致します。

IF文でのマッチング

プログラムを作成するときに[IF文]でキーを比較するパターンです。

フローチャートでは横広がりになります。

フローチャート

右の[KEY1 = KEY2]のときに行う処理を
・[等しいファイルに出力]と[売上ファイル読み込み]を条件[KEY1 ≠ KEY2 になるまで]繰り返し
・[商品マスタ読み込み]の判定文を無くす
にしても同じですが、「1件づつを読み込み処理する」にするフローにしました。

ExcelVBAのプログラム

'---------- メイン処理 ----------
Sub matching1test()
'変数を定義
    Dim ix1, ix2, ix3, ix4, ix5 As Long
    Dim key1, key2 As String
'ファイルオープン
    ix1 = 0
    ix2 = 0
    ixe = 0
    ixl = 0
    ixg = 0
    Sheets(1).Columns(4).Clear
    Sheets(1).Columns(5).Clear
    Sheets(1).Columns(6).Clear
    Sheets(1).Columns(7).Clear
    Sheets(1).Columns(8).Clear
'旧商品ファイル、新商品ファイルの初期読み込み
    Call read1_sub(ix1, key1)
    Call read2_sub(ix2, key2)
'旧商品ファイル、新商品ファイルともに終了するまで繰り返す
    Do Until key1 = String(1, Hex(255)) And key2 = String(1, Hex(255))
        If key1 = key2 Then
            Call equal_sub(ix1, ix2, ixe, ixl, ixe)
            Call read2_sub(ix2, key2)
            If key1 <> key2 Then
                Call read1_sub(ix1, key1)
            End If
        Else
            If key1 < key2 Then
                Call less_sub(ix1, ix2, ixe, ixl, ixg)
                Call read1_sub(ix1, key1)
            Else
                Call great_sub(ix1, ix2, ixe, ixl, ixg)
                Call read2_sub(ix2, key2)
            End If
        End If
    Loop
End Sub

'---------- サブルーチン ----------

Sub read1_sub(ix1, key1)
    ix1 = ix1 + 1
    If Sheets(1).Cells(ix1, 1) = "" Then
        key1 = String(1, Hex(255))
    Else
        key1 = Sheets(1).Cells(ix1, 1)
    End If
End Sub

Sub read2_sub(ix2, key2)
    ix2 = ix2 + 1
    If Sheets(1).Cells(ix2, 2) = "" Then
        key2 = String(1, Hex(255))
    Else
        key2 = Sheets(1).Cells(ix2, 2)
    End If
End Sub

Sub equal_sub(ix1, ix2, ixe, ixl, ixg)
    ixe = ixe + 1
    Sheets(1).Cells(ixe, 4) = Sheets(1).Cells(ix2, 2)
    Sheets(1).Cells(ixe, 5) = Sheets(1).Cells(ix2, 3)
End Sub

Sub less_sub(ix1, ix2, ixe, ixl, ixg)
    ixl = ixl + 1
    Sheets(1).Cells(ixl, 6) = Sheets(1).Cells(ix1, 1)
End Sub

Sub great_sub(ix1, ix2, ixe, ixl, ixg)
    ixg = ixg + 1
    Sheets(1).Cells(ixg, 7) = Sheets(1).Cells(ix2, 2)
    Sheets(1).Cells(ixg, 8) = Sheets(1).Cells(ix2, 3)
End Sub

作成したデータは以下の通りです。

・処理前

・処理後

見やすくするために、すべてのファイルをSheets(1)で作成・処理しており、商品コードとしています。売上ファイルは処理前後がわかりやすいように"A",”B”など1項目を設けています。

説明を分かりやすくするために処理後の説明をsheets(2)に作成していますが、実行結果はsheets(1)に作成されます。

入力の
・[商品マスタ]はA列で、読み込みは変数ix1を1ずつアップ
・[売上ファイル]はB-C列で、読み込みは変数ix2を1ずつアップ

出力の
・①はD-E列で、書き込みは変数ixe(equalの意)を1ずつアップ
・②はF列で、書き込みは変数ixl(lessの意)を1ずつアップ
・③はG-H列で、書き込みは変数gx5(greatの意)を1ずつアップ

出力ファイルは、最初にクリアした状態にする必要があるため、[Sheets(1).Columns(n).Clear]でクリアしています。

既にオープンしているExcelシートを仮のファイルに見立てて処理していることから、
・読み書きするために必要な添え字[ix1]~[ixg]に初期値設定すること、及び、書き込むためのシートクリアをファイルオープン
・処理結果をそのまま画面に表示しおくため、ファイルのクローズ処理はない
です。

ループ処理でのマッチング

プログラムを作成するときにループで
・KEY1とKEY2が等しい間の処理を行い、等しく無くなったら次へ
・KEY1がKEY2より小さい間の処理を行い、小さく無くなったら次へ
・KEY1がKEY2より大きい間の処理を行い、大きく無くなったら先頭に戻る
で処理するパターンです。

フローチャートは縦長です。

フローチャート

以下のフローチャートは同じものです。

前者をスマホで閲覧すると鮮明ですが、パソコンでは文字が小さくなってしまうため後者を作成しました。

ExcelVBAのプログラム

'---------- メイン処理 ----------
Sub matching2()
'変数を定義
    Dim ix1, ix2, ixe, ixl, ixg As Long
    Dim key1, key2 As String
'ファイルオープン
    ix1 = 0
    ix2 = 0
    ixe = 0
    ixl = 0
    ixg = 0
    Sheets(1).Columns(4).Clear
    Sheets(1).Columns(5).Clear
    Sheets(1).Columns(6).Clear
    Sheets(1).Columns(7).Clear
    Sheets(1).Columns(8).Clear
'旧商品ファイル、新商品ファイルの初期読み込み
    Call read1_sub(ix1, key1)
    Call read2_sub(ix2, key2)
'旧商品ファイル、新商品ファイルともに終了するまで繰り返す
    Do Until key1 = String(1, Hex(255)) And key2 = String(1, Hex(255))
'KEY1 = KEY2の間 繰り返す
        Do Until key1 <> key2 Or key1 = String(1, Hex(255))
            Do Until key1 <> key2
                Call equal_sub(ix1, ix2, ixe, ixl, ixg)
                Call read2_sub(ix2, key2)
            Loop
            Call read1_sub(ix1, key1)
        Loop
'KEY1 < KEY2の間 繰り返す
        Do Until Not (key1 < key2)  '⇔ Until key1 >= key2 [≧]
            Call less_sub(ix1, ix2, ixe, ixl, ixg)
            Call read1_sub(ix1, key1)
        Loop
'KEY1 > KEY2の間 繰り返す
        Do Until Not (key1 > key2)  '⇔ Until key1 <= key2 [≦]
            Call great_sub(ix1, ix2, ixe, ixl, ixg)
            Call read2_sub(ix2, key2)
        Loop
    Loop
End Sub

'---------- サブルーチン ----------

Sub read1_sub(ix1, key1)
    ix1 = ix1 + 1
    If Sheets(1).Cells(ix1, 1) = "" Then
        key1 = String(1, Hex(255))
    Else
        key1 = Sheets(1).Cells(ix1, 1)
    End If
End Sub

Sub read2_sub(ix2, key2)
    ix2 = ix2 + 1
    If Sheets(1).Cells(ix2, 2) = "" Then
        key2 = String(1, Hex(255))
    Else
        key2 = Sheets(1).Cells(ix2, 2)
    End If
End Sub

Sub equal_sub(ix1, ix2, ixe, ixl, ixg)
    ixe = ixe + 1
    Sheets(1).Cells(ixe, 4) = Sheets(1).Cells(ix2, 2)
    Sheets(1).Cells(ixe, 5) = Sheets(1).Cells(ix2, 3)

End Sub

Sub less_sub(ix1, ix2, ixe, ixl, ixg)
    ixl = ixl + 1
    Sheets(1).Cells(ixl, 6) = Sheets(1).Cells(ix1, 1)
End Sub

Sub great_sub(ix1, ix2, ixe, ixl, ixg)
    ixg = ixg + 1
    Sheets(1).Cells(ixg, 7) = Sheets(1).Cells(ix2, 2)
    Sheets(1).Cells(ixg, 8) = Sheets(1).Cells(ix2, 3)
End Sub

最後に

長くなりましたが最後まで目を通して頂き、ありがとうございました!

上記プログラムをコピー&ペーストして試し、さらに応用し業務効率化の一助になればと思います。

参考記事:

Excel-VBA 実例 この通りやれば動く、とにかく触ってみよう
こんにちは! 健史(たけふみ)です。 パソコンをお使いの方の多くは、表計算ソフトExcelを利用されていると思います。 この記事は、Exceは知っているけれど ・Excel-VBAって何? ・Excel-VBAは取っ付きにく...
バッチ処理プログラムのパターン コントロールブレイク
こんにちは! 健史(たけふみ)です。 Excelシート上のデータやテキストファイルなどを先頭から順次読み込み処理するバッチ処理のロジックには基本パターンがあります。 順次読み込み処理するロジックは、プログラミングのベースとなるも...

コメント