엑셀 필터링 후 첫번째 값 찾기 공식
엑셀 필터링 후 첫번째 값 찾기 목차 바로가기
함수 공식
{ =INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN($시작셀),4),1,"")&MIN(IF(SUBTOTAL(3,OFFSET($시작셀,ROW($범위)-ROW($시작셀),0)),ROW($범위)))) }
' 본 공식은 배열수식이므로 Ctrl + Shift + Enter 로 입력합니다.
' 본 공식은 배열수식이므로 Ctrl + Shift + Enter 로 입력합니다.
해당 공식은 Q&A 게시판 질문에서 아이디어를 얻어 작성되었습니다. 질문을 올려주신 '엑셀탐구자'님께 감사드립니다.
인수 설명
| 인수 | 설명 |
| $시작셀 | 필터링 범위의 시작셀 입니다. |
| $범위 | 필터가 적용 될 범위입니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 필터링 후 화면에 보이는 첫번째 값 찾기예제파일
호환성
운영체제 호환성 Windows 버전 모든 엑셀 버전에서 사용 가능합니다. Mac 버전 모든 엑셀 버전에서 사용 가능합니다. 사용된 기초 함수
필터링 후 첫번째 값 찾기 공식 알아보기
공식 설명
해당 공식은 필터가 적용된 범위의 첫번째 값을 찾아 반환하는 함수 공식입니다. 필터가 적용된 범위 넓이에 상관없이 필터링 후 화면에 보이는 범위 내 첫번째 값을 반환합니다.
본 공식은 숨겨진 셀은 제외 후 화면에 보이는 값에 대해서만 데이터를 집계하는 SUBTOTAL 함수를 사용합니다. 상황에 따라 더 복잡한 형태의 고급수식이 필요할 경우, SUBTOTAL 함수 대신 AGGREGATE 함수를 사용할 수도 있습니다.
해당공식은 배열수식입니다. 따라서 입력시 반드시 CTRL + SHIFT + ENTER 로 입력해야 하는 것에 주의합니다.
공식의 동작원리
- OFFSET 함수로 범위의 모든 값을 순서대로 반환합니다.
■ $시작셀 : A1
■ $범위 : A1:A10
=OFFSET(A1,ROW(A1:A10)-ROW(A1),0)
=OFFSET(A1,{1,2,3,4,5,6,7,8,9,10}-1,0)
=OFFSET(A1,{0,1,2,3,4,5,6,7,8,9},0)
={A1,A2,A3,... A10} - SUBTOTAL 함수를 사용하여 범위 중 화면에 보이는 값의 개수만 셉니다.
■ 화면에 보이는 범위 : A4, A6
=SUBTOTAL(3,OFFSET($시작셀,ROW($범위)-ROW($시작셀),0))
=SUBTOTAL(3,{A1,A2,A3,A4,A5,A6,A7,A8,A9,A10},0)
={0,0,0,1,0,1,0,0,0,0} '화면에 보이는 셀만 1을 반환합니다. - IF 함수를 사용하여 값이 1인 경우 해당 위치의 행번호를 반환합니다.
=IF(SUBTOTAL(3,OFFSET($시작셀,ROW($범위)-ROW($시작셀),0)),ROW($범위))
=IF({0,0,0,1,0,1,0,0,0,0},{1,2,3,4,5,6,7,8,9,10})
={FALSE, FALSE, FALSE, FALSE, 4, FALSE, 6, FALSE,...} - MIN 함수로 IF 함수로 반환된 범위의 최소값을 구합니다.
=MIN(IF(SUBTOTAL(3,OFFSET($시작셀,ROW($범위)-ROW($시작셀),0)),ROW($범위)))
=MIN({FALSE, FALSE, FALSE, FALSE, 4, FALSE, 6, FALSE,...})
=4 - 숫자→알파벳 변환공식을 사용하여 범위의 열기호(알파벳)을 반환합니다.
=SUBSTITUTE(ADDRESS(1,COLUMN($시작셀),4),1,"")
=SUBSTITUTE(ADDRESS(1,1,4),1,"")
=SUBSTITUTE("A1",1,"")
="A" - INDIRECT 함수를 통해 필터링 후 첫번째 값 구하기 공식을 마무리합니다.
=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN($시작셀),4),1,"")&MIN(IF(SUBTOTAL(3,OFFSET($시작셀,ROW($범위)-ROW($시작셀),0)),ROW($범위))))
=INDIRECT("A"&4)
=INDIRECT("A4") '화면의 보이는 범위의 첫번째 셀인 A4 셀의 값을 반환합니다.
- OFFSET 함수로 범위의 모든 값을 순서대로 반환합니다.
