자유

쓰레기 버전의 희망_aggregate 를 filter 함수 처럼 쓰기

박

9월 9일 조회 614

Q&A 계시판에서

비어있는 셀 제외하고 값이 있는 셀의 제목만 찾기

라는 글에서 라온보리 님이 보여주신 함수가 대단히 인상깊었는데

2019 이하에서 aggregate 함수를 2021의 filter 함수 처럼 사용해 해결하셨기 때문입니다.

핵심은 AGGREGATE(15,6,@@@@@@@)

한국에서는 잘 쓰지 않는 방법이라
GTP에게 물어 보니 외국에서는 많이 쓰는 방법이라고 하네요.
라온보리님이 GPT 이용해서 찾아낸 답변 아닌가 싶습니다.

혹시 다들 알고 있던 것데 저만 몰랐을 수도 있지만 마법의 손님이나 오빠두 님이 쓰는걸 본적 없고
다른 고수님들이 쓴적 없는걸 볼때 한국에서 잘 알려진 지식은 아니라고 봅니다.

한국에서는 보통 AGGREGATE(9,6,@@@@@@@) 정도만 보급되었는데 사실 별 쓸모 없죠
하지만 AGGREGATE(15,6,@@@@@@@) 은 전혀 다르다고 봅니다

여기서 핵심은 AGGREGATE(15,6,@@@@@@@) 15, 6 이라는 옵션입니다.
저걸로 필터링을 구현할 수 있다는게 제 의견입니다.

제가 테스트해 본 결과 sumproduct, lookup 함수의 상위 호환입니다.

저는 저 답을 약간 개량해서

정말 압도적인 퍼포먼스를 보여주는 걸 볼 수 있습니다.

2019 이하 사용자님들은 반드시 익혀야 할 함수로 보이고
저기서 보여준 능력치는 filter 함수 정도지만
실은 그 이상의 잠재력을 가진 것 아닐까 싶습니다.

#통계 함수 #FILTER 함수 #SUMPRODUCT 함수

마

마법의손 9월 9일

알고는 있지만 손이 안가서....
위 aggregate..수식이나 아래 수식이나 같습니다..~
=INDEX($1:$1,SMALL(IF($C$2:$O$2<>"",COLUMN($C$2:$O$2)),ROW(A1)))

박

박정호2 작성자 9월 9일

@마법의손 님 이건 IF 함수가 들어가서 CES로 써야 합니다.
당연히 다르다고 봅니다.

수

수미수 9월 9일

널리 쓰는 방식입니다. GPT 검색 아닙니다. 내부에 IF 계열 배열 연산만 없다면 CSE 없이 Enter로 배열수식을 작성할수 있어서 예전부터 많이 써온 방식입니다.
IF 계열 함수에 배열 연산은 반드시 CSE가 동반되어야 하는데 IF 대신 나누기 오류를 발생시켜 분기 작업을 대신하고 오류 무시로 CSE 없이 집계값을 찾는 방식입니다.

박

박정호2 작성자 9월 9일

@수미수 님 널리 쓴다고 하기엔 전 1년 가까이 여기서 본 적이 없습니다만?
정말 널리 쓰는 것 맞습니까?

수

수미수 9월 9일

@박정호2 님

엑셀을 이곳 한곳으로 한정하니 그렇지요. 다른 커뮤니티와 네이버 지식인에도 aggregate 검색하면 비슷한 패턴 수없이 나옵니다.

박

박정호2 작성자 9월 9일

@수미수 님 그렇군요. 진짜 숨겨진 비기 같은데 진즉 좀 공개해 주시지
전 몰랐습니다.
라온보리 님이 보여주실 때가진 전혀 몰랐습니다.
아무튼 이건 이 오빠두엑셀 사이트에 제가 좋은 함수를 알리고자 한 글입니다
저렇게 좋은 함수라면 널리 공유되야 마땅하지 않겠습니까
적어도 이 사이트 사람들은 잘 몰랐던 건 확실해 보입니다.
그런데 님이 '누구나 알고 있었다'라는 발언은
이 사이트에 좋은 함수를 알리고자 노력했던 저의 기여에 대한 폄하와
비아냥으로 느껴졌습니다.

