［グループ化］と［マージ］の合わせ技

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

Power Queryの本領は

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

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

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

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

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

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

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

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

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

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

［グループ化］

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

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

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

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

［クエリのマージ］

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

０（ゼロ）以外は『異常点』

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

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

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

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

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

経営管理の要諦の一つ

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

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

■■■■■

「正しいモダンExcel」の使い方の基本を学ぶには、Power Query（パワークエリ）とPower Pivot for Excel（パワーピボット）の両者を「一体理解」する必要があります。
ぜひ、拙著「モダンExcel入門」（日経BP）で学んでみて欲しいと思います。
サンプルデータで、実際に手を動かしながら、理解を深めることもできます。参考にしてください。

■■■■■

「モダンExcel研究所」を楽しむ際の【注意事項】

コンテンツコピー、スクレイピングなど【厳禁】　☚【検知ツール設置済】☚悪質な場合【法的措置】を講じます

（過負荷によるサーバー障害が生じた経験上、ご協力お願いいたします！）

今回のポイント

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

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