第18回 モダンExcel、それぞれの役割と選択のポイント

「循環取引」という厄介な不正会計の手口をご存知でしょうか。ありもしない取引をグルグル回す、一度手を出すと麻薬のようになかなかやめられない、監査の過程で発見も困難とされる粉飾の手口です。
※ 詳しくは「会計ドレッシング10episodes」(東洋経済新報社)などをご参照ください。
この「循環取引」が、業界を大きく震撼させています。
2020年1月に発覚した、東証1部上場N社ほか少なくとも5社による1000億円超ともいわれる粉飾事例は、最たる循環取引事例の一つです。
こうした不正会計の発見局面にも「モダンExcel」は活用できます。
データ連携の検証事例とモダンExcel
発見が難しいとされる循環取引ですが、筆者はこれまで3件の循環取引を発見・処理した経験があります。
循環取引のような不正会計の端緒を発見するには、異常点監査技法と共にCAAT(Computer Assisted Audit Techniques、コンピュータ利用監査技法)の知識が必要となります。
このような「数字」に対する思考回路は「データを可視化し、洞察を得る」という管理会計全般に共通するものです。
第9回でも少し触れましたが、CAATの一つに「データ突合によるデータ連携の検証作業」という技法があります。一言でいえば、販売データと出荷データの連携を見て差異のあるデータを異常値として抽出する、という手法です。
こうしたデータ連携を従来Excelで行うには「VLOOKUP関数」を用いることが一般的です。しかし、この方法には弱点があります。いわゆる「104万行の壁」の存在などもありますし、そもそも従来Excelの場合はデータ量が多くなるほど処理速度が遅くなり、実務での適用が困難を極めることになりもします。
そこで、モダンExcelの出番です!
モダンExcelには大きく分けて2つのツールがある
① Power Query(以下、パワークエリと呼びます)
② Power Pivot for Excel(以下、パワーピボットと呼びます)
どちらを使えば良いのかな?

「データ突合によるデータ連携の検証作業」というデータの整合性を把握する場合、①パワークエリであれば「グループ化+クエリのマージ+減算」(第5回、第8回、第9回などを参照)により、販売データと出荷データの整合性を検証できます(図表1)。

他方、②パワーピボットであれば「リレーションシップ」(第12回を参照)後、ピボットテーブルで分析するという方法で、データの整合性を検証することができます(図表2)。

このように、VLOOKUP関数の代替としてモダンExcelを用いる比較的簡単なデータ分析事例では、①パワークエリ、②パワーピボット、どちらでも期待する結果を得られる場合もあるのです。
それゆえ、選択に悩む…

モダンExcelの役割と選択のポイント
経営管理の実務において「モダンExcel」選択のポイントは、双方が担う役割に注目してみることです。
①パワークエリは、「1列1行1項目」にデータを整理する
②パワーピボットは、DAX(第16回を参照)で様々な計算を行い、データを見える化する
そして、①②という「モダンExcel」を通じて、ピボットテーブルで切り口=ディメンションを変えながら、新たな発見・洞察を得ようとするわけです。
こうしたことを踏まえ、筆者が心がける「モダンExcel」を使ったデータ分析の基本スタンスは、次のとおりです。

①パワークエリで「1列1行1項目にデータを整理」
②パワーピボットで「DAXによる様々な計算」結果を通じ「データを可視化」
➡ 新たな発見・洞察を得る!
こうしたポイントを押さえておくと、的確なデータ分析ができると思います。

モダンExcel研究所
フォローお待ちしてます!