Excel講座

度数分布表とクロス集計

本章で用いる実習用ファイル

excelFrequencyTable

一つのカテゴリーで集計することを単純集計と呼ぶ。特にその中でカテゴリーごとのデータの数を集計したものを度数分布表と呼ぶ。

複数のカテゴリーを掛け合わせて集計することをクロス集計と呼ぶ。属性間での傾向を比較するときなどに、クロス集計は用いられる。アンケート調査では必須の集計方法である。今回はExcelでクロス集計する方法を考える。

	A	B	C	D	E
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
全データ

名前定義

セルには以下のように名前を定義している。

グループ: C2:C123
身長: D2:D123
血液型: E2:E123

単純集計

一般的な集計

カテゴリーごとにデータの数や合計・平均値などの統計量を求めるときに条件付き集計を行う。

	G	H	I
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

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二つの関数を用いた配列数式、両方の式を立てよ。

COUNTIFS関数を用いた場合
	G	H

8		人数
9	140	=COUNTIFS(身長,">="&G9,
10	145
11	150
12	155
13	160
14	165
15	170
16	175
17	180
18	185
19

COUNT関数IF関数を用いた場合
	G	H

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.大なり小なりを含む条件)。

クロス集計

クロス集計1
	K	L	M
1	グループ	血液型	人数
2	モーニング娘。	A	21
3	モーニング娘。	B	6
4	モーニング娘。	O	14
5	モーニング娘。	AB	3
6	Berryz工房	A	1

37	BEYOOOOONDS	AB	1
38

グループと血液型という複数のカテゴリーごとの平均値を求めるのがクロス集計である。複数条件の集計を行えば良い。

しかしこの表は(EXCELでの集計はしやすいが)見づらい。そこでカテゴリーをクロスさせた表にするのが一般的である。

クロス集計2
	O	P	Q	R	S	T
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
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

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

	O	P	Q	R	S	T

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

	O	P	Q	R	S	T

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

	O	P	Q	R	S	T

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