Excelのピボットテーブルの作り方 - 操作手順を徹底解説

ピボットテーブルは、Microsoft Excelの中でも特に優秀な機能の1つでありながら、多くの人から敬遠されています。

ピボットテーブルの便利な点は、規模の大きいデータセットを整理して、重要な情報を読み取るのに役立つことです。

一方、ピボットテーブルが敬遠されているのは、Excelの使い方をマスターしている人が少ないうえ、「ピボットテーブルは複雑で難しい」というイメージがあるからです。

しかし、Excelのピボットテーブルは、皆さんが思い込んでいるよりもずっと簡単に作り方をマスターすることができます。

この記事では、ピボットテーブルを作る手順を1つずつ解説していきますが、その前にまずはピボットテーブルがどんな機能なのか、そしてピボットテーブルを使うことでどんなメリットが得られるのかについて振り返っておきましょう。

別の言い方をすると、ピボットテーブルを活用すれば、画面上に無限に広がっているように見える数値の羅列から何らかの意味のある情報を引き出すこと、具体的には、データをさまざまな基準で分類して有益な判断材料を引き出すことが容易になります。

ピボットテーブルの「ピボット（回転軸）」という名前は、表示するデータを切り替えて、別の視点からデータを捉えられるようにするところに由来しています。

なお、表示するデータを切り替える際に、データの追加や削除などの変更は行われません。データから有益な情報を引き出せるように、データを整理しなおすだけです。

ピボットテーブルには実際にどんなメリットがあるのかについて、まだイメージが掴めないとしても、具体的な場面を想像すれば、すぐに理解できることもあります。

そこで、ここからはピボットテーブルが役に立つ具体的な状況を5つ見ていきます。
 

1. 複数の製品の売上高を比較する

たとえば、ワークシートに製品A、B、Cという3つの製品の月別売上高のデータが含まれており、この3つの製品の中で売上が最も多いのはどれかを確認したいと仮定します。

もちろん、ワークシートに目を通しながら製品Aの売上高を手作業で1つずつ足していき、さらに製品Bと製品Cでも同様の手順を繰り返して、各製品の売上の合計を計算すること自体は可能です。しかし、これではあまりにも手間がかかりすぎます。

仮にそのワークシートに何万行ものデータが含まれているとしたら、そのすべてを手作業で分類するには、いくら時間があっても足りません。

そこで、ピボットテーブルを使用すれば、製品A、B、Cの売上を自動で集計し、それぞれの売上の合計を求めることができます。しかも、計算には1分もかかりません。
 

2. 売上の合計に対する製品別の割合を表示する

ピボットテーブルを作成すると、通常は行や列ごとの数値の合計が表示されます。しかし、自動で表示できるのはそれだけではありません。

たとえば、ワークシートに3つの異なる製品の売上高を四半期別に入力し、そのデータを基にピボットテーブルを作成したとします。

このピボットテーブルの各列の末尾には、3つの製品それぞれについて、四半期別の売上高の合計が自動的に表示されます。

では、単に売上を合計した数字ではなく、会社全体の売上に対する製品別の割合を知りたい場合はどうすればよいのでしょうか？

ピボットテーブルでは、単に列の値を合計する代わりに、すべてのデータの合計に対する列ごとの割合を求めるように設定することができます。

たとえば、3つの製品の売上の合計が200万円で、製品Aの売上高が45万円の場合、ピボットテーブルの設定を変更することで、製品Aの売上高の割合が22.5％であると求めることができます。

ピボットテーブルで売上全体に対する製品別の割合を表示するには、売上高の総計のセルを右クリックして、［計算の種類］から［総計に対する比率］を選択します。
 

3. 重複しているデータを統合する

たとえば、ブログのリニューアルに伴い、大量のURLの更新が必要になったと仮定します。

運の悪いことに、ブログ用のレポート作成ソフトではURLの更新に対応できず、ブログ記事の閲覧数がそれぞれ2つの異なるURLに分かれて集計されてしまいました。

その結果、スプレッドシートには、ブログ記事ごとにデータが2つずつあり、正確な閲覧数を得るには、2つに分かれてしまった記事それぞれのデータを集計する必要があります。

