• ベストアンサー
  • 困ってます

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

  • 質問No.8085625
  • 閲覧数296
  • ありがとう数1
  • 気になる数0
  • 回答数3
  • コメント数0

お礼率 77% (21/27)

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

タイトルでは上手く書けなかったのですが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
  • ベストアンサー

ベストアンサー率 66% (5277/7938)

『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)と計算する事は可能です。
お礼コメント
koba28

お礼率 77% (21/27)

keithin様

ありがとうございます。
やはり1個の関数で済ますことはできないのですね。
分かりやすい説明、考え方ありがとうございました。
投稿日時:2013/05/13 11:46

その他の回答 (全2件)

  • 回答No.3

ベストアンサー率 42% (1835/4282)

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

=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

ベストアンサー率 52% (885/1701)

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

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

配列数式を使う。↓Ctrl+Shft+Enterで確定する事。
=AVERAGE(IF(ISNUMBER(MATCH(C3:L3,{"I","II"},0)),C4:L4))
結果を報告する
このQ&Aにはまだコメントがありません。
あなたの思ったこと、知っていることをここにコメントしてみましょう。
関連するQ&A

その他の関連するQ&Aをキーワードで探す

ピックアップ

ページ先頭へ