■ 목차
1. 함수 설명
2. Subtotla 함수의 구문 / 사용방법
3. Subtotal 함수 사용해 보기 - Sum 함수
** 핵심 요약
1. 함수 설명
- Subtotal 함수는 숨겨진 행을 제외하고 현재 보이는 셀만의 합계 등을 구해주는 함수입니다.
- 자동 필터로 필터링이 된 상태에서도 Sum 함수의 경우 숨겨진 행을 포함하여 전체 범위의 합계를 구해주지만
- Subtotal 함수를 사용하게 되면 숨겨진 행은 제외하고 현재 보이는 셀만의 합계 등을 구할 수 있습니다.
- Subtotal 함수는 영어로는 소계(小計)라는 의미이고 엑셀에서는 "부분합"이라고 합니다.
- 이름이 total이라서 합계만 구해줄 것 같지만 사실 계산에 사용할 함수의 종류를 지정할 수 있으며 Average, Counta, Max, Min, Sum 등의 다양한 계산을 할 수 있습니다.
- Sum 함수와 Subtotal 함수가 어떻게 다른지 조금 혼돈스러울 수 있는데 이번 기회에 정리하시고 Subtotal 함수를 사용하는 이유를 알아보도록 하겠습니다.
- 결론적으로는 "자동 필터"에서는 Sum 함수 대신에 Subtotal 함수를 사용하면 된다 정도로 이해하시면 될 듯 합니다.
2. Subtotal 함수의 구문 / 사용방법
-SUBTOTAL(function_num,ref1,[ref2],...)
- function_num 필수 요소입니다. 소계에 사용할 함수를 지정하는 숫자 1-11 또는 101-111입니다. 1-11에는 수동으로 숨겨진 행이 포함되는 반면 101-111은 해당 행을 제외합니다. 필터링된 셀은 항상 제외됩니다.
- ref1 필수 요소입니다. 부분합을 계산할 첫 번째 명명된 범위 또는 참조입니다.
- ref2,... 선택 요소입니다. 부분합을 계산할 명명된 범위 또는 참조로서 2개에서 254개까지 지정할 수 있습니다.
- 첫 번째 인수 function_num은 계산하고자 하는 함수를 지정하는 숫자입니다.
- 숨겨진 행 포함 여부에 따라서 1 - 11, 101 - 111의 숫자로 함수를 지정하며 총 11가지 함수를 사용할 수 있습니다.
- 우리가 많이 사용하게 되는 함수는 103번 (Counta 함수)과 109번 (Sum 함수)입니다.
- 함수의 종류를 외우실 필요는 없고 함수를 입력하게 되면 풍선 도움말로 표시가 되니깐 보고 선택을 하시면 됩니다.
- 1번대 (1 ~ 11, 숨겨진 행 포함)과 100번대 (101 ~ 111, 숨겨진 행 무시)의 차이는 "숨겨진 행을 무시" 할 것인 여부입니다.
- 같은 Subtotal 함수의 Sum 계산을 사용하여도 function_num 9번을 입력하게 되면 "숨겨진 행을 포함"하여 합계를 구해주고
- 109번을 선택하게 되면 "숨겨진 행을 무시하고 ( = 보이는 셀만)" 합계를 구해주게 됩니다.
- 보통 엑셀에서 얘기하는 "숨겨진 행"이라는 게 2가지가 있는데 Subtotal 함수의 경우 각각을 조금 다르게 취급합니다.
- ① 행 숨기기 메뉴로 숨겨진 행이 있고 ② 자동 필터의 필터링 작업을 하게 되면 숨겨지는 행이 있습니다.
- Subtotal 함수에서 "숨겨진 행을 무시" 한다는 의미는 ① 행 숨기기 메뉴로 숨겨진 행을 얘기합니다.
- 그리고 ② 자동 필터의 필터링 작업으로 숨겨진 행은 Subtotal 함수의 경우 1번대, 100번대 모두 "무시"를 합니다. ^^
- 조금 혼돈스러울 수 있는데 아래 설명에서 깔끔하게 정리를 해드리겠습니다.
- 지금 우리는 숨기기 메뉴/필터 메뉴의 숨긴 행이 계산에서는 "다르다"라는 정도로 이해를 하시면 됩니다.
- 두 번째 인수부터는 일반적인 Sum, Average, Counta 등의 함수를 사용할 때 사용하는 입력값 (참조 셀 범위)이 되고
- 첫 번째 인수 (function_num)을 선택해 주는 것을 제외하고는 선택된 함수의 기존 사용법과 동일합니다.
- Subtotal 함수가 "숨겨진 행"의 포함여부로 계산을 해준다고 얘기를 드렸는데
- 이게 "숨겨진 행(세로)"의 경우만 무시하고 계산하는 게 가능하고 "숨겨진 열(가로)"의 경우는 지원하지 않습니다.
- "열(가로)의 숨기기도 함수 적용은 가능한데 숨긴 열을 "무시" 해주지 않는다는 의미입니다. ^^
3. Subtotal 함수 사용해 보기 - Sum 함수
- 아래 월별 지점별 판매 현황에서 "강동"지점만을 필터링하여 판매량 합계를 구해보고
- Sum 함수와 Subtotal 함수의 9, 109번의 차이를 비교해 보겠습니다.
- 1) 자동 필터 설정 및 "강동" 지점 필터링 설정
- 자동 필터 설정은 표의 임의의 셀을 선택한 다음 ① 단축키 (Ctrl + Shift + L)을 사용하거나
- ② 메뉴 패스 (리본 메뉴) : "데이터" 탭 > "정렬 및 필터" 그룹 > "필터"를 클릭해 주시면 됩니다.
- 2) 함수입력 - Sum, Subtotal 함수
- 판매량 "D7:D11" 전체 영역을 대상으로 Sum 함수를 적용하고 Subtotal 함수는 합계 계산을 위하여
- function_num 인수는 " 9 (숨겨진 행 포함)"와 "109 (숨겨진 행 제외)" 인수로 2개의 함수를 그림과 같이 입력해 줍니다.
- 3) "필터링 조건 설정" 및 합계 수식 확인
- 음, 결과가 좀 이상듯이 나왔습니다.
- 옵션 9의 Sum (숨겨진 행 포함)와 109의 Sum(숨겨진 행 제외)이 다르다고 하면서 모두 제외가 되어 있네요. ^^
- 앞에 얘기드린 것과 같이 자동필터의 숨겨진 행의 경우 Subtotal 함수는 9번, 109번 모두 무시를 하기 때문입니다.
- 다음은 조금 다른 게 "행 숨기기"메뉴를 사용해서 결과가 어떻게 나오는지 보도록 하겠습니다.
- 이번에는 우리가 생각하는 결과가 나온 듯합니다.
- Subtotal 함수의 9번 (숨겨진 행 포함), 109번 (숨겨진 행 무시)가 원하는 "포함/무시" 기능을 제대로 기능을 하고 계산해줍니다. ^^
- 복잡해 보이는데 결과적으로 우리가 알아야 하는 첫 번째는 ① Sum 함수는 어떤 경우이든 숨겨진 셀을 "포함"하여 계산한다.입니다.
- Sum 함수의 적용 수식 "=SUM(D6:D10)"에서 "D6:D10"은 연속된 셀 주소를 표시하는 형식이고 A7;A8;A9;A10;A11의 셀 참조를 대신하는 표기입니다.
- 그리고 셀 참조는 숨겨진 셀뿐만 아니라 다른 시트, 심지어 다른 통합 문서까지 모두 참조를 하는 게 원칙이고
- 이런 원칙에서 예외로 보이는 셀만을 계산하게 해주는 함수가 Subtotal 함수라고 보셔야 합니다.
- 두 번째는 ② Subtotal_109는 어떤 경우든 숨겨진 셀을 "무시"하고 계산합니다.
- "행 숨기기 기능", "필터링으로 숨기기"의 경우에 따라서 "포함/무시" 기능이 조금 혼돈이 되지만
- 우리는 그냥 "숨겨진 셀을 무시"를 하고 싶으면 Subtotal 함수에서 100번대의 계산 기능들을 사용하면 됩니다.
- 숨겨진 셀은 포함하고 필터링한 셀은 무시하고 싶은 경우 1번대 수식을 사용하는데 이런 게 필요한 경우가 거의 없다고 보시면 됩니다.
- 그냥 Sum 함수 혹은 Subtotal_109 둘 중에 하나 쓰신다고 생각을 하시면 됩니다.
- Subtotal 함수를 사용하는 이유는 거의 대부분이 "자동 필터"에서 숨겨진 셀을 제외하고 개수, 합계를 구하고자 하는 목적입니다.
- 그리고 Subtotal 함수의 원래 기능은 "부분합" 즉, 중간중간에 합계를 구해서 집계를 하는 기능이고
- 그래서 Subtotal 함수가 계산할 때 무시하는 게 한 개 더 있는데 바로 "Subtotal 함수"를 적용한 수식의 값입니다.
- 저희는 이런저런 함수의 세세한 기능을 모두 아실 필요 없습니다. ^^
- 그냥 숨겨진 것 포함 = Sum 함수, 어떻게 숨겼던 숨겨진 것 무시 = Subtotal_109라고 구분하시면 됩니다.
- "작업 표시 줄"에도 합계, 평균 등의 마우스로 선택된 범위의 계산값을 보여 주는데 여기는 어떤 함수를 기준으로 값들을 보여 줄가요? ^^
- Subtotal 함수와 동일한 값을 보여 줍니다. 한 번 확인해 보시기 바랍니다.
- 다만 Subtotal 함수로 계산한 중간값이 있는 경우는 조금 다름니다.ㅠ
** 핵심 요약 : Subtotal 함수 사용법 - 감춰진 셀을 제외하고 보이는 셀만 합계, 개수를 구해보자
1. 함수 설명
- Subtotal 함수는 숨겨진 행을 제외하고 현재 보이는 셀만의 합계 등을 구해주는 함수이다.
- 자동 필터에서 필터링한 후에도 Sum 함수는 숨겨진 행을 모두 포함하여 계산해 주기 때문에
- 필터링된 결과를 기준으로 숨겨진 행을 제외하고 계산을 하고 싶으면 Subtotal 함수를 사용해야 한다.
2. Subtotla 함수의 구문 / 사용방법
-SUBTOTAL(function_num, ref1, [ref2],...)
- function_num 필수 요소입니다. 소계에 사용할 함수를 지정하는 숫자 1-11 또는 101-111입니다. 1-11에는 수동으로 숨겨진 행이 포함되는 반면 101-111은 해당 행을 제외합니다. 필터링된 셀은 항상 제외됩니다.
- ref1 필수 요소입니다. 부분합을 계산할 첫 번째 명명된 범위 또는 참조입니다.
- ref2,... 선택 요소입니다. 부분합을 계산할 명명된 범위 또는 참조로서 2개에서 254개까지 지정할 수 있습니다.
- 첫 번째 인수 function_num은 계산하고자 하는 함수를 지정하는 숫자이다.
- 1번대 (1 ~ 11, 숨겨진 행 포함)와 100번대 (101 ~ 111, 숨겨진 행 무시)로 구분하여 사용한다.
- 여기서 "숨겨진 행"은 ① "행 숨기기 메뉴"로 숨겨진 행과 ② "자동 필터의 필터링 작업"으로 숨겨진 행 중에서 ①을 의미한다.
- 두 번째 인수부터는 일반적인 Sum, Average, Counta 등의 함수를 사용할 때 사용하는 입력값 (참조 셀 범위)이다.
- 숨긴 행만 무시 가능하고 숨긴 열 무시는 지원하지 않는다.
3. Subtotal 함수 사용해 보기 - Sum 함수
- 1) 자동 필터 설정 및 "강동" 지점 필터링 설정
- 자동 필터 설정 : 단축키 (Ctrl + Shift + L)
- 리본 메뉴 : "데이터" 탭 > "정렬 및 필터" 그룹 > "필터"
- 2) 함수입력 - Sum, Subtotal 함수
- Sum, Subtotal_9, Subtotal_109 함수로 숨겨진 행에 따라 어떻게 다른지 비교해 보자
- 3) "필터링 조건 설정" 및 합계 수식 확인
- ① Sum 함수는 어떤 경우이든 숨겨진 셀을 "포함"하여 계산한다.
- ② Subtotal_109는 어떤 경우든 숨겨진 셀 "무시"하고 계산한다.
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글