人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

Excelに関する質問です。

A列に氏名、
B列に点数。
C列にクラス名がズラーっとが書いてある。

偏差値を出すにあたって、全クラスで出すのは簡単なのですが、クラス別に偏差値出すにはどのようにしたら良いでしょうか?
SUMPRODUCT関数などを使ってみましたがうまく行きません。
よろしくお願いします。

1394453023
●拡大する

●質問者: lunchtei
●カテゴリ:コンピュータ インターネット
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● bg5551
●1ポイント

オートフィルターをかけてSTDEVP関数で求められます。

http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter.htm
偏差値関数
http://www.wanichan.com/pc/excel/2010/4/page03.html


lunchteiさんのコメント
ありがとうございます。 もちろん1クラス1クラス「オートフィルタ」を使えば良いのですが、数百クラスあったりとかしたら対応出来ませんので、一つの式で表現したいと言うことです。

bg5551さんのコメント
このようなマクロを使った物はだめでしょうか。 http://www.vector.co.jp/soft/win95/business/se482811.html

2 ● きゃづみぃ
●10ポイント

=(B2-AVERAGE(IF(C:C="1組",B:B,"")))/STDEV(IF(C2:C20="1組",B2:B20,""))*10+50

こんな感じかな?

数式を入れてから CTRL+SHIFT+ENTERとしてやってください。

C2:C20などの範囲は 適宜変更してください。


きゃづみぃさんのコメント
最初のB2が 求めたい人の点数です。

lunchteiさんのコメント
添付表で、例えば点数をクラス内で順位を付ける場合は =SUMPRODUCT(($C$2:$C$2000=C3)*($B$2:$B$2000>B2))+1 でクラス内で順位を付けることが出来ます。 これをクラス内で偏差値を出したい場合、SUMPRODUCT関数では出来ないので何か適当な関数でもあれば教えて頂きたいと言うことです。 口足らずですいません。

lunchteiさんのコメント
"=SUMPRODUCT(($C$2:$C$2000=C3)*($B$2:$B$2000>B2))+1" すいません。式がうまくアップ出来ないようです。

きゃづみぃさんのコメント
>|| =SUMPRODUCT(($C$2:$C$2000=C3)*($B$2:$B$2000>B2))+1 ||< SUMPRODUCTでは できないと思いますよ。

lunchteiさんのコメント
やはりそうですよね。 なにかいい関数ないでしょうかね。 別件ですがどのようにして式をアップされたのでしょうか?

きゃづみぃさんのコメント
>|| =(B2-AVERAGE(IF(C:C="1組",B:B,"")))/STDEV(IF(C2:C20="1組",B2:B20,""))*10+50 ||< これでは ダメなのかな? というか 使えない? ちなみに 上記の箇所は はてな記法を用いています。 偏差値の求め方は 「(偏差値を求めたい得点-平均点)/標準偏差 × 10 + 50」 ですよね? それを 1組という条件を入れて ただ単に 数式にしただけですが。 なので SUMPRODUCTを使う必要は ないと思いますが、 無理矢理使ったほうがいいのでしょうか?

きゃづみぃさんのコメント
ちなみに CTRL+SHIFT+ENTER が キモですから。

lunchteiさんのコメント
ありがとうございます。クラスが数百以上ある場合の対応が困難です。はてな記法勉強してみます。

きゃづみぃさんのコメント
別に 数百以上 あっても 問題ないですけどね。 途中の"1組"は C2とかに変えて、最初のセルに貼り付けたものを コピーして 他のセルに貼り付ければいいだけです。

3 ● rsc
●189ポイント ベストアンサー

まず、「氏名、点数、クラス、偏差値」の右側に、「クラス平均、点数2乗、クラス標準偏差」の項目を追加します。
次に、2行目を以下の通りにします。(^_^;
・クラス平均
=SUMIF($C$2:$C$16,C2,$B$2:$B$16)/COUNTIF($C$2:$C$16,C2)
・点数2乗
=B2*B2
・クラス標準偏差
=SQRT(SUMIF($C$2:$C$16,C2,$F$2:$F$16)/COUNTIF($C$2:$C$16,C2)-E2^2)
・偏差値
=(B2-E2)/G2*10+50

※参考URL
http://d.hatena.ne.jp/rsc96074/20100726/1280131745
http://www.relief.jp/itnote/archives/001029.php
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1469894094


lunchteiさんのコメント
ありがとうございます。会社出たので帰宅後にやってみます。

lunchteiさんのコメント
これは素晴らしい。文句ありません。 ありがとうございました。
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