Excel에서 소계 추가하는 방법: 수식, 피벗 테이블 및 Python

수천 개의 행이 있는 스프레드시트는 구조적인 요약 없이는 읽기가 매우 어렵습니다. Excel에서 데이터를 효과적으로 정리하기 위해 부분합(Subtotal) 기능은 가장 강력하고 영향력 있는 도구 중 하나입니다.

회계 장부를 관리하는 회계사든, 지역별 실적을 분석하는 영업 관리자든, 캠페인 지표를 추적하는 마케터든, Excel에서 부분합을 추가하는 방법을 배우면 수동 작업 시간을 크게 단축할 수 있습니다.

이 가이드에서는 Excel에서 부분합을 만드는 네 가지 방법을 살펴봅니다:

1. 내장 부분합 도구 (가장 빠른 방법)
2. Excel 부분합(SUBTOTAL) 함수 (동적 계산용)
3. 피벗 테이블 (현대적인 대안)
4. Python 자동화 (일괄 처리용)

Excel의 부분합이란 무엇인가요?

부분합은 워크시트 내의 관련 데이터 그룹 사이에 삽입되는 요약 계산입니다. 이를 통해 다음을 수행할 수 있습니다:

• 크고 정리되지 않은 데이터 세트를 논리적이고 읽기 쉬운 범주로 분류
• 합계, 평균, 개수 및 기타 집계 값을 한눈에 확인
• 그룹을 확장하거나 축소하여 특정 섹션에 집중
• 상세 데이터와 요약 데이터를 한 시트에서 동시에 유지

방법 1: 내장 부분합 도구 사용

Excel의 부분합 기능은 기본적인 보고 요구 사항에 대해 부분합을 추가하는 가장 빠른 방법입니다. 자동으로 요약 행을 삽입하고 개요 보기를 생성하여 데이터 세부 수준에 따라 데이터를 축소하거나 확장할 수 있습니다.

1단계: 데이터 정렬

부분합은 특정 열의 값 변경을 기준으로 데이터를 그룹화하므로, 먼저 해당 열을 정렬해야 합니다.

• 그룹화할 열(예: "지역")을 클릭합니다.
• 데이터 탭으로 이동하여 텍스트 오름차순 정렬 또는 내림차순 정렬을 클릭합니다.

2단계: 부분합 도구 열기

• 데이터 세트 내의 아무 셀이나 선택합니다.
• 다시 데이터 탭으로 이동하여 부분합(개요 그룹 내)을 클릭합니다.

3단계: 부분합 설정 구성

세 가지 중요한 옵션이 포함된 부분합 대화 상자가 나타납니다:

• 그룹화할 항목: 정렬한 열(예: "지역")을 선택합니다.
• 사용할 함수: 합계, 개수, 평균, 최대값, 최소값 등 계산 방식을 선택합니다.
• 부분합 계산 항목: 요약하려는 숫자 열의 확인란을 선택합니다.

확인을 클릭합니다. Excel은 즉시 각 그룹 뒤에 "합계" 행을 삽입하고 데이터 세트 맨 아래에 "총합계" 행을 추가합니다.

전문가 팁: 개요 수준 작업

워크시트 왼쪽 가장자리를 보면 개요 버튼(1, 2, 3)이 있습니다. 다음을 수행할 수 있습니다:

• 1 클릭: 총합계만 표시
• 2 클릭: 그룹 부분합 및 총합계 표시
• 3 클릭: 모든 상세 행과 부분합 표시
• + / - 버튼 사용: 개별 그룹을 확장하거나 축소

⚠️ 참고: 데이터 탭의 부분합 버튼은 서식이 지정된 Excel 표 안에서는 작동하지 않습니다. 총계 행(표 디자인 → 요약 행)에서 SUBTOTAL 함수를 사용하거나, 먼저 표를 범위로 변환하십시오.

방법 2: Excel SUBTOTAL 함수 사용

내장 도구는 정적이지만, SUBTOTAL 함수는 데이터가 변경될 때 실시간으로 다시 계산되는 동적 수식입니다. 매달 재사용되는 템플릿을 만드는 재무 분석가들이 많이 사용합니다.

구문:

=SUBTOTAL(function_num, ref1, [ref2], ...)

