엑셀 SUBTOTAL 함수 사용법: 숨겨진 행·필터링된 데이터 소계 계산하기

엑셀 워크시트에 숨겨진 행, 필터링된 데이터 또는 그룹화된 데이터가 포함되어 있을 때는 SUBTOTAL(소계) 함수를 활용하는 것이 좋습니다. SUBTOTAL 함수는 계산 시 숨겨진 값을 포함하거나 제외할 수 있어 유연한 데이터 분석이 가능합니다. 단순히 합계를 구하는 것뿐만 아니라 평균, 최댓값, 최솟값, 표준편차, 분산 등 다양한 통계 값을 한 번에 계산할 수도 있습니다. 지금부터 엑셀에서 소계를 삽입하는 방법을 자세히 알아보겠습니다.

적용 버전: 이 글의 내용은 Microsoft 365용 Excel, Excel 2019 및 Excel 2016에 적용됩니다.

SUBTOTAL 함수 구문 이해하기

SUBTOTAL 함수는 워크시트의 값을 다양한 방식으로 요약하는 데 사용됩니다. 특히 숨겨진 행이 있는데 그 행까지 계산에 포함하고 싶을 때 매우 유용합니다.

SUBTOTAL 함수의 구문은 다음과 같습니다.
SUBTOTAL(function_num, ref1, ref2,…)

function_num 인수

function_num 인수는 필수 항목으로, 소계 계산에 사용할 수학 연산의 종류를 지정합니다. SUBTOTAL 함수는 숫자를 더하거나, 선택한 숫자의 평균을 구하거나, 범위에서 최댓값과 최솟값을 찾거나, 선택한 범위의 값 개수를 세는 등 다양한 작업을 수행할 수 있습니다.

참고로 SUBTOTAL 함수는 데이터가 없는 셀과 숫자가 아닌 값이 들어 있는 셀은 자동으로 무시합니다.

function_num 인수는 결과에 숨겨진 행을 포함할지 제외할지에 따라 달라집니다. 여기서 말하는 숨겨진 행에는 수동으로 숨긴 행과 필터로 숨겨진 행이 모두 해당됩니다.

function_num 인수 목록

주의: function_num 1~11은 '숨기기' 명령으로 숨긴 행의 값만 포함합니다. '필터' 명령을 사용하는 경우 SUBTOTAL 계산에서 필터로 숨겨진 결과는 제외됩니다.

ref1, ref2,… 인수

ref1 인수는 필수 항목입니다. 선택한 function_num 인수의 결과를 계산하는 데 사용되는 셀을 의미하며, 값, 단일 셀 또는 셀 범위를 지정할 수 있습니다.

ref2,… 인수는 선택 사항으로, 계산에 추가로 포함할 셀들을 나타냅니다.

숨겨진 행에 SUBTOTAL 함수 사용하기

엑셀 함수는 수식 입력줄에 직접 입력하거나 함수 인수 대화 상자를 통해 입력할 수 있습니다. 수식 입력줄을 사용해 함수를 직접 입력하는 방법을 설명하기 위해, 다음 예제에서는 COUNT function_num 인수를 사용하여 보이는 행의 값 개수와 보이는 행 + 숨겨진 행 전체의 값 개수를 각각 구해보겠습니다.

SUBTOTAL 함수로 워크시트의 행 개수를 세는 방법은 다음과 같습니다.

1. 여러 행의 데이터가 있는 워크시트를 준비합니다.
2. 보이는 행의 개수가 표시될 셀을 선택합니다.
3. 수식 입력줄에 =SUBTOTAL을 입력합니다. 입력하는 동안 Excel이 함수를 제안하면 SUBTOTAL 함수를 더블클릭합니다.
   팁: 함수 인수 대화 상자를 사용하려면 수식 탭 → 수학/삼각 → SUBTOTAL을 차례로 선택합니다.
4. 나타나는 드롭다운 메뉴에서 102 – COUNT function_num 인수를 더블클릭합니다.
5. 쉼표(,)를 입력합니다.
6. 워크시트에서 수식에 포함할 셀을 선택합니다.
7. Enter 키를 누르면 2단계에서 선택한 셀에 결과가 표시됩니다.
8. 보이는 행과 숨겨진 행 모두의 개수가 표시될 셀을 선택합니다.
9. 수식 입력줄에 =SUBTOTAL을 입력하고 제안되는 SUBTOTAL 함수를 더블클릭합니다.
10. 드롭다운 메뉴에서 2 – COUNT function_num 인수를 더블클릭한 후 쉼표(,)를 입력합니다.
11. 워크시트에서 수식에 포함할 셀을 선택한 후 Enter 키를 누릅니다.
12. 데이터 행 중 일부를 숨깁니다. 이 예제에서는 매출액이 $100,000 미만인 행만 숨겼습니다.

필터링된 데이터에 SUBTOTAL 함수 사용하기

필터링된 데이터에 SUBTOTAL 함수를 적용하면 필터로 제외된 행의 데이터는 자동으로 무시됩니다. 필터 조건이 변경될 때마다 함수가 재계산되어 현재 보이는 행의 소계를 실시간으로 표시합니다.

데이터를 필터링하면서 계산 결과의 변화를 확인하는 방법은 다음과 같습니다.

1. SUBTOTAL 수식을 만듭니다. 예를 들어 필터링된 데이터의 소계와 평균값을 구하는 수식을 만들어 봅니다.
   참고: 보이는 행용 function_num 인수와 숨겨진 행용 인수 중 무엇을 사용해도 상관없습니다. 필터링된 데이터에서는 두 인수 모두 동일한 결과를 제공합니다.
2. 데이터 집합에서 아무 셀이나 선택합니다.
3. 홈 탭 → 정렬 및 필터 → 필터를 선택합니다.
4. 드롭다운 화살표를 사용해 워크시트 데이터를 필터링합니다.
5. 필터 조건을 바꿀 때마다 값이 어떻게 변하는지 확인합니다.

그룹화된 데이터에 SUBTOTAL 함수 사용하기

데이터가 그룹화되어 있다면 각 그룹별로 소계를 적용한 뒤 전체 데이터 집합의 총합계(grand total)까지 한 번에 계산할 수 있습니다.

1. 데이터 집합에서 아무 셀이나 선택합니다.
2. 데이터 탭 → 소계를 선택하여 소계 대화 상자를 엽니다.
3. 그룹 기준(At each change in) 드롭다운 화살표를 클릭하고 소계를 계산할 그룹화 기준을 선택합니다.
4. 사용할 함수(Use function) 드롭다운 화살표를 클릭하고 function_num에 해당하는 함수를 선택합니다.
5. 소계 추가 대상(Add subtotal to) 목록에서 수식을 적용할 열을 선택합니다.
6. 확인을 클릭합니다.
7. 각 데이터 그룹별로 소계가 삽입되고, 데이터 집합 맨 아래에 총합계가 삽입됩니다.
8. function_num을 변경하려면 데이터 집합의 아무 셀이나 선택한 후 데이터 → 소계를 클릭하고, 소계 대화 상자에서 원하는 설정을 다시 지정하면 됩니다.