Excel・PowerPoint使いこなし術

ExcelとPowerPointに関連する使いこなしヒント集

「ピボットテーブル」は怖くない! データ「蓄積」から「分析」へステップアップ

表に入力したデータの集計にバツグンの威力を発揮する「ピボットテーブル」。ピボットテーブルをマスターすると、ビジネスデータをさまざまな角度から分析できるようになる。

[2017年 7月27日公開]

ピボットテーブルとは? これを知れば「集計」「分析」ができる!

日々の売り上げデータをExcelのワークシートに入力・保管している人は多いだろう。しかし、データは「蓄積」するだけではただの記録だ。貯めたデータをいかに「集計」し「分析」してビジネスに生かすかが、競合他社に差をつけるポイントとなる。

分析のためには、まず集計が不可欠だ。例えば売れ筋商品を知るには「商品ごとの集計」、業績の推移を知るには「年ごとや月ごとの集計」、支店の成績を知るには「支店ごとの集計」という具合に、データ分析の目的に応じて、さまざまな項目での集計が必要になる。

「ピボットテーブル」を使うと、こうした集計を簡単に行える。図1を見てほしい。売り上げデータを入力した表だ。データには、「支店」「注文形態」「金額」などの項目が含まれている。そして、この表をもとに作成したのが、図2のピボットテーブルだ。「支店」別「注文形態」別に「金額」を集計している。

図1 日々の売り上げを入力した表。

図2 図1の表から作成したピボットテーブル。行見出しに「支店」、列見出しに「注文形態」を配置して、「金額」を集計している。

驚くことに、ピボットテーブルの集計には数式の入力は一切不要。集計項目として「支店」「注文形態」「金額」の三つを指定するだけで、「支店別/注文形態別」集計表を自動作成できるのだ。これらの集計項目は、「フィールドリスト」と呼ばれるウィンドウの「行」欄、「列」欄、「値」欄で指定する仕組みになっている(図3)。

図3 画面右のウィンドウを「フィールドリスト」と呼ぶ。フィールドリストの「行」「列」「値」がそれぞれピボットテーブルの行見出し(赤)、列見出し(ピンク)、集計値(緑)に対応している。

フィールドリストの「行」「列」「値」の項目は、自由に変更できる。つまり、自由に集計項目を入れ替えて、さまざまな角度からデータを集計・分析できるのだ。これこそが、ピボットテーブル最大の強みと言えるだろう。

ピボットテーブルの作成は簡単!

実際に、ピボットテーブルを作成してみよう。集計元の表の先頭行に、「受注日」「支店」などの項目名を必ず入力しておく。「ピボットテーブルの作成」ダイアログボックスを開き、集計元の表のセル範囲を指定して「OK」ボタンをクリックする(図4)。すると、新しいワークシートに空のピボットテーブルが作成される。画面右にはフィールドリストが表示され、集計元の表の1行目に入力されている項目名が並ぶ(図5)。

図4 表の中のセルを一つ選択して、「挿入」タブにある「ピボットテーブル」ボタンをクリック。表示される設定画面で、表のセル範囲が正しく指定されていることを確認して、「OK」ボタンをクリックする。

図5 新規シートに空のピボットテーブルが作成される。画面右のフィールドリストには、集計元の1行目に入力されている項目名が一覧表示される。

続いて、集計項目を指定していく。操作は簡単。フィールドリストの上部で集計項目を選び、下部の「行」「列」「値」欄にドラッグするだけだ。すると、指定した項目のデータがピボットテーブルに配置され、集計が行われる(図6、図7)。

図6 フィールドリストの上部で「支店」にマウスポインターを合わせ、「行」欄にドラッグすると、ピボットテーブルの行見出しの位置に支店名が表示される。

図7 同様に、「注文形態」を「列」欄に、「金額」を「値」欄にドラッグすると、「支店別/注文形態別」の集計が行われる。

表示形式を設定して数値を読み取りやすくする

