2つのExcelブックの内容を比較する

Excelで間違い探し。

Excelブック(Aとします)で表が出来ていて、その表に適時に必要事項を入力して更新していくという作業があるとします。一定期間、たとえば月ごとで報告書なりの形式で出力し、Excelブックをそこで別名保存(Bとします)して、そのブックは更新を終了します。元のブック(A)は引き続き更新処理が継続されます。

このようにマスターとなるAブックから、一定期間でBブックを切り出して保存していく、という世代管理的手法は結構おこなわれているのではないかと思います。

また、前世代のBをコピーして変更したところだけ書き換えてB’として保存。これを繰り返していく方法も、実務あるあるではないでしょうか。

ところで、あるBと、その1つ前の世代のBでどこが変更されたのか知りたいならば、あなたはどうしますか?

目視は論外として、ワークシート関数やフィルターを使った方法をいくつか思いつきますが、毎回違うファイルが対象になるとしたら、手軽さや取り回しの良さは、それらの方法では期待できないことは想像に難くないでしょう。

ここはプログラムにやらせましょう。

VBAでブックの内容を比較する

やることは極めて単純で、対象のブックを両方開いて双方の同じセルアドレスの値をひたすら比較していくだけです。

サンプルとしてシートは1枚でA1セルから始まる10×1000の表を作成しました。値はわかりやすいように全部1にしておきます。

comparison1.png

このブックをコピーして片方の値を一部書き換えます。

comparison2.png

ロジックは単純明快なので早速コーディングします。処理するファイルとは切り離しておきたいので専用ブックを作成してそこへプロシージャを記述します。

Sub diff()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Set wb1 = Workbooks.Open("C:\Users\ore\Desktop\1.xlsx")
    Set wb2 = Workbooks.Open("C:\Users\ore\Desktop\1111.xlsx")
    
    Dim r1 As Range
    Set r1 = wb1.Worksheets(1).Range("a1").CurrentRegion

    Dim r2 As Range
    Set r2 = wb2.Worksheets(1).Range("a1").CurrentRegion

    Dim row As Long
    Dim col As Long

    For row = 1 To r1.Rows.Count
        For col = 1 To r1.Columns.Count
            If Not r1(row, col).Value = r2(row, col).Value Then
                Debug.Print r1(row, col).Address
            End If
        Next
    Next
    
    wb1.Close
    wb2.Close
End Sub

実行結果は

comparison3.png

ファイルパスを決め打ってますが、ここをセル値から引っ張ってくるようにしたり、IfがTrueのときの処理をワークシートから好きなように設定できるようにしたりすれば実用になるかと思います。

目的は達成しましたが、ここで終わってしまうわけにはいきません。このサイトのコンセプトExcel×Pythonを実践するまでは!

Pythonでブックの内容を比較する

同じ処理をPythonで書いてみます。ExcelファイルはOpenPyXLで扱います。

import openpyxl as excel

wb1 = excel.load_workbook('C:\\Users\\ore\\Desktop\\1.xlsx')
wb2 = excel.load_workbook('C:\\Users\\ore\\Desktop\\1111.xlsx')
ws1 = wb1.active
ws2 = wb2.active

for row in ws1:
    for cell in row:
        if ws1[cell.coordinate].value != ws2[cell.coordinate].value:
            print(cell.coordinate)

ものっすごいシンプルなコードです。神ツールOpenPyXLがよしなにやってくれていることや、Pythonの記法によるところも大きいですが、VBAは変数の宣言にかなりの文字数を費やさなければならないことがおわかりでしょう。

実行結果は

comparison4.png

VBAでも変数宣言を省略できますが、絶対にやめましょう。理由はさんざんネット上に書かれていますのでググってくださいね。Pythonはそもそも宣言という概念がなく型の記述もありません。

素でメモ帳などにコーディングしていくとしたら、Pythonのこの仕様は案外キツいと思います。VBAで変数宣言をはしょるのがダメな理由が概ね当てはまります。しかし、この弱点はIDEが補完してくれます。IDEとPythonが組み合わさると、最強のシンプルコーディングが実現します。

VBAもエディタがもう少しまともに構文解析をしてくれるようになるか、IDEでコーディングできるようになれば変数宣言はする必要がなくなるでしょう。IDEについては別記事がありますのでぜひご覧ください。

VBAは遅いのか?~速度比較~

ここまでできると誰でも思うことがあります。「これどっちが速いの?」

VBAは他言語より遅いというイメージが世間一般には広まっている気がします。はたして本当でしょうか?比べてみましょう。

