関数で求めた２つのセル範囲内の、平均値を出したい。

質問

日の出から日の入までの範囲の平均温度を求めたいのですが、日の出時刻と日の入り時間はVLOOKUP関数によって求めています。
Ａ列に時刻、Ｂ列に温度が並んでいます。
求めた時刻が例えば、日の出6:00（セル番号 A360)、日の入18:00(セル番号 A1080)となっている場合。
当然ながら、=AVERAGE(A360:A1080)と入力すれば可能ですが、
=AVERAGE(VLOOKUP関数数式:VLOOKUP関数数式）はエラーとなります。
=AVERAGE(VLOOKUP関数数式,VLOOKUP関数数式)は２点のみの平均温度となってしまいます。
どのような方法で解決できるでしょうか？
あるようで見つからないのですが、指定した範囲内に一致する数値があった場合にそのセル番号を返すような数式はあるのでしょうか？

回答No.1

zap35 · ベストアンサー

例えばOFFSET関数とMATCH関数を組み合わせる方法があります
D1に日の出時刻、D2に日の入時刻を関数で求めておくと

=AVERAGE(OFFSET($B$1,MATCH(D1,A:A,1)-1,0,MATCH(D2,A:A,1)-MATCH(D1,A:A,1)+1,1))
で平均気温（？）が求められます

＞指定した範囲内に一致する数値があった場合にそのセル番号を返すような数式
それがMATCH関数です。

お礼

早速の回答ありがとうございます。
とりあえず教えて頂いた数式を入力してみますと目的どおりの結果となりました。ただ、私にとっては複雑で内容を理解できていないのでもう少し勉強してみます。

回答No.2

imogasi

これは配列数式（多分SUMPRODUCTでも可能か、未確認）で解くのが適した方法かと思う。
例データ　ちょっと勝手な例に簡素化している。
F1:H6に
日 日の入り 日の出(24時を越える例になってすまない）
1 11 20
2 20 30
3 24 31
4 28 33
5 30 36
ーーーー
A1:B3
A列　　B列
1 日
日の入り 日の出で
11 20
ーー
A3は　=VLOOKUP($A$1,$F$2:$H$6,2,FALSE)
B3は　=VLOOKUP($A$1,$F$2:$H$6,3,FALSE)
で求める。
ーーー
D列　E列　D1:E16
時刻（に当たるもの）　　　　計数
1 2
3 3
5 6
7 5
9 4
11 2
13 7
15 3
17 2
19 4
21 5
23 5
25 6
27 7
29 2
31 3
ーー
関数
=AVERAGE(IF((D1:D16>=$A$3)*(D1:D16<=$B$3),E1:E16,""))
ト入れてShift、Ctrl,Enterの３つのキーを同時押しする。
結果
３．６
ーーー
検算　
D列とE列の一部（＝D列で条件に合致するもの）
時刻の間隔のつもり。
１１より大で２０より小のもの
11 2
13 7
15 3
17 2
19 4
計 18
平均 3.6ーー＞一致
＝＝＝
これも
＞私にとっては複雑で内容を理解できていないのでもう・・
言われそうだが、質問の内容が難しいタイプなんです。身に過ぎた
課題にぶつかったということです。
少し易しくする方法は中間列などを多用するしかないようにおもう。
しかし日の数が多くなると収拾付かない。

お礼

ご指導ありがとうございます。大変参考になりました。
結局、データロガーの値を貼り付けのみで解決したかったので、OFFSETとMATCHを使うことにしました。
えーと、はい、完璧に理解しましたのでご心配なく。