ランダムな数字の平均値を出したい

質問

B列とE列とG列にランダムに数字が入っています。
A列にはア、イ、ウ、エ、オとカテゴリーの文字列が入っています。

このうちある条件をみたす（例えばA列にウと入っている）行のB列とE列とG列の数字の平均値をとりたいのですが、
どうすればよいでしょうか？

ifaverage関数を使おうと思いましたが、対象列が複数あり、対象列の間の列には
今回の計算の対象にはしたくない数字が入っているので、範囲指定で平均値をとることができません。
=sumif()/countif()でaverageifと同じ効果を出そうとしましたが、
数字には0があったり、セルにブランクが含まれていたりするので、やはりうまくいかないような気がします。

アドバイスお願いします。

回答No.3

keithin · ベストアンサー

別にちょこっと手直すだけですけどね。
=SUMPRODUCT(SUMIF(A:A,"ウ",OFFSET(A:A,0,{1,4,6})))/SUMPRODUCT(COUNTIFS(A:A,"ウ",OFFSET(A:A,0,{1,4,6}),">0"))

＃そーいうイレギュラーな事をしたいと、肝心な説明を手抜きするからこんな具合に二度手間になります。次回の反省点になさってください。（て言っとかないと、逆切れちゃう人が多いので。）
判りやすい方の数式は特に反応もありませんし、こちらは十分詳しくご説明したのでこれ以上解説は要らないと思いますので、もし興味があるなら同様に出来ますから自力で解明してください。

お礼

再度のご回答ありがとうございます！
質問が舌足らずだったようで申し訳ございませんでした。
実は会社で作業をしているのですが、今自宅なのですぐに試すことができないのです。
会社から掲示板サイトへの書き込みが原則禁止されているので
これから出社して早速ためしてみます！

回答No.2

keithin

たとえば
=SUMPRODUCT(SUMIF(A:A,"ウ",OFFSET(A:A,0,{1,4,6})))/SUMPRODUCT(COUNTIFS(A:A,"ウ",OFFSET(A:A,0,{1,4,6}),"<>"))
などのように。

＃補足
AVERAGEIF系の関数では全くできません。
簡単に言えば
（ウであってＢ列のSUMIF、Ｅ列のSUMIF、Ｇ列のSUMIFの合計）÷（ウであってＢ列が空白じゃないCOUNTIFS、Ｅ列のCOUNTIFS、Ｇ列のCOUNTIFSの合計）
で計算できます。
前述のようにイミフメイな事をしなくても、ふつーに丁寧に
=(SUMIF(A:A,"ウ",B:B)+SUMIF(A:A,"ウ",E:E)+SUMIF(A:A,"ウ",G:G))/(COUNTIFS(A:A,"ウ",B:B,"<>")+COUNTIFS(A:A,"ウ",E:E,"<>")+COUNTIFS(A:A,"ウ",G:G,"<>"))
と計算するのなら判りますよね。

＃参考
＞数字には0があったり、セルにブランクが含まれていたりする

ブランクは当然ノーカウントですが、ゼロはふつーに計算対象です。何が言いたかったのでしょうね？

補足

ご回答ありがとうございます！
例えば、計算対象のセルに、6、8、0、2、12がそれぞれ入っていたとして、
計算したい結果は、平均値にゼロのセルをカウントしない28/4＝7、なのですが、
0も分母にワンカウントされてしまうので、平均値が28/5＝5.6、という結果が出てしまうのです。

回答No.1

angkor_h

averageif(excel2007以降)で出来ませんか?　平均値対象範囲は複数行が適用できます。

> 今回の計算の対象にはしたくない数字が入っているので…
これは具体的には何ですか?　したくない(人の思考)？　関数が無視してくれない？

####
私も経験していますが、「数字として無効なセル」を
　無効なのでエラーを返す関数　…したいことが出来ない
　無効なので無視してくれる関数　…ありがたい
があって苦労します。
たとえば、「na()」はグラフでは無視するのにSUMではエラーになったり…