経理部門に限らず突発的に生じるのがデータ同士の照合です。固定資産システムから出した資料と会計データが一致しない、仕入データと支払データが合わないなどルーティンの仕事に追加で発生してしまいます。
会計監査やらシステム変更やらで発生、、、
経理部門にいると監査の会計士の人から「このデータとこのデータ合計が一致してないですがなぜですか?」とよく聞かれます。
あとはシステム変更した後などは、システム間で明細の不一致が生じたりして、データ同士の照合をしなければならない場面によく出くわします。
そんなときのために、照合の方法を押さえておきましょう。データベースの結合でいう「完全外部結合」の考え方で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 |
このデータの照合の方法はいろいろなデータで応用できます。作業も複雑な作業はないので、一度やってみれば簡単にできますので、ぜひ活用してください。
コメント