松田軽太のブロぐる

企業の情シスで働いています。このブログでは読んだ本など思いつくままに書いています。

【スポンサーリンク】

【Excel初心者向け】支払データと売上データを簡単にピボットテーブルで違算照合する方法

【スポンサーリンク】

ある日突然、上司から「得意先から送られてくる支払データと我が社の売上データが合っているか超特急で照合してくれ」と言われたらどうしますか?


こんにちは! 松田軽太です。


時間に余裕があるならExcelが得意な人とか情報システム部に丸投げすることもできますが、超特急でやらなければならないなら、自分でやるしかありません。


でも、もし、あなたがExcel得意じゃなかったら…と考えるとゾっとしますよね。

 

f:id:matuda-kta:20190316010503p:plain

 

そこで今回はExcelを使って2つのデータを簡単に照合する方法をご紹介します。

 

 得意先からの支払データの項目を確認する

照合するとはいっても、どんな項目があるのか分からなければ、照合もできません。


今回の例では下記のような項目でした。

処理日、発注№、発注明細№、商品CD、数量、単価、金額、貴社伝票番号

 

f:id:matuda-kta:20190316011138p:plain

 

もっと多くの項目がある複雑なデータの場合は、不必要な項目は削除してしまった方が良いでしょう。

 

 自社の売上データの項目を確認する

おそらく皆さんの会社には基幹システムの売上検索画面とかBIツールなどから売上データをExcel出力できる環境があるのではないでしょうか?

 

今回の例では得意先からの支払データの項目に合わせて売上データから下記のような項目を出力しました。

 

計上日、発注№、商品CD、数量、単価、金額、伝票番号

 

f:id:matuda-kta:20190316011203p:plain

 

なるべく同じような項目を選んでExcelデータを出力しましょう。

 

 2つのデータの共通点を見つける 

次にこの2つのデータを比較するために共通する項目を見つけます。

違算照合する場合には、よく発注№やオーダー№をキーにすることが多いですが今回どうでしょうか?

 

得意先の支払データは発注№と発注明細№の項目が分かれていますが、自社の売上データでは発注№と発注明細№が「-(ハイフン)」でつながれて一つの項目になっていました。

 

これでは照合することができません。

 

他の項目を探してみると自社の伝票№が共通していることが分かりました。

今回は伝票№をキーにすることにします。

 

f:id:matuda-kta:20190316011953p:plain

 

 2つのデータを一つのシートにまとめる

得意先からの支払データと自社の売上データを比較するので、一つのシートにコピペしてまとめましょう。


その際、どのデータが支払データなのか売上データなのかが分かるように「データ種別」という項目を追加します。


そして「得意先 支払データ」「自社 売上データ」と入力し、判別できるようにします。

 

f:id:matuda-kta:20190316012437p:plain

 

ピボットテーブルという機能を活用する 

Excelにはピボットテーブルというメンドクサイ集計をチャチャっと集計してくれる便利機能が用意されています。

 

中には「いや、前にピボットテーブルって便利だって聞いて使ってみたけど、意味わかんないんだけど!」って人もいらっしゃるのではないでしょうか?

 

ご安心ください、そういう人はたくさん居ます!

 

しかし、使い方が分かれば、ピボットテーブルは驚くほど簡単に2つのデータを比較してくれるので、今回はこのブログのやり方をそっくり真似して、まずは活用してみましょう。

 

 「挿入」から「ピボットテーブル」を選択する 

Excelの上の部分に「挿入」と書いてあるので、そこをクリックすると「ピボットテーブル」と書かれた表のような図が表示されるのでクリックします。

 

すると「ピボットテーブルの作成」という画面が現れます。

ここは迷わずそのまま「OK」を押しましょう!

 

f:id:matuda-kta:20190316012812p:plain

 

するとピボットテーブルのシートが追加されます!

f:id:matuda-kta:20190316013557p:plain

 

下図を参考にして集計したい項目を配置します。

 

①金額をΣ値の枠に、伝票番号を行の枠に、データ種別を列の枠に、それぞれマウスで配置します。

 

②伝票番号別に金額が集計されます。

 

f:id:matuda-kta:20190316014238p:plain

 

集計内容を分かり易くするためにラベル名を項目名に修正します。

f:id:matuda-kta:20190316014930p:plain

 

たったこれだけの簡単な操作で伝票番号別に金額を比較することができました。

f:id:matuda-kta:20190316015101p:plain

 

どうでしたか?

実際にやってみるとピボットテーブルって意外と簡単でしたよね?

 

まとめ

ピボットテーブルを活用すると面倒な集計も簡単に行えます。

今回は金額を集計しましたが、集計項目を数量にすることももちろん可能です。

 

他にもピボットテーブルには便利な使い方が沢山あります。 

是非、Excelのピボットテーブルを活用して、仕事を楽にしましょう!

 

もっと詳しくピボットテーブルの使い方を知りたい場合は、こちらの本を参考にしてみてください。

これならできる Excel ピボットテーブル作成 超入門~仕事の現場で即使える

これならできる Excel ピボットテーブル作成 超入門~仕事の現場で即使える

 

 

この他にも当ブログではExcelの簡単に使う方法をご紹介していますので、参考にしてくださいね。

www.matudakta.com

 

www.matudakta.com

 

【スポンサーリンク】