度数分布表とクロス集計
- 本章で用いる実習用ファイル
一つのカテゴリーで集計することを単純集計と呼ぶ。特にその中でカテゴリーごとのデータの数を集計したものを度数分布表と呼ぶ。
複数のカテゴリーを掛け合わせて集計することをクロス集計と呼ぶ。属性間での傾向を比較するときなどに、クロス集計は用いられる。アンケート調査では必須の集計方法である。今回はExcelでクロス集計する方法を考える。
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | No | 名前 | グループ | 身長 | 血液型 | |
2 | 1 | 中澤裕子 | モーニング娘。 | 158 | O | |
3 | 2 | 石黒彩 | モーニング娘。 | 160 | A | |
4 | 3 | 飯田圭織 | モーニング娘。 | 167 | A | |
5 | 4 | 安倍なつみ | モーニング娘。 | 152 | A | |
6 | 5 | 福田明日香 | モーニング娘。 | 149 | B | |
122 | 121 | 山﨑愛生 | モーニング娘。 | 159 | B | |
123 | 122 | 橋迫鈴 | アンジュルム | 152 | O | |
124 | ||||||
全データ |
単純集計
一般的な集計
カテゴリーごとにデータの数や合計・平均値などの統計量を求めるときに条件付き集計を行う。
G | H | I | J | ||
---|---|---|---|---|---|
1 | 血液型 | 人数 | 平均身長 | ||
2 | A | 52 | 158.20 | ||
3 | B | 22 | 157.59 | ||
4 | O | 40 | 157.69 | ||
5 | AB | 8 | 156.38 | ||
6 | 全血液型 | 122 | 157.80 | ||
7 |
課題:単純集計(形式)
セルH2,H6,I2,I6に式を立て、残りはオートフィルを用いて表を完成させよ。
度数分布表
カテゴリーは質的データを使うのが一般的だが、身長のような量的データを使うこともできる。その際は適当な幅で区切ってカテゴリー化する。このときの区切りをビンと呼ぶ。
今回はビンを140(cm)を最小に、5(cm)区切りで設定した。
140-145 | 145-150 | 150-155 | 155-160 | 160-165 | 165-170 | 170-175 | 175-180 | 180-185 |
140-145の区切りは
- 140以上150未満
- 140より大きく150以下
という二つの方式があるが、日本では一般的に「140以上150未満」とするのが一般的なので、そちらを採択する。
G | H | I | ||
---|---|---|---|---|
8 | 人数 | |||
9 | 140 | 0 | ||
10 | 145 | 3 | ||
11 | 150 | 35 | ||
12 | 155 | 41 | ||
13 | 160 | 30 | ||
14 | 165 | 11 | ||
15 | 170 | 1 | ||
16 | 175 | 0 | ||
17 | 180 | 1 | ||
18 | 185 | 0 | ||
19 |
セルH9には「身長>=140(F9)」かつ「身長<145(F10)」の条件に当てはまる「身長」のデータ数を求める式を入力する。
この度数分布表をグラフにしたものがヒストグラムである。
課題:度数分布表(形式)
COUNTIFS関数およびCOUNT,IF二つの関数を用いた配列数式、両方の式を立てよ。
G | H | I | ||
---|---|---|---|---|
8 | 人数 | |||
9 | 140 | =COUNTIFS(身長,">="&G9, | ||
10 | 145 | |||
11 | 150 | |||
12 | 155 | |||
13 | 160 | |||
14 | 165 | |||
15 | 170 | |||
16 | 175 | |||
17 | 180 | |||
18 | 185 | |||
19 |
G | H | I | ||
---|---|---|---|---|
20 | 人数 | |||
21 | 140 | =COUNT(IF((身長>=G21)*( | ||
22 | 145 | |||
23 | 150 | |||
24 | 155 | |||
25 | 160 | |||
26 | 165 | |||
27 | 170 | |||
28 | 175 | |||
29 | 180 | |||
30 | 185 | |||
31 |
途中まで書いてあるので、その続きを考えよ(cf.大なり小なりを含む条件)。
クロス集計
K | L | M | N | ||
---|---|---|---|---|---|
1 | グループ | 血液型 | 人数 | ||
2 | モーニング娘。 | A | 21 | ||
3 | モーニング娘。 | B | 6 | ||
4 | モーニング娘。 | O | 14 | ||
5 | モーニング娘。 | AB | 3 | ||
6 | Berryz工房 | A | 1 | ||
37 | BEYOOOOONDS | AB | 1 | ||
38 |
グループと血液型という複数のカテゴリーごとの平均値を求めるのがクロス集計である。複数条件の集計を行えば良い。
しかしこの表は(EXCELでの集計はしやすいが)見づらい。そこでカテゴリーをクロスさせた表にするのが一般的である。
O | P | Q | R | S | T | U | ||
---|---|---|---|---|---|---|---|---|
1 | A | B | O | AB | 計 | |||
2 | モーニング娘。 | 21 | 6 | 14 | 3 | 44 | ||
3 | Berryz工房 | 1 | 2 | 5 | 0 | 8 | ||
4 | ℃-ute | 4 | 1 | 2 | 1 | 8 | ||
5 | アンジュルム | 10 | 2 | 5 | 1 | 18 | ||
6 | Juice=Juice | 4 | 1 | 4 | 0 | 9 | ||
7 | カントリー・ガールズ | 1 | 2 | 3 | 0 | 6 | ||
8 | こぶしファクトリー | 2 | 3 | 2 | 1 | 8 | ||
9 | つばきファクトリー | 1 | 4 | 3 | 1 | 9 | ||
10 | BEYOOOOONDS | 8 | 1 | 2 | 1 | 12 | ||
11 | 計 | 52 | 22 | 40 | 8 | 122 | ||
12 |
課題:グループ・血液型のクロス集計(形式)
セルP2に条件付き集計を行う式を立て、オートフィルでセルS10まで求めよ。S列および11行は合計(SUM)せよ。
O | P | Q | R | S | T | U | ||
---|---|---|---|---|---|---|---|---|
1 | A | B | O | AB | 計 | |||
2 | モーニング娘。 | |||||||
3 | Berryz工房 | |||||||
4 | ℃-ute | |||||||
5 | アンジュルム | |||||||
6 | Juice=Juice | |||||||
7 | カントリー・ガールズ | |||||||
8 | こぶしファクトリー | |||||||
9 | つばきファクトリー | |||||||
10 | BEYOOOOONDS | |||||||
11 | 計 | |||||||
12 |
前述の通り、複数条件の集計を行えば良いのだが、条件指定が行方向(グループ名)と列方向(血液型)に分かれている点に注意が必要である。九九表を応用すること。
課題:身長・血液型のクロス集計(形式)
身長を5cmで区切ったものと血液型を人数で集計したクロス集計表を作成せよ。
O | P | Q | R | S | T | U | ||
---|---|---|---|---|---|---|---|---|
13 | A | B | O | AB | 計 | |||
14 | 140 | 0 | 0 | 0 | 0 | 0 | ||
15 | 145 | 1 | 1 | 1 | 0 | 3 | ||
16 | 150 | 11 | 7 | 15 | 1 | 35 | ||
17 | 155 | 20 | 8 | 8 | 5 | 35 | ||
18 | 160 | 13 | 4 | 12 | 1 | 41 | ||
19 | 165 | 6 | 1 | 4 | 0 | 11 | ||
20 | 170 | 1 | 0 | 0 | 0 | 1 | ||
21 | 175 | 0 | 0 | 0 | 0 | 0 | ||
22 | 180 | 0 | 1 | 0 | 0 | 1 | ||
23 | 185 | 0 | 0 | 0 | 0 | 0 | ||
24 | 計 | 52 | 22 | 40 | 8 | 122 | ||
25 |