필터 후 첫 번째 값 찾기 공식
지원 버전 자세히 보기
필터에서 화면에 보이는 첫 번째 값을 반환하는 공식입니다.
인수 설명
012021 이후 버전 공식
=XLOOKUP ( 1, SUBTOTAL ( 103, OFFSET ( 시작셀, ROW ( 범위 ) - ROW ( 시작셀 ), 0 ) ), 범위, "" )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
02레거시 배열수식
{=INDIRECT ( SUBSTITUTE ( ADDRESS ( 1, COLUMN ( 시작셀 ), 4 ), 1, "" ) & MIN ( IF ( SUBTOTAL ( 103, OFFSET ( 시작셀, ROW ( 범위 ) - ROW ( 시작셀 ), 0 ) ), ROW ( 범위 ) ) ) )}인수 설명 자세히 보기
동작 원리
012021 이후 버전 공식
SUBTOTAL 함수는 숨긴 행을 0으로 표시하고 XLOOKUP 함수는 첫 번째 1에 대응하는 값을 반환합니다.
2021 이후 버전 공식의 예제 시트에서 F1 셀이 오하늘을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
ROW 함수가 시작셀부터의 간격을 계산합니다
ROW 함수는 A2:A8의 행 번호에서 시작셀 A2의 행 번호를 빼서 0부터 6까지의 간격을 반환합니다.
=ROW ( $A$2:$A$8 ) - ROW ( $A$2 )
= {0;1;2;3;4;5;6}
A2에서 각 셀까지 이동할 행 수입니다. SUBTOTAL 함수가 보이는 행을 표시합니다
OFFSET 함수는 A2에서 행 간격만큼 내려가 A2:A8을 한 셀씩 참조합니다. SUBTOTAL 함수의 103은 화면에 보이는 비어 있지 않은 행을 1, 숨긴 2·3·5·7행을 0으로 반환합니다.
=SUBTOTAL ( 103, OFFSET ( $A$2, {0;1;2;3;4;5;6}, 0 ) )
= {0;0;1;0;1;0;1}
A4·A6·A8만 화면에 보여 각각 1입니다. XLOOKUP 함수가 첫 번째 보이는 값을 찾습니다
XLOOKUP 함수는 표시 배열에서 첫 번째 1을 찾고, 같은 위치에 있는 A4의 값을 반환합니다.
=XLOOKUP ( 1, {0;0;1;0;1;0;1}, $A$2:$A$8, "" )
= "오하늘"
첫 번째 1과 같은 위치의 값입니다. 02레거시 배열수식
SUBTOTAL 함수가 보이는 행의 번호를 추리고 INDIRECT 함수가 가장 앞선 셀의 값을 반환합니다.
레거시 배열수식의 예제 시트에서 F1 셀이 오하늘을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
ROW 함수가 시작셀부터의 간격을 계산합니다
ROW 함수는 A2:A8의 행 번호에서 시작셀 A2의 행 번호를 빼서 0부터 6까지의 간격을 반환합니다.
=ROW ( $A$2:$A$8 ) - ROW ( $A$2 )
= {0;1;2;3;4;5;6}
A2에서 각 셀까지 이동할 행 수입니다. SUBTOTAL 함수가 보이는 행을 표시합니다
OFFSET 함수는 A2에서 행 간격만큼 내려가 A2:A8을 한 셀씩 참조합니다. SUBTOTAL 함수의 103은 화면에 보이는 비어 있지 않은 행을 1, 숨긴 2·3·5·7행을 0으로 반환합니다.
=SUBTOTAL ( 103, OFFSET ( $A$2, {0;1;2;3;4;5;6}, 0 ) )
= {0;0;1;0;1;0;1}
A4·A6·A8만 화면에 보여 각각 1입니다. IF 함수와 MIN 함수가 첫 행 번호를 찾습니다
IF 함수는 표시 배열이 1인 위치에만 행 번호를 남기고, MIN 함수는 남은 행 번호 중 가장 작은 4를 반환합니다.
=IF ( {0;0;1;0;1;0;1}, ROW ( $A$2:$A$8 ) )
= {FALSE;FALSE;4;FALSE;6;FALSE;8}
A4·A6·A8의 행 번호만 남은 배열입니다. =MIN ( {FALSE;FALSE;4;FALSE;6;FALSE;8} )
= 4
화면에 보이는 첫 번째 셀의 행 번호입니다. INDIRECT 함수가 첫 셀의 값을 반환합니다
ADDRESS 함수와 SUBSTITUTE 함수는 시작셀의 열 문자 A를 만들고 행 번호 4를 이어 붙여 A4를 만듭니다. 이후 INDIRECT 함수가 A4를 실제 셀 참조로 바꾸어 값을 반환합니다.
=SUBSTITUTE ( ADDRESS ( 1, COLUMN ( $A$2 ), 4 ), 1, "" ) & 4
= "A4"
열 문자와 행 번호를 합친 참조 문자열입니다. =INDIRECT ( "A4" )
= "오하늘"
A4 셀의 값입니다. 
두번째 값은 아래 수식을 사용해보세요.
{ =INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN($시작셀),4),1,"")&SMALL(IF(SUBTOTAL(3,OFFSET($시작셀,ROW($범위)-ROW($시작셀),0)),ROW($범위)),2)) }