== 度数分布表をexcelで作成する方法いろいろ ==

◇ここでは度数分布表Microsoft Excel で作成する方法をいくつか試してみて,所要時間,難易度,間違いを防ぎやすいかどうか,使用上の注意点などをチェックしてみる.
 数百〜数千件のデータがexcelで与えられたものとして,所要時間の目安は30分以内簡単な関数は使うがマクロは使わない程度の作業を想定する. (なお,評価は,筆者の感想 : 作者はExcelを得意としているわけでなく,必要なときに使う程度.マニュアル等を調べ尽くして書いているわけではなく,初心者として試用したときの感想)
◇以下で検討する方法の要約◇

 関数COUNTIF()を利用する方法
所要時間:
難易度:
元データが追加・削除されたときの自動更新:
(ワークシート関数なら自動更新される)
注意点:登場するデータがあらかじめ分かっている必要がある
数値データを階級に分けるのに使うとき:▲
(総合判断)筆者のおすすめ度 ⇒
 関数 FREQUENCY() を利用する方法
所要時間:
難易度:▲
元データが追加・削除されたときの自動更新:
(ワークシート関数なら自動更新される)
データは数値でなければならない(データが文字列のときは困る)
(総合判断)筆者のおすすめ度 ⇒ ▲
 ピボットテーブルを利用する方法
所要時間:▲
難易度:▲
元データが追加・削除されたときの自動更新:▲
データは数値でも文字列でもできる
前もってどんなデータがあるか分からなくても集計できる
(総合判断)筆者のおすすめ度 ⇒
 分析ツールを利用する方法
所要時間:▲
難易度:▲
元データが追加・削除されたときの自動更新:▲
データは数値でなければならない(データが文字列のときは困る)
(総合判断)筆者のおすすめ度 ⇒ ▲

1 関数COUNTIF()の利用

 次の例1のような名前のデータが数百から数千件並んでいて,その各々の件数を度数分布表にしたいとき.

 例えば,例1のデータがA列にあるとき,
(1) C列,D列に次の例の水色部分のように都道府県名,度数の欄をあらかじめ準備する.
(2) D列の赤枠で示したセルに
=COUNTIF($A$2:$A$1000,C2)
と直接記入する
またはメニューから「挿入」→「関数」→「統計」→「COUNTIF」→「OK」→「範囲の右端をクリック」→対話型ボックスが出てきたらA2から下の方へデータのある範囲をドラッグ→エンターキーを押す→ファンクションキーのF4を押す→検索欄の右端をクリック→セルC2をクリック(次の例では神奈川県のあるところ)→エンターキーを押す→「OK」
(3) 次にD2の式をコピーしてD列の下の方まで貼り付ける.
例1
都道府県名
愛知県
大阪府
東京都
東京都
東京都
広島県
兵庫県
秋田県
青森県
・・・
集計表1
都道府県名 度数
神奈川県 9
大阪府 8
山梨県 8
愛知県 5
東京都 5
広島県 5
兵庫県 5
・・・  
例2
余暇の過ごし方
読書
テニス
昼寝
サッカー
ウォーキング
読書
テレビ
買い物
・・・
備考
▲1 [何があるか前もって分からないと集計できない]

 この方法で集計するには,「どんなデータがあるか」が前もって分かっていなければならない.例2のような自由記述式回答を集計するような場合には,すべてのデータをあらかじめ見て,登場する名前を書き出しておく必要があり,大変困難.(自由記述式でデータ件数が非常に多いときに,もれなく・重複なく数え上げたかどうか確かめるのが大変な場合には,この方法は適していない.また,「東京」以外に「tokyo」「東 京」といった別表記がある場合には,当然数え漏れが生じる.)

○2 条件式に数字,文字,セル番地を記入すれば,その値に等しい個数が数えられる

 この方法で,COUNTIF(セルの範囲,条件式) の条件式にセルの番地を代入すると,そのセルの値に等しいデータの個数が返される.
 条件式と表示される内容 
条件式 表示される内容
=COUNTIF(A:A,C2) A列にあるデータのうち,セルC2の値に一致するセルの個数
=COUNTIF(A:A,"東京都") A列にあるデータのうち,東京都という文字列に等しいセルの個数
=COUNTIF(A:A,10) A列にあるデータのうち,10という数に等しいセルの個数
=COUNTIF(A:A,"<10")

▲複数個の条件を入れることはできない
A列にあるデータのうち,10より小さい値のセルの個数
=COUNTIF(A:A,">=10")
▲同上
A列にあるデータのうち,10以上の値のセルの個数
=COUNTIF(A:A,"<="&C2) A列にあるデータのうち,セルC2の値以下のセルの個数
 ※ 「C3の値以下でC2の値より大」のような指定は,文字としての不等号とセル番地を&で文字列の連結をして
=COUNTIF(D2:D16,"<="&C3) - COUNTIF(D2:D16,"<="&C2)
のように2つの式の差で書くことが考えられる.(1つのセルでこの式を作ってコピー・貼り付け)
 ただし,一番小さい階級では引き算は不要.

○3 データが更新されると集計は自動更新される

 データが追加・修正される場合(予備テストのときに集計用テーブルを準備しておいて本番の日に正式データで清書する場合,次年度に引き継がれデータ件数が変わる場合など)に,COUNTIF()の第1引数(セルの範囲)が目的とするデータ全部を含んでいるかどうかが重要となる.もし,データが追加される可能性があれば,「セルの範囲」を指定するときに,上の例のように非常に大きな行番号を指定しておくとか,Aの列見出し(画面上端にある  A  という欄)をクリックして,その列の下端まで全部選択する.直接入力するにはA:A とする.
