第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)。

図表1 パワークエリの「グループ化+クエリのマージ+減算」で分析


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

図表2 パワーピボット「リレーションシップ」の後、ピボットテーブルで分析


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

それゆえ、選択に悩む…

モダンExcelの役割と選択のポイント

経営管理の実務において「モダンExcel」選択のポイントは、双方が担う役割に注目してみることです。

①パワークエリは、「1列1行1項目」にデータを整理する

②パワーピボットは、DAX(第16回を参照)で様々な計算を行い、データを見える化する

そして、①②という「モダンExcel」を通じて、ピボットテーブルで切り口=ディメンションを変えながら、新たな発見・洞察を得ようとするわけです。

こうしたことを踏まえ、筆者が心がける「モダンExcel」を使ったデータ分析の基本スタンスは、次のとおりです。

①パワークエリで「1列1行1項目にデータを整理」
②パワーピボットで「DAXによる様々な計算」結果を通じ「データを可視化」
➡ 新たな発見・洞察を得る!

こうしたポイントを押さえておくと、的確なデータ分析ができると思います。

モダンExcel研究所

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

error: Content is protected !!