[Excel] 엑셀에서 필터링 후 남은 행의 합계 구하기

 정보

• 업무명     : 엑셀에서 필터링 후 남은 행의 합계 구하기

• 작성자     : 박진만

• 작성일     : 2020-02-17

• 설   명      :

• 수정이력 :

 

 내용

[특징]

• 엑셀에서 필터링 후 보이는 행만의 합계를 구하는 방법 소개

 

[기능]

• 필터링 후 보이는 행의 합계를 계산하는 SUBTOTAL 함수

• 상단  SUBTOTAL 함수의 인자 설명

• 필터링 뿐 아니라 열 숨김 까지 고려하는 방법

• 기타 SUBTOTAL 함수의 인자 설명

• SUBTOTAL 함수의 편리한 기능 (부분합)

• 요점

 

[사용법]

• 없음

 

[사용 OS]

• Window 10

 

[사용 언어]

• Excel v2010

 

 세부 내용

• 엑셀에서 총 합을 구하는 경우는 매우 흔하다.

• 이 때 SUM 함수는 지정된 영역 (행과 열)만을 계산하는 것이 기본 옵션이다.

• 따라서 자동 필터에 의한 검색 (필터) 또는 숨겨진 행이 있는 경우 보통은 보이지 않는 줄까지 더해지게 된다.

• 그러나, 보이지 않는 행까지 총 합을 구하고 싶지 않는 경우도 흔히 발생한다.

• 가령 표를 만들고 있으면 자동 필터를 걸거나 줄을 숨길 수 있지만 정작 보이지 않는 행을 제외하고 계산을하려고해도 어떻게해야할지 모르는 경우가 많다

• 본 글에서는, 행을 필터링 한 이후 보이지 않는 행은 총 합계의 계산에서 제외하는 방법을 소개하고자 한다.

 

[ 필터링 후 보이는 행의 합계를 계산하는 SUBTOTAL 함수]

• 합계 함수의 대표격인 SUM 함수는, 필터 및 숨기기 등으로 보이지 않게 된 행까지 더해버리는 문제가 있다.

• 그것과는 반대로 보이고있는 행의 합계 만주는 것이 SUBTOTAL 함수이다.

• 아래의 엑셀을 예시로 들어보자.

 

• 아직 필터에 의한 검색하고 있지 않기 때문에, SUM 함수, SUBTOTAL 함수 모두 동일한 합계가 계산되고 있다.

• 이 상태에서 "남자"를 필터 한 결과는 아래와 같아진다.

 

 

• 즉 SUM 함수는 보이지 않는 줄까지 합계를 계산하기 때문에 필터 전과 동일한 금액으로 되어있다.

• 그러나, SUBTOTAL 함수는 보이고있는 행만의 합계를 계산하는 것을 알 수 있다.

 

[상단  SUBTOTAL 함수의 인자 설명]

• 필터에서 보이고있는 행 합계 만 싶을 때 SUBTOTAL 함수는 다음과 같이 구성되어 있다.

• 예를 들어, 아래의 엑셀처럼 셀 "C4"에서 "C9"까지의 합계를 구하는 경우를 본다면.

  • =SUBTOTAL(9, C4:C9)

• 첫번째 파라미터는 "9", 두 번째 인수에 총 계산 범위 (C4: C9)를 지정한다.

• 첫 번째 인수는 "계산 방법"을 지정하는 인수로, "9"는 "SUM"을 구하는 계산 방법 이다.

• 이제 SUM 함수 '= SUM (C4: C9) "과 같은 계산을하지만 SUM 함수의 차이는"필터에 보이지 않게 된 행은 제외하고 합계 한다" 라는 것이다.

 

[필터링 뿐 아니라 열 숨김 까지 고려하는 방법]

• 이어는 필터가 아닌 행숨김 이후 표시되는 행만 합계하는 방법이다.

• 아래의 엑셀에서는 행 4와 5를 "숨기기"한 결과이다.

 

• 이 경우 필터뿐만 아니라 "숨겨지지 않은 행"만 총합이 되어있는 것을 알 수 있다.

• 숨겨진 행을 제외한 행을 합계하는 SUBTOTAL 함수는 다음과 같이 입력된다.

  • =SUBTOTAL(109, C4:C9)

• 위와 비교했을 때 첫 번째 인수가 다른것을 확인할 수 있다.

• 즉 "9"에 100을 더한 "109"을 지정할 수 있다.

• 여기서 "109"란 "숨겨진 행을 제외한 총합" 이라는 의미이다.

• 열 숨김까지 고려 한 경우 첫 번째 인수의 계산 방법은 "109"를 지정해야하지만, 필터링만 존재한다 하더라도"109" 라고 지정할 수 있다.

[기타 SUBTOTAL 함수의 인자 설명]

• 마지막으로, SUBTOTAL 함수의 상세한 계산 방법 (인수).

• SUBTOTAL 함수는 만능 계산 함수라고 할 수 있다.

• 첫 번째 인수에 "9"(또는 "109")을 지정하여 SUM 함수처럼 총합을 구할 수 있으며, 마찬가지로 첫 번째 인수에 "1"(또는 "101")을 지정하면 AVERAGE 함수 같이 평균을 구할 수 있다.

• SUBTOTAL 함수에서 사용할 수있는 첫 번째 인수의 계산 방법은 다음과 같다.

숨겨진 행을 포함하여 계산	숨겨진 행을 제외하고 계산	계산내용
1	101	AVARAGE (평균)
2	102	COUNT (숫자가 입력된 셀의 개수)
3	103	COUNTA (입력된 셀의 개수)
4	104	MAX (최대값)
5	105	MIN (최소값)
9	109	SUM (합계)

• 이외에도 PRODUCT (곱하기), STDEV (표준 편차) 등의 계산도 가능하다.

 

[SUBTOTAL 함수의 편리한 기능 (부분합)]

• 또한 해당 함수는 표 부분합을 사용하는 경우에 편리하게 사용할 수 있다.

• 하단의 링크를 참조할 수 있다.

https://shlee1990.tistory.com/497

[Excel] 엑셀에서 부분합을 쉽게 계산하기

 

 요점

• 필터와 숨겨진행을 지정하여 "보이는 행"만 합계 하고자하는 경우 SUBTOTAL 함수를 사용한다.

• SUBTOTAL 함수는 총합뿐만 아니라 평균과 계산 등 자주 사용하는 계산 방법을 인수로 지정할 수있다.

 참고 문헌

[논문]

• 없음

[보고서]

• 없음

[URL]

• 없음

 

 문의사항

[기상학/프로그래밍 언어]

• sangho.lee.1990@gmail.com

[해양학/천문학/빅데이터]

• saimang0804@gmail.com

 

 

 

본 블로그는 파트너스 활동을 통해 일정액의 수수료를 제공받을 수 있음