ExcelやGoogleスプレッドシート、あるいは業務システムからダウンロードしたCSVファイルなどのデータ突き合わせで苦労したことはありませんか？
私もコンサルタントとしてITベンダーとして、ユーザ部門として様々な業務データを扱ってきました。受領したデータをボタンひとつで突き合わせて不一致の差分データを簡単に抽出できたらどんなに便利でしょうか。
しかし、データ突き合わせに王道はありません。両方が同じ形式やフォーマットならよいのですが、多くの場合はバラバラです。バラバラだからこそ丁寧に、手順に沿って突き合わせを行い、差異が発生している箇所を着実に特定していくことが効果的です。
これまで何記事かに分けてデータチェックのノウハウを紹介してきました。本記事ではそれをまとめます。
データチェックを4つのプロセスに分けて解説します。
- データ突合せ前
- データ突合せ時
- データ差異発生時
- データ突合せが終わるまで
データ突合せ前に確認すべきこと
データ突合せする時の多くは、時間が限られています。そのためデータを入手するとすぐに突き合わせをしたくなります。ただ、速く正確にデータチェックを完了させるためにこそ、事前の確認が大切になります。
まずは、受領したファイルから確認をしていきます。
データチェックの基礎知識その１ファイルに関するあれこれ
データの入ったファイルが、Excelやスプレッドシートであればファイルのバージョンや、対象となるシートを確認します。また、テキストファイルの
データの場合は、以下の項目を見ておきます。
- 区切り文字は何か？（固定長、カンマ、タブetc）
- 改行コードは何か？（CR+LF、LF、なし etc）
- 文字コードは何か？（UTF-8、S-JIS、EBCDIC etc）
改行コードと文字コードについての参考記事：データチェックの基礎知識その2 改行コードと文字コード
データ突合せの基本は縦と横をそれぞれ合わせること
対象ファイルの確認ができたらデータ突合せに入ります。
最初に縦(レコード)を合わせるためのキー項目を特定します。キー項目とはその行を特定するのに必要となる項目です。一つの項目で一行に決まることもあれば、複数項目が合わさって一つに決まることもあります。例えば顧客コードや商品コードで一行に決まる場合もあれば、顧客コードに地域コードや部門コードを組み合わせる必要がある場合もあります。
次に、横(カラム、フィールド)を合わせるための項目定義を確認します。設計書に項目定義がある場合は、桁数やデータ型、備考を参考にをマッピングを行います。定義にもとづいて、一つの項目の先頭数桁を切り取ったり、逆に桁埋めしたり、いくつかの項目を結合して突き合わせることもあります。
そして、AccessやExcel、Googleスプレッドシートでキー項目で対象データを結合し、一致/不一致を確認します。Accessの場合はクエリを使い、ExcelやスプレッドシートではVLOOKUP関数などを使います。
参考記事：データチェックの基礎知識その3縦と横を何であわせるか
データ差異が発生した時に確認すべきこと
突合せをして全く差異が発生しないことはめったにありません。型や桁の変換がなかったとしても機械的に突き合わせると何らかの差異が出るのを覚悟しておきましょう。
突合せ前に確認したことと同様に、縦（行レコード）の差異、横（項目フィールド）の差異の順番で原因を確認していきます。
2つのデータを比較して件数に差異があった時は、まず片方だけに存在するデータを特定します。それぞれに共通する点を探します。特定の日付で作成されたデータや、特定の顧客や地域に限定されていないかなどです。
差異はプログラムのバグや、データ移行や臨時対応などで発生することが多いため、その原因にあたりをつけながら差異のあるレコード、差異のある項目を確認していきます。
ある程度片方を見たら残りを見てみるのも有用です。数項目だけ違っていてあとは同じデータが存在することが大いにあるからです。違いに気づきやすくなります。
また、データ件数が多い場合は大量データを一気に扱うと処理が重くなったりExcelだとファイルが強制終了することもあるため、件数を絞って確認を進めるのもおすすめです。
参考記事：データチェックの基礎知識その4 差異が発生した時の分析方法
差異が解消するまで突合せを繰り返すために
差異の原因が特定できたら元データの修正やプログラムの改修を行います。
しかしながら変更を加えると改善することもありますが改悪することもありえます。差異が完全になくなるまで効率よくチェックを繰り返せるよう、チェックツールと手順の作成をお勧めします。その際、なるべく手作業は減らすことです。
手作業は作業者によって品質にばらつきが出るからです。
▼その最たる例が、先頭に埋められている0を消してしまうことです。CSVファイルをExcelで開く時は要注意です。
参考記事：データチェック時は先頭0落ちに気をつける
▼チェックツール作成において、AccessではVBAのようなプログラミング言語を使わなくても、SQLやクエリを視覚的に作成することでデータ操作を手軽に行うことができます。
参考記事：Access初心者がデータの現新比較をしたい時に読む本
▼SQLはシンプルな構文を組み合わせて使います。慣れると使いやすく、実行してから結果を得るまで独特の爽快感があります。
参考記事：SQLを学ぶ時のおすすめ順
▼また、SQLやクエリをもう少し手軽に処理できるAlteryxのようなツールもあります。
参考記事：SQLプログラミングからセルフサービスデータ分析へ
終わりに
本記事では、現行システムと新システムの2つのデータを比較するようなデータ突合せ作業に関わるノウハウを紹介してきました。
プログラマーやSEのようにプログラミング経験がない方もこれらの作業に携わることはあると思います。その際のお役に立てれば幸いです。