マトリックス表から「平均」を計算

エクセルの「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」の使い方の基本を学ぶには、Power Query(パワークエリ)とPower Pivot for Excel(パワーピボット)の両者を「一体理解」する必要があります。
ぜひ、拙著「モダンExcel入門」(日経BP)で学んでみて欲しいと思います。
サンプルデータで、実際に手を動かしながら、理解を深めることもできます。参考にしてください。

■■■■■

モダンExcel研究所

フォローお待ちしてます!

error: Content is protected !!