Microsoft365版のExcelでは、新しくPIVOTBY関数を使えるようになりました。PIVOTBY関数は、ピボットテーブルの機能を使用せず、2つのカテゴリで縦方向・横方向にクロス集計を行うことができます。
PIVOTBY関数を使ってクロス集計を行う
たとえば、このような顧客の売上を記録した営業記録表があります。この表からPIVOTBY関数を使って顧客×商品のクロス集計を作成したいと思います。
| PIVOTBY(グループ化列1, グループ化列2, 集計列, 集計方法, [見出し], [行の合計], [行の順序], [列の合計], [列の順序],[フィルター]) |
|---|
| 任意の2つのカテゴリで集計を行うことができる。 「グループ化列1」「グループ化列2」で集計したいカテゴリの列を選択を選択し、集計列で計算に使用したい数値データの列を選択する。集計方法には、合計、構成比、平均など計算の種類を指定する。 その他、見出し、合計、順序、フィルターなどを指定することで、カスタマイズすることができる。 |
PIVOTBY関数を挿入し、グループ化列1には集計したいカテゴリの列を一つ選択します。ここでは、顧客の列を選択します。グループ化列2にはもう一つ集計したいカテゴリの列を選択します。ここでは、商品の列を選択します。集計列には計算に使用したい数値データの列を選択します。ここでは、売上の列を選択します。集計方法では、計算の種類を指定します。ここでは合計のSUMを指定します。
=PIVOTBY(B1:B25,C1:C25,E1:E25,SUM)
最低限、この4つの引数を入力すれば、クロス集計表を作成することができます。Enterで確定すれば、縦軸が顧客・横軸が商品のクロス集計表が作成されます。
計算の方法は合計だけでなく、様々なものを選択できます。「PERCENTOF」を選択すれば、売上の構成比が表示されます。
「AVERAGE」を選択すれば、売上の平均が表示されます。
5つ目以降の引数を設定すれば、クロス集計表をカスタマイズすることができます。5つ目の引数には見出しを表示させるかを選択します。元の表と同じ見出しを表示させる場合は、「3」を選択します。
=PIVOTBY(B1:B25,C1:C25,E1:E25,SUM,3)
そうすると縦軸・横軸のそれぞれに顧客・商品という見出しが表示されます。
6つ目、7つ目の引数では、行の表示についてカスタマイズすることができます。標準では、クロス集計表の一番下に合計が表示されていますが、6つ目の引数で0を指定すれば、一番下の合計欄を削除することができます。
=PIVOTBY(B1:B25,C1:C25,E1:E25,SUM,3,0)
また、7つ目の引数では並び替えの順序を指定できます。今は顧客が五十音順に並んでいますが、「-1」を指定すれば逆順に並び替えることができます。
=PIVOTBY(B1:B25,C1:C25,E1:E25,SUM,3,0,-1)
また、売上の大きい順に並び替えたい場合は、「-2」を指定します。そうすると、合計欄の売上を基準として売上の大きい順に並び替えることができます。
=PIVOTBY(B1:B25,C1:C25,E1:E25,SUM,3,0,-2)
8つ目、9つ目の引数では、列の表示についてカスタマイズすることができます。基本的には行方向と同じで、8つ目の引数では合計欄の表示について設定でき、9つ目の引数では並び替えの設定ができます。
たとえば、8つ目の引数に「0」を指定すれば、右側の合計欄を削除することができます。
=PIVOTBY(B1:B25,C1:C25,E1:E25,SUM,3,0,-2,0)
また、9つ目の引数に「-2」を指定すれば、商品別の売上の大きい順に並び替えることができます。
さらに10個目の引数では、集計を行う条件を指定できます。たとえば、D列の取引種別に「重要」と書かれているデータだけを抽出して集計したい場合、D列を選択し、=”重要”と入力します。
=PIVOTBY(B1:B25,C1:C25,E1:E25,SUM,3,0,-2,0,D1:D25=”重要”)
これで、取引種別が重要となっているものだけ集計されます。
設定できる引数自体は多いですが、最初の4つの引数さえ入力すれば、簡単にクロス集計表を作成することができます。
GROUPBY関数でも解説しましたが、データの追加を反映したい場合は、元の表をあらかじめテーブルに変換しておきます。表の上にカーソルを合わせ、Ctrl+Tを押してテーブルに変換します。
あとは先ほどと同じようにPIVOTBY関数を入力します。
=PIVOTBY(テーブル1[[#すべて],[顧客]],テーブル1[[#すべて],[商品]],テーブル1[[#すべて],[売上]],SUM)
これで下にデータを追加してもGROUPBY関数の集計結果に反映されます。
コメント