- ピボットテーブルとは?
- 【補足】ピボットテーブルと関係の深いクロス集計とは
- ピボットテーブルの活用例(ピボットテーブルでできること)
- ピボットテーブルの作り方・使い方【基本編】
- 作成したピボットテーブルを削除する方法
- ピボットテーブルのメリット
- ピボットテーブルの使い方【実践編】
- ピボットテーブルの使い方で気をつけること
- 困ったときはピボットテーブルの小技で解決!
- ピボットテーブルの使い方は簡単!どんどん活用して業務効率化を図ろう
ピボットテーブルとは?
ピボットテーブルとは、Microsoft Excel(以下、Excel)で関数や数式を使わず、初心者でも簡単に膨大なデータを集計したり、分析したりできる機能のことです。ここから、ピボットテーブルの活用例や、作成するために必要な条件などについてわかりやすく解説します。
ピボットテーブルを作るために必要な3つの条件
ピボットテーブルを作るには、元のデータに対して以下3つの条件を整える必要があります。
- 1行目にタイトル行がある
- 2行目以降のデータが連続している
(途中に空白行がない) - データに関係のない値・文字列が入力されている余計なセルがない
(データと隣接していなければ問題なし)
ピボットテーブルで使われる用語
ピボットテーブルの作成時に使われる主な用語は、以下の通りです。
- フィールド:各集計に使われる項目(例:商品、年、金額など)
- フィルターボックス:レポートフィルター(分析の対象を一部に限定する機能)に表示する項目を入れるための場所
- 列ボックス:横軸(列ラベル)に表示する項目を入れるための場所
- 行ボックス:縦軸(行ラベル)に表示する項目を入れるための場所
- 値ボックス:数値として表示する項目を入れるための場所
- 作業ウィンドウ:表示させるデータを選ぶための場所
【補足】ピボットテーブルと関係の深いクロス集計とは
クロス集計とは、2種類以上のデータ・変数を掛け合わせて集計し、分析するための統計手法を指します。クロス集計表は、以下のようにクロス集計することで出来上がる表のことです。
クロス集計を用いれば、「年齢・年代別の回答状況」「商品ごとの月別売上高」などを可視化できます。保有するデータをクロス集計することで、今まで見えていなかった傾向が見えてくることもあるでしょう。
しかし、手作業でクロス集計するには一定の手間がかかります。そこで、誰でも手軽にクロス集計できる方法のひとつが、Excelピボットテーブル機能の活用です。
ピボットテーブルの活用例(ピボットテーブルでできること)
以下に、ピボットテーブルの機能をビジネスに活用した例をまとめました。
【ピボットテーブルの活用例】
・売上管理・分析
具体例:担当者・エリア・商品ごとに売上を集計する
・アンケートの集計
具体例:顧客から集めたアンケートの回答内容をまとめる
・ランキングの作成
具体例:売れている商品などを順位づけする
・データのグラフ化
具体例:集計した内容を売上順などでグラフにする
ピボットテーブルの作り方・使い方【基本編】
ここから、ピボットテーブルの基本的な作り方・使い方を紹介します。
集計・分析するためのデータを準備する
ピボットテーブルを作成する前に、売上実績や支払い履歴など、集計・分析したいデータを準備します。分析に必要な表を用意する際は、ピボットテーブルの3条件をすべて満たしていることを確認しておきましょう。条件を満たしていない場合は、事前に表を修正しておいてください。
ピボットテーブルを挿入する
条件を満たすデータの準備が整ったら、ピボットテーブルを挿入していきます。やり方は、カーソルをデータの上に合わせて(データ上ならどこでもいい)、「挿入」タブ→「ピボットテーブル」を選択するだけです。
選択後、以下のようなダイアログボックスが表示されます。
表示されているデータの範囲が正しければ、OKボタンをクリックしましょう。元データの隣にピボットテーブルのシートが新たに出現します。
集計するフィールドを選択する
ピボットテーブルを使用する際は、新たに出現したシートの右側にある作業フィールド(「ピボットテーブルのフィールド」)で、ピボットフィールドに盛り込みたいフィールド(項目)をいずれかのボックスにドラッグ・アンド・ドロップします。
例えば、商品ごとの売上が見たければ、行ボックスに「商品名」、値ボックスに「金額」をドラッグします。すると、以下のように商品の合計金額が表示されます。
一方、表示させたくない不要なフィールドは、各ボックスに入っている部分をドラッグ・アンド・ドロップで元に戻すことで削除できます。
【無料診断】あなたの仕事力はどれぐらい?リモート・在宅ワークに役立つ仕事力をチェック
作成したピボットテーブルを削除する方法
誤ってピボットテーブルを作成した場合や、ピボットテーブルが不要になった場合は、以下の方法で削除できます。
1. 任意のセルで、「ピボットテーブル分析」をクリックし、「アクション」「選択」「ピボットテーブル全体」を選択する
2. ピボットテーブルの範囲が指定されたら、Deleteキーを押して削除する
これだけの作業で、ピボットテーブルを簡単に消去できます。
ピボットテーブルのメリット
ここで、ビジネスでピボットテーブルを使うか決めかねている方向けに、メリットを4つ紹介します。すぐにピボットテーブルを使いこなしたいという方は、ピボットテーブルの使い方【実践編】を先にご確認ください。
メリット①:データ集計が楽
データ集計が楽なことが、ピボットテーブルのメリットです。仮に、会社の本部で以下のような販売記録を持っていたとしましょう。
データ量は豊富ですが、この表を見ただけで経営戦略やマーケティングなどに活かすことはできません。そこで、ピボットテーブルを活用してデータを集計すれば、手間をかけずに各担当者がどれくらい販売実績を上げたかひと目でわかります。
メリット②:データの更新が簡単
データの更新が簡単な点も、ピボットテーブルのメリットです。先ほどの販売記録に新たなデータを追加した際、「更新」ボタンを押すなどの簡単な作業のみで、ピボットテーブルに反映できます。
メリット③:直感的に操作ができる
直感的に操作ができる点も、Excelでピボットテーブルを活用するメリットです。グラフ作成が苦手な方でも、簡単な操作でデータを集計してグラフを作成できます。
メリット④:関数や数式の知識が不要
Excelを使うのにもかかわらず、関数や数式の知識が不要な点もピボットテーブルのメリットです。
例えば、先ほどから使用している販売記録において、「伊藤」さんの販売額は204,836円です。通常、この結果を導き出すためには、SUMIF関数を使用しなければなりません。
しかし、ある程度Excelに関する知識がある方でなければ、すぐに「SUMIF」を思いつかないでしょう。そこで、ピボットテーブルを使えば、関数を使わなくても「伊藤」さんを含む各担当者の販売額を一度に導き出せます。
ピボットテーブルの使い方【実践編】
ここから、以下のデータを使ってより具体的な活用例・使い方を見ていきましょう。
月別の売上をまとめる場合(商品ごと)
例えば、商品ごとの月別の売り上げを見たいときには、以下のように操作して売上を時系列で表示します。
今回の例は月ですが年間・半期・四半期になってもやり方は変わりません。このような時系列のピボットテーブルは、いろいろな場面で使われます。
【ドラッグする項目】
□ 列ラベル:月
□ 行ラベル:商品名
□ 値エリア:合計/金額
これに少し手を加えて、行ボックスに「年」を追加すると以下のように年別・月別の売上も見られます。前年との比較をするときには、この形が便利です。
【ドラッグする項目】
□ 列ラベル:月
□ 行ラベル:商品名、年
□ 値エリア:合計/金額
項目の順番によっても表示のされ方が変わってくることもポイントです。集計・分析したい内容に応じて、項目を組み換えましょう。
データを並べ替える場合(売上順)
データを集計・分析しやすくするため、並べ替えもやってみましょう。以下のデータの行ラベルを並べ替えたい場合は、「▼」マークのボタンをクリックし、「その他の並べ替えオプション」を選択します。
「売り上げの低い商品を上から順」に並べるのであれば「昇順」を選択、「売り上げの高い商品を上から順」に並べるのであれば「降順」を選択します。売り上げの場合は降順のほうが見やすいので、ここでは「降順」を選びます。さらに、売り上げの合計金額を基準にしたいので、内容も「合計/金額」に変えましょう。これで合計売上順に商品が並びます。
データの傾向分析をする場合(特徴・売れ筋)
お店ごとに「どの商品が売れているのか」「どういった特徴があるのか」といったこともピボットテーブルなら簡単に分析ができます。営業担当者別・支店別・部署別など、属性が変わっても同じように集計可能です。
【ドラッグする項目】
□ 列ラベル:カテゴリー、商品名
□ 行ラベル:店舗
□ 値エリア:合計/金額
グラフを入れる場合(ピボットグラフの活用)
「ピボットグラフ」で視覚的に見やすくする方法もあります。呼び出し方は「ピボットテーブルツール」→「分析」→「ピボットグラフ」です。
「ピボットグラフ」は、ピボットテーブルの内容を自動的にグラフ化してくれるツールです。ただ数字を羅列するよりも、内容をより直観的に把握できます。
個別の情報を見る場合(レポートフィルターの活用)
レポートフィルターは、抜き出したい情報だけを見たいときに使います。例えば、「ハンバーグの売上がよくないので詳しくデータを見たい」場合は、フィルターボックスに「商品名」をドラッグし、ピボットテーブル内で「ハンバーグ」を選択しましょう。
他のシートに移す場合(ピボットテーブルの移動)
作成したピボットテーブルだけを表示したい場合は、他のシートに移しましょう。ピボットテーブルの移動は、以下のやり方で簡単にできます。
1. 「ピボットテーブル分析」をクリックして、「アクション」「ピボットテーブルの移動」を選択する
2. 「ピボットテーブルの移動」ウィンドウが表示されたら、「新規ワークシート」か「既存のワークシート」を選択する
(新たに作成したシートに移動するなら「新規のワークシート」、既存のシートを使うなら「既存のワークシート」)
*今回は、「新規ワークシート」を選択
3. 新たなシートにピボットテーブルが移動する
なお、元々のシートからはピボットテーブルがなくなり、グラフだけが残ります。
【無料診断】そのモヤモヤの原因は?キャリアのヒントが見つかる「モヤモヤ解消診断」
ピボットテーブルの使い方で気をつけること
ピボットテーブルを使うにあたって、いくつか気をつけなければならないことがあります。ここから、注意点を確認していきましょう。
新しいデータを追加したら必ず「更新」する
ピボットテーブル作成に使用した元データに新たな内容を追加した場合、必ず「更新」しましょう。なぜなら、元データに新たな情報を追加したり修正したりしても、そのままでは作成済みのピボットテーブルに反映されないからです。
元データの最新情報を作成済みのピボットテーブルに反映させるためには、「ピボットテーブル分析」のタブを選択してから、「更新」ボタンをクリックします。
なお、カーソルをピボットテーブル上に置かないと、「ピボットテーブル分析」タブは表示されません。
わかりやすさ・見やすさを重視する
ピボットテーブルを作成する際は、見やすさ・わかりやすさを重視しましょう。
ピボットテーブルは、膨大なデータをひと目で確認できる点が魅力です。しかし、種類が多い項目(例:販売日や50社以上ある販売先など)を行ラベルにしてしまうと、縦長に表示されて一度に情報を把握することが困難になります。また、色々な情報を伝えたいと思っていくつもの項目をラベルに加えても、結果的に見にくい表になることがあります。
日付の自動グループ化に注意する
元のデータベースに日付データが含まれている場合、ピボットテーブルでは「年」「四半期」「月」などグループ化されて表示されるようになります。もし四半期や年が不要であれば、通常どおり項目をドラッグで外すことにより、見たい項目だけを表示できます。
日付の自動グループ化は便利なのですが、1日単位でデータを見たいという時には不便です。日付ごとのデータを表示したい場合は、「グループ解除」しましょう。行ラベルのどこかにカーソルを合わせて右クリック→「グループ解除」を選択すると、行が日付に変わります。
また、以下のように「グループ解除」ではなく「グループ化」を選択して、自分で表示したい項目を選ぶこともできます。
空白セルの表示を工夫する
読み手に配慮し、空白セルの表示を工夫しましょう。
以下は、月別の各商品売上高をピボットテーブルで示した例です。
1月の商品B・商品D・商品Eのセルを始め、さまざまな場所に空白のセルがあることがわかるでしょう。実際はその月に販売がなかった場合でも、事情を知らない上司や取引先がこの資料を見た際に、「記入漏れのある不完全な資料では?」と考える可能性があります。
そこで、空白セルに「―」や「0」などを表示して、相手に記入漏れではないことを伝えることがポイントです。流れを以下にまとめました。
1. 「ピボットテーブル分析」をクリックし、「ピボットテーブル」「オプション」を選択する
2. 「ピボットテーブルオプション」が表示されたら、「空白セルに表示する値」にチェックを入れて値を入力し(今回は「―」)、「OK」をクリックする
ここまでの処理で、以下のように空白セルに「―」が表示されるようになりました。
困ったときはピボットテーブルの小技で解決!
最後に、困ったときに役立つピボットテーブルのテクニックを紹介します。
自動調整機能を無効にする(自動調整が邪魔なとき)
ピボットテーブルには「データを組み替えるごとに列と幅の自動調整する機能」が備わっています。自動調整が邪魔になるときは、この機能を無効化しましょう。
まず、ピボットテーブル上で右クリック(もしくは、「ピボットテーブル分析」「ピボットテーブル」「オプション」を順にクリック)をして、「ピボットテーブルオプション」を選択します。ダイアログボックスが現れるので、「レイアウトと書式」タブ内にある「更新時に列幅を自動調整する」のチェックを外します。
これで、いちいち列の幅を元に戻さなくて済みます。ぜひ試してみてください。
表示桁数を整える(数字が大きくて表が見にくいとき)
「データの数字が大きくて、表が見づらい」という場合には、表示させる桁を減らすこともできます(百万円単位、千円単位など)。ピボットテーブル上で右クリックをして、「値フィールドの設定」を選びます。
そして、表示されたダイアログボックス下の「表示形式」を選択し、分類内にある「ユーザー定義」を使います。右側の種類のボックスに「#,##0,」と入力すれば完了です。
なお、読み手の誤解を招かないように、表示桁数を減らす際は余白に(千円)などと表記しておいた方がよいでしょう。
「おすすめピボットテーブル」機能を使う(イメージがわかないとき)
どの項目をどのエリアにドラッグすればいいのかわからなければ、「おすすめピボットテーブル」を活用しましょう。元データの内容をExcelが自動的に判断し、おすすめの集計方法を提案してくれます。
使い方は「ピボットテーブルツール」→「分析」→「おすすめピボットテーブル」です。「おすすめピボットテーブル」で一度ピボットテーブルを作成してから自分なりに項目を組み替えていけば、慣れていない方でも簡単にピボットテーブルを扱えます。
作業ウィンドウを再表示する(消えてしまったとき)
ピボットテーブルを作成している際、何らかの拍子に作業フィールドが消えてしまうことがあるでしょう。
作業ウィンドウは簡単に再表示できるので、こんな時も慌てる必要はありません。まず右上の「表示」を選択してから、「フィールド リスト」をクリックしてください。「表示」のアイコンが見当たらない場合は、上部の「ピボットテーブル分析」*をクリックしましょう。
*「ピボットテーブル分析」は、作成したピボットテーブル上にカーソルを置いておけば表示されているはずです。
「フィールド リスト」をクリックして、以下のように再度作業ウィンドウが現れました。
複数のフィールドを使う(細かく分析したいとき)
スライサー機能で複数のフィールドを使えば、データをさらに細かく分析できます。
以下は、エリア別の販売実績を表示した表です(「担当エリア」を行ラベルに指定してピボットテーブル、グラフを作成)。この架空の会社は「関西」「東北」で、特に販売実績をあげていることがわかります。
では、販売商品によって、エリアごとの実績に差は出るのでしょうか。ここで、スライサー機能を使って、「商品」のフィールドを挿入します。
上部の「スライサーの挿入」をクリックしてください(見当たらない場合は、まず「ピボットテーブル分析」を選択)。
続いて、「スライサーの挿入」ウィンドウが表示されたら、分析したい項目を選択して(今回は「商品」)「OK」をクリックします。
新たに「商品」ウィンドウが表示されたら、気になる項目を選択します。今回は、「商品C」のエリア別販売実績を見てみましょう。
以下のとおり、商品Cのエリア別販売実績が表示されました。ピボットテーブルのスライサー機能を活用したことで、商品Cが「中部」エリアにおいて最も販売実績をあげていることがわかります。
この架空の会社は、全エリアの中で「中部」エリアの販売実績が3位なので、商品Cが他エリアよりも「中部」エリアで支持されていることには何か特殊な要因・事情があるのかもしれません(中部エリアで商品Cの大型案件が成約、地元メディアで商品Cが取り上げられたなど)。
集計フィールドを挿入する(計算式を追加したいとき)
消費税が考慮されていないなどの理由で、後からピボットテーブルに計算式を追加したい場合は、「集計フィールド」を挿入しましょう。
まず、「ピボットテーブル分析」を選択し、「フィールド/アイテム/セット」→「集計フィールド」の順にクリックします。
続いて、「名前」(ピボットテーブルに追加するフィールド名)や「数式」を入力して「OK」をクリックします。数式を入力する際は、使うフィールドを選択して「フィールドの挿入」をクリックすると入力が便利です(今回は「合計」の部分)。
以下のように、消費税込みのフィールドを追加したピボットテーブルに変わりました。
ピボットテーブルの使い方は簡単!どんどん活用して業務効率化を図ろう
ピボットテーブルは、データ分析・集計時にとても強力な味方になるツールです。一度使い方を覚えておけば、いつでも膨大なデータを集計したり、グラフにしたりして見やすくできます。
使い方は難しくありません。さっそく手元にあるデータを使って、自分なりのピボットテーブルを作成してみてはいかがでしょうか。
【関連記事】
Excel(エクセル)掛け算の方法は3つ!「*」以外の便利な関数も紹介
【Excel】IF関数を総まとめ!基本の使い方から条件分岐まで紹介
Excelで棒グラフを簡単に作成する方法は?積み上げ棒グラフの作り方も解説
あなたの本当の年収がわかる!?
わずか3分であなたの適正年収を診断します
![いい転職が未来を変える。DODA](https://cdn-ak.f.st-hatena.com/images/fotolife/d/doda-media/20190930/20190930152904.png)