本講座では、データの集計を効率よく行うために統計関数やグループ化機能を使った集計テクニックを学んでいきます。
統計関数の利用方法
統計関数の利用方法を説明します。
画面から統計関数を利用する方法(統計関数を知らなくてOK)
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

- 統計関数を利用する列を選択します。(以下の例だとB列)
- [変換]タブを選択し[統計アイコン]を選択します。
- 合計値や、最大値、最小値等の統計関数を選択することができます。今回は、[平均]をクリックしてB列の行の値の平均値を出してみます。

数式バーを確認するとList.Average(列名)という平均値を求める統計関数が表示されています。そして、数式バーの下に表示されている値が統計関数によって得られた値になります。今回の場合は、平均値が表示されています。
= List.Average(変更された型[B列])

続いて前のステップに戻ります。数式バーの左側にある[fx]を選択して直前のステップ「変更された型」を入力します。これで、先ほど求めた平均値をパワークエリに保持させたまま、直前ステップに戻ることができます。

- [列の追加]を選択します。
- [カスタム列]を選択します。

新しい列名を入力して、カスタム列の式には、先ほど平均値を求めたステップ名を入力します。今回は、[計算された平均]がステップ名です。ステップ名は、分かりやすい名称に変更することができます。

B列の平均値が最終列に追加されました。

統計関数を直接入力する方法
感のいい方はお気づきだと思いますが、統計関数を知っていれば以下のように直接カスタム列の式に入力するという方法もあります。この方が早いですが、関数を瞬時に出す必要があります。

主要な統計関数一覧
統計関数名称 | 統計関数 |
---|---|
合計 | List.Sum([列名]) |
最小値 | List.Min([列名]) |
最大値 | List.Max([列名]) |
中央 | List.Median([列名]) |
平均 | List.Average([列名]) |
標準偏差 | List.StandardDeviation([列名]) |
値のカウント | List.NonNullCount([列名]) |
グループ化
グループ化は、Excelの関数であるSUMIF(計算)とAVERAGE(平均)などの関数に相当する機能です。具体的には、複数のデータ行を、合計、平均、最大、最小、平均、または中央値で集計して分かりやすいデータに変換することができます。
一つの列でグループ化
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
手始めに一つの列でグループ化する方法を説明します。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

- [変換]タブを選択します。
- [グループ化]を選択します。

「コード」ごとにグループ化して、数値A、数値Bの合計値と平均値を表示させます。
- [詳細設定]を選択します。
- [集計の追加]を4回押下します。
- 新し列名を入力します。
- 集計方法をプルダウンから[合計]と[平均]を選択します。
- 集計する列名をプルダウンより選択します。

コードごとにグループ化され、数値A、数値Bでそれぞれ合計値と平均値が表示されています。

二つの列でグループ化
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
続いて複数の列でグループ化する方法を説明します。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

- [変換]タブを選択します。
- [グループ化]を選択します。

[大分類]と[小分類]二つの列でグループ化して、数値A、数値Bの合計値を表示させます。
- [詳細設定]を選択します。
- [グループ化の追加]を2回押下します。
- [大分類]と[小分類]を選択します。
- [集計の追加]を2回押下します。
- 新し列名を入力します。
- 集計方法をプルダウンから[合計]を選択します。
- 集計する列名をプルダウンより選択します。

[大分類]と[小分類]でグループ化され、数値A、数値Bの合計値が表示されています。

パワークエリ学習コンテンツ
-
第1回PowerQuery講座|パワークエリとは?何ができる?どう起動する?
-
第2回PowerQuery講座|パワークエリのデータソースの取り込み方法をデータ形式毎に解説
-
第3回PowerQuery講座|パワークエリの『列』操作
-
第4回PowerQuery講座|パワークエリの『行』操作
-
第5回PowerQuery講座|文字を整えて(テキストクレンジング)パワークエリで扱いやすいようにする。
-
第6回PowerQuery講座|パワークエリの数字計算(四則演算、四捨五入、切り上げ、切り捨て)
-
第7回PowerQuery講座|パワークエリの統計関数やグループ化機能を使った列の集計方法
-
第8回PowerQuery講座|パワークエリの時間を表すデータ型と時間を使った計算
-
第9回PowerQuery講座|パワークエリの様々な条件判断
-
第10回PowerQuery講座|パワークエリで行と列を入れ替える(列のピボット解除、列のピボット)
-
第11回PowerQuery講座|パワークエリにおけるクエリの管理方法
コメント