Excelピボットテーブルの基本｜元データの整え方、集計の組み立て方、更新されないときの確認
ピボットテーブルを作る手順
空の集計表を用意する
元データが整ったら、ピボットテーブルを作ります。手順は次のとおりです。
- 元データの中のセルを一つ選ぶ。
- 「挿入」タブの「ピボットテーブル」を選ぶ。
- 表示された画面で、元データの範囲（テーブル名）が正しいかを確かめる。
- 置き場所は「新規ワークシート」を選び、OKを押す。
新しいシートの左側に空の集計表の枠が、右側に「ピボットテーブルのフィールド」という作業窓が表示されます。作業窓の上には元データの見出しの一覧があり、下には「フィルター」「列」「行」「値」の4つの枠があります。
作業窓が消えてしまったときは、集計表の中のセルを選ぶと、もう一度表示されます。それでも出ないときは、「ピボットテーブル分析」タブの「フィールドリスト」を押します。
4つの枠の役割
4つの枠は、集計表のどこに何を置くかを決める場所です。見出しの一覧から項目をドラッグして、それぞれの枠に入れます。
初めて作るときは、「行」と「値」の2つだけを使うのがおすすめです。たとえば「店舗」を行へ、「金額」を値へ入れると、店舗ごとの金額の合計が縦に並んだ表ができます。これだけで、手で小計を足していた作業の多くが置き換わります。
列とフィルターを足していく
行と値で表ができたら、次に「商品」を列へ入れてみます。すると、店舗を縦、商品を横に並べた表になり、どの店舗でどの商品が売れているかが一目で分かります。
項目が多すぎて表が横に広がりすぎるときは、列から外して「フィルター」に移します。フィルターに入れた項目は表の上に絞り込みの欄として表示され、特定の商品だけ、特定の担当者だけに絞った集計を切り替えて見られます。
表の形が分かりにくくなったら、枠から項目を作業窓の外へドラッグすれば外せます。何度入れ替えても元データは変わらないので、見たい切り口が見つかるまで試してください。
合計・個数・平均を切り替える
値フィールドの設定
値の枠に入れた数字は、最初は「合計」か「個数」で集計されます。集計方法を変えるには、値の枠にある項目（「合計 / 金額」など）をクリックし、「値フィールドの設定」を選びます。
画面の「集計方法」の一覧から、合計・個数・平均・最大・最小などを選べます。たとえば同じ「金額」の列でも、合計にすれば売上の総額、個数にすれば販売の件数、平均にすれば1件あたりの金額になります。
| 集計方法 | 分かること | 使う場面の例 |
|---|---|---|
| 合計 | 数字を足した総額 | 店舗別の売上 |
| 個数 | 記録が何件あるか | 担当者別の対応件数 |
| 平均 | 1件あたりの値 | 1回の注文の平均金額 |
| 最大・最小 | 一番大きい値・小さい値 | 1日の最大販売数 |
同じ項目を2回入れる
値の枠には、同じ項目を2回入れることができます。たとえば「金額」を2回入れて、一方を合計、もう一方を個数にすれば、店舗ごとの売上総額と販売件数を並べて見られます。
値フィールドの設定の画面では、表示される名前も変えられます。「合計 / 金額」のままだと分かりにくいので、「売上金額」「件数」などの名前にしておくと、人に見せる表として使いやすくなります。名前は、元データの見出しと同じ名前にはできないので、少し違う言葉を選びます。
金額の表示形式をそろえる
集計された金額に桁区切りのカンマを付けたいときは、値フィールドの設定の左下にある「表示形式」から設定します。集計表のセルを選んで普通の表示形式を変えることもできますが、集計表を作り直したり更新したりしたときに元に戻ってしまうことがあります。値フィールドの設定から変えておけば、更新しても形式が保たれます。
グループ化と比率で見え方を変える
日付を月や四半期にまとめる
日付の列を行に入れると、版によっては自動で「年」「四半期」「月」にまとめて表示されます。自動でまとまらないときや、まとめ方を変えたいときは、集計表の日付のセルを右クリックして「グループ化」を選びます。
グループ化の画面では、「月」「四半期」「年」などを選べます。月だけを選ぶと、年が違っても同じ月がまとめて集計されるため、2年分のデータなら「1月」に2年分の合計が入ってしまいます。年をまたぐデータでは、「年」と「月」の両方を選ぶようにしてください。
グループ化ができないときは、日付の列に文字列や空白が混ざっていることが多いです。見た目は日付でも文字として入っているセルがあると、その列全体をまとめられません。日付の見え方が崩れているときの直し方は、Excelで日付や数字の表示がおかしいときを参考にしてください。
数字を範囲でまとめる
金額や年齢などの数字も、グループ化で範囲ごとにまとめられます。数字のセルを右クリックして「グループ化」を選び、「先頭の値」「末尾の値」「単位」を指定します。たとえば先頭を0、単位を1000にすれば、「0〜999」「1000〜1999」のような区切りで件数を数えられます。
この方法を使うと、「1回の注文金額がどの範囲に多いか」のような分布がすぐに分かります。区切りの幅は、細かすぎると表が長くなり、粗すぎると違いが見えなくなるので、何回か変えて見やすい幅を探します。
合計に対する割合を出す
数字の大きさだけでなく、全体に占める割合を見たいこともあります。値フィールドの設定の「計算の種類」タブを開き、「総計に対する比率」を選ぶと、それぞれの数字が全体の何パーセントかで表示されます。
ほかにも、列の合計に対する比率、行の合計に対する比率、前の項目との差分などが選べます。店舗ごとの売上の構成比や、前の月からの増え方を見たいときに便利です。割合と元の金額を両方見たいときは、先ほどのように同じ項目を値に2回入れ、一方だけを比率の表示にします。
データを足したら更新する
更新は自動ではない
ピボットテーブルでいちばんつまずきやすいのが、更新です。元データに行を足したり値を直したりしても、集計表は自動では変わりません。集計表のセルを選んで右クリックし、「更新」を選ぶ必要があります。
「ピボットテーブル分析」タブの「更新」ボタンからも更新できます。一つのファイルに集計表が複数ある場合は、「すべて更新」を使うとまとめて更新できます。ファイルを開いたときに自動で更新したい場合は、ピボットテーブルのオプションの「データ」タブで「ファイルを開くときにデータを更新する」に印を付けます。
範囲の外の行は反映されない
更新しても新しい行が反映されないときは、元データの範囲を確認します。元データをテーブルにせず、普通のセル範囲から作った場合、範囲は作ったときの行までで固定されます。その下に行を足しても、範囲の外なので集計に入りません。
範囲を広げるには、「ピボットテーブル分析」タブの「データソースの変更」を選び、範囲を指定し直します。毎回の手間を減らすなら、元データをテーブルに変換し、データソースにテーブル名を指定しておくのが確実です。
数字の明細をたどる
集計表の数字が正しいか確かめたいときは、その数字のセルをダブルクリックします。すると新しいシートが作られ、その数字のもとになった元データの行だけが一覧で表示されます。
この一覧を見れば、想定外の行が混ざっていないか、表記ゆれで別の項目に分かれていないかを確認できます。確認が終わったら、作られたシートは削除してかまいません。このシートは元データの写しなので、ここで値を直しても元データには反映されない点に注意してください。