この場合,当然そのシートで下の方の行には不要なデータがあってはいけない.

○4 セルの範囲には$をつける

 「セルの範囲」を指定するときに,ファンクションキーF4を押してA2:A1000というような範囲を$A$2:$A$1000のような絶対参照に変更しておく.(ドルマークを直接記入してもよい.)このようにしておくと,D3, D4, D, ・・・へ式をコピー・貼り付けしたときに元の範囲が変化しない.(A2:A1000のような相対参照のままで式をコピー・貼り付けすると,範囲が順次変化して,求める集計にならない.)
.
2 関数FREQUENCY()の利用

 次の例3のような数値のデータが数百から数千件並んでいるときに,「区切りとなる値」以下のデータ個数を右側のセルに書き込みたいとき.
(なお,実際には次のG,H欄の階級と度数を表示したものを使う. G2には=C2&"〜"&d2などと文字列を連結して記入,下の列はそのコピー.H2にはE2などと記入)
 例えば,例3のデータがA列にあるとき,
(1) D列に次の例のように区切りとなる値をあらかじめ準備する.
(2) E列の赤枠で示したセルに
=FREQUENCY(A:A,D2:D11)
と記入する.(度数はE2の部分だけ埋まる.)
(またはメニューから「挿入」→「関数」→「統計」→「FREQUENCY」→対話型ボックスがで「データ配列」:A2から下の方へデータのある範囲をドラッグ(またはA列の列見出しをクリック)→「区間配列」:区切りとして準備したD2:D11の範囲をドラッグ→エンターキーを押す→「OK」)
(3) E2からE11の範囲をドラッグして選択→Ctrl+Shiftのまま,表よりも上にあってマウスを近づけて止めると「数式バー」と表示される所をマウスでクリックして,エンターキーを押す.
例3
  A
1 データ
2 10
3 27
4 12
5 63
6 34
7 40
8 92
9 79
10 66
  ・・・

C D E
  区切りとなる値 度数
0 10 3
11 20 3
21 30 3
31 40 7
41 50 8
51 60 2
61 70 5
71 80 4
81 90 4
91 100 1

G H
階級 度数
0〜10 3
11〜20 3
21〜30 3
31〜40 7
41〜50 8
51〜60 2
61〜70 5
71〜80 4
81〜90 4
91〜100 1
 計 40
▲1 [データが数値の場合に使えるが,データが文字列のときには使えない]


○2 区切りとなる値は小さいものから大きなものへ(昇順に)並べる.

 区切りとなる値が1020,・・・となっているとき,10の横に表示されるのは10以下の個数,20の横に表示されるのは,
10<x ≦ 20 となるデータの個数となる.
 整数値からなるデータを,1桁台,10点台,20点台,・・・のように分類したいときは,区切りとする値を,9,19,29,・・・のように書く.(この分類では満点の100があるときに数えもれがないように,最後の階級だけ100にする.)
 小数からなるデータを,10未満,20未満,・・・などと分類するのは難しい.(区切りの値として,9.999,19.999,・・・などと適当な桁で切って指定しても,9.9999が登場すると困る.)

○3 元のデータが更新されると,セルが指定した範囲内にある限り,度数分布表は自動更新される

3 ピボットテーブルの利用

 次の例4例5のように,先頭行にデータのタイトルを付けて,データは下に向かって途切れることなく並べてあるものとする.(空白行があるとその手前までのみ数えられる.)
例4  データが文字列のとき(「次の操作」「前の操作」ボタンで解説を表示)

例5  データが数値のとき(「次の操作」「前の操作」ボタンで解説を表示)

○1 データが文字列でも数値でも使える.

○2 どんなデータがあるか前もって分かっていなくても使える.

3 元のデータが更新されたとき,度数分布表は自動的には更新されないが,ピボットテーブルを選択してから[!]更新アイコンをクリックすれば,度数分布表も更新される.

- - ピボットテーブルを使って集計した値は,集計したときの値になっており,集計した後に元のデータが書き換えられたとき,集計された値(ピボットテーブルによる出力結果)はそのままでは追随しない.ただし,!更新アイコンを押すと再計算される.
※元データを1件消去した場合は「空白」データが1件できたとことになるが,元データを1件追加している場合は,追加されたデータも集計すべきデータの範囲内に含められているかどうか確かめた方がよい.このような場合に,集計ミスが起りやすい.

4 分析ツールの利用

○1 この機能を使うには,「ツール」のメニューに「分析ツール」というサブメニューが表示される必要がある.
(表示されないとき,「ツール」→「アドイン」→「分析ツール」にチェックを入れて「ok」)

▲2 [データが数値の場合に使えるが,データが文字列のときには使えない] 

○3 次の例6で度数を数えるには,数値データの部分と区切りとなる数値に各々タイトルをつけておく.また,区切りとする値によって,その値以下を数えるので,例えば,9,19,29,・・・,89100のように書く.(小さいものから順に並べておく)
4 元のデータが更新されたとき,度数分布表は自動的には更新されない.- - 分析ツールを使って集計した値は,集計したときの値になっており,集計した後に元のデータが書き換えられたとき,集計された値(分析ツールによる出力結果)はそれに追随しない.
例6  「次の操作」「前の操作」ボタンで解説を表示


■簡単なチェック問題
 次のExcelファイル(dosuu1.xls )のA列には「一番好きなスポーツ」のアンケート調査結果,G列には300点満点の得点集計がある.(いずれも架空のもの)
(1) 「一番好きなスポーツ」で最も多かった種目と人数は, 種目:  人数:(人)
(2) 得点が140点以上159点以下の人数は (人)



(携帯版)統計メニューに戻る

...メニューに戻る