日付順 (降順) でリストされているこの例のデータでは、日付別に、値が現在の値より最後に高かったか低かったかを調べられるようにしたいと思います。
現在の月が 12 月で値が 5 の場合、値が最後に低かったのは 11 月 (4) であり、値が最後に高かったのは 9 月 (8) です。
これまで MATCH/INDEX などを使用して見つけたソリューションでは、5 に最も近い次に高い値である 8 月 (6) が返されます。日付順にリストを検索したいと思います。
これは、たとえば「今月の値は 9 月以来の最高値です」と言う場合に使用されます。
答え1
動的配列数式フィルターがある場合:
=@FILTER(A3:A7,B3:B7>=B2)
そして
=@FILTER(A3:A7,B3:B7<=B2)
そうでない場合は、INDEX/AGGREGATE を使用します。
=INDEX(A:A,AGGREGATE(15,7,ROW(A3:A7)/(B3:B7>=B2),1))
そして
=INDEX(A:A,AGGREGATE(15,7,ROW(A3:A7)/(B3:B7<=B2),1))
答え2
この配列 (CSE) 式により、問題はほぼ解決されます。
セルS16の数式:
{=INDEX($P$16:$P$21,MAX(IF(($Q$16:$Q$21<=$Q16),$Q$16:$Q$21,0),0))}セル T16:
{=INDEX($P$16:$P$21,MIN(IF(($Q$16:$Q$21>=$Q16),$Q$16:$Q$21,0),0))}
注意
- フォーミュラを仕上げるCtrl+Shift+Enter。
:編集済み:
最も近い最高値/最低値を見つけるこの 2 段階のソリューションをお勧めします。
セル S20 の配列 (CSE) 数式:
{=INDEX($P$16:$P$21,MATCH(MIN(IF($Q$16:$Q$21>$Q16,$Q$16:$Q$21)),$Q$16:$Q$21,0))}セル T20 の配列 (CSE) 数式:
{=INDEX($P$16:$P$21,MATCH(MAX(IF($Q$16:$Q$21<$Q16,$Q$16:$Q$21)),$Q$16:$Q$21,0))}
注意
S20とT20の仕上げフォーミュラCtrl+Shift+Enter。
必要に応じて、数式内のセル参照を調整できます。