Excelのピボットテーブルとは?基本的な使い方、項目追加、更新、範囲変更、注意点などすべて解説。

Excelのピボットテーブルは、データ分析を行う上で欠かせない機能です。

ピボットテーブルを使いこなすことで、様々な切り口でデータを切り分け高速で示唆を導くことが可能になります。

特に「右クリック」はピボットテーブルを使いこなす上で最重要となり、ExcelCampでは「右クリックを制す者、ピボットテーブルを制す」とお伝えしています。

この記事ではExcelのピボットテーブルという機能の全容を、まだ使ったことがない方でもわかりやすく解説します。「右クリック」を含め、ショートカットで操作する方法も紹介しておりますのでぜひ参考にしてください。

ExcelCampはExcel初心者・苦手な方のための研修です。
・たった1日でExcel操作が「激変」します。
・講義中に反復練習するから、その場でExcelが得意になります。
・延べ2,000名以上が参加し、満足度は9.71(10点満点)です。

目次

ショートカットキーの表記について

ExcelCampではショートカットの表記を以下に統一しています。

・「&」は同時に押すショートカットです。たとえばCtrl&Cなら、Ctrlキーを押しながら同時にCキーも押すことで発動します。

・「+」は順番に押すショートカットです。たとえばAlt+E+Lなら、Altキーを押した後に一度指を離し、ELキーをそれぞれ順番に押すことで発動します。

ピボットテーブルとは?何に使う?

ピボットテーブルとは、Excelで集計したデータテーブルを分析するために使う機能です。データ分析のプロは、主にピボットテーブルを以下2つの用途で使用します。

用途1:様々な切り口で集計分析を行い「示唆」を導くため
用途2:データの構造を「理解・把握」するため

それぞれ解説します。

用途1:様々な切り口で集計分析を行い、示唆を導くため

ピボットテーブルを使うと、データの種類によりますが「店舗」「都道府県」「従業員数」「立地」など様々な切り口で「売上」「利益」「コスト」など集計したいデータを瞬時に分析できます。

例:都道府県ごとの売上平均(架空のデータです)

集計分析はSUMIFS関数COUNTIFS関数でもできますが、ピボットテーブルを使うことで関数を書くことなく圧倒的に速いスピードで分析できるため重宝します。

用途2:データの構造を「理解・把握」するため

データ分析の現場では、自分が作成していないExcelデータを分析する場面が多々あります。そのためまず行うべきは、共有されたExcelデータの構造理解です。

データ構造の理解・把握にもピボットテーブルが重宝します。

例えば以下のようなシートを共有されたとします。

ピボットテーブルを開き、「行」の項目にそれぞれ項目をいれることで、視覚的に分かりやすくどのようなデータが書かれているのか把握することができます。

確認のときには、特にデータに「ゆらぎ」や「異常値」がないかも同時にチェックします。

データの「ゆらぎ」とは

「完了」「完了済」「完了ずみ」「済」など、同じ意味の言葉が違う表現で書かれている状態。
データがゆらいでいるとExcel上では異なるデータだと認識されるため、分析の前に修正する必要がある。

ExcelCamp講師 野沢先生

以下、実際に「データの理解」のためにピボットテーブルを活用してみます

「利用店舗」を行にいれて中身を確認。赤枠の箇所でデータが「ゆらいでいる」ため修正が必要。
「月」と「回答日」を行にいれて確認。5月、6月の日毎のデータを集計していることがわかる。日付の誤りもなさそう。
「満足度」を行にいれると、「空白」のセルを発見。不明なのか記入漏れなのか、確認が必要。
ExcelCamp講師 野沢先生

データの「ゆらぎ」や「異常値」は、1つ含まれるだけでも分析結果を狂わせるため、データを確認・把握する段階で見つけておくことが大切です。

ExcelCampはExcel初心者・苦手な方のための研修です。
・たった1日でExcel操作が「激変」します。
・講義中に反復練習するから、その場でExcelが得意になります。
・延べ2,000名以上が参加し、満足度は9.71(10点満点)です。

ピボットテーブルで必須の技術「右クリック」について

この記事ではたくさんのピボットテーブルの操作方法を解説していますが、要所要所で「右クリック」を使う操作が頻出するため先に紹介します。

ほとんどの方が「マウス」で操作していると思いますが、ExcelCampでは「ショートカットで右クリックする」技術を推奨しています

右クリックをショートカットで入力する方法は2つあります。

ショートカット1:右クリックキー

キーボードに上記のようなマークのボタンがあるでしょうか。このボタンは「右クリックキー」といい、押すと右クリックメニューを表示することができます。

PCによって位置やデザインが違うので、存在するかどうか確かめてみてください。

