Windows 11 & Office 2021 Excel 2021

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

投稿日:2024年1月2日 更新日:

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

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

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

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

Power Queryでグループ化で[集計の追加]で求めたテーブル

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

Excel2013にPower Queryをインストールして[POWER QUERY]タブを表示

Excel2013にPower QueryアドインをMicrosoftのダウンロードセンターのページからインストールしてみました。OSは、W ...

続きを見る

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

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

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

サンプルのテーブル

関数で求めた場合の結果

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

関数で求めた結果

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

=UNIQUE(テーブル1[得意先名])

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

=COUNTIF(テーブル1[得意先名],F2#)

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

=SUMIF(テーブル1[得意先名],F2#,テーブル1[金額])

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

UNIQUE関数で重複しない値をリスト化して表内の該当セルを塗りつぶす

Excel2021とMicrosoft 365のExcelでは、UNIQUE(ユニーク)関数を使用することができます。 UNIQUE関数を使 ...

続きを見る

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

SUMIF関数で先頭の1文字が同じセルの売上を集計する

Excelで顧客番号や商品番号の先頭の1文字が同じセルの合計をしたいという場合の方法です。 SUMIF関数を使用します。 指定された検索条件 ...

続きを見る

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

ピボットテーブルで重複データをチェックして一意のリストを作成

Excelのリストで重複データが混在している場合、重複データをチェックして一意のリストを作成したい場合があります。 重複データをチェックする ...

続きを見る

Power Queryのグループ化

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

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

テーブルまたは範囲から

選択したExcelテーブルにリンクされる新しいクエリを作成します。

選択したデータがテーブルまたは名前付き範囲ではない場合は、テーブルに変換されます。

[データ]タブの[データの取得と変換]グループにある[データまたは範囲から]

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

[ホーム]タブの[グループ化]

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

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

グループ化

現在選択されている列の値に基づいてこのテーブルの行をグループ化します。

[ホーム]タブの[変換]グループにある[グループ化]

[変換]タブの[グループ化]

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

[変換]タブの[テーブル]グループの[グループ化]

ショートカットメニューの[グループ化]

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

ショートカットメニューの[グループ化]

[グループ化]ウィンドウで[集計の追加]

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

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

[グループ化]ウィンドウ

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

[得意先名]ごとの個数

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

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

[グループ化]ウィンドウの[詳細設定]と[集計の追加]

[新しい列名]に、[合計]と入力します。

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

[新しい列名]に[合計]を追加

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

[グループ化]ウィンドウの[列]に[金額]を指定

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

Power Queryエディターの結果

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

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

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

このクエリへの変更を保存します。クエリエディターウィンドウを閉じて、結果の読み込み先を指定してください。

[ホーム]タブの[閉じる]グループの[閉じて読み込む]-[閉じて次に読み込む]

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

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

[データのインポート]ダイアログボックス

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

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

Power Queryエディターを閉じて読み込んだテーブル

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

ピボットテーブルで集計

[行数のカウント]と[個別の行数のカウント]の違い

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

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

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

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

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

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

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

[得意先名]と[商品コード]が共に同じ行があるサンプルのテーブル

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

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

[グループ化]ウィンドウの[行数のカウント]と[個別の行数のカウント]

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

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

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

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

Power Queryエディターでグループ化した結果

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

重複したレコードがないサンプルテーブル

[行数のカウント]と[個別の行数のカウント]が同じ数値になったPower Qery エディターの結果

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

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

[得意先名]と[商品コード]が共に同じ行があるサンプルのテーブル

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

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

[グループ化]ウィンドウの[グループ化の追加]で[商品コード]を指定して、[操作]は[個別の行数のカウント]を指定

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

重複した行が削除されたPower Queryエディターの結果

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

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

[カウント]見出しで右クリック、ショートカットメニューの[削除]

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

[カウント]列を削除したPower Queryエディター

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

重複しない一意の値を求める方法と複数列で重複をチェックする方法

Excelで2つの列にデータが入力されていて、そのデータの差分を求めたい場合がありますね。 A列には分析用データとして別表に貼り付け済みのI ...

続きを見る

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

複数の条件に合うデータを抽出する方法(別シートへの書き出しも可能)

Excelのデータ抽出の方法はいろいろありますが、ここでは[データ]タブの[並べ替えとフィルター]グループにある[詳細設定]を使って、複数の ...

続きを見る

グループ化の追加

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

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

サンプルのテーブル[テーブル1]

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

[グループ化]ウィンドウの[グループ化の追加]

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

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

[グループ化]ウィンドウで[得意先名]を追加

以下のようになります。

グループ化を追加したPower Queryエディターの結果

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

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

ショートカットメニューの[型の変更]-[日付]

以下のようになります。

[日付]に変更したPower Queryエディターの結果

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

created by Rinker
★★★★★ マクロもVBAも必要なく標準機能だけでOK♪
Amazonのレビューを見る

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

Power Queryエディターと同じ結果を求めたピボットテーブル

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

アンケートはピボットテーブルで簡単集計

アンケートをとって、顧客満足度などの調査をすることがあると思います。 アンケートをとった後は、集計しますね。その時にピボットテーブルを活用す ...

続きを見る

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

ピボットテーブルの元データをテーブルにするとデータソースの更新が便利

ピボットテーブルを作成した後、ピボットテーブルの元データの範囲が変わると、その度に[オプション]タブの[データソースの変更]をクリックしてダ ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Windows 11 & Office 2021 Excel 2021

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.