[Excel BI/Power Query] そのフィルター大丈夫ですか？そのピボットテーブル レポート本当に正しいですか？

かなり前に書きかけていた記事を追記・完成させて公開します。

Excel は大量のデータから欲しいデータを絞り込み、集計をするのは得意とするところですし、それを実現するために、SUMIFS 関数などの「数式・関数」を使ったり、オートフィルターやテーブル機能などの「機能」を使ったり、VBA を使ったプログラミングで実現するなど、さまざまな方法を使うことができます。それらの中でも「ピボットテーブル」は本当によく使います。

今回データを「絞り込む」ということに関して、以前、私自身がはまって、その理由が小一時間わからず、あらためて Excel の機能の特性を勉強させられたので、共有したいと思います。特に、ピボットテーブルと取得と変換 (Power Query) のクエリエディタを組み合わせて使っている方は、データの絞り込みの仕組みの違いを意識していないと、以下のような思わぬところで落とし穴にはまるかもしれません。

データソースからデータを絞り込む方法 - ピボットテーブル

実際の元データは4年分の勉強会開催のデータでしたが、ここでは簡単なサンプルを使って説明します。ピボットテーブルの元データとなるデータソースは外部の Web サーバー上から取り出すテーブルですが、Excel 上にサンプルのテーブルを作り、それを元データとするピボットテーブルを作成します。

テーブルからピボットテーブルを作成する

このケースであれば、全部で9件分のデータをピボットテーブルでは「タイプ」のAからFにまとめて集計しています。元のデータソースは「テーブル」で作成しているので、11行目に新しいレコードが追加されても、ピポットテーブルは「更新」するだけで、元データソースの範囲を再指定することなく、追加された新しいレコードを含めた集計結果をピボットテーブルで返します。(これも知らない人が多いので注意です！）

元のデータソースにレコードを追加して、ピボットテーブルでレポートを更新する

いつもやっている、見慣れた操作とその結果です。
このピボットテーブルレポ―トから、タイプの A と B の集計の必要がない場合は、ピボットテーブルの「フィルター機能」を使うと思います。ピボットテーブルで絞込みをするための方法は2つの方法があって、１つは行ラベルにある矢印ボタンからフィルターを設定する方法、もう１つはピボットテーブルのフィールドリストにある「フィルター」を使って絞り込む方法です。ここでは行ラベルのボタンを使ってフィルターをかけてみます。

行ラベルのボタンでフィルターを設定する

何も問題ありませんよね。
「このレポートを作成・提出して終わり」といった Excel の使い方であれば、ピボットテーブルを使って作成するレポートはここで終わりだったと思います。

自分のケースの場合は、上記のサンプルのように Excel のテーブルが元データソースではなく、WebサービスからAPIで取得するものでした。その為、データを取得し、絞り込むところは取得と変換(旧Power Query)のクエリエディタで行っていました。結果、元のデータソースに日々データが追加されるような場合でも、不要なデータ（上記の例ではタイプAとB)を「Power Query エディタ－」で除外し、取得と変換の「更新」ボタンで最新データを取りこんで、新規に追加されたデータをピボットテーブル レポートに表示していました。

さて、サンプルに戻り、上記のケースと同様の状況を作ってみます。データソースのテーブルに新しいデータを追加して、ピボットテーブルを更新してみます。ピボットテーブルではAとBを除くフィルターが設定されている状況です。

データを更新、追加してピボットテーブルを更新する

タイプGの30、Bの30、Hの100を追加します。ピボットテーブルを更新することで、Bはフィルターでチェックを外しているので、集計対象になりません。Gは40から70に更新され、総計も110から140になりました。
ところが、Hのレコードはピボットテーブル レポートに反映されません。

この絞込みのフィルターの条件は「AとBをはずす」ではなく、絞り込むときに存在していたA,B,C,D,E,F,Gからチェックが付いている「C,D,E,F,Gだけを選ぶ」なのです。よって、新規にデータソースに追加されたHはピボットテーブル レポートに反映されないのです。

私がはまったのはまさにこのケースで、「ちょっと楽してピボットテーブル レポートの列フィルタで絞り込み」した結果、除外ではなく、そこに表示されているものだけを選択する、になってしまったため、その後でデータ/ レポートの更新を行っても、追加された新しいタイプのレコードはピボットテーブルに反映されない状態になったのでした。

