【ExcelVBA】シートにある表を比較して相違しているセルを特定するには

この記事では、シートにある表を比較して相違しているセルを特定する方法についてご説明します。

【動画】シートにある表を比較して相違しているセルを特定する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

比較元の表のセルと比較先の表のセルを比較し、比較結果を出力するシートに比較元の表と比較先の表を出力させ、相違しているセルに色を付けています。

相違がある場合は、比較元の表のセルに黄緑、比較先の表のセルに黄色で色付けしています。

前提

今回のマクロには以下の前提があります。

表の構成は同じであること

比較する表の構造は同じであることが前提です。

表の列数や行数は同じでないと正しく比較できないので、表の列数や行数は同じ場合に正しい比較結果が取得できます。

表の外にある値は比較できない

表の外にある値は比較できません。

例えば表が3列だとして、5列目のセルに値を入力してもマクロは拾わずスルーしてしまいます。

あくまで表の中の値を比較するものだと認識して頂けたら幸いです。(今回のテーマがあくまで表の値を比較する、なのですみません)

マクロ作成の流れ

Excelファイルの例

今回は次のExcelファイルを作成しました。

以上に比較元と比較先のシート名をそれぞれ入力します。

また、今回のサンプルでは比較元のシートには「2022年」、比較先のシートには「2023年」と名前を付けています。

以上の比較元と比較先の表の相違を「比較結果」というシートに出力します。

以下は比較した結果が表示されたシートの状態です。

コードの例

Option Explicit

Sub test()
    
    Dim ws1         As Worksheet        'ワークシート用変数(データの比較元)
    Dim ws2         As Worksheet        'ワークシート用変数(データの比較先)
    Dim ws3         As Worksheet        'ワークシート用変数(比較結果表示用)
    Dim lr1         As Long             '比較元のシートのデータがある最終行
    Dim lr2         As Long             '比較先のシートのデータがある最終行
    Dim lc1         As Long             '比較元のシートのデータがある最終列
    Dim lc2         As Long             '比較先のシートのデータがある最終列
    Dim rowPos      As Long             '行位置
    Dim colPos      As Long             '列位置

        
    '比較元のワークシートを読み込む
    Set ws1 = Worksheets(Worksheets("top").Range("compFrom").Value)
    
    '比較先のワークシートを読み込む
    Set ws2 = Worksheets(Worksheets("top").Range("compTo").Value)
    
    '比較した結果を出力するシートを読み込む
    Set ws3 = Worksheets("比較結果")
    
    '比較元のシートのデータがある最終行を取得する
    lr1 = ws1.Cells(Rows.count, 1).End(xlUp).row
    
    '比較元のシートのデータがある最終列を取得する
    lc1 = ws1.Cells(1, columns.count).End(xlToLeft).Column
    
    '比較先のシートのデータがある最終行を取得する
    lr2 = ws2.Cells(Rows.count, 1).End(xlUp).row
        
    '比較先のシートのデータがある最終列を取得する
    lc2 = ws2.Cells(1, columns.count).End(xlToLeft).Column
    
    '比較結果を貼り付けるシートをクリアする
    ws3.Cells.Clear
    
    For colPos = 1 To lc1
    
        '比較結果を貼り付けるシートに、比較元のデータのヘッダーをコピーする
        ws3.Cells(1, colPos) = ws1.Cells(1, colPos)
        
        '比較結果を貼り付けるシートに、比較先のデータのヘッダーをコピーする
        ws3.Cells(1, colPos + lc1 + 1) = ws2.Cells(1, colPos)
        
    Next colPos
    
    ' Sheet3にSheet1とSheet2のデータをコピーし、差分をハイライトする
    For rowPos = 2 To lr1
    
        For colPos = 1 To lc1
            
            '比較結果を貼り付けるシートに、比較元のセルのデータをコピーする
            ws3.Cells(rowPos, colPos) = ws1.Cells(rowPos, colPos)
            
            '比較結果を貼り付けるシートに、比較先のセルのデータをコピーする
            ws3.Cells(rowPos, colPos + lc1 + 1) = ws2.Cells(rowPos, colPos)
            
            If ws1.Cells(rowPos, colPos) <> ws2.Cells(rowPos, colPos) Then
            
                '比較元と比較先の値が異なる場合
                
                '値が異なるセルに塗りつぶし色を設定する(今回は黄緑に色付け)(比較元)
                ws3.Cells(rowPos, colPos).Interior.ColorIndex = 4
                
                '値が異なるセルに塗りつぶし色を設定する(今回は黄色に色付け)(比較先)
                ws3.Cells(rowPos, colPos + lc1 + 1).Interior.ColorIndex = 6
                                
            End If
            
        Next colPos
        
    Next rowPos

End Sub

注目すべきコード①

