ノン・プログラマー、仕事のためのマクロ・VBA(8)　面倒なデータチェックは、VBAの得意領域

ノン・プログラマー、仕事のためのマクロ・VBAシリーズ

2022年7月4日2024年6月8日

問題です。

以下の<図1>と<図2>の相違点はどこでしょう？

ノン・プログラマー、仕事のためのマクロ・VBA(8)　面倒なデータチェックは、VBAの得意領域 — <図1>

正解は…?

セルC9に入力されている金額に、1円誤差がありました!!

これ、単なる「重箱の隅つつきゲーム」のようですが、実務でも、似たような突き合わせが必要になることはありませんか?

「会計資料の金額が数円合わないせいで、お家に帰れない〜」的な悲鳴を、Twitterなどでもよく聞きます。上記のサンプルでは、わずかレコード20数件での突き合わせですが、それでも、まちがいさがしは至難のワザです。

まして、実際には数百件、数千件のレコードを検査することがあり、すべてを目視で正誤判定することは、【無理!】と断言してよいでしょう。

そこで、Excel関数(データベース関数)のVLOOKUPなどを使って突合しようする人もいます。しかし、同一シート/ブック内にExcel関数を多用しすぎると、データ容量が大きくなり、メモリーもかなり占有されてしまいますよね。

ただでさえお家に帰れない残業中に、重たいパソコンのグルグル砂時計をながめる切なさといったら….

そこで、このような作業が必要だと予想される場合には、VBAで突合プログラムを組んでおくことをオススメします。

(あくまでも、平時にあらかじめ用意しておく方がよいですよ。問題が起きてからプログラムを組みはじめたら、「お家に帰れない」どころか、「会社に住み込む」ことになりかねません。

今回は、一番シンプルな2シート突合のVBAプログラムを組みました。使用したのは、LBound関数とUBound関数です。

別々のシートにある、行列数がまったく同じ表の正誤判定をするプログラム

Sub 複数シートのデータ正誤判定プログラム()
 
'変数定義(比較対象の配列名;Hikaku(シートws1)比較元の配列名;moto(シートws2))
'比較対象2配列の比較範囲を定義する変数;Hani
 
Dim Hikaku As Variant
Dim moto As Variant
Dim ws1 As String
Dim ws2 As String
Dim Hani As String
    
'iからkまではループのカウンタ

Dim i As Integer
Dim j As Integer
Dim k As Integer

ws1 = "check"
ws2 = "moto"
    
Hani = "A2:C23"  '比較対象と、比較元の行列数は合わせます
k = 2    '各配列には表見出し行があるので、2行目以降を比較対象にします

Hikaku = Sheets(ws1).Range(Hani)
moto = Sheets(ws2).Range(Hani)



For i = LBound(Hikaku, 1) To UBound(Hikaku, 1)
For j = LBound(Hikaku, 2) To UBound(Hikaku, 2)
'UBound関数/LBound関数を利用して、 
'配列のインデックス（要素数）最大値、最小値を取得します

If Not Hikaku(i, j) = moto(i, j) Then
Sheets(ws1).Cells(k + i - 1, j).Interior.Color = vbYellow
'比較対象と比較元2シートの同一セル番地にあるデータが
‘一致しない場合のみ、セル色を黄色く塗ります
            	End If
        	Next
    Next
End Sub

配列のインデックス番号最小値/最大値を返すLBound/UBound

上記のプログラムで行なっていることを、ごく簡単に図示するとこのようになります。

ここで、2シートのセルA2からC23までを、順々に比較している構文が、以下の部分です。

For i = LBound(Hikaku, 1) To UBound(Hikaku, 1)
　For j = LBound(Hikaku, 2) To UBound(Hikaku, 2)
　　If Not Hikaku(i, j) = moto(i, j) Then
　　　Sheets(ws1).Cells(k + i - 1, j).Interior.Color = vbYellow
    End If
  Next
Next

LBound/UBoundが配列サイズをはかる関数で、これをFor NEXTステートメントと合わせて利用することで、最小値Lower(LBound)から最大値Upper(UBound)までの列行を、1つずつ検査していくプログラムとなるのです。

今回は、固定長(3列23行)のデータを比較する記述としていますが、データが膨大になる見込みであれば、事前に終端セルを見つけ出すEnd(xlUP)を使って、レコード数をカウントしておく必要があります。

いずれにせよ、注意することは、参照元の表とチェック用データとが、同じサイズ(列行数)である必要があるということです。

まとめ

まちがいさがしはあなたの仕事ではありません。
LBound/UBoundを活用して、VBAを働かせましょう。

VBAで配列を操作する関数には、LBound/UBoundのほかに、Array、Splitなどもありますが、いずれも、使いこなすと非常に便利な機能です。これらの構文をうまく活用して、自動化をどんどん進めていきましょう。

とくに、数字だけが並んだ表から差分を見つけ出す作業は、人力ではムダや限界があります。Excel関数(データベース関数)を使って解決できる場合もありますが、差分の単純比較はVBAがもっとも得意とする分野の一つですから、スキマ時間をみつけて、よく使う業務用書類の検算プログラムは、ぜひご自身であらかじめ作ってみましょう。

これで、あなたの「うわぁ、計算合わなくてお家帰れない〜」が、1日でもすくなくなることを祈ります!