ピボットテーブルで何ができる？表の集計・分析を効率化しよう

突然ですが、あなたはExcelを何に使っていますか？簡単な表やグラフの作成、単純な関数程度なら少し調べればなんとかなります。しかし、複数の要素が複雑に絡み合う計算となると話が違ってきます。例えば「今期の売上成績を分析して、それを元に来期の計画を立てて提案してくれ」と言われたら、あなたならどうしますか？

• 営業成績を上げているのはどの部署？
• 売れ行きの良い商品は何？
• 発注が集中しやすいのはいつ？
• この商品はどの層の顧客に人気がある？

このような疑問に答えるために一つずつ表を編集していたら膨大な作業時間が必要になってしまいます。ピボットテーブル機能を使えるようになれば、面倒な作業も便利に片付けられます。

（例）

• 表の中にある膨大なデータを自動集計できる
• 重複したデータを一つにまとめる
• 項目ごとに分析する
• 面倒な数式や特別な関数は不要、クリック・ドラッグ操作だけ

とはいえ、ピボットテーブルの機能や活用方法は多岐にわたるので一度でマスターすることは難しいもの。今回は基本的な操作・機能に絞って説明します。画面上の操作を追いながら、ピボットテーブルの使い方を覚えていきましょう。なお、本記事の操作画面はmacOS上で撮影したものを使用していますが、Windows版でも同様の機能を利用できます。

【ピボットテーブルでできること】クロス集計を使ってデータを集計・分析しよう！

ピボットテーブルの最大の特徴はクロス集計表を簡単に作成できること。関数を使う場合はSUM関数を二方向に設定しなければなりませんが、ピボットテーブルの機能を使えば簡単にクロス集計表を作成できます。

そもそもクロス集計とは？

クロス集計とは、縦・横の二方向に項目を割り振って集計する表のことで、二つの項目の相関関係を明らかにすることができます。元となるデータの中から二種類の要素を限定して分析・集計を行う方法です。例えば次のような表について、集計表を作成する場合を考えてみましょう。

「チームごとに受注総額が最も高かった種別はどの仕事？」と聞かれたら、どのような表を作ればいいでしょうか？フィルター機能やセルのコピー・ペーストだけで作成しようとすると、かなりの手間です。この作業を一瞬で終わらせるのがピボットテーブルなのです。

ピボットテーブルは複数の項目がある複雑な表の集計・分析に最適

実際にピボットテーブルで作成した表がこちらです。

表の作成にかかった時間は数秒程度。例えばここに「受注総額と合わせて受注件数も表示したい」といった場合も、すぐに項目を追加できます。

こちらも所要時間は数秒ほど。ピボットテーブルを使えば表計算の業務負担を大幅に減らせます。

ピボットテーブルを使って実際に集計・分析してみよう

ピボットテーブルを作成するには、その元になる表が必要になります。

ただし、どんな表でも使えるわけではありません。

• 表の中に空白やセル結合がある
• 同じ列の中に異なる書式設定のデータがある
• 項目に表記ゆれがある

このような表からピボットテーブルを作成すると、データの集計がうまくいかないことがあります。ピボットテーブルを作成する前に、元となる表に不具合がないか確認しましょう。

ピボットテーブルの枠を作成しよう

元になる表の一部をクリックして選択します。その状態で［挿入］タブから［ピボットテーブル］を選択します。

上の画面はMacですが、Windowsの場合も［挿入］タブから［ピボットテーブル］で同じ機能が使えます。

［ピボットテーブルの作成］ウィンドウが開きます。今回は特に設定を変更する必要はないので、［OK］をクリックします。

自動で新しいシートが作成され、ピボットテーブルの枠が表示されました。ここからの作業では、この枠の中におさめる表を作っていきます。

フィールドリスト（ピボットテーブルを作成する枠）を表示させよう

ピボットテーブルの枠をクリックすると、画面右側に「フィールドリスト」が表示されます。

赤く枠で囲んだのがフィールドリストです。このフィールドリストを操作して、ピボットテーブルの中身を作っていきます。フィールドリストの上部、赤い枠で囲んだ部分には［フィールド名］が表示されています。

「フィールド」とはピボットテーブルを作る前に選択した表の見出し部分のことです。

