ピボットテーブルでデータ集計する方法｜初心者向け作成手順を解説

しーとちゃん

けんじさん、月ごと・担当者ごとの売上集計を頼まれたんですけど、関数を組み合わせるだけだとかなり大変で…

けんじ

それ、ピボットテーブルを使えば一瞬で終わるよ。

しーとちゃん

ピボットテーブルって名前は聞いたことあるんですけど、難しそうで避けてました…

けんじ

実は考え方さえわかれば、関数より簡単なくらいなんだ。今日は基本の作り方から説明するね。

VLOOKUPとXLOOKUPのような検索系の関数も便利ですが、「月ごとの売上合計」「担当者別の件数」といった集計作業では、ピボットテーブルの方が圧倒的に速く、しかも関数を1つも書かずに実現できます。

自分が新人の頃、月次の売上データを担当者別・商品別に何パターンも集計する作業に丸1日かかっていましたが、ピボットテーブルを覚えてからは同じ作業が数分で終わるようになりました。この記事では、初心者の方向けに基本の作成手順を解説します。

ピボットテーブルとは

ピボットテーブルは、大量のデータを「行」「列」「値」の組み合わせで自由に集計・並べ替えできる機能です。元データを直接編集することなく、視点（ピボット）を変えながら様々な角度で集計表を作れるのが最大の特徴です。

たとえば、「日付・担当者・商品名・売上金額」が入力された数百行のデータがあったとき、ピボットテーブルを使えば以下のような集計を、それぞれ数クリックで作成できます。

• 担当者別の売上合計
• 月別・商品別の売上件数
• 商品別の売上平均

ピボットテーブルの作成手順

手順1: 元データを準備する

ピボットテーブルを作る前に、元データの形式を整えておく必要があります。

• 1行目に見出し（列名）を入れる
• 空白の行・列を作らない
• セルの結合をしない
• 1つの列には同じ種類のデータだけを入れる

自分が最初によくやってしまったミスは、集計しやすいようにと元データに空白行を挟んでしまうことでした。これがあると、ピボットテーブルが正しくデータ範囲を認識できなくなります。

元データでよく起きるトラブルと、その原因を表にまとめておきます。

特に3つ目の「見えない空白」は厄介です。見た目はまったく同じなのに、ピボットテーブル上では別人として集計されます。自分は請求データでこれをやってしまい、合計が合わない原因を探すのに半日使ったことがあります。元データを受け取ったら、まずTRIM関数で余分な空白を落とすのが習慣になりました。

元データは「テーブル」にしておくと後がラクです。 元データのどこかを選んで Ctrl + T を押すとテーブルに変換され、以降は行を追加するだけで自動的に範囲が広がります。ピボットテーブル側は「更新」を押すだけで新しい行まで拾ってくれるので、毎月データが増えるファイルでは特に効きます。範囲を毎回指定し直す作業が丸ごとなくなります。

手順2: ピボットテーブルを挿入する

元データのセルを1つ選択した状態で、リボンの「挿入」タブから「ピボットテーブル」を選択します。データ範囲が自動的に認識されるので、内容を確認して「OK」を押すと、新しいシートにピボットテーブルの土台が作成されます。

手順3: フィールドを配置する

画面右側に表示される「フィールドリスト」から、集計したい項目を以下の4つのエリアにドラッグ＆ドロップします。

「担当者別の月次売上合計」を作りたい場合は、「行」に担当者名、「列」に月、「値」に売上金額をドラッグします。これだけで、自動的にクロス集計表が完成します。

値の集計方法を変更する

「値」エリアに数値を入れると、デフォルトでは自動的に合計（SUM）で集計されます。ただし、集計方法は自由に変更できます。

値エリアのフィールド名をクリックし「値フィールドの設定」を選ぶと、以下のような集計方法を選択できます。

自分がよく使うのは「個数」への変更です。たとえば「担当者ごとの受注件数」を知りたい場合、売上金額の列を値エリアに入れて集計方法を「個数」に変えるだけで、件数の集計表になります。

ここで1つ注意があります。「個数」と「数値の個数」は別物です。「個数」は空白以外のセルをすべて数えるのに対し、「数値の個数」は数値だけを数えます。金額欄に「―」や「未定」といった文字が混ざっているデータだと、この2つで結果がずれます。件数が想定と合わないときは、まずここを疑ってみてください。

