鷹尾祥(アキラ)のExcel BI ブログ

Excelの革新的機能であるパワーピボット/パワークエリ/DAXについて紹介します。

選択肢テーブルによる集計の切り替え

選択肢テーブルによる集計の切り替え

f:id:modernexcel7:20190922113035p:plain

目標

今回のテーマは 選択肢テーブルによる集計の切り替えです。

今回はリレーションシップを持たない選択肢テーブルからスライサーを作り、その選択肢をメジャーに組み込んでピボットテーブルをより便利なものにします。

以下の図でいうと、オレンジのスライサーの集計タイプの選択肢で、ピボットテーブルの集計を売上・原価・利益のいずれかに切り替えます。

f:id:modernexcel7:20190922113035p:plain

技術的なポイント

 ● リレーションシップを持たない独立した選択肢テーブルを作る
 ● 選択肢テーブルの値を取ってくる(VALUES)
 ● 1つだけの値が選ばれているかチェックする(COUNTROWS、HASONEVALUE
 ● 条件に応じて表示を切り替える(SWITCH)

実践

とりこむステップ

選択肢テーブルの作成

集計タイプという項目を1つだけ用意し、売上、原価、利益の3行をテキストで並べ、テーブルとして書式設定でテーブルを作ります。

f:id:modernexcel7:20190922071520p:plain

テーブル名は先頭にパラメータ(Parameter)のPを付けて、P_集計タイプにします。

f:id:modernexcel7:20190922072203p:plain

データモデルへの読み込み

ワークシートテーブルはできましたが、このままではDAXで参照できないのでデータモデルに読み込みます。

P_集計タイプテーブルにカーソルを置いてデータ → テーブルまたは範囲からをクリックし、Power Query エディターを開きます。
f:id:modernexcel7:20190922072715p:plain

Power Query エディターが開いたら、何もせずにそのまま閉じて読み込む→閉じて次に読み込むを選びます。

f:id:modernexcel7:20190922072917p:plain

データのインポート画面が現れますので、接続の作成のみを選び、このデータをデータモデルに追加するにチェックを入れてOKを押します。

f:id:modernexcel7:20190922073402p:plain

これで、P_集計タイプデータモデルに追加されました。

Power Pivotアドインがある環境だとダイアグラムビューで確認できますが、以下のようにリレーションシップを持たない孤立したテーブルになっています。

f:id:modernexcel7:20190922074137p:plain

スライサーとしてDAXフィルターコンテキストに値を渡すためだけのテーブルなので、これで大丈夫です。

【別解】選択肢テーブルの作成:空のクエリで作成

なお、P_集計タイプテーブル作成の別解として空のクエリから直接作成することもできます。

まず、データ→データの取得→その他のデータソースから→空のクエリを選びます。

f:id:modernexcel7:20190922074401p:plain

Power Query エディターが開いたら、数式バーに直接以下の式を記入し、Enterを押します。

f:id:modernexcel7:20190922075029p:plain

= #table({"集計タイプ"}, {{"売上"}, {"原価"}, {"利益"}})

すると、以下のようなテーブルが作成されます。

f:id:modernexcel7:20190922075219p:plain

これはテーブルを直接作成する文で、#table内の最初のパラメータ、{"集計タイプ"}は項目名(列名)、次のパラメータの{{"売上"}, {"原価"}, {"利益"}}はその項目内の各レコードとなっています。

あとは、クエリ名を変更して前述の手順と同じ形でデータモデルに読み込みます。

閉じて読み込む→閉じて次に読み込むを選択します。

f:id:modernexcel7:20190922072917p:plain

データのインポート画面が現れますので、接続の作成のみを選び、データモデルに追加するにチェックを入れてOKを押します。

f:id:modernexcel7:20190922073402p:plain

なお、空のクエリで直接作成した場合、ピボットテーブルフィールドリストを見たときに重複表示されないというメリットがあります。

以下のように、ワークシートテーブルから作成したものはワークシートテーブルとデータモデルの二つが、空のクエリで作成したものはデータモデルのもののみが表示されています。

f:id:modernexcel7:20190922080002p:plain

これでテーブルの作成は完了しました。次にスライサーを追加します。

ならべるステップ

次に作成したP_集計タイプテーブルからスライサーを作ります。拙著Excelパワーピボット7つのステップでデータ集計・分析を「自動化」する本』商品別売上推移シートを開きます。

ピボットテーブルにカーソルを移動し、ピボットテーブル分析→スライサーの挿入→すべて→P_集計タイプ→集計タイプの手順で、スライサーに追加します。
この時、データモデルのアイコンのついているテーブルを選ぶことに注意してください。

f:id:modernexcel7:20190922082801p:plain

これでスライサーが追加されました。

f:id:modernexcel7:20190922083201p:plain

ただし、このテーブルはその他のテーブルとはリレーションシップを一切持っていないので、ボタンを選んでもピボットテーブルは一切変化しません。この段階では、ピボットテーブルに対してフィルターコンテキストを変更する術を持っていないからです。

かぞえるステップ

ここからはメジャーを作ってスライサーの値をピボットテーブルに反映させていきます。

スライサーの値を取得する(VALUES)

まず先ほど作成したスライサーで一つの値だけを選択してください。今回は売上を選択します。

f:id:modernexcel7:20190922091609p:plain

次に以下の手順でスライサーで選択された値を表示するためのメジャーを作成します。

フィールドリスト→F_売上明細→右クリック→メジャーの追加

最初に「’」をクリックしてP集計タイプテーブルの集計タイプ列を探しますが、列は選択肢に現れません。

f:id:modernexcel7:20190922100339p:plain

そのままでは列は表示されないので、VALUESという関数を使って表示します。

集計:
= VALUES('P_集計タイプ'[集計タイプ])

f:id:modernexcel7:20190922092034p:plain

メジャーを作成したら、ピボットテーブルの値フィールドに追加します。
すると、スライサーで選択した売上が文字として表示されます。

f:id:modernexcel7:20190922092312p:plain

さらに、原価、利益をスライサーで選択しても、ピボットテーブルが変化することを確認してください。

f:id:modernexcel7:20190922094156p:plain

一つだけの値が選択されているかチェック(COUNTOROWS)

では、スライサーで複数の値を選ぶとどうなるでしょうか?
Ctrlキーを押しながら売上と原価の二つを選んでみてください。次のエラーが表示されます。

f:id:modernexcel7:20190922094304p:plain

要するに、ピボットテーブルのセルには一つの値しか表示できないところに、無理やり二つ以上のデータを置こうとしたことによるエラーです。

このVALUESという関数は少し特殊な関数で、主な機能は指定したテーブルまたは列を、重複を排除したユニークなテーブルに変換するですが、もう一つ、結果が1行・1列だった場合は、テーブルではなく一つの値に変換するという機能を持っています。

つまり先ほど上手くいっていたのは、たまたま結果が一行・一列だったため、その値をピボットテーブルが受け取って表示していたにすぎないのです。

なので、値が1行・1列であるときと、そうでないときとのエラー処理を追加する必要があります。'P_集計タイプ'[集計タイプ]で列を指定しているので、1行か1行でないかを判断すればよいです。
先ほど作成したメジャーを以下のように変更します。

まず、 メジャーを書きかえて、VALUES ( 'P_集計タイプ'[集計タイプ] )のテーブルの行数を出します。

集計:
= COUNTROWS ( VALUES ( 'P_集計タイプ'[集計タイプ] ) )

COUNTROWSは、フィルターコンテキストの中でテーブルの行数をかぞえる関数です。
これで、行数を数えることができました。スライサーで売上、原価、利益を選択して複数行のカウントができることを確認してください。

f:id:modernexcel7:20190922095654p:plain

次にテーブルの行数が1の時は、選択された値を、それ以外の場合はブランクを表示するように変更します。

集計:
=IF (
COUNTROWS ( VALUES ( 'P_集計タイプ'[集計タイプ] ) ) = 1,
VALUES ( 'P_集計タイプ'[集計タイプ] )
)

こうすると、一つだけ項目が選択されている時はその項目を表示し、複数選んだ場合はブランクになるようになりました。
ちなみにDAXのIF文でFALSE(偽)のケースを省略すると、結果はブランクになります。

f:id:modernexcel7:20190922095905p:plain

選択に応じて集計を切り替える(SWITCH)

最後の仕上げに取り掛かります。集計タイプの値に応じて表示する集計を変えます。

集計:
=
IF (
COUNTROWS ( VALUES ( 'P_集計タイプ'[集計タイプ] ) ) = 1,
SWITCH (
VALUES ( 'P_集計タイプ'[集計タイプ] ),
"売上", [売上合計],
"原価", [売上合計] - [利益合計],
"利益", [利益合計]
)
)

あわせて書式に桁区切りも追加します。
f:id:modernexcel7:20190922111458p:plain

これで、スライサーのボタンで集計タイプを切り替えることができるようになりました。
f:id:modernexcel7:20190922111711p:plain

別解:1つだけ選択の判定(HASONEVALUE)

ちなみにHASONEVALUE関数を使って以下の数式にすることも可能です。読んで字のごとく値が一つだけの時にTRUEとなる関数です。
こちらの関数は、COUNTROWSと異なり、テーブルではなく、( 'P_集計タイプ'[集計タイプ] )が直接インプットとなります。

=
IF (
HASONEVALUE ( 'P_集計タイプ'[集計タイプ] ),
SWITCH (
VALUES ( 'P_集計タイプ'[集計タイプ] ),
"売上", [売上合計],
"原価", [売上合計] - [利益合計],
"利益", [利益合計]
)
)

なお、Powre BIではSELECTEDVALUEという関数がダイレクトで使えるそうですが、EXCELではまだ使えないようですね。

そのほかの応用例

今回は、売上、原価、利益という費目でパターン分けしましたが、例えば、単期/累計というスイッチにすることも可能です。

また、この選択肢テーブルは数値も値にセットできますので、1, 1000, 1000000という数字を使って数字の桁調整を行ったり、移動平均のレンジを変更したり、度数分布表の階級幅を変更したりと色々と応用ができます。

参考図書

こちらのテクニックは、以下DAX Patterns』という本で紹介されている「Parameter Table」を応用したものです。こちらの本はDAXを使った典型例を多く紹介しておりますので、興味のある方は是非ご覧ください。

DAX Patterns 2015 by Marco Russo Alberto Ferrari(2014-12-19)

DAX Patterns 2015 by Marco Russo Alberto Ferrari(2014-12-19)

こちらのWebサイトもご覧ください。
www.daxpatterns.com