엑셀 필터링 후 첫번째 값 찾기 공식 사용법 및 동작원리 총정리

필터링 후 화면에 보이는 범위 중 첫번째 값을 반환하는 함수 공식의 사용법 및 동작원리를 알아봅니다.

홈페이지 » 엑셀 필터링 후 첫번째 값 찾기 쉬운 방법 :: 함수 공식

엑셀 필터링 후 첫번째 값 찾기 공식

엑셀 필터링 후 첫번째 값 찾기 목차 바로가기
함수 공식
{ =INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN($시작셀),4),1,"")&MIN(IF(SUBTOTAL(3,OFFSET($시작셀,ROW($범위)-ROW($시작셀),0)),ROW($범위)))) }
' 본 공식은 배열수식이므로 Ctrl + Shift + Enter 로 입력합니다.

해당 공식은 Q&A 게시판 질문에서 아이디어를 얻어 작성되었습니다. 질문을 올려주신 '엑셀탐구자'님께 감사드립니다.

인수 설명
필터링 후 첫번째 값 찾기 인수 설명_R
엑셀 필터링 후 첫번째 값 구하기 공식에 들어가는 인수
인수설명
$시작셀필터링 범위의 시작셀 입니다.
$범위필터가 적용 될 범위입니다.

예제파일 다운로드

호환성
운영체제호환성
Windows 버전모든 엑셀 버전에서 사용 가능합니다.
Mac 버전모든 엑셀 버전에서 사용 가능합니다.
사용된 기초 함수
시작지점으로부터 지정한만큼 이동한 곳의 셀 참조 또는 값을 반환합니다.
셀 또는 범위를 텍스트/문자열에서 직접 참조합니다.

필터링 후 첫번째 값 찾기 공식 알아보기

공식 설명

해당 공식은 필터가 적용된 범위의 첫번째 값을 찾아 반환하는 함수 공식입니다. 필터가 적용된 범위 넓이에 상관없이 필터링 후 화면에 보이는 범위 내 첫번째 값을 반환합니다.

본 공식은 숨겨진 셀은 제외 후 화면에 보이는 값에 대해서만 데이터를 집계하는 SUBTOTAL 함수를 사용합니다. 상황에 따라 더 복잡한 형태의 고급수식이 필요할 경우, SUBTOTAL 함수 대신 AGGREGATE 함수를 사용할 수도 있습니다.

해당공식은 배열수식입니다. 따라서 입력시 반드시 CTRL + SHIFT + ENTER 로 입력해야 하는 것에 주의합니다.

공식의 동작원리
  1. 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}
  2. 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을 반환합니다.
  3. 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,...}
  4. MIN 함수로 IF 함수로 반환된 범위의 최소값을 구합니다.
    =MIN(IF(SUBTOTAL(3,OFFSET($시작셀,ROW($범위)-ROW($시작셀),0)),ROW($범위)))
    =MIN({FALSE, FALSE, FALSE, FALSE, 4, FALSE, 6, FALSE,...})
    =4
  5. 숫자→알파벳 변환공식을 사용하여 범위의 열기호(알파벳)을 반환합니다.
    =SUBSTITUTE(ADDRESS(1,COLUMN($시작셀),4),1,"")
    =SUBSTITUTE(ADDRESS(1,1,4),1,"")
    =SUBSTITUTE("A1",1,"")
    ="A"
  6. INDIRECT 함수를 통해 필터링 후 첫번째 값 구하기 공식을 마무리합니다.
    =INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN($시작셀),4),1,"")&MIN(IF(SUBTOTAL(3,OFFSET($시작셀,ROW($범위)-ROW($시작셀),0)),ROW($범위))))
    =INDIRECT("A"&4)
    =INDIRECT("A4") '화면의 보이는 범위의 첫번째 셀인 A4 셀의 값을 반환합니다.
현재 페이지 댓글알림 신청
알림 설정
guest
0 Comments
Inline Feedbacks
View all comments
0
여러분의 생각을 댓글로 남겨주세요.x