そこで役に立つのがピボットテーブルです。記事の重複を検索して、閲覧数を手作業で集計していく必要はありません。

ピボットテーブルを使用して、ブログ記事のタイトルごとにデータを集計すれば、重複していた記事の閲覧数が自動で集計されます。
 

4. 部門別の従業員数を把握する

ピボットテーブルの便利な点は、通常のExcelの表ではすぐに確認できないものを自動で計算できるところにあります。

たとえば、同じデータを含む行の数を集計することが可能です。

Excelのシート内に従業員の一覧があり、従業員の名前の横に所属している部門が記載されている場合、このデータからピボットテーブルを作成して、各部門に所属している従業員の人数を表示することができます。

ピボットテーブルを活用すれば、Excelのデータを部門名で並べ替え、行数を手作業で数える手間を省くことができます。
 

5. 空白のセルに既定の値を入力する

Excelにデータを入力する際、すべてのセルが埋まるとは限りません。

新しいデータが入手できるのを待って、入力しないままでいると、空白のセルが目に付いてわかりにくくなったり、上司にデータを見せたときに説明が必要になったりするおそれがあります。

このような状況でも、ピボットテーブルは威力を発揮します。

ピボットテーブルをカスタマイズすれば、空白のセルに「0円」や「未定」などの既定の値を簡単に入力できます。

データの量が多い表を何人もの人が目にするような場面では、空白のセルにすばやく値を入力できる機能が使えると特に便利です。

ピボットテーブルの空白のセルに自動で値を入力するには、ピボットテーブルを右クリックして［ピボットテーブル オプション］を選択し、表示されたウィンドウで［空白セルに表示する値］のチェックボックスをオンにして、セルに他の値が入力されていない場合に表示する値を指定します。
 

1. 一定の範囲の行や列にデータを入力する
2. 条件を指定してデータを並べ替える
3. セルを選択してピボットテーブルを作成する
4. フィールドを選択して［行ラベル］の部分にドラッグする
5. フィールドを選択して［値］の部分にドラッグする
6. 集計方法を変更する

1. 一定の範囲の行や列にデータを入力する

ピボットテーブルの作成は、基になるデータを含んだExcelの表を作るところから始まります。

Excelの表を作成するには、一定の範囲の行や列に値を単純に入力していきます。先頭の行や列には、値の分類を表す見出しを入力しましょう。

たとえば、ブログ記事の閲覧数の一覧をExcelで作成する場合、それぞれの記事のURL、タイトル、現在までの閲覧数などの列を作成することになるでしょう。

なお、この後の手順は、ここで作成した表を使って説明していきます。

2. 条件を指定してデータを並べ替える

必要なデータをシートにすべて入力したら、ピボットテーブルを作成した後に扱いやすくなるように、条件を指定してデータを並べ替えておきましょう。

データを並べ替えるには、まず画面上部の［データ］タブをクリックして、［並べ替え］アイコンを選択し、表示されたウィンドウで、並べ替えの基準になる列と並べ替える順序を指定します。

たとえば、現在までの閲覧数のデータで並べ替えるには、［列］のセクションでこの列を指定し、記事を並べ替える順番を昇順と降順のどちらにするかを選択します。

そして、［並べ替え］ウィンドウの右下にある［OK］ボタンをクリックすると、ブログ記事の閲覧数の順にデータが並べ替えられます。

3. セルを選択してピボットテーブルを作成する

ワークシートにデータを入力し、好きな順番に並べ替えたら、ピボットテーブルで集計するセルを選択し、画面上部の［挿入］タブをクリックして、［ピボットテーブル］のアイコンを選択します。

あるいは、ワークシート内の任意の場所をクリックしてから［ピボットテーブル］を選択し、ピボットテーブルに含めるセルの範囲を指定することもできます。

アイコンを選択するとダイアログボックスが開くので、セルの範囲に加えて、ピボットテーブルを新規のワークシートと既存のワークシートのどちらに配置するかを選択します。

新しいワークシートに配置する場合、Excelブックの下部にあるタブから別のシートに移動することができます。選択が完了したら、［OK］をクリックします。

