【Excel】Power Queryで「行の集計」を追加する!Excelのグループ化で合計・平均を算出する手順

Excelで大量のデータを扱う際、特定の条件でデータをまとめ、合計や平均を算出したい場面があります。これは「グループ化」や「集計」と呼ばれる操作です。Excelの標準機能でもグループ化は可能ですが、データソースが複数あったり、複雑な集計が必要になったりすると、手作業では限界があります。そのような場合に非常に強力なのがPower Queryです。Power Queryを使えば、データの整形から集計までを自動化し、繰り返し利用できる手順として保存できます。この記事では、Power Queryで「行の集計」機能を追加し、Excelのグループ化と同様の合計・平均算出を行う手順を解説します。この手順を習得すれば、データ集計作業の効率が格段に向上します。

Power Queryの「行の集計」機能は、指定した列を基準にデータをグループ化し、各グループに対して合計、平均、件数などの集計値を計算できる機能です。これにより、元データを加工することなく、必要な集計結果を簡単に得ることができます。特に、複数のデータソースを結合して集計する場合や、定期的に同じ集計を行う場合にその威力を発揮します。手作業での集計ミスを防ぎ、データ分析の精度を高めるためにも、Power Queryの活用は非常に有効です。

Power Queryの「行の集計」機能とは

Power Queryの「行の集計」機能は、テーブル内のデータを指定した列の値に基づいてグループ化し、各グループに対してさまざまな集計演算を実行する機能です。例えば、商品ごとの売上データを商品名でグループ化し、各商品の合計売上金額や平均単価を算出するといったことが可能です。この機能は、Excelの「ピボットテーブル」や「グループ化」機能と似ていますが、Power Queryはデータの整形プロセスの一部として組み込める点が大きな特徴です。これにより、データの取り込みから加工、集計までを一連の流れで自動化できます。集計の種類も、合計、平均、最小値、最大値、件数、標準偏差など、豊富に用意されています。

Power Queryで「行の集計」を追加する手順

Power Queryで「行の集計」を追加するには、まずExcelにデータを読み込み、Power Queryエディターを開く必要があります。その後、集計したい列を指定し、「行の集計」ダイアログボックスで集計方法を設定します。以下に具体的な手順を示します。

1. Excelにデータを読み込む
   Excelで集計したいデータが含まれるテーブルを選択します。次に、「データ」タブの「データの取得と変換」グループにある「テーブルまたは範囲から」をクリックします。これにより、データがPower Queryエディターに読み込まれます。
2. 集計したい列を選択する
   Power Queryエディターが開いたら、グループ化の基準となる列(例: 商品名、地域名など)を1つ以上選択します。複数選択する場合は、Ctrlキーを押しながらクリックします。
3. 「行の集計」機能を選択する
   選択した列がある状態で、「ホーム」タブの「変換」グループにある「集計」をクリックします。または、「変換」タブの「テーブル」グループにある「集計」をクリックしても同じ機能にアクセスできます。
4. 「行の集計」ダイアログボックスを設定する
   「行の集計」ダイアログボックスが表示されます。ここで以下の設定を行います。
   1. グループ化する列: すでに選択されている列が表示されています。必要に応じて変更・追加・削除が可能です。
   2. 集計する列: 集計したい値が含まれる列を選択します。例えば、売上金額の合計を計算したい場合は「売上金額」列を選択します。
   3. 操作: 集計方法を選択します。ここでは「合計」を選択します。他にも「平均」「最小」「最大」「件数」など様々なオプションがあります。
   4. 新しい列名: 集計結果が格納される新しい列の名前を入力します。例えば、「合計売上」と入力します。
   5. 複数の集計を追加する: 別の集計を追加したい場合は、「列の追加」ボタンをクリックします。例えば、「平均単価」という名前で「単価」列の「平均」を算出するなど、複数の集計を同時に行うことができます。
5. 「OK」をクリックして集計を実行する
   設定が完了したら、「OK」ボタンをクリックします。Power Queryエディターに、指定した列でグループ化され、集計結果が新しい列として表示されます。
6. データをExcelに読み込む
   集計結果に問題がなければ、「ホーム」タブの「閉じて読み込む」をクリックします。これにより、集計されたデータがExcelシートに新しいテーブルとして読み込まれます。

Power Queryの「行の集計」でよくある誤操作と対処法

Power Queryの「行の集計」機能は非常に便利ですが、いくつかの注意点やよくある誤操作があります。ここでは、それらの例と対処法を解説します。

集計結果が意図した値にならない場合

集計結果が期待通りの数値にならない場合、いくつかの原因が考えられます。まず、集計対象の列のデータ型を確認してください。数値として認識されていない列(例: 文字列型になっている、通貨記号などが含まれている)は、正しく計算されません。Power Queryエディターで、集計対象の列のデータ型が「10進数」や「整数」など、数値として適切に設定されているか確認し、必要であればデータ型を変更してください。また、グループ化の基準となる列に、意図しない空白や表記ゆれ(例: 「東京」と「東京都」)がないかも確認が必要です。これらの表記ゆれは、別々のグループとして扱われてしまうため、集計結果に影響します。空白や表記ゆれがある場合は、前処理の段階で修正しておきましょう。

特定のグループだけ集計されない場合

特定のグループのデータが集計されない場合、そのグループのデータがPower Queryエディターで正しく読み込まれていない可能性があります。データのソースに問題がないか、またはPower Queryの読み込み設定で除外されていないかを確認してください。また、グループ化の基準となる列に、目に見えない特殊文字が含まれている可能性も考えられます。Power Queryエディターで、該当する列の空白文字などを確認し、不要な文字は削除してください。それでも解決しない場合は、一度Power Queryのステップをリセットし、再度最初から手順を試してみることも有効な手段です。

「列の追加」で集計を追加できない場合

「行の集計」ダイアログボックスで「列の追加」ボタンをクリックしても、新しい集計項目を追加できない、あるいは追加した集計が反映されないという状況が発生することがあります。この場合、すでに同じ名前の列がテーブル内に存在しているか、または集計対象の列が正しく選択されていない可能性があります。新しい集計列の名前が既存の列名と重複していないか確認し、重複している場合は別の名前に変更してください。また、集計する列と操作が正しく選択されているかも再確認しましょう。これらの設定が正しく行われていれば、複数の集計を問題なく追加できます。

Power QueryとExcelのグループ化機能の比較

Power Queryの「行の集計」機能とExcelの標準機能である「グループ化」機能は、どちらもデータを集計するために使用されますが、その特性には違いがあります。

Power Queryは、データの取り込みから整形、集計までを自動化できるため、繰り返し行う集計作業や、複数のデータソースを扱う場合に特に強力です。一方、Excelのグループ化は、手軽にExcelシート内のデータを集計したい場合に便利です。どちらの機能も、目的に応じて使い分けることが重要です。

まとめ

この記事では、Power Queryを使用して「行の集計」機能を追加し、Excelのグループ化と同様に合計や平均を算出する手順を解説しました。Power Queryの「行の集計」機能を使えば、データの取り込みから集計までを自動化し、作業効率を大幅に向上させることができます。集計結果が意図しない値になる場合や、特定のグループが集計されないといったトラブルシューティングの方法も併せて説明しました。この知識を活かし、Power Queryでのデータ集計をマスターすることで、より高度なデータ分析への第一歩を踏み出せるでしょう。今後、さらに複雑な集計や、他のPower Query機能との連携についても探求することをお勧めします。