• function_num (필수): Excel에 수행할 수학 연산을 정확히 알려주는 숫자 코드입니다.
  • 기본 코드(수동으로 숨겨진 행 포함): 1 = AVERAGE, 2 = COUNT, 4 = MAX, 5 = MIN, 9 = SUM (가장 많이 사용됨)
  • 기본 코드에 100을 더하면 수동으로 숨겨진 행을 무시합니다(예: 109 = 수동으로 숨겨진 행을 제외한 SUM).
• ref1 (필수): 부분합을 구할 셀 범위
• [ref2] (선택): 데이터가 연속적이지 않은 경우 추가할 수 있는 추가 범위

SUBTOTAL 수식 예시

E2:E10 셀에 데이터가 있는 경우:

• 필터링된 목록에서 값을 합산하려면 =SUBTOTAL(9, E2:E10)을 사용합니다.
• 항목 수를 계산하려면 =SUBTOTAL(2, E2:E10)을 사용합니다.

SUM 대신 SUBTOTAL을 사용하는 이유

가장 큰 장점은 SUBTOTAL이 범위 내의 다른 SUBTOTAL 결과를 자동으로 무시하여 이중 계산을 방지한다는 것입니다. 또한 Excel 필터를 준수하므로, 데이터를 필터링할 때 표시된 행만 계산에 포함됩니다.

방법 3: 현대적인 대안 - 피벗 테이블

피벗 테이블은 본질적으로 원본 데이터와 별도로 요약을 표시하는 새로운 테이블을 만드는 동적 요약 엔진입니다. 부분합 도구보다 더 많은 유연성이 필요할 때 특히 유용합니다.

Excel에서 피벗 테이블을 만드는 방법:

1. 전체 데이터 세트를 선택합니다.
2. 삽입 탭으로 이동하여 피벗 테이블을 클릭합니다.
3. 범주(예: "지역")를 행 필드로 드래그합니다.
4. 숫자 값(예: "매출")을 값 필드로 드래그합니다.

Excel은 숫자 필드에 대해 기본적으로 "합계"를, 텍스트 필드에 대해 "개수"를 설정합니다.

부분합 동작 사용자 지정

기본적으로 피벗 테이블은 모든 최상위 행 필드에 대해 부분합을 표시합니다. 하지만 세부적인 제어가 가능합니다:

• 행 레이블(예: "지역")을 마우스 오른쪽 버튼으로 클릭합니다.
• 필드 설정을 선택합니다.
• "부분합" 아래에서 다음을 선택할 수 있습니다:
  • 자동: 기본 부분합을 표시합니다.
  • 사용자 지정: 특정 함수를 선택할 수 있습니다(예: 동일한 필드에 대해 합계와 평균을 모두 표시).
  • 없음: 총합계만 필요한 경우 부분합을 완전히 제거합니다.

✅ 장점: 기존 부분합 도구와 달리 피벗 테이블은 먼저 Excel에서 데이터를 정렬할 필요가 없습니다. 또한 원본 데이터가 업데이트되면 자동으로 새로 고침되며, 수식을 다시 작성할 필요 없이 여러 그룹화 계층과 계산 유형을 쉽게 추가할 수 있습니다.

방법 4: Python으로 프로그래밍 방식의 부분합 추가

일괄 처리나 데이터 파이프라인 내에서 부분합 생성을 자동화해야 하는 사용자를 위해, Free Spire.XLS for Python 라이브러리는 프로그래밍 방식으로 부분합을 삽입하는 강력한 방법을 제공합니다.

이 방법은 내장 부분합 도구(방법 1)의 동작을 코드를 통해 완전히 복제하므로, Excel을 수동으로 열지 않고도 수백 개의 파일을 처리할 수 있습니다.

사전 요구 사항

먼저 Python(3.7 이상)이 설치되어 있는지 확인하십시오. 터미널이나 명령 프롬프트를 열고 다음을 실행하십시오:

pip install Spire.Xls.Free

Excel에 부분합을 삽입하는 Python 코드

다음 스크립트는 Excel 통합 문서를 로드하고, 지정된 데이터 범위에 부분합을 적용하며, 결과를 새 파일로 저장합니다:

