엑셀 필터된 데이터의 합 구하기 (subtotal 함수)

포스트 목차.

• 01. 엑셀에서의 필터
• 02. 엑셀에서 행 숨기기
• 03. 엑셀에서 합 구하기
• 04. Subtotal 함수
• 05. 결론

SUBTOTAL 함수를 사용하여 필터된 데이터의 합이 궁금한 사람은 04로 이동하자. 

 

 

 

00. 사용할 데이터

 

본 포스트에서 사용할 데이터는 다음과 같다.

 

 

01. 엑셀에서의 필터

엑셀은 필요한 데이터만 걸러내는 필터 기능을 제공한다.  

 

필터 기능을 사용하기 위한 작업 순서는 다음과 같다.

 

1) 필터를 적용할 데이터를 선택한다.

 

2) 데이터 > 필터를 선택한다.

필터 설정 방법

필터 설정 결과

 

 

3) 제목행에 있는 열이름의 화살표 버튼을 눌러서 필터를 적용한다.

 

위의 예에서 부개체의 필터 버튼을 선택 후 A를 선택한 결과는 다음과 같다.

 

필터에서 특정한 데이터를 선택

 

필터 결과

 

부개체의 값이 A인 데이터만 화면에 보여지는 것을 확인할 수 있다.

또한 좌측의 행번호를 보면 기존의 행번호가 유지되는 상태에서 필터를 거치지 못한 데이터들이 숨김 처리된 것을 확인할 수 있다.

 

즉 필터는 원하는 데이터 외의 다른 데이터는 숨김처리 하는 기능이라고 생각할 수 있다.

 

02. 엑셀에서 행 숨기기

엑셀에서는 사용자가 임의로 행이나 열을 숨길 수 있다.

 

위의 예제로 돌아가서 특정 행을 숨기는 작업을 해보자.

 

NO 6, 7, 8 (행번호 12, 13, 14)를 숨기려고 한다.

숨길 행 번호에 마우스를 가져가면 화살표 모양으로 변경된다. 이 때 마우스를 클릭하면 행이 선택된다. 

행 번호를 클릭한 상태에서 마우스를 드래그하거나, 컨트롤 키를 누른 상태에서 여러 행 번호를 클릭하면 복수의 행을 선택하는 것이 가능하다.

 

숨기고자하는 행이 선택된 상태에서 마우스 우클릭 하여 선택한 행 숨기기를 클릭하면, 행을 숨길 수 있다.  (열의 경우도 같은 방법으로 숨길 수 있다.)

 

특정 행을 숨긴 결과는 다음과 같다. 11번행 다음에 15번 행이 나오므로 12번행에서 14번행까지는 숨겨졌다는 것을 알 수 있다.

 

 

03. 엑셀에서 합 구하기

엑셀에서  데이터의 합을 구하는 방법은 여러가지가 있을 수 있지만,  대부분의 사람들이 가장 먼저 생각나는 함수는 SUM함수일 것이다.

 

(1) SUM 함수 사용하여 합을 구해보자. 

위의 그림처럼  SUM함수를 이용해서 G7부터 G18까지의 합을 구하여 셀H4에 표기해보자.

 

셀H4에 다음과 같은 수식을 입력하면 무정란 유정란을 포함하여 생상된 모든 달걀의 수가 구해진다. 

 

=SUM(G7:G18)

SUM 함수 사용하기

 

G열에있는 숫자를 모두 더한 값인 24로 셀H4가 채워지는 것을 확인

 

여기서 한 가지 의문이 들 수 있다. 필터를 걸거나, 행 숨기기를 적용해서, 화면에 보이는 데이터의 수가 달라진 경우 SUM 함수의 결과는 어떻게 될까? 결론부터 말하면 SUM 함수의 결과는 데이터가 화면에 보이고 말고와는 상관없이 일정하다는 것이다. 

 

(2) 필터를 적용하여 SUM 함수의 결과값에 변화가 생기는지 확인해보자.

 

부개체 A로 필터를 설정한 결과

 

