■度数分布表，ヒストグラムの作成（Excelの利用）
【例題１】
　右のデータ（図１）は，ある実験を繰り返したときに得られた水溶液の量（単位ml）とします．このデータから，階級幅10ml，第１階級を10ml以上20ml未満として，度数分布表とヒストグラムを作ってください．
（解答）
=== ◆度数分布表の作成◆ ===
数学の問題ですが，データ数が多いので転記に労を要しないように，画面上で右の図１をドラッグ→反転表示→マウス右クリック→コピー→Excelのワークシート上で単純に貼り付けとして，データをExcelに取り込み，計算はExcel上でやってください．（列見出し［水溶液（ml）というラベルの欄］１つと数値データ54件の計55行のデータになります．）
　データは表２のようにA1に貼り付けられたものとして以下の解説を行います．
　以下の操作方法の詳しい解説は，この頁及びこの頁にもあります．
■１･･･ワークシート関数 COUNTIF()を利用する方法
(1) 図２のC列のようにあらかじめ階級表を準備しておきます．
　階級表は問題の指定に従って，10から始まり階級幅10で埋めておきますが，大きな階級としてどの値まで必要となるか（実際には140）は，データを一瞥するなどの方法で調べておく必要があります．
図２
　 A B C D E
1 水溶液(ml) 　 水溶液(ml) 　 　
2 18.2 　 10 　 　
3 25.2 　 20 　 　
4 57.2 　 30 　 　
5 29.0 　 40 　 　
6 56.6 　 50 　 　
7 23.1 　 60 　 　
8 73.8 　 70 　 　
9 24.6 　 80 　 　
10 20.1 　 90 　 　
11 20.9 　 100 　 　
12 41.7 　 110 　 　
13 36.9 　 120 　 　
14 40.3 　 130 　 　
15 54.5 　 140 　 　
… … 　 　 　 　
結果
D
　
4
7
3
11
16
0
6
1
1
2
0
2
1
　
…

(2)○ D2のセルに =COUNTIF($A$2:$A$55,">="&C2)-COUNTIF($A$2:$A$55, ">="&C3)と書きこむと，C2の値以上C3の値未満の度数が得られます．
○ 赤字の所を$マーク付きの絶対参照にしておくと，D2の値をD3からD14にコピー・貼り付けするだけで同様の結果が得られます．
○ この方法では，１つ左のセルの値以下に該当する個数から左下のセルの値以下に該当する個数を引くという式になっているので，C15に次の階級の下端の値も書き込んでおく必要があります．
○　図２右側のような≪結果≫が，D列に表示されればＯＫです．（D15のセルに式を貼り付けると意味のない式になるので，何も貼り付けておらず，何も表示されません．）

■２･･･ピボットテーブルを利用する方法
　この問題のようにデータが小数（実数）値であるときに，ピボットテーブルを利用して度数分布表を作るときは，幾つかの注意点があります．

(1)右の図１のデータをExcel上に貼り付けた結果が図２のようになっているとき，この図２から出発してピボットテーブルを利用して度数分布表を作成するためには，セルA1の「水溶液（ml）」という列見出し［ラベル］もピボットテーブルのデータ範囲に含める必要があります．このラベルを含めずに数値データだけをデータ範囲にすると，先頭のデータがラベルと見なされて，データが1件少なくなります．

(2)ピボットテーブルを作成したときの既定値では，「値の合計」になりますが，ここで調べたいのは「個数」なので，ピボットテーブルの左上端のボタンをダブルクリックして，右図３のように「データの個数」を選択します．

(3)「データの個数」を選択してもデータが小数（実数）値である場合には，一般には同じデータはなく，図４のように異なるデータが１件ずつ数えられているという当然の結果が表示されます．
　そこで，これを「階級幅10ml」の度数分布表にしたいときは，右図４のように左の欄を右クリック→グループと詳細の表示→グループ化→先頭の値10，末尾の値140，単位10とします．
先頭の値 ⇔ 第１階級の10ml以上という指定
末尾の値 ⇔ 最後の階級の140ml未満という指定
単位 ⇔ 階級幅
というように対応しています．

