FILTER関数は、安易に使わない方が良い

FILTER関数に関する「一次情報」を確認

「モダンExcel」を難しくする原因には、いくつもの「複合要因」があります。
言い換えれば、「DAX」の解釈を難しくしている原因の一つに、「一次情報」にない、不必要なDAXの記述がなされているということが大きな要因の一つだと思います。

中でも「FILTER関数でフィルターする」という解釈がまかり通っていますが、これは「悪手」となることもあります。

FILTER 関数 (DAX) - DAX詳細については、以下をご覧ください。FILTER
FILTER 関数 (DAX) - DAX docs.microsoft.com
FILTER 関数 (DAX) - DAX

「一次情報」は大切です。必ず見るようにしましょう!
「一次情報」の引用がないコンテンツは、疑ってかかる必要もあります。

日本語で書かれた、あるいは日本語の動画など、「モダンExcel」界隈の情報には「う~ん……。」と唸ってしまうような、「一次情報」とは相容れないレベルのコンテンツが散見されます。
そうしたコンテンツの中には、「相対パス」などの最悪、情報漏洩するリスクのある内容も含まれていますので注意してください。

ソフトにしろ、ハードにしろ、設計者の意図があり、その意図に従う限り、問題は生じません。
しかし、設計者=マイクロソフトの意向に反して「モダンExcel」を使うと、とんでもないことが起こることもあるということは知っておく必要があります。

ビジネスで「モダンExcel」を使うのであれば、リスクのない、設計者の意図に従った、既定の=安全な使い方をすべきです。
設計者の意図に従った使い方であれば、「レガシーエクセル」よりも「モダンExcel」ははるかに効率的・効果的にデータを収集・分析してくれます。

FILTER関数を安易に使いたがる原因は、これではないだろうか?

そもそも、FILTER関数を使いたがるのは、なぜ?

恐らく、「SUMX関数」の説明が原因なのでは、というのが有識者の見解です。

たしかに~、そうかもね…。

「Data Analysis Expressions (DAX) リファレンス」に出てくる「SUMX関数」の解説に、次のDAX式が出て来ます。

= SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])
SUMX 関数 (DAX) - DAX詳細については、以下をご覧ください。SUMX
SUMX 関数 (DAX) - DAX docs.microsoft.com
SUMX 関数 (DAX) - DAX

上記式を見れば、確かに「FILTER関数」でフィルターしています。
しかも、DAX関数の基本の一つ「SUMX関数」の「一次情報」の解説にあるので、目につきやすくもあります。
そうすると、「一次情報」にあるから「これが、最適なんでしょ」、そう思うのも仕方がないことなのかもしれません。

実は「一次情報」には、古い情報の記述が残っている場合もあって、必ずしも解説の記述が最適でない場合もあるのです(注)。

(注)あえて古い情報を残している理由の一つに、原理原則の理解をMSが促進している側面もあると思います。

巷でよく見かける「FILTER関数を使う」「フィルターあり」のDAX事例

今回使うデータモデルはこんな感じ。あまりきれいではないのはご愛敬。

「フィルター関数」が悪手であることを検証するため、2つのDAX式、「フィルターあり」と「フィルターなし」という「メジャー」を作成、比較します(注)。

(注)次のコード自体に実務上の意味はほとんどありません。単なる比較用、説明用として割り切ってください。

一つ目のメジャーは、その名を「financials[フィルターあり]」としました。
CALCULATE関数で、financialsテーブルの[ Sales]列を、DISTINCT関数で一意の値にしたものを数え、その際、FILTER関数で、financialsテーブルの[COGS]列(コスト)が「100000より大きいもの」だけを対象に計算するという計算内容です。

    financials[フィルターあり] =
        CALCULATE (
            DISTINCTCOUNT ( financials[ Sales] ),
            FILTER (
                financials,
                financials[COGS] > 100000
            )
        )

結果はこんな感じ。

CALCULATE関数はFILTER関数という常識

拙著「モダンExcel入門」(日経BP)の読者であれば、こうなるはずです。

え!?なんで、そこでFILTER関数を使うの…。

おいおい、そこはCALCULATE関数で十分じゃろ!

というのも、CALCULATE関数は「フィルター関数」だからです。

CALCULATE 関数 (DAX) - DAX詳細については、以下をご覧ください。CALCULATE
CALCULATE 関数 (DAX) - DAX docs.microsoft.com
CALCULATE 関数 (DAX) - DAX

上記「一次情報」、拙著「モダンExcel入門」(日経BP)のP150~「「CALCULATE」は「フィルター関数」」などを理解すれば、上述のDAX式は次のように書き換えることができます。

