엑셀 필터 후 자동 넘버링 (표에서도 적용 가능) :: 엑셀함수공식
엑셀 필터 후 자동 넘버링 목차 바로가기
함수 공식
SUBTOTAL 간단공식
=SUBTOTAL(103, $기준셀:기준셀)*1
'대부분의 상황에서 사용가능한 간단공식입니다.
'대부분의 상황에서 사용가능한 간단공식입니다.
AGGREGATE 응용공식
=AGGREGATE(3,5,$기준셀:INDIRECT("R"&ROW()&"C"&COLUMN($기준셀),FALSE))
'다양한 조건으로 순번을 적용해야 할 경우 사용하는 응용공식입니다.
'다양한 조건으로 순번을 적용해야 할 경우 사용하는 응용공식입니다.
인수 설명
| 인수 | 설명 |
| $기준셀 | 필터 후 자동 넘버링을 매기도록 참조할 항목범위의 시작셀입니다. 순번을 출력하는 범위가 아닌, 순번을 매길 항목범위의 시작셀을 절대참조로 입력합니다. |
공식 사용예제
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 필터 후 자동 순번 매기기 공식예제파일
사용된 기초 함수
엑셀 필터 후 자동 넘버링 공식 알아보기
공식 설명
본 공식은 필터링 또는 행/열 숨기기 후에도 출력된 결과에 상관없이 자동으로 순번을 매기는 공식입니다. 공식에 들어가는 인수인 '$기준셀'은 넘버링을 출력하는 범위가 아닌, 넘버링을 매길 항목범위의 시작셀로 입력하는 것에 주의합니다. (값이 출력될 범위를 기준셀로 입력할 경우 순환참조 오류가 발생합니다.)
SUBTOTAL 함수를 사용한 기본 공식의 자세한 사용법은 아래 영상 강의를 참고해주세요.
해당 공식은 AGGREGATE 함수를 중심으로 동작합니다. AGGREGATE 함수는 숨겨진 행 또는 오류를 무시하고 다양한 방식으로 데이터를 집계할 수 있는 유용한 함수입니다. AGGREGATE 함수에 대한 자세한 설명은 아래 관련 포스트를 참고하세요.
공식의 동작원리
- INDIRECT 함수를 사용하여 기준셀로부터 확장되는 확장범위를 작성합니다.
만약 $A$1:INDIRECT("A"&ROW()) 를 입력하고 아래방향으로 자동채우기 할 경우, 출력되는 범위는 아래와 같습니다.첫번째: $A$1:INDIRECT("R"&ROW(1)&"C"&COLUMN($A$1),FALSE) $A$1:$A$1
두번째: $A$1:INDIRECT("R"&ROW(2)&"C"&COLUMN($A$1),FALSE) $A$1:$A$2
세번째: $A$1:INDIRECT("R"&ROW(3)&"C"&COLUMN($A$1),FALSE) $A$1:$A$3
... - AGGREGATE 함수를 사용하여, '숨겨진 셀은 제외한 채 비어있지 않은 셀의 개수'를 셉니다.
따라서, 위에서 작성한 확장범위에서 숨겨진 셀을 제외하고 '화면에 보이는 비어있지 않은 셀'의 개수를 계산하게 되면서, 필터링 후 자동 넘버링 공식이 마무리 됩니다.=AGGREGATE(3,5,$A$1:INDIRECT("R"&ROW()&"C"&COLUMN($A$1),FALSE))
' 8행이라고 가정합니다. (ROW()=8)
=AGGREGATE(3,5,$A$1:$A$8)
' $A$1:$A$8 에서 화면에 보이는 비어있지 않은 셀의 개수를 셉니다.
자주묻는질문
질문1. 숫자가 입력된 항목만 기준으로 자동 순번을 매기고 싶어요.
AGGREGATE 함수에서 첫번째 인수인 계산방식을 2로 변경하면, 숫자가 입력된 항목을 대상으로만 자동 순번을 매길 수도 있습니다.
=AGGREGATE(2,5,$기준셀:INDIRECT("R"&ROW()&"C"&COLUMN($기준셀),FALSE))
' AGGREGATE 함수의 첫번째 인수를 2로 변경하면, COUNT 함수로 집계 되면서 '숫자'를 대상으로만 자동 순번을 매깁니다.질문2. 오류가 입력된 항목은 제외하고 자동 넘버링 작업도 가능한가요?
AGGREGATE 함수의 두번째 인수인 집계방식을 7로 변경하면, 숨겨진 셀 뿐만 아니라 오류를 제외하고 자동 넘버링을 할 수도 있습니다.
=AGGREGATE(3,7,$기준셀:INDIRECT("R"&ROW()&"C"&COLUMN($기준셀),FALSE))
' AGGREGATE 함수의 두번째 인수를 7로 변경하면, 숨겨진 셀과 오류값을 제외하고 데이터를 집계합니다. - INDIRECT 함수를 사용하여 기준셀로부터 확장되는 확장범위를 작성합니다.
