엑셀 EXCEL 자동 순번 매기기(자동 채우기, ROW 함수, SUBTOTAL 함수)

 

표에서 가장 많이 사용하는 데이터라 한다면 1순위가 순번 매기기라고 할 수 있습니다. 1부터 100 혹은 1000 등 관련 데이터가 몇 번째에 있는지 확인할 때 기준이 되는 데이터죠. 그렇기 때문에 보통의 실무 상황에서 순번을 데이터상에 표시하는 경우가 많습니다. 없으면 오히려 어색할 정도죠.
 
이번 포스팅에서는 순번을 매길 때 가장 많이 사용하는 세 가지의 기능을 한번 알아보도록 하겠습니다. 각 기능마다 장단점이 분명히 존재하기 때문에 상황에 따라 사용하시면 될 것 같습니다.
 
 

• 자동 채우기 기능 사용
• ROW 함수 사용
• SUBTOTAL 함수 사용

 
 

1. 자동 채우기 기능 사용

자동 채우기를 사용한 순번 매기기는 가장 간단하면서도 보편적인 방법입니다. 자동 채우기라고 한다면 어느 정도의 패턴을 가지고 패턴대로 순번을 매기는 것이라고 말할 수 있습니다.
 
예를 들어 아래의 A,B,D 등 상품이 있는 표에 순번을 자동 채우기로 매기기로 해보겠습니다.
 

순번 채우기 예시

 
먼저, 순번에 1,2이라는 패턴을 만들어 준 후에 범위를 1,2까지 지정하고 2번 셀의 오른쪽 하단을 더블 클릭해 주세요. 
그러면 자동 채우기 기능을 통해서 1,2,3,...,5번까지의 순번이 자동적으로 채워지게 됩니다.
 

자동 채우기를 통한 순번 매기기

 
 
이 방법에 대한 단점이라고 한다면 중간의 데이터가 없어지게 될 경우 순번이 자동으로 업데이트가 되지 않습니다.
 
예를 들어 위의 표에서 5번 행(순번 3)이 없어졌다고 가정해 보겠습니다.
 

자동 채우기 단점

 
순번의 3번만 삭제되고 순번은 1,2,4,5, 등 순번이 업데이트가 되지 않습니다. 그렇기 때문에 수동적으로 다시 한번 자동 채우기를 통해서 업데이트를 시켜줘야 합니다. 하지만 ROW 함수를 통해서 우리는 이러한 단점을 극복할 수 있습니다.
 
 
 

2. ROW 함수 사용

자동 채우기의 단점을 극복하기 위해 자동적으로 순번이 업데이트될 수 있도록 ROW 함수를 사용해 보도록 하겠습니다.
 
ROW 함수라고 하면 행에 대한 번호를 출력하는 함수로 예를 들어 B3셀에서 ROW 함수를 입력하게 되면 3이라는 값이 출력됩니다. 3번째에 있는 행이라는 얘기죠. 그렇기 때문에 ROW 함수를 사용해서 쉽게 순번을 매기기가 가능합니다.
 
앞의 1번 예시를 그대로 가져와 적용해 보도록 하겠습니다.
 
먼저 B3 셀에 가서 아래와 같이 입력해 주세요
 

=ROW()-2

 
위 식의 의미는 B3 셀의 행 값에 -2를 하라는 얘기입니다. 이미 눈치채셨겠지만 그렇게 하면 값은 1이 됩니다. 즉, 첫 번째 순번이라는 얘기죠.
 
만약 첫 번째 순번이 B2 셀이라고 가정한다면 ROW 함수는 =ROW()-1을 하면 첫 번째 순번이 되겠지요.
 
이후, 자동 채우기 혹은 B3:B7까지 범위를 잡으시고 CTRL+D 단축키를 사용하면 
 

ROW 함수를 사용한 연속 순번 채우기

 
1부터 5까지의 순번이 매겨지게 됩니다.
 
이 방식은 앞선 자동 채우기의 단점을 극복할 수 있습니다. 예를 들어 B5 셀(순번 3, 상품 D)의 값을 지운다고 가정하겠습니다.
 

ROW 함수를 사용한 연속 순번 채우기

 
ROW 함수를 사용하였기 때문에 B5 셀(순번 3)의 값을 지워도 밑에 있던 상품 E의 순번이 B5 셀이 되어버리고 함수를 통해 자동적으로 순번 3으로 업데이트가 되어 버립니다.
 
하지만 이 방법도 단점은 있습니다. 예를 들어 필터를 했을 때죠.
 
만약, 필터를 통해 상품 B, D를 숨겨 보도록 하겠습니다. 결과는... 아시다시피 ROW 함수를 사용하여도 열의 값이 고정이기 때문에 순번이 업데이트 되지를 않습니다.
 