ネットで拾ってきたタイム計測コードを前述のコードに組み込み実行します。コードの先頭で計測開始、すべての処理が終わって計測終了します。

使用するPCのスペックは次の通りです。

comparison6.png

ストレージはSSDです。数世代前のCPUとはいえ、事務所のPCといえばいまだにCeleronあたりが幅をきかせている有様なので、それよりはずっと速い環境なのはご承知置きください。

まずはVBA

comparison5.png

0.59秒。

次にPython

comparison7.png

0.25秒。

ええ、もう誤差の範囲です。どちらも待ち時間はないに等しいです。Pythonが圧勝かなと思っていましたが、意外にVBAが速かったです。

表は10×1000の大きさなので1万個のセルを総当たりしたことになります。これをもっと増やします。

10,000行まで拡張して計10万個のセルとしました。最後のセルまで走査しているかの確証のためJ10000のセルを違う値にしておきます。

VBA

comparison8.png

1.28秒。

Python

comparison9.png

あれ?

もう一回

comparison10.png

2.75秒。

逆転されました。

実務で使う分にはまだまだ問題にはならない時間ですが、セルが多くなるとOpenPyXLの変換コストが高くなるということでしょうか。さすがに本家本元と同じようにはいかないようです。


ブック間で値が違うセルが大量にあったらどうなるか試してみます。

1~5000行目を違う値にしました。10×5000で5万回のログ生成&出力が加わります。

VBA

comparison11.png

12.45秒。あからさまに遅くなりました。イミディエイトウィンドウに逐次出力されていくので、そこがボトルネックになっている模様。

Python

comparison12.png

3.67秒。出力は最後にズドンなので、それほど速度に影響していないようです。

VBAの出力先をイミディエイトウィンドウではなくセルにしてみます。

Debug.Print r1(row, col).Address

ここを、値が違うセルがわかればいいのでセル背景色を赤にするコードへ書き換えて

r2(row, col).Interior.Color = 255

実行すると

comparison13.png

こうなり

comparison14.png

2.53秒でした。

イミディエイトウィンドウがやたら遅いということですね。

5万セルの書き換えでこの程度なら十分実用に耐えるのではないでしょうか。セルアドレスをログに取りたいならば、配列に入れておいて、最後にシートへ一発で書き出す方法で同程度の時間になるはずです。


ちなみにOpenPyXLでやるとすると

print(cell.coordinate)

これを

ws2[cell.coordinate].fill = PatternFill('solid', fgColor='ff0000')

こう書き換えて(関連モジュールのimportも必要)

comparison15.png

6.36秒でした。

やっぱりOpenPyXLのセルアクセスでコストがかかっていますね。

まとめ

餅は餅屋に。

注意する点として、OpenPyXLに限らずVBA以外でExcelを扱うモジュールはどれもそうですが(昔さわったPHPでも同様でした)書式の扱いは苦手なようで、「見た目」の再現性はかなりいい加減です。

ファイルを開いて何もタッチしないで保存しただけでも、凝ったレイアウト(いわゆる神エクセル)であればあるほど原型をとどめません。あくまで値を処理するためのものと割り切った方がいいです。


今回はファイル配布はありません。コードをコピペしてお使いください。

参考サイト

時間計測はそれぞれ次のサイトの方法を使用しました。

VBA

Python

related pages
Power AutomateがVBAを駆逐する
誰でもプログラマー時代がやってくる。

ついにMicrosoftがVBAを本気で滅しにきたようです。我々VBAerがお払い箱になる日も、そう遠い未来ではないでしょう。「AIに取って代わられなくなる職業、確率95%でプログラマー」がいよいよ現実味を帯びてきました。

Read More ...
VBAでファイルを開くのが遅すぎるのでPythonで無理矢理に高速化する
VBAとPythonを連携させてファイル読み込み高速化。

我々VBAerにとっては周知の事実ですが、Excel-VBAで時間がかかる処理と言えば、「ワークシートへのアクセス」と「ファイルを開く」です。Pythonでデータ取得してVBAでExcelへ取り込むことでファイルを開く処理を高速化します。

Read More ...
Excelに最適な画面解像度はどれか
Full HDを表示できないモニターは窓から投げ捨てろ!

結論。WQHD(2560×1440px)以上の解像度でFull HD相当まで拡大表示。 私は4kモニターをデュアルで175%拡大表示にして使っています。最強です(自慢)。

Read More ...
この記事の
作成日

2019-12-21

更新日

2019-12-22

ページ内検索
目次
WEB MASTER
さいた
神エクセル撲滅協会理事(自称)
さいたま市民 埼玉こそ地上の楽園