表示された結果がなんか変!というときの、正しいメジャーなのか検証する方法、というか一つの考え方

Power Pivot for Excel(パワーピボット)で使う「DAX」を、どのように正しく動作させていけばよいのか、「デバッグ」のポイントをご紹介します。

「デバッグ」とは、プログラムを修正することを言います。

本件事例の概要

ここでは、財務会計システムからCSVデータをPower Queryに取り込み後、ETL(抽出・変換・読込)を通じ、「財務会計」データを「管理会計」データに変換しています。

「ETL」の定義は、拙著20ページにあります。下記がそれです。

このETLにより得られた「管理会計」データを使い、「予実管理」をしています。

なぜ、そうするかについて、ここでは深く触れません。
一言でいえば、管理会計の基本である「損益分岐点分析」を行えるようにするため、こうしたことを行っています。
その方法は、従来型の財務会計PLを「変動費」「固定費」に分けて、利益志向型の管理会計PLにすることで可能となります。

そのうえで、Power Pivot for Excelで「DAX」を用いて、管理会計ベースで実績と予算を比較しているのが、下記に示す複数のピボットテーブルになります。

こうしたことできるようになると、財務会計データを読み込むだけで、「経営者が大切にする管理会計」という目線で「予実管理」ができるようになるので、大変便利だと思います(詳細割愛)。

そこで遭遇した、「デバッグ」事例です。

自動化とファクト

こうした予実管理は、「VBA」による「マクロ」でも可能です。
しかし、VBAによるマクロの場合、工数が多くなり、プロセスも複雑になりがちで、なにより古臭い。

ここではこれ以上の議論を割愛し、別稿に委ねますが、VBAは早晩「自動化」の主役から確実に引きずり降ろされることになります。
なぜなら、既に自動化を担うアーキテクチャのトレンドから、外れつつあるからです。

今後は「モダンExcel」、そしてその先にある「Power Platform」などの、いわゆる「ノーコード」「ローコード」のツールによる「自動化」が主流になることは間違いありません。
これも「ファクト」です。

データ分析者は、常に「ファクト」でモノゴトを見て、感じて、語る必要があります。

これは、多くの経営者が大切にしている「三現主義」と言い換えることもできるでしょう。
三現主義とは、現地・現物・現況を観る、という「経営管理の鉄則」です。

「ファクト」に基づかない「フェイク」は、時に自らの身を亡ぼすことにもなります。
くれぐれも、留意してください。

よく見る

それでは、本件デバッグ事例について、詳細にみていきましょう。

まず、「全部署」を対象にしたとき。
一見すると、異常点は見られず、正確に計算しているように見えますね。

「営業1部」を選択した状態も、問題なしのようです。

しかし、「営業2部」を選択すると…。

おかしいの、わかります?

上図の下段、「予算累計%」列の「売上高」行、セルE55が「68.7%」となっています。
この列は売上高に対する割合を表す列であるので、このセルE55は「100%」でなければいけません。

こういうミスを発見するために、最低限の「会計」知識が必要で~す(詳細割愛)

なぜこんなことが起きたのか、検証し、デバッグする必要があります。

ちなみに、「年」部分が上段の実績が「2017」であるのに対し、下段の予算が「2019」となっているのは、問題ありません。
「2017」「予算」は全社合計とされ各部署で区別されず、「2019」から部署ごとに「予算」が区分計上されているからです(詳細割愛)。

【デバッグ1】 スライサーの接続

上図45~47行目あたりにある「営業1部」「営業2部」「管理部」というボタンの箇所を「スライサー」と言います。

拙著「モダンExcel入門」189ページ~にも説明がありますので、参考にしてください。

この「スライサー」の各ボタンをクリックすることで、部門フィルターを変更でき、データの見え方を切り替えることができます。

「スライサーの接続に問題がある?」と思い、接続状況を確認。

特に異常点は見当たりませんでした。

【デバッグ2】 メジャーの整合性

上図の上段「実績累計%」と下段「予想累計%」はメジャーです。
これらのメジャーは、一方を作成後、コピペして使いまわしています。
メジャーのコピペは便利ですので、試してみてください。

但し、メジャーのコピペ後、計算対象となるデータ範囲が異なるので、適宜該当箇所を変更しています。
元々のメジャーは、こうです。

この「実績累計%」というメジャーをコピペして、次のように「予算累計%」にしています。

