Excel 対象範囲を自由に設定して平均値を計算する

質問

時系列に並んだ年度別の売上データについて、
任意に設定した年度の範囲での平均値を算出したいと思っています。

　　A　　　B　　C　　　D　　E　　　F　　G
1　年度　2000　2001　2002　2003 2004　2005
2　売上　5000　4000　6000　6000　8000　9000

という表の横に

　　H　　I　　　　J
1　From　To　　平均売上
2　2001　2004　　6000

という部分を設け、このFrom(H2)とTo(I2)欄に任意の年を入力すれば
右側の平均売上(J2)が計算されるようにしたいのです。

同様の条件で、指定した期間における年平均の伸び率の
計算ならばHLOOKUP関数を利用すればよいということは
すぐにわかったのですが、AVERAGE/DAVERAGEの場合は
入力した条件に対応する売上高ではなくそのセル番号を
参照する必要があるという箇所で混乱してしまいました。

回答No.3

noname#204879 · ベストアンサー

J2: =AVERAGE(OFFSET(B2,0,MATCH(H2,B1:G1,0)-1,1,I2-H2+1))

お礼

ありがとうございます。
これはシンプルでとてもわかりやすいです。

回答No.5

imogasi

別解
例データ
A1:G2
年度 2000 2001 2002 2003 2004 2005
売上 5000 4000 6000 6000 8000 9000
A5:B5に
2001 2004
C5とかに
=SUMPRODUCT((B1:G1>=$A$5)*(B1:G1<=$B$5)*(B2:G2))/(B5-A5+1)
結果
6000
--
配列数式でも出来るが
=SUM(IF((B1:G1>=$A$5)*(B1:G1<=$B$5),B2:G2,0))/(B5-A5+1)
と入れて、SHIFT,CTRL,ENTERキーを同時押しする。
結果は6000

お礼

ありがとうございます。
いろいろと勉強になります。ひとつひとつ確認しながらやってみます。

回答No.4

grumpy_the_dwarf

さらに別解

=SUMPRODUCT((B1:G1>=H2)*1,(B1:G1<=I2)*1,B2:G2)/SUMPRODUCT((B1:G1>=H2)*1,(B1:G1<=I2)*1)

お礼

ありがとうございます。
式の論理構造を示すのに適した方式ですね。

回答No.2

mitarashi

別解です。
=AVERAGE(INDIRECT(ADDRESS(2,MATCH(H2,$B1:$G1,0)+1,4) & ":" & ADDRESS(2,MATCH(I2,$B1:$G1,0)+1,4)))
でいかが。

お礼

INDIRECTを使ってもいけるんですね。
どうもありがとうございます。

回答No.1

noname#64582

Ｊ２セルに、

=AVERAGE(OFFSET($A$2,0,MATCH(H2,$B$1:$G$1,0),1,MATCH(I2,$B$1:$G$1,0)-MATCH(H2,$B$1:$G$1,0)+1))

ではどうでしょう。

お礼

式をひとつひとつ確認しながら理解することができました。
どうもありがとうございます。