Excelピボットテーブルの基本｜元データの整え方、集計の組み立て方、更新されないときの確認

よくあるつまずきと直し方

合計のはずが個数になる

金額の列を値に入れたのに「個数 / 金額」と表示されることがあります。これは、その列に空白のセルや、文字列として入った数字が混ざっているときに起こりやすい現象です。値フィールドの設定で合計に切り替えることはできますが、文字列の数字はそのまま合計から抜け落ちます。

元データの金額の列を確かめ、左上に緑の三角が付いているセルや、左寄せで表示されている数字がないかを探します。数値に直したら、集計表を更新します。合計の数字が、元データを手で足した結果と合うかを一度確かめておくと安心です。

「(空白)」という項目が出る

集計表に「(空白)」という項目が表示されるのは、元データにその列が空のセルがあるからです。入れ忘れなのか、もともと値がない記録なのかを確かめ、必要なら元データを直します。

元データの範囲を広めに指定していて、下の空の行まで範囲に入っている場合にも「(空白)」が出ます。範囲を指定し直すか、テーブルを元データにすれば解消します。

レイアウトを見やすくする

行に2つ以上の項目を入れると、最初の形では項目名が1つの列に段差を付けて並び、表として読みにくいことがあります。「デザイン」タブの「レポートのレイアウト」から「表形式で表示」を選ぶと、項目ごとに列が分かれた表になります。

同じタブの「アイテムのラベルをすべて繰り返す」を選ぶと、空白になっていた項目名が各行に表示されます。集計結果を別の表に貼り付けて使うときは、この形にしておくと扱いやすくなります。小計や総計が不要なら、同じタブの「小計」「総計」から表示しない設定にできます。

並び順を変えたい

集計表の項目は、最初は名前の順（あいうえお順や番号順）で並びます。売上の多い順に並べたいときは、値の列のセルを右クリックし、「並べ替え」から「降順」を選びます。上位の数件だけを見たいときは、行の項目の絞り込みボタンから「値フィルター」の「トップテン」を使うと、上位の件数を指定して表示できます。

「4月・5月・6月」のように決まった順番で並べたい項目が、思った順にならないこともあります。その場合は、項目名のセルを選んで枠の端をつかみ、ドラッグして好きな位置へ動かせます。

消したはずの項目が絞り込みに残る

元データからある商品をすべて消して更新したのに、絞り込みの一覧にその商品名が残ることがあります。これは、ピボットテーブルが過去に出てきた項目を覚えているためです。ピボットテーブルのオプションの「データ」タブにある「各フィールドに保持するアイテム数」を「なし」にして更新すると、今の元データにある項目だけが残ります。

参照したセルにGETPIVOTDATAが入る

集計表の外のセルで「=」と入力してから集計表の数字をクリックすると、普通のセル番地ではなく、GETPIVOTDATAという関数が入ることがあります。これは、項目名を手がかりに集計表から値を取り出す関数です。集計表の形が変わっても同じ数字を取り出せる利点がありますが、式を下へコピーしても参照先がずれないため、戸惑う人も多いところです。

普通のセル参照にしたいときは、クリックではなくセルの番地を直接入力します。いつも普通の参照にしたい場合は、「ピボットテーブル分析」タブの「オプション」の横にある下向きの印から、「GetPivotData の生成」の印を外します。

もう一歩進んだ使い方

スライサーで絞り込みを押しやすくする

フィルターの代わりに、スライサーを使う方法もあります。集計表を選んで「ピボットテーブル分析」タブの「スライサーの挿入」を選び、絞り込みたい項目（店舗など）に印を付けると、項目名が並んだボタンの枠が表示されます。

ボタンを押すだけで絞り込めるので、Excelに慣れていない人と表を共有するときにも使いやすくなります。日付で絞り込みたいときは、期間をつまみで選べる「タイムライン」も使えます。

元の表と集計表を分けて管理する

ピボットテーブルを長く使うなら、元データのシートと集計表のシートを分けておくのがよい方法です。元データのシートには記録を足すことだけをし、集計や見た目の調整は集計表のシートで行います。こうしておくと、どこを直せばよいかが分かりやすくなります。

別の表の情報を元データに加えたいとき（商品番号から商品の分類を引いてくるなど）は、元データの側にXLOOKUPなどで列を足してから集計すると、分類ごとの集計ができるようになります。商品の分類のような情報は、元データとは別の小さな対応表にまとめておき、元データの側で引いてくる形にすると、分類を変えたときも対応表を直すだけですみます。

関数と使い分ける

ピボットテーブルは、全体の傾向をつかんだり、いろいろな切り口を試したりするのに向いています。一方で、毎月同じ形の報告書を作り、決まったセルに数字を入れる作業なら、SUMIFSやCOUNTIFSなどの関数の方が扱いやすいこともあります。

まずピボットテーブルで全体を眺めて、どの切り口が大事かを見つけ、その切り口だけを関数で決まった表にする。この順番で進めると、無駄な集計を作らずにすみます。

まとめ

ピボットテーブルは、元データを書き換えずに、行・列・値・フィルターの4つの枠へ項目を置くだけで集計表を作れる機能です。うまく使えるかどうかは、1行に1件、1列に1種類の情報という元データの形で大きく変わります。結合セルや小計の行、表記ゆれ、文字列の数字を先に直しておくと、集計が合わないというつまずきの多くを防げます。

集計方法は値フィールドの設定で切り替え、日付や数字はグループ化でまとめ、割合は計算の種類で表示できます。元データを直したり行を足したりしたら、最後に必ず更新することを忘れないでください。

最初の一歩として、手元にある一覧表を一つ選び、Ctrl＋T でテーブルに変換してから、ピボットテーブルを作ってみてください。行に一つの項目、値に金額や数量を一つ入れるだけで、これまで手で足していた小計がすぐに表になるはずです。