この見出しがピボットテーブルを作るための項目になります。

どの項目で分析するか決めよう

まず、分析したい内容によって「どのフィールドを項目に指定するか」を決める必要があります。今回は先ほど作成した「チームごとに受注総額が最も高かった種別」を分析するためのピボットテーブルを例に説明します。フィールドテーブルの中には［フィルタ］［行］［列］［値］の枠があります。

［行］に指定したフィールドはピボットテーブルの縦の項目になり、［列］に指定したフィールドはピボットテーブルの横の項目になります。

「値」は表の中で集計される数値です。

分析に使うフィールドを項目に設定しよう

実際に項目を［行］［列］に設定してみましょう。［フィールド名］からフィールドをクリックして選択します。次にフィールドを選択して［行］［列］の枠の中にドラッグします。

［行］に［チーム］を、［列］に［種別］をそれぞれドラッグして移動させます。

縦横の項目が挿入されました。元のデータに重複した値が複数ある場合は、自動的に重複したデータをまとめてくれます。

班ごと、種別ごとに集計された受注総額が挿入されました。このように［行］が縦の項目に、［列］が横の項目に、［値］が表の中の数値になることをおさえておきましょう。

同じピボットテーブルを別の項目でもう一度分析してみよう

ここまでの作業では「チームごとに受注総額が最も高かった種別」のクロス集計表を作成しました。ピボットテーブルの機能を使えば、同じ表からさらに別のクロス集計表を作成することができます。例えば、同じ表から「各社員が受注した件数を種別ごとに集計したい」と言われたら、どんな項目からクロス集計表を作成すればいいでしょうか？答えは「縦横の項目を［社員名］［種別］の組み合わせに変更し、［件数］を集計対象の数値にする」です。

では、上のような表をピボットテーブルの機能を使って作成しましょう。同じ表を元に新しいピボットテーブルを作成することもできますが、今回は既存のピボットテーブルを修正する方法で作ります。まずはピボットテーブルをクリックしてフィールドリストを表示させます。

［行］［値］の枠にはそれぞれ［チーム］［受注総額］のフィールドが入っています。これから作るピボットテーブルではこれらの項目は不要なので、削除してしまいましょう。まずは［行］から［チーム］を削除します。フィールドを削除したいときは、フィールドを選択して枠外にドラッグします。

フィールドが削除され、ピボットテーブルの内容も変更されました。

同様に、［値］から［合計／受注総額］を削除します。

これで［行］［値］の中が空になりました。

今度は［行］［値］の枠に、それぞれ［社員名］［件数］のフィールドをドラッグします。

ピボットテーブルの項目と数値が入れ替わりました

このようにピボットテーブルの機能を使用すれば、一瞬で表の項目や数値を編集することができます。

ピボットテーブルからグラフを作って分析しよう

作成したピボットテーブルからさらにグラフを作成することもできます。グラフにすることでより視覚的に分かりやすくなるだけでなく、フィールドリストの編集やフィルター操作によって表示する項目を変更することも可能です。

グラフを作成する手順

実際にグラフを作成しましょう。ピボットテーブルをクリックすると、右上に［ピボットテーブル分析］という項目が表示されます。

Windowsでは右上に表示される［ピボットテーブルツール］から［分析］を選択し、右側に表示される［ピボットグラフ］をクリックします。

ダイアログボックスが開くので、好きなグラフの形式を選択して［OK］をクリックしてください。

グラフが挿入されました。ピボットテーブルから作成したグラフのことをピボットグラフと呼びます。

ピボットグラフを編集してさらに分析しよう

ピボットテーブルのフィルターを使えば、ピボットグラフの内容を編集することもできます。試しに、ピボットテーブルの［列ラベル］を編集してみましょう。今は全種類の［種別］が表示されていますが、これを［webサイト制作］だけを表示するように編集します。ピボットテーブルの［列ラベル］フィルターを操作すると、列ラベルに表示されている項目の一覧を見ることができます。この一覧のうち、チェックの入った項目がグラフ上に表示されています。

［webサイト制作］以外のチェックを外すと、［webサイト制作］の件数だけを見ることができます。