クエリエディターの絞込みは「除外」（注：基本は・・・です）

ややこしいのは、この Excel の絞り込みの操作と、クエリ エディターでの操作がほぼほぼ見た目が同じということです。クエリエディタで行のフィルターをかける操作は、ピボットテーブルと同じような操作で、列名にあるボダンを使って指定できます。

クエリエディタ―の列名ボタンからフィルターを設定する

繰り返しになりますが、ピボットテーブルのフィルター設定と同じ操作でも、上記の場合、その意味は違っていて、■(すべて選択)が押されている状態から A, B を外した場合は、まさに「AとBを外す」という条件になります。詳細エディターで確認すると、以下のようなM言語による式が記録されています。

フィルターされた行 = Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B"))

上記の式では、Table.SelectRows に それぞれの行の[タイプ]がAではなく、かつ、Bでもない、という条件を引数として与えています。<>は「等しくない」を表します。

よって、全件が入っている元データから「取得と変換」のクエリ エディターを使って行の絞り込みを行い、その絞り込んだ結果を使ってピボットテーブルを作る方法は、レポートに新しいタイプの新規の行がきちんと追加されているので、問題ありませんでした。この使い方の多くは、レポートに不適切なレコードが入ってきたら、クエリエディターのフィルターオプションでそのタイプを除外するように設定して、必要なデータのみレポートで利用していました。

トラブルのケースでは、ついうっかりピボットテーブル側で行の絞り込み条件を設定しそのままにしてしまい、たまたま元のテーブルに新しいタイプのレコードが追加されたことを「知っていた」ので、ピボットテーブル レポートが正しくないことに気が付きましたが、これ、実際のデータは数万件で、毎月追加される件数もかなりの数ですから、気が付かないことのほうが多いかもしれませんね。

しかし、このトラブル、これで終わりではなかったのです。

クエリ エディターの絞り込みは必ず「除外」ではなく選択する数による

ところが、別件でまたピボットテーブル レポートに新しいデータが追加されないケースに遭遇しました。今度はピボットテーブルの列フィルタは使っていません。そこでクエリ エディターを見ると、なんと除外ではなく、Excel のピボットテーブルのフィルタ同様に「選択」になっていました。

= Table.SelectRows(変更された型, each ([タイプ] = "C" or [タイプ] = "D" or [タイプ] = "E" or [タイプ] = "F" or [タイプ] = "G"))

上記の式では、タイプがCまたはDまたはEまたはFまたはGと等しいもの、という指定になっています。これでは11種類めの新規のタイプが追加されてもクエリ エディターによる絞り込みテーブルには追加されず、ピボットテーブル レポートにも追加されません。

サンプルの10種類のタイプをもつレコードを用意して検証してみると以下のような結果になりました。

除外が1つの場合
= Table.SelectRows(変更された型, each ([タイプ] <> "A"))

除外が2つの場合

= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B"))

除外が3つの場合

= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B" and [タイプ] <> "C"))

除外が4つの場合

= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B" and [タイプ] <> "C" and [タイプ] <> "D"))

除外が5つの場合（ここで条件式が変わる）

= Table.SelectRows(変更された型, each ([タイプ] = "F" or [タイプ] = "G" or [タイプ] = "H" or [タイプ] = "I" or [タイプ] = "J"))

以降除外が9までは「＝」の指定

除外が10個の場合＝すべて除外 （また <> が使われる）

= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B" and [タイプ] <> "C" and [タイプ] <> "D" and [タイプ] <> "E" and [タイプ] <> "F" and [タイプ] <> "G" and [タイプ] <> "H" and [タイプ] <> "I" and [タイプ] <> "J"))

なるほど、過半数以上になると条件式が「反転」するようですが、みなさん、これ、意識してますか？ 

これ以来、自分は行のフィルター設定をいぢる場合は、ドロップダウンで表示されるGUIを使って指定しても、かならず fx の数式の条件が = なのか、<> なのかを確認するようになりました。

行フィルターを使用している、みなさんのクエリ エディターやピボットテーブル レポート、本当に新しいレコード含まれていますか？ 

ちょっと怖いですね。

以上、参考になれば幸いです。