ExcelのPIVOTBY関数って知ってる？データ集計に便利な関数の使い方

ExcelのPIVOTBY関数の使い方が分からないという人もいるのではないだろうか。PIVOTBY関数はデータの集計を簡単に行える便利な関数だ。

本記事ではExcelのPIVOTBY関数の使い方を解説する。PIVOTBY関数のオプションの使い方についてもまとめた。

PIVOTBY関数とは？

PIVOTBY関数はデータ集計を行うための関数である。

まずPIVOTBY関数は、作成した表内の2つの列をチェックする。2つの列のデータを行・列という2つの軸に沿ってグループ化させる。最後に3つ目の列のデータを、その2つの軸に沿って集計する。

たとえば、A列に日付、B列に商品、C列に売上金額が書かれた表があるとする。

PIVOTBY関数を使えば、日付・商品ごとに売上の合計金額をまとめることができる。

■PIVOTBY関数の基本構文

PIVOTBY関数の基本構文は次のとおり。

• =PIVOTBY({行},{列},{値},{集計方法})

1つ目に{行}、2つ目に{列}を指定する。3つ目の{値}を、4つ目の{集計方法}で集計することができる。

5つ目以降の引数にはオプションを指定できる。

PIVOTBY関数の基本的な使い方

PIVOTBY関数の基本的な使い方について解説する。

PIVOTBY関数で次の3つを行う方法を順番に見ていこう。

1. 合計を求める
2. 件数を求める
3. 平均を求める

■1.合計を求める

PIVOTBY関数で合計を求める方法を解説する。

以下の例では「=PIVOTBY(B2:B10,C2:C10,D2:D10,SUM)」としている。

B列に店舗名、C列に商品名が書かれている。店舗・商品ごとにD列の売上個数の合計を集計する。

実行すると、次のように「店舗」を行、「商品」を列とした表が作成される。店舗・商品ごとに売上個数が足し算されていることが分かる。

関連記事：Excelで数値を合計するには？「+」やSUM関数を使って計算する3つの方法

■2.件数を求める

続いて、件数を求める方法を解説する。

以下の例では「=PIVOTBY(B2:B10,C2:C10,D2:D10,COUNT)」としている。

今度は4つ目の引数に「COUNT」が指定されている。

実行すると、同じく店舗・商品ごとに集計が行われた。今回は、売上件数が集計されている。

関連記事：写真付きで解説！ExcelでCOUNTやSUMなどの関数を使って数値や個数のカウントを行なう方法

■3.平均を求める

今度は店舗・商品ごとの売上個数の平均を求めてみよう。

以下の例では「=PIVOTBY(B2:B10,C2:C10,D2:D10,AVERAGE)」としている。

実行すると、店舗・商品ごとの売上個数の平均が求められている。

たとえば、東京店舗の商品Aの売上個数は「1」「3」「4」なので、その平均の「2.666…」が表示されている。

関連記事：Excelで平均値を求めるには？AVERAGE関数やAVERAGEIF関数のかんたんな使い方

PIVOTBY関数のオプションの使い方

PIVOTBY関数の基本構文をおさらいする。

• =PIVOTBY({行},{列},{値},{集計方法})

基本的には4つの引数を指定するが、オプションとして5つ以上の引数を指定できる。オプションを指定することで、通常とは異なる集計を行えるようになる。

PIVOTBY関数のオプションを使って次の2つを行う方法を順番に見ていこう。

1. ヘッダーを表示する
2. 行ヘッダーに総計と小計を含める

■1.ヘッダーを表示する

PIVOTBY関数はオプションで表示・非表示を変えられる。

PIVOTBY関数の5つ目の引数は、値にヘッダーがあるかどうかを確認し、ヘッダーを表示するかどうかを決めるためのオプションである。

5つ目の引数に「3」を指定すれば、ヘッダーを表示させられる。ただし、行・列・値の引数にヘッダーが含まれている必要がある。

以下では「=PIVOTBY(B1:B10,C1:C10,D1:D10,SUM,3)」としている。

実行すると、以下のようにヘッダーが表示される。

■2.行ヘッダーに総計と小計を含める

PIVOTBY関数は、行が複数列の場合、行ヘッダーに総計や小計を含められる。

PIVOTBY関数の6つ目の引数は、行ヘッダーに総計や小計を含めるか決めるオプションである。6つ目の引数に「-2」を指定することで、総計と小計をそれぞれ出力する。

以下の例では「=PIVOTBY(B2:C10,,D2:D10,SUM,0,-2)」としている。

列は省略している。その代わりに、行を2列指定している（行集計）。

実行すると、次のような形式で集計される。上部に総計と小計が出る。

まとめ

本記事ではPIVOTBY関数とは何かについて解説した。最後にPIVOTBY関数の使い方についておさらいしよう。

◯PIVOTBY関数の構文

• =PIVOTBY({行},{列},{値},{集計方法})

◯PIVOTBY関数の使用例

1. 合計を求める
   例：=PIVOTBY(B2:B10,C2:C10,D2:D10,SUM)
   
2. 件数を求める
   例：=PIVOTBY(B2:B10,C2:C10,D2:D10,COUNT)
   
3. 平均を求める
   例：=PIVOTBY(B2:B10,C2:C10,D2:D10,AVERAGE)

PIVOTBY関数は少し処理内容は複雑だが、慣れれば既存の表から楽に集計ができて便利だ。

構成／編集部