Excelのピボットテーブルは、データ分析を行う上で欠かせない機能です。
ピボットテーブルを使いこなすことで、様々な切り口でデータを切り分け高速で示唆を導くことが可能になります。
特に「右クリック」はピボットテーブルを使いこなす上で最重要となり、ExcelCampでは「右クリックを制す者、ピボットテーブルを制す」とお伝えしています。
この記事ではExcelのピボットテーブルという機能の全容を、まだ使ったことがない方でもわかりやすく解説します。「右クリック」を含め、ショートカットで操作する方法も紹介しておりますのでぜひ参考にしてください。
この記事ではExcelCamp講師が研修で実際に説明している手順に沿って、解説いたします。
ショートカットキーの表記について
ExcelCampではショートカットの表記を以下に統一しています。
・「＆」は同時に押すショートカットです。たとえばCtrl&Cなら、Ctrlキーを押しながら同時にCキーも押すことで発動します。
・「＋」は順番に押すショートカットです。たとえばAlt+E+Lなら、Altキーを押した後に一度指を離し、E、Lキーをそれぞれ順番に押すことで発動します。
ピボットテーブルとは？何に使う？
ピボットテーブルとは、Excelで集計したデータテーブルを分析するために使う機能です。データ分析のプロは、主にピボットテーブルを以下2つの用途で使用します。
用途1：様々な切り口で集計分析を行い「示唆」を導くため
用途2：データの構造を「理解・把握」するため
それぞれ解説します。
用途1：様々な切り口で集計分析を行い、示唆を導くため
ピボットテーブルを使うと、データの種類によりますが「店舗」「都道府県」「従業員数」「立地」など様々な切り口で「売上」「利益」「コスト」など集計したいデータを瞬時に分析できます。
集計分析はSUMIFS関数やCOUNTIFS関数でもできますが、ピボットテーブルを使うことで関数を書くことなく圧倒的に速いスピードで分析できるため重宝します。
用途2：データの構造を「理解・把握」するため
データ分析の現場では、自分が作成していないExcelデータを分析する場面が多々あります。そのためまず行うべきは、共有されたExcelデータの構造理解です。
データ構造の理解・把握にもピボットテーブルが重宝します。
例えば以下のようなシートを共有されたとします。
ピボットテーブルを開き、「行」の項目にそれぞれ項目をいれることで、視覚的に分かりやすくどのようなデータが書かれているのか把握することができます。
確認のときには、特にデータに「ゆらぎ」や「異常値」がないかも同時にチェックします。
ExcelCamp講師 野沢先生以下、実際に「データの理解」のためにピボットテーブルを活用してみます
ExcelCamp講師 野沢先生データの「ゆらぎ」や「異常値」は、1つ含まれるだけでも分析結果を狂わせるため、データを確認・把握する段階で見つけておくことが大切です。
「ExcelCamp」はExcel初心者・苦手な方のための研修です。
・たった1日でExcel操作が「激変」します。
・講義中に反復練習するから、その場でExcelが得意になります。
・延べ10,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秒以内に項目削除が可能です。
ピボットテーブルの使い方、「値の集計方法」の変更方法
例えば都道府県ごとの売上を表示させる場合、合計集計よりも平均集計のほうが正確な分析ができるはずです。
ピボットテーブルのデフォルト設定では、値フィールドに入れた数値は合計集計で表示されるため、「値の集計方法」を変更する必要があります。
まずシート上で値の集計方法を変更したい列に移動します。
右クリックキーを押して、「値の集計方法」という項目を選択するため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秒でできるショートカットをご紹介します。
わかりやすいように、元データの売上数値を大げさに変更してみます。
この状態ではまだピボットテーブルにデータは反映されていません。
データの更新は右クリックキーを押して、「更新」を選びます。
「更新(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
となります。
ピボットテーブルを使う上での注意点
最後に、ピボットテーブルを扱う上で実際の業務でよく直面する注意点を3つ紹介します。
注意点1：最新の状態かどうか常に確認する
元データの編集、追加をした場合は必ず「データの更新」「データソースの変更」が必要です。気をつけていても忘れてしまうことがあるため、ピボットテーブルを扱う前は必ずデータが最新の状態になっているか確認すべきです。
注意点2：表示されなくなったときの対処法
ピボットテーブルの範囲内からセルが外に出ると、右側のメニュー(ピボットテーブルのフィールド)が表示されなくなります。
ExcelCamp講師 野沢先生アクティブセルの位置で表示/非表示が切り替わるだけなので、
表示が消えた場合も焦らずに、ピボットテーブルの範囲にセルを移動してくださいね。
注意点3：クロス集計で「列」にいれる項目
ピボットテーブルでは、「行」と「列」に項目を入れることでクロス集計を行うこともできます。
上の例では行フィールドに「小項目」、列フィールドに「仕入先」をいれてクロス集計をしています。
「項目数が多いラベルを列に入れないこと」が注意点となります。
ExcelCamp講師 野沢先生小項目の項目数は5個、仕入先の項目数は2個なので、項目数が少ない「仕入先」を列をいれています。
もし行と列を逆にしてしまうと、以下のようにクロス集計表が見にくくなります。
ExcelCamp講師 中田先生Excelはデータを縦方向(行が増える方向)に伸ばしたほうが見やすくなります。
クロス集計を行う場合にはご注意ください。
休日「1日」を投資して、平日の生産性を最大「20倍」にするExcel研修
私たちが提供するExcel研修「上位1%を目指す！ExcelCamp」では、これまで延10,000名以上の方に研修を受講いただき、受講者満足度は9.71(※)という大変高い評価を頂いております。
※10点満点中の評価の平均値。
休日1日を研修に使うのは「やや重たい」というご意見もいただきます。
しかし、本当にExcelスキルを実践で使えるレベルで高めるとなると、通常数年はかかるものです。
実際にExcelCampで教える内容は、代表の中田が業務コンサルタントとして数年間、毎日10時間以上Excelと向き合った結果ようやく生まれたスキルです。
そのことを考えると、休日の「たった1日」を投資して、その後のExcel業務の生産性を圧倒的に高めることは決して悪くない選択だと思います。
参加者の中にはお伝えしたスキルを仕事の現場で活かし、生産性が「20倍」になったという事例もあります。
動画サービス「bizplay(ビズプレイ)」で研修の一部を無料公開中！
※全4回の動画のダイジェスト版です。
※第1~4回の内容をフルで視聴するには、bizplayへの無料会員登録が必要です。
現在セミナー動画見放題サービス「bizplay」で、ExcelCamp Day1の講義内容の一部を無料配信しています。
全4回に分けてExcelが得意になるための考え方や覚えておくべき基本操作を解説していますので、Day1への参加を迷われている方はぜひ一度ご覧ください。
著書「神速Excel」も絶賛発売中
