AVERAGEIFS関数の引数が可変の場合

質問

averageifs関数のことについて教えてください。

引数の合計対象範囲をindirect関数とaddress関数、match関数を使い可変にしています。
条件範囲また条件はダイアログボックスで確認する限り計算されてはいるのですが、
戻り値が#VALUEになります。

どうすればキチンと戻り値が表示されるのでしょうか？

ちなみに可変の部分（合計対象範囲）をaverage関数で計算するとちゃんと計算されています。

　　　　　　　　　↓こんな式です。　　　
AVERAGEIFS(INDIRECT(ADDRESS(MATCH(DATE(データ!$D$1,$A3,1),データ!$D:$D,0),6,,,"データ")):INDIRECT(ADDRESS(MATCH(DATE(データ!$D$1,$A3+1,0),データ!$D:$D,0),6,,,"データ")),データ!$C:$C,0)

ややこしいですが、お願いします。

回答No.3

keithin · ベストアンサー

ちなみに「可変範囲で計算したい」のが主のご質問だったのでしたら，
INDEX関数
OFFSET関数
INDIRECT関数
などが利用できます。

計算例：
=AVERAGEIFS(INDEX(データ!F:F,MATCH(DATE(データ!$D$1,$A3,1),データ!$D:$D,0)):INDEX(データ!F:F,MATCH(DATE(データ!$D$1,$A3+1,0),データ!$D:$D,0))
,INDEX(データ!C:C,MATCH(DATE(データ!$D$1,$A3,1),データ!$D:$D,0)):INDEX(データ!C:C,MATCH(DATE(データ!$D$1,$A3+1,0),データ!$D:$D,0)),0)

計算例：
B1に対象範囲の先頭行　=MATCH(DATE(データ!$D$1,$A3,1),データ!$D:$D,0)
B2に対象範囲の終わる行　=MATCH(DATE(データ!$D$1,$A3+1,0),データ!$D:$D,0)
B3に
=AVERAGEIFS(INDIRECT("データ!F"&B1&":F"&B2),OFFSET(データ!C1,B1-1,0,B2-B1+1,1),0)

＃どれを使ってもリクツは大体同じです（ダラダラ長くなるのも一緒です）が，ご質問で掲示された数式みたいにわざわざ「セル番地の文字列」を経由するよりは，もうちょっとだけすっきりできると思います。
＃もちろん，回答１のようにしてしまえば遙かに簡単なので，まずそう回答したわけですが。

お礼

ありがとうございます。返信が遅れてすみません。
わざわざ２通りの回答までいただいてありがたいです。
条件範囲と合計対象範囲が違っていたことを気づいていませんでした。

おかげさまで無事式を完成できました。
ご教授ありがとうございました。

回答No.2

KURUMITO

条件範囲も同じ範囲としなければいけませんので次のような式にすればよいでしょう。

=AVERAGEIFS(INDIRECT(ADDRESS(MATCH(DATE(データ!$D$1,$A3,1),データ!$D:$D,0),6,,,"Sheet2")):INDIRECT(ADDRESS(MATCH(DATE(データ!$D$1,$A3+1,0),データ!$D:$D,0),6,,,"データ")),INDIRECT(ADDRESS(MATCH(DATE(データ!$D$1,$A3,1),データ!$D:$D,0),3,,,"データ")):INDIRECT(ADDRESS(MATCH(DATE(データ!$D$1,$A3+1,0),データ!$D:$D,0),3,,,"データ")),0)

お礼

ありがとうございます。
条件範囲が間違っていました。

おかげさまで無事、式が完成しました。
ご教授ありがとうございました。

回答No.1

keithin

関数のヘルプから抜粋：
AVERAGEIFS では、各検索条件範囲に含まれるセルの個数と形状は、合計対象範囲と必ず一致している必要があります。

という条件を満たしていないので，エラーになります。

計算例：ただしD列が日付の昇順等で整列されているとして
=AVERAGEIFS(データ!F:F,データ!D:D,">="&DATE(データ!$D$1,$A3,1),データ!D:D,"<="&DATE(データ!$D$1,$A3+1,0),データ!C:C,0)