ExcelCamp講師 野沢先生

大体キーボードの右下あたり、Altキーや矢印キーの近くにあるはずです。

ショートカット2:Shift&F10

もしPCに右クリックキーがなければ、ShiftキーとF10キーを同時に押すことでも右クリックメニューを開くことができます。

F10キーは「Fnキー」を同時に押さないと動作しないことがあります。

ExcelCamp講師 野沢先生

以下の説明では、基本的に右クリックキーを用いて説明しますが、

右クリックキーがないPCをお使いの方は、Shift&F10を使ってください。

ピボットテーブルの使い方、シートを作成(挿入)する方法

Alt+N+V+T+Enter

ピボットテーブルを作成(挿入)する方法は、分析した範囲を選択した状態で「挿入」→「ピボットテーブル」→「テーブルまたは範囲から」を選択します。

ショートカットで入力する場合、順番にAltキー、Nキー、Vキー、Tキー、Enterキーを押してください

上記のようにピボットテーブル用のシートが新たに作成されます。

シート名の変更方法

ピボットテーブル用のシートは通常のシートと特性が異なるため、シート名ですぐに判別できるようにしておくと便利です。

具体的には「Pvt」「ピボット分析」などわかりやすい名前に変更してください。

順番にAltキー、Hキー、Oキー、Rキーを押すことでシート名の変更ができます。

ExcelCamp講師 野沢先生

シート名変更について詳しくは以下の記事をご確認ください。

作成したシートを削除する方法

基本的にピボットテーブルのシートは使い終えた後も削除せずに残しておくことがおすすめですが、削除したいときにはAltキー、Hキー、Dキー、Sキーを順番に押します。

マウスでたどると「ホーム」→「削除」→「シートの削除」から削除できます
ExcelCamp講師 野沢先生

効率を求めたい方は、Alt+H+D+SよりもAlt+E+Lが1打少なくおすすめです

ピボットテーブルの使い方、項目を追加・削除する方法

ピボットテーブルの項目追加、削除は右側のメニューで操作します。

上側には、テーブルにある列のラベル名が並んでいます。

チェックをつけることで項目を追加することができます。

項目の追加先(フィールド)は下側に表示されていて、「フィルター」「列」「行」「値」の4種類があります。

フィルター:集計対象となるデータを絞り込む
列:列に表示させたい項目
行:行に表示させたい項目
値:集計結果として出したい数値

ExcelCamp講師 野沢先生

デフォルトの機能として、ラベルにチェックを付けることで文字データは「行フィールド」に、数値データは「値フィールド」に追加されます。

意図したフィールドに追加できなかった場合は、ドラッグアンドドロップで変更できます。

また、項目を削除したい場合は、フィールドに追加されているラベル名を外側にドラッグアンドドロップします。

ExcelCamp講師 野沢先生

ただExcelCampおすすめは「ノーマウス」、すべてをショートカットで操作することです

ショートカットで操作する方法

使うショートカット
メニュー内にアクティブを移動:F6キーを2回
チェックを付ける:Spaceキー
好きな場所に項目を追加する:右クリックキー
シートにアクティブを戻す:Escキー

まず右側のメニューにアクティブを移動させる必要があります。

ほとんどのPCでF6キーを2回押すことでアクティブを移動できます。

検索窓にカーソルが移ったら成功です

この状態でキー、キーで項目を移動できます。

今回は「契約の部門」を行フィールドに追加してみます。

Spaceキーでチェックを付ければ、文字データはデフォルトで「行フィールド」に追加されるのでこれで完了です。

他のフィールドに追加したい場合は、右クリックキーを押します

「フィルター」「行」「列」「値」それぞれ追加先が選択できるので、キー、キーで選んでEnterキーで確定させます

最後にシートにアクティブを戻すため、Escキーを一度押してください

矢印キーでセル上を移動できるようになっていれば完了です。

ExcelCamp講師 野沢先生

ピボットテーブルのショートカットは
F6キー2回でメニューにアクティブを移動
右クリックキーで項目追加
Escキーでシートにアクティブを戻す
が基本動作なのでセットで覚えるようにしてください。

また追加したい項目を削除する場合は、シート上で削除したい項目の列までセルを移動します

次に、右クリックキーを押します。

「日報入力者(ラベル名)の削除」と書かれた項目があり、Vキーを押すことで削除できます。

削除できました。

ExcelCamp講師 野沢先生

右クリックキー+Vの2打で操作可能なので、0.5秒以内に項目削除が可能です。

ExcelCampはExcel初心者・苦手な方のための研修です。
・たった1日でExcel操作が「激変」します。
・講義中に反復練習するから、その場でExcelが得意になります。
・延べ2,000名以上が参加し、満足度は9.71(10点満点)です。