(4)≪結果≫は右図５のようになりますが，次のような特徴があることを頭の片隅に置いておく必要があります．
4.1　一般の度数分布表とは少し異なり，「データがない階級は表示されません」．･･･この問題のデータには，60台の数値，110台の数値は含まれていませんが，ここで行ったようにピボットテーブルのグループ化によって集計するときは，その欄はありませんので，一般に度数分布表としてレポートを文書化するときは，その欄を追加して度数0とします．
4.2　図５のようにまとめた度数分布表においては，第１階級は10ml以上20ml未満を，第２階級は20ml以上30ml未満を表しています．（10-20 | 20-30 | ...などと区切ったときに，日本では「以上-未満」という意味に解釈されるのが普通ですが，これと異なる習慣の読者が含まれると予想されるときは，単に10-20 | 20-30 | ...のまままとめるのでなく，10以上-20未満などと明示した方がよいでしょう．）
　なお，この例題のデータには，40.0というちょうど境界線上に来る値を入れてありますが，これが正しく，40以上-50未満の階級に入っており，30以上-40未満の階級に入っていないことを確かめておくとよいでしょう．

■３･･･分析ツールのヒストグラムを利用する方法
(1)分析ツールのヒストグラムを利用して度数分布表を作るためには，■１の図２のC列と同様，右図６のように階級の境界線となる表をあらかじめ書きこんでおく必要があります．
○ Excel2007でデータ→データ分析→ヒストグラム（Excel2002では，ツール→分析ツール→ヒストグラム）と進むときに，
入力範囲　⇔　図１の元データ（図２のA列）
データ区間　⇔　図６の境界線の表
と対応させます．
○ このとき「ラベル」欄にチェックを入れる場合は，両方ともラベルが必要となりますので，図６の側にもラベルを付けておく必要があります．（図６のように作った境界線の表で先頭行にラベルを付けていないときは，入力範囲（元のデータの「水溶液(ml)」）も，データ範囲（図６の「境界線」というラベル）のいずれも含めずに指定します．つまり，ラベルは両方とも含めるか，両方とも含めないかのいずれかです．）
○　図６の境界値は，小さいものから大きいものへ順に並んでいなければなりません．もし準備した境界線よりも大きな値が含まれている場合には，その度数が「次の級」という形で表示されます．
(2)分析ツールのヒストグラムで出力される表は，指定した境界線（区切りとする値）以下の個数です．
○ この出力結果は，上記の■１，■２の結果と概ね１段ずれていることに注意してください．例えば10の右に表示されているのは10以下の個数です．
○ さらに，この出力結果ではちょうど「区切りとなる値」があるときに，下の階級に含まれることに注意してください．「以上-未満」方式の階級ではなく「よりも大-以下」方式の階級になっているため，右の表中の赤で示したように，40.0というような境界線上のデータは下の階級に含めて数えられ，日本での通常の使い方と合いません．
　これを避けるためには，境界値として，9.99, 19,99, 29.99, ...のように指定すればよいでしょう．（数学的にはおかしなことですが，小数第1位まで測定するような場合，9.99<x≦10, 19.99<x≦20のような値は登場しないので，1の代わりに0.99を使うとよいことになります．）

■４･･･FREQUENCY()関数を利用する方法
(1)分析ツールのヒストグラムを利用するときと同様に，FREQUENCY()関数を利用にも階級の境界線となる表をあらかじめ書きこんでおく必要があります．
(2)図２のようなデータに対して，セルD2に =FREQUENCY(A2:A55,C2:C15) もしくは，データのラベル，区切り線のラベルを両方とも含めて =FREQUENCY(A1:A55,C1:C15)と書きこんだ場合，値が表示されるのはセルD2だけです．（10以下のデータはないので0になります．）
　この式をコピーしてD3:D15に貼り付けても求める度数分布表にはなりません．（=FREQUENCY($A$2:$A$55,$C$2:$C$15)のように絶対参照にしておいても同じです．）
　度数分布表を作るためには，まずD3:D15を選択して反転表示にしておき，次にワークシート上端の数式欄にマウスを当ててから，Ctrl+Shift+Enterとします．
(3)ワークシート関数FREQUENCY()を利用して度数分布表を求める場合にも，上記の■３と同様に「よりも大-以下」方式の階級として出力されますので，注意を要します．（9.99, 19.99, ..　などの値を境界値とすれば防げます．）
図１
水溶液(ml)
18.2
25.2
57.2
29.0
56.6
23.1
73.8
24.6
20.1
20.9
41.7
36.9
40.3
54.5
59.3
22.5
43.6
38.3
59.1
52.4
48.2
58.2
76.8
56.2
49.6
16.3
12.6
54.1
47.6
93.3
41.9
57.6
10.2
43.8
33.7
42.4
40.0
105.7
81.9
101.5
77.7
58.6
128.5
73.4
73.0
55.6
73.5
57.1
55.7
138.0
44.0
56.6
128.3
51.1
図３
図４
図５