ピボットテーブルで大量の金額データを集計すると、数値が非常に大きくなる。桁が読み取りづらいので、桁区切りの表示形式を設定したい。しかし、「ホーム」タブの「桁区切りスタイル」ボタンを使って設定した場合、集計項目を入れ替える際に表示形式が解除されてしまうことがある。ここでは、ピボットテーブル専用の表示形式の設定方法を紹介する。

まず、数値のセルを選択して、「分析」タブの「フィールドの設定」ボタンをクリックする。「分析」タブは、ピボットテーブル内のセルを選択したときだけに表示されるタブだ。「値フィールドの設定」ダイアログボックスが表示されたら、「表示形式」ボタンをクリックする(図8)。すると、表示形式の設定画面が開くので、桁区切りを設定すればよい(図9、図10)。

図8 数値のセルを選択して、「分析」タブの「フィールドの設定」ボタンをクリックする。表示される「値フィールドの設定」ダイアログボックスで「表示形式」ボタンをクリックする。

図9 表示形式の設定画面が開く。「分類」欄で「数値」を選び、「桁区切り(,)を使用する」にチェックを付ける。「OK」ボタンをクリックすると、図8の「値フィールドの設定」ダイアログボックスに戻るので、「OK」ボタンをクリックして閉じる。

図10 選択したセルだけでなく、「金額」の全セルに桁区切りの表示形式が設定された。

図8の「値フィールドの設定」ダイアログボックスは、ピボットテーブルの「値」欄に配置した項目(ここでは「金額」)に関する設定を行う画面だ。この画面から表示形式を設定すると、「セルB5」のような固定されたセルではなく、「『金額』が表示されているセル」を対象に設定が行われる。ピボットテーブルの「金額」以外の項目が入れ替わって「金額」が表示されるセルが変化した場合でも、必ず「金額」のセルに桁区切りの表示形式が適用される。

ちなみに、「値フィールドの設定」ダイアログボックスでは「個数」「平均」「最大」「最小」など、集計方法を変更することも可能だ。集計方法を変更すれば、「金額の平均」や「最高金額」などを知ることができる。

集計元のデータの修正/追加を集計に反映させる

集計元の表のデータを修正しても、自動ではピボットテーブルに反映されない。データを修正したときは、「分析」タブの「更新」ボタンをクリックすると、最新のデータで再集計される(図11)。

また、集計元の表に新しいデータを追加したときは、「分析」タブの「データソースの変更」ボタンをクリックしよう。すると、集計元のセル範囲を指定するための画面が表示されるので、新規データを含めたセル範囲を指定すればよい。

図11 ピボットテーブル内のセルを選択しておく。「分析」タブの「更新」ボタンをクリックすると、集計元のデータの変更が集計結果に反映される。また、「データソースの変更」ボタンをクリックすると、集計元のセル範囲を指定し直せる。

集計項目を入れ替えて、別の角度で集計する

ピボットテーブルの醍醐味は、マウス操作で簡単に集計項目を指定したり、入れ替えたりできることだ。行見出しや列見出しを入れ替えれば、データをさまざまな視点から分析できる。ここでは、「支店別/注文形態別」の集計表を「商品別/支店別」の集計表に作り替えてみる。操作はフィールドリスト上で行うが、ピボットテーブル以外のセルを選択するとフィールドリストが消えてしまうので、必ずピボットテーブル内のセルを選択しておこう。

操作は至って簡単。不要になった「注文形態」を集計表から削除するには、フィールドリストの外にドラッグする(図12)。また、「支店」を行見出しから列見出しの位置に入れ替えるは、フィールドリストの「行」欄から「列」欄へドラッグして移動すればよい。新規に「商品」を追加するのも、ドラッグでOK(図13)。「行」「列」の内容が変更すると、即座にピボットテーブルの集計項目も変化する。

図12 「列」欄にある「注文形態」をフィールドリストの外にドラッグして削除する。次に、「行」欄にある「支店」をドラッグして、「列」欄に移動する。

図13 「商品」をドラッグして「行」欄に追加する。すると、フィールドリストの「行」「列」の内容の変更に伴い、ピボットテーブルの行見出しと列見出しが変わり、再集計が行われる。

集計項目や集計対象のデータを抽出する

