■Excelを用いた度数分布表の作成[2]  FREQUENCY関数の利用(含む:分析ツール→ヒストグラム)

■ はじめに

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

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

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

 ここでは、ほとんどのパソコンにインストールされているMicrosoft Excelを用いて度数分布表を作成する方法を解説します。操作画面はExcel2007で解説します。(他のバージョンでもほとんど同じです。)
表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
■1 ワークシート関数FREQUENCY()を用いる方法

 Excelのワークシート関数FREQUENCY()は、検索範囲と区切りとなる階級値の組が与えられたときに、各々の階級に入るデータの個数(度数)を返す関数です。この関数を正しく利用するには、この関数の特徴についてかなり正確に理解している必要があります。
(*)右の表1のような文字列データ(行ラベル(=生徒番号)が名前であるのは構わないが、通学手段の度数を調べたいときに、この通学手段というデータ(2列目に書かれているデータ)が「バス」などの文字列で記入されているもの)であるときは、FREQUENCY()は利用できません。COUNTIF()を利用する方法など他の頁を見てください。
(*)右の表2,表3のような整数値または実数値の数値データを幾つかの階級に分類したときに、各階級に入る度数を返す関数ですが、FREQUENCY()を利用するためには、ユーザがあらかじめ「階級を区切りを示す数値表」を作成しておかなければなりません。

 表2はサイコロを30回投げたときの出た目の回数を記録した生データとします。

○ 表2のような整数の数値データからなる生データをFREQUENCY()を使って集計するには:
(1)あらかじめ表2Aのように階級表を作っておきます。
○ この階級表は、区切りとなる値(出た目の欄:縦に並べる)が昇順(小さいものから大きいものへ)に並んでいなければなりません。
  A B C D E
1 試行回数 出た目   出た目 回数
2 No.1 2   1 9
3 No.2 6   2 4
4 No.3 1   3 4
5 No.4 1   4 4
6 No.5 4   5 6
7 No.6 5   6 3
     
31 No.30 1      
(2)  FREQUENCY()関数を利用するには、右の例では表の  で示したセルE2に =FREQUENCY()を書き、そのデータ配列に  で示したセルB2:B31を指定し、区間範囲に  で示したD2:D7を指定します。

E2に =FREQUENCY(B2:B31,D2:D7) と書きます。

”ここから先が非常に難しい!!”
E3からE7には、E2の数式をコピー・貼り付けしても駄目です。
FREQUENCY()関数は、「配列」(順序を問題にした値の組)として利用しなければならないので、上記のように1つ作ったE2の式を「E2からE7までの配列」にしなければなりません。そのためには、
(I)まず、E2をポイントしておく。
(II)次に、E2〜E7までドラッグして反転表示にする。
(III)Excel画面上端にある数式バー(A,B,Cなどの列見出しの上の白い空白部分=E2をポイントしてときは、=FREQUENCY(B2:B31,D2:D7)と書いてあるのが見える場所)をポイントして、Ctrl+Shift+Enter(CtrlキーとShiftキーを押しながら、Enterキーを1回押す)とします。
○表2A(再掲)
出た目 回数
1  
2  
3  
4  
5  
6  

※ FREQUENCY()関数の階級区切りは160.0x<165.0のように使用され、我々には非常に使いにくいものとなっています。しかし、(以下は憶測にすぎませんが)これは欧米文化と日本文化の好みの違いなのかもしれません。例えば、サッカーでU19、U23という場合、各々19歳以下、23歳以下の選手というように区切りとなっている値が下の階級に含まれています。
FREQUENCY()関数で使われている区切り値:19歳以下、23歳以下など
日本人好みの区切り値:20歳以上、25歳以上など
 Excelは欧米人好みのソフトなのかなと軽く受けめて、区切りの値の処理を間違わないように気をつけることが重要




※左の表のような整数の数値データをFREQUENCY()関数で集計する場合には、E3に返された4という数字は1<x2の範囲に入るデータの個数を表しことになりますが、元のデータB2〜B31は整数値なのでこの不等号はまったく働いておらず、 =2 という皮1枚だけでつながっていることになります。
 同様にして、E7のセルは5<x6の範囲に入るデータの個数すなわち =6 となる個数を表しています。
 これに対して、E2のセルはその前のデータがありません(「出た目」という文字列が書かれていて、数値として評価できるものがありません)。このような区切り値の下端では単に、「その値以下」となる個数が返されるようです。
