엑셀 필터 적용 시 번호 순서 안 깨지게 매기는 SUBTOTAL 함수 활용법

안녕하세요! 직장인의 칼퇴를 돕는 블로그, 시간절약연구소입니다.

엑셀로 업무를 하다 보면 데이터가 너무 많아서 특정한 조건만 골라보기 위해 필터 기능을 정말 자주 사용하게 됩니다. 그런데 여기서 아주 치명적인 문제점이 하나 발생합니다. 전체 데이터에 ‘1, 2, 3, 4…’ 순서대로 일련번호를 매겨두었는데, 필터를 적용해 데이터를 걸러내면 번호가 뒤죽박죽 섞이거나 중간 번호가 뻥뻥 뚫려서 나타나는 현상입니다.

보고서나 출력을 앞두고 번호가 1, 3, 7, 12번 이런 식으로 나오면 다시 수동으로 번호를 고쳐야 하느라 막대한 시간 손실이 발생하죠. 오늘은 이 문제를 단 1초 만에 해결하고, 필터를 걸어도 번호가 1부터 차례대로 완벽하게 정렬되도록 만드는 SUBTOTAL 함수의 활용법을 완벽하게 파헤쳐 보겠습니다.

Table of Contents

1. 왜 필터를 쓰면 엑셀 번호가 깨질까요? (기존 방식의 문제점)

우리가 보통 엑셀에서 번호를 매길 때 가장 먼저 떠올리는 방법은 무엇인가요? 바로 첫 번째 셀에 1을 입력하고, 두 번째 셀에 =A1+1 식을 적은 뒤 아래로 쭉 드래그(자동 채우기)하는 방식입니다.

이 방식은 데이터가 가만히 있을 때는 아주 훌륭하게 작동합니다. 하지만 필터를 적용하여 특정 행(Row)들을 숨기게 되면 숨겨진 행까지 포함해서 수식이 계산되기 때문에, 화면에 보이는 행 기준으로 번호가 매겨지지 않고 원래 행 번호 기반으로 유지되거나 꼬이게 됩니다. 즉, 엑셀에게 “화면에 보이는 데이터만 대상으로 순번을 매겨줘”라고 명령을 내려야 하는 것입니다.

2. 해결책: SUBTOTAL 함수란 무엇인가요?

SUBTOTAL 함수는 지정한 범위 내에서 합계, 평균, 개수 등 다양한 통계를 구할 수 있는 함수입니다. 일반적인 SUM이나 COUNT 함수와 결정적인 차이점이 있습니다. 바로 ‘사용자가 필터로 숨긴 행을 계산에서 제외한다’는 강력한 특성입니다.

SUBTOTAL 함수의 기본 구조는 다음과 같습니다.

=SUBTOTAL(통계_번호, 범위)

여기서 첫 번째 인수인 ‘통계_번호’에 어떤 숫자를 넣느냐에 따라 합계가 될 수도 있고, 개수가 될 수도 있습니다. 특히 순번을 매길 때 핵심이 되는 것은 바로 COUNTA(개수 세기) 기능입니다.

함수 번호 (숨김 포함)	함수 번호 (숨김 제외)	연산 종류	설명
1	101	AVERAGE	평균 구하기
3	103	COUNTA	비어 있지 않은 셀 개수 (순번 매기기에 사용)
9	109	SUM	합계 구하기

표에서 보시다시피 필터로 숨겨진 항목을 제외하고 계산하려면 함수 번호 앞에 ‘100번대’를 사용해야 합니다. 따라서 개수를 세는 COUNTA 함수는 3 대신 103을 사용합니다.

3. 실전 예시: SUBTOTAL로 번호 깨짐 방지하기

그렇다면 실제로 엑셀 시트에 이 함수를 어떻게 적용해야 할까요? 단계별로 아주 쉽게 설명해 드리겠습니다. 예를 들어, A열이 순번이고 B열에 과일 이름, C열에 판매량이 있다고 가정해 보겠습니다.

순번을 입력할 첫 번째 셀(예: A2 셀)을 클릭합니다.
다음과 같은 수식을 입력합니다:
=SUBTOTAL(103, $B$2:B2)
엔터를 친 후, 데이터가 있는 아래쪽 행까지 수식을 복사(드래그)합니다.

여기서 가장 중요한 포인트는 범위 설정입니다. 시작 셀인 $B$2는 달러($) 기호로 절대 참조를 걸어주어 고정하고, 끝나는 셀인 B2는 상대 참조로 둡니다. 이렇게 하면 아래로 행이 내려갈수록 범위가 $B$2:B3, $B$2:B4 형태로 점점 늘어나게 됩니다.

이 상태에서 과일 필터를 적용하여 ‘사과’만 골라보세요. 놀랍게도 사과 행들만 화면에 나타나면서 순번이 1, 2, 3, 4번으로 끊김 없이 깔끔하게 재정렬되는 것을 확인할 수 있습니다. 필터를 해제해도 원래 전체 데이터 기준 순번으로 완벽하게 돌아옵니다.

4. SUBTOTAL 활용 시 주의사항 및 꿀팁

SUBTOTAL 함수를 사용할 때 몇 가지 유의해야 할 점이 있습니다.

– 빈 셀이 없어야 합니다: COUNTA(103) 함수는 텍스트나 숫자가 입력된 셀의 개수를 세기 때문에, 기준이 되는 열(예: B열 이름)에 빈 셀이 있으면 번호가 밀리거나 누락될 수 있습니다. 반드시 데이터가 채워져 있는 열을 범위로 지정해 주세요.

– 표(Table) 기능과 함께 쓰면 더 쉽습니다: 엑셀의 [삽입] – [표] 기능을 이용해 데이터를 표 형식으로 만들면, SUBTOTAL 함수를 쓸 필요도 없이 필터를 걸었을 때 자동으로 행 번호가 순차적으로 매겨지는 편리함도 누릴 수 있습니다. 하지만 기존 서식을 유지해야 하거나 복잡한 양식이라면 오늘 배운 SUBTOTAL 함수가 정답입니다.

오늘 준비한 엑셀 팁은 여기까지입니다. 매번 필터 쓸 때마다 번호를 수동으로 고치느라 야근하셨던 분들이라면, 오늘부터 꼭 SUBTOTAL 함수를 적용해 보시길 바랍니다. 업무 속도가 최소 10배는 빨라질 것입니다.

시간절약연구소는 앞으로도 직장인들의 칼퇴를 위한 유익하고 실용적인 엑셀 노하우를 전달해 드리겠습니다. 감사합니다!