저는 오빠두님이 모를 정도면 사실상 한국 사람들도 대부분 모르고 있을 거라는 생각으로 저런 과감한 발언을 했는데 너무 무식한 거였네요 세상은 넓고 고수는 많습니다.

좋은 함수가 있다면 꽁꽁 숨기지 마시고 좀 알려주시는게 좋지 않을까요
그리고 더더군나다 그걸 다른 사람에게 알려주고자 노력한 사람의 노력도 알아 주셨으면 좋겠습니다.

수

수미수 9월 9일

@박정호2 님 괜히 확대해석 하지마세요. 널리 쓰이는 방식이란건 자명한 사실이고.. 저는 누구나 알고 있었다. 라고 한 적 없습니다.
aggregate 로 필터링 하는 패턴을 누구나 알고 있는 vlookup과 같은 방식이 아니에요.
적어도 Legacy 배열 수식을 충분히 다루어 본 적이 있는 유경험자들 사이에서는 그리 특별할게 없는 수식이지.. 누구나 이 패턴을 다 알고 있는 기본 패턴은 아닙니다.
여기 주인장님이 이걸 모른다구요? 전 알고 있다고 봅니다.
그냥 수식을 쓰는 성향 차이에요. aggregate 보다 large, small을 쓰는게 수식이 더 간결하고 어파치 내부에서 배열연산을 하는건 같으니까 성능차이도 없어요.
이걸 뭐 숨긴다고 할수 있겠어요? 그냥 저는 Legacy 배열 수식을 요즘엔 안쓰니깐 여기서 작성한 적이 없는거고..
어차피 이 방식은 동적 배열에서 더 이상 쓸 일 없어요. 개인적으로 그리 중요하게 생각하지 않아도 될거라고 봅니다.

박

박정호2 작성자 9월 9일

@수미수 님 댓글을 보고 제가 미처 생각하지 못했던 부분을 다시 돌아보게 되었습니다.
말씀대로, Legacy 배열 수식을 오랫동안 깊이 다뤄오신 분들께는 익숙하고 특별할 것 없는 패턴일 수 있겠다는 생각이 듭니다. 동적 배열 이전의 깊은 배열 수식의 세계는 제가 잘 알지 못하는데, 덕분에 새로운 관점을 얻었습니다. 역시 깊이가 다르시다는 걸 느낍니다.
제 글은 어디까지나 엑셀 2019 이하에서 FILTER 함수처럼 쓸 수 있는 새로운 가능성을 발견한 기쁨에, 저와 같은 처지의 분들과 희망을 공유하고 싶었던 마음이 컸습니다. 제 표현이 다소 과격하게 느껴지셨다면, 새로운 지식을 접한 저의 설렘이 과했던 탓이니 너그러이 이해해주시면 감사하겠습니다.
앞으로도 깊이 있는 지식 많이 배워갈 수 있기를 바랍니다

더

더블유에이 9월 9일

index/small 이 사용자 측면에서는 훨씬 편하다고 생각합니다.
오래전이긴 하지만 비슷한 방법으로 이것저것 써본적이 있는데 aggregate 함수는 인덱싱을 지정하려면 범위 시작셀을 꼭 한번 씩 넣어줘야해서 index/small 로 썼던 것 같네요.
그리고 쓰레기 버전이라뇨..;; 아직 2016 쓰는 기업체가 많습니다..
2021 이후에 동적배열이 너무 편리한것이지 이전 버전을 쓰레기라고 하는건 조금 불편하네요;;

박

박정호2 작성자 9월 9일

@더블유에이 님 확실히 고수님들은 다르시군요 감사합니다.
아무래도 생산성 차이가 많이 나고
제가 2019로 아무리 열심히 풀어도
채택 안되는 경우가 많아 푸념이 늘었습니다.