Excel情報563　関数 --1つの数式で集計表を作成する方法

【テーマ】
今回は1つの数式を使って、複数の項目を組み合わせた集計表を自動作成する方法をご紹介します。集計したい項目の範囲や集計方法を指定するだけで、データを整理して表示できます。

【方法】
PIVOTBY関数を使用します。

【参考】
元データをテーブル化すると、データを追加、削除した場合も、自動で集計表に反映されます。

■ 今回の内容

今回はPIVOTBY関数を使って、1つの数式で複数の項目からデータを分類・集計し

集計表を自動作成する方法をご紹介します。

※PIVOTBY関数はMicrosoft 365、Excel 2021以降で利用可能です。

　ご利用のExcelのバージョンによっては対応していない場合があるため、ご注意ください。

■ 設定方法

例として、「売上金額表」シートのデータを元に、

「集計表」シートにPIVOTBY関数を使って担当者別・商品別に売上金額を集計し、合計を表示します。

「集計表」シートで、集計結果を表示したいセル（例ではB2セル）を選択し、次の数式を入力します。

Excelのスピル機能により、1つのセルに数式を入力するだけで、集計結果が自動的に表示されます。

※スピルについては、バックナンバーをご参照ください。

【バックナンバー535】セル--スピル機能について

=PIVOTBY(売上金額表!D5:D15,売上金額表!E5:E15,売上金額表!H5:H15,SUM)

◆PIVOTBY関数で指定する項目について

　　　　集計したいデータに合わせて「行」「列」「値」「集計方法」を指定します。

①

②

③

　　　　　④

=PIVOTBY(売上金額表!D5:D15,売上金額表!E5:E15,売上金額表!H5:H15,SUM)

①

売上金額表!D5:D15：

行に表示する項目（例では、担当者名）

②

売上金額表!E5:E15：

列に表示する項目（例では、商品）

③

売上金額表!H5:H15：

集計する値（例では、売上金額）

④

SUM：

集計方法（例では、合計）

今回の例では、担当者・商品ごとの「売上金額」を合計したいため、集計方法に SUM を指定しました。

SUM のほかにも、平均を求める AVERAGE や、データの件数を数える COUNT などが指定できます。

◆集計結果を自動更新

　　　　　元データを変更した場合、数式の結果に反映されるため、集計表を作り直す必要がありません。

◆PIVOTBY関数とピボットテーブルの違い

用途や操作方法に応じて、それぞれを使い分けることができます。

・ PIVOTBY関数：

数式で集計方法を指定するため、集計の条件や内容を数式として

管理できることが特徴です。

・ピボットテーブル：

項目をドラッグ＆ドロップして配置することで、

数式を使わずに集計表を作成できることが特徴です。

PIVOTBY関数

ピボットテーブル

作成方法

数式を入力

画面操作で各項目を配置

集計表の更新

数式の計算結果として反映

「更新」操作が必要

集計条件

数式で指定

フィールド画面で指定

並べ替え・フィルター

数式の指定で設定可能

画面上で操作可能

集計表のレイアウト

数式の結果として決まる

行・列・値などをドラッグして配置

集計表の編集

数式を変更

フィールド画面を操作

■ ご参考までに

元データをテーブル化すると、データを追加・削除した場合も、集計結果に自動的に反映されます。

=PIVOTBY(テーブル1[担当者],テーブル1[商品],テーブル1[売上金額],SUM)