financials[フィルターなし] =
        CALCULATE (
            DISTINCTCOUNT ( financials[ Sales] ),
            financials[COGS] > 100000
        )

結果は次の「フィルター関数使用なし」のとおり。
先の「フィルター関数使用あり」と並べてみると、当然ですが「結果」は一致します。

結果がおんなじなら、そんなに目くじら立てなくてもいいじゃないの?

そうとも言えないんだよ…。

フィルター関数を使うのは、悪手!

巷にあふれかえる「フィルター関数」を使ったDAX式では、最悪、計算結果をはじき返さないことがあります。
データ量が多くなるにつれ、計算負荷がかかることが要因の一つです。

下記は最初に示した「フィルターあり」の、巷でよく見かけるDAX事例の結果を「可視化」したものです。
トータルで「14ms(ミリ秒)」かかっています。
今回は非常に小さな700件のデータなので、それほど影響ありません。

ただし、その計算プロセスを見ると、なんと「17行」もあります!

つまり、「フィルター関数」を使うと、これだけ多くの計算プロセスを経て、計算結果を得ることになってしまうという問題点があるのです。
だから、FILTER関数は使うべきではない、そうなります。

非効率、極まりないね!

「正しいモダンExcel」では、フィルター関数はよくよく考えてから使う!

他方、拙著「モダンExcel入門」(日経BP)でもご紹介する、「フィルターなし」の場合がこちらです。

ご覧いただけば、一目瞭然。
計算プロセスは、わずかに「1行」のみ!
計算にかかった時間も、たった「4ms(ミリ秒)」です。

今回は700件の事例ですが、これが1億行あることを考えてみてください。
最悪、計算できない、こうしたことも現実に起こり得るわけです。

スタックオーバーフロー発生!

次の「一次情報」にも「フィルター引数として FILTER を使用しない」という指摘が、はっきり書いてあります。
ご一読ください。

DAX でフィルター引数として FILTER を使用しない - DAXフィルター引数としての FILTER 関数の使用に関するベスト プラクティス。
DAX でフィルター引数として FILTER を使用しない - DAX docs.microsoft.com
DAX でフィルター引数として FILTER を使用しない - DAX

DAX式でどうしてもフィルター関数を使わなければいけない、そうした場合も現実にあります。
ただ、これも「データモデル」を変更すれば対処可能です(割愛)。

【補足】次のようなツイートがありました。

ツイート主は、勉強会でご一緒させていただくことも多いEiki Suiさんです。
ありがとうございます。
これに対し、PowerBIの師匠であるTakeshi Kagataさんからもツイートがありました。

いつも的確なご指摘、ご指導、ありがとうございます。

本稿リンク先に上記指摘に関する内容も含まれますので、読者にはリンク先も読み込んでいただけば幸いです
ここでの議論は、安易にFILTER関数を使うのは良くないよ!基本をしっかりお願いします!という注意喚起。
しかし、よく考えずFILTER関数を使う事例が、本当に多いんですよね。

なんちゃってモダンエクセル、それ大丈夫?

断言します!
あなたの「モダンExcel」が遅い理由は、「なんちゃってモダンエクセル」を使ってしまっているからです。

「なんちゃってモダンエクセル」派の解釈は、「たまたま」結果を返すだけということが多い……。
DAXに対する理解不足、別の言い方をすれば「データモデル」に対する理解不足、こうしたことが原因の根底にあるとも言えるでしょう。

「なんちゃってモダンエクセル」派は、「予算」と「実績」のような「二つのファクトテーブル」を用いたデータモデル(下図中央参照)を「ダイヤモンド型」などと独自の表現をしたりもします。
こうした正規表現ではない独自呼称を見ると、「あ~、この人は、データモデルに対する基本的な理解がないね……。」という具合に、「正しいモダンExcel」や「データモデル」を知っている人たちからすればなるわけです。

正しくは「ギャラクシーモデルのダブルファクトテーブル」ですよ!



「予算」と「実績」の比較計算をするのに「パワクだけで!」というのが、そもそも間違いです。
なぜなら、「なんちゃってモダンエクセル」派が「パワク」と称するPower Query、エクセルでは「データの取得と変換」という機能は、メモリーを大量消費します。
つまり、「パワク」だけでこうした計算をしようとすると、処理に時間がかかってしまうのです。
「正しいモダンExcel」を理解している人であれば、もう一つの「モダンExcel」ツールである「Power Pivot for Excel」で「データモデル」にして解決することを知っています。

他にも「なんちゃってモダンエクセル」派の問題点は、満載です……。
「パワクでやって、あとはVBAにお任せ!」とか、「ワークシートで、カレンダーテーブルを作ればよい!」とか、「相対パス、これ便利だぞ!」とか、とにかく「一次情報」と相容れない「なんちゃってモダンエクセル」が巷を席巻しているのは、なぜなのというお気持ち……。

