엑셀에서 대량의 데이터를 다룰 때 그룹별로 합계를 빠르게 확인하고 싶다면 소계(Subtotal) 기능이 가장 효과적인 해결책입니다. 이 글에서는 내장 소계 기능부터 SUBTOTAL 함수, VBA 매크로, 피벗 테이블, 파워 쿼리까지 다양한 방법으로 소계를 삽입하고 관리하는 실전 기술을 단계별로 소개합니다.
방법 1 – 엑셀에서 자동으로 소계 삽입하기
1단계: 데이터 정렬하기
- 시트 내 아무 셀이나 선택한 후 데이터 탭으로 이동하여 정렬을 클릭합니다.
- 정렬 대화 상자가 화면에 나타납니다.
- 정렬 기준 필드에서 소계를 삽입할 기준이 되는 열을 선택합니다.
- 정렬 대상은 셀 값으로 유지합니다.
- 순서는 오름차순 A~Z로 설정합니다.
- 확인을 클릭합니다.
- 이제 영업 담당자(Sales Reps) 열이 알파벳 순으로 정렬됩니다.
2단계: 소계 기능 적용하기
엑셀에 내장된 소계 기능(소계 바로가기)을 사용해 보겠습니다.
- 데이터 범위 안의 셀을 클릭한 후 데이터 탭의 그룹화 그룹에서 소계를 선택합니다.
- 소계 대화 상자가 나타나면, 여기서는 SUM 함수를 사용해 소계를 삽입합니다.
- 그룹화할 항목 필드에는 소계를 삽입할 기준 데이터를 지정합니다.
- 사용할 함수 필드에서 SUM을 선택해 총합을 계산합니다.
- 확인을 클릭합니다.
이제 엑셀에 소계가 성공적으로 삽입되며, 영업 담당자 열마다 소계와 총계가 표시됩니다.
- 왼쪽 상단 모서리의 1, 2, 3 번호 아이콘은 소계 활용의 핵심 도구입니다. 이 윤곽 기호를 통해 그룹을 관리하고 소계를 표시하거나 숨길 수 있습니다.
방법 2 – 여러 개의 소계 추가하기
2.1. 서로 다른 열에 여러 소계 삽입하기
앞서 영업 담당자 열에 소계를 적용하는 방법을 살펴봤습니다. 이번에는 카테고리(Category)를 기준으로 소계를 하나 더 추가해 보겠습니다.
- 데이터 탭에서 정렬 대화 상자를 엽니다.
- 먼저 영업 담당자를 기준으로 정렬합니다.
- 수준 추가를 클릭합니다.
- 영업 담당자로 정렬한 뒤, 두 번째 수준에서 카테고리를 기준으로 정렬합니다.
- 확인을 클릭합니다.
- 방법 1에서 설명한 대로 데이터 탭에서 소계 대화 상자를 엽니다.
- 그룹화할 항목에서 영업 담당자를 선택합니다.
- 엑셀이 영업 담당자 열에 소계를 삽입합니다.
- 다시 소계 팝업을 엽니다.
- 이번에는 카테고리가 변경될 때마다 소계가 계산되도록 설정합니다.
- 현재 소계 대체 옵션의 체크를 반드시 해제하세요. 체크된 상태로 두면 이전 소계가 지워지고 하나의 소계만 남게 됩니다.
- 확인을 클릭합니다.
이렇게 여러 번 그룹화하면서 엑셀에 소계를 삽입할 수 있습니다.
추가로 소계를 넣고 싶다면 소계 대화 상자에서 다른 그룹화 기준을 선택하면 됩니다.
2.2. 같은 열에 여러 소계 찾기
같은 열에 여러 종류의 소계를 삽입할 수도 있습니다. 총합을 구한 후 평균과 표준편차를 추가로 계산해 보겠습니다. 이를 위해 AVERAGE 함수와 StdDev(표준편차) 함수를 사용합니다.
먼저 방법 1에서 SUM 함수로 영업 담당자별 소계를 삽입한 상태에서 시작합니다.
- 소계 대화 상자를 두 번 더 엽니다.
- 첫 번째에는 AVERAGE 함수를, 두 번째에는 StdDev 함수를 선택합니다.
- AVERAGE 함수는 선택한 열의 평균값을 구합니다.
- StdDev 함수는 표준편차를 계산합니다.
이 함수들을 기반으로 데이터에 각각의 소계가 삽입됩니다.
방법 3 – 엑셀 표(Table)에 소계 삽입하기
엑셀 표에는 소계 기능을 직접 적용할 수 없습니다. 표 형식에서는 소계 기능이 작동하지 않습니다.
소계를 삽입하려면 먼저 표를 일반 범위로 변환해야 합니다.
- 표 안의 아무 셀이나 선택한 후 표 디자인 탭으로 이동하여 도구 그룹에서 범위로 변환을 선택합니다.
- 엑셀이 표를 범위로 변환할지 묻는 메시지를 표시하면 예를 클릭해 확인합니다.
이제 엑셀이 표를 일반 범위로 변환했으므로, 소계 기능을 적용해 소계를 삽입할 수 있습니다.
방법 4 – 필터와 함께 SUBTOTAL 함수 적용하기
지금까지는 소계 기능을 사용했습니다. 엑셀에는 데이터에 소계를 추가하는 SUBTOTAL 함수도 별도로 제공됩니다. 특히 필터링된 데이터를 다룰 때 이 함수가 매우 유용합니다.
SUBTOTAL 함수의 구문은 다음과 같습니다:
SUBTOTAL(function_num, ref1, [ref2], …)
첫 번째 인수인 function_num은 서로 다른 함수를 나타내는 숫자(1~9 및 101~109)로 지정됩니다.
| 인수 | 기능 |
|---|---|
| 1~9 | 필터로 숨겨진 행은 무시하지만, 수동으로 숨긴 행은 포함합니다. |
| 101~109 | 필터로 숨겨진 행과 수동으로 숨긴 행을 모두 무시합니다. |
4.1. 필터링된 행에서 소계 구하기
아래와 같은 SUBTOTAL 수식을 사용해 소계를 삽입했습니다.
첫 번째 인수로 9를 사용했기 때문에 필터링된 셀은 계산에서 제외됩니다.
- 영업 담당자 열의 드롭다운 화살표를 클릭하고 이름(예: Adam)을 선택해 해당 이름으로 필터링합니다.
함수가 필터링된 셀에 대해서만 소계를 계산하고 나머지는 무시하는 것을 확인할 수 있습니다.
아래 이미지는 인수 9와 109를 사용했을 때 SUBTOTAL 함수의 결과 차이를 보여줍니다.
4.2. 수동으로 숨긴 행에서 소계 구하기
수동으로 숨긴 행에 대한 SUBTOTAL 함수 결과를 확인해 보겠습니다. 행 번호를 마우스 오른쪽 버튼으로 클릭하고 컨텍스트 메뉴에서 숨기기를 선택하면 행을 숨길 수 있습니다.
인수 9와 109의 결과를 비교해 보세요. 인수 9는 수동으로 숨긴 행의 셀까지 포함하지만, 인수 109는 해당 셀들을 무시합니다.
방법 5 – VBA 코드로 소계 구하기
- ALT+F11 키를 눌러 VBA 편집기(Visual Basic Editor) 창을 실행합니다.
- 도구 모음에서 삽입을 클릭한 후 모듈을 선택합니다.
- 모듈 창에 아래 VBA 코드를 입력합니다.
코드:
Sub Calculate_Subtotal()
Dim iColumn As Integer
Dim iValue As Integer
Dim xValue As Integer
Application.ScreenUpdating = False
iValue = 5
xValue = iValue
Range("B5").CurrentRegion.Offset(1).Sort Range("B6"), 1
Do While Range("B" & iValue) <> ""
If Range("B" & iValue) <> Range("B" & (iValue + 1)) Then
Rows(iValue + 1).Insert
Range("B" & (iValue + 1)) = "Subtotal " & Range("B" & iValue).Value
For iColumn = 7 To 8 'Columns to Calculate Sum
Cells(iValue + 1, iColumn).Formula = "=SUM(R" & xValue & "C:R" & iValue & "C)"
Next iColumn
Range(Cells(iValue + 1, 1), Cells(iValue + 1, 8)).Font.Bold = True
iValue = iValue + 2
xValue = iValue
Else
iValue = iValue + 1
End If
Loop
Application.ScreenUpdating = True
End Sub
- 실행(Run) 버튼을 클릭해 VBA 코드를 실행합니다.
엑셀이 영업 담당자를 기준으로 데이터에 소계를 자동으로 삽입합니다.
방법 6 – 피벗 테이블에 소계 추가하기
피벗 테이블에는 네 개의 필드가 있으며, 우리 데이터를 다음과 같이 배치했습니다:
1. 필터: 영업 담당자
- 열: 없음
- 행: 카테고리, 제품
- 값: 수량, 단가, 총 가격, 이익
- 디자인 탭으로 이동하여 레이아웃 그룹의 소계 옵션 드롭다운을 클릭합니다.
- 드롭다운 메뉴에서 그룹 하단에 모든 소계 표시를 선택합니다.
피벗 테이블이 행 레이블을 기준으로 각 그룹 하단에 소계를 삽입하고, 표 맨 아래에는 전체 총계를 추가합니다.
방법 7 – 파워 쿼리(Power Query)로 소계 삽입하기
파워 쿼리를 사용하면 그룹별 소계를 손쉽게 삽입할 수 있습니다.
- 데이터 범위 안의 셀을 클릭한 후 데이터 탭으로 이동하여 데이터 가져오기 및 변환에서 테이블/범위에서를 클릭합니다.
- 테이블 만들기 대화 상자에서 확인을 클릭합니다.
- 엑셀이 파워 쿼리 편집기 창으로 전환됩니다.
- 처리할 열(예: 영업 담당자, 카테고리)를 선택한 후 변환 탭에서 그룹화 기준 아이콘을 클릭합니다.
- 그룹화 기준 대화 상자에서 새 열 이름을 입력합니다.
- 수행할 연산 유형(예: SUM)을 선택합니다.
- 계산을 적용할 열(예: 총 가격)을 선택합니다.
- 더 많은 그룹을 추가하려면 집계 추가를 클릭합니다.
- 이익의 소계를 계산하려면 열 필드에서 이익을, 연산 필드에서 합계를 선택하고 집계 이름을 지정합니다(예: Profit).
- 확인을 클릭합니다.
- 영업 담당자와 카테고리를 기준으로 소계가 삽입된 것을 확인할 수 있습니다.
- 홈 탭으로 이동하여 닫기 및 로드 드롭다운을 클릭한 후 닫기 및 로드를 선택합니다.
파워 쿼리 편집기가 소계가 포함된 그룹화된 데이터를 엑셀 워크시트로 불러옵니다.
엑셀에서 소계 제거하는 방법
- 데이터 탭에서 소계 대화 상자를 연 후 모두 제거를 클릭합니다.
- 그룹과 소계가 모두 제거되어 데이터가 일반 범위로 돌아갑니다.
참고: 소계를 제거하면 그룹화는 사라지지만, 데이터는 정렬된 상태 그대로 유지됩니다. 즉, 정렬 이전의 원본 상태로 되돌아가지 않습니다.
자주 묻는 질문(FAQ)
1. 엑셀에서 소계가 제거되지 않는 이유는?
여러 가지 원인으로 소계 기능이 작동하지 않을 수 있습니다.
i) 보호된 워크시트: 시트가 암호로 보호되어 있다면 먼저 시트 보호를 해제해야 합니다.
ii) 공유 통합 문서: 여러 사용자가 통합 문서를 공유 중이라면 공유를 해제하거나 통합 문서 상태를 확인해야 할 수 있습니다.
iii) 사용자 지정 수식 또는 매크로: 소계가 사용자 지정 수식이나 매크로로 추가된 경우, 이를 수정하거나 삭제해야 제거할 수 있습니다.
2. 엑셀에서 소계를 축소하거나 확장하려면 어떻게 하나요?
그룹화된 윤곽 기능을 사용하면 됩니다. 엑셀은 자동으로 윤곽 기호를 추가해 소계 그룹을 축소하거나 확장할 수 있게 해줍니다. 그룹 행 옆의 빼기(-) 또는 더하기(+) 기호를 클릭하면 각각 소계를 축소하거나 확장할 수 있습니다.
3. 데이터가 변경될 때 소계를 자동으로 업데이트할 수 있나요?
네, 가능합니다. 데이터가 변경되면 소계가 자동으로 업데이트되도록 엑셀을 설정할 수 있습니다. 또한 소계 행을 마우스 오른쪽 버튼으로 클릭하고 컨텍스트 메뉴에서 '새로 고침'을 선택해 수동으로 갱신할 수도 있습니다. 자동 업데이트가 되지 않는다면 통합 문서의 수식 자동 계산 옵션이 켜져 있는지 확인하세요.
4. AGGREGATE 함수와 SUBTOTAL 함수는 같은 기능인가요?
아니요, 구문이 비슷하지만 AGGREGATE 함수와 SUBTOTAL 함수에는 차이점이 있습니다. AGGREGATE는 오류 값을 무시하는 등 더 다양한 옵션을 제공합니다.
핵심 요약
- 엑셀의 '소계' 기능을 사용하면 지정한 열을 기준으로 데이터 그룹별 소계를 삽입할 수 있습니다.
- 소계를 삽입하려면 데이터 범위를 선택한 후 '데이터' 탭의 '그룹화' 그룹에서 '소계' 버튼을 클릭합니다.
- 여러 열을 기준으로 선택하면 여러 수준의 소계를 추가할 수 있습니다.
- 빼기(-)와 더하기(+) 기호로 표시되는 그룹화 윤곽 기능을 통해 소계를 축소하거나 확장할 수 있습니다.
- 엑셀 표나 동적 이름 범위를 활용하면 데이터가 변경될 때 소계가 자동으로 업데이트됩니다.
연습용 통합 문서 다운로드
연습용 통합 문서는 여기에서 다운로드할 수 있습니다.
<< 엑셀 소계로 돌아가기 | 엑셀 배우기