エクセルの関数

質問

60人位のあるテストの点数で平均を求めた後、平均点以上と平均点以下の集団に分けて、おのおのの平均を求めたいのですが、良い方法はありませんか？平均は、アベレージで求められアベレージの数はわかるのですが、アベレージ以下の人数とかは、求め方がわかりません。宜しくお願いします。

回答No.6

taocat · ベストアンサー

こんばんは。

おかしいですねぇ。ちゃんとテスト済なんですが。

B2:B9にちゃんと点数データは入れてますか？
平均以上の人数の式
=SUMPRODUCT((B2:B9>=AVERAGE(B2:B9))*1)
この最後の、*1　はちゃんと入ってますか？
これがないと、０になります。

新しいブックを開いて
B2:B9に計算し易いきりのいい点数を入れて
C1にでも当方が提示した式をコピーしてみてください
確実に計算されるはずです。

式は手入力すると間違う可能性がありますのであくまでもコピーしてくだされ。

何回も言うようですが、テスト済です。←　くどい！（笑）
以上です。

お礼

ありがとうございました。おっしゃるとおりどこかで間違っていたのですね！式をコピーしたらちゃんと動きました。お手数おかけしました。

回答No.5

taocat

こんばんは。

点数データは、B2:B9　

--------------------------------------------
平均以上の人数

=SUMPRODUCT((B2:B9>=AVERAGE(B2:B9))*1)

平均以上の点数合計

=SUMPRODUCT((B2:B9>=AVERAGE(B2:B9))*B2:B9)

---------------------------------------------
平均未満の人数

=SUMPRODUCT((B2:B9<AVERAGE(B2:B9))*1)

平均未満の点数合計

=SUMPRODUCT((B2:B9<AVERAGE(B2:B9))*B2:B9)

----------------------------------------------

後は割り算ですね。
それから例えば、E2　に全体の平均があるとすると
上記の式の、AVERAGE(B2:B9)　を　E2　に変更。

以上です。

補足

SUMPRODUCTなんて関数があったのですね！教えてもらった通り作ったのですがうまく計算ができません。なぜでしょうか？SUMPRODUCT((B2:B9>=AVERAGE(B2:B9))*1)=0　になってしまいます。

回答No.4

134

sheet1に点数と名前の一覧があるとします。
Ａ列に名前、Ｂ列に点数　の羅列

sheet2の
Ａ１セルに「全体」
Ａ２セルに「平均」と入れ、

Ａ３セルに
=average(sheet1!B2:B61）　　　
で　平均を求めてあるとします。

Ｂ～Ｃ１セルに「平均点以上の集団」と入れ
Ｂ２セルに「平均」　Ｃ２セルに「人数」

Ｄ～Ｅ１セルに「平均未満の集団」と入れ、
Ｄ２セルに「平均」　Ｅ２セルに「人数」
とします。

Ｂ３セルに、
　=average(if(sheet1!b2:b61>=a3,sheet1!b2:b61,""))
と入力して、shiftキーとctrlキーを押しながら、enterを押します。

Ｃ３セルに
　=count(if(sheet1!b2:b61>=a3,sheet1!b2:b61,""))
と入力して、shiftキーとctrlキーを押しながら、enterを押します。

Ｄ３セルに
　=average(if(sheet1!b2:b61<a3,sheet1!b2:b61,""))
と入力して、shiftキーとctrlキーを押しながら、enterを押します。

Ｅ３セルに
　=count(if(sheet1!b2:b61<a3,sheet1!b2:b61,""))
と入力して、shiftキーとctrlキーを押しながら、enterを押します。

こうすると、平均以上の集団、平均未満の集団の平均が出ると思います。

なお、点数の一覧表と同じシートに平均点の計算結果がある場合、条件付き書式の「セルの値が」-「次の値以上」（または「次の値未満」）-平均点を計算したセル番地-書式　　で、　書式を区別すると、平均以上と未満の区分けが明瞭になります。

回答No.3

omusupa

A1=20
A2=50
A3=30
A4=10
以上のように、A1からA4に適当な数字を（適当な点数）を入力してください。

そして、
B2番地に以下の式を入力してください。
=IF(A1>=AVERAGE($A$1:$A$4),"平均以上","平均以下")
そして、B4までコピーをします。

上記までで、その点数が、平均以上のものか、平均以下のものかを分けることができますので、

あとは、平均以上か平均以下と入っている列を並べ替えをしてしまって、個々に平均を求めるか

もしくは、並べ替えをしてはいけないのであれば、

D1に「平均以下」と入力
D2に「平均以上」と入力して、

E1に　=SUMIF(B1:B4,D1,A1:A4)/COUNTIF(B1:B4,D1)
E2に　=SUMIF(B1:B4,D2,A1:A4)/COUNTIF(B1:B4,D2)
と入力できれば、平均以下／以上の平均を求めることができると思います。

ちなみに、SUMIFで平均以上だけの点数を合計し、COUNTIFで平均以上の数を数えて、
あとは、個々を割って、平均を求めるという方法です。

参考になれば。

回答No.2

BLUEPIXY

全体の平均点をどっかのセルに求めて置いてSUMIFとCOUNTIFを使えば良いと思います。
A1:A60が点数で、全体の平均がC1にある時、
平均点より大きい集団の平均
=SUMIF(A1:A60,">"&C1,A1:A60)/COUNTIF(A1:A60,">"&C1)

お礼

OKです。SUMIF・COUNTIFを使用して計算ができました。">"&がみそですね！気がつきませんでした。
ありがとうございます。

回答No.1

kaiu

とりあえずどこかのセルに
アベレージの値を固定で取って置いて
＜や＞でtrue/falseの値を
取得すればいかがですか？

補足

早速の回答ありがとうございます。
色々なテストの点数を集計使用としてるのでアベレージが変化しても追従できるような計算式を作りたいのですが具体的にどんな式にすればよいでしょうか？