日ごろのエクセル作業で
2つの表の違いを見つけたい場面あると思います！
って諦めてませんか！？
でもそのままにしておくと
ミスが多発！残業が減らない！
という原因になりかねません。
そこで今回PowerQueryを使って
この整合性チェックを自動化してみました！
プログラミング未経験の方には難しいかもしれませんが
習得すればこんな事ができるんだ！という意味で
チェックしてみて下さい！
手動で整合性チェックを行う場合
5000行のデータがあるとします！
一方は正解データ、もう一方は5つの間違いがあるデータ！
あなたはどうやって間違いを見つけますか！？
まず手動でどう対応するのか考えてみましょう！
どの行に間違いがあるか？
まずは行を炙り出しましょう！
正解データと
間違い含むデータを縦に連結させます！
縦につながったデータ全体に対して重複削除する！
そうすると一番下に、重複削除されなかった5行が出てきました！
全て同じ内容のデータなら5000行しか残らないはず！
この消えなかった5行が間違いを含む行だと炙り出せました！
さらにどの列に間違いがあるのか？
それでは5つに絞った5行について
それぞれどの列が間違いなのかを見つけ出します！
例えば「タイトル」列について！
VLOOKUP関数を使って正解のデータを隣のセルに持ってきます！
あとは持ってきた正解データと
隣のセルを「=」で比較して
結果が「TRUE」なら、データは正しい。
結果が「FALSE」なら、データは誤り。
画像でいうと黄色セルのタイトルが誤っているという事になります！
かじむー
PowerQueryで自動化した場合
今回４つの関数を自作しました！
①function_recheck
②get_zip_list
③get_mis_list
④get_mis_rows
①がメイン関数で
②③④を中で呼び出して出来上がってます！
自動で整合性チェックした結果
まずはどのような結果になったか見てみましょう！
「データ」→「データの取得」→「ファイルから」→「ブックから」
ここから２つのExcelファイルに接続する。
接続した２つのテーブルを選択して実行すると…
こんな感じで自動で間違いを見つけ出します！
自作した関数の中身
①前処理としてテーブルを圧縮してリスト化してます！
ここで自作関数の「get_zip_list」を呼び出してます！
1行ずつリストに化した感じですね！
この後の作業がしやすくなります！
②存在チェック
ここは手動で行った重複削除の処理に当たります！
ポイントは
・For文にあたるList.TransformMany関数
・自作関数「get_mis_list」の呼び出し
List.TransformMany関数についてはコチラを参考にしました！
get_mis_list関数の中身でもList.TransformMany関数を使ってるので
2重ループになってます！
やっていることは
[1, 2, 3, 4, 5] というリスト(データ1行分)が
[5, 4, 3, 2, 1], [1, 3, 5, 7, 9], [2, 4, 6, 8, 10], … リスト全体(全行)に
存在するかどうかチェックしています！
③不一致行
ここは手動でやった、重複削除されなかった行を炙り出す処理に当たります！
②で存在しなかったデータには「”mis”」と付けているので
“mis” となっている行を抽出してます！
④差異の抽出
ここは手動でやった、VLOOKUPの処理に当たります！
間違いが1つもなければ空のテーブルを返し
間違いがあれば自作関数「get_mis_rows」を呼び出します！
この get_mis_rows関数 が VLOOKUPの処理になりますね！
VLOOKUP関数の検索値となる部分は
あらかじめシートの設定しておき
get_mis_rows関数内で取得しています！
テーブルを圧縮してリスト化！
VLOOKUPの検索値に当たる部分は
あらかじめ下記のように「list_key」と名付けたテーブルに
セットしておきます！今回は「No」列をセット！
そして「// キー列名の取得」で取得しています！
一度効率化に時間を割いてみよう！
と思ってませんか！？
その積み重ねが
残業になり
自由な時間が減り
人生あっという間に！
果たしてこれは大げさでしょうか…？(;・∀・)
1度思いっきり自動化に時間を割いてみましょう！
今大変でも、その後の作業が楽になります！！！
皆さんも是非！作業の自動化にトライしてみて下さい！
かじむー