あなたの「モダンエクセル」は、なぜ、思うような計算結果を示してくれないのか?!|モダンExcel研究所(モEx研) 「104万行の壁なんて、もうないよ!」|note「データ分析&可視化の新しい教科書 モダンExcel入門」(日経BP)の作者が教える、「モダンExcel」で特に留意すべき事項は3つあります。 ①正しい「カレンダーテーブル」の作成 ②正しい「データモデル」の理解 ③モダンExcel「2大ツール」の上手な使い分け ここでは、これら「正しいモダンExcel」の基本となる3点を取り上げ解説します。 というのも、これら3つは「正しいモダンExcel」を取り扱う際に、「最低限」守らなければいけない「基本」の「型」だからです。 こうした「基本」を知ることは、あなたの「モダンExcel」そしてその先にある「Power BI」の理解にも、きっ
あなたの「モダンエクセル」は、なぜ、思うような計算結果を示してくれないのか?!|モダンExcel研究所(モEx研) 「104万行の壁なんて、もうないよ!」|note note.com
あなたの「モダンエクセル」は、なぜ、思うような計算結果を示してくれないのか?!|モダンExcel研究所(モEx研) 「104万行の壁なんて、もうないよ!」|note

本件「フィルター関数」の事例を見て、読者の皆さんもよく考えて欲しいと思います。
「一次情報」を基にした「正しいモダンExcel」と「なんちゃってモダンエクセル」は、まったくの別物です。
「なんちゃってモダンエクセル」派の主張はリスクが高すぎるので、ビジネスで使うべきではないことを申し添えます。

「なんちゃってモダンエクセル」を広めた関係者は、
世間に申し開きすべき!猛省を促す!

「モダンExcel入門」に対する感想を紐解くと分かることもある

拙著「モダンExcel入門」(日経BP)の、どんな点が読者をひきつけているのか、「書評」「コメント」などを集めてみました。

「モダンExcel入門」に対する、好意的な「書評」「コメント」だけ!集めてみた|モダンExcel研究所(モEx研) 「104万行の壁なんて、もうないよ!」|noteここでは、当方並びに拙著「モダンExcel入門」(日経BP)に対する、好意的な「書評」や「コメント」だけ!掲載します。 これも「ファクト」です。 Amazonでは「悪意に満ちた低評価レビュー」が多く、読者の皆様に別の「ファクト」にも目を向けていただくため、一石を投じます。 拙著「モダンExcel入門」に対する評価は、読者の皆様の「賢明なご判断」に委ねたいと思います。 なお、好意的な「書評」「コメント」、お待ちしております! 特に、Amazonでの「高評価」「好評価」レビューをいただけると、うれしいです。 いただいた方には、この場を借りて「感謝の意」を表し、当方は小躍りしなが
「モダンExcel入門」に対する、好意的な「書評」「コメント」だけ!集めてみた|モダンExcel研究所(モEx研) 「104万行の壁なんて、もうないよ!」|note note.com
「モダンExcel入門」に対する、好意的な「書評」「コメント」だけ!集めてみた|モダンExcel研究所(モEx研) 「104万行の壁なんて、もうないよ!」|note

色々な意見の中には、確かに否定的な意見もあります(上記リンクでは、あえて取り上げていません)。
その多くが「分かりにくい」「簡単すぎる」という2点に集約されます。


①「分かりにくい」という意見について

恐らく「レガシーエクセル」の「セル」思考が染みついてしまっているからだろうと推測します。
「モダンExcel」は「セル」ではなく、Accessのように「列」の思考回路が必要になるので、「分かりにくい」となるのでしょう。
VBAに長けた方の中に「ベタ打ちの表が良い。テーブルなんて使うべきではない」と断言される方もいますが、こうした思考回路の根底に「セル」で考える癖が染みついてしまっているわけです。
「データベース」の一環としてエクセルを捉えるのであれば、「列」で考えるべきです。
「列」の思考回路を身につけることが、「モダンExcel」を理解することの始まりになると思います。


②「簡単すぎる」という意見について

恐らく「なんちゃってモダンエクセル」の影響が大きいと思います。
「なんちゃってモダンエクセル」では、とにかく複雑なDAX式を書くため、一見して簡単なDAX式のみの拙著は読者からすれば物足りないのかもしれません。
ただし、「モダンExcel」の本質を理解すれば、おのずと「メジャー」と呼ぶDAX式は簡単な記述になります。
逆説的に言えば、複雑なDAX式を「入門書」で取り上げるのは、「入門者」の理解を阻害する要因にすらなりえます。
「モダンExcel」入門者は、簡単で、基本的なことを、「一次情報」あるいはそれに基づく「正しい情報」を読み込んで、実際に自分の手を動かしつつ、しっかり基本を理解することが大切だと思います。
なによりも、「モダンExcel」の二大ツールである「Power Query」と「Power Pivot for Excel」、加えて「レガシーエクセル」のピボットテーブル等の機能を「三位一体理解」することが大切です。

