ピボットテーブルの作り方: Excelで集計表を作る手順

ピボットテーブルは、表の行をRegionのようなカテゴリーでまとめ、Salesのような数値をグループごとに集計します。数式は要りません。作るには、データの中のセルをクリックして挿入 > ピボットテーブルを選び、OKを押して、Regionを行に、Salesを値にドラッグします。下のシートはピボットテーブルではなく、同じ集計を数式で作ったものなので、合計が変わる様子を確かめられます。

数式リセット

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

UNIQUEが各地域を1回ずつ並べ、SUMIFがそれを合計します。North 455、South 305、East 170で、全体の930のそれぞれ49%、33%、18%です。C3を185に変えると、Southの合計と3つの割合がすぐに変わります。ピボットテーブルでも同じ数値が表示されますが、更新した後でないと変わりません。

ピボットテーブルの作り方

始める前に元のデータを確かめてください。見出しの行が1行あり、すべての列に名前があること、1行に1件のデータであること、途中に空白の行や列がないこと、小計の行がないことです。

1. データの中のどれかのセルをクリックします。
2. 挿入 > ピボットテーブルを選びます(バージョンによっては挿入 > ピボットテーブル > テーブルまたは範囲から)。
3. Excelが範囲を入力します。新規ワークシートを選んでOKを押します。
4. 空のピボットテーブルが表示され、右側に列見出しを並べたピボットテーブルのフィールドウィンドウが表示されます。
5. Regionを行のボックスに、Salesを値のボックスにドラッグします。ピボットテーブルには各地域が1回ずつ、その隣に「合計 / Sales」が表示され、総計の行も付きます。
6. 表示を変えるには、フィールドをボックスの間でドラッグするか、ウィンドウの外にドラッグします。

どこから始めればよいかわからないときは、挿入 > おすすめピボットテーブルがデータに合った既製のレイアウトをいくつか表示します。Macでもメニューは同じで、挿入 > ピボットテーブルです。

行、列、値、フィルター

「ピボットテーブルのフィールド」ウィンドウには4つのボックスがあり、どのピボットテーブルも、どの列をどのボックスに入れるかの組み合わせです：

• 行：左側に縦に並ぶカテゴリーで、値の種類ごとに1行になります(Region)。
• 列：上に横に並ぶカテゴリーで、値の種類ごとに1列になります(Product)。
• 値：組み合わせごとに計算する数値です。数値の列では合計が既定で、個数、平均、最大、最小などは「値フィールドの設定」にあります。
• フィルター：ピボットテーブル全体を絞り込むフィールドで、上にドロップダウンとして表示されます。

Regionを行に、Productを列に、Salesを値に入れると、上のデータのピボットテーブルは次のようになります：

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

このレイアウトを数式で作るには、UNIQUEで地域を縦に、TRANSPOSE(UNIQUE())で商品を横に並べ、2つのリストを条件として受け取る1つのSUMIFSでマス目のすべてのセルを計算します：

数式リセット

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

E2はNorth、South、Eastを縦に、F1はAppleとPearを横にスピルし、F2のSUMIFSがその間の3行2列のマス目を埋めます。地域と商品の組ごとに1つの合計です。B5をAppleからPearに変えると、Eastの2つのセルが変わります。ここでの順番は値が最初に出てきた順で、ピボットテーブルはラベルをアルファベット順に並べます。

合計の代わりに個数、平均、割合を出す

ピボットテーブルでは、「値」ボックスのフィールドをクリックして値フィールドの設定を選びます。集計方法タブで合計、個数、平均、最大、最小を切り替え、計算の種類タブで数値を総計に対する比率、列集計に対する比率、累計などに変えます。どの数式にもそのまま対応するものがあります：

数式リセット

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Northは3件で平均151.7、Southは3件で平均101.7、Eastは2件で平均85.0です。総計に対する比率の列は、このページの最初のシートにあります。

1つの商品で集計を絞り込む

「フィルター」ボックスは、ピボットテーブルの上にドロップダウンを置きます。数式で作るなら、プルダウンリストのあるセルと、SUMIFに条件を1つ足したSUMIFSを使います。F1で商品を選んでください：

数式リセット

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Appleを選ぶと、Northは215、Southは150、Eastは60です。Pearを選ぶと、240、155、110に変わります。さらに条件を足す方法はSUMIFSを、Excelでリストを付ける方法はプルダウンリストのページを参照してください。

ピボットテーブルを更新する

ピボットテーブルは元のデータのコピー(ピボットキャッシュ)を持っていて、元のセルが変わっても再計算されません。データを編集した後は：

• ピボットテーブルのどこかを右クリックして更新を選ぶか、WindowsではAlt+F5を押します。
• データ > すべて更新(Ctrl+Alt+F5)は、ブックのすべてのピボットテーブルを更新します。
• ファイルを開くたびに更新するには、ピボットテーブルを右クリックしてピボットテーブルオプションを選び、データタブでファイルを開くときにデータを更新するにチェックを入れます。

元の範囲の下に足した新しい行は、更新しても含まれません。ピボットテーブル分析 > データソースの変更で範囲を変えるか、もっとよいのは、ピボットテーブルを作る前に元のデータをテーブルにすることです。データを選択してCtrl+Tを押すか、挿入 > テーブルを使います。テーブルは行を足すと広がり、ピボットテーブルは次の更新でそれを取り込みます。

1つの数式ですべての地域を合計する

数式リセット

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: F2で、E2:E4に並んだすべての地域の売上を1つの数式で合計してください。

答え合わせ答えを見る

答えは455、305、170をスピルします。SUMIFにリストE2:E4全体を条件として渡すと地域ごとに1つの合計が返るので、下にコピーするものはありません。Excel 2019以前にはUNIQUEもスピルもないので、E2:E4に地域を入力して=SUMIF($A$2:$A$9,E2,$C$2:$C$9)を下にコピーします。$記号がないと範囲が行ごとに下に動き、合計が間違います。

GROUPBYとPIVOTBY: 1つの数式でピボットテーブル

Microsoft 365のExcelには、1つの数式で集計表全体を作り、普通の数式と同じように更新なしで再計算する関数が2つあります。最新のMicrosoft 365のサブスクリプションが必要です。上のデータでは：

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

GROUPBYは、行のフィールド、値、関数(SUM、COUNTA、AVERAGE、MAX、PERCENTOF)を受け取ります。PIVOTBYはその間に列のフィールドを足します。どちらもピボットテーブルと同じように、ラベルを並べ替えて合計の行を付けます。

ピボットテーブルと数式、どちらを使うか

データを探索して一度だけ問いに答えるならピボットテーブルを、レポートに置き、ほかの数式に値を渡し、常に最新でなければならない集計には数式を使います。ピボットテーブルの数値を確かめるには、そのセルの1つをSUMIFSで作り直します。2つが合わなければ、たいていピボットテーブルの更新が必要か、元の範囲が短すぎます。