エクセル – ピボットグラフ – 値の平均線を追加する方法 (DAX・フィルター関数)
ピボットグラフへ平均値や中央値など、基本統計量の基準線を追加します。ピボットが参照しているもとデータへ列を追加せず、DAX・フィルター関数を使用し計算メジャーを作成します。
目次
ピボットの平均値
データについて
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_1-1024x506.png)
左画像がピボットへ接続するデータです。4月1日から6月30日までの3か月間です。
・ディメンション = 日付 (1日1行)
・メジャー = 売上高
このデータからピボットグラフを作成して売上高の平均線を追加します。
右画像にように接続データへ列を追加して値を計算する方法は使用しません。
集計方法を「平均」にする
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_2-1024x529.png)
データは3か月ありますが、まず、2か月にしています。
・「軸」へ「日付」
・「値」へ「売上高」を二重に設定します。
・グラフは縦棒です。
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_3-1024x527.png)
・「売上高」のどちらかの▼三角をクリックして
・「値フィールドの設定」を選択します。
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_4-1024x528.png)
・「集計方法」タブ
・「平均」を選択して「OK」です。
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_5-1024x529.png)
・グラフを組み合わせにして平均値を折線へ変更しました。
平均値は売上高合計値と一致します。
4月1日から5月31日の期間の平均値になるのだろうといった期待には、この方法では沿えません。
集計方法の平均とは何か
期間中の日の平均売上高
軸を月へ変更することで目的とする値へ近づいてきました。ここからさらに、軸に設定している月 (2か月)をまとめた軸へ変更すれば平均値を求めることができそうです。
4月1日~5月31日の期間の日の平均売上高は
・分子
4月1日~5月31日の期間の売上高合計
・分母
4月1日~5月31日の日=61
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_8-1024x532.png)
しかし、グラフの軸は「日付」の日です。
軸が「日付」の日の状態のまま、軸が「日付」の月の2か月合計の線をグラフへプロットします。
フィルター関数 ALLSELECTED
CALCULATE
=CALCULATE(平均値を求める式,フィルター式)
関数「CALCULATE」はフィルター式の定義に従い平均を求める式 (テーブル式) の計算結果を返します。
#平均値を求める式 AVERAGE([売上高])
・平均を求める式は「AVERAGE([売上高])」
売上高の平均値を求めます。この式が返す値は「日付」の日ごとの平均値です。つまり、はじめに説明した値、「集計方法平均」と同一値です。
ALLSELECTED
#フィルター式 ALLSELECTED(テーブル,フィルター保持しないカラム)
・フィルター式は関数「ALLSELECTED」です。
括弧の中へ保持しないフィルターを定義します。
「ALLSELECTED()」 このように括弧の中を省略するとすべてのフィルターが適用されます。
・テーブル
フィルター保持しないカラムがあるテーブル名を指定します。省略できます。
今回のデータは1テーブルなので省略します。
・フィルター保持しないカラム
フィルター保持しないカラム名を指定します。複数指定できます。
今回は「ALLSELECTED()」でもできるのですが、月別に平均値が計算できるように「日付」を設定します。
#DAX式 =CALCULATE(AVERAGE([売上高]),ALLSELECTED(sales[日付]))
これで「日付」のフィルターを保持せず (軸の日付を無視して) 「売上高」の平均値を算出することができます。
「日付」のフィルターを保持しないということは、グラフの軸を「日付」に設定しても軸を無視して計算するということです。
グラフ作成
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_9-1024x530.png)
計算メジャーを新規作成します。
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_10-1024x530.png)
・作成した計算メジャーを「値」へ設定
・「組み合わせグラフ」で「平均値」を折線にします。
第2軸に設定する必要はありません。
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_11-1024x529.png)
平均値が一本線になっていれば成功です。
月ごとの平均線
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_12-1-1024x529.png)
「日付」の月ごとに平均線を変えるときは、「日付 (月)」を軸へ設定します。「日付」の上へ設定するとグラフの軸ラベルが見やすくなります。
・「日付」の日はフィルターされない
・「日付」の月はフィルターされる
フィルター関数で保持しないフィルターを「日付」だけに定義しているので、軸に設定している「日付 (月)」が保持される結果、月での集計が可能になります。
その他の基本統計量
百分位
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_13-1024x530.png)
=CALCULATE(百分位求める式,ALLSELECTED(sales[日付]))
平均値を求めるDAXをコピペして「平均値を求める式」の部分を「百分位を求める式」へ変更します。
#百分位を求める式 PERCENTILE.EXC(sales[売上高],0.75)
「0.75」の値を変更してください。「0.5」が中央値です。
「1.0」>値>「0.0」の範囲で変更できます。「1.0」と「0.0」は入力できません。
最大値・最小値
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_14-1024x530.png)
関数「PERCENTILE.EXC」が「1.0」と「0.0」を受け付けないので
・最大値「MAX」
・最小値「MIN」
グラフ作成
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_15-1024x530.png)
それぞれの計算メジャーを「値」へ設定します。
もとデータへ列を追加しない理由
当月の値 (列を追加する方法)
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_16-1024x532.png)
画像のように当月の値を表示したい。
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_18-1024x530.png)
画像のように列を追加します。
選択期間の値 (列を追加する方法)
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_17-1024x530.png)
選択した期間、あるいは、全期間の値を見たい。
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_19-1024x530.png)
期間を組み合わせた列を追加します。
データ期間が7月、8月、9月と追加されるたびに列を追加します。
計算メジャーを作成しておけばデータを追加しても列を追加する必要がなくなります。
データを追加する方法
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_22-1024x526.png)
・もとデータの下へ翌月のデータをコピペします。
・「データ」タブ
・「すべて更新」をクリックします。
![](https://otukai.jp/wp-content/uploads/2021/03/xlsx-allselected_23-1024x532.png)
追加したデータへ接続できていれば成功です。