【Excel】GROUPBY・PIVOTBY関数で集計表を動的に生成!Excelの最新集計関数の使い方

Excelの集計作業は、データ分析において非常に重要です。しかし、従来のピボットテーブルやSUMIFS関数では、集計条件を変更するたびに再設定が必要でした。この手間を解消し、より柔軟で動的な集計表を生成できるのが、Microsoft 365で利用可能になったGROUPBY関数とPIVOTBY関数です。この記事では、これらの最新集計関数の使い方と、具体的な活用方法を解説します。

GROUPBY関数とPIVOTBY関数を使いこなすことで、集計表の作成・更新作業が劇的に効率化されます。データ分析のスピードと精度を向上させたい方は、ぜひ参考にしてください。

GROUPBY関数とPIVOTBY関数の概要

GROUPBY関数とPIVOTBY関数は、Excel for Microsoft 365の最新バージョンで導入された、動的な集計を行うための関数です。これらの関数は、指定した条件に基づいてデータを集計し、その結果を配列として返します。従来のピボットテーブルのように、GUI操作で設定を変更する必要がなく、数式だけで集計条件を自在に操れる点が最大の特徴です。

GROUPBY関数は、指定した列の値をグループ化し、各グループの合計、平均、件数などを計算します。一方、PIVOTBY関数は、2つ以上の列を組み合わせてピボットテーブルのようなクロス集計表を生成します。これらの関数を理解することで、より高度なデータ分析が容易になります。

GROUPBY関数の使い方

GROUPBY関数は、指定した列でデータをグループ化し、集計値(合計、平均、件数など)を計算します。基本的な構文は以下の通りです。

GROUPBY(array, row_fields, col_fields, values, function, [total_row_mode], [total_col_mode], [filter_mode], [sort_order])

主要な引数について解説します。

• array: 集計対象のデータ範囲を指定します。
• row_fields: グループ化の基準となる行フィールドを指定します。複数指定可能です。
• col_fields: グループ化の基準となる列フィールドを指定します。複数指定可能です。
• values: 集計対象の値が含まれる列を指定します。
• function: 実行する集計関数を指定します(例: “SUM”, “AVERAGE”, “COUNT”)。

具体的な使用例を見てみましょう。例えば、A1:D10の範囲に「商品名」「地域」「売上」「数量」のデータがあるとします。「商品名」でグループ化し、「売上」の合計を計算するには、以下の数式を使用します。

=GROUPBY(A1:D10, A1:A10, , D1:D10, "SUM")

この数式は、A列の各商品名ごとに、D列の売上合計を計算して返します。`col_fields`と`values`を省略し、`function`に”COUNT”を指定すれば、各行フィールドの件数を取得できます。

GROUPBY関数で複数列を集計する

GROUPBY関数では、`row_fields`や`values`に複数の列を指定することで、より複雑な集計が可能です。例えば、「商品名」と「地域」でグループ化し、「売上」と「数量」の合計を計算したい場合は、以下のように記述します。

=GROUPBY(A1:D10, {A1:A10, B1:B10}, , {D1:D10, E1:E10}, "SUM")

この数式では、`row_fields`に配列定数 `{A1:A10, B1:B10}` を指定し、`values`にも配列定数 `{D1:D10, E1:E10}` を指定しています。これにより、商品名と地域を組み合わせたグループごとに、売上と数量の合計が計算されます。

GROUPBY関数で集計関数を複数指定する

function引数に配列定数を使用することで、複数の集計関数を同時に適用できます。例えば、「商品名」ごとに「売上」の合計と平均を計算するには、以下の数式を使用します。

=GROUPBY(A1:D10, A1:A10, , D1:D10, {"SUM", "AVERAGE"})

これにより、各商品名に対して、売上合計と売上平均が隣接する列に表示されます。

PIVOTBY関数の使い方

PIVOTBY関数は、2つ以上のフィールドを使用してクロス集計表(ピボットテーブルのような形式)を生成します。GROUPBY関数よりも高度な集計が可能です。基本的な構文は以下の通りです。

PIVOTBY(array, row_fields, col_fields, values, function, [total_row_mode], [total_col_mode], [filter_mode], [sort_order])

PIVOTBY関数もGROUPBY関数と似た引数を持ちますが、`row_fields`と`col_fields`に複数のフィールドを指定することで、2次元の集計表を作成できる点が異なります。

例として、先ほどのA1:D10のデータ(「商品名」「地域」「売上」「数量」)を使用します。「商品名」を行フィールド、「地域」を列フィールドとし、「売上」の合計を表示するには、以下の数式を使用します。

=PIVOTBY(A1:D10, A1:A10, B1:B10, D1:D10, "SUM")

この数式は、「商品名」を行、「地域」を列とするクロス集計表を生成し、各セルには該当する商品名と地域の売上合計が表示されます。