‘固変分解後jisseki’ を ’yosan’ にするなど変更箇所は当然ありますが、DIVIDE(割り算)などの計算ロジックに変更は見当たりません。
メジャーの不整合も、なしです。

【デバッグ3】 メジャーによる計算ロジック

ここで、ふと思いました。最初の指摘に戻ります。

そもそも、「なぜ、セルE55が68.7%となっているのか? これは、どのような計算ロジックなのか?」ということが頭をよぎりました。

そこで、考えました。

セルE55の計算ロジックは、こうなっているはずです。

 ➡ 予算累計(セルD55)666 ÷ X = 予算累計%(セルE55)68.7%

検算の基本は、逆算!

逆算すれば、X=969.432…

これと同様のロジックで、56行目(変動費)から62行目(営業利益)まで計算すると、いずれの計算結果ともに X=969前後に落ち着きます。

「969?」

この結果から、分子には問題がなく、分母が間違っている可能性が高い、こう思いました。

そこで、メジャーの中で分母に相当する部分だけを取り出し、検算用のメジャーを新たに作成し、ピボットテーブルで表示することにしました。

わかりました! メジャーがおかしいのです。具体的には、引数に問題があることが分かりました。

ちなみに、「969」という数字はいったいどこから来たのか突き詰めてみると、「2019」「Q1」「予算累計」「売上高」の値(セルT54)であるということもわかりました。

メジャーをデバッグ!

もう一度上述のメジャーを見てください。

いずれのメジャーにも「ある大切な部分」が不足しています。わかりますか?

それは、’部署名’ という要素が引数に含まれていないのです。これが悪さをしていました。

先ほどのメジャーを、次のようにデバッグします。

変更箇所は、ALLEXCEPT関数の2つ目の引数として ’部署名’ を、上下それぞれのメジャーで追加しています。

これで正しく計算できるようになりました!

こうしたことを理解するには、「コンテキスト」という「モダンExcel」を一体理解するための中心的なコンセプト、「動作」に関する概念を知る必要があります。

本件では、「コンテキスト」に不備があったため、うまく「動作」しなかった、というわけです。

「コンテキスト」などの詳細については、拙著「モダンExcel入門」、第2章、PART4『使いこなしに必要な「DAX」の基礎知識』132ページ~をご参照ください。

不思議なこと

「一体なぜ、予算累計%のみで問題があるように表示され、実績累計%は問題ないように見えたのか?」

こうしたことは、DAXで「たま」にある「動作」なのです。

「一見すると問題ない」ように計算表示されるのは「たまたま」ということが、DAXでは結構あります。

そして、こうしたことは「モダンExcel」入門者にありがちなことでもあります。
結果として、計算ミスを犯していてもスルーパスしている、こうしたことが「なんちゃってモダンエクセル」派のWEBサイトなどの事例を見ていると散見されます。

そもそも「計算ミス」の原因には、様々な要因があります。
上記のように、「集計に必要な引数の誤り」や、「メジャーの書き損じ」は、典型的な計算ミスの要因です。

また、次に示すように、「モダンExcel」の活用に必要な基本的な視点がそもそも欠けていることがDAXの計算結果に悪影響を及ぼしていることもあります。
くれぐれも、留意してください。

・『適切なカレンダーテーブルがない』
⇧ ⇧ ⇧
拙著「モダンExcel入門」、第2章、PART2「時系列分析に必須のカレンダーテーブル」112ページ~参照


・『不適切なデータモデルとなっている』

⇧ ⇧ ⇧
拙著「モダンExcel入門」、第2章、PART3「リレーションシップで、複数データを関連付ける」121ページ~参照


・『暗黙のメジャーを使用している』

⇧ ⇧ ⇧
自動的に、つまり暗黙的に「メジャー」とされるものを「暗黙のメジャー」という。これは、ピボットテーブルのΣ値の「合計」などと考えればよい。
この「暗黙のメジャー」では計算を誤る。必ずオリジナルの「メジャー」を作成しなければならない

計算結果の不具合につながる要因は他にもありますが、その大きな原因の一つに上記指摘のような、そもそも「正しいデータモデルになっていない」ことが挙げられます。