○ 表3のような実数(小数)の数値データからなる生データをFREQUENCY()を使って集計するには:
(1)あらかじめ表3Aのように階級表D2〜D8を(縦に)作っておきます。
○ この階級表は、区切りとなる値(D列)が昇順(小さいものから大きいものへ)に並んでいなければなりません。

○ この階級表は、最終的なレポートなどにおいて右の表3完成型のように書きたくても、FREQUENCY()を使う段階では表3完成型のように書くと使えません。(「160〜」や「160.0x<165.0」には数字以外のもの(〜や≦,<)が書かれており、このようなセルは数値としては使用できません。このため第1段階でD列の数値データを階級値としてE列に集計し、第2段階で別の列に =E2 のように「値の参照」で自動的に転記することを考えます。)

○【重要:ここが最大の山場】
 右の表3完成型を作るためには、表3Bではなく、表3Aのように集計しなければなりません。
 というのは、FREQUENCY()関数は「前の値よりも大きくその値以下となる個数を返す」特徴があるので、表3B表3AのようにD列を記入したとき、
→ 表3BではE2のセルにx160となる個数が入り、E3のセルに160<x165となる個数が入るため、表3完成型に転記したとき間違った数え方になります。
 このようにFREQUENCY()関数には「…よりおおきい…以下」の形で数える特徴があるので、FREQUENCY()関数を使うためにはD列の書き方を工夫します。(※右欄参照)
→ D列に表3Aのように書くと、E列に160.0x<165.0の個数が入ります。

【階級表の書き方:要点】 D列には、完成型として予定する階級の上端よりも1目盛り小さい数をその桁数まで書く
表3Bの数え方でE2に入る値 表3Bの数え方で
E3に入る値
E4
159.9 160.0 160.1 164.9 165.0 165.1 169.9 170.0
表3Aの数え方で
E2に入る値
E3 E4
(2)  FREQUENCY()関数を利用するには、上記の整数の場合と同様、セルE2に =FREQUENCY()を書き、そのデータ配列にB2:B51を指定し、区間範囲にD2:D8を指定します。

⇒ E2に =FREQUENCY(B2:B51,D2:D7) と書きます。

E3からE8には、E2の数式をコピー・貼り付けしても駄目です。
FREQUENCY()関数は、「配列」(順序を問題にした値の組)として利用しなければならないので、上記のように1つ作ったE2の式を「E2からE8までの配列」にしなければなりません。そのためには、
(I)まず、E2をポイントしておく。
(II)次に、E2〜E8までドラッグして反転表示にする。
(III)Excel画面上端にある数式バー(A,B,Cなどの列見出しの上の白い空白部分=E2をポイントしてときは、
=FREQUENCY(B2:B51,D2:D8)と書いてあるのが見える場所)をポイントして、Ctrl+Shift+Enter(CtrlキーとShiftキーを押しながら、Enterキーを1回押す)とします。
(3) 表をレポートなどに使う形にするには:
 G列などに階級表を表3完成型の形で書き、H2に =E2 という形で値の参照式を書けばOKです。H3以下は式の単純コピー・貼り付けでできます。
× 表3B
  A B C D E
1 生徒番号 身長   階級 人数
2 No.1 175.1   160  
3 No.2 182.1   165  
4 No.3 167.0   170  
5 No.4 176.7   175  
6 No.5 168.4   180  
7 No.6 167.8   185  
  190  
51 No.50 173.3      

○ 表3A
  A B C D E
1 生徒番号 身長   階級 人数
2 No.1 175.1   164.9  
3 No.2 182.1   169.9  
4 No.3 167.0   174.9  
5 No.4 176.7   179.9  
6 No.5 168.4   184.9  
7 No.6 167.8   189.9  
  194.9  
51 No.50 173.3      
表3完成型
階級 人数
160.0〜  
165.0〜  
170.0〜  
175.0〜  
180.0〜  
185.0〜  
または
階級 人数
160.0x<165.0  
165.0x<170.0  
170.0x<175.0  
175.0x<180.0  
180.0x<185.0  
185.0x<190.0  


