В этом примере данные отсортированы по дате (по убыванию), и я хотел бы иметь возможность посмотреть, когда значение в последний раз было выше или ниже текущего значения, по дате.
Если текущий месяц — декабрь, а значение равно 5, то последний раз оно было ниже в ноябре (4), а последний раз оно было выше в сентябре (8).
Решения, которые я нашел до сих пор с использованием ПОИСКПОЗ/ИНДЕКС/и т. д., вернули бы следующее большее значение, например, август (6), поскольку это ближайшее большее значение к 5. Я хотел бы выполнить поиск по списку в порядке дат.
Например, это можно использовать, чтобы сказать: «Значение этого месяца является самым высоким с сентября».
решение1
Если у вас есть динамическая формула массива ФИЛЬТР:
=@FILTER(A3:A7,B3:B7>=B2)
и
=@FILTER(A3:A7,B3:B7<=B2)
если нет, то используем ИНДЕКС/АГРЕГАТ:
=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.
:Отредактировано:
Я хотел бы порекомендовать это двухэтапное решение для поиска ближайшего самого высокого/самого низкого значения.
Формула массива (CSE) в ячейке S20:
{=INDEX($P$16:$P$21,MATCH(MIN(IF($Q$16:$Q$21>$Q16,$Q$16:$Q$21)),$Q$16:$Q$21,0))}Формула массива (CSE) в ячейке T20:
{=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.
При необходимости вы можете скорректировать ссылки на ячейки в формуле.