拙著「モダンExcel入門」(日経BP)では、この「三位一体理解」の重要性を説いています。
中でも、上述「CALCULATE関数は、フィルター関数」ということを理解することこそが「モダンExcel」の理解、そしてその先にあるデータビジュアライジング(可視化)ツール「Power BI」の基本になります。

CALCULATE関数がDAX攻略のポイントの一つだ

繰り返し、繰り返し、「CALCULATE関数」の理解をしていく、こうしたことが大切です。
というのも、「CALCULATE関数」は、DAX関数の基本であり、中心的存在の関数だからです。
しかも、その機能は定期的に更新し続けてもいます。

したがって、定期的に「CALCULATE関数」に関する知識をブラッシュアップし続ける必要もあります。
DAXの正しい理解は「CALCULATE関数に始まり、CALCULATE関数に終わる」、そう言っても過言ではないでしょう。

その意味で、CALCULATE関数をはじめとする基本のDAX関数だけを解説する拙著「モダンExcel入門」(日経BP)は、一見すると簡単な内容にとどまっているので、読者からすると「物足りない」と見えてしまうのかもしれませんね。

でも、上述のような背景が分かれば、「物足りない」くらいがちょうどよいと思います。
拙著の読後は「一次情報」を見よう、そうなってくれることを期待もします。

Data Analysis Expressions (DAX) リファレンス - DAXData Analysis Expressions (DAX) は計算式の作成のために組み合わせることができる関数と演算子のライブラリです。
Data Analysis Expressions (DAX) リファレンス - DAX docs.microsoft.com
Data Analysis Expressions (DAX) リファレンス - DAX

不都合な真実

否定的な意見には、いくつかの特徴が見られます。

・拙著「販売直後」に、
・ネット通販「最大手」Amazonで、
・いずれも「なんちゃってモダンエクセル」派による、
・「低評価レビュー」ばかり。

なぜ、彼らが拙著に対し「否定的なコメント」をせざる得ないのか?
賢明な読者の貴方ならば、もうお分かりだと思います。

「なんちゃってモダンエクセル」派からすれば、「正しいモダンExcel」の存在が煙たいわけです。

本件「フィルター関数」事例のような「正しいモダンExcel」が世に知れ渡ることになれば、「なんちゃってモダンエクセル」派の主張に「誤り」や「落ち度」が多数存在していることが世間に露呈してしまいます。
「なんちゃってモダンエクセル」派からすれば、彼らの主張の根幹が崩れ、彼らにとって「不都合な真実」が暴露されてしまうことを恐れているのだろうと思います。
だから、ネットで書籍を購入されることの多い「Amazonで低評価レビュー」を繰り返すわけです。
拙著が売れなければ、彼らの主張が通りやすくなる、そう考えれば合点がいきます。

この本(拙著)は、★が低いね。
だったら、こっちの方が良いかもね、
★も高いし、レビューもいっぱいあるし、安心だね!

ヒトの心理を巧みに操るのに長けた「なんちゃってモダンエクセル」主宰者は、拙著上梓を契機に不躾に「丸パクりしやがって!」と、当方身に覚えのない真っ赤な嘘のツイートで因縁をつけてきて、影響力のあるインフルエンサーを多数巻き込みながら、大々的にネガティブキャンペーンを行ってきました。
それが今でも繰り広げ続けられる事実には、閉口し、唖然とし、困惑もします。

繰り返します。
「なんちゃってモダンエクセル」派の主張には「フィルター関数」をはじめ、様々な問題があります。
その中には、彼らが主張する「相対パス」事例のように、最悪、情報漏洩に繋がる案件も含まれます。

賢明な読者は、「なんちゃってモダンエクセル」派に惑わされないで欲しいと思います。
悧発な読者は、マイクロソフトが提供するDocsなどの「一次情報」を読み込むようにしてください。
「英語が~」「面倒だ~」「難しい~」という方は、「一次情報」を引用するコンテンツを参照するようにしてほしいと思います。

拙著「モダンExcel入門」(日経BP)は、マイクロソフトの「一次情報」に基づき解説しています。
サンプルファイル付きなので、手を動かしながら、理解を深めていただける工夫もあります。
実際に書店でお手に取っていただければ幸いです。

■■■■■

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

■■■■■

モダンExcel研究所

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

error: Content is protected !!