(3) 행 숨기기를 적용하여 SUM 함수의 결과값에 변화가 생기는지 확인해보자. 

 

 

화면에 보이는 데이터와는 상관없이 SUM함수의 결과는 일정하다는 것을 확인할 수 있다.

 

04. SUBTOTAL 함수

사용자에 따라서 화면에 보이는 데이터 만의 합이 궁금한 사람도 있을 것이다.  이러한 작업을 할 수 있는 방법은 여러가지가 있지만 본 포스트에서는 SUBTOTAL 함수를 사용하여 화면에 보이는 데이터의 합을 구해보려고 한다.

 

(1) SUBTOTAL 함수의 인자

 

SUBTOTAL 함수는 합 외에 여러가지 작업을 할 수 있는 함수다.

 - 첫 번째 인자인 Function_num의 값에 따라 수행할 작업이 결정된다.  

 - 두 번째 인자부터는 SUM함수와 동일하게 특정 연산을 할 셀의 참조 또는 영역이 들어간다.

 

첫번째 인자인 Function_num은 1에서 11까지의 값 또는 101에서 111까지의 값이 들어갈 수 있다.   숨겨진 행을 무시하길 원할 경우 100번대 숫자를 지정하고, 숨겨진 행까지 연산에 포함되길 원할 경우에는 1에서 11까지의 숫자를 지정하면 된다.

 

숨겨진 행 포함	숨겨진 행 무시	연산 종류
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

 

(2) SUBTOTAL 함수로 합 구하기

 

본 포스트에서 관심있는 부분은 SUM이기 때문에 FunnctionNum 값이 9, 109인 경우만 다루려고 한다. 다른 숫자도 유사한 함수의 결과만 다를 뿐 유사한 통계함수이기 때문에 같은 방식으로 접근하면 된다.

 

SUM 함수와 동일한 영역에 대해 SUBTOTAL 함수를 적용해보자.

 

위의 그림처럼 첫 번째 인자가 9인 경우와 109인 경우를 나눠서 각각 셀 H1, H2에 표기하려고 한다.

 

셀 H1과 H2에 다음 수식을 입력해보자.

 

H1:  =SUBTOTAL(9, G7:G18)

H2:  =SUBTOTAL(109, G7:G18)

 

현재는 수식에 관여하는 모든 셀이 보이기 때문에, SUM 함수의 결과와 SubTotal 함수의 결과가 동일하다.

 

(1) 행 숨기기 적용 후의 결과

 

먼저 첫 번째 인자 9와 109의 차이를 알기 위해서 행13~15를 숨겨보자.

첫번째 인자가 9 인 경우 숨겨진 셀의 내용까지 포함하여 SUM 연산을 하여 SUM 함수와 같은 결과를 반환한다. 첫번째 인자가 109인 경우 숨겨질 셀의 내용은 연산에 포함시키지 않아서 SUM 함수와 다른 결과를 반환한다.

 

(2) 필터만 적용한 경우

 

위에서 적용한 숨기기를 취소한 후에, 이번에는 부개체 A 에 대한 필터를 걸어보자.

 

 

SUBTOTAL 함수의 결과는 화면에 보이는 데이터에 대한 값만 더해진 것을 확인 할 수 있다.

 

(3) 필터 적용 후 숨기기 시도

 

위에서 적용된 필터를 그대로 유지한 상태에서 9행을 숨기기 해보자.

 

이미 필터가 걸려있기 때문에 두 함수 모두 화면에 보이는 데이터에 대한 SUM만 하는 것을 확인할 수 있다.

 

05. 결론

 사용자에 따라 필터에 해당하는 데이터의 합이 궁금한 상황이 발생할 수 있다. 이런 경우에는  SUBTOTAL 함수의 9,109 인자를 셋팅하여 사용하면 필터 상태에 따라 SUM 연산을 할 수 있다. 본 포스트에서는 SUM만 다루고 있지만,  SUBTOTAL 함수에서 제공하는 모든 연산을 필터 결과에 따라 동적으로 수행하는 것이 가능하다.