Power Queryエディターで複数のグループ化の結果を表示するには

Power Queryには、［グループ化］という機能があります。

COUNTIF関数やSUMIF関数、またはピボットテーブルで求める結果と同じ集計ができます。

どの方法で求めるのがベストなのかは、状況などによって異なるとは思いますが、Power Queryでの方法も知っておくと役立つことがあると思います。

また、Power Queryでは、複数列の重複行を除いた結果を表示することもできます。

参考Power Queryは、Excel2016以降では標準機能となっていますが、Excel2010とExcel2013はアドインをインストールすることで使用できます。

テーブルの確認と求める結果

以下のテーブルをPower Queryエディターで開きます。テーブル名は［テーブル1］です。

［得意先］をグループ化して個数と金額の合計を求めたいと思います。

関数で求めた場合の結果

関数で求めた場合の結果です。これと同じものをPower Queryエディターで求めます。

セル［F2］には、UNIQUE関数を使って数式を入力しています。数式は先頭のセルに入力すると、スピル機能によって自動で数式が入力されます。

セル［G2］には、COUNTIF関数を使って数式を入力しています。［#］は［スピル範囲演算子］と呼ばれるものです。

セル［H2］には、SUMIF関数を使って数式を入力しています。

参考UNIQUE関数は、Excel2021とMicrosoft 365のExcelで使用できる関数です。

SUMIF関数については、以下の記事で解説しています。

COUNTIF関数は、重複のチェックなどでも使用できます。

Power Queryのグループ化

テーブル内のセルをアクティブにして、［データ］タブの［データの取得と変換］グループにある［データまたは範囲から］をクリックします。

ポップヒントにもあるように、テーブルでない場合は自動的にテーブルに変換されます。

Power Queryでグループ化するには、3つの方法があります。どの方法で実行しても同じです。

［ホーム］タブの［グループ化］

まず、グループ化したい列を選択しておきます。ここでは、［得意先名］列になります。

［ホーム］タブの［変換］グループにある［グループ化］をクリックします。

［変換］タブの［グループ化］

または、［変換］タブの［テーブル］グループの［グループ化］をクリックします。

ショートカットメニューの［グループ化］

もう1つは、グループ化したい列名で右クリックして、ショートカットメニューから［グループ化］をクリックします。

［グループ化］ウィンドウで［集計の追加］

［グループ化］ウィンドウが表示されます。

このまま［OK] ボタンをクリックすると、

［得意先名］ごとの個数が結果として表示されます。

しかし、ここでは、得意先ごとの金額の合計も求めたいので、［詳細設定］をクリックします。

そして、［新しい列名］から［集計の追加］をクリックします。

［新しい列名］に、［合計］と入力します。

［操作］は、プルダウンリストから［合計］をクリックします。

［列］には、合計したい列［金額］を指定して、［OK］ボタンをクリックします。

結果、以下のようになります。

Power Queryエディターの結果を読み込む

テーブルと同じシートに読み込みたいと思います。

［ホーム］タブの［閉じる］グループの［閉じて読み込む］から［閉じて次に読み込む］をクリックします。

［データのインポート］ダイアログボックスが表示されます。

［テーブル］と［既存のワークシート］を選択してセルも指定します。そして、［OK］ボタンをクリックします。

以下のようにテーブルとして読み込まれます。

関数で求めた結果と同じになっているのを確認できます。

参考上記の結果と同じものをピボットテーブルで求めることもできます。

［行数のカウント］と［個別の行数のカウント］の違い

［グループ化］ウィンドウの［操作］には、以下の選択肢があります。

この中に［個別の行数のカウント］というのがあります。

• 合計
• 平均
• 中央
• 最小
• 最大
• 行数のカウント
• 個別の行数のカウント
• すべての行

［行数のカウント］と［個別の行数のカウント］の違いが分かると活用の幅も広がると思います。

例えば、以下のようなテーブルがあります。

［得意先名］と［商品コード］が同じ値の行が複数あります。2つの列で同じデータをチェックしたいとします。

以下は、分かりやすくするために事前にセルに塗りつぶしを設定しています。

このテーブルをPower Queryエディターで読み込み、［グループ化］ウィンドウを表示します。

ここでは、［新しい列名］は、分かりやすくするために［操作］と同じ名前を入力します。［OK］ボタンをクリックします。

結果、以下のようになります。

［行数のカウント］は、［得意先名］別のカウント数です。

［個別の行数のカウント］は、［得意先名］と［商品コード］が共に一致している行は1つをカウントして、他の行は省かれています。

［行数のカウント］と［個別の行数のカウント］の数値が異なるということは、［得意先名］と［商品コード］が共に一致している行があるということになります。

もし、重複した行がない場合は、［行数のカウント］と［個別の行数のカウント］の数値は同じになります。

複数列で一致している重複行を削除した結果を表示

［個別の行数のカウント］の意味が分かると、複数列で重複した行を削除したテーブルを求めることができます。

［グループ化］ウィンドウの［グループ化の追加］で［商品コード］を指定します。

［操作］では、［個別の行数のカウント］を指定して［OK］ボタンをクリックします。

以下のように表示されます。

［カウント］列は不要なので削除します。

列名で右クリックしてショートカットメニューから［削除］をクリックします。

この状態でシートに読み込むといいと思います。

参考条件付き書式を使って、複数列で重複をチェックする方法については、以下の記事で解説しています。

古くからある機能ですが、［フィルターオプションの設定］で重複を削除したリストを作成できます。また、複数列で共に一致している重複行を削除することもできます。

グループ化の追加

Power Queryエディターでは、グループ化を追加することもできます。

ここでは、テーブル1の［納入月日］と［得意先名］でグループ化して合計金額を求めます。

［グループ化］ウィンドウを表示したら、［詳細設定］をクリックして、［グループ化の追加］をクリックします。

プルダウンリストから［得意先名］を選択します。

［新しい列名］には、［合計］と入力して、［操作］はプルダウンリストから［合計］を選択し、［列］ではプルダウンリストから［金額］を選択します。

以下のようになります。

［納入月日］の表示形式を変更します。

［納入月日］の列名で右クリックして、ショートカットメニューの［型の変更］から［日付］を選択します。

以下のようになります。

Power Queryについて学習するなら、以下のテキストがお勧めです。

参考ピボットテーブルで集計すると、以下のようなレイアウトになります。

ピボットテーブルは、アンケート集計などでも活用できます。

また、ピボットテーブルの元になるデータはテーブルを使用すると、更新が楽になります。