エクセルで範囲指定をして,範囲内の上位10位の平均値を求める方法を教えてください.

エクセルで範囲指定をして,範囲内の上位10位の平均値を求める方法を教えてください. 図のように,C列の番号が同じ番号のF列の値の上位10位の平均値を求めたいです.なお,10位が複数ある場合は,すべて10位として計算に含めたいです. C列の番号は1~350個あり,計算に使う行数も番号によってバラバラです. どのような関数を使えばうまくいくか教えてください.

画像

Excel12閲覧

ベストアンサー

0

範囲の指定はC列の数値が同じになっている範囲を意味しているとします。 お示しの通りの表として作業列を作って対応します。 K2セルには次の式を入力して下方にドラッグコピーします。 =IF(F2="","",C2*100+RANK(F2,INDEX(F:F,MATCH(C2,C:C,0)):INDEX(F:F,MATCH(C2,C:C,0)+COUNTIF(C:C,C2)-1),0)) その後にお求めのデータですが例えばH2セルから下方には平均を求めたい範囲としてC列の数値を入力します。例えば113のようにします。 I2セルから下方には指定された範囲での上位10位までの平均値を表示することにしてI2セルには次の式を入力して下方にドラッグ子p-すればよいでしょう。 =IF(H2="","",AVERAGEIFS(INDEX(F:F,MATCH(H2,C:C,0)):INDEX(F:F,MATCH(H2,C:C,0)+COUNTIF(C:C,H2)-1),INDEX(K:K,MATCH(H2,C:C,0)):INDEX(K:K,MATCH(H2,C:C,0)+COUNTIF(C:C,H2)-1),">="&H2*100+1,INDEX(K:K,MATCH(H2,C:C,0)):INDEX(K:K,MATCH(H2,C:C,0)+COUNTIF(C:C,H2)-1),"<="&H2*100+10)) なお、作業列が目障りでしたらその列を選択して「非表示」にすればよいでしょう。

回答者の補足を見ましたらすべてのデータの範囲でとありましたがH列にすべての範囲の数値を入力してI列にそれぞれの数値範囲での平均が表示されますので お求めの平均値は =AVERAGE(I:I)

ThanksImg質問者からのお礼コメント

手早く算出することができました. 大変助かりました. ありがとうございました!! また機会がありましたらよろしくお願いいたします.

お礼日時:10/27 12:29

その他の回答(2件)

1

データーの範囲は指定してもらうとして。 こんな感じ。 =SUMPRODUCT((RANK($C$5:$C$355,$C$5:$C$355,0)<=10)*1,($C$5:$C$355))/SUMPRODUCT((RANK($C$5:$C$355,$C$5:$C$355,0)<=10)*1)

1人がナイス!しています

あ、C列前提で作ってますが、指定値に対する範囲条件がわからないので範囲内(今回はC列だけ)で判断した計算式です。 多分これをF列でやればいいと思います。

1

=AVERAGE(F2:F15) 範囲をアクティブにして右上の方のシグマから、平均を選択しても。 難しい関数は分からないので、私だったらC列の113だけを集めて、F列を大きい順に並べ替えてから、上位10個の平均値出すけどな。

1人がナイス!しています

1~350のすべての上位10位の平均値を求めたいです. 350回その動作を繰り返すのは大変だと思いました. IFなどを使って一括で350個の平均値を算出できるといいのですが…