리뷰) 마법의 손님이 보여준 마법 같은 공식

마법의 손님이 보여준 마법 같은 공식에 대한 리뷰

=IFERROR(INDEX($C$1:$C$20,1/LARGE(INDEX(($A$1:$A$20=$E2)*($B$1:$B$20=F$1)/ROW($A$1:$A$20),),ROW($A1))),"")

 

공식의 의미는 FILTER 함수 입니다. 그것도 CSE free

식의 의미는 =FILTER($C$1:$C$20,($A$1:$A$20=$E2)*($B$1:$B$20=F$1),"")
대부분 이게 더 익숙하실 것 같습니다.

마법의 손님이 보여준 공식의 의미는
어떤 낮은 버전의 엑셀에서도 FITER 함수를 쓸 수 있다입니다.

사실 더 대단합니다.
저 공식을 약간 변형하면
XLOOKUP, UNIQUE, SORT 함수를 구현할 수 있기 때문입니다.
게다가 CSE free

이게 무슨 의미인지 잘 아실 겁니다.

XLOOKUP 구현은

=IFERROR(INDEX($C$1:$C$20,1/LARGE(INDEX(($A$1:$A$20=$E2)*($B$1:$B$20=F$1)/ROW($A$1:$A$20),),ROW($A1))),"")

=IFERROR(INDEX($C$1:$C$20,1/LARGE(INDEX(($A$1:$A$20=$E2)*($B$1:$B$20=F$1)/ROW($A$1:$A$20),),1)),"")

파란색 부분 만 바꿔주면 됩니다. 다른 것도 비슷합니다. 조금 변형해 주면 됩니다.
(정보 공유실에 aggregate로 UNIQUE, SORT 구현한 거 올렸는데 그렇게 하면 됩니다.)

저도 이해하기 어려운 공식이었는데 마법의 손님을 통해 3번 정도 보면서 알게 됐습니다.

=IFERROR(INDEX($C$1:$C$20,1/LARGE(INDEX(($A$1:$A$20=$E2)*($B$1:$B$20=F$1)/ROW($A$1:$A$20),),ROW($A1))),"")

일단 논리적으로도 매우 아릅답지만 핵심은 빨간색 부분이라고 봅니다.
INDEX( ,) 이라고 할 수 있고 의미는 INDEX( ,0) 입니다.
쉽게 말해 CSE free 가능한 이유가 저것 덕분입니다.

실제로 INDEX( ,) 를 붙이면 CSE Lock 이 풀리는 경우가 많습니다.

=INDEX(C1:C11,MATCH(1,(A1:A11=D2)*(B1:B11=D3),0)) CSE Lock
=INDEX(C1:C11,INDEX(MATCH(1,(A1:A11=D2)*(B1:B11=D3),),0)) CSE free

a1=1, a2=2, a3=3
이라고 할때

=SUM((A1:A3>1)*A1:A3) CSE Lock
=SUM(INDEX((A1:A3>1)*A1:A3,)) CSE free

특히 MATCH 함수의 CES Lock 이 잘 풀리는데

오빠두님이 올리신 고유값 예제
=INDEX($B$10:$B$17,MATCH(0,COUNTIF($F$9:F9,$B$10:$B$17),0)) CSE Lock
=INDEX($B$10:$B$17,MATCH(0,index(COUNTIF($F$9:F9,$B$10:$B$17),),0)) CSE free

다만 sum 보단 sumproduct 쓰는 게 좋고
IF 함수나 sumifs 는 안되기 때문에 한계가 있습니다.

2019 이하 엑셀에선 match, large, small 이 함수들의 경우
index( ,) 을 쓰면 장점이 많은 것 같습니다.

다만 개인적으로는 aggregate(15,6)을 추천합니다.
포텐션은 저 공식이 더 커 보이지만

현실적으론 aggregate(15,6) 로도
FITER, XLOOKUP, UNIQUE, SORT
모두 구현 할 수 있고(사실상 호환 함수)

공식짤때 이쪽이 더 편하다고 봅니다.
다만 한셀 같은 곳에는 aggregate 가 없고
google 도 없는데 호환성 측면에서는 더 유리할 것 같습니다.

 

PS
위에 소개한 공식은 구글시트에서 잘 작동하는 걸 확인했습니다.