5년 연속 IT분야 베스트셀러! 「 진짜쓰는 실무엑셀 」로 2026년 공부 끝내기 오빠두엑셀 `2026 무료 챌린지` 오픈! 완주하고 수료증 받아가세요! 엑셀이 막히셨나요? Q&A 게시판에서 바로 해결하세요.
메뉴
실무 위키›응용 공식›엑셀 필터 후 자동 순번 매기기 공식

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

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

필터로 남은 행에 끊김 없는 순번을 매기는 공식입니다.

예제파일 다운로드
엑셀 필터 후 자동 순번 매기기 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

01SUBTOTAL 함수 간단 공식

=SUBTOTAL ( 103, $기준셀:기준셀 ) * 1
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
기준셀필수순번을 매길 항목 범위의 시작셀입니다. 순번 출력셀을 지정하면 순환참조가 발생하므로, 항목의 첫 셀을 절대참조로 입력합니다.
A2=SUBTOTAL(103,$B$2:B2)*1
A
B
C
D
E
1
순번
항목
화면 상태
금액
2
1
미래상사
표시
120
3
2
한빛유통
표시
85
3
드림문구
표시
95
6
4
다온상회
표시
110
5
온누리상사
표시
105
9
화면 상태가 '숨김'인 TSV 4·7행을 숨기면, A2:A8 셀은 보이는 다섯 항목에 1부터 5까지 순번을 매깁니다. 숨긴 행의 수식 결과는 직전 순번을 유지합니다.

02Excel 2010 이후 AGGREGATE 함수 공식

=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))
A
B
C
D
E
1
순번
항목
화면 상태
금액
2
1
미래상사
표시
120
3
2
한빛유통
표시
85
3
드림문구
표시
95
6
4
다온상회
표시
110
5
온누리상사
표시
105
9
화면 상태가 '숨김'인 TSV 4·7행을 숨기면, 기본식은 A2:A8 셀에 1, 2, 2, 3, 4, 4, 5를 반환합니다. 숫자만 세는 두 번째 구문은 기준셀을 D2로 바꾸며, 세 번째 구문의 옵션 7은 숨긴 행과 오류를 함께 제외합니다.
이 공식이 사용하는 함수
SUBTOTAL 함수 AGGREGATE 함수 INDIRECT 함수 ROW 함수 COLUMN 함수

동작 원리

02Excel 2010 이후 AGGREGATE 함수 공식

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

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

1

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

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

="R"&ROW ( )&"C"&COLUMN ( $B$2 )
= "R8C2" 현재 행의 항목 셀 B8을 가리키는 주소입니다.
2

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

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

=$B$2:INDIRECT ( "R8C2", FALSE )
= $B$2:$B$8 기준셀부터 현재 행까지 늘어난 참조 범위입니다.
3

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

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

=AGGREGATE ( 3, 5, $B$2:$B$8 )
= 5 숨긴 두 행을 제외한 항목 개수입니다.

댓글 6

댓글 6
5 (5개 평가)
박성철
박성철 2020.05.08 11:33
SUBTOTAL(3,범위) 절대참조 넣어주시면 편하게 지정 할 수 있습니다!
굴레악
굴레악 2020.12.08 18:32
SUBTOTAL(103, $기준셀:기준셀)*1
표만들기 표에서 적용하면 요거는 가끔 마지막 목록쯤에서 제대로 안 나타나네요 ㅡㅡ'
중복 표기 되면서요.
그래서 새로 자동채우기를 해야 정상 순번이 됩니다.
그리고 마지막에 *1 유무에 따라 달라지나요?
기존에 없이 사용했는데 오류나서 *1 넣어봤는데 같은 증상이 나오네요.
미니니니
미니니니 2021.09.05 16:29
와~~ 댓글 이런거 잘 안남기는데
정말 편하게 잘 사용했습니다!!!!!!!!
여기 사이트 자주 들어올 것 같아요 넘넘 감사해요!! 짱좋다정말
고슴도치
고슴도치 2023.01.17 09:56
예제 파일이 다운로드가 안됩니다 ㅠㅠ
오빠두엑셀
오빠두엑셀 작성자 2023.01.17 15:49
안녕하세요.
잠시 다운로드 설정에 오류가 있었습니다.
문제를 해결하였으니 다시 확인해보시겠어요?^^ 감사합니다.
강민준🤗
강민준🤗 2024.08.11 20:17
좋은 강의 감사합니다🙇‍♂️
스크랩 완료