公開日 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('データテーブル'[日付 (年)]))結果が計算の種類のときと一致していれば成功です。
