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

テーマはピボットを使ったデータの照合 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を使ってくっつけてみます。

Vlookupを使った照合

支払データの合計は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

このデータの照合の方法はいろいろなデータで応用できます。作業も複雑な作業はないので、一度やってみれば簡単にできますので、ぜひ活用してください。

コメント

タイトルとURLをコピーしました