[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] 엑셀에서 부분합을 쉽게 계산하기
정보 업무명 : 엑셀에서 부분합 쉽게 계산하기 작성자 : 박진만 작성일 : 2020-02-17 설 명 : 수정이력 : 내용 [특징] 엑셀에서 부분합 쉽게 계산하는 방법 [기능] SUM 함수의 한계 SUBTOTAL 함수로 부분합 구하기..
shlee1990.tistory.com
요점
-
필터와 숨겨진행을 지정하여 "보이는 행"만 합계 하고자하는 경우 SUBTOTAL 함수를 사용한다.
-
SUBTOTAL 함수는 총합뿐만 아니라 평균과 계산 등 자주 사용하는 계산 방법을 인수로 지정할 수있다.
참고 문헌
[논문]
- 없음
[보고서]
- 없음
[URL]
- 없음
문의사항
[기상학/프로그래밍 언어]
- sangho.lee.1990@gmail.com
[해양학/천문학/빅데이터]
- saimang0804@gmail.com
본 블로그는 파트너스 활동을 통해 일정액의 수수료를 제공받을 수 있음