엑셀: 필터 걸어도 번호(순번) 자동 생성법(subtotal함수, if함수 설명)

업무를 하다보면 데이터에 일일이

순번을 넣는 것이 은근히 귀찮은데요.

이럴때는

필터 했을 때 자동으로 번호가 수정되고

또 데이터를 추가할 때마다

번호가 자동 생성되게끔

해 놓으면 업무가 편해집니다.

위 그림처럼 필터시

번호가 자동으로 수정되고

데이터 입력시 자동으로 순번이

생성되는 함수에 대해서

알아보겠습니다.

 사용할 함수 익히기

이번에 사용할 함수는

SUBTOTAL함수와 IF함수입니다.

둘다 많이 사용하는 함수입니다.

 

SUBTOTAL 함수

 

 subtotal은 필터가 적용되는 표에서는

거의 필수로 사용됩니다.

 

-함수설명

입력한 함수종류(합계, 평균 등)에

따라서 데이터를 계산합니다.

 

 

-함수형식

=subtotal(함수번호, 참조범위)

 

 

-예시

*필터를 적용해서 화면에 보이는 값만

계산할때 subtotal함수를 주로 사용하므로

함수번호는 3자리짜리만

사용한다고 보시면 됩니다.

보통 103과 109를 가장 많이 쓰므로

두 개를 외우면 됩니다.

 

 

IF함수

 

가장 많이 쓰이고 중첩해서 쓰면

복잡한 논리도 표현이 가능합니다.

 

-함수 설명

조건을  충족하는 경우와

충족하지 않는 경우의 값을 표시한다.

 

-함수 형식

=IF(조건, 조건 충족시 값, 아닐 때 값)

 

-예시

 

 SUBTOTAL 함수 입력하기

순번 자동입력을 위해서

SUBTOTAL함수의

103(COUNTA)을 사용하겠습니다.

최종 결과는 아래와 같이 됩니다.

 원리는 [이름] 항목 셀에

문자를 넣으면

SUBTOTAL 103이

문자가 들어간 셀의 개수를 세고

셀의 개수가 곧

번호가 되는 것입니다.

 

연번이 생성될 셀에

=SUBTOTAL(103,

까지 입력합니다.

 그리고 위에 그림처럼

뒤에 범위를 마저 입력합니다.

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

 

범위가 $B$2:B2 이유를

설명 드리겠습니다.

그림을 보시면 연번이 2행부터 시작입니다.

그리고 데이터는 B열[이름]에 채워져 있습니다.

 

 

B열의 데이터 셀개수를

셀 것이기 때문에

시작이 범위는 B2부터입니다.

이 함수를 밑에 쭈욱 복사할 때

시작이 변하면 안되므로

시작은 절대값 $B$2입니다.

*앞에 달러가 붙으면 절대값*

 

함수가 입력되는 셀의 행까지

데이터 셀의 개수를 세야하므로

마지막이 B2입니다.

 

밑으로 복사하면 알아서

아래와 같이 숫자가 증가합니다.

이 함수는 가끔 오류가 발생합니다.

위의 사진에서

함수 뒤에 *1을 붙인 이유는

오류방지용입니다.

 

IF함수 입력하기

 

 =SUBTOTAL(103,$B$2:B2)*1

를 입력하면 아래와 같이 됩니다.

0이 상당히 거슬립니다.

SUBTOTAL함수를

입력해 놓은 모든 셀에

B열에 아무것도 없으니

자동으로 0이 나온 겁니다.

 

그래서 IF함수를 사용하여

지저분해 보이는 0을 없애줄 겁니다.

 

 

 

[이름] 항목(B2)에

아무것도 있지 않으면(=“”)

공란으로(“”) 표시하고

그렇지 않을 경우

SUBTOTAL(103,$B$2:B2)*1

을 표시합니다.

 

위의 말대로 IF함수를

나타내면

아래와 같습니다.

 

=IF(B2="","",SUBTOTAL(103,$B$2:B2)*1)

 

이것을 아래로 미리 복사해 놓으면 됩니다.

이렇게 함수가 입력됐으면 끝난 겁니다.

저는 한 500개 정도까지

미리 복사해 놓고 씁니다.

 

이름을 기준으로 번호를

생성하도록 했기 때문에

이름 입력할 때마다

번호가 자동생성 되며

필터 적용시 자동으로

1번부터 재생성 되는 것을

확인 하실 수 있습니다.