エクセルピボット計算の種類 DAX関数式_4_基準値に対する比率

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

はじめに

データについて

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

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

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

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

データ製作所

＜無料＞　建築着工統計調査_表1_用途別_全国_月次 2011年1月 - 2024年2月 (列 - ...

https://data-seisaku.com/00600-mlit-00600120-hyo1-extract-listm

ことばの定義

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

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

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

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

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

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

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

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

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

集計方法

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

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

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

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

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

計算の種類基準値に対する比率

特定の年を実践

　「基準値に対する比率」を選択すると、基準フィールドでディメンションを選択できます。また、基準アイテムも選択可能です。

　ここでは、基準フィールドを「日付 (年)」、基準アイテムを「2020」に設定して説明します。
得られる値は、2020年対比になります。

計算式

セルの値 / 2020年の値

セルの値 : セルの値
2020の値 : 「日付 (年)」が2020の行の値

DAX関数式 2020の値

セルの値

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

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

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

2020年の値

　現在のピボットの行は「用途」「日付 (年)」、列は「建築主」のコンテキストフィルターが適用されています。
　現在の、行と列のコンテキストフィルターのうち、行に設定している「日付 (年)」が2020ではないときにコンテキストフィルターを無視してメジャーを集計 (合計) することで2020年の値をセルへ返すことができます。

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

IF : 日付が2020のときだけ「建物の数 (棟)」を合計する論理関数

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

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

結果の見方

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

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

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

　例えば、セルH1では
・「用途」＝「A住居専用住宅」を適用する
・「日付 (年)」＝「2020」を適用するが、「2020」でないときは適用しない
・「建築主」＝「国」はする適用する

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

基準値 2020の値に対する比率

セルの値 / 2020値の値

・セルの値 : すべてのセルの値がセルの値の状態
・2020の値 : それぞれの列の値 (列の値はタテに並んでいる) が2020の値の状態

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

詳細計算式

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

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

計算の種類前の値に対する比率

前の値を実践

　基準値に対する比率」を選択すると、基準フィールドでディメンションを選択できます。また、基準アイテムも選択可能です。

　ここでは、基準フィールドを「日付 (年)」、基準アイテムを「前の値」に設定して説明します。
得られる値は、前年対比になります。

計算式

セルの値 / 前のセルの値 (前の年の値)
　2020年は前の値がない (null) のだから本来は計算不可能なのですがピボットでは100%を返します。

DAX関数式前の値

前の値

セルの値は前で説明しているので割愛します。

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

　ただし、日付を年でグループ化しているので話がややこしくなります。

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

(注) タイムインテリジェンス関数で使用できるパラメーター (値) は「日付」だけです。ところがピボットは「日付」を「日付 (年)」でグループ化しています。「日付 (年)」とは日付という数値ではなく、文字列です。
　ということで、日付でメジャーを集計しながら文字列のコンテキストへ値を返すという技が要求されます。

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

DATEADD : 現在のコンテキストの日付から、前年にシフトした日付の値を返すタイムインテリジェンス関数
　「現在のコンテキストの日付から」ということは、例えばデータテーブルの最終の日付が11月30日のとき、前年は、前前年の12月1日から前年の11月30日になります。
　そうなると、2022年の前年のメジャーは2020年12月1日～2021年11月30日の集計になります。つまり、テータテーブルの最終日付次第で1年が動的に定義されます。一方で「日付 (年)」は文字列ですから静的です。
　通常の1年は1月1日～12月31日のセンチュリーイヤーです。そこでPREVIOUSYEARを入れ子にして、1年とは1月1日～12月31日であると定義します。

PREVIOUSYEAR : データの最終日の基づいて前年の値を集計するタイムインテリジェンス関数
　年末の規定値が12月31日ですから、この関数でデータテーブルの日付の最終日が何時だろうが1年とは1月1日～12月31日だ！ということになります。これでDATEADDの動的課題を解決できます。
　ちなみに、PREVIOUSYEARは前年の1月1日～12月31日のメジャーを集計するのだからDATEADDのパラメーターは「0」(当年) です。「-1」にすると前前年になります。

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

結果の見方

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

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

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

　各セルに適用されている行のコンテキストフィルターのうち「日付 (年)」を無視して前年の値を得る。前年へシフトした「日付 (年)」で列の「用途」、行「建設主」に従って値を返します。

基準値前の値に対する比率

セルの値 / 前年の値 (前の値)

・セルの値 : すべてのセルの値がセルの値の状態
・前年の値 : それぞれの列の値 (列の値はタテに並んでいる) が前年の値の状態