【Excel】データの照合でピボットテーブルの活用(練習用データあり)

経理部門に限らず突発的に生じるのがデータ同士の照合です。固定資産システムから出した資料と会計データが一致しない、仕入データと支払データが合わないなどルーティンの仕事に追加で発生してしまいます。

会計監査やらシステム変更やらで発生、、、

経理部門にいると監査の会計士の人から「このデータとこのデータ合計が一致してないですがなぜですか？」とよく聞かれます。

あとはシステム変更した後などは、システム間で明細の不一致が生じたりして、データ同士の照合をしなければならない場面によく出くわします。

そんなときのために、照合の方法を押さえておきましょう。データベースの結合でいう「完全外部結合」の考え方でExcelのピボットを使って照合しましょう。
（完全外部結合：A、Bの2つのデータを結合する際に、AとBで一致するデータのほか、Aだけにあるもの、Bだけにあるものもすべて含めて結合する。）

VlookupやXlookupでは上手くいかないことが多い

データを照合するとなれば、とりあえずはvlookupを使ってやってみよう、という人も多いと思います。

しかし、不一致の原因がはっきり分かっていないときは、vlookupでは上手くいかないことが多いです。

簡単なデータで見ていきましょう。

こんなデータだとvlookでは対応できない

次のような仕入データと支払データで考えてみましょう。
合計が仕入データが5,900、支払データが5,300と一致していません。

発注番号がキーになりそうなデータです。

状況を整理しておくと、
[発注番号a006：200]は仕入データにあるが支払データにない
[発注番号a007]は仕入データでは金額が1000で支払データでは金額が900と相違している
[発注番号a009]は仕入データでは金額が800で支払データでは2行に分かれて400と400で合計では一致している
[発注番号b001:-300]は仕入データにはないが、支払データにだけある。

仕入データに支払データをvookupを使ってくっつけてみます。

支払データの合計は5,200となってしまい、元データの5,300と一致しません。

vlookupのベースとなっている仕入データに含まれない[発注番号b001：-300]のデータはvlookupでは引っかかりません。
また、仕入データでは金額が800、支払データでは400と400の2行に分かれている発注番号a009のデータは上の行の分しか引っかかりません。

その結果、vlookupでは上手く照合ができません。

照合はピボットテーブルを使った方法がおすすめ

先ほどの例のように、双方に欠落データがある場合やデータが2行に分かれてしまっている場合はvlookupでは上手く照合ができません。

こんなときは、ピボットテーブルを使った方法がおすすめです。

まず、両方のデータを一つの表に加工します。

①仕入データをA列を空けてB列から貼り付ける
②支払データの1行目（見出し行）を仕入データの次の列から貼り付ける
③支払データの2行目以降を仕入データが終わった次の行、仕入データが終わった次の列から貼り付ける
④照合のキーとなる発注番号をA列に表示させる。（A2セルに「C2＆H2」を入力して最終行までコピーすれば両方のデータとも発注番号をA列に表示可能。
A列の1行目に「照合キー」など見出しを入力しておく。

そして、この加工したシートをピボットテーブルで集計します。

行に照合キー、合計でそれぞれのデータの金額列を設定すると、「完全外部結合」の形式でデータ同士の照合ができます。

こんな感じで、差異の原因が[a006]と[a007]と[b001]ということが分かります。

練習用にデータを表で貼り付けておきます。これをExcelに貼り付ければ、上記の照合の練習が可能です。

仕入データ

購入先	発注番号	仕入日	仕入金額	摘要
A社	a001	1月1日	1000	商品A
B社	a002	1月5日	200	商品A
C社	a003	1月5日	500	商品A
D社	a004	1月20日	600	商品A
E社	a005	1月21日	300	商品A
F社	a006	1月25日	200	商品A
G社	a007	1月25日	1000	商品A
H社	a008	1月28日	900	商品A
I社	a009	1月30日	800	商品A
J社	a010	1月31日	400	商品A
			5900

支払データ

支払日	発注番号	相手先	支払金額	摘要
2月10日	a001	A社	1000	振込
2月10日	a002	B社	200	振込
2月10日	a003	C社	500	振込
2月10日	a004	D社	600	振込
2月10日	a005	E社	300	振込
2月10日	a007	G社	900	振込
2月10日	b001	G社	-300	振込（値引）
2月10日	a008	H社	900	手形
2月10日	a009	I社	400	振込
2月10日	a009	I社	400	手形
2月10日	a010	J社	400	振込
			5300