Excel Maniacs

相関分布表を作る第３弾・・・【問題】

(●`ε´●)：第１弾が区間が同じ、かつ区間に幅無し。

　　　　　　第２弾が区間が同じ、かつ区間に幅あり。

　　　　　　で、第３弾は？

【´･ω･`】:第３弾は区間が異なり、区間に幅あり・・・。

【問題】

英語の得点と５教科の得点の相関関係分布表を作ります。

英語の得点は0から100の整数値です。５教科の得点は０から５００の

整数値です。相関分布表のレイアウトは下を参考にしてください。

*

*

*

*

*

なおデータは4２人分で2行目からP列に５教科のと句点、Q列に英語の得点が

入力されています。

５教科	英語
415	81
261	67
199	39
422	94
284	76
377	81
489	99
345	75
124	22
322	80
74	16
114	14
36	0
446	96
500	100
353	73
456	88
156	30
263	57
276	58
255	59
431	89
263	49
440	92
329	69
265	57
286	62
373	67
475	97
321	75
215	31
347	81
348	74
402	82
339	79
348	86
348	84
246	54
412	92
197	45
372	86
124	26

※三四郎、Excelの解答作成用ファイルを用意しました。必要ならば

　Mihon002.lzhをダウンロード してください。

※縦横の計には予め、SUM関数が入っているので、その部分は

考慮する必要はない。D4からM13に入れる式を答えればよい。

【´･ω･`】：・・・なんて問題をだそうと思っていたんだけど・・・

　　　　　○○さんの第２弾の解答そのままでなんとかなっちゃうんだよね。

　　　　　でも、せっかく第３弾まで問題を考えていたから、出すね。

※コメント、解答の書き込みは即OKです。

相関分布表を作る第２弾・・・【解答】

問題はこちらを参照。

【解答】

D4に　・・・

=DCount($P$1:$Q$43,1,{{"数学","=<" & Left(D$3,Find("-",D$3)-1)},{"数学",">=" & Mid(D$3,Find("-",D$3)+1,2)},{"英語","=<" & Left($C4,Find("-",$C4)-1)},{"英語",">=" & Mid($C4,Find("-",$C4)+1,2)}})

・・・と入力。そしてM13までコピペ。(悪児さんの指摘により、一部修正）

【´･ω･`】:このように複数条件による集計が標準関数で簡単にできてしまうところが

　　　　　三四郎の素晴らしいところだな。おそらく1999年発売の「三四郎９」から

　　　　　（あるいはもっと前から）可能だったんだと思う。

(●`ε´●):Excelだとこの手のことをやろうとすると配列数式に頼ったりして、ちょっと

　　　　　　初心者には厳しいところがあるが、三四郎だと条件式を単純に並べるだけで

　　　　　　出来てしまう。おいらはもともとはEXCEL派なんだけどこの辺りの所は

　　　　　　三四郎の良さを素直に認めざるを得ない。

【´･ω･`】:ExcelではCountIf関数やSumIf関数は複数条件には対応していなかった。

　　　　　　複数条件に対応したCountIfs関数やSumIfs関数が登場したのは

　　　　　　EXCEL２００７になってからだからね。

　　　　　　JustSystemさん、もっと宣伝上手になろうよ。

　　　　　　　　さてＥｘｃｅｌでの解答だけど作業列無しの一発解答はエクセルバカさんが

　　　　　　凄いのを出してきそうなので敢えて作業列ありのやつを提示しよう。

【Ｅｘｃｅｌ番外編】

１．適当な場所に下のような変換テーブルを作る。この場合はＤ１７：Ｅ１７。

得点	行・列番号
0	13
10	12
20	11
30	10
40	9
50	8
60	7
70	6
80	5
90	4

２．セルＲ２に　=VLOOKUP(P2,$D$18:$E$27,2)　と入力。Ｓ４３までコピペ。

３．セルＴ２に　=R2*100+S2　と入力。Ｔ４３までコピペ。

４．Ｄ４に　=COUNTIF($T$2:$T$43,COLUMN(D4)*100+ROW(D4))　と入力。

　　M１３までコピペ。

相関分布表を作る第２弾・・・【問題】

【´･ω･`】:この辺りの問題になってくるとかなり、ハイレベルでExcelでやるにしても

　　　　　けっこう、歯ごたえがあるとおもう。

(●`ε´●)：ま、普通は作業列を使った処理を考えるはな・・・。

【´･ω･`】:世の中には普通じゃない人もいるから・・・。

【問題】

英語と数学の２教科の得点の相関関係分布表を作ります。

得点は0から100の整数値です。相関分布表のレイアウトは下を参考にしてください。

*

*

*

*

*

なおデータは4２人分でP列に数学、Q列に2行目から英語の得点が

入力されています。

数学	英語
86	81
30	67
41	39
70	94
28	76
67	81
96	99
60	75
29	22
41	80
13	16
36	14
18	0
79	96
100	100
67	73
96	88
33	30
46	57
51	58
39	59
82	89
58	49
82	92
61	69
47	57
50	62
86	67
92	97
48	75
61	31
52	81
63	74
78	82
51	79
45	86
48	84
42	54
68	92
31	45
57	86
23	26

尚、正解のイメージは下図である。

英語	数学
	評定	100-90	89-80	79-70	69-60	59-50	49-40	39-30	29-20	19-10	9-0	計
	100-90	3	1	2	1	0	0	0	0	0	0	7
	89-80	1	2	1	1	2	3	0	0	0	0	10
	79-70	0	0	0	3	1	1	0	1	0	0	6
	69-60	0	1	0	1	1	0	1	0	0	0	4
	59-50	0	0	0	0	1	3	1	0	0	0	5
	49-40	0	0	0	0	1	0	1	0	0	0	2
	39-30	0	0	0	1	0	1	1	0	0	0	3
	29-20	0	0	0	0	0	0	0	2	0	0	2
	19-10	0	0	0	0	0	0	1	0	1	0	2
	9-0	0	0	0	0	0	0	0	0	1	0	1
	計	4	4	3	7	6	8	5	3	2	0	42

※三四郎、Excelの解答作成用ファイルを用意しました。必要ならば

　MIhon001.lzhをダウンロード してください。

※縦横の計には予め、SUM関数が入っているので、その部分は

考慮する必要はない。D4からM13に入れる式を答えればよい。

※解答は8/18（土）発表予定。