複数のCSVやシートの突き合わせを自動化するときの考え方
月初、ネットショップの注文データをCSVで書き出します。次に入金明細を別のシステムから書き出します。さらに、倉庫から送られてきた出荷リストのExcelファイルを開きます。3つのファイルを並べて、注文番号を見ながら一行ずつ目で追っていく。金額が合わない行を見つけては付箋を貼り、担当者に確認のメールを送る。
数十行なら何とかなります。数百行になると、集中力が切れた頃に見落としが出ます。しかも来月も、再来月も、同じ作業が待っています。こうした突き合わせ作業は自動化の効果が大きい一方で、設計を誤ると「自動で出た結果が信用できない」状態に陥りがちです。手を動かす前に決めるべきことを整理します。
何をもって「同じもの」とするかを先に決める
突き合わせの自動化で最初にやるべきは、照合の鍵になる列を決めることです。これが曖昧なまま作業を始めると、あとから全部やり直しになります。
鍵に向いているのは、次の条件を満たす列です。
- ファイルをまたいで同じ値が入っている
- 重複しない、または重複するパターンが決まっている
- 桁数や形式が安定している
注文番号、伝票番号、会員IDなどが候補になります。氏名や会社名は鍵に向きません。表記がぶれるうえ、同姓同名や似た社名があるためです。
鍵になる単独の列がない場合は、複数の列を組み合わせて鍵を作ります。日付と取引先名と金額、のように3つを連結した文字列を作り、それを照合に使う方法です。ただしこの場合、どれか1つでもずれると別物と判定されるので、後述する表記ゆれの処理が重要になります。
表記ゆれの処理ルールを文章で書き出す
照合が合わない原因の大半は、データそのものの間違いではなく表記の違いです。自動化する前に、どう揃えるかを決めておきます。
- 前後の空白を削除するか
- 全角と半角をどちらに統一するか（英数字、カタカナ、記号それぞれ）
- 「株式会社」を削るか残すか、前株と後株をどう扱うか
- ハイフンや括弧を削除するか
- 日付を何形式に統一するか
- 金額の円マーク、カンマ、小数点以下をどう扱うか
- 大文字と小文字を区別するか
これを頭の中だけで決めず、箇条書きで書き出してください。書き出しておくと、後から結果がおかしいときに「どのルールが原因か」をたどれます。書いていないと、毎回ゼロから原因を探すことになります。
なお、揃える処理は元データを書き換えるのではなく、照合用の列を新しく作って行うのが安全です。元の値が残っていれば、判定を間違えたときに戻せます。
差分を3種類に分類する
突き合わせの結果は、「合った」「合わなかった」の2つではありません。少なくとも次の3つに分けて出力すると、確認作業が一気に楽になります。
- 両方にあって、値も一致している行。これは確認不要です。
- 両方にあるが、値が違う行。金額違い、数量違いなど。どの列がいくつ違うかまで出します。
- 片方にしかない行。一方のファイルにだけある行と、もう一方にだけある行は、それぞれ別のリストにします。原因も対応も違うためです。
最後のものをひとまとめにしてしまうと、確認する人が毎回「これはどっちのファイルの話か」を考えることになります。最初から分けて出すだけで、判断の速さが変わります。
出力の形は、元の行をそのまま残したうえで、右端に判定結果の列を足す形がおすすめです。判定だけを見せられても、確認する人は結局元データを探しに行くことになります。
手作業のまま残す部分を決める
すべてを自動化しようとすると、例外処理の実装が膨らみ、いつまでも完成しません。最初から「ここは人が見る」と決めておくほうが早く実用段階に届きます。
自動判定に向かないのは、次のようなケースです。
- 一対多、多対多で対応している行（1件の入金で複数の請求をまとめて支払っているなど）
- 金額のわずかな差が、振込手数料なのか単純な誤りなのか判断が必要な行
- 過去の経緯を知らないと判断できない行
こうした行は「要確認」として別リストに出し、人が処理します。全体の数パーセントであれば、それで十分実用的です。逆に要確認が半分を超えるなら、鍵の選び方か表記ゆれのルールを見直すサインです。
同じ手順を繰り返せる形にする
一度きりの照合ならその場で終わりますが、毎月やるなら再現できる形にしておく必要があります。
- 入力ファイルを置く決まったフォルダを作る
- ファイル名の付け方を決める（対象月が分かる形にする）
- 実行の手順を、順番どおりに箇条書きで残す
- 出力の保存先と、過去分の残し方を決める
作った本人以外でも回せるかどうかが基準です。手順書を見ながら別の人が一度通してみて、詰まった箇所を書き足す。この一往復をやっておくと、担当が変わっても止まりません。
表計算ソフトの関数で組むか、スクリプトで組むかは、件数と頻度で決めます。数百行で月1回なら関数の範囲で足りることが多く、件数が増えるか頻度が上がるならスクリプト化を検討する、という目安で構いません。
まとめ
突き合わせの自動化は、処理を書くことより前の設計で成否が決まります。鍵となる列を決め、表記の揃え方を文章に残し、差分を3種類に分けて出し、人が見る例外をあらかじめ切り出す。この4点を押さえておけば、出てきた結果を信用して使えます。
今日できることは、いつも照合している2つのファイルを開いて、照合の鍵に使える列がどれかを決めることです。単独では足りないなら、何と何を組み合わせるかまで書き出してみてください。
IRS WEBスタジオでは、データの突き合わせ作業の自動化や手順づくりを承っています。今の照合作業の整理からご相談いただけます。