[함수] 자동으로 순서번호 정렬해주는 Subtotal함수 활용법

 

Subtotal함수는 부분합을 구하는 함수인데 그 용법이 다양합니다.

=Subtotal(Function_num, ref1, [ref2], ... )

 

인수는 필수인수 최소 2개이고 2번째 인수와 성격이 같은 인수가 1개 필수, 그 이상은 선택입니다.

 

첫번째 인수인 Function number가 중요합니다. 이 번호에 따라서 아주 많은 활용을 할 수 있습니다.

0번대와 100번대가 있는데 행 숨김을 했을때 그것을 포함하느냐 하지 않느냐의 차이입니다.

Function_num (숨겨진 행 포함)	Function_num (숨겨진 행을 무시)	함수
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

 

9번, 부분합계를 구하는 방법은 이전 포스팅을 참고해주시기 바랍니다.

 

참고글 :  2017.04.09 - [엑셀/엑셀 함수] - [엑셀함수] 필터값만 계산하고 싶을 때 사용하는 SUBTOTAL함수

[엑셀함수] 필터값만 계산하고 싶을 때 사용하는 SUBTOTAL함수

 

 

본 포스팅에서는 subtotal함수를 이용해서 자동으로 순번을 정렬해주는 방법을 소개하겠습니다.

 

 

위와 같은 데이터가 있을 때 B열의 번호는 대체로 자동채우기로 1,2,3~ 숫자 기입을 할 것입니다.

이때 남학생만 필터를 해볼게요.

 

 

이때 B열의 번호까지 필터링이 되어버렸어요.

저게 학생의 고유 번호가 아니라 단순한 순서일때 1부터 13까지 나오면 좋을 것 같아요.

이런 경우 사용하면 좋은 subtotal함수를 알려드릴게요.

 

 

비어있지 않은 셀의 갯수를 세는 COUNTA를 이용할 것이고, 이것은 SUBTOTAL함수 내에서 3번 Func_Num입니다.

첫번째 인수에는 3을 기입하시구요.

 

 

어떤 셀을 Counta해서 Subtotal을 할것인지 그 "어떤셀"을 지정해줍니다.

D3에서 D3, 이 범위에 데이터가 있는 셀의 갯수만 세주는 것입니다.

앞의 D3에 절대참조를 걸어놨으니 뒤의 D3이 바뀌면 어떻게 참조하나 볼게요.

 

 

입력셀이 밑으로 이동하면 범위가 함께 늘어나게 절대/상대참조를 걸어준 것입니다.

그럼 이제 아까처럼 필터를 해볼게요!

 

 

필터를 2개를 걸어도 화면에 필터링된 부분합만 계산해서 번호가 자동으로 바뀌는 것이죠!

두번째 인수는 꼭 D열로 하지않아도 됩니다. E열도, H열도 모두 가능하지만 중요한 것은 데이터가 비어있는 열은 선택하면 계산이 힘들겠죠.

Counta함수는 [비어있지 않은 셀의 갯수]를 세는 함수니까요.

 

 

예를 들어 이렇게 연락처처럼 정보가 비어있다면 제대로 갯수를 세지 못하고 그럼 순번이 꼬이게 되겠죠.

 

 

비어있지 않은 셀을 세다보니 비어있는 G15셀은 카운팅하지 않아버려서 중복숫자가 발생합니다.

물론 이런 오류도 함수를 사용해서 수정할 수는 있겠죠^^

하지만 전 엑셀을 이용한 업무를 할 때, 굳이굳이 복잡한 함수중첩은 지양하는 편입니다. 엑셀시트든 일이든 간편하고 심플한게 베스트라고 생각해요!

 

이상 아주 간단하게 자동으로 순번입력해주는 Subtotal함수였습니다.