いつもお疲れ様です！なのです！
そもそもピボットテーブルって聞いたことありますか？
実はExcelのピボットテーブルは、データ分析を行う上で欠かせない機能です。
使わないと損！
これを使うだけでかなりの時短効果があります！
ピボットテーブルを使いこなすことで、
様々な切り口でデータを切り分け高速で示唆を導くことが可能になります！
特に「右クリック」はピボットテーブルを使いこなす上で最重要となります。
この記事ではExcelのピボットテーブルという機能の全容を、
まだ使ったことがない方でもわかりやすく解説します。
「右クリック」を含め、ショートカットで操作する方法も紹介しておりますので
ぜひ参考にしてみてください。
目次
ピボットテーブルとは？何に使うの？
ピボットテーブルとは、Excelで集計したデータテーブルを分析するために使う機能です。
データ分析のプロは、主にピボットテーブルを以下2つの用途で使用します。
用途①：様々な切り口で集計分析を行い「示唆」を導くため
用途②：データの構造を「理解・把握」するため
それぞれ解説します。
用途①：様々な切り口で集計分析を行い、示唆を導くため
ピボットテーブルを使うと、
データの種類によりますが「店舗」「都道府県」「従業員数」「立地」など
様々な切り口で「売上」「利益」「コスト」など集計したいデータを瞬時に分析できます。
集計分析はSUMIFS関数やCOUNTIFS関数でもできますが、
ピボットテーブルを使うことで、
関数を書くことなく圧倒的に速いスピードで分析できるため重宝します。
用途②：データの構造を「理解・把握」するため
データ分析の現場では、
自分が作成していないExcelデータを分析する場面が多々あります。
そのためまず行うべきは、共有されたExcelデータの構造理解です。
データ構造の理解・把握にもピボットテーブルが重宝します。
例えば以下のようなシートを共有されたとします。
ピボットテーブルを開き、「行」の項目にそれぞれ項目をいれることで、
視覚的に分かりやすくどのようなデータが書かれているのか把握することができます。
確認のときには、
特にデータに「ゆらぎ」や「異常値」がないかも同時にチェックします。
データの「ゆらぎ」とは
「完了」「完了済」「完了ずみ」「済」など、
同じ意味の言葉が違う表現で書かれている状態。
データがゆらいでいるとExcel上では異なるデータだと認識されるため、
分析の前に修正する必要があります。
以下、実際に「データの理解」のためにピボットテーブルを活用してみます
データの「ゆらぎ」や「異常値」は、
1つ含まれるだけでも分析結果を狂わせるため、
データを確認・把握する段階で見つけておくことが大切です。
ピボットテーブルで必須の技術「右クリック」
この記事ではたくさんのピボットテーブルの操作方法を解説していますが、
要所要所で「右クリック」を使う操作が頻出するため先に紹介します。
ピボットテーブルを扱う際にはマウスを使うことを推奨します。
ただ、マウスを使用しない方もいると思いますので、
右クリックをショートカットで入力する方法は2つ紹介します！
ショートカット①：右クリックキー
キーボードに上記のようなマークのボタンがあるでしょうか。
このボタンは「右クリックキー」といい、
押すと右クリックメニューを表示することができます。
PCによって位置やデザインが違うので、存在するかどうか確かめてみてください。
ショートカット②：Shift＋F10
もしPCに右クリックキーがなければ、
ShiftキーとF10キーを同時に押すことでも右クリックメニューを開くことができます。
ピボットテーブルの使い方、シート作成方法
ピボットテーブルを作成(挿入)
Alt→N→V→T→Enter
ピボットテーブルを作成(挿入)する方法は、
分析した範囲を選択した状態で、
「挿入」→「ピボットテーブル」→「テーブルまたは範囲から」を選択します。
ショートカットで入力する場合、
順番にAltキー、Nキー、Vキー、Tキー、Enterキーを押してください。
上記のようにピボットテーブル用のシートが新たに作成されます。
シート名の変更方法
シート名の変更
Alt→H→O→R
ピボットテーブル用のシートは通常のシートと特性が異なるため、
シート名ですぐに判別できるようにしておくと便利です。
具体的には「Pvt」「ピボット分析」などわかりやすい名前に変更してください。
順番にAltキー、Hキー、Oキー、Rキーを押すことでシート名の変更ができます。
作成したシートを削除する方法
シートの削除
Alt→H→D→S
基本的にピボットテーブルのシートは、
使い終えた後も削除せずに残しておくことがおすすめですが、
削除したいときにはAltキー、Hキー、Dキー、Sキーを順番に押します。
ピボットテーブルの使い方、項目を追加・削除する方法
ピボットテーブルの項目追加、削除は右側のメニューで操作します。
上側には、テーブルにある列のラベル名が並んでいます。
チェックをつけることで項目を追加することができます。
項目の追加先(フィールド)は下側に表示されていて、
「フィルター」「列」「行」「値」の4種類があります。
フィルター：集計対象となるデータを絞り込む
列：列に表示させたい項目
行：行に表示させたい項目
値：集計結果として出したい数値
デフォルトの機能として、ラベルにチェックを付けることで
文字データは「行フィールド」に、
数値データは「値フィールド」に追加されます。
意図したフィールドに追加できなかった場合は、
ドラッグアンドドロップで変更できます。
また、項目を削除したい場合は、
フィールドに追加されているラベル名を外側にドラッグアンドドロップします。
ショートカットで操作する方法
使うショートカットキー
・メニュー内にアクティブを移動：F6キーを2回
・チェックを付ける：Spaceキー
・好きな場所に項目を追加する：右クリックキー
・シートにアクティブを戻す：Escキー
まず右側のメニューにアクティブを移動させる必要があります。
ほとんどのPCでF6キーを2回押すことでアクティブを移動できます。
この状態で↑キー、↓キーで項目を移動できます。
今回は「契約の部門」を行フィールドに追加してみます。
Spaceキーでチェックを付ければ、
文字データはデフォルトで「行フィールド」に追加されるのでこれで完了です。
他のフィールドに追加したい場合は、右クリックキーを押します。
「フィルター」「行」「列」「値」それぞれ追加先が選択できるので、
↑キー、↓キーで選んでEnterキーで確定させます。
最後にシートにアクティブを戻すため、Escキーを一度押してください。
矢印キーでセル上を移動できるようになっていれば完了です。
追加したい項目を削除する場合
シート上で削除したい項目の列までセルを移動します。
次に、右クリックキーを押します。
「日報入力者(ラベル名)の削除」と書かれた項目があり、
Vキーを押すことで削除できます。
削除できました。
ピボットテーブルの使い方、「値の集計方法」の変更方法
例えば都道府県ごとの売上を表示させる場合、
合計集計よりも平均集計のほうが正確な分析ができるはずです。
ピボットテーブルのデフォルト設定では、
値フィールドに入れた数値は合計集計で表示されるため、
「値の集計方法」を変更する必要があります。
まずシート上で値の集計方法を変更したい列に移動します。
右クリックキーを押して、
「値の集計方法」という項目を選択するためMキーを押します。
「合計」「データの個数」「平均」など様々な集計方法を選ぶことができます。
今回は平均集計にしたいので、
Aキーを押すことで切り替えることができます。
ピボットテーブルの表示の見やすい設定
ピボットテーブルのデフォルト設定では、
複数項目を入れて集計を表示すると以下のようなデザインになります。
このままだと見にくいデザインで分析効率も上がりづらいため、
以下3つの設定をおすすめします。
おすすめ設定①：レポートレイアウトを「表形式」に
上のメニューから「デザイン」→「レポートのレイアウト」→「表形式で表示」を選択します。
ショートカットで操作するとAlt→J→Y→P→Tと順番に押します。
この設定で「国」と「都道府県」をそれぞれ別の列で表示できるため、
集計結果が見やすくなります。
おすすめ設定②：小計を消す
次に小計を消します。
上のメニューから「デザイン」→「小計」→「小計を表示しない」を選択します。
ショートカットで操作するとAlt→J→Y→T→Dと順番に押します。
小計も削除したほうが見やすいことが多いです。
コラム：設定①、②をデフォルト設定にする方法
設定1、2は、ピボットテーブルを操作するときに
必ずと言っていいほど行う操作なので、
デフォルト設定にしてしまったほうが効率的です。
まずAlt→T→Oと順番に押して、「Excelのオプション」ボックスを開きます。
左のメニューバーから「データ」を選び、「既存レイアウトの編集」を開きます。
それぞれ以下の設定を行います。
小計を「小計を表示しない」に変更。
レポートのレイアウトを「表形式で表示」に変更。
最後に「OK」を2回押して設定完了です。
おすすめ設定③：数字を桁区切りにする
集計結果の数値は「,(カンマ)」で桁区切りさせたほうが見やすくておすすめです。
まず、桁区切りに変更したい範囲を選択します。
Alt→H→Kを順番押しまたはCtrl＋Shift＋1を同時押しすることで、
数字を桁区切りに変更できます。
ピボットテーブルの元データを変更したときの更新方法
データの更新：右クリック+R
※Refresh(更新)の頭文字
ピボットテーブルの元データの値を変更した場合、
毎回「データの更新」をする必要があります。
元データを変更しても、
ピボットテーブルの数値は自動で変わってくれません。
毎回行う操作なので、0.5秒でできるショートカットをご紹介します。
わかりやすいように、元データの売上数値を大げさに変更してみます。
この状態ではまだピボットテーブルにデータは反映されていません。
データの更新は右クリックキーを押して、「更新」を選びます。
「更新(R)」と書いてあるので、
Rキーを押すことで「更新」を最速で選択できます。
無事、8月19日のあさりの売上「1,000,000」が反映されました。
どこのセルにいても設定できるので0.5秒以内に操作可能です！
上のメニューから「ピボットテーブル分析」→「更新」でもできますが、
右クリック+Rのほうが数倍速いためおすすめです。
ちなみにAlt＋F5＋(Fn)の同時押しでも更新ができます。
ピボットテーブルの元データに項目を追加したときの更新方法
元データに新しく項目を追加した場合、
右クリック+Rをしても項目が追加されません。
一番下の行に、赤色の項目を新たに追加しました。
ピボットテーブルシートで右クリック+Rを押しても、
8月22日に「にんじん」の項目は表示されていません。
これは、はじめに選択したピボットテーブルの選択範囲が変わっていないからです。
「データソースの変更」から選択範囲を広げる必要があります。
データソースの変更は、
「ピボットテーブル分析」→「データソースの変更」から行います。
ショートカットで操作すると、
Alt→J→T→I→Enterを順番に押します。
選択範囲が、
新しく追加した「にんじん」の行まで伸びていないことがわかります。
この範囲は、
Shiftキーを押しながら↓キーを一度押すことで拡げることができます。
最後にEnterキーでOKを選択すると、
ピボットテーブルに「にんじん」が表示されました。
ピボットテーブルを使う上での注意点
最後に、ピボットテーブルを扱う上で実際の業務で、
よく直面する注意点を3つ紹介します。
注意点①：最新の状態かどうか常に確認する
元データの編集、追加をした場合は必ず、
「データの更新」「データソースの変更」が必要です。
気をつけていても忘れてしまうことがあるため、
ピボットテーブルを扱う前は必ずデータが最新の状態になっているか確認すべきです。
注意点②：表示されなくなったときの対処法
ピボットテーブルの範囲内からセルが外に出ると、
右側のメニュー(ピボットテーブルのフィールド)が表示されなくなります。
アクティブセルの位置で表示/非表示が切り替わるだけなので、
表示が消えた場合も焦らずに、ピボットテーブルの範囲にセルを移動してくださいね。
注意点③：クロス集計で「列」にいれる項目
ピボットテーブルでは、
「行」と「列」に項目を入れることでクロス集計を行うこともできます。
上の例では行フィールドに「小項目」、
列フィールドに「仕入先」をいれてクロス集計をしています。
「項目数が多いラベルを列に入れないこと」が注意点となります。
小項目の項目数は5個、仕入先の項目数は2個なので、
項目数が少ない「仕入先」を列をいれています。
もし行と列を逆にしてしまうと、
以下のようにクロス集計表が見にくくなります。
Excelはデータを縦方向(行が増える方向)に伸ばしたほうが見やすくなります。
クロス集計を行う場合にはご注意ください。
最後に
いかがでしたか？
ピボットテーブルは業務の場においてもかなり重宝されるので、
習得しておくことをおすすめします。
また、元データを更新した際にも、
ピボットテーブルだと好きなデータを楽に変更可能なので、
元データを更新して表を作り直すといった無駄な手間も省けます！
ピボットテーブルを作りやすくするための表作成の方法は、
以下記事で紹介しているので、よかったら読んでみてください！
↓以下参考記事
↓ショートカット一覧表GET
コメント