エクセルピボット 計算の種類 DAX関数式_2_列集計に対する比率

公開日 2023年2月12日 最終更新日 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名の身長 (メジャー) を合計して該当するセルへ値を返す。

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

計算の種類 列集計に対する比率

計算の種類 列集計に対する比率を実践

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

列集計に対する比率の計算式

セルの値 / 列の総計

セルの値 : セルの値
列の総計 : タテ計 (小計のセルがあるときはそれを除く合計)

DAX関数式

セルの値

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

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

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

列の総計 (タテ計)

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

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

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

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

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

結果の見方

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

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

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

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

列集計に対する比率

セルの値 / 列の総計

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

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

詳細計算式

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

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