※ 数学的に考えると、E3のセルに165.0x<170.0となる個数を入れるためには、D2、D3のセルには164.9999···169.9999···のように書きこまなければならないように思われますが、コンピュータ上の表計算ソフトでは小数は有効数字15桁程度の数字しか扱えません。また、実際に我々が扱うデータは160.3 , 160.07のように小数第何位までで一切られた数になっています。そこで、その桁数で1目盛り小さい数を書けば目的を達成できます ⇒ E3のセルに165.0x<170.0となる個数を入れるためには、D2、D3のセルには164.9169.9と書く。気味が悪いと思えば、164.99169.99でももちろんOKです。
 例えば小数第1位まで書かれたデータでは、165.0x<170.0となる数字は、165.0, 165.1 , 165.2 , ··· , 169.8, 169.9 しか登場しないからです。
(一見手品のように見えますが、「東京図書版の数学B」の教科書にも、これとほぼ同じ考え方が紹介されています。)
(参考1)
○ この頁で解説したFREQUENCY()関数による度数分布表の作成は、Excelの「分析ツール」からも行うことができます。この場合の階級表作成上の注意点は、この頁で述べたFREQUENCY()関数の特徴と全く同じです。
 Excel2002では、「ツール」→「分析ツール」→「ヒストグラム」→「入力範囲に生データの数値のみの範囲(1列分だけ)」を(列ラベルも含めるときは「ラベル」にチェック),「データ区間に階級値の範囲」を指定します。Excel2007では、「データ」→「データ分析」→「ヒストグラム」以下同様です。
 ただし、FREQUENCY()関数による方法は、ワークシート関数を用いているので参照している生データの値が変更された場合でも、度数分布表は自動的に再計算されます。(Excelの初期設定で、再計算するようになっているとき。なお、FREQUENCY()関数の引数から分かるように、データ数が増減されるときは集計範囲は自動的には更新されません。)
 これに対して、「分析ツール」を用いた「ヒストグラム」の作成では、作成時点での生データに対する度数分布が書きこまれるますので、生データの値が変更された場合には、度数分布表は自動的には再計算されません。
(参考2)
○ ヒストグラムと棒グラフ
 表3のように「理論上は連続的に分布していると考えられる実数値のデータ」を幾つかの階級に分けてできる「度数分布表」をグラフにするときは、通常次の図のように「ヒストグラム」と呼ばれる柱状のグラフで表されます。このような「ヒストグラム」では通常、縦棒の間に隙間がないグラフが用いられます。(値や階級がつながっているという雰囲気がよく出る。)
   これに対して、いわゆる「棒グラフ」は次の例のように、縦棒の間に隙間があるグラフが用いられるのが普通です。(このように表すとA型とB型などがつながっていないことがよく分かります。また、ヒストグラムでは度数以外に「横軸も数値」であるのに対して、血液型の例では「横軸は名前のラベル」になっている点が違います。)
≪練習用の問題≫ 次のデータから度数分布表を作ってください。
 表Iは表Iの2の分類に従って度数分布表にしてください。(この表はCOUNTIF()を利用した度数分布表の問題と同じです。2つの方法で行うと、比較・点検できます。)
 表IIは160.0x<165.0,165.0x<170.0,…のように5.0の階級幅で数えてください。
 データの取り込み方:キーボードから入力しなくても、画面上で下向きにドラッグ・コピーし、Excel上に単純に貼り付けるとExcelデータになります。(単純なテキストデータなので、安全に取り込むことができます。)

表I 表Iの2 表II
卵の標本番号 重さ(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未満
生徒番号 身長
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


■読み終わったら→ ここ ←をクリック■
≪解答例≫

表Iの問題
階級 度数   規格 重さ(g) 度数
45.9 13   SS 40以上46未満 13
51.9 9   S 46以上52未満 9
57.9 8 ⇒  MS 52以上58未満 8
63.9 14   M 58以上64未満 14
69.9 16   L 64以上70未満 16
75.9 0   LL 70以上76未満 0

表IIの問題
階級 度数   階級 度数
164.9 4   160.0≦x<165.0 4
169.9 10   165.0≦x<170.0 10
174.9 13 170.0≦x<175.0 13
179.9 7   175.0≦x<180.0 7
184.9 16   180.0≦x<185.0 16
189.9 0   185.0≦x<190.0 0
194.9 0   190.0≦x<195.0 0
○===メニューに戻る