第9回 合わせ技で本領発揮! グループ化+マージ

[グループ化]と[マージ]の合わせ技

Power Queryの各機能を単独で用いるだけでも、従来Excelよりかなり便利で凄いことができます。

Power Queryの本領は

”合わせ技”で実感できるよ!

今回は[グループ化]という機能+第5回で解説しました[マージ]の合わせ技で、データ同士の連携具合を確認する手順を見ていきます。

例えば “出荷データ” と “販売データ” がそれぞれ存在する場合、出荷データの方には “取引” 単位で1日に何個ものデータがあるのに対し、販売データの方は “日付” 単位で1日1個の集約された取引データとなっている場合があります。

管理業務の一環として、不正会計の兆候をつかむため、こうした出荷データと販売データの突合します。従来Excelでは、拙著『CAATで粉飾・横領はこう見抜く』(中央経済社)で説明している「データ名寄せとデータ連携」でこうした兆候を検証することになります。

検証作業自体は、そう難しいものではありません。

検索に欠かせない“VLOOKUP関数”や文字列操作に必要な“テキスト関数”、データ項目を一つにまとめる“&演算子”、対象データを抽出するために用いることが可能な“ジャンプ機能”などを使うだけです。

でも、工数が多くなりがち…。

従来Excelを使う場合、かなり煩雑な処理をデータが追加・更新される都度行う必要がありました。

そこは、モダンExcelでしょ!

しかし、モダンExcelを使えば、こうした「データ名寄せとデータ連携」のような煩雑となりがちなExcel作業も非常に簡単にできるようになります。

[グループ化]

まず、出荷データと販売データをそれぞれ Power Query で読み込みます。

その上で出荷データについて、”出荷日付” と ”得意先コード” を基準に、”出荷金額” を ”合計” する[グループ化]を行います(図表1)。

図表1 「出荷日付」で「得意先コード」ごとにグループ化
「縦棒」に操作(ここでは、「合計」)したい列を選択

これにより、同一の出荷日付で多数の出荷データがある場合でも、出荷日と得意先コードを基準としてデータを取りまとめることができます。この取りまとめた出荷データは、販売データと一致することが普通です。

このように[グループ化]という機能を使うことで、出荷日付と得意先コードという複数の基準を持ち合わせた出荷金額のようなデータを集計し “出荷日合計” とすることができるようになります。

[クエリのマージ]

この “出荷日合計” データと販売データの “売上金額” を突合する際、[クエリのマージ]をVLOOKUP関数のように使い、“出荷日合計” に対応する “売上金額” の列を、第5回で説明した[完全外部]により結合(マージ)します(図表2)。

図表2 クエリのマージで完全外部(両方のデータの不整合値を抽出する)
Microsoft Public Affiliate Program (JP)(マイクロソフトアフィリエイトプログラム)

0(ゼロ)以外は『異常点』

そして “出荷日合計” のデータ列と、販売データの “売上金額” の列をそれぞれ選択した状態で、[列の追加]タブの[数値の列]グループの[標準]で[減算]をクリックします(図表3)。

図表3 SHIFT+引き算したい項目(ここでは、「出荷日合計」と「売上金額」)を選択後、「減算」

すると、“出荷日合計” と “売上金額” の差額を計算できます。

通常であれば双方のデータは一致し、減算した結果は0(ゼロ)と表示されるはずです。

仮に0(ゼロ)以外のデータがあれば、出荷データと販売データで違った値を持っているということを示します。

こうしたデータの祖語・不一致という異常点が、粉飾や横領の痕跡になり得るのです。

経営管理の要諦の一つ

『異常点』を早期につかむ!

出荷データや販売データなどは毎月更新されますが、モダンExcelを使えばデータの追加・差し替えだけで簡単に分析結果をはじき出せるというのは、業務効率化にかなり有効な手段だと思います。

今回のポイント

・Power Queryの本領は、合わせ技でより実感することができる。

・[クエリのマージ]をVLOOKUP関数のように使い、[グループ化]で複数の基準を持ち合わせたデータを集計できる。

モダンExcel研究所

フォローお待ちしてます!

1件のピンバック

コメントは現在停止中です。

error: Content is protected !!