「なんちゃってモダンエクセル」派の説明では、上記「正しいデータモデル」の基本を無視しているため、「一見すると正しそうな結果に見えるが、実は致命的な計算ミスをしている」、こうしたことがWEBサイト等で散見される現状があります。
くれぐれも、留意してください。

「モダンExcel」とその先にある「Power BI」でDAXにより計算する場合、上記の「モダンExcel」活用に必要となる基本的な視点は、必ず守っていただく必要があります。

そのうえで、必ず「計算結果を眺める」ようにしてください。
サンプルベースで構わないので「大切な箇所は検算」を忘れずに行ってください。

こうした計算ロジックの検証作業は、「IT統制」を確認する大事な作業の一つでもあります。
但し、そのプロセスに変更がない限り、ITによる統制なので「一回だけ検証」すれば、それで十分です。
面倒がらず、主要な計算ロジックの検証は必ず行うようにしてください。

問題なければ、あとはデータをいくらでも追加することができます。
更新ボタンをクリックするだけで、パワーピボットが勝手に年度更新してくれます。

モダンExcelって、基本ポイントを押さえれば、すごく便利だね!

プログラムミスを「発見」しやすくするために必要なこと

適切なデータモデルで、簡単なDAXを心掛ければ、デバッグというプログラム修正作業は、そんなに難しいことではありません。

反対に、不適切なデータモデルの場合、デバッグが困難を極めることもあります。

たとえば、ネットや市販本などでも「ファクトテーブル」が「メジャーだけ」になっていない事例を散見しますが、これは要注意です。
適切なデータモデルの場合、「ファクトテーブルは、メジャーだけになる」からです。

不適切なデータモデルについては、下記も参考になると思います。

基本的なことができていない「なんちゃってモダンエクセル」の場合、あっちも、こっちも、という具合にプログラムを修正しまくり、結局デバッグがうまくいかないことがあります。

挙句の果て、裏技で「モダンExcel」のオプション画面でエラーを外してみたり、と色々なことを提案されるWEBサイトを見かけもしますが、こうしたことは単なる一時しのぎにすぎません。
根本的な解決にはなっていないことが多いように思います。

そもそも、オプション画面を触ることは、そうそうないはずですし、それにより更なる不具合を助長することにもつながりかねません。
よって、オプション画面をむやみやたらと触ることは、お勧めできません。

「適切なデータモデル」で、「簡単なDAX」を心掛ける。

これが、DAXと仲良しになる基本の「き」です。

VBAを使ったマクロでもありがちですが、精通した人が作成したマクロほど、一見何を意味するプログラムなのか解読不能、意味不明、なんのことだかさっぱりわからない計算ロジック、こうしたことがよくあります。

IT統制を見てきた者からの忠告!

プログラムは、とにかく「可読性」が大切です。

「一見して分かりやすい」プログラムは、「間違いも発見しやすく」、そもそもきちんと「動作」します。

こうしたこともあるので、可読性の低いプログラム作成は、即刻やめるべきです。


これは、DAXでも同じことです。

「メジャー」や「計算列」を考える際、長々と「DAX」を記述することはやめるべきです。
その工夫については色々ありますが、基本的なことを申し上げれば、「中間メジャー」を上手に使う、「VAR」をうまく活用する、ということにあると思います。

詳しくは、拙著「モダンExcel入門」、第3章、PART5「目標値、実績値、達成率をビジュアル化する」P228~、PART6「ピボットグラフで移動平均線を描く」P251~などをご参照ください。

シンプルに考える

モノゴトの本質は、いつもシンプルです。

DAXに限らず、モノゴトの「基本」をしっかり理解する、これが大切です。

拙著「モダンExcel入門」は、「1次情報」に基づき、モダンExcelを一体理解できるよう、「基本」に忠実に解説しています。
「モダンExcel」の応用を期待される方には物足りないかもしれませんが、「基本」を知りたい方には最適な内容になっていると自負しております。ぜひ、参考にしてみてください。

🌷

■ 拙著「モダンExcel入門」に対して、敵対勢力による卑劣な「Amazonレビューのコメント付き低評価」の結果、「1次情報」を基にした「モダンExcel」の「基本」の浸透に、重大な懸念が生じています。
ぜひ、皆様のお力添えをいただければと思います。ご支援のほど、よろしくお願いいたします。 ■

🌸

■■■■■

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

■■■■■

■■■■■

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

■■■■■

モダンExcel研究所

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

error: Content is protected !!