プログラミング

マッチング 1:1のロジックを徹底解説!VBAで確認

プログラミング

こんにちは! 健史です。

COBOLではよく使われるマッチング処理について、初めてプログラミングする場合、

・2つのキーを比較しながら処理はどうやって作成するの?

・ファイルの読み込みタイミングはどうすればいいの?

・どうやって作ればわかりやすいの?

・新入社員教育でやっているけど、難しくて理解できない、困っている

という方へ、1:1のマッチング処理を徹底解説します。

尚、プログラムはVBAで紹介・検証していますが、ロジックはCOBOLはもちろん他の言語でも使えます

構造化によるマッチングも紹介します。

これまで「IF文によるキー比較」で作成されてこられた方も、ご一読頂ければと思います。

スポンサーリンク

1:1 マッチングの説明

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

例えば、商品コード順にソートされた新商品コードファイルと旧商品コードファイルがあり商品コードで照合し、旧から新に更新されたデータで
・そのままの商品コード
・追加された商品コード
・削除された商品コード
をリストアップする場合です。

「1:1 マッチング」とは、商品コードのように「比較するそれぞれのファイルのキーに重複がなくユニーク」な照合処理のことです。

Excelであれば、2つのシートに新商品コードと旧商品コードを格納し、[VLOOKUP]関数をそれぞれの表に入力して見つけることでしょう。

データベースを操作するSQLであれば、「そのままの商品コード」「追加された商品コード」「削除された商品コード」用にそれぞれ数行のコマンドで抽出できるでしょう。

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

IF文でのマッチング

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

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

この書き方が良いとか悪いとか思っておりませんので、悪しからずです。

要は「どのパターンで標準化するか」、また「慣れ」の問題ですから、どのパターンでも品質や生産性は確保できます。

フローチャート

補足します。

終了したら最大値である16進数:255をセットする

コンピュータ上では、半角1文字は[0~255]までの256種類で格納・処理されます。

シフトJISというコードでは、'A'は[65]、16進数では[41]です。

コンピュータ上の半角1文字の最大値は[255]、16進数では[FF]です。

その最大値に割り当てられている文字はなく、コンピュータが処理するデータとしてExcelやオンライン画面から入ってこないのです。

そこでデータを読み終わった状態を示すために、コンピュータ上の最大値である[255]、16進数では[FF]をセットするのです。

それによりキーの大小を比較するのにも、そのまま使えます。

以下VBAのプログラムでは1文字だけ[255]、16進数では[FF]をセットし比較処理していますが、想定通りの動作となりました。

汎用コンピュータのプログラミング言語であるCOBOLでは、[HIGH-VALE]という定数をセットすると全ての桁に[255]、16進数では[FF]がセットされます。

キーをワークで定義した項目とする理由
マッチング処理では、複数の項目を繋ぎ合わせてマッチングキーにすることが殆どです。

例えば、自動車部門とバイク部門がある部品コードでのマッチングの場合は「部門・部品コード」がキーとなります。

その場合は、2項目を1項目にして比較する必要があります。

そのためワーク上にマッチングキーを定義して、読み込んだ時に複数の項目を1項目としています。

その読み込み処理もサブルーチン化して、メインの処理ではサブルーチンを呼び出すことで実行します。

メイン処理では、ワークで定義したキーを比較する記述すればよくプログラムも見やすくなります。(キーを比較する全箇所に1つひとつ全ての項目を記述する必要がない)

ExcelVBAのプログラム

'変数を定義
    Dim ix1, ix2, ix3, ix4, ix5 As Long
    Dim key1, key2 As String
'---------- メイン処理 ----------
Sub MAIN00()
'ファイルオープン
    ix1 = 0
    ix2 = 0
    ix3 = 0
    ix4 = 0
    ix5 = 0
    Sheets(1).Columns(3).Clear
    Sheets(1).Columns(4).Clear
    Sheets(1).Columns(5).Clear
'旧商品ファイル、新商品ファイルの初期読み込み
    Call SUB01_READ1
    Call SUB01_READ2
'旧商品ファイル、新商品ファイルともに終了するまで繰り返す
    Do Until key1 = String(1, Hex(255)) And key2 = String(1, Hex(255))
        If key1 = key2 Then
            Call SUB01_EQUAL
            Call SUB01_READ1
            Call SUB01_READ2
        Else
            If key1 < key2 Then
                Call SUB01_LESS
                Call SUB01_READ1
            Else
                Call SUB01_GREATER
                Call SUB01_READ2
            End If
        End If
    Loop
