Excel Maniacs

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

問題はこちらを参照。

【´･ω･`】:まずは三四郎版から・・・・

【解答】

D4に・・・

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

・・・と入力。M１３までコピペ。

(●`ε´●):なんだ、第2弾と変わらないじゃないか？！

【´･ω･`】:うん、同じやり方。基本に忠実・・・。次はエクセル版・・・。

【解答・番外Excel版】

１．適当な場所に２つの区間の変換テーブルをつくる。

　　この場合は５教科がC18:D27,英語がF18:G２７。

　　

5教科			英語
得点	列番号		得点	行番号
0	13		0	13
50	12		10	12
100	11		20	11
150	10		30	10
200	9		40	9
250	8		50	8
300	7		60	7
350	6		70	6
400	5		80	5
450	4		90	4

２．R2に　=VLOOKUP(P2,$C$18:$D$27,2)　と入力。R43までコピペ。

３．S2に　=VLOOKUP(Q2,$F$18:$G$27,2)　と入力。S43までコピペ。

4．T2に　=R2*100+S2　と入力。T43までコピペ。

5．D4に　=COUNTIF($T$2:$T$43,COLUMN(D4)*100+ROW(C4))　と入力。M13までコピペ。

【´･ω･`】:エクセル版の解答ということでこれを紹介したが、エクセル特有の機能を

　　　　　使っているわけではないので、このやり方は表計算ソフト一般で通用するはずだ。

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

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

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

　　　　　　で、第３弾は？

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

【問題】

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

英語の得点は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１３までコピペ。