チェックを外すと、その項目は表示されなくなります。
ここでは［web記事の作成］［動画の編集］のチェックを外したので、２つの種別の受注件数は表示されなくなりました。

着目したい数値だけをまとめたグラフを作成することができました。件数だけではなく、サイト制作を受注した社員は3人いたこともわかります。ある観点に着目してグラフを整理すると、別の視点から物事が見えることがあります。ピボットテーブルとピボットグラフは連動して動いています。両方を見比べながら集計や分析を行うとより効果的です。

ピボットテーブルを使って分析するメリット

ピボットテーブルでは一つのデータから様々な項目を取り出して分析できることを説明してきました。ここでは、ピボットテーブルを使うメリットについてまとめます。

難しい関数を使わなくても分析できる

例えばSUMIF関数を使えば、条件に合致したデータの合計値を求めることができます。
しかし、この方法は当然関数を知っている人にしかできません。引継ぎなどで別の人からデータを受け取ったあと、さらにデータ件数や分析する項目が増えるとどうなるでしょう。関数を知らなければ当然使えません。関数を扱える人であっても、参照するセルの範囲や条件を再指定する手間が発生します。ピボットテーブルを使えば、関数の知識がない人でも「どの項目について分析するか」さえわかれば簡単に表の編集ができます。

他の方法よりも集計・分析がカンタン

ピボットテーブルを使えば簡単にクロス集計表を作成できるため、表作成にかかる時間や労力を削減できます。同じ表からいくつでもピボットテーブルを作成でき、修正も簡単です。
表の作成に時間を減らせる分データの分析に時間を費やせるため、効率的に仕事をすることができます。

ピボットテーブルで分析するときに気をつけること

とても便利なピボットテーブルですが、使用方法を誤ると正しく集計されないことがあります。ここでは、ピボットテーブルを作成するときに必ず注意してほしいことをまとめました。

元になる表に空白やセル結合はない

ピボットテーブルを作成しようとしたときに「そのピボットテーブルのフィールド名は正しくありません」とメッセージが表示され、作成できないことがあります。

理由として考えられることは「元になる表の見出しに空白やセル結合がある」こと。項目に名称が設定されていないと、ピボットテーブルの作成自体ができなくなります。見出し以外の部分空白のセルがある場合も、そのセルが「（空白）」という項目にカウントされてしまい、正しく集計できないことがあります。

ピボットテーブルを作成する前に必ず空白やセル結合がないか確認してください。

元になる表の文字列に表記ゆれはない

ピボットテーブルで集計を行うと、例えば「A班」「　A班」（文字の前にスペースが挿入されている）は別の項目として集計されてしまいます。

特に文字列の前後にスペースが挿入されている場合は一見分かりにくく、注意が必要です。正しい集計を行うためにも、データを入力するときは必ず表記方法をそろえましょう。「文字列」「日付」などのセルの書式設定も見出しごとに統一するようにしてください。

ピボットテーブルを直接編集してはいけない

ピボットテーブルの中のデータは元の表と連動しています。直接ピボットテーブルの中の数値を直接変更しようとすると、次のようなエラーメッセージが表示されます。

データを修正したい場合は必ず元のデータを修正し、「ピボットテーブル分析」から「更新」を選択してください。どうしてもピボットテーブルを直接編集したい場合は、ピボットテーブル全体をコピーし、別のシートに「値の貼り付け」でペーストしましょう。

データを修正したい場合は必ず元のデータを修正し、「ピボットテーブル分析」から「更新」を選択してください。どうしてもピボットテーブルを直接編集したい場合は、ピボットテーブル全体をコピーし、別のシートに「値の貼り付け」でペーストしましょう。

 

まとめ

今回は、ピボットテーブルが様々な視点からの分析に役立つ便利な機能であることをお伝えしました。ピボットテーブルを使えば、二つの項目の相関関係を表す「クロス集計表」を一瞬で作成することができます。関数を入力する手間も省略できますし、自由に項目を指定・編集できるので多角的な分析にも効果的です。多くの人にとってまだ馴染みのない機能かもしれませんが、思っているよりもずっと操作はカンタンです。ひとつずつ使い方をマスターして、効率よく仕事をすすめましょう！

こちらもオススメ