スライサーが使えるウォーターフォール・チャートを作る

f:id:modernexcel7:20190831135057p:plain

目標

ピボットテーブル、ピボットグラフはユーザーの操作で自由にレイアウト、集計・分析の組み合わせを変えられる極めて便利なレポートですが、欠点もあります。

　●　ユーザーの操作で変化してしまうので、レポートを固定できない
　●　ピボットグラフはすべてのレポートは使えない

一つ目の点は、社内の多くの人にレポートを送る場合などに、レイアウトが変更されて誤った数字が独り歩きする恐れがあります。

二つの目の点については、残念ながらピボットグラフではウォーターフォールチャート、ヒストグラム、散布図、サンバーストなどのレポートは使用できません。

今回はいったん作成したピボットテーブルをCUBE関数に変換して通常の集計表にすることでこれらの問題を解決します。ただし、ただ変換するのではなく、ピボットテーブルの最大の良さであるインタラクティブ性を残したまま変換します。

技術的なポイント

　●　データモデルからピボットテーブルを作る
　●　スライサーを追加する
　●　CUBE関数に変換する
　●　関数化された表からグラフを作る

実践

サンプルデータとして拙著Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本のデータモデルを使用します。

まずは挿入→ピボットテーブルからピボットテーブルを作ります。この時、このブックのデータモデルを使用するを選びます。
f:id:modernexcel7:20190831095300p:plain

ピボットテーブルのレイアウトは以下のように設定します。今回はウォーターフォールチャートを作るので、列に会計年度を、値に売上前年差異を置きます。

f:id:modernexcel7:20190831100545p:plain

合わせてスライサーも追加しておきます。今回は支店名を追加しました。

f:id:modernexcel7:20190831100517p:plain

これでベースとなるピボットテーブルができました。ここまではいつも通りの手順です。

CUBE関数に変換する

次にこのピボットテーブルを通常のExcel集計表に変換します。ピボットテーブルにカーソルを置いたまま以下の手順を実行します。

ピボットテーブル分析（分析）　→　OLAPツール　→　数式に変換

f:id:modernexcel7:20190831100641p:plain

なお、ピボットテーブルを作るときににデータモデルを選択しなかった場合、グレーアウトされてクリックできません。
f:id:modernexcel7:20190831095130p:plain

これで通常のExcel集計表ができました。
f:id:modernexcel7:20190831132045p:plain

ここでスライサーのボタンをどれか選んでみて下さい。なんと集計表にスライサーのフィルターがかかります！
f:id:modernexcel7:20190831132329p:plain

これでピボットテーブルの良さであるインタラクティブ性を残したまま、Excel集計表ができました。

値の入っているセルをクリックすると、CUBEVALUE関数がスライサーを参照していることが分かります。
f:id:modernexcel7:20190831132423p:plain

なお、CUBEVALUEの引数は以下4つを参照しています。
　①　このブックのデータモデル　"ThisWorkbookDataModel"
　②　メジャー　　　　　　　　　$B4
　③　フィルター　　　　　　　　C$3
　④　スライサー　　　　　　　　スライサー_支店名51

さらに、$B4、C$3はそれぞれ、以下のCUBEMEMBER関数が設定されています。
　$B4　=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[売上前年差異]")
　C$3　=CUBEMEMBER("ThisWorkbookDataModel","[カレンダー].[会計年度].&[2016]")

なお、C$3に直接｢2016｣と指定しても計算結果はエラーになります。CUBEMEMBERでフィルターを指定する必要があります。