root 
問題です。
以下の<図1>と<図2>の相違点はどこでしょう？
正解は...?
セル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日でもすくなくなることを祈ります!
↓↓こんなツールはいかがでしょうか？
↓↓個人的にAmazonを日々ウォッチしてるんですが・・これお得でいいな！って感じた商品をご紹介！！