ピボットテーブルの使い方、「値の集計方法」の変更方法

例えば都道府県ごとの売上を表示させる場合、合計集計よりも平均集計のほうが正確な分析ができるはずです

都道府県ごとに店舗数が違う可能性があるため、合計集計の値は分析価値が少ない

ピボットテーブルのデフォルト設定では、値フィールドに入れた数値は合計集計で表示されるため、「値の集計方法」を変更する必要があります。

まずシート上で値の集計方法を変更したい列に移動します。

右クリックキーを押して、「値の集計方法」という項目を選択するためMキーを押します

「合計」「データの個数」「平均」など様々な集計方法を選ぶことができます。今回は平均集計にしたいので、Aキーを押すことで切り替えることができます

ExcelCamp講師 野沢先生

マウスで値フィールドのラベルをクリック→「値フィールドの設定」でも変更可能ですが、上記で紹介した右クリックキー+M+Aのほうが数倍速く操作可能なのでおすすめです。

ピボットテーブルの表示を見やすくなる、3つのおすすめ設定

ピボットテーブルのデフォルト設定では、複数項目を入れて集計を表示すると以下のようなデザインになります。

行フィールドに「国」「都道府県」、値フィールドに「売上」を追加しています

このままだと見にくいデザインで分析効率も上がりづらいため、以下3つの設定をおすすめします。

おすすめ設定1:レポートレイアウトを「表形式」に

上のメニューから「デザイン」→「レポートのレイアウト」→「表形式で表示」を選択します。

ショートカットで操作するとAlt+J+Y+P+Tと順番に押します

ExcelCamp講師 野沢先生

この設定で「国」と「都道府県」をそれぞれ別の列で表示できるため、集計結果が見やすくなります。

おすすめ設定2:小計を消す

次に小計を消します。

上のメニューから「デザイン」→「小計」→「小計を表示しない」を選択します。

ショートカットで操作するとAlt+J+Y+T+Dと順番に押します

ExcelCamp講師 野沢先生

小計も削除したほうが見やすいことが多いです。

コラム:設定1、2をデフォルト設定にする方法

設定1、2は、ピボットテーブルを操作するときに必ずと言っていいほど行う操作なので、デフォルト設定にしてしまったほうが効率的です

まずAlt+T+Oと順番に押して、「Excelのオプション」ボックスを開きます。

左のメニューバーから「データ」を選び、「既存レイアウトの編集」を開きます。

それぞれ以下の設定を行います。

小計を「小計を表示しない」に変更。

レポートのレイアウトを「表形式で表示」に変更。

最後に「OK」を2回押して設定完了です。

ExcelCamp講師 野沢先生

これで、毎回ピボットテーブルを開くたびに設定1、2を行う時間を短縮することができます。

おすすめ設定3:数字を桁区切りにする

集計結果の数値は「,(カンマ)」で桁区切りさせたほうが見やすくておすすめです。

まず、桁区切りに変更したい範囲を選択します。

Alt+H+Kを順番押しまたはCtrl&Shift&1を同時押しすることで、数字を桁区切りに変更できます。

ExcelCamp講師 野沢先生

通貨表記(¥)に変更したい場合はCtrl&Shift&4
%表記はCtrl&Shift&5を入力してください。

※「%表記」はAlt+H+P(ホーム[H]→パーセント[P])のショートカットも覚えやすくておすすめです。

ピボットテーブルの元データを変更したときの更新方法

データの更新:右クリック+R
Refresh(更新)の頭文字

ピボットテーブルの元データの値を変更した場合、毎回「データの更新」をする必要があります。

ExcelCamp講師 野沢先生

元データを変更しても、ピボットテーブルの数値は自動で変わってくれません

毎回行う操作なので、0.5秒でできるショートカットをご紹介します。

わかりやすいように、元データの売上数値を大げさに変更してみます。

元データの8月19日、A商店のあさりの売上を「1,000,000」に変更しました。

この状態ではまだピボットテーブルにデータは反映されていません。

あさりの売上は「1,000,000」になっていない

データの更新は右クリックキーを押して、「更新」を選びます。

「更新(R)」と書いてあるので、Rキーを押すことで「更新」を最速で選択できます。

無事、8月19日のあさりの売上「1,000,000」が反映されました。

ExcelCamp講師 野沢先生

どこのセルにいても設定できるので0.5秒以内に操作可能です。

上のメニューから「ピボットテーブル分析」→「更新」でもできますが、右クリック+Rのほうが数倍速いためおすすめです。

ちなみにAlt&F5(&Fn)の同時押しでも更新ができます。

