Excel: 날짜별 다음 최고/최저 값 찾기

Question

날짜별로(내림차순) 나열되는 이 예제 데이터에서 값이 현재 값보다 마지막으로 높거나 낮았던 시기를 날짜별로 조회할 수 있도록 하고 싶습니다.

이번 달이 12월이고 값이 5인 경우 마지막으로 가격이 낮아진 때는 11월(4)이고 마지막으로 가격이 높았던 때는 9월(8)입니다.

지금까지 MATCH/INDEX/etc를 사용하여 찾은 솔루션은 5에 가장 가까운 높은 값인 8월(6)을 다음으로 높은 값으로 반환합니다. 날짜 순서로 목록을 검색하고 싶습니다.

예를 들어 "이번 달 값이 9월 이후 최고 값입니다"라고 말하는 데 사용됩니다.

Answer 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))

Answer 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.

• 필요에 따라 수식에서 셀 참조를 조정할 수 있습니다.