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

[最新更新] 2023年07月26日
[初回公開] 2021年09月09日

Excel で年代や性別の情報を持ったアンケート回答のデータや、教科ごとの成績が表化した学生のデータといったまとめられた情報をピボットテーブルと呼ばれる機能を利用して特定の年代や任意の基準値で範囲を区切って集計し、円グラフなどに視覚化する方法を紹介する。

１．Excel のピボットテーブルとは

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

上図はウェブサイトの各月の総アクセス数を 1,000pv ごとに区切った場合の個数をピボットテーブルを使ってグラフ化した様子である。
通常の Excel のグラフはシート内のセルに入力した値 1 つずつがグラフの要素となって表れるが、ピボットテーブルを使うことで集計した結果としてコンパクトに情報をまとめることができる。

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

２．Excel のピボットテーブルで集計してグラフにする方法

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

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

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

2-2．ピボットテーブルを作成する

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

ウィンドウ「ピボットテーブルの作成」では Excel で通常のグラフを作成するようにセルに入力済の値を範囲指定する。
そのため「テーブルまたは範囲を選択」にチェックを入れて、前述のデータがある範囲をドラッグして指定する。

このデータの範囲を指定する時、ピボットテーブル集計で項目名を利用して切り替えを行うため、1 行目の項目名（サンプルでは「日付」と「アクセス数」）も範囲に含めるようにする。

また、今回はピボットテーブルで集計した結果を「アクセス数」など元データがあるシートとは別のシートに展開することとする。
そのため「新規ワークシート」にチェックし、ボタン「OK」をクリックすると下図のようにピボットテーブル用のシートが作成される。

新しく作成したシートはピボットテーブルと、Excel の画面右側にピボットテーブルのフィールドが表示された状態となる。

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

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

ここでさらに集計・分析するために上図のように「アクセス数」のみを左下の欄「行」にマウスでドラッグすると左側のシート欄が行ラベルに切り替わる。

今回は元のデータが行（縦方向）ごとに入力したためピボットテーブルのフィールドも配置を示す行にセットしたが、もし列（横方向）ごとにデータを入力していた場合は右上の列にドラッグする。

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

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

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

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

2-4．集計した値をグループ化する

ピボットテーブルの属性を設定したことにより、アクセス数ごとの個数が割り出された。
続いてアクセス数（pv）の範囲を指定して、範囲ごとの個数を集計させるためにグループ化を行う。

今回は「アクセス数」を 1,000pv ごとの範囲にグループ化したいため、下図の行ラベルにあるいずれかの値の上で右クリックしてメニューを表示し、メニュー内の「グループ化」を選択する。

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

• 先頭の値：0
• 末尾の値：10000
• 単位：1000

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

ボタン「OK」をクリックしてグループ化されると下図のように 1,000 ごとの範囲でデータの個数がさらに集計される。

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

以上でアクセス数が 1,000pv ごとの件数を集計した表が作成されたので、この表を利用して円グラフに視覚化する。
ピボットテーブルからグラフを生成する場合も通常のシート上にあるデータからグラフを作る流れと同様となり、グラフの元データとなる値があるセルをマウスで選択した状態で、上部メニューより「挿入 -> グラフ」で作成したいグラフを選択する。

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

円グラフにすることで例えばアクセス数の「1,000 以上で 2,000 未満の月」が最多の 60 回で、2,000 以上のアクセス数になるほど減少傾向にあるのが視覚的にわかるようになる。

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

関連記事