from spire.xls import *
from spire.xls.common import *

# Workbook 객체 생성 및 Excel 파일 로드
workbook = Workbook()
workbook.LoadFromFile("Regional.xlsx")
sheet = workbook.Worksheets[0]

# 부분합 계산을 위한 데이터 범위 정의
# 범위는 B2에서 E10까지 (헤더 제외, 데이터 행만)
data_range = sheet.Range["B2:E10"]

# 전체 매개변수로 Subtotal 메서드 호출
sheet.Subtotal(data_range, 0, [3], SubtotalTypes.Sum, True, False, True)

# 결과 파일 저장
workbook.SaveToFile("AddSubtotals.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

출력: B2:E10 범위 내에서 첫 번째 열("지역")을 기준으로 그룹화하여 네 번째 열("판매 단위")에 합계 부분합을 적용합니다.

매개변수 분석

Subtotal() 메서드는 출력에 대한 완전한 제어를 제공하는 7개의 매개변수를 허용합니다:

⚠️ 참고: Excel 인터페이스와 마찬가지로, Subtotal()을 호출하기 전에 원본 데이터가 그룹화 열을 기준으로 정렬되어 있어야 합니다. 정렬되지 않은 데이터는 값이 변경될 때마다 부분합 행을 생성하여 의도하지 않은 작은 그룹을 많이 만들게 됩니다.

참고 읽기: Python을 사용하여 Excel에서 행과 열 그룹화 또는 그룹 해제하기

자주 묻는 질문 (FAQ)

Q: 동일한 열에 여러 부분합을 추가할 수 있나요?

네, 동일한 열에 두 개 이상의 계산(예: 합계와 평균 모두)을 적용할 수 있습니다:

• 평소처럼 첫 번째 부분합을 삽입합니다.
• 부분합 대화 상자를 다시 엽니다.
• 다른 함수를 선택합니다.
• "현재 부분합 대치" 확인란을 선택 해제합니다.
• 확인을 클릭합니다.

Python에서는 두 번째 호출 시 replace=False로 설정하여 Subtotal() 메서드를 두 번 호출합니다.

Q: Excel에서 부분합을 위한 단축키는 무엇인가요?

직접적인 키보드 단축키는 없지만, Alt + A + B를 사용하여 부분합 대화 상자를 열 수 있습니다.

Q: 부분합을 제거하는 방법은 무엇인가요?

실수를 했거나 원본 데이터로 되돌리려면 데이터 > 부분합으로 돌아갑니다. 대화 상자 왼쪽 하단에 있는 모두 제거 버튼을 클릭합니다. 이렇게 하면 모든 부분합 행과 개요 기호가 즉시 삭제되어 데이터가 원래 정렬된 상태로 복원됩니다.

Q: 부분합만 복사할 수 있나요?

네. 개요 버튼 2를 클릭하여 합계만 표시한 다음, Alt + ; (세미콜론)을 눌러 표시된 셀만 선택하고 새 시트에 복사/붙여넣기 할 수 있습니다.

마치며

Excel에서 부분합을 추가하는 방법을 배우는 것은 데이터 분석을 위한 필수 기술이며, 올바른 방법은 전적으로 워크플로에 달려 있습니다. 빠르고 일회성인 분석에는 내장 부분합 도구가 가장 좋습니다. SUBTOTAL 함수는 동적이고 필터 친화적인 계산을 지원하며, 피벗 테이블은 유연하고 다차원적인 보고에 탁월합니다.

반복적인 작업을 자동화하고 대규모로 파일을 처리해야 할 때는 Free Spire.XLS를 사용하는 Python이 이상적인 옵션입니다. 이 네 가지 방법을 모두 마스터하면 대규모 데이터 세트로 작업할 때 훨씬 더 빠르고 자신감 있게 업무를 수행할 수 있습니다.

참고 항목

• Excel 파일의 행 수 계산 — 열지 않고 계산하기
• Excel에서 중복 열을 삭제하는 방법: 4가지 최고의 방법
• Excel에 수식을 삽입하는 방법: 6가지 쉬운 방법
• Python: Excel에서 피벗 테이블 생성 또는 운영하기
• C#/VB.NET: Excel에서 행과 열 그룹화 또는 그룹 해제하기