最初に見て頂きたいのは17行目から23行目です。

    '比較元のワークシートを読み込む
    Set ws1 = Worksheets(Worksheets("top").Range("compFrom").Value)
    
    '比較先のワークシートを読み込む
    Set ws2 = Worksheets(Worksheets("top").Range("compTo").Value)
    
    '比較した結果を出力するシートを読み込む
    Set ws3 = Worksheets("比較結果")

コードの説明

以上のコードは、比較元、比較先、そして比較結果のワークシートを取得する処理のコードです。

ws1が比較元、ws2が比較先、wsが比較結果を出力するシートで、それぞれの変数で各シートのプロパティやメソッドをマクロが扱うことができるようになります。

注目すべきコード②

次に見て頂きたいのは26行目から35行目です。

    '比較元のシートのデータがある最終行を取得する
    lr1 = ws1.Cells(Rows.count, 1).End(xlUp).row
    
    '比較元のシートのデータがある最終列を取得する
    lc1 = ws1.Cells(1, columns.count).End(xlToLeft).Column
    
    '比較先のシートのデータがある最終行を取得する
    lr2 = ws2.Cells(Rows.count, 1).End(xlUp).row
        
    '比較先のシートのデータがある最終列を取得する
    lc2 = ws2.Cells(1, columns.count).End(xlToLeft).Column

コードの説明

以上のコードは、比較元の表と比較先の表それぞれの、最終行と最終列の位置を取得するコードです。

最終行と最終列の位置を取得することで表の範囲を特定することができ、比較結果を出力するシートに比較元の表と比較先の表を出力することができます。

コードの詳細

26行目では、比較元の最終行を取得しています。

29行目では、比較元の最終列を取得しています。

32行目では、比較先の最終行を取得しています。

35行目では、比較先の最終列を取得しています。

注目すべきコード③

次に見て頂きたいのは40行目から48行目です。

    For colPos = 1 To lc1
    
        '比較結果を貼り付けるシートに、比較元のデータのヘッダーをコピーする
        ws3.Cells(1, colPos) = ws1.Cells(1, colPos)
        
        '比較結果を貼り付けるシートに、比較先のデータのヘッダーをコピーする
        ws3.Cells(1, colPos + lc1 + 1) = ws2.Cells(1, colPos)
        
    Next colPos

コードの説明

以上のコードは、比較結果を出力するシートに比較元と比較先のヘッダを出力するコードです。

注目すべきコード④

次に見て頂きたいのは51行目から53行目です。

    For rowPos = 2 To lr1
    
        For colPos = 1 To lc1

以上のコードは、行数分のループと列数分のループを行うためのFor文です。

このループ内で比較元と比較先の表を、比較結果を出力するシートに出力し、値が相違している値のセルに色付けも行います。

注目すべきコード⑤

次に見て頂きたいのは56行目から59行目です。

            '比較結果を貼り付けるシートに、比較元のセルのデータをコピーする
            ws3.Cells(rowPos, colPos) = ws1.Cells(rowPos, colPos)
            
            '比較結果を貼り付けるシートに、比較先のセルのデータをコピーする
            ws3.Cells(rowPos, colPos + lc1 + 1) = ws2.Cells(rowPos, colPos)

コードの説明

以上のコードは、比較元と比較先の表のセル値を、比較結果を出力するシートに出力する処理のコードです。

注目すべきコード⑥

次に見て頂きたいのは61行目から71行目です。

            If ws2.Cells(rowPos, colPos) <> ws1.Cells(rowPos, colPos) Then
            
                '比較元と比較先の値が異なる場合
                
                '値が異なるセルに塗りつぶし色を設定する(今回は黄緑に色付け)(比較元)
                ws3.Cells(rowPos, colPos).Interior.ColorIndex = 4
                
                '値が異なるセルに塗りつぶし色を設定する(今回は黄色に色付け)(比較先)
                ws3.Cells(rowPos, colPos + lc1 + 1).Interior.ColorIndex = 6
                                
            End If

コードの説明

以上のコードは、比較元と比較先の表のセル値が相違しているか判定し、相違していればセルに黄緑(比較元のセル)と黄色(比較先のセル)に色付けする処理のコードです。

コードの詳細

61行目のコードでは比較元と比較先の表のセル値が相違しているか判定するIF文で、相違していれば66行目で比較元のセルに黄緑で色付けします。

また、69行目で比較先のセルに黄色で色付けします。

動作確認

マクロ実行前

以下のシートの表に相違しているセルがあるか特定します。

マクロ実行後

マクロを実行すると、以下のように比較した結果が表示されます。

比較元の表に相違があるセルには黄緑色で、比較先では黄色で色付けしています。

最後に

この記事では、シートにある表を比較して相違しているセルを特定する方法についてご説明しました。

いくつかのシートの表の値に相違がある個所を洗い出したい時には本記事を参考にしてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら