■Excelを用いた度数分布表の作成[3] ピボットテーブル
■ はじめに

 以下においては、右の表1〜表3のような生データを集計して、表1A〜表3Aのような度数分布表を作成する方法を解説します。(「データの個数」を「度数」といいます。)

 生データは、表1では「バス」のような文字データ、表2では「2」のような整数の数値データ、表3では「175.1」のような実数(小数)の数値データとして与えられており、各々数十から数千程度の件数が表計算ソフト上に入力されているものとします。

 数十件程度のデータならば「手作業(=目で数えて)」でも集計はできますが、数千件程度となると手作業ではミスが多くなり、点検にも時間がかかりますので、表計算ソフトを用いて短時間に・正確に数えて「表1A〜表3Aのような度数分布表に仕上げる」方法を考えます。

 ここでは、ほとんどのパソコンにインストールされているMicrosoft Excelのピボットテーブルを用いて度数分布表を作成する方法を解説します。操作画面はExcel2007で解説します。(他のバージョンでもほとんど同じです。)

■ ピボットテーブルの長所

 Excelのピボットテーブルを利用すると
(1) 表1のようにデータが「バス」のような文字列である場合でも度数分布表を簡単に作成することができます。特に、好きなスポーツ、余暇の過ごし方のような自由記述式アンケートの集計などにおいてどのようなデータが登場するかあらかじめ予想できない場合や表記の異なるデータが登場する場合などにおいても楽に対応できます。

(2) 表2のような整数の数値データ表3のような実数の数値データから度数分布表を作成する(さらにヒストグラムを作成する)ことも簡単にできます。

(3) ピボットテーブルを利用するときは、COUNTIF()ではあらかじめ準備しなければならなかった値の一覧表やFRWQUENCY()であらかじめ作成しておかなければならなかった階級表(区切り値の表)は必要なく、ツールから自動的に出力されます。ただし、元のデータが書き換えられたとき、ピボットテーブルの出力結果は自動的には追随しません。データ範囲が変更されない限り(データの追加・削除がなければ)、「」アイコンを利用することにより更新することはできます。)
表1表2表3
生徒番号 通学手段
No.1 バス
No.2 列車+徒歩
No.3 自転車
No.4 列車
No.5 列車+徒歩
No.6 バス+徒歩
No.7 バス+徒歩
No.8 自転車
No.40 バス+徒歩
試行回数 出た目
No.1 2
No.2 6
No.3 1
No.4 1
No.5 4
No.6 5
No.7 3
No.8 1
No.30 1
生徒番号 身長
No.1 175.1
No.2 182.1
No.3 167.0
No.4 176.7
No.5 168.4
No.6 167.8
No.7 166.5
No.8 166.4
No.50 173.3


表1A表2A表3A
通学手段 人数
バス 6
バス+徒歩 10
列車 6
列車+徒歩 6
自転車 9
徒歩 3
合計  40
出た目 回数
1 9
2 4
3 4
4 4
5 6
6 3
合計 30
階級 人数
160.0〜 4
165.0〜 10
170.0〜 13
175.0〜 7
180.0〜 16
185.0〜 0
合計 50
■ ピボットテーブルを利用した度数分布表の作成

(I) 文字列データからの度数分布表の作成

 下の表4のような文字列データ(B列が文字列)からピボットテーブルを利用して度数分布表を作成するには(以下、Excel2007で解説。Excel2002の場合は下記)
(1) データテーブル(下図ではA1からB51までの範囲)のどこかをポイントしておく。この段階でポイントしていないときは、(2)の操作の後に行う。

(2) メインメニューで「挿入」→「ピボットテーブル」を選択する。

(3) (1)の操作を行っていれば、データテーブルの途中に空白行、空白列がない限りデータの下端・右端までの連続した範囲が自動的に「テーブルまたは範囲」として入力される。

(4) 下図5のようにそのワークシート内で右下方向にデータがない範囲を選択して「既存のワークシート」に出力するようにすると、何度も練習したときにワークシートが増え過ぎることはない。→OKボタンをクリック

(5) 下図6にように生徒番号(行見出しに使用しているデータ)は選択せず、通学手段だけを選択→ピボットテーブルが一部できてくる

(6) 右図7にように右上の欄から通学手段をつかんでテーブルの右下の欄までドラッグ

(7) この例のように生データが文字列データとなっている場合には、ピボットテーブルの左上の欄は「データの個数」だけが利用可能となる。右図8のようにできあがる。

表4
図5



※ Excel2002の場合(青字がExcel2007との相違点)
(1) データテーブルのどこかをポイントしておく。この段階でポイントしていないときは、(2)の操作の後に行う。

(2) メインメニューで「データ」→「ピボットテーブルとピボットグラフ レポート」を選択する。→Excelのリスト/データベース、ピボットテーブルを選択して[次へ]

(3) (1)の操作を行っていれば、データテーブルの途中に空白行、空白列がない限りデータの下端・右端までの連続した範囲が自動的に「テーブルまたは範囲」として入力される。

(4) そのワークシート内で右下方向にデータがない範囲を選択して「既存のワークシート」に出力するようにすると、何度も練習したときにワークシートが増え過ぎることはない。→OKボタンをクリック

(5) 右図9にように通学手段だけを選択→「行のフィールドを・・・」と書かれた欄へドラッグ。「データアイテムを・・・」と書かれた欄へも同じものをドラッグ

(6) 上のExcel2007の場合と同様にできあがる。
図6
図7
図8












図9
(II) 整数の数値データからの度数分布表の作成

 右図10のような整数の数値データからピボットテーブルを使って度数分布表を作成する場合は、上記の文字列データとほぼ同じ方法で行うことができます。
 出典:気象庁統計資料、1951年から2010年までのわが国における年ごとの台風上陸数 ホーム > 気象統計情報 > 天気予報・台風 > 過去の台風資料 > 台風の統計資料 > 台風の上陸数 :
http://www.data.jma.go.jp/fcd/yoho/typhoon/statistics/landing/landing.html


 整数の数値データの場合に文字列データと異なる点は、ピボットテーブルを作成したときに、右上の欄でデータとして「年間(台風上陸数)」を選択したときに、右図11のように初め「合計」が表示される点です。
 求めたいものは「年間上陸数を行ラベル」として集計した「データの個数」なので、その目的に合うように次のように変形します。

(*) 右図12のようにピボットテーブルのフールドリストと書かれた欄から「年間」の項目を行ラベルへドラッグします。
(**) 右図13→図14のようにピボットテーブルの左上欄をダブルクリックして「データの個数」を選択します。→OK


※ Excel2002の場合(Excel2007との相違点)
(*) メインメニューで「データ」→「ピボットテーブルとピボットグラフ レポート」を選択する。→Excelのリスト/データベース、ピボットテーブルを選択して[次へ]
(*) 「年間」を「行のフィールドを・・・」と書かれた欄と「データアイテムを・・・」と書かれた欄の両方へドラッグ

図10
図11
図12
図13
図14
(III) 実数の数値データからの度数分布表の作成

(はじめに)
 もし、あなたが「次のような疑問」を持っているとすれば、あなたは数学的に非常に見通しの良い方です・・・「そもそも、実数の数値からなるデータについて、階級の区切り値も示さずに度数分布表を作ると、(理論上実数値には等しいものはまずないから)各々1個ずつという自明な結果となって、意味がないのではないか?

 実際、Excelのピボットテーブルを使って実数の数値データから度数分布表を作ると右図15のようになり(全部異なる値が小さいものから順に並ぶだけだから)各々1人ずつという自明な結果が得られ、あなたの洞察が素晴らしかったことが示されます。

 しかし、Excelでは一旦この場面になってから「身長の区分のグループ化」を行うことができ、この表を「度数分布表」に変えることができるのです。

 右図15のような50人の生徒の身長データがあり、小数第1位まで測定されているものとします。このデータから、「155.0(cm)以上160.0(cm)未満」「160.0(cm)以上165.0(cm)未満」・・・のように5(cm)間隔に階級を設定して度数分布表を作るには
【要点】
○ ピボットテーブルを作ってから、行見出しを右クリックして「グループ化」する。
○ 階級幅は次の図のように設定する。

