【Excel】複数条件のAVERAGE

質問

ある特定したいくつかの要素の合計を、セル「I5」にだすとします。

たとえば表２のH2の「年」、同表のH3の「店舗」、同表のG5以下の「種目」からそれぞれ具体的な条件を、たとえば表1のA列より「2016年」、同表のE列より「店舗(1)」、同表のB列より「アスパラガス」を探し出すようにする場合関数式は以下のような式となりますが、

=SUMIFS($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3)

これを合計ではなく平均値で出す場合、「SUMIFS」のみ「AVERAGE」に入れ替え
=AVERAGE($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3)
という式を「I5]に入れると確かに平均値はあっていたのですが、この式をコピー（セルのコピー）しても、正常に反映されず、I5の回答がそのままコピーされてしまいます。

何が原因なのでしょうか？

回答No.2

miso_kasu · ベストアンサー

>確認のために手動計算してみましたところ、合っていました。
SUMIFS関数で合計を算出した範囲の平均値であれば合っているはずはないのですが平均対象のセルを再確認してください。
A列が2016年でG列がアスパラガスは対象データが無いようです。

>＞AVERAGEIFS
>に差し替えたのですが、#DIV/0!になってしまいます。
前述のように条件に合う計算対象が1つもないため「0で除算したときのエラー」になったのです。

>外側というのは何に対しての外側でしょうか？どう設定すればよいかわかりません。
=AVERAGEIFS($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3)
　　　　　↓
=IFERROR(AVERAGEIFS($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3),"")

>つまり、列全体とした場合上の式は
=AVERAGEIFS(D:D,A:A,$H$2,B:B,$G5,E:E,I$3)
　　　　↓
=IFERROR(AVERAGEIFS($D:$D,$A:$A,$H$2,$B:$B,$G5,$E:$E,H$3),"")
H3とI3は結合しているのでI$3にはデータが有りません。
$E:$EをH$3と比較してください。

お礼

おかげさまで目的が叶いました。ありがとうございました！

回答No.1

miso_kasu

>これを合計ではなく平均値で出す場合、「SUMIFS」のみ「AVERAGE」に入れ替え
「AVERAGE」ではなく「AVERAGEIFS」にしてください。
但し、集計する対象が無いときは「0で除算」のエラーになります。

>という式を「I5]に入れると確かに平均値はあっていたのですが、
手動で計算してみましたか？
AVERAGE関数はカッコ内にカンマ区切りで列記されたセルの値や実数が全て計算対象になりますのでAVERAGEIFS関数と異なった結果になります。

>この式をコピー（セルのコピー）しても、正常に反映されず、I5の回答がそのままコピーされてしまいます。
I5をI6へコピーすると同じ結果になると思います。

>何が原因なのでしょうか？
AVERAGEとAVERAGEIFSでは求める対象が異なります。
前述のように「AVERAGE」を「AVERAGEIFS」に修正してください。
エラーを避けるにはIFERROR関数を外側に付けてください。

お礼

=AVERAGEIFS($D$2:$D$707,$A$2:$A$707,$H$2,$B$2:$B$707,$G5,$E$2:$E$707,I$3)
ですが、行は707までに増やしました。
参照される側のセルを増やすたびに、式を変えていかなければなりません。
列全体を選択させるにはたとえば
=SUMIF(B:B,G6,D:D)
のようになるらしいですが、「B:B」と「D:D」には絶対値の記号「$」がつけられてません。
「B:B」はB列全部という意味だと思いますが、このように示せば絶対値の「$」マークは不要なのでしょうか？

つまり、列全体とした場合上の式は
=AVERAGEIFS(D:D,A:A,$H$2,B:B,$G5,E:E,I$3)
こうなるのでしょうか？

補足

ご回答ありがとうございます。

＞ 手動で計算してみましたか？
確認のために手動計算してみましたところ、合っていました。

＞AVERAGEIFS
に差し替えたのですが、#DIV/0!になってしまいます。

#DIV/0!は数式や関数が0（ゼロ）または空白のセルで割られていることを伝えるメッセージだそうですが、これは
＞集計する対象が無いときは「0で除算」のエラー
のことだと思いますが、
＞ エラーを避けるにはIFERROR関数を外側に付けてください。
外側というのは何に対しての外側でしょうか？どう設定すればよいかわかりません。