こちらは素人です。
エクセル2016でパワークエリ、ピボットテーブルを使います。
office2016スタンダードなので、パワーピボットタブ出ません。
(「開発」タブのCOMアドインにパワーピボットがない)
今回
ピボットテーブル(パワークエリ・DAX)
例えば、成績の平均を出そうと思った。
(すみません。前半の処理はうろ覚えです。また復習・確認します。)
こんなテーブルを用意(テーブル1)。
氏名 国語 算数 クラス
山田 50 60 1組
川田 80 200 2組
田中 50 60 1組
島田 80 60 2組
松田 50 60 1組
この備忘では、科目ごとの平均を出します。
これをパワークエリで数字列を選択してピボット解除して下記の形にしてデータモデルに追加
氏名 クラス 属性 値
山田 1組 国語 50
山田 1組 算数 60
川田 2組 国語 80
川田 2組 算数 200
田中 1組 国語 50
田中 1組 算数 60
島田 2組 国語 80
島田 2組 算数 60
松田 1組 国語 50
松田 1組 算数 60
データモデルを使ってピボットテーブルを作成(この時、白紙のセルを選択して、
ピボットテーブルをデータモデル(テーブル1)を使う)
平均を出す準備:(素人なので、よちよち歩きです。)
(1)ピボットテーブルの値フィールドに値(合計)と値(カウント・個数)を用意
(合計点数÷人数のため)
(こんな感じ。行ラベルずれてます)
行ラベル 合計 / 値 カウント / 値2
1組 330 6
山田 110 2
国語 50 1
算数 60 1
松田 110 2
国語 50 1
算数 60 1
田中 110 2
国語 50 1
算数 60 1
2組 420 4
川田 280 2
国語 80 1
算数 200 1
島田 140 2
国語 80 1
算数 60 1
総計 750 10
(点数の合計が750点 人数は10件(5人で国語と算数))
(2)国語の点数と算数の点数のメジャー(DAX)を作る
メジャーの名前:国語の点数
数式:=CALCULATE([合計 / 値],'テーブル1'[属性]="国語")
(算数も同様)
(こんな感じ。行ラベルずれてます)
行ラベル 合計 / 値 カウント / 値2 国語の点数 算数の点数
1組 330 6 150 180
山田 110 2 50 60
国語 50 1 50 60
算数 60 1 50 60
松田 110 2 50 60
国語 50 1 50 60
算数 60 1 50 60
田中 110 2 50 60
国語 50 1 50 60
算数 60 1 50 60
2組 420 4 160 260
川田 280 2 80 200
国語 80 1 80 200
算数 200 1 80 200
島田 140 2 80 60
国語 80 1 80 60
算数 60 1 80 60
総計 750 10 310 440
(国語の総合計は310点 算数は440点)
(3)国語と算数の人数のメジャー(DAX)を作る
メジャーの名前:国語の人数
数式:=CALCULATE([カウント / 値],'テーブル1'[属性]="国語")
(算数も同様)
(こんな感じ。行ラベルずれてます)
行ラベル 合計 / 値 カウント / 値2 国語の点数 算数の点数 国語の人数 算数の人数
1組 330 6 150 180 3 3
山田 110 2 50 60 1 1
国語 50 1 50 60 1 1
算数 60 1 50 60 1 1
松田 110 2 50 60 1 1
国語 50 1 50 60 1 1
算数 60 1 50 60 1 1
田中 110 2 50 60 1 1
国語 50 1 50 60 1 1
算数 60 1 50 60 1 1
2組 420 4 160 260 2 2
川田 280 2 80 200 1 1
国語 80 1 80 200 1 1
算数 200 1 80 200 1 1
島田 140 2 80 60 1 1
国語 80 1 80 60 1 1
算数 60 1 80 60 1 1
総計 750 10 310 440 5 5
(4)国語と算数の平均のメジャー(DAX)を作る
メジャーの名前:国語の平均
数式:=DIVIDE([国語の点数],[国語の人数])
(算数も同様)
(こんな感じ。行ラベルずれてます)
行ラベル 合計 / 値 カウント / 値2 国語の点数 算数の点数 国語の人数 国語の平均 算数の人数 算数の平均
1組 330 6 150 180 3 50 3 60
山田 110 2 50 60 1 50 1 60
国語 50 1 50 60 1 50 1 60
算数 60 1 50 60 1 50 1 60
松田 110 2 50 60 1 50 1 60
国語 50 1 50 60 1 50 1 60
算数 60 1 50 60 1 50 1 60
田中 110 2 50 60 1 50 1 60
国語 50 1 50 60 1 50 1 60
算数 60 1 50 60 1 50 1 60
2組 420 4 160 260 2 80 2 130
川田 280 2 80 200 1 80 1 200
国語 80 1 80 200 1 80 1 200
算数 200 1 80 200 1 80 1 200
島田 140 2 80 60 1 80 1 60
国語 80 1 80 60 1 80 1 60
算数 60 1 80 60 1 80 1 60
総計 750 10 310 440 5 62 5 88
おおまかにはこんな感じでした。
総計の62と88が平均点です。
(意味ないか。)
考えたこと・・・「計算フィールド・計算領域」は自分のエクセル2016ではない?
youtubeで見たりすると、パワーピボットタブから「管理」をクリックしデータビューを開き、
画面下の計算領域にメジャーを作成しているものがあります。
自分の環境では、パワーピボットタブがないので、このやり方ではなく、
ピボットテーブルのフィールドリストから作っています。
試しに、エクセル2019(パワーピボットタブあり)で算数の人数と算数の平均を
計算領域にメジャーを作成すると、簡単にできました。(国語をコピペ)
作成したファイルをエクセル2016で開きましたが、問題なさそうです。
考えたこと・・・もっとパワーピボットの仕組みを分からないと使えない。
ALLやALLSELECTEDなどのDAX関数は今回使わなかったですが、
コンテキスト(実行される状況で違った動作をすること?)
やイテレータ(sumでなくsumxなどの行ごとの処理)など、そういうのも
理解しないとメジャーの作成は難しいかなと思いました。