図６
境界値
10
20
30
40
50
60
70
80
90
100
110
120
130
140
結果
データ区間 頻度
10 0
20 4
30 7
40 4
50 10
60 16
70 0
80 6
90 1
100 1
110 2
120 0
130 2
140 1
次の級 0


=== ◆ヒストグラムの作成◆ ===
　作成した度数分布表をもとにして，ヒストグラムを作成します．
　ヒストグラムとは簡単に言えば縦棒グラフですが，簡単に見えて手ごわい注意点があります．
(1)　右図７のような度数分布表から縦棒グラフを作成すると，A列のデータが数値型であるために，そのままではA列もB列もグラフになってしまい，図９のようにA列の値の不要な縦棒が表れてしまいます．ここでは，A列の値を横軸のラベルに使うにはどうしたらよいのかというのが１つの問題です．
(2)　図８の血液型百分率を縦棒グラフにすると図11のようになり，それぞれの型は別の型であってつながっていないので，縦棒が離れた棒として表示されても変ではありませんが，ヒストグラムは図12のような多数のデータからなる分布を適当な幅で区切って棒にしたものなので，10-20の隣には20-30があるべきで「隙間が空いているはずはありません」．通常，Excelで縦棒グラフを作成すると，図11のように隙間のあるグラフになるものを，図10のように隙間のない縦棒グラフに仕上げるにはどうしたらよいかというのがもう１つの問題です．
(1)図８のようなデータでA1:B5の範囲を選択して，グラフウィザード→縦棒グラフとしたときに，図11のようにA列のデータが横軸ラベルとなるのは，A列のデータが文字列型だからです．これに対して，図7のようなデータでA1:B14の範囲を選択して，グラフウィザード→縦棒グラフとしたときに，図９のようにA列のデータも棒グラフとして表示されてしまうのは，A列のデータが数値型だからです．
　そこで，図８のデータにおいてA列のデータを文字列型にすれば，B列のデータだけの１種類の縦棒グラフになるはずですが，B列のデータがA列のデータを = COUNTIF()とか =FREQUENCY()のように参照していると，B列のデータが壊れてしまうため出来ない相談になります．
（簡単な解決策１）
･･･元のデータを変えずに，これを参照する別のデータを作る
　例えば，セルE1に =B1と記入し，B14までコピー・貼り付けすると，E列はB列の値と等しくなります．
　次に，セルD1に =A1&"" とし，D14までコピー・貼り付けすると，D列はA列の数値を文字列型に変更したものになります．（=A1によって数値がコピーされ，&演算子によりこれと文字列が結合される結果，全体として文字列型になります．結合する文字列には空文字""を指定します．）
　以上により，D1:E14の範囲を選択して，グラフウィザード→縦棒グラフとすると，D列のデータが横軸ラベルとなります．
　この方法で，さらに完成度の高いものにするには，セルD1に =A1&"以上"&(A1+10)&"未満"とする，あるいは，=A1&"-"&(A1+10)のように文字列を結合させたものを入れるとレポートとして発表できる形になります．（各々の横軸ラベルは，10以上20未満，10-20などとなります．）
（簡単な解決策２）
･･･散布図にしてから，縦棒グラフに変える
　はじめに，グラフの種類として散布図を選ぶと，A列の値をｘ座標，B列の値をｙ座標とする図13のような散布図ができますので，次にこのグラフ上を右クリックして，グラフの種類を変更して縦棒グラフにすると，A列の値を横軸ラベルとする棒グラフができます．
（簡単な解決策３）
･･･図９のような縦棒グラフを作ってから，項目ラベルの追加とデータの削除を行う
　Excel2002では，図９のような縦棒グラフを作ってから，グラフを右クリックして，元のデータ→系列→項目ラベルに使用というところにA2:A14のデータを指定する．さらに，その同じ画面で，系列という項目で水溶液を削除してしまうと目的のグラフとなります．
　Excel2007では，図９のような縦棒グラフを作ってから，グラフを右クリックして，データの選択→横（項目）ラベル：編集というところにA2:A14のデータを指定する．さらに，その同じ画面で，凡例項目 系列（S）という項目で水溶液を選択して，削除すると目的のグラフとなります．
(2)縦棒グラフができてから縦棒の隙間をなくすには，Excel2002では縦棒のどれかを右クリックし，データ系列の書式設定→オプション→棒の間隔を0にします．
Excel2007では縦棒のどれかを右クリックし，データ系列の書式設定で要素の間隔を0にします．
図７
　 A B
1 水溶液(ml) 度数
2 10 4
3 20 7
4 30 3
5 40 11
6 50 16
7 60 0
8 70 6
9 80 1
10 90 1
11 100 2
12 110 0
13 120 2
14 130 1
図８
　 A B
1 血液型 ％
2 A 40
3 B 20
4 AB 10
5 O 30


