エクセルピボット 計算の種類 DAX関数式_13_指数 (インデックス)

公開日 2023年2月19日 最終更新日 2023年2月20日

はじめに

データについて

<無料> 建築着工統計調査_表1_用途別_全国_月次 (列 – 複数値形式)

政府統計の総合窓口(e-Stat)(https://www.e-stat.go.jp/
「建築着工統計調査」(国土交通省)を基にデータ製作所が作成

データをダウンロードしたページ

 こちらのページを参照、データをダウンロードすると本稿のとおり再現できます。

ことばの定義

・データテーブル : ピボットで集計する前のもとデータのこと (下の画像)。ピボットテーブルのことではありません。
・ディメンション : データテーブルの項目フィールド、「日付」「地域」「用途」を指します。
・メジャー : データテーブルの値フィールド、「床面積の合計 (㎡)」「建築物の数 (棟)」「工事予定額 (万円)」を指します。本稿では、メジャーは「建物の数 (棟)」だけを使用しています。
・集計 : 値を計算すること。合計とは限りません。
・値 : ディメンションの値、メジャーの値、数値とは限りません。

データテーブル
コンテキストとフィルター

・コンテキストフィルターが適用されていない : 下画像の左側のピボット
 「行」「列」ともにコンテキストがなく、メジャーを集計 (合計) している状態です。コンテキストがない、つまり、コンテキストフィルターが適用されていない。

 なお、エクセルでコンテキストフィルターとうい言葉は一般的に使用されず、単にフィルターと表現されます。ディメンションフィルターとの誤用を避ける目的で、本稿ではコンテキストフィルターとしています。
 タブローのコンテキストフィルターは常にディメンションフィルターの上位になりますが、エクセルは使用する関数次第で上位にも下位にもなります。

・コンテキストフィルターが適用されている : 下画像右側のピボット
 行は「用途」の「A住居専用住宅」「B住居専用準住宅」で、「日付 (年)」は「2020」「2021」「2022」のコンテキストフィルタが適用されている。
 列は「建築主」の「国」「市区町村」「都道府県」のコンテキストフィルターが適用されている。

・セルの値 : ピボットの行列がクロスしたメジャー集計値。下画像でH4の値 (セルの値) は171です。

 コンテキストフィルターが適用されているされているH4のセルの値とは、
*「用途」=「A住居専用住宅」AND「日付 (年)」=「2020」AND「建築主」=「国」
 このコンテキストフィルター条件を満たすメジャーの集計 (合計) 値といえます。

・ディメンションフィルター : ディメンションのいくつかの項目を選択している状態
 ピボットは「用途」「日付 (年)」「建築主」でディメンションフィルターを適用しています。
 本稿は、常にディメンションフィルターを適用していることを前提し説明しています。説明の都度にディメンションフィルターについて記述していません。
 なお、メジャーフィルターは適用していません。

 本投稿は、このような定義と、画像左右のピボットの変化をもとに解説しています。

集計方法

 計算の種類の前段階である集計方法は「合計」です。「SUM」「SUMX」のような合計値を返す集計関数を使用するという意味です。

<ピボットのイメージ>
 ある会社には複数の部があります。
・ディメンションフィルター : 複数の部から営業部を選ぶ。

・コンテキスト : 営業部員の性別や苗字や出身地のデータテーブルから、クロス集計表をつくる。
 行列がクロスするセルへは、男性・佐藤さん・東京出身のようなコンテキストフィルター条件を満たす値だけが入る 。女性・佐藤さん・東京出身ならば、その条件を満たすセルへ値を返す。

・メジャー集計 (合計) : 男性・佐藤さん・東京出身が2名いたら、2名の身長 (メジャー) を合計して該当するセルへ値を返す。

 ピボットテーブル・ピボットグラフは、コンテキストに従いメジャーを集計する機能といえます。

計算の種類 指数

計算の種類 指数を実践

 計算の種類から「指数」を選択すると、基準フィールドのうちのどれかが自動選択されて他のディメンションはグレーアウトします。(どれが選ばれるのか?は、事前の操作によるところなのですが、ここでは説明を割愛します。結論をいうと、どれを選択しても同じ結果になります。)

指数の計算式

((セルの値) x (総計)) / ((行の総計) x (列の総計))

セルの値 : セルの値
総計 : ピボットに返しているすべてのセルの合計値 (小計のセルがあるときはそれを除く合計)
行の総計 : ヨコ計 (小計のセルがあるときはそれを除く合計)
列の総計 : タテ計 (小計のセルがあるときはそれを除く合計)

DAX関数式

セルの値

 メジャー「建物の数 (棟)」を集計 (合計) するとセルの値を得ることができます。なぜ集計関数「SUM」が必要なのかというと、データテーブル (ピボットのもとになっているデータ) の「建物の数 (棟)」は複数行あるからです。

=SUM('データテーブル'[建築物の数 (棟)])

 集計結果は画像左側のピボットの値と一致します。

総計

 現在のピボットの行は「用途」「日付 (年)」、列は「建築主」のコンテキストフィルターが適用されています。
 現在の、行と列のコンテキストフィルターをすべて無視しながらメジャーを集計 (合計) することで総計をすべてのセルへ返すことができます。
 はじめの説明のように、行・列ともにコンテキストフィルターが適用されていない状態で「建物の数 (棟)」を集計 (合計) し、すべてのセルへ値を返すわけです。

=SUMX(ALLSELECTED('データテーブル'),[建築物の数 (棟)])

SUMX : フィルター条件付きで「[建築物の数 (棟)]」を合計する集計関数

ALLSELECTED : 現在の、すべてのコンテキストフィルターを無視する (メジャーフィルターは無視しない) という条件を定義するフィルター関数

結果の見方

 左側のピボットのセルB4は、
「用途」=「A住居専用住宅」AND「日付 (年)」=「2020」AND「建築主」=「国」
 このコンテキストフィルター条件を満たすメジャーの集計 (合計) 値です。

 同様にセルC4は
「用途」=「A住居専用住宅」AND「日付 (年)」=「2020」AND「建築主」=「市区町村」

 それぞれのセルの値は、それぞれのコンテキストフルタ―条件を満たすメジャーの集計 (合計) 値です。

 総計の場合は、各セルに適用されている行・列のコンテキストフィルターをすべて無視してメジャーを集計 (合計) すれば、すべてのセルへ総計を返すことができます。

行の総計 (ヨコ計)

 行の値は1行ごとに横へ並んでいます。横へ並んだ値を集計 (合計) するから1行ごとのヨコ計が行の総計です。

 現在のピボットの行は「用途」「日付 (年)」、列は「建築主」のコンテキストフィルターが適用されています.。
 現在の、行と列のコンテキストフィルターのうち、列に設定されている「建築主」のコンテキストフィルター無視することで行の総計を算出できます。

=CALCULATE(SUM('データテーブル'[建築物の数 (棟)])
,ALLSELECTED('データテーブル'[建築主]))

CALCULATE : フィルター条件付きで「[建築物の数 (棟)]」の合計を集計するフィルター関数

ALLSELECTED : 現在の、列で使用されている「建築主」のコンテキストフィルター (メジャーフィルターは無視しない) を無視するフィルター関数

結果の見方

 左側のピボットのセルB4は、
「用途」=「A住居専用住宅」AND「日付 (年)」=「2020」AND「建築主」=「国」
 このコンテキストフィルター条件を満たすメジャーの集計 (合計) 値です。

 同様にセルC4は
「用途」=「A住居専用住宅」AND「日付 (年)」=「2020」AND「建築主」=「市区町村」

 それぞれのセルの値は、それぞれのコンテキストフルタ―条件を満たすメジャーの集計 (合計) 値です。

 例えば、セルH1では
・「用途」=「A住居専用住宅」を適用する
・「日付 (年)」=「2020」を適用する
・「建築主」=「国」は無視する

 各セルに適用されている列のコンテキストフィルター (「建築主」) を無視し、行の「用途」と「日付 (年)」に従ってメジャーを集計 (合計) すれば、すべてのセルへ行の総計が返ります。

「建築主」が「国」「市区町村」「都道府県」いずれであろうが、これらを無視するからすべてのセルの値は行の総計と一致します。

列の総計 (タテ計)

 列の値は1列ごとに縦へ並んでいます。縦へ並んだ値を集計 (合計) するから1列ごとのタテ計が列の総計です。

 現在のピボットの行は「用途」「日付 (年)」、列は「建築主」のコンテキストフィルターが適用されています.。
 現在の、行と列のコンテキストフィルターのうち、行に設定されている「用途」と「日付 (年)」のコンテキストフィルター無視することで列の総計を算出できます。

=CALCULATE(SUM([建築物の数 (棟)]),ALLSELECTED('データテーブル'[用途]),ALLSELECTED('データテーブル'[日付 (年)]))

CALCULATE : フィルター条件付きで「[建築物の数 (棟)]」の合計を集計するフィルター関数

ALLSELECTED : 現在の、行で使用されている「用途」と「日付 (年)」のコンテキストフィルターを無視する (メジャーフィルターは無視しない) フィルター関数

結果の見方

 左側のピボットのセルB4は、
「用途」=「A住居専用住宅」AND「日付 (年)」=「2020」AND「建築主」=「国」
 このコンテキストフィルター条件を満たすメジャーの集計 (合計) 値です。
 セルB4と同様にセルB5の値もセルB6の値も、すべてのセル値は、それぞれが、それぞれのコンテキストフルタ―条件を満たすメジャーの集計 (合計) 値です。

 例えば、セルH1では
・「用途」=「A住居専用住宅」を無視する
・「日付 (年)」=「2020」を無視する
・「建築主」=「国」は適用する

 各セルに適用されている行のコンテキストフィルター (「用途」と「日付 (年)」) を無視し、列の「建設主」だけに従ってメジャーを合計すれば、すべてのセルは列の総計の値になります。

 「用途」が「A居住専用住宅」であろうが「B居住専用準住宅」であろうが、「日付 (年)」が「2020」「2021」「2022」いずれであろうが、これらを無視してすべてのセルの値が列の総計と一致します。

指数 (インデックス)

 ここまで解説してきた値を次の計算式にあてはめます。

((セルの値) x (総計)) / ((行の総計) x (列の総計))

・セルの値 : すべてのセルの値がセルの値の状態
・総計 : すべてのセルの値が総計の状態
・行の総計 : すべてのセルの値が行の総計の状態
・列の総計 : すべてのセルの値が列の総計の状態

 通常のエクセルはセルの値とセルの値を計算する。ピボットのときはセルの状態とセルの状態を計算するといえます。

=(SUM('データテーブル'[建築物の数 (棟)])
*SUMX(ALLSELECTED('データテーブル'),[建築物の数 (棟)]))
/(CALCULATE(SUM('データテーブル'[建築物の数 (棟)]),ALLSELECTED('データテーブル'[建築主]))
*CALCULATE(SUM([建築物の数 (棟)]),ALLSELECTED('データテーブル'[用途]),ALLSELECTED('データテーブル'[日付 (年)])))

 結果が計算の種類のときと一致していれば成功です。