PIVOTBY関数で複数集計関数を適用する

PIVOTBY関数でも、function引数に配列定数を使用することで、複数の集計関数を適用できます。例えば、「商品名」を行、「地域」を列とし、「売上」の合計と「数量」の平均を表示するには、以下の数式を使用します。

=PIVOTBY(A1:D10, A1:A10, B1:B10, {D1:D10, E1:E10}, {"SUM", "AVERAGE"})

この場合、`values`に売上と数量の列を指定し、`function`に”SUM”と”AVERAGE”を指定します。結果として、各商品名と地域の組み合わせに対して、売上合計と数量平均が表示されるクロス集計表が生成されます。

PIVOTBY関数でデータフィルターを適用する

PIVOTBY関数では、`filter_mode`引数を使用して、特定の条件を満たすデータのみを集計対象とすることができます。例えば、「地域」が「東京」であるデータのみを対象に集計するには、`filter_mode`に条件を指定します。

=PIVOTBY(A1:D10, A1:A10, B1:B10, D1:D10, "SUM", , , B1:B10="東京")

この例では、`filter_mode`に `B1:B10=”東京”` を指定しています。これにより、地域が「東京」であるデータのみが集計対象となります。

GROUPBY関数とPIVOTBY関数の比較

GROUPBY関数とPIVOTBY関数は、どちらも動的な集計を可能にしますが、その用途と機能には違いがあります。

項目	GROUPBY関数	PIVOTBY関数
主な用途	1次元のリスト形式での集計	2次元のクロス集計表(ピボットテーブル形式)の生成
集計軸	行フィールドまたは列フィールドのいずれか一方に主眼	行フィールドと列フィールドの両方を指定可能
複雑な集計	複数フィールドによるグループ化は可能だが、2次元表の生成には不向き	複数フィールドによるクロス集計、フィルター適用に優れる
出力形式	指定したフィールドでグループ化されたリスト	行と列で構成される表形式

GROUPBY関数は、単一のカテゴリごとの集計や、複数のカテゴリを列挙するリスト形式の集計に適しています。一方、PIVOTBY関数は、Excelのピボットテーブルのように、行と列の組み合わせでデータを多角的に分析したい場合に強力な機能を発揮します。

GROUPBY・PIVOTBY関数でよくある質問と対処法

Q1: 関数を入力してもエラーが表示される

原因と対処法:

1. Excelのバージョンが古い: GROUPBY関数とPIVOTBY関数はMicrosoft 365の最新バージョンで利用可能です。Excelのバージョンを確認し、必要であればアップデートしてください。
2. 引数の指定ミス: 関数の構文や引数のデータ型が間違っている可能性があります。各引数(特に配列、フィールド、関数)が正しく指定されているか、ヘルプを参照しながら確認してください。
3. データ範囲の重複: `array`、`row_fields`、`col_fields`、`values`などの範囲が重複している場合、意図しない結果やエラーを引き起こすことがあります。各範囲が独立しているか確認してください。

Q2: 集計結果が意図した通りにならない

原因と対処法:

1. フィールドの指定誤り: `row_fields`や`col_fields`に指定した列が、実際にグループ化したい項目と一致しているか確認してください。
2. 集計関数(function)の指定誤り: “SUM”, “AVERAGE”, “COUNT”などの集計関数名が正しく記述されているか確認してください。大文字・小文字は区別されません。
3. フィルター条件の誤り: `filter_mode`を使用している場合、条件式が正しく記述されているか確認してください。例えば、`B1:B10=”東京”`のように、比較対象と値が明確に指定されている必要があります。
4. データ形式の問題: 集計対象の数値データが文字列として認識されている場合、SUMやAVERAGE関数が正しく機能しないことがあります。セルの書式設定を確認し、必要であれば数値形式に変換してください。

Q3: total_row_modeやtotal_col_modeが機能しない

原因と対処法:

1. 引数の順序: `total_row_mode`や`total_col_mode`は、`filter_mode`よりも前の引数に指定する必要があります。これらの引数を省略する場合でも、カンマ(,)でプレースホルダーを空けておく必要があります。
2. Excelのバージョン: これらのモードは比較的新しい機能のため、古いバージョンではサポートされていない可能性があります。Microsoft 365の最新バージョンを使用しているか確認してください。

まとめ

GROUPBY関数とPIVOTBY関数は、Excelでのデータ集計作業を劇的に効率化する強力なツールです。これらの関数を活用することで、数式だけで動的な集計表を生成し、データ分析のスピードと柔軟性を向上させることができます。従来のピボットテーブルでは難しかった、数式ベースでの集計条件の変更も容易になります。

まずは簡単なデータでGROUPBY関数とPIVOTBY関数の基本的な使い方を試し、徐々に複雑な集計に挑戦してみてください。これらの最新関数を使いこなすことで、Excelを使ったデータ分析の可能性が大きく広がります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】