図９
図10

図11
図12

図13

　右図14は，ある実験で粒子80個の重さを測定した結果だとします．このデータを画面上でドラッグ→右クリック→コピー→Excelワークシート上に単純に貼り付けてください．
　階級幅1.0(g)，第１階級を1.0(g)以上2.0(g)未満として，Excel上で度数分布表とヒストグラムを作るとき，以下の問題に答えてください．
　正しい番号を選択してください．
［問題１］
1.0(g)以上2.0(g)未満の階級に含まれるデータの度数は，次のうちどれですか．

111 212 313 414 515



［問題２］
2.0(g)以上3.0(g)未満の階級に含まれるデータの度数は，次のうちどれですか．

145 246 347 448 549




D E
1 粒子の重さ(g) 度数
2 1.0
3 2.0
4 3.0
5 4.0
6 5.0
7 6.0
8 7.0
9 8.0
［問題３］
図14のデータがA1:A81に貼り付けられているときに，右図のような集計表をD1:E9に準備したとき，Excelの分析ツール／ヒストグラムを利用して階級の境界線としてD列の値を使用すると，粒子の重さが3.0(g)よりも大で4.0(g)以下となる階級に含まれるデータの度数（出力結果の4.0の右に表示される数値）は，次のうちどれですか．

15 26 37 48 59




D E
1 粒子の重さ(g) 度数
2 0.99
3 1.99
4 2.99
5 3.99
6 4.99
7 5.99
8 6.99
9 7.99
［問題４］
図14のデータがA1:A81に貼り付けられているときに，右図のような集計表をD1:E9に準備したとき，Excelのワークシート関数 = FREQUENCY() を利用して階級の境界線としてD列の値を使用すると，粒子の重さが3.99(g)よりも大で4.99(g)以下となる階級（によって求められる4.0(g)以上5.0(g)未満の階級）に含まれるデータの度数（出力結果の4.99の右に表示される数値）は，次のうちどれですか．

12 24 36 48 510



［問題５］
図14のデータから度数分布表を作成しているとき，何らかの都合で元のデータ図14の１つが訂正された場合に（追加・削除はない），訂正されたデータに応じて度数分布表が自動的に更新されるのは次のうちどれですか．

1ワークシート関数 =COUNTIF()で数えている場合
2分析ツールのヒストグラムで数えている場合
3ピボットテーブルで数えてグループ化している場合



［問題６］
次の各々の表で度数の欄に数値が記入されているものとして，これらの表をExcelのグラフウィザードで棒グラフを指定したとき，そのままでは粒子の重さ(g)の欄がヒストグラムの横軸項目ラベルにならないものはどれですか．
ア
粒子の重さ(g) 度数
1.0 ??
2.0 ??
3.0 ??
4.0 ??
5.0 ??
6.0 ??
7.0 ??
8.0 ??
イ
粒子の重さ(g) 度数
1.0(g) ??
2.0(g) ??
3.0(g) ??
4.0(g) ??
5.0(g) ??
6.0(g) ??
7.0(g) ??
8.0(g) ??


ウ
粒子の重さ(g) 度数
1-2 ??
2-3 ??
3-4 ??
4-5 ??
5-6 ??
6-7 ??
7-8 ??
8-9 ??
エ
粒子の重さ(g) 度数
1.0以上2.0未満 ??
2.0以上3.0未満 ??
3.0以上4.0未満 ??
4.0以上5.0未満 ??
5.0以上6.0未満 ??
6.0以上7.0未満 ??
7.0以上8.0未満 ??
8.0以上9.0未満 ??


1ア 2イ 3ウ 4エ



 図14
粒子の重さ(g)
3.5
3.2
4.0
6.5
7.0
3.5
2.2
2.5
2.7
5.0
3.0
3.2
2.7
3.0
3.3
3.0
5.0
3.0
2.4
2.2
2.8
2.5
2.3
2.0
2.0
2.3
2.3
1.9
2.0
3.0
1.9
1.9
2.8
1.8
1.9
2.6
2.7
1.8
2.7
2.5
2.3
2.5
2.8
1.8
1.6
2.0
2.7
2.2
3.4
2.5
2.5
2.7
2.8
4.2
1.7
2.2
1.8
2.3
2.4
1.4
2.8
2.1
2.2
2.5
1.9
1.9
2.2
1.5
1.8
2.6
2.9
2.0
2.5
2.8
2.2
3.0
2.5
2.0
2.3
2.7
...メニューに戻る