ROW 함수를 사용한 연속 순번 채우기 단점

 
그래서 우리는 SUBTOTAL 함수를 사용해서 필터링했을 때도 자동으로 순번이 매겨지도록 업데이트되는 데이터 환경을 만들 수 있습니다.
 
 

 

300x250

 

3. SUBTOTAL 함수 사용

앞선 포스팅에서 SUBTOTAL 함수에 대해서 설명한 적이 있는데요. 이 방법을 보시기 전에 SUBTOTAL 함수에 대한 포스팅을 한번 보시는 것도 추천드립니다.
 
 

엑셀 EXCEL 화면에 보이는 셀만 계산 SUBTOTAL 함수

 
SUBTOTOTAL 함수를 사용할 때에는 기능번호 103, COUNTA를 사용해서 순번을 매길 수 있습니다.
COUNTA의 경우 값이 채워져 있는 셀의 개수를 세는 함수입니다.
 
앞선 방식과 동일하게 1번의 예시를 가지고 설명해 보도록 하겠습니다.
 
첫 번째 순번이 되는 B3 셀에 아래의 수식을 입력해 주세요
 

=SUBTOTAL(103,$C$3:C3)

 
여기서 103은 COUNTA 함수로 비어있지 않은 셀의 범위를 세겠다는 함수이며 뒤의 $C$3:C3 은 C3을 절대값으로 하고 C3,4,5, 등 아래로 내려가는 지정 범위를 일컫습니다. 다시 얘기드리자면 C3부터 시작해서 밑으로 내려가는 채워진 셀의 개수를 세는 함수를 뜻합니다.
 
이후 자동 채우기 기능을 통해 B3에서 B7까지 자동으로 채우든 아니면 B3:B7까지 범위 지정 이후 CTRL+D 를 통해 아래 복사를 하시면 연속된 순번을 만들 수 있습니다.
 

SUBTOTAL 함수를 사용한 연속 순번 채우기

 
이 방식은 SUBTOTAL을 사용하여 연속된 순번을 매겼기 때문에 셀을 숨기든 혹은 필터링으로 숨겨졌든 자동으로 순번이 업데이트됩니다.
 
예를 들어 B5 열(순번 3, 상품 D)을 숨기는 조건 그리고 필터링을 해서 필터를 설정한 조건을 보도록 하겠습니다.
 

SUBTOTAL 함수를 사용한 연속 순번 채우기

 
셀 숨기기를 하였을 때와 필터링으로 셀이 숨겨졌을 때 모두 순번이 자동적으로 업데이트되는 것을 확인할 수 있습니다.
 
하지만 이 방법도 약간의 단점은 있습니다. SUBTOTAL 함수를 설정할 때 아셨겠지만 상품의 개수(C열)에 만약 빈셀이 있을 경우, COUNTA 함수를 사용했기 때문에 순번이 누락되는 경우가 발생합니다. 따라서 기준을 잡을 때는 반드시 빈셀이 없는 열을 기준으로 SUBTOTAL 함수를 사용해야 한다는 것을 인지하셔야 합니다.
 
 
 

4. 줄이며

지금까지 자동 채우기, ROW 함수, SUBTOTAL 함수를 사용해서 자동으로 순번을 매기는 방법을 알아보았는데요.
각각의 방법마다 장단점이 존재합니다. 
 
자동 채우기는 적용 방법은 쉽지만 데이터 삭제 시 자동 업데이트가 안된다는 단점
ROW 함수는 순번이 자동 업데이트가 되지만 필터를 사용할 시 순번 업데이트가 필요할 때는 안된다는 단점
SUBTOTAL 함수는 모든 조건에서 순번이 자동 업데이트가 되는 장점이 있지만 기준 열에 데이터가 없을 경우, 빈셀일 경우 순번이 누락될 수 있는 단점 등이 있습니다.
 
무엇 하나가 나은 방식이라고 콕 집어 말씀드릴 수 없지만 보통은 ROW 함수를 많이 사용하는 편입니다. 필터 시에 순번을 업데이트하는 경우가 많지 않을뿐더러 자동 채우기 기능을 사용하면 자동 업데이트가 안되기 때문이죠.
 
사실 엑셀에 순번 매기기에는 앞서 소개드렸던 3가지의 방법 외에도 다른 방법들이 많이 있습니다. 하지만 보편적으로 많이 쓰이는 방법에 대해서 나열해 드린 것이고 이 방법들이 엑셀 사용하실 때 많은 도움이 되셨으면 합니다. 아마 나중에는 상황에 맞는 본인의 방식을 많이 사용하시게 되실 겁니다. 그때까지는 이 블로그가 많은 도움이 되었으면 합니다.