Excel でピボットテーブルを利用した値の範囲ごとの集計とグラフの作成方法 -Officeソフトの使い方(46)-

Excel で回答や成績といった個々のデータをピボットテーブルと呼ばれる機能を利用して年代や閾値といった範囲で区切って集計し、円グラフなどに視覚化する方法を紹介する。

Excel でピボットテーブルを利用した値の範囲ごとの集計とグラフの作成方法




1.ピボットテーブルとは


ピボットテーブルとは Excel のシートにある複数のデータを集計したり分析する機能で、関数の打ち込みを行うことなくマウス操作だけで結果を表示させることができる。



テレビや新聞等で見かける、アンケートの回答を年代ごとに分かれて集計された円グラフや、テストの得点を 10 点ごとに区切った範囲にそれぞれ何人いるかといった集計にピボットテーブルが利用される。

2.ピボットテーブルで集計してグラフにする方法


ピボットテーブルでデータを集計し、最終的にグラフとして視覚化する方法は次の通りである。
今回は「毎月のアクセス数を 1,000pv ごとに区切った場合の個数」を円グラフにする。

2-1.シートに集計したい値を入力して選択


ピボットテーブルを利用するためには集計元となる複数のデータが必要になる。
下図のように Excel のシートに月ごとのアクセス数をセルに入力する。

集計したいデータを入力


2-2.ピボットテーブルの作成


次にピボットテーブルを作成するために Excel の上部メニューより「挿入 -> ピボットテーブル」を選択するとウィンドウ「ピボットテーブルの作成」が表示される。

ピボットテーブルの作成




ウィンドウ「ピボットテーブルの作成」では、上記にてシートに値を入れた状態なので「テーブルまたは範囲を選択」にチェックを入れてデータがある範囲をドラッグして指定する。
この後、集計時の切り替えで項目名を利用するため、一行目の項目名も範囲に含めるようにする。

ピボットテーブルの作成


また、今回はピボットテーブルで集計した結果をデータを入力したシートとは別のシートに展開するために「新規ワークシート」にチェックし、ボタン「OK」をクリックする。

作成されたピボットテーブル


ボタン「OK」をクリックすると上図のように別シートにピボットテーブルと、Excel の画面右側にピボットテーブルのフィールドが表示され、ピボットテーブルが作成された状態となる。

2-3.ピボットテーブルの属性を選択


ピボットテーブルが作成された後は、作成時に指定した範囲内で集計を行うために属性を設定する。
集計する範囲として「日付」と「アクセス数」を含めたのでピボットテーブルのフィールドにもこの 2 つの項目が表示される。

ここでさらに集計・分析するために「アクセス数」のみを左下の行にドラッグすると左側のシート欄が行ラベルに切り替わる。
今回は元のデータが行(縦方向)ごとに入力したためピボットテーブルのフィールドも配置を示す行にセットしたが、もし列(横方向)ごとにデータを入力していた場合は右上の列にドラッグする。

集計するデータの配置を指定


続いて集計したい項目を指定するためにもう一度「アクセス数」を右下の値にドラッグし、ドラッグ後は左のシート欄に元データの値が表示される。

集計する値を指定


次に一定範囲ごとの件数を集計したので、ピボットテーブルのフィールドの右下の値にある「合計 / アクセス数」のプルダウンをクリックして「値フィールドの設定」を選択する。

値フィールドの設定


「値フィールドの設定」を選択するとウィンドウ「値フィールドの設定」が表示されるので、選択したフィールドのデータより「データの個数」を選択してボタン「OK」をクリックする。

データの個数を選択


データの個数を選択すると左側のシート欄で合計値だったものが個数の表示に切り替わる。
この設定により「アクセス数」の各値がユニークキーとなり、同じ値があれば個数としてカウントアップされる集計になっている。



2-4.集計した値をグループ化


ピボットテーブルの属性を設定したことにより、アクセス数ごとの個数が割り出されたので、次いで範囲を指定して範囲ごとの個数を集計させるためグループ化を行う。
今回は「アクセス数」を 1,000 ごとの範囲にグループ化するため、行ラベルにあるいずれかの値の上で右クリックしてメニューを表示し、メニュー内の「グループ化」を選択する。

グループ化を選択


表示されたウィンドウ「グループ化」には先頭の値、末尾の値、単位の 3 つを指定することができ、1,000 ごとの範囲にするために次のように入力してボタン「OK」をクリックする。

・先頭の値:0
・末尾の値:10000
・単位:1000

グループ化の設定


先頭の値と末尾の値にチェックを入れると自動的にピボットテーブル内の先頭または末尾の値を取得してくれるが、上図のように先頭の値が 379 で単位を 1,000 にしてしまうと範囲が 379 ~ 1,378、1,379 ~ 2,378 となるため、先頭の値は 0、末尾の値は 10,000 とする。

グループ化されると下図のように 1,000 ごとの範囲でデータの個数がさらに集計される。

グループ化して個数を再集計


2-5.ピボットテーブルからグラフを作成


以上でアクセス数が 1,000pv ごとの件数を集計した表が作成されたので、この表を利用して円グラフに視覚化する。



ピボットテーブルからグラフを生成する場合も通常のシート上にあるデータからグラフを作る流れと同様で、グラフで利用する値をマウスで選択した状態で、上部メニューより「挿入 -> グラフ」で作成したいグラフを選択する。

挿入 -> グラフ


今回は円グラフを選択し、アクセス数を範囲分けしてカウントしたものが下図となる。

ピボットグラフを利用したグラフ border=


円グラフにすることでアクセス数の 1,000 以上 2,000 未満の月が最多の 60 回で、2,000 以上のアクセス数になると現象傾向にあるのが視覚的にわかるようになる。

このように元のデータをピボットテーブルで集計することで簡単に分析できるとともに、資料に利用するグラフの生成まで簡単に行うことができる。

できるポケットExcel関数 基本&活用マスターブック Office


関連記事