なお、セルを選択した状態で、［ピボットテーブル］アイコンの右にある［おすすめピボットテーブル］のアイコンをクリックし、提示された行や列の集計方法の中からピボットテーブルに使用するものを選ぶこともできます。

注：Excelのバージョンが2016より古い場合、［ピボットテーブル］のアイコンが、画面上部の［挿入］タブではなく、［テーブル］タブや［データ］タブの中に置かれている場合があります。

また、Google スプレッドシートの場合は、上部のメニューで［データ］、［ピボットテーブル］の順に選択すれば作成できます。
 

4. フィールドを選択して［行ラベル］の部分にドラッグする

手順3が完了し、新しいピボットテーブルが作成されたら、列の見出し名の付いたフィールドを［行ラベル］の部分にドラッグします。

この行ラベルは、ブログ記事のタイトルや製品名など、ピボットテーブルがデータを整理するときの基準を表すものです。

今回は、大量のブログ記事のデータをタイトル別に整理したいので、記事のタイトルを表すフィールドをクリックして、［行ラベル］の部分にドラッグします。

注：Excelのバージョンによって、ピボットテーブルの見た目が異なる場合がありますが、基本的な操作は変わりません。
 

5. フィールドを選択して［値］の部分にドラッグする

データを整理する基準を決めたところで、次はピボットテーブルに値を追加します。そのためには、フィールドを選択して［値］の部分にドラッグします。

今回のサンプルでは、ブログ記事の閲覧数をタイトル別に集計したいので、閲覧数を表すフィールドを［値］の部分にドラッグします。

6. 集計方法を変更する

既定では、指定した値の合計が計算されますが、平均値を算出したり、最大値や最小値を求めたりするなど、自分の希望に応じて集計方法を変更することができます。

Macの場合は、［値］にドラッグしたフィールドの横に小さく表示されている「i」のアイコンをクリックし、使用するオプションを選択して［OK］をクリックすると、ピボットテーブルが自動的に更新されます。

Windowsの場合は、フィールドの横にある下向きの小さい三角形のアイコンをクリックし、［値フィールドの設定］を選択すると、集計方法のオプションが表示されます。

ここでは、ピボットテーブルをさらに便利に使える機能を5つ紹介します。
 

スライサー機能

ピボットテーブルのデータをフィルタリングできる機能です。特定項目のデータのみを確認したい場合に活用できます。

「ピボットテーブルツール」を選択し、「分析」から「スライサーの挿入」を選択するとスライサーが表示されます。その後、分析したい項目を選んでOKボタンを押すとデータが表示されます。

レイアウト・デザイン変更

データ分析に目的に合わせて表のレイアウトを変更することで、分析しやすくなります。レイアウトの種類は3つあるので、使い分けるようにしましょう。

1. コンパクト形式：データを横に広がらないようにし、スクロールを最小限にして見やすくしたい場合におすすめ
2. アウトライン形式：項目を横に展開しているため、階層別の分析がしやすい
3. 表形式：表で整理されるので、別のシートにコピーしやすく汎用性が高い

グループ化

一覧化されたデータをカテゴライズして整理したい際には、グルーピングが有効です。

例えば、月ごとのデータを四半期ごとにグルーピングすると、以下のようになります。

＜グルーピング前＞

• 1月
• 2月
• 3月
• 4月
• 5月
• 6月

＜グルーピング後＞

• 第1四半期
  • 1月
  • 2月
  • 3月
• 第2四半期
  • 4月
  • 5月
  • 6月

上記では日付や時刻でグルーピングをしましたが、グループの名前は自由に設定できるので、目的に合わせたグルーピングが可能です。
 

ピボットテーブルを作成する基本的な手順については、以上の説明のとおりですが、ピボットテーブルを使って行う作業の種類によっては、他の操作や機能についても知っておいた方がよい場合もあります。

たとえば、ピボットテーブル内のデータの順番を変更したい場合には、SORT関数が便利です。あるいは、別のソースのデータをレポートに取り込みたい場合は、VLOOKUP関数が役に立ちます。

また、Excelでもっときれいなグラフを作成したいとお悩みの方は、以下のグラフのデザインを良くするためのヒントに関するHubSpotブログの記事をご覧ください。