同じフィールドを2回使ってもいい

意外と知られていませんが、同じ項目を値エリアに2回ドラッグできます。売上金額を2回入れて、片方を「合計」、もう片方を「平均」にすれば、担当者ごとの売上合計と平均単価を1つの表に並べられます。

自分は月次資料で、売上金額を3回入れて「合計・個数・平均」を横に並べる形をよく使います。合計だけを見ていると「件数は少ないが単価が高い担当者」と「件数は多いが単価が低い担当者」の区別がつかないためです。

「計算の種類」で比率や前月比を出す

集計方法の変更と並んで便利なのが、同じ「値フィールドの設定」の中にある計算の種類です。ここを使うと、電卓や追加の数式を一切使わずに、比率や増減を出せます。

前月比を出したいときは、「基準値に対する比率」を選び、基準フィールドを日付（月）、基準アイテムを「（前の値）」に設定します。これだけで、月ごとの前月比が並びます。なお、一番古い月は比較対象がないため100%と表示されるので、その行は非表示にするか、資料上で注記しておくと親切です。

自分がこれを知る前は、ピボットテーブルの隣に手作業で =B3/B2 のような数式を並べていました。行が増えるたびに数式を引き直す必要があり、しかも並べ替えをすると壊れます。計算の種類を使えば、行が増えても勝手に追従してくれます。

ピボットテーブルの結果を別のセルで参照するときの落とし穴

ピボットテーブルの数値を別のセルで使おうとして = を打ってからセルをクリックすると、=GETPIVOTDATA("売上金額",$A$3,...) という長い数式が勝手に入ります。これに戸惑う人はとても多いです。

GETPIVOTDATA自体は「レイアウトが変わっても正しい値を取ってくる」という利点のある関数なのですが、単純に隣のセルへコピーしたいだけの場合は完全に邪魔になります。オートフィルしても同じセルを参照し続けてしまうためです。

自動挿入を止めたい場合は、「ファイル」→「オプション」→「数式」から「ピボットテーブル参照にGetPivotData関数を使用する」のチェックを外します。 以降は普通のセル参照（=B5 など）が入るようになります。自分は業務用のPCでは最初にこの設定を切っています。

日付データを月・四半期でグループ化する

元データに日付が入っている場合、ピボットテーブル上でその日付を右クリックし「グループ化」を選ぶと、月・四半期・年単位でまとめて集計できます。これにより、日別データしかなくても「月別の推移」をすぐに確認できるようになります。

元データを更新したときの注意点

ピボットテーブルは、元データを更新しても自動では反映されません。データを追加・修正したあとは、ピボットテーブル上で右クリックして「更新」を選ぶか、リボンの「ピボットテーブル分析」タブから「更新」ボタンを押す必要があります。

自分もこれを忘れて、古いデータのまま資料を提出しそうになったことが何度かあります。定期的にデータを追加するファイルでは、更新を習慣にすることを強くおすすめします。ショートカットは Alt + F5（全体を更新するなら Ctrl + Alt + F5）です。よく使うキー操作はよく使うショートカットキー一覧にまとめています。

ファイルを開いたときに自動更新させることもできます。ピボットテーブルを右クリックして「ピボットテーブル オプション」→「データ」タブ→「ファイルを開くときにデータを更新する」にチェックを入れておけば、開き忘れによる古い数字を防げます。共有ファイルではこれを入れておくと安心です。

更新のたびに列幅が元に戻ってしまうのも、地味にストレスの溜まる挙動です。これも同じ「ピボットテーブル オプション」の「レイアウトと書式」タブで、「更新時に列幅を自動調整する」のチェックを外せば止まります。せっかく整えた資料の体裁が毎回崩れる、という悩みはこれで解消します。

見た目を整えるひと手間

ピボットテーブルは初期状態のままだと、資料としてはやや読みにくい形をしています。自分が必ず行っている調整は次の3つです。