ピボットテーブルでは抽出機能も充実している。分析対象のデータだけを抽出することで、集計表が見やすくなり、効率よくデータ分析を行える。

「行見出し」や「列見出し」に表示されるデータを絞り込むには、「行ラベル」や「列ラベル」と表示されるセルの「▼」ボタンをクリックして、メニューから抽出条件を選ぶ(図14)。すると、瞬時に指定したデータが抽出される(図15)。なお、抽出を解除するには、メニューから「○○からフィルターをクリア」を選べばよい。

図14 「行ラベル」のセルにある「▼」ボタンをクリックする。非表示にする商品のチェックを外し、必要な商品だけにチェックが付いた状態にして、「OK」ボタンをクリックする。

図15 抽出が実行され、チェックが付いた商品だけが表示された。抽出を解除するには、図14のメニューから「○○からフィルターをクリア」を選ぶ。

「スライサー」という機能を使うと、「行見出し」や「列見出し」のデータだけでなく、集計対象のデータそのものを抽出することも可能になる。スライサーは、Excel 2010で追加された抽出機能だ。

例えば、「商品別/支店別」の集計表で、特定の「注文形態」のデータだけを集計するには、「注文形態」のスライサーを使用する。スライサーで「イージーオーダー」を選ぶと集計表が「イージーオーダー」のみの集計結果に、「フルオーダー」を選ぶと「フルオーダー」のみの集計結果に早変わりする(図16~図18)。

図16 ピボットテーブル内のセルを選択して、「分析」タブの「スライサーの挿入」ボタンをクリックする。表示される設定画面で「注文形態」を選ぶ。

図17 「注文形態」スライサーが表示される。タイトルバーをドラッグして、見やすい位置に移動しておこう。最初は、スライサーで「イージーオーダー」と「フルオーダー」が選択されており、ピボットテーブルには全データの集計結果が表示される。

図18 スライサーで「イージーオーダー」をクリックすると、「イージーオーダー」の売り上げデータだけが集計される。図17と比べると、数値が小さくなっているのが分かるだろう。なお、抽出を解除するには、スライサーの右上端にある「フィルターのクリア」ボタンをクリックする。また、スライサーを削除するには、スライサーの無地の部分をクリックして、「Delete」キーを押す。

月ごとや四半期ごとにグループ化して集計する

時系列の売り上げ推移を分析するために、日付を月単位や四半期単位にまとめたいことがある。Excel 2016では、日付データを「行」や「列」に追加すると、自動的にグループ化が行われる(図19)。複数月の日付が含まれる場合は「月」「日」でグループ化され、複数年のデータが含まれる場合は「年」「四半期」「月」でグループ化される。

図19 「受注日」を「行」欄にドラッグすると、自動的に「月」と「受注日」が追加される。ピボットテーブルの行見出しのセルには「1月」「2月」などと表示され、月ごとの集計が行われる。月名の前の「+」や「-」をクリックすれば、その月の日付の展開と折り畳みを切り替えられる。図は、「6月」のデータを展開したもの。

「四半期」や「年」など、別の単位でグループ化したいときは、「グループ化」ダイアログボックスでグループ化の単位を設定する(図20、図21)。なお、Excel 2013以前のバージョンでは日付データを「行」や「列」に追加しても自動ではグループ化が行われないが、図20のように「グループ化」ダイアログボックスを使えばExcel 2016と同様にグループ化の単位を設定できる。

図20 月のセルを選択して、「分析」タブの「グループ」→「グループの選択」をクリックする。「グループ化」ダイアログボックスが表示されたら、「月」と「四半期」を選択して「OK」ボタンをクリックする。

図21 「四半期」と「月」でグループ化された集計表に変わる。

今回は、ピボットテーブルを使用したデータの集計方法を紹介した。ピボットテーブルを使いこなすことで、これまで蓄積するだけだったデータを活用・分析のフェーズへ移すことができる。ぜひマスターして、ビジネスを優位に進めてほしい。

もっとExcelの応用スキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をオススメします

マイクロソフトの「Office 365」は、ExcelやPowerPointを始めとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る