Excelで集計表を作ろうとして、
- 関数をコピーして
- 行を追加するたびに式を直して
- 気づいたら表がぐちゃぐちゃ…
そんな経験はありませんか？
実は、Excelの集計作業のほとんどは「ピボットテーブル」で一瞬で終わらせることができます。
関数だと『条件が増えるたびに数式を書き直す』必要がありますが、ピボットテーブルならマウスで項目を動かすだけ。数式は1つも書きません！
本記事では、練習用Excelファイルを使って
元データを追加・変更しても壊れにくいピボットテーブルの作り方
を、画像付きで丁寧に解説します。
動画や画像を見ながら同じ操作をすれば、
Excel初心者でも10分程度で基本操作をマスターできます。
ぜひ練習用ファイルをダウンロードして、一緒に操作しながら進めてみてください。
壊れにくいピボットテーブルの考え方
ピボットテーブルがうまく使えない原因は、
操作が難しいからではなく、元データの扱い方にあることがほとんどです。
多くの場合、
- 元データがテーブルになっていない
- 行を追加したのに集計に反映されない
- 更新方法が分からず作り直している
といった状態でつまずきます。
そのため、まずは元データをテーブル化します。
- 元データをテーブル化
- ピボットテーブルを正しい手順で作成
- データ追加・変更時は更新するだけ
元データをテーブル化することで、一度作ったら何度でも使い回せる集計表になります。
テーブルにしておけば、行が増えてもExcelが自動で認識してくれるので、範囲の指定ミスや数式の直しは一切不要です！
※詳しい理由は[こちらの記事]で解説していますが、まずは「データの追加を自動で認識させる魔法」だと思って進めてみてください！
ピボットテーブルとは？
ピボットテーブルとは、
大量のデータをドラッグ操作だけで集計できるExcelの機能です。
例えば、
- 売上を「商品別」「月別」で集計
- 担当者ごとの合計金額を表示
- カテゴリ別の件数を確認
といったことが、数式を書かずに一瞬でできます。
まずは完成イメージを見てみましょう。
ピボットテーブルの作り方を動画で解説しています
このように、マウス操作だけで「店舗別>四半期別>月別>日別」と階層にしてデータ集計できるのが「ピボットテーブル」になります。
ピボットテーブルを挿入しよう
では、練習用ファイルを使って、実際にピボットテーブルを作っていきます。
ステップはたったの3つです！順番に見ていきましょう！
1.テーブル化
まず最初に、元データをテーブル化しておきます。
上の動画では元データをテーブル化する部分を省略していますが
データの増減があった場合に、データ範囲を自動拡張してくれるので
必ずテーブル化しておきましょう！
※テーブル機能の基本編は【こちら】
※テーブル化の操作を忘れてしまった方は、この動画の[01:26]あたりから見てみてください
テーブル化したら、テーブル名を付けるのを忘れないようにしましょう！
ここでは「取引明細」とつけておきます。
2.挿入
続いて、ピボットテーブルを挿入していきます。
テーブルのどこでも良いので、どこかをクリックした状態でリボンメニューの「挿入」タブをクリックし、一番左の「ピボットテーブル」アイコンをクリックします。
すると、「テーブルまたは範囲からのピボットテーブル」というダイアログボックスが出てきます。
内容を確認すると、「テーブル/範囲」の項目には、自動的にテーブルが選択されて、先ほどつけたテーブル名が入っているのが分かります。
3.配置
次に、ピボットテーブルを配置していきます。
先ほどのダイアログボックスの中ほどに「ピボットテーブルを配置する場所を選択してください」という項目があるので、配置したい方のラジオボタンをクリックします。
基本的にはデータとピボットテーブルは別シートに配置することが多いので、ここでも「新規ワークシート」と選択していきます。
選択したらOKボタンを押して、ダイアログボックスを閉じます。
すると、以下のようなシートが新しく作成されたと思います。
これでピボットテーブルを挿入することができましたが、この時点では、まだ何も集計されていないので、次のステップの「フィールド操作」で集計を行います。
ピボットテーブルのフィールド操作
では、いよいよ集計結果を表示していきましょう。
下の画像で赤く囲んだ「ピボットテーブルを操作するには～」と書いてある四角い箱をクリックしたら、リボンメニューに「ピボットテーブル分析」と「デザイン」というタブが表示されるのを確認してください。
「表示されないよ」という方は「ピボットテーブルを操作するには～」と書いてある
四角い箱を選択できていないと思いますので、再度確認してみてくださいね
表示されたことを確認したら、「ピボットテーブル分析」のタブをクリックしてください。
すると右側に「フィールドリスト」というアイコンが出るので、これをクリックします。
すると、画面右側に、「ピボットテーブルのフィールド」というものが出てきました。
ピボットテーブルでは、この「ピボットテーブルのフィールド」を使って集計内容を決めていきます。
この「ピボットテーブルのフィールド」内をもう少し見ていきましょう。
このフィールド内は、大きく2ブロックに分かれています。
まず赤で囲んだ上半分は、元データのヘッダーにある項目名が表示されています。
次に青で囲んだ下半分は、実際にピボットテーブルで集計するものを決めていくエリアになります。
上半分にある項目を、下半分にある集計エリアのフィルターや行、列、値などに配置していくことで、ピボットが集計をしてくれます。
- 行：分類したい項目
- 列：横に並べたい項目
- 値：集計したい数値
では、実際に操作してみましょう！
まず、「取引日」を「行」のボックスの中にマウスでドラッグアンドドロップします。
すると、先ほどまで何もデータが表示されていなかったA列にデータが表示されました。
すると、行ボックスの中に「月(取引日)」と「日(取引日)」という項目が自動生成されます。
そして、以下のようにプラスボタンが付与され、クリックで展開できるようになります。
一旦ここまで詳細なものは不要という場合は、先ほどの「ピボットテーブルのフィールド操作」行のボックスにある「月(取引日)」と「日(取引日)」を集計対象から除外することが可能です。
除外する場合は、以下の2通りのやり方があります。どちらでも、やりやすい方でOK！
- 画像左
行ボックスの消したい項目名の右にある下向き矢印をクリック → フィールドの削除を選択 - 画像右
上半分の項目名の中から、消したい項目名を探して、レ点チェックを外す
「あれ？私は『四半期』も勝手に出てきたよ！」という方も大丈夫です。
これはExcelが気を利かせてグループ化してくれているだけなので
不要なものは同じ手順で消してしまってOKです🐾
「月(取引日)」と「日(取引日)」を集計対象から除外したら、次に行ボックスの「取引日」の下に「店舗名」を入れてみましょう。
するとピボットテーブルは以下のような集計表になります。
これだと「日付」が大カテゴリになってしまうので、作りたいデータ形式になっていません。
こういう場合は、右側の「ピボットテーブルフィールド」の行ボックスで項目の順番を入れ替えてあげます。
すると、ピボットテーブルの集計結果も、大カテゴリが「店舗名」となりました。
今は全て展開されている状態になっているので、店舗名の左にあるマイナスボタンを押すと折りたたんだり、展開したりすることが可能です。
ただ、データ量が多い時に、ひとつずつ折りたたんでいくのは現実的ではないですよね。
その場合は、ピボットテーブルのどこでも良いので、どこかを選択した状態で、リボンメニューの「ピボットテーブル分析」を選択して、画像の赤枠で囲んでいるアイコンをクリックすると、一括で折りたたみが可能です。
青枠で囲んでいるアイコンをクリックすると、一括展開も可能です。
特定の店舗だけを展開・折りたたみしたい場合は、マウスで店舗名の左のボタンをクリック。
一括で展開・折りたたみをしたい場合はリボンメニューからと覚えておくと良いですね！
どちらもアイコンの上にマウスを持っていくと
「フィールドの折りたたみ」や「フィールドの展開」と出るので
使いたい方をクリックすればOKです！
では、次に横に並べたい集計項目を決定していきます。
ここでは「商品名」ごとに集計をしたいので、先ほどと同じように「商品名」を「列」のボックスの中にマウスでドラッグアンドドロップします。
すると、今後は横方向に「商品名」の列が追加されました。
最後に値に集計したい項目を入れていきます。
ここでは、売上金額を集計したいので「合計取引額」を「値」のボックスの中にマウスでドラッグアンドドロップします。
すると、ピボットテーブルの集計表に値が入りました！
関数などを一切使用せず、マウスだけで集計表が完成しました！
とりあえず『値』には数字が入っている項目を入れればOK！
よく見ると、空白になっているところがあります。
これは集計できていないわけではなく、ピボットテーブルでは、該当がない場合は空白になるのがデフォルト設定になっているためです。
この空白表示は「0」や「なし」など、任意で設定可能です。
ピボットテーブル内のどこでも良いので、どこかを選択した状態で、マウスを右クリックします。
するとメニューが出てくるので、下の方にある「ピボットテーブルオプション」を選択します。
「ピボットテーブルオプション」というダイアログボックスが出てくるので、真ん中より少し下にある「空白セルに表示する値」のテキストボックスに、表示させたい値を入れてOKで閉じます。
今回は「0」を指定しますが「なし」なども指定可能です。
先ほどは空白だったセルに「0」表示されました。
ピボットテーブルのグループ化
日付データがある場合は、月別・年別にまとめることもできます。
ピボットテーブルの作成は出来ましたが、店舗名の横にあるボタンをクリックして展開すると、日付が羅列されていて、「何月がいくら」がパッと見て分からないですよね。
ここを月別や年別・四半期別などでグループ化することが可能なので、やってみましょう。
最初に「四半期別」にグループ化してみます。
どの行でも良いので、日付の列でマウスを右クリックします。
すると真ん中より少し下あたりに「グループ化」があるので、これをクリックします。
現状は、デフォルトで以下のようになっていると思います。
この「日」と「月」をクリックで解除し「四半期」をマウスでクリックし、最後にOKを押します。
すると、このように「四半期」にまとめて集計結果が表示されました。
ですが、さすがにこれだとザックリ過ぎるので、この要領で「四半期」→「月」→「日」とボタンで展開できるよう階層になるようグループ化していきます。
先ほどと同じ要領で、日付の列で右クリック → グループ化 と進みます。
先ほど「四半期」だけにしたので「月」と「日」もクリックして追加して、OKボタンを押します。
階層を「月」までにしたい場合は「日」は選択しなくてOKです
これで「四半期」→「月」→「日」と階層になりました。
これだけで、日別 → 月別 → 年別や四半期別の集計が一瞬で切り替わります。
これができると、上司に『もう集計終わったの？！』って驚かれます（笑）
日付データの中に1つでも『空欄』や『日付以外の文字』が入っているとエラーでできない
というルールがあります。
グループ化できない時は、日付データの中に「空欄」や「日付以外の文字」がないかチェックしてみて！
→ その日付セルをダブルクリックして「カレンダー」が出てこない場合や
「2025/13/40」のような存在しない日付もエラーの原因になります🐾
にゃんこのひと言🐾
グループ化のエラーで30分溶かす前に、手元に「逆引き辞典」を一冊置いておくと安心です。ググるより早いことも多いですよ🐾
小計・総計の表示方法を変更しよう
このままでもピボットテーブルとして十分機能しますが、もう少し見た目を整えていきたいと思います。
ピボットテーブルは初期設定のままだと、
- 小計が多すぎる
- 小計は必要だけど、位置が…
- 総計がジャマ
など感じることがあります。
そんな時は、「デザイン」タブ →「小計」「総計」から表示方法を変更できます。
それぞれ順番に対応方法を見ていきましょう。
小計を消したい
ピボットテーブルはデフォルトでは、グループ化した全ての項目に小計が入ります。
結果、小計がかなり多くなり、小計が不要な人にとってはすごくジャマになります。
そういう場合は、そもそも小計を非表示にしてしまいましょう。
ピボットテーブル内のどこでも良いので、どこかを選択した状態で、リボンメニューの「デザイン」を選択して、一番左にある「小計」アイコンをクリックします。
いくつか選択肢が出てくるので、「小計を表示しない」を選択します。
先ほどまで表示されていた小計が非表示になりました。
小計は必要だけど、位置を変えたい
ピボットテーブルは、デフォルトでは小計は先頭行に表示されます。
もちろん先頭行に表示してほしい人やケースもあれば、末尾に表示してほしい人やケースもありますよね。
そういう場合は、小計の位置を「先頭」から「末尾」に変更しましょう。
ピボットテーブル内のどこでも良いので、どこかを選択した状態で、リボンメニューの「デザイン」を選択して、一番左にある「小計」アイコンをクリックします。
先ほどと同じく、いくつか選択肢が出てくるので、「全ての小計をグループの末尾に表示する」を選択します。
これで、先ほどまで先頭行にあった小計が、グループの末尾に移動しました。
末尾から先頭に変更したい時も同じ要領で
「グループの先頭に表示」を選択でOK！
総計を非表示にしたい
総計も、ピボットテーブルはデフォルトでは行・列どちらも表示されるので、必要に応じて表示状態を変更できます。
やり方は小計と同じ要領で、ピボットテーブル内のどこでも良いので、どこかを選択した状態で、リボンメニューの「デザイン」を選択して、左から2番目にある「総計」アイコンをクリックします。
この中から使用したいものをクリックすればOKです。
ここでは「列のみ集計を行う」に設定してみます。
先ほどまで表示されていた、H列の総計が非表示になりました。
このように、用途に合わせて、
- 小計を非表示
- 総計のみ表示
など、自由に調整してみましょう。
ピボットテーブルの色・デザインを変更しよう
見た目を整えたい場合は、同じく「デザイン」タブからスタイルを変更できます。
- 罫線あり／なし
- 色付き
- シンプル表示
実際にやってみましょう！
これまで同様に、ピボットテーブル内を選択した状態で、リボンメニューの「デザイン」を選択します。
例えば、縞模様(行)に✓を入れると、1行ごとに色が変わって見やすくなったりします。
データ量が多いと目が滑るので、縞模様になっていると地味にうれしいですよね。
色を変更したい時は、右側の「ピボットテーブル スタイル」から選びます。
資料提出用・社内用や集計用途別などで、色を使い分けるのがおすすめです。
他にも「ピボットテーブル スタイル」の右にある下向き矢印をクリックすると色々なスタイルが出てきます。「見やすい」を第一優先に、好みのものを選んでみてくださいね！
元データに追加があった場合の更新
実はピボットテーブルは、元データに行を追加しても、原則ピボットテーブルは自動では更新されません。
ですが、更新方法はとても簡単です。
まず、実際に「取引明細」のテーブルの最終行に移動します。
Ctrl + ↓ のショートカットキーが便利です🐾
最終行にデータを追加しましょう。
ここでは「12/31に広島店でキーボードMを1つ、19,800円」というデータを追加しました。
では「Sheet1」のピボットに戻ります。
更新の前後が分かりやすいように、追加した広島店の12月を展開しておきます。
ピポットテーブル内でマウスを右クリックし、出てきたメニューの上の方にある「更新」をクリックします。
これだけで、先ほど追加した「12/31にキーボードMを1つ、19,800円」というデータが反映されました！
数値や内容を修正した場合も、同じく「更新」を行えばOKです。
今回のように、12/31のデータではなく6月や7月のデータが漏れていた場合も、最終行に追加すればピボットテーブルがきちんと集計表にしてくれるので、とても楽ちんです！
ちなみに、ピボットテーブルの中にマウスを置いて
[Alt] + [F5] を押すだけでも更新できちゃいます！
慣れてきたらこっちの方が早いです🐾
最近のExcel（Microsoft 365など）を使っている方は
追加しただけで自動で反映される場合もありますが
基本はこの『更新』ボタンを押すと覚えておきましょう🐾
にゃんこのひと言🐾
ピボットテーブルをマスターすると、分析が楽しくてついついPCに張り付いちゃいます。首や肩がバキバキになる前に、スタンドで視線を上げて「疲れない環境」を作るのも、立派な仕事術です🐾
ピボットテーブルが更新されない場合のチェック項目
もしピボットテーブルを更新しても、最新の情報が反映されない場合は、以下を確認してみましょう。
- 元データがテーブル化されているか
テーブルになっていないと、追加した行が範囲外になってしまいます。 - データ範囲が正しいか
念のため「ピボットテーブル分析」タブの「データソースの変更」から、テーブル名が入っているか確認してみましょう
こうしたスタンドで画面の高さを調整するだけでも、長時間の分析作業による肩や首への負担がぐっと楽になります。
さらにExcelを極めて「残業ゼロ」を目指したい方へ🐾
本記事ではピボットテーブルの基本を解説しましたが、実務では
「もっと複雑な条件で集計したい」「思わぬエラーが出て30分溶かしてしまった」
という場面が必ず出てきます。
もし、あなたが「最短ルートで、誰にも負けない集計・分析スキルを身につけたい」と考えているなら、独学で試行錯誤するよりも、プロの解説動画を1回見てしまうのが一番の近道です。
世界最大級の学習プラットフォーム「Udemy」では、実務に即したピボットテーブルの神講座が多数用意されています。時間を買って一生モノのスキルを手に入れ、今日から「残業ゼロ」を実現しましょう！
＼ 30分溶かして悩む日々はもう卒業！ ／
👉 【Excelの神】ピボットテーブルを極めて集計・分析を自動化する最強講座
我流で10時間悩むより、プロの技を3時間で盗む方が、圧倒的に「定時退社」に近づきます。浮いた時間で、次はパワークエリに挑戦するのもアリですね。
まとめ
お疲れ様でした！
これで、壊れにくいピボットテーブルの基本操作は完了です。
今回のポイントは、以下の3点です。
- 元データはテーブル化しておく
- 集計はピボットテーブルに任せる
- データ追加・変更時は「更新」するだけ
一度この仕組みを作っておけば、
- 売上集計
- 在庫管理
- 業務レポート
など、さまざまな場面で使い回せます。
最初は少し難しく感じるかもしれませんが、一度慣れてしまえば、もう関数地獄には戻れません（笑）
ぜひ、日々の業務でたくさん活用して、自分だけの『最強の集計表』を作ってみてくださいね🐾