• 空白セルに0を表示する。 「ピボットテーブル オプション」→「レイアウトと書式」で「空白セルに表示する値」に 0 を入れます。空欄のままだと「データがない」のか「入力漏れ」なのか読み手が判断できません
• 数値に桁区切りを入れる。 値フィールドの設定内にある「表示形式」ボタンから設定します。セルに直接書式を設定すると更新時に消えることがあるので、こちらから設定するのが確実です
• レイアウトを「表形式」にする。 「デザイン」タブ→「レポートのレイアウト」→「表形式で表示」を選ぶと、項目名が各列に並んだ見慣れた表の形になります。初期状態の「コンパクト形式」は1列に階層が押し込まれるため、他の人に渡す資料では読みにくいことが多いです

この3つをやるだけで、「ピボットテーブルの生出力」ではなく「作り込んだ集計表」に見えます。会議に出す資料では、ここまでやっておくと質問の量が明らかに減りました。

スライサーで見やすく絞り込む

「ピボットテーブル分析」タブの「スライサーの挿入」を使うと、担当者や地域などの項目をボタン形式で表示し、クリックひとつでデータを絞り込めるようになります。

自分は月次会議の資料を作る際、スライサーを使って「その場で担当者を切り替えながら数字を見せる」形にしています。関数だけで同じことをしようとすると複雑な式が必要になりますが、スライサーならクリックだけで済むので、資料作成の手間も大幅に減らせます。

日付で絞り込みたい場合は、スライサーの代わりにタイムライン（「ピボットテーブル分析」タブ→「タイムラインの挿入」）が便利です。スライダーをドラッグするだけで期間を変えられるので、「直近3ヶ月だけ見せる」といった操作がその場でできます。

なお、スライサーは複数のピボットテーブルに同時接続できます。スライサーを右クリックして「レポートの接続」を開き、連動させたいピボットテーブルにチェックを入れるだけです。売上表と件数表を並べたダッシュボードで、1つのスライサーを操作すれば両方が同時に切り替わる、という作り方ができます。自分が月次資料をこの形にしてから、会議中に「じゃあA支店だけだと？」と聞かれてもその場で答えられるようになりました。

Googleスプレッドシートでも同じことができる

Excelを持っていない環境でも、Googleスプレッドシートに同等のピボットテーブル機能があります。操作の入り口が違うだけで、考え方は同じです。

大きな違いは最後の行で、スプレッドシート版は元データを直せば自動で集計に反映されます。「更新を押し忘れて古い数字のまま」という事故が起きないのは、共同編集の場面では明確な利点です。逆に、扱えるデータ量や細かい書式設定はExcelの方が優れています。使い分けの詳細はGoogleスプレッドシートとの連携と共同編集の基本にまとめています。

つまずきやすいポイントのまとめ

最後に、自分が実際に困ったことのある挙動を一覧にしておきます。

3つ目の「削除した項目が残る」は、退職した担当者の名前がいつまでもスライサーに出続けるケースで、意外と気づきにくい問題です。設定を1回変えるだけで消えます。

まとめ

ピボットテーブルは、関数を使わずに大量データを様々な角度から集計できる強力な機能です。まずは「行」「列」「値」の3つのエリアにフィールドをドラッグする感覚をつかむところから始めて、慣れてきたらグループ化やスライサーも活用してみてください。

覚える順番としては、次のステップがおすすめです。

1. 元データをテーブル化（Ctrl + T）してから作る
2. 「行」「列」「値」にドラッグして集計表を作る
3. 集計方法を「合計」以外に変えてみる
4. 日付をグループ化して月別にする
5. 「計算の種類」で構成比や前月比を出す
6. スライサー・タイムラインで絞り込めるようにする

自分の実感として、2までで日常業務の8割は片付きます。3以降は必要になったときに1つずつ覚えれば十分です。最初から全部を覚えようとして挫折するより、まず1つの集計表を自分の手で作ってみる方が確実に身につきます。

集計した数字の中から「異常な値」や「基準を下回った項目」を見つけたいときは、条件付き書式を組み合わせると、目で探す手間そのものがなくなります。合計や平均といった基本の集計関数についてはExcel関数の基本にまとめています。

しーとちゃん

関数を1個も書かずにこんなに集計できるんですね！今度の月次報告、ピボットテーブルで作ってみます。

けんじ

きっと驚くくらい時間が短縮できるはずだよ。データの更新だけは忘れないようにね。