Excelのピボットテーブルの作り方｜初心者向けに集計の基本を解説
本ページはプロモーションが含まれています
ピボットテーブルは、元の表を選んで「挿入」→「ピボットテーブル」を選び、項目を「行」と「値」にドラッグするだけで集計表が作れる機能です。関数を書かなくても、担当者別・月別・商品別の合計や件数をすぐに出せます。この記事では、作る前の表の準備から、基本の作り方、集計方法の変え方、データを更新するときの注意点までを解説します。
目次
ピボットテーブルでできること
ピボットテーブルは、たくさんの行がある一覧表（売上明細、問い合わせ記録など）を、項目ごとにまとめて集計する機能です。
たとえば次のような売上明細があるとします。
| 日付 | 担当者 | 商品 | 数量 | 金額 |
|---|---|---|---|---|
| 10/1 | 佐藤 | ペン | 10 | 1,000 |
| 10/1 | 鈴木 | ノート | 5 | 1,500 |
| 10/2 | 佐藤 | ノート | 3 | 900 |
この表から、次のような集計がマウス操作だけで作れます。
- 担当者ごとの金額の合計
- 商品ごとの販売件数
- 担当者×商品のクロス集計
- 月ごとの売上の推移
SUMIFやCOUNTIFでも同じ集計はできますが、集計の切り口を変えたいときにドラッグで入れ替えられるのがピボットテーブルの強みです。
作る前に元の表を整える
ピボットテーブルがうまく作れない原因の多くは、元の表の形にあります。作る前に次の点を確認しましょう。
- 1行目が見出し（項目名）になっている：見出しが空欄の列があるとエラーになります
- セルの結合をしていない：結合セルがあると正しく集計できません
- 途中に空白の行・列がない：空白行があると、そこで表が終わったと判断されることがあります
- 1行に1件のデータになっている：小計行や合計行は元の表に入れない
- 同じ列のデータの種類をそろえる：金額の列に「未定」などの文字が混ざっていると合計できません
元の表は「テーブル」にしておくと更新が楽
表の中のセルを選んで「Ctrl＋T」を押し、表をテーブルに変換しておくと、行を追加してもピボットテーブルの集計範囲が自動で広がります。テーブルにしていないと、追加した行が集計に含まれないことがあります。
ピボットテーブルの基本の作り方
ここでは「担当者ごとの金額の合計」を作る手順を例に説明します。メニューの表示名はお使いのExcelのバージョンで異なる場合があります。
- 元の表の中のセルを1つ選択する
- 「挿入」タブの「ピボットテーブル」をクリックする（ボタンにメニューが付いている場合は「テーブルまたは範囲から」を選ぶ）
- 表示された画面で、範囲が表全体になっているか確認する
- 配置する場所で「新規ワークシート」を選び、「OK」をクリックする
- 新しいシートと、右側に「ピボットテーブルのフィールド」が表示される
- フィールド一覧の「担当者」を、下の「行」の枠にドラッグする
- 「金額」を「値」の枠にドラッグする
これで、担当者ごとの金額の合計と総計が表示されます。
4つのエリアの役割
フィールド一覧の下にある4つの枠は、それぞれ次の役割があります。
| エリア | 役割 | 例 |
|---|---|---|
| 行 | 集計表の縦方向に並べる項目 | 担当者、商品 |
| 列 | 集計表の横方向に並べる項目 | 月、地域 |
| 値 | 集計する数値 | 金額、数量 |
| フィルター | 集計対象を絞り込む項目 | 店舗、年度 |
「行」に担当者、「列」に商品、「値」に金額を入れると、担当者×商品のクロス集計表になります。項目を枠から外したいときは、枠の外へドラッグするか、フィールド一覧のチェックを外します。
集計方法や表示を変える
合計ではなく件数や平均にする
「値」に入れた項目は、数値なら合計、文字が含まれる列なら個数で集計されるのが基本です。変えたいときは次の手順で操作します。
- 集計表の数値のセルを右クリックする
- 「値の集計方法」を選ぶ
- 「合計」「個数」「平均」「最大」「最小」などから選ぶ
金額の列なのに「個数」で集計されている場合は、元の表のその列に文字や空白のセルが混ざっていないか確認しましょう。
構成比（%）で表示する
担当者ごとの売上が全体の何%かを見たい場合は、数値のセルを右クリックし、「計算の種類」から「総計に対する比率」を選びます。
日付を月ごとにまとめる
日付を「行」に入れたときに日ごとに並んでしまう場合は、日付のセルを右クリックして「グループ化」を選び、「月」を選択すると月単位でまとめられます。バージョンによっては、日付を入れた時点で自動的に年や月にまとめられることもあります。
集計の元データを確認する
集計表の数値をダブルクリックすると、その数値の元になった明細だけが新しいシートに表示されます。「この合計はどのデータから来ているのか」を確認したいときに便利です。
元データを変えたら「更新」が必要
ピボットテーブルは、元の表を書き換えても自動では集計し直されません。元データを修正・追加したら、必ず更新の操作をしてください。
- ピボットテーブルの中のセルを右クリックする
- 「更新」を選ぶ
複数のピボットテーブルをまとめて更新したい場合は、ピボットテーブルを選んだときに表示される専用のタブ（「ピボットテーブル分析」など。名前はバージョンで異なります）の「更新」から「すべて更新」を選びます。
更新しても追加した行が反映されないとき
元の表をテーブルにしていない場合、集計範囲が最初に作ったときのままになっています。専用タブの「データソースの変更」から範囲を選び直すか、元の表をテーブルに変換してから範囲を指定し直してください。
うまく作れないときのチェックポイント
| 症状 | 主な原因 | 対処法 |
|---|---|---|
| 作成時にエラーが表示される | 見出しが空欄の列がある | 1行目の見出しをすべて入力する |
| 合計にならず個数になる | 数値の列に文字や空白がある | 元データの文字を数値に直す |
| 同じ担当者が2行に分かれる | 表記ゆれ・余分なスペース | 元データの表記をそろえる |
| 新しいデータが集計されない | 更新していない・範囲が古い | 更新する、データソースを確認する |
| 「(空白)」という行が出る | 元データに空欄のセルがある | 空欄を埋めるか、フィルターで除外する |
表記ゆれは、同じ名前なのに全角・半角やスペースが違うことで起こります。重複や表記の揺れを確認したいときは、Excelで重複を削除する関数｜UNIQUEとCOUNTIFの使い分けの方法でも一覧を確認できます。集計結果の中で目立たせたい行がある場合は、Excelの条件付き書式で行全体に色を付ける方法｜$の付け方で失敗しないも参考になります。
まとめ
- ピボットテーブルは「挿入」→「ピボットテーブル」から作り、項目をドラッグして集計する
- 作る前に、見出しの入力・結合セルなし・空白行なしの表に整える
- 「行」「列」「値」「フィルター」の4つのエリアに項目を入れて集計表を組み立てる
- 合計・個数・平均などは「値の集計方法」で切り替えられる
- 元データを変えたら「更新」が必要。元の表はテーブルにしておくと範囲の更新が楽になる
まずは「行」に1項目、「値」に1項目を入れる一番シンプルな形から作ってみてください。慣れてきたら「列」や「フィルター」を加えて、集計の切り口を増やしていきましょう。