End Sub

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

Sub SUB01_READ1()
    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 SUB01_READ2()
    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 SUB01_EQUAL()
    ix3 = ix3 + 1
    Sheets(1).Cells(ix3, 3) = Sheets(1).Cells(ix1, 1)
End Sub

Sub SUB01_LESS()
    ix4 = ix4 + 1
    Sheets(1).Cells(ix4, 4) = Sheets(1).Cells(ix1, 1)
End Sub

Sub SUB01_GREATER()
    ix5 = ix5 + 1
    Sheets(1).Cells(ix5, 5) = Sheets(1).Cells(ix2, 2)
End Sub

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

・処理前

・処理後

見やすくするために、すべてのファイルをSheets(1)で処理しており、商品コードとしています。

入力の
・[旧商品ファイル]はA列で、読み込みは変数ix1を1ずつアップ
・[新商品ファイル]はB列で、読み込みは変数ix2を1ずつアップ

出力の
・[等しいファイル]はC列で、書き込みは変数ix3を1ずつアップ
・[小さいファイル]はD列で、書き込みは変数ix4を1ずつアップ
・[大きいファイル]はE列で、書き込みは変数ix5を1ずつアップ

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

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

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

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

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

この書き方も良いとか悪いとか思っておりませんので、悪しからずです。

フローチャート

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

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

ExcelVBAのプログラム

'変数を定義
    Dim ix1, ix2, ix3, ix4, ix5 As Long
    Dim key1, key2 As String
'---------- メイン処理 ----------
Sub matching2()
'ファイルオープン
    ix1 = 0
    ix2 = 0
    ix3 = 0
    ix4 = 0
    ix5 = 0
    Sheets(1).Columns(3).Clear
    Sheets(1).Columns(4).Clear
    Sheets(1).Columns(5).Clear
'旧商品ファイル、新商品ファイルの初期読み込み
    Call SUB01_READ1
    Call SUB01_READ2
'旧商品ファイル、新商品ファイルともに終了するまで繰り返す
    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))
            Call SUB01_EQUAL
            Call SUB01_READ1
            Call SUB01_READ2
        Loop
'KEY1 < KEY2の間 繰り返す
        Do Until Not (key1 < key2)  '⇔ Until key1 >= key2 [≧]
            Call SUB01_LESS
            Call SUB01_READ1
        Loop
'KEY1 > KEY2の間 繰り返す
        Do Until Not (key1 > key2)  '⇔ Until key1 <= key2 [≦]
            Call SUB01_GREATER
            Call SUB01_READ2
        Loop
    Loop
End Sub

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

Sub SUB01_READ1()
    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 SUB01_READ2()
    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 SUB01_EQUAL()
    ix3 = ix3 + 1
    Sheets(1).Cells(ix3, 3) = Sheets(1).Cells(ix1, 1)
End Sub

Sub SUB01_LESS()
    ix4 = ix4 + 1
    Sheets(1).Cells(ix4, 4) = Sheets(1).Cells(ix1, 1)
End Sub

Sub SUB01_GREATER()
    ix5 = ix5 + 1
    Sheets(1).Cells(ix5, 5) = Sheets(1).Cells(ix2, 2)
End Sub

最後に

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

「1:1 のマッチング処理を理解できました!」でしょうか。


COBOLにてIF文型で標準化されておられる組織・担当の方、今からでもUNTIL型にすることを検討し変更されてはいかがでしょうか。

構造化できてIF文型よりは見やすく、新人の方にも説明しやすく分かりやすいと思います。

説明するときの注意点は「UNTIL条件を満たしていれば、1回も処理されない」でしょうか。[WITH TEST AFTER]をつけない限りにおいては。




尚、PL/SQLでマッチング処理を作成しました。

PL/SQL マッチング処理 SQLとの比較も紹介
こんにちは! 健史です。 SQLでは、マッチング処理がとても簡単にできます! PL/SQLで同じ処理を作成してみました。 尚、入出力データはEXCELシートで説明していますが、オラクル上にロードしたデータをSqlDevel...

参考にして頂ければと思います。

コメント