【詳細】
(*) はじめの操作は、整数の数値データから成る場合と同じ:
(1) データテーブル(図15ではA1からB51までの範囲)のどこかをポイントしておく。この段階でポイントしていないときは、(2)の操作の後に行う。

(2) メインメニューで「挿入」→「ピボットテーブル」を選択する。

(3) (1)の操作を行っていれば、データテーブルの途中に空白行、空白列がない限りデータの下端・右端までの連続した範囲が自動的に「テーブルまたは範囲」として入力される。

(4) そのワークシート内で右下方向にデータがない範囲を選択して「既存のワークシート」に出力するようにすると、何度も練習したときにワークシートが増え過ぎることはない。→OKボタンをクリック

(5) 生徒番号(行見出しに使用しているデータ)は選択せず、通学手段だけを選択→ピボットテーブルが一部できてくる。(しかし、この段階では右図16のように「身長の合計」などという欲しくもない項目が1つできるだけで、まだ度数分布表として目指す形にはなっていない。)

(6) 右図17のようにピボットテーブルのフールドリストと書かれた欄から「身長」の項目を行ラベルへドラッグします。

(7) 右図18のように同じデータが2つ並んだ変な集計表になっています。(数値のデータをピボットテーブルに掛けると初め「合計」になるが、各データがほとんど1つずつしかないから、値と合計が一致します。)

(8) まず、合計ではなく「データの個数」に変更するために、ピボットテーブルの左上端をダブルクリックします。(右図19)→(右図20)
(これで上記の図15で予想した通りに、各データ1個ずつ(たまたま一致するデータがあっても2個くらい)という分布表になります。)
(9) ここから、「身長の区分のグループ化」を行います。
  まず、図21のように、ピボットテーブルの行見出しとなっている身長の欄を「右クリック」します。

(10) 図22のようにグループ化を選択します。

(11) 図23のダイアログボックスで設定するグループ化においては、FREQUENCY()関数の利用のときと同様に、指定した区切り値が160.0<x165.0のように「…より大…以下」型で使われることに注意します。
 すなわち、図24のように設定すると、先頭の値のチェックは外して(最小値を区切りの下端に使うことはやめる)、160.0から5.0の階級幅で185.0まで区切っていくという設定になります。(初めに登場したダイアログボックスに最小値(先頭の値)と最大値(末尾の値)が見えているから、それらの間を5.0ずつ区切っていけばよいと考える)
 図24の書き方は、「先頭の値」「末尾の値」のチェックを外すこと、「単位」(=階級幅)を5.0にすることについては正しい操作ですが、160.0から185.0という値が間違っています。この設定では、160.0<x165.0165.0<x170.0,… 180.0<x185.0の幅に区切られて、日本の常識となっている「…以上…未満」型になりません。
(165.000000000のような高精度な測定を行うときに165.000000000のような区切り値それ自体が登場する可能性はまずありませんが、通常の身長、体重測定のように小数第1位までの測定では165.0のように区切り値自体が登場することがあります。この値を160.0<x165.0 に含めてしまうと160.0x<165.0とは書けないことになります。)
 図25のように区切りたい値よりも0.1だけ小さい小数を使うと、「…以上…未満」型にすることができます。(このデータでは、例えば159.91のような小数第2位までの実数は登場しないから)

(12) OKボタンをクリックすればできあがりです。(図26)

※この図26においては、「ある階級で一番大きな値」が「次の階級で一番小さい値」となって重複して登場していることに注意してください。この表では159.9<x164.9164.9<x169.9,… 179.9<x184.9 を表しています。レポートなどにまとめるときは、値が重複するような書き方は避けて、出来上がった表の「値のコピー」を作ってから、160.0- , 165.0- , … 180.0- などと書き直す方がよいでしょう。
図15
図16
図17
図18
図19
図20
図21
図22
図23
図24
図25
図26



※Excel202の場合、数値データのときの操作上の相違点は(II)で述べた通り。グループ化と区切り値の設定はExcel2007の場合と同じです。
≪練習用の問題≫ 次のデータから度数分布表を作ってください。
 (表Iの問題では、160.0から5.0の階級幅で度数分布表を作ってください。表IIの問題では、表IIの2の区分に従って度数分布表を作ってください。)
 データの取り込み方:キーボードから入力しなくても、画面上で下向きにドラッグ・コピーし、Excel上に単純に貼り付けるとExcelデータになります。(単純なテキストデータなので、安全に取り込むことができます。)

