同一行で複数条件を検索して値の平均値を求めたい

質問

みなさん教えてください。

タイトルでは上手く書けなかったのですがExcelで下のようなデータがあります。

1,みかん,I,I,I,II,II,II,III,III,III,III
商品コード,231,3,3,5,6,3,4,1,7,2,9

この様なデータが1万件程度あります。

このデータを基に平均を求めたいのですがその条件が上段のセルに”I”が入っている下段の平均値（この場合は3,3,5の平均値）、次に上段に”I”と”II”が入っている下段の平均値（この場合は3,3,5,6,3,4の平均値）、最後に上段”I”と”II”、”III”が入っている下段の平均値（この場合は3,3,5,6,3,4,1,7,2,9の平均値）です。
商品名の後ろのI～IIIは商品によってデータ数が変動します。

上段に”I”のみ入っているデータの平均値は
=averageif(C3:L3,"I",C4:L4)で出来ましたが、同一行に複数条件を入れる場合
=averageifs(C4:L4,C3:L3,"I",C3:L3,"II")ですると#DIV/0!が返されました。
どのように関数を入れたらよろしいのでしょうか？
よろしくお願いします。

PCはwindowsXP、Excelは2010を使用しています。

回答No.1

keithin · ベストアンサー

『I「または」II』のような重ねは，AVERAGEIFS関数では計算できませんので，次のようにする必要があります。
=SUMPRODUCT(SUMIF(C3:L3,{"I","II"},C4:L4))/SUMPRODUCT(COUNTIF(C3:L3,{"I","II"}))
=SUMPRODUCT(SUMIF(C3:L3,{"I","II","III"},C4:L4))/SUMPRODUCT(COUNTIF(C3:L3,{"I","II","III"}))

＃敢えて言うと
「IまたはIIまたはIII」は「全て」と読み替えてAVERAGE(C4:L4)で計算する事は可能です
「IまたはII」は「IIIではない」と読み替えて，=AVERAGEIF(C3:L3,"<>III",C4:L4)と計算する事は可能です。

お礼

keithin様

ありがとうございます。
やはり1個の関数で済ますことはできないのですね。
分かりやすい説明、考え方ありがとうございました。

回答No.3

KURUMITO

例えばＣ３セルから右横セルにI、IIなどが有り、その下の行に数値が有るとして
Ｃ5セルからＥ5セルにかけてIのみの平均、IとIIの平均、IIとIIIの平均を表示させるとしたらＣ5セルには次の式を入力してＥ5セルまでドラッグコピーします。

=IF(COLUMN(A1)=1,AVERAGE(INDEX($C4:$X4,1):INDEX($C4:$X4,COUNTIF($C3:$X3,"I"))),IF(COLUMN(A1)=2,AVERAGE(INDEX($C4:$X4,1):INDEX($C4:$X4,COUNTIF($C3:$X3,"I")+COUNTIF($C3:$X3,"II"))),AVERAGE(INDEX($C$4:$X4,COUNTIF($C3:$X3,"I")+1):INDEX($C$4:$X$4,COUNTIF($C$3:$X$3,"I")+COUNTIF($C3:$X3,"II")+COUNTIF($C3:$X3,"III")))))

数値が小数点以下に多くなる場合には小数点以下の表示桁数を減らす操作をすればよいでしょう。

回答No.2

mt2008

AVERAGEIFSの検索条件はandです。
つまり、貴方の式↓
=averageifs(C4:L4,C3:L3,"I",C3:L3,"II")
は、上段が「I」「II」両方の条件を満たす場合の平均を出そうとしているので、該当なしとなり#DIV/0! を返しています。
これを回避する方法として２案提示ます。お好きな方をどうぞ。

「III」以外の平均を出す。
=AVERAGEIFS(C4:L4,C3:L3,"<>III")　

配列数式を使う。↓Ctrl+Shft+Enterで確定する事。
=AVERAGE(IF(ISNUMBER(MATCH(C3:L3,{"I","II"},0)),C4:L4))