「プログラミング素人でもできる ChatGPTとPythonで作業効率化ツールを作成」第2回です。
今回は作ろうとしたけどできなかったExcel同士の比較ツールについてご紹介します。
（「作ろうとしたけどできなかった」というのは、当初は「Excel同士を比較するツール」を作りたかったのですが、最終的にできたものは「比較のためのデータを作成するツール」だったからです）
Excel同士ならツールを作るのもそこまで難しくなさそうだし、それにわざわざツールを作らなくても関数とかVBAとかでなんとかなるのでは？と思った方もいらっしゃるかもしれません。
私も初めは同じ考えで、この“Excelである”ことが障壁になるとは夢にも思いませんでした。
Excelの比較もプログラムでやりたい
とある案件で先方から支給されたデータを弊社で連結・加工する、というものがあります。
この中で、弊社で連結・加工したデータと支給データを比べて漏れている箇所がないかをチェックする工程があります。
最初は目視でチェックしていましたが時間がかかるため、Excel対Excelの突き合わせなので自動化できるのでは？ということでまず他のスタッフがツールをひとつ作りました。
（“他のスタッフ”とは当ブログサイトでおなじみのD橋さんです）
そして、このD橋さんが作ったツールとは違うやり方でチェックするためのツールを私が作成することになりました。
扱うExcelデータは2種類です。
ひとつは先方支給のExcelデータ（各地域のデータ）。こちらは地域ごとでひとつのファイルになっています。内容はイベントについての情報（日時や開催場所など）です。
これが都道府県ごとにフォルダでまとまっており、さらに各都道府県がその月のイベント日程としてひとつのフォルダに格納されています。フォルダ構造は以下の通り。
もうひとつは弊社で加工したデータ（マスターデータ）。
先方支給のデータにスクリプトをかけて一つにまとめたものです（スクリプトは別の部署でかけています）。
D橋さんが作ったツールは各地域のデータを結合してマスターデータと比較します。
つまりスクリプトで行ったことをもう一度やり直して、マスターデータと一致するかをチェックする、というものです。
これに対し私は各地域のデータとマスターデータをそのまま比較する方法をとることにしました。
各地域のデータはファイルが分かれているけど、Excel同士であればそこまで難しくないのでは、というのが最初の考えでした。
Excel同士なら……と甘く見ていました
マスターデータと各地域のデータの見た目は以下のような感じです。
マスターデータ
各地域のデータ
マスターデータと各地域のデータでは年月日の表記が少し異なるので、マスターデータの表記を各地域のデータの表記に合わせた形に加工しました。（これはExcelの関数を使えばできます）
その後、以下の2点をChatGPTに伝えました。
- 各地域のデータの各列がマスターデータのどの列に対応しているかの一覧
- マスターデータと各地域のデータで一致する行同士には○、一致しない行同士には×が入るようなコードを作ってほしい
この条件をもとに出力されたコードを試しましたがうまくいきません。
まず一致しているはずの行同士（目視だと一致していると判定する行同士）に不一致であるという結果が出ました。
うまくいかないことをChatGPTに報告すると、データ型の違いや不要なスペースの混入が不一致の原因かもしれない、と言われました。
それらを排除する（データ型をそろえる、スペースを削除する）工程も含めたコードを再度出力してもらいましたがやはりうまくいきません。
このあとも何度かChatGPTに不具合報告→修正したコードの試行を繰り返しましたが、まったく改善されず……
そんななか、各行をひとまとまりのデータとして行ごとに比較する方法はどうか、と聞いたときに（私の中ではかなり）革新的な方法をChatGPTが教えてくれたのです。
それは「各行をハッシュ化して比較する」というものでした。
ハッシュ化に救われました
ハッシュ化というのは元の値をハッシュ値に置き換えることです。
ハッシュ値とはハッシュ関数によって得られる固定長の値で、同じ値からは必ず同じハッシュ値が得られるが、元の値が少しでも違うと全く異なるハッシュ値になる、というものです。
（参考はhttps://e-words.jp/w/%E3%83%8F%E3%83%83%E3%82%B7%E3%83%A5%E5%8C%96.html。私自身ふんわりとしか理解していませんが、ハッシュ値を元の値に戻すのは難しいのでパスワードの管理なんかに使われるらしいです）
同じ値からは必ず同じハッシュ値が得られるなら、もうデータ型の違いに悩まされることもないのでは？と思い、各行をハッシュ化して比較する方法にシフトチェンジしました。
しかし、そもそもデータ型が異なると、同じ内容に見える行でもハッシュ値が異なるということが判明しました。
Excelのままだとデータ型の違いが足を引っ張るので、それぞれのデータを一度csvにしてからハッシュ化することにしました。
csvに変換する方法はマスターデータと各地域のデータで同じにしてくれるようChatGPTにお願いしました。結果、csv化するコードはすんなりできました。
また、これをそれぞれハッシュ化するコードもすんなりできました。
（というより、Excelのまま比較するときのコード作成があまりにもうまくいかなかったので、相対的にすんなりできたように感じただけかもしれません）
csv、連結……のはずが
ハッシュ化まで済んだところで、各地域のデータのcsvが地域ごとでバラバラだと比較するのに不便なことに気が付きました。
さっそくChatGPTにcsvを連結するコードを書いてもらいました。
最初にただ連結するだけのコードを書いてもらったのですが、元のファイル名がないと不一致の箇所が出たときにどのファイルのものなのか分かりません。
このため連結するときに一番左の列に連結元のファイル名を入れることも一緒にお願いしました。ところが。
元のファイル名も入れる仕様にしたとたん、列の順番がメチャクチャになる箇所が出てきました。
何度修正したコードを出してもらっても改善されません。
どうやら最初のcsvを書き出すときに範囲を指定しなかったのがよくなかったようです。
（各地域のデータは各地域の担当者が記入する用のいわば「記入用シート」なので、1・2行目には比較データであるメインの表組には関係ない情報が載っています。この不要な行が入っていたことが悪さをしていたようです）
しぶしぶ最初に立ち戻り、各地域のデータのExcelをcsv化するところからやり直しました。
メインの表組が始まる3行目以降をcsv化する範囲に指定することで、列の順番問題は解決しました。
いよいよ比較
材料がそろったので、最後のクエスト「各地域のデータとマスターデータを比較して、各地域のデータの必要な行がマスターデータに入っているかチェックする」に挑みました。
ハッシュ値を比較して同じだったら「○」を各行に入れるコードをChatGPTにお願いしました。
これが一番簡単だろうと思っていましたが、今までの工程で一番難しかったようです。
各地域のデータにある、マスターデータには入っていないはずの空白行などにも「○」が入っていました。
コードの修正をお願いしても改善されません。
あるはずのないものに対して「ある」判定されているということは、「○」が入っている他の行にも実は一致していない行があるのでは……？と思い、比較にPythonを使うのはやめました。
Pythonが使えないので、最終的にはExcel上で関数を使って比較することにしました。
比較するcsvをExcelの「データの取得」から開き、ハッシュ値をCOUNTIF関数を使って比較しました。
実行の流れ
まず、前準備としてマスターデータの日付部分を年・月・日・曜日でそれぞれの列に分割します。
このマスターデータと各地域のデータにそれぞれ以下のプログラムを実行します。
マスターデータと各地域のデータでcsv化するコードを分けているのは、フォルダの構造が異なるからです。
マスターデータをcsv化するコード
各地域のデータをcsv化するコード
プログラムを実行すると、それぞれ以下のようになります。
マスターデータのほうは、Excelの左側にある比較に不要な列は含めずにcsv化しています。
このプログラムで作成したcsvは、Excelで開こうとすると（おそらく）文字コードの関係で文字化けしてしまうので、テキストエディタで開いています。
マスターデータのcsv
各地域のデータのcsv
次にこれらをそれぞれハッシュ化します。コードはこちら。
ハッシュ化するコードは、各地域のデータ、マスターデータとも同じものを使います。
ハッシュ化するコード
ハッシュ化したものが以下になります。右側のランダムな英数字の並びがハッシュ値です。
ハッシュ値の入ったマスターデータ
ハッシュ値の入った各地域のデータ
次に、ハッシュ化した各地域のデータを1つのデータにまとめます。コードはこちら。
複数のcsvをまとめるコード
まとめたものがこちら。左側1列目にそれぞれのファイル名が入ります。
1つにまとまった各地域のデータ
各工程での処理についてですが、最初のcsv化は各地域のデータは80ファイルほどで約3分かかります。
マスターデータは1ファイルのみで600行前後なので一瞬で終わります。
ハッシュ化以降は各地域のデータもマスターデータも処理は一瞬です。
まとめ
Excel同士で簡単に終わらせるつもりが、Excel→csv→Excelと行ったり来たりする方法になってしまいました。プログラムを使って比較することもかなわず……。
しかし比較のための補助ツールとしては有用です。
目視と併用で3回ほどテストし、目視でチェックしたときと同じ箇所を差異として見つけられました。
また、データの準備から比較までトータルで1時間ほどと、目視に比べると約3分の1の時間で作業できるようになりました。
テストの結果は問題なさそうなので今は実作業でも使用しています。
ChatGPTがコードを書いてくれるようになったことで、「この部分自動化できそう」や「これも作れるかも」が増えました。私自身、「効率化ツール」シリーズでご紹介した2つ以外にもいくつかツールを作っています。
みなさんもChatGPTで効率化ツール、作ってみてはいかがでしょうか。