表I 表II 表IIの2
生徒番号 身長
No.1 175.1
No.2 182.1
No.3 167.0
No.4 176.7
No.5 168.4
No.6 167.8
No.7 166.5
No.8 166.4
No.9 184.1
No.10 173.3
No.11 177.3
No.12 179.6
No.13 182.8
No.14 163.1
No.15 182.5
No.16 181.2
No.17 174.6
No.18 170.2
No.19 173.4
No.20 183.7
No.21 183.8
No.22 173.6
No.23 173.5
No.24 181.7
No.25 164.3
No.26 164.1
No.27 174.1
No.28 171.9
No.29 168.7
No.30 180.4
No.31 177.3
No.32 165.0
No.33 168.9
No.34 170.3
No.35 176.0
No.36 165.7
No.37 174.1
No.38 182.7
No.39 182.8
No.40 173.7
No.41 183.2
No.42 180.4
No.43 180.8
No.44 179.2
No.45 166.7
No.46 172.6
No.47 184.1
No.48 173.3
No.49 164.7
No.50 184.2
卵の標本番号 重さ(g)
No.1 60.6
No.2 66.2
No.3 56.6
No.4 58.2
No.5 47.3
No.6 51.8
No.7 69.4
No.8 44.6
No.9 58.6
No.10 66.6
No.11 55.8
No.12 45.2
No.13 63.8
No.14 66.1
No.15 49.7
No.16 66.4
No.17 45.4
No.18 58.0
No.19 68.4
No.20 64.5
No.21 59.9
No.22 61.8
No.23 69.3
No.24 44.0
No.25 40.0
No.26 60.2
No.27 42.5
No.28 58.8
No.29 40.9
No.30 47.1
No.31 48.6
No.32 46.9
No.33 54.1
No.34 41.5
No.35 57.6
No.36 57.2
No.37 69.5
No.38 69.6
No.39 43.1
No.40 68.2
No.41 50.8
No.42 68.7
No.43 45.7
No.44 59.2
No.45 65.2
No.46 57.0
No.47 66.6
No.48 57.7
No.49 65.0
No.50 60.7
No.51 41.0
No.52 60.8
No.53 52.2
No.54 48.0
No.55 64.1
No.56 48.7
No.57 40.2
No.58 44.0
No.59 59.5
No.60 58.0
規格 重さ(g)
SS 40以上46未満
S 46以上52未満
MS 52以上58未満
M 58以上64未満
L 64以上70未満
LL 70以上76未満

≪解答例≫
表Iの問題
データの個数 / 身長        
身長 集計   身長 集計
159.9-164.9 4   160.0〜 4
164.9-169.9 10   165.0〜 10
169.9-174.9 13   170.0〜 13
174.9-179.9 7   175.0〜 7
179.9-184.9 16   180.0〜 16
総計 50      
※ 右欄のようにレポート用に清書するには、ピボットテーブルにおいて文字列として「159.9-164.9」などと記入された欄から左5文字を取り出して =left(D3,5) 、これに0.1を足したもの =(LEFT(D3,5)+.0) に ".0〜" という文字を結合すればよいから、=(LEFT(D3,5)+0.1)&".0〜"などと書けば転記ミスを避けることができます。

表IIの問題
データの個数 / 重さ(g)          
重さ(g) 合計   規格 重さ(g) 度数
39.9-45.9 13   SS 40以上46未満 13
45.9-51.9 9   S 46以上52未満 9
51.9-57.9 8   MS 52以上58未満 8
57.9-63.9 14   M 58以上64未満 14
63.9-69.9 16   L 64以上70未満 16
総計 60   LL 70以上76未満 0
※この度数分布表は階級幅6(g)で作成するようになっています(表IIの2))ので、先頭の値39.9、末尾の値75.9、単位(=階級幅6)とすれば上記の表が得られます。






■読み終わったら→ ここ ←をクリック■
確率統計のメニューに戻る 高校数学のメニューに戻る