マトリックス表から「平均」を計算
エクセルの「AVERAGE」関数は、よく使う、そして便利だ。
だが、モダンExcelはその比ではない。色々な集計が、バリエーション豊富に、しかも簡単にできるようになる。
元データ
次のようなデータ。
このような、いわゆる「マトリックス表」は、ヒトが判断するにはわかりやすい。
しかし、コンピューターはこの形式を苦手とする。
そこで、Power Queryで「1列1項目のきれいなデータ」にETL（抽出・変換・読込）する必要がある。
エクセルの「AVERAGE」関数で「平均」
たとえば、次のように「AVERAGE」関数で「平均」を計算する。
IF関数を使い、J列の（1から3という）「区分」名を指定し、該当する行で「平均」を計算している。
モダンExcelの一例
モダンExcelの最も基本的な「列のピボット解除」後、ピボットテーブルで「平均」を計算する。
列のピボット解除
詳細エディターに記述された内容は次のとおり。
4つのステップがある。このうち、上3つは「中の人」が記述したもので、最後の一つ「”Unpivoted Columns” = Table.UnpivotOtherColumns(#”Changed Type”, {“区分”, “名前”}, “属性”, “値”)」だけが当方の作業。それも、「列のピボット解除」というアイコンのクリックだけ。
結果は次のとおり。
元データでは、「点数」が5列になっていた。これを「列のピボット解除」を使い、「値」にまとめた。
ピボットテーブルで「平均」の計算
上記のように「1列1項目のきれいなデータ」にできたならば、これをエクセルのワークシート上にピボットテーブルとして読み込む。
「値フィールドの設定」から「平均」を選択すれば、ピボットテーブルで「平均」を計算できる。
「平均点」の計算は、Power Queryで整えたDataの「値2」という列を「Σ値」に配置して行う。
そして、「値フィールドの設定」画面から、名称を「平均点」に変更し、「集計方法」で「平均」を選択し、ピボットテーブルで「平均」を計算する。結果は、次のとおり。
おまけ
上図では「総平均」を表示したが、不要であれば非表示にもできる。
区分別、名前別に集計し直したい場合も、ピボットテーブルであれば簡単だ。
区分別に、点数の高い名前順に並べることも、ピボットテーブルであれば簡単。
ピボットテーブルを複数作成し、まとめて表示することもできる。
同じデータを、違った視点で眺めると、新たな「洞察」を得ることができる。
この先にあるのが、拙著「モダンExcel入門」（日経BP）で紹介する「経営ダッシュボード」という思考回路。
総括と更なるおまけ
今回の方法は、拙著「モダンExcel入門」（日経BP）の読者であれば理解できる、基本的なモダンExcelの活用事例である。
しかも今回、M言語によるプログラミングは一切していない。Power Pivot for ExcelのDAX関数も使っていない。Power Queryで「列のピボット解除」後、ピボットテーブルの「集計の方法」で「平均」としているだけだ。
「1列1項目のきれいなデータ」にさえできれば、言い換えれば「データベース」の形式にできれば、ピボットテーブルで色々なことができるようになる。
その上で、「DAX」と「データモデル」に対する理解を深めると、レガシーエクセルのピボットテーブルではできない、次のような表現が可能となる。
これは、「点数_1」のデータを「区分」別に集計したもので、誰が、何回含まれるのか（受験回数のようなものと理解できる）、「平均点」を計算し並記している。
Power Pivot for ExcelでDAX（データ分析式）による「メジャー」を書くと、こうしたことが可能となる。
モダンExcelを「一体理解」することで、データ分析は実に簡単にできるようになるし、様々な角度からデータを見ることができ、新たな「洞察」を得られるようになる。
基本が大切。
「モダンExcel研究所」を楽しむ際の【注意事項】
コンテンツコピー、スクレイピングなど【厳禁】 ☚【検知ツール設置済】☚悪質な場合【法的措置】を講じます
（過負荷によるサーバー障害が生じた経験上、ご協力お願いいたします！）
モダンExcel研究所
フォローお待ちしてます！