【Excel】ピボットテーブルで挿入したスライサーで選択中の項目を取得する。

（１） やりたいこと

データテーブルからピボットテーブルで集計し、ピボットグラフを作成。これにスライサーを設定して項目の絞込みと連動するグラフを作る時の話です。

スライサーでどの項目を選択したかはスライサーのボタンの色を見ればわかりますが、最終的なアウトプットにスライサー自体を表示したくない場合、現在スライサーで選択している項目を取得してセルやテキストボックスできればよいと思い考えてみました。

下図が概要をまとめたものです。

（２） 事前準備

まず、（データがテーブルの場合）テーブルデザインタブの中の「ピボットテーブルで集計」からピボットテーブルを作成します。

データの列項目は複数ありますが、今回は月別の売上高推移の棒グラフを作りたいので、ピボットテーブルの行に「年月」、値に「売上高」をセットします。

ピボットグラフとスライサーを追加した状態（説明は省略します）が下図となります。スライサーは分類としました。

この状態で例えばスライサーの「果物」を選択するとピボットテーブル、グラフともに連動して「果物」だけの数値に絞り込まれます。

（３） スライサー選択項目の取得方法

（以下、説明用にすべて一つのシートで進めていきますが、実務ではグラフ・スライサーとピボットテーブル、数式は別シートに分けるのが無難だと思います。）

まず、元データからピボットテーブルを追加します。行にはスライサーと同じ項目「分類」を、値は「売上高」をセットします。（値はなんでも構いません）

スライサーを選択した状態で右クリックし「レポートの接続」をクリックします。

二つのピボットテーブルの左端のチェックボックスを選択してＯＫをクリックします。

これで二つのピボットテーブルが接続されます。

試しにスライサーで「果物」と「肉類」を選択すると、下のピボットテーブル2も連動して果物と肉類に絞り込まれます。

ここからセルに数式を入れていきます。E26、F26の「分類_一意」「ｽﾗｲｻｰ選択判定」は手入力した見出しです。

まず、E27セルに元データの分類が一意となるように次の数式を入力します。

=SORT(UNIQUE(data[分類]))

UNIQUE関数でdata（テーブル名）の分類列の一意の値を取得した結果をSORT関数で降順に並び替えています。

下図の通り表示されました。スピルされますのでテーブルに新しい分類が追加された場合も自動的にこの数式の結果に追加されます。

次にとなりのF27セルにスライサー選択判定用の数式を入力します。

=IF(ISERROR(GETPIVOTDATA("売上高",$B$25,"分類",E27)),"",E27)

GETPIVOTDATA関数でピボットテーブル2の分類にE27の値がなければエラーとなるので、IFとISERROR関数を使いエラーの場合はブランクとし、値がある場合はE27の値を返すようにします。

果物は選択されているので「果物」が返ります。

F27の数式を下にコピーします。分類数は3つですが、データに新規分類が追加されても対応するように適当なところまでコピーしています。（が、ここはもっとよい方法があるのではないかと・・納得できていません）

これでスライサーで選択中の項目を取得することができました。

試しに「魚介類」に絞り込むと下図の通り正しく表示されました。

最後に、取得した判定結果を一つのセルに表示するための数式をE1セルに入れてみます。

=TEXTJOIN(",",TRUE,INDIRECT("F27:F"&26+COUNTA(E27#)))

やりたいことはF27～F29に表示された文字列をTEXTJOIN関数で「,」で区切って繋げたいだけなのですが、新規分類が発生した場合にセル参照範囲が変わってしまうので、COUNTA関数でE27以下に表示された分類数を数え、INDIRECT関数でF列の参照に変換しています。（OFFSET関数等を使って別の書き方ができるかもしれません）

数式の結果がE1セルに表示されました。

複数の項目を選択すると下図のようになります。

あとは、先ほどの数式にタイトルなど任意のテキストを追加します。

下図のように表示できます。

（４） 最後に

今回はひとつのスライサー項目のみとしましたが、複数のスライサーの項目を取得する場合はもうひと手間必要です。

試行錯誤しながら考えましたが、実はもっと簡単にピボットテーブルのスライサーの選択項目を取得する方法があるかもしれません。