■度数分布表,ヒストグラムの作成(Excelの利用)
【例題1】
(解答)右のデータ(図1)は,ある実験を繰り返したときに得られた水溶液の量(単位ml)とします.このデータから,階級幅10ml,第1階級を10ml以上20ml未満として,度数分布表とヒストグラムを作ってください. === ◆度数分布表の作成◆ ===
数学の問題ですが,データ数が多いので転記に労を要しないように,画面上で右の図1をドラッグ→反転表示→マウス右クリック→コピー→Excelのワークシート上で単純に貼り付けとして,データをExcelに取り込み,計算はExcel上でやってください.(列見出し[水溶液(ml)というラベルの欄]1つと数値データ54件の計55行のデータになります.)
以下の操作方法の詳しい解説は,この頁及びこの頁にもあります.データは表2のようにA1に貼り付けられたものとして以下の解説を行います. ■1・・・ワークシート関数 COUNTIF()を利用する方法 (1) 図2のC列のようにあらかじめ階級表を準備しておきます. 階級表は問題の指定に従って,10から始まり階級幅10で埋めておきますが,大きな階級としてどの値まで必要となるか(実際には140)は,データを一瞥するなどの方法で調べておく必要があります.
(2)○ D2のセルに =COUNTIF($A$2:$A$55,">="&C2)-COUNTIF($A$2:$A$55, ">="&C3)と書きこむと,C2の値以上C3の値未満の度数が得られます. ○ 赤字の所を$マーク付きの絶対参照にしておくと,D2の値をD3からD14にコピー・貼り付けするだけで同様の結果が得られます. ![]() ○ 図2右側のような≪結果≫が,D列に表示されればOKです.(D15のセルに式を貼り付けると意味のない式になるので,何も貼り付けておらず,何も表示されません.) ■2・・・ピボットテーブルを利用する方法 この問題のようにデータが小数(実数)値であるときに,ピボットテーブルを利用して度数分布表を作るときは,幾つかの注意点があります. (1)右の図1のデータをExcel上に貼り付けた結果が図2のようになっているとき,この図2から出発してピボットテーブルを利用して度数分布表を作成するためには,セルA1の「水溶液(ml)」という列見出し[ラベル]もピボットテーブルのデータ範囲に含める必要があります.このラベルを含めずに数値データだけをデータ範囲にすると,先頭のデータがラベルと見なされて,データが1件少なくなります. (2)ピボットテーブルを作成したときの既定値では,「値の合計」になりますが,ここで調べたいのは「個数」なので,ピボットテーブルの左上端のボタンをダブルクリックして,右図3のように「データの個数」を選択します. (3)「データの個数」を選択してもデータが小数(実数)値である場合には,一般には同じデータはなく,図4のように異なるデータが1件ずつ数えられているという当然の結果が表示されます. そこで,これを「階級幅10ml」の度数分布表にしたいときは,右図4のように左の欄を右クリック→グループと詳細の表示→グループ化→先頭の値10,末尾の値140,単位10とします.
先頭の値 ⇔ 第1階級の10ml以上という指定
というように対応しています.末尾の値 ⇔ 最後の階級の140ml未満という指定 単位 ⇔ 階級幅 (4)≪結果≫は右図5のようになりますが,次のような特徴があることを頭の片隅に置いておく必要があります.
4.1 一般の度数分布表とは少し異なり,「データがない階級は表示されません」.・・・この問題のデータには,60台の数値,110台の数値は含まれていませんが,ここで行ったようにピボットテーブルのグループ化によって集計するときは,その欄はありませんので,一般に度数分布表としてレポートを文書化するときは,その欄を追加して度数0とします.
4.2 図5のようにまとめた度数分布表においては,第1階級は10ml以上20ml未満を,第2階級は20ml以上30ml未満を表しています.(10-20 | 20-30 | ...などと区切ったときに,日本では「以上-未満」という意味に解釈されるのが普通ですが,これと異なる習慣の読者が含まれると予想されるときは,単に10-20 | 20-30 | ...のまままとめるのでなく,10以上-20未満などと明示した方がよいでしょう.) なお,この例題のデータには,40.0というちょうど境界線上に来る値を入れてありますが,これが正しく,40以上-50未満の階級に入っており,30以上-40未満の階級に入っていないことを確かめておくとよいでしょう. ■3・・・分析ツールのヒストグラムを利用する方法 (1)分析ツールのヒストグラムを利用して度数分布表を作るためには,■1の図2のC列と同様,右図6のように階級の境界線となる表をあらかじめ書きこんでおく必要があります.
○ Excel2007でデータ→データ分析→ヒストグラム(Excel2002では,ツール→分析ツール→ヒストグラム)と進むときに,
(2)分析ツールのヒストグラムで出力される表は,指定した境界線(区切りとする値)以下の個数です.
入力範囲 ⇔ 図1の元データ(図2のA列)
と対応させます.データ区間 ⇔ 図6の境界線の表 ○ このとき「ラベル」欄にチェックを入れる場合は,両方ともラベルが必要となりますので,図6の側にもラベルを付けておく必要があります.(図6のように作った境界線の表で先頭行にラベルを付けていないときは,入力範囲(元のデータの「水溶液(ml)」)も,データ範囲(図6の「境界線」というラベル)のいずれも含めずに指定します.つまり,ラベルは両方とも含めるか,両方とも含めないかのいずれかです.) ○ 図6の境界値は,小さいものから大きいものへ順に並んでいなければなりません.もし準備した境界線よりも大きな値が含まれている場合には,その度数が「次の級」という形で表示されます.
○ この出力結果は,上記の■1,■2の結果と概ね1段ずれていることに注意してください.例えば10の右に表示されているのは10以下の個数です.
○ さらに,この出力結果ではちょうど「区切りとなる値」があるときに,下の階級に含まれることに注意してください.「以上-未満」方式の階級ではなく「よりも大-以下」方式の階級になっているため,右の表中の赤で示したように,40.0というような境界線上のデータは下の階級に含めて数えられ,日本での通常の使い方と合いません. これを避けるためには,境界値として,9.99, 19,99, 29.99, ...のように指定すればよいでしょう.(数学的にはおかしなことですが,小数第1位まで測定するような場合,9.99<x≦10, 19.99<x≦20のような値は登場しないので,1の代わりに0.99を使うとよいことになります.) ■4・・・FREQUENCY()関数を利用する方法 (1)分析ツールのヒストグラムを利用するときと同様に,FREQUENCY()関数を利用にも階級の境界線となる表をあらかじめ書きこんでおく必要があります. (2)図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()を利用して度数分布表を求める場合にも,上記の■3と同様に「よりも大-以下」方式の階級として出力されますので,注意を要します.(9.99, 19.99, .. などの値を境界値とすれば防げます.) |
図1
![]() ![]() ![]()
|
=== ◆ヒストグラムの作成◆ === 作成した度数分布表をもとにして,ヒストグラムを作成します. ヒストグラムとは簡単に言えば縦棒グラフですが,簡単に見えて手ごわい注意点があります.
(1) 右図7のような度数分布表から縦棒グラフを作成すると,A列のデータが数値型であるために,そのままではA列もB列もグラフになってしまい,図9のようにA列の値の不要な縦棒が表れてしまいます.ここでは,A列の値を横軸のラベルに使うにはどうしたらよいのかというのが1つの問題です.
(2) 図8の血液型百分率を縦棒グラフにすると図11のようになり,それぞれの型は別の型であってつながっていないので,縦棒が離れた棒として表示されても変ではありませんが,ヒストグラムは図12のような多数のデータからなる分布を適当な幅で区切って棒にしたものなので,10-20の隣には20-30があるべきで「隙間が空いているはずはありません」.通常,Excelで縦棒グラフを作成すると,図11のように隙間のあるグラフになるものを,図10のように隙間のない縦棒グラフに仕上げるにはどうしたらよいかというのがもう1つの問題です.
(1)図8のようなデータでA1:B5の範囲を選択して,グラフウィザード→縦棒グラフとしたときに,図11のようにA列のデータが横軸ラベルとなるのは,A列のデータが文字列型だからです.これに対して,図7のようなデータでA1:B14の範囲を選択して,グラフウィザード→縦棒グラフとしたときに,図9のようにA列のデータも棒グラフとして表示されてしまうのは,A列のデータが数値型だからです.そこで,図8のデータにおいてA列のデータを文字列型にすれば,B列のデータだけの1種類の縦棒グラフになるはずですが,B列のデータがA列のデータを = COUNTIF()とか =FREQUENCY()のように参照していると,B列のデータが壊れてしまうため出来ない相談になります.
(簡単な解決策1)
(2)縦棒グラフができてから縦棒の隙間をなくすには,Excel2002では縦棒のどれかを右クリックし,データ系列の書式設定→オプション→棒の間隔を0にします.・・・元のデータを変えずに,これを参照する別のデータを作る 例えば,セル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などとなります.) (簡単な解決策2) ・・・散布図にしてから,縦棒グラフに変える はじめに,グラフの種類として散布図を選ぶと,A列の値をx座標,B列の値をy座標とする図13のような散布図ができますので,次にこのグラフ上を右クリックして,グラフの種類を変更して縦棒グラフにすると,A列の値を横軸ラベルとする棒グラフができます. (簡単な解決策3) ・・・図9のような縦棒グラフを作ってから,項目ラベルの追加とデータの削除を行う Excel2002では,図9のような縦棒グラフを作ってから,グラフを右クリックして,元のデータ→系列→項目ラベルに使用というところにA2:A14のデータを指定する.さらに,その同じ画面で,系列という項目で水溶液を削除してしまうと目的のグラフとなります. Excel2007では,図9のような縦棒グラフを作ってから,グラフを右クリックして,データの選択→横(項目)ラベル:編集というところにA2:A14のデータを指定する.さらに,その同じ画面で,凡例項目 系列(S)という項目で水溶液を選択して,削除すると目的のグラフとなります. Excel2007では縦棒のどれかを右クリックし,データ系列の書式設定で要素の間隔を0にします. |
|
右図14は,ある実験で粒子80個の重さを測定した結果だとします.このデータを画面上でドラッグ→右クリック→コピー→Excelワークシート上に単純に貼り付けてください.
階級幅1.0(g),第1階級を1.0(g)以上2.0(g)未満として,Excel上で度数分布表とヒストグラムを作るとき,以下の問題に答えてください.
正しい番号を選択してください.
→5
→3
[問題3]
図14のデータがA1:A81に貼り付けられているときに,右図のような集計表をD1:E9に準備したとき,Excelの分析ツール/ヒストグラムを利用して階級の境界線としてD列の値を使用すると,粒子の重さが3.0(g)よりも大で4.0(g)以下となる階級に含まれるデータの度数(出力結果の4.0の右に表示される数値)は,次のうちどれですか. 15 26 37 48 59 HELP →3
図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 HELP →1
[問題5]
図14のデータから度数分布表を作成しているとき,何らかの都合で元のデータ図14の1つが訂正された場合に(追加・削除はない),訂正されたデータに応じて度数分布表が自動的に更新されるのは次のうちどれですか. 1ワークシート関数 =COUNTIF()で数えている場合 2分析ツールのヒストグラムで数えている場合 3ピボットテーブルで数えてグループ化している場合 HELP ワークシート関数で数えている場合は,データが更新された場合に(再計算をOFFにしていない限り)集計表も更新されますが,分析ツールやピボットテーブルからの出力(書き込み)は,自動的には更新されません. ※ ワークシート関数 =FREQUENCY() を使っている場合にも,自動的に更新されます. →1
[問題6]
次の各々の表で度数の欄に数値が記入されているものとして,これらの表をExcelのグラフウィザードで棒グラフを指定したとき,そのままでは粒子の重さ(g)の欄がヒストグラムの横軸項目ラベルにならないものはどれですか.
1ア 2イ 3ウ 4エ HELP
イ,エは「粒子の重さ(g)」の欄が文字列型なので,そのままで項目ラベルになります.
ウは,1−2=−1などと数値になるのではないかとう疑問もあり得ますが,=1−2のように計算式として記入されていれば数値になりますが,単純に1−2と書かれていれば,1−2という文字列となり,画面にもそのような文字列として表示されています.(もし,計算式なら −1と表示されているはずです). 結局,「粒子の重さ(g)」の欄が数値型になって,そのままでは項目ラベルにならないのはアです. →1
|
図14
|