실무 위키›응용 공식›엑셀 필터 후 자동 순번 매기기 공식

엑셀 필터 후 자동 순번 매기기 공식

지원 버전 자세히 보기

엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

SUBTOTAL 함수와 AGGREGATE 함수로 필터를 적용한 뒤 화면에 보이는 행에만 끊김 없는 순번을 매기는 공식입니다.

예제파일 다운로드

예제 미리보기 크게 보기

인수 설명

01SUBTOTAL 함수 공식 (모든 버전)

=SUBTOTAL ( 103, $기준셀:기준셀 )

인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)

인수구분설명

기준셀필수순번을 매길 항목 범위의 시작셀입니다. 순번 출력셀을 지정하면 순환참조가 발생하므로, 항목의 첫 셀을 절대참조로 입력합니다.

A2=SUBTOTAL(103,$B$2:B2)

순번

항목

화면 상태

금액

미래상사

표시

120

한빛유통

표시

드림문구

표시

다온상회

표시

110

온누리상사

표시

105

화면 상태가 '숨김'인 4행과 7행을 숨기면, A2:A8 셀은 보이는 다섯 항목에 1부터 5까지 순번을 매깁니다. 숨긴 행의 수식 결과는 직전 순번을 유지합니다.

02AGGREGATE 함수 공식 (엑셀 2010 이후)

=AGGREGATE ( 3, 5, $기준셀:INDIRECT ( "R"&ROW ( )&"C"&COLUMN ( $기준셀 ), FALSE ) )

=AGGREGATE ( 2, 5, $기준셀:INDIRECT ( "R"&ROW ( )&"C"&COLUMN ( $기준셀 ), FALSE ) )

=AGGREGATE ( 3, 7, $기준셀:INDIRECT ( "R"&ROW ( )&"C"&COLUMN ( $기준셀 ), FALSE ) )

인수 설명 자세히 보기

인수구분설명

기준셀필수확장 집계를 시작할 범위의 첫 셀입니다. 첫 번째·세 번째 구문은 항목열의 B2를 사용하고, 숫자만 세는 두 번째 구문은 숫자열의 D2를 사용합니다. 순번 출력셀을 지정하면 순환참조가 발생합니다.

A2=AGGREGATE(3,5,$B$2:INDIRECT("R"&ROW()&"C"&COLUMN($B$2),FALSE))

순번

항목

화면 상태

금액

미래상사

표시

120

한빛유통

표시

드림문구

표시

다온상회

표시

110

온누리상사

표시

105

화면 상태가 '숨김'인 4행과 7행을 숨기면, 기본식은 A2:A8 셀에 1, 2, 2, 3, 4, 4, 5를 반환합니다. 숫자만 세는 두 번째 구문은 기준셀을 D2로 바꾸며, 세 번째 구문의 옵션 7은 숨긴 행과 오류를 함께 제외합니다.

이 공식이 사용하는 함수

SUBTOTAL 함수 AGGREGATE 함수 INDIRECT 함수 ROW 함수 COLUMN 함수

동작 원리

02AGGREGATE 함수 공식 (엑셀 2010 이후)

ROW 함수와 COLUMN 함수에서 얻은 행·열 번호로 INDIRECT 함수가 확장범위를 만들고, AGGREGATE 함수는 숨긴 행을 제외해 순번을 계산합니다.

엑셀 2010 이후 AGGREGATE 함수 공식의 예제 시트에서 A8 셀이 5를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

ROW 함수와 COLUMN 함수가 끝셀 주소를 만듭니다

A8 셀에서는 ROW 함수가 현재 행 번호 8을, COLUMN 함수가 기준셀 B2의 열 번호 2를 반환합니다. 두 값을 R1C1 형식으로 연결하면 R8C2가 됩니다.

="R"&ROW ( )&"C"&COLUMN ( $B$2 )

= "R8C2" 현재 행의 항목 셀 B8을 가리키는 주소입니다.

INDIRECT 함수가 확장범위를 만듭니다

INDIRECT 함수는 R8C2를 B8 참조로 바꿉니다. 이 참조를 절대참조 시작셀 B2와 연결하면 현재 행까지 늘어나는 B2:B8 범위가 됩니다. INDIRECT 함수는 시트를 다시 계산할 때마다 갱신되는 휘발성 함수입니다.

=$B$2:INDIRECT ( "R8C2", FALSE )

= $B$2:$B$8 기준셀부터 현재 행까지 늘어난 참조 범위입니다.

AGGREGATE 함수가 보이는 항목만 셉니다

계산방식 3은 비어 있지 않은 셀을 세고 옵션 5는 숨긴 행을 제외합니다. B2:B8에서 숨긴 4행과 7행을 빼면 화면에 보이는 항목이 다섯 개이므로 A8 셀은 5를 반환합니다.

=AGGREGATE ( 3, 5, $B$2:$B$8 )

= 5 숨긴 두 행을 제외한 항목 개수입니다.

5 (5개 평가)

박성철 2020.05.08 11:33

SUBTOTAL(3,범위) 절대참조 넣어주시면 편하게 지정 할 수 있습니다!

굴레악 2020.12.08 18:32

SUBTOTAL(103, $기준셀:기준셀)*1
표만들기 표에서 적용하면 요거는 가끔 마지막 목록쯤에서 제대로 안 나타나네요 ㅡㅡ'
중복 표기 되면서요.
그래서 새로 자동채우기를 해야 정상 순번이 됩니다.
그리고 마지막에 *1 유무에 따라 달라지나요?
기존에 없이 사용했는데 오류나서 *1 넣어봤는데 같은 증상이 나오네요.