ピボットテーブルの元データに項目を追加したときの更新方法

元データに新しく項目を追加した場合、右クリック+Rをしても項目が追加されません

一番下の行に、赤色の項目を新たに追加しました。

ピボットテーブルシートで右クリック+Rを押しても、8月22日に「にんじん」の項目は表示されていません

「更新」をしても追加したはずの「にんじん」が出ない
ExcelCamp講師 野沢先生

これは、はじめに選択したピボットテーブルの選択範囲が変わっていないからです。

「データソースの変更」から選択範囲を広げる必要があります

データソースの変更は「ピボットテーブル分析」→「データソースの変更」から行います。

ショートカットで操作すると、Alt+J+T+I+Enterを順番に押します

選択範囲が、新しく追加した「にんじん」の行まで伸びていないことがわかります

この範囲はShiftキーを押しながらキーを一度押すことで拡げることができます。

にんじんの行まで選択範囲が含まれていることを確認してください

最後にEnterキーでOKを選択すると、ピボットテーブルに「にんじん」が表示されました。

ExcelCamp講師 野沢先生

2つの更新方法をまとめると、
・元データを編集した場合→右クリック+R
・元データを追加した場合→Alt+J+T+I
となります。

ExcelCampはExcel初心者・苦手な方のための研修です。
・たった1日でExcel操作が「激変」します。
・講義中に反復練習するから、その場でExcelが得意になります。
・延べ2,000名以上が参加し、満足度は9.71(10点満点)です。

ピボットテーブルを使う上での注意点

最後に、ピボットテーブルを扱う上で実際の業務でよく直面する注意点を3つ紹介します。

注意点1:最新の状態かどうか常に確認する

元データの編集、追加をした場合は必ず「データの更新」「データソースの変更」が必要です。気をつけていても忘れてしまうことがあるため、ピボットテーブルを扱う前は必ずデータが最新の状態になっているか確認すべきです。

注意点2:表示されなくなったときの対処法

ピボットテーブルの範囲内からセルが外に出ると、右側のメニュー(ピボットテーブルのフィールド)が表示されなくなります。

範囲内にセルがあれば、右側のメニューが表示される
セルが範囲外に出ると、右側のメニューが消える
ExcelCamp講師 野沢先生

アクティブセルの位置で表示/非表示が切り替わるだけなので、

表示が消えた場合も焦らずに、ピボットテーブルの範囲にセルを移動してくださいね

注意点3:クロス集計で「列」にいれる項目

ピボットテーブルでは、「行」と「列」に項目を入れることでクロス集計を行うこともできます

上の例では行フィールドに「小項目」、列フィールドに「仕入先」をいれてクロス集計をしています。

項目数が多いラベルを列に入れないこと」が注意点となります。

ExcelCamp講師 野沢先生

小項目の項目数は5個、仕入先の項目数は2個なので、項目数が少ない「仕入先」を列をいれています

もし行と列を逆にしてしまうと、以下のようにクロス集計表が見にくくなります。

ExcelCamp講師 中田先生

Excelはデータを縦方向(行が増える方向)に伸ばしたほうが見やすくなります。

クロス集計を行う場合にはご注意ください。

休日「1日」を投資して、平日の生産性を最大「20倍」にするExcel研修

私たちが提供するExcel研修「上位1%を目指す!ExcelCamp」では、これまで延2,000名以上の方に研修を受講いただき、受講者満足度は9.71(※)という大変高い評価を頂いております。

※10点満点中の評価の平均値。

休日1日を研修に使うのは「やや重たい」というご意見もいただきます。

しかし、本当にExcelスキルを実践で使えるレベルで高めるとなると、通常数年はかかるものです

実際にExcelCampで教える内容は、代表の中田が業務コンサルタントとして数年間、毎日10時間以上Excelと向き合った結果ようやく生まれたスキルです。

そのことを考えると、休日の「たった1日」を投資して、その後のExcel業務の生産性を圧倒的に高めることは決して悪くない選択だと思います。

参加者の中にはお伝えしたスキルを仕事の現場で活かし、生産性が「20倍」になったという事例もあります。

動画サービス「bizplay(ビズプレイ)」で研修の一部を無料公開中!

※全4回の動画のダイジェスト版です。
※第1~4回の内容をフルで視聴するには、bizplayへの無料会員登録が必要です。

現在セミナー動画見放題サービス「bizplay」で、ExcelCamp Day1の講義内容の一部を無料配信しています。

全4回に分けてExcelが得意になるための考え方や覚えておくべき基本操作を解説していますので、Day1への参加を迷われている方はぜひ一度ご覧ください。

著書「神速Excel」も絶賛発売中

目次
閉じる