表示された結果がなんか変！というときの、正しいメジャーなのか検証する方法、というか一つの考え方
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」から部署ごとに「予算」が区分計上されているからです（詳細割愛）。
【デバッグ１】 スライサーの接続
上図45～47行目あたりにある「営業１部」「営業2部」「管理部」というボタンの箇所を「スライサー」と言います。
拙著「モダンExcel入門」189ページ～にも説明がありますので、参考にしてください。
この「スライサー」の各ボタンをクリックすることで、部門フィルターを変更でき、データの見え方を切り替えることができます。
「スライサーの接続に問題がある？」と思い、接続状況を確認。
特に異常点は見当たりませんでした。
【デバッグ２】 メジャーの整合性
上図の上段「実績累計％」と下段「予想累計％」はメジャーです。
これらのメジャーは、一方を作成後、コピペして使いまわしています。
メジャーのコピペは便利ですので、試してみてください。
但し、メジャーのコピペ後、計算対象となるデータ範囲が異なるので、適宜該当箇所を変更しています。
元々のメジャーは、こうです。
この「実績累計％」というメジャーをコピペして、次のように「予算累計％」にしています。
‘固変分解後jisseki’ を ’yosan’ にするなど変更箇所は当然ありますが、DIVIDE（割り算）などの計算ロジックに変更は見当たりません。
メジャーの不整合も、なしです。
【デバッグ３】 メジャーによる計算ロジック
ここで、ふと思いました。最初の指摘に戻ります。
そもそも、「なぜ、セルE55が68.7％となっているのか？ これは、どのような計算ロジックなのか？」ということが頭をよぎりました。
そこで、考えました。
セルE55の計算ロジックは、こうなっているはずです。
➡ 予算累計（セルD55）666 ÷ X ＝ 予算累計％（セルE55）68.7％
検算の基本は、逆算！
逆算すれば、Ｘ＝969.432…
これと同様のロジックで、56行目（変動費）から62行目（営業利益）まで計算すると、いずれの計算結果ともに Ｘ＝969前後に落ち着きます。
「969？」
この結果から、分子には問題がなく、分母が間違っている可能性が高い、こう思いました。
そこで、メジャーの中で分母に相当する部分だけを取り出し、検算用のメジャーを新たに作成し、ピボットテーブルで表示することにしました。
わかりました！ メジャーがおかしいのです。具体的には、引数に問題があることが分かりました。
ちなみに、「969」という数字はいったいどこから来たのか突き詰めてみると、「2019」「Q1」「予算累計」「売上高」の値（セルT54）であるということもわかりました。
メジャーをデバッグ！
もう一度上述のメジャーを見てください。
いずれのメジャーにも「ある大切な部分」が不足しています。わかりますか？
それは、’部署名’ という要素が引数に含まれていないのです。これが悪さをしていました。
先ほどのメジャーを、次のようにデバッグします。
変更箇所は、ALLEXCEPT関数の2つ目の引数として ’部署名’ を、上下それぞれのメジャーで追加しています。
これで正しく計算できるようになりました！
こうしたことを理解するには、「コンテキスト」という「モダンExcel」を一体理解するための中心的なコンセプト、「動作」に関する概念を知る必要があります。
本件では、「コンテキスト」に不備があったため、うまく「動作」しなかった、というわけです。
「コンテキスト」などの詳細については、拙著「モダンExcel入門」、第2章、PART４『使いこなしに必要な「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章、PART５「目標値、実績値、達成率をビジュアル化する」P228～、PART６「ピボットグラフで移動平均線を描く」P251～などをご参照ください。
シンプルに考える
モノゴトの本質は、いつもシンプルです。
DAXに限らず、モノゴトの「基本」をしっかり理解する、これが大切です。
拙著「モダンExcel入門」は、「1次情報」に基づき、モダンExcelを一体理解できるよう、「基本」に忠実に解説しています。
「モダンExcel」の応用を期待される方には物足りないかもしれませんが、「基本」を知りたい方には最適な内容になっていると自負しております。ぜひ、参考にしてみてください。
🌷
■ 拙著「モダンExcel入門」に対して、敵対勢力による卑劣な「Amazonレビューのコメント付き低評価」の結果、「1次情報」を基にした「モダンExcel」の「基本」の浸透に、重大な懸念が生じています。
ぜひ、皆様のお力添えをいただければと思います。ご支援のほど、よろしくお願いいたします。 ■
🌸
「モダンExcel研究所」を楽しむ際の【注意事項】
コンテンツコピー、スクレイピングなど【厳禁】 ☚【検知ツール設置済】☚悪質な場合【法的措置】を講じます
（過負荷によるサーバー障害が生じた経験上、ご協力お願いいたします！）
モダンExcel研究所
フォローお待ちしてます！