Python을 사용하여 Excel 피벗 테이블 만들기 및 맞춤화
발행: (2025년 12월 16일 오전 10:47 GMT+9)
10 min read
원문: Dev.to
Source: Dev.to
Excel 피벗 테이블은 대규모 데이터 세트를 요약하고 분석하는 강력한 도구로, 사용자가 원시 데이터에서 트렌드와 인사이트를 빠르게 파악할 수 있도록 도와줍니다. 그러나 피벗 테이블을 수동으로 만들고 유지 관리하는 것은 시간이 많이 걸리고 오류가 발생하기 쉬우며, 특히 보고서를 정기적으로 업데이트해야 할 때 더욱 그렇습니다.
Python을 사용하면 이 과정을 효율적으로 자동화할 수 있습니다. 피벗 테이블을 프로그래밍 방식으로 생성하면 일관성을 보장하고 수작업을 줄이며 보고 워크플로우의 신뢰성을 향상시킬 수 있습니다.
이 기사에서는 Python을 사용해 Excel 피벗 테이블을 만들고 사용자 정의하는 방법을 배웁니다. 다음 내용을 다룹니다:
- 환경 설정
- 데이터 준비
- 기본 피벗 테이블 생성
- 일반적인 사용자 정의(행/열 필드, 집계 방법, 보고서 필터)
Source: …
환경 설정 및 데이터 준비
자동화 여정을 시작하려면 Excel 파일과 상호 작용할 수 있는 신뢰할 수 있는 Python 라이브러리가 필요합니다. 이번 튜토리얼에서는 Spire.XLS for Python을 사용할 것입니다. 이 라이브러리는 Excel 문서를 읽고, 쓰고, 조작하기 위해 설계된 강력한 도구입니다.
라이브러리 설치
pip install spire.xls샘플 데이터를 만들고 Excel 시트에 기록하기
from spire.xls import *
# 1. 샘플 데이터
headers = [
"Region", "Product", "Salesperson",
"Units Sold", "Revenue", "Date"
]
data = [
["East", "A", "Alice", 10, 1000, "2023-01-15"],
["West", "B", "Bob", 15, 1500, "2023-01-20"],
["East", "A", "Alice", 12, 1200, "2023-02-10"],
["South", "C", "Charlie", 8, 800, "2023-02-25"],
["West", "B", "Bob", 20, 2000, "2023-03-05"],
["North", "A", "Alice", 11, 1100, "2023-03-12"],
["East", "C", "Charlie", 9, 900, "2023-04-01"],
["South", "B", "Bob", 14, 1400, "2023-04-18"],
["North", "C", "Alice", 7, 700, "2023-05-01"],
["West", "A", "Charlie", 18, 1800, "2023-05-10"],
]
# 2. 워크북 및 시트 초기화
workbook = Workbook()
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]
sheet.Name = "RawData"
# 3. 헤더 기록
for col_index, header in enumerate(headers, start=1):
sheet.Range[1, col_index].Text = header
# 4. 데이터 행 기록
for row_index, row in enumerate(data, start=2):
for col_index, value in enumerate(row, start=1):
sheet.Range[row_index, col_index].Value = str(value)
# 5. 열 자동 맞춤
for col in range(1, len(headers) + 1):
sheet.AutoFitColumn(col)
print("Sample data written to 'RawData' sheet.")
# 선택 사항: 워크북 저장
# workbook.SaveToFile("DataForPivot.xlsx", ExcelVersion.Version2016)생성된 시트 미리보기
핵심: 기본 피벗 테이블 만들기
# Continue from the previous script
# Determine the last row (header + data rows)
last_row = len(data) + 1
# Define the data range (A1:F{last_row})
data_range = sheet.Range[f"A1:F{last_row}"]
# Add a worksheet for the pivot table
pivot_sheet = workbook.Worksheets.Add("PivotReport")
# Create a pivot cache based on the data range
cache = workbook.PivotCaches.Add(data_range)
# Add the pivot table to the new sheet
pt = pivot_sheet.PivotTables.Add(
"SalesPivot", # Pivot table name
pivot_sheet.Range["A1"], # Start cell
cache # Pivot cache
)
print("Basic pivot table structure created.")이 시점에서 워크북에는 빈 피벗‑테이블 셸이 포함되어 있습니다. Excel에서 파일을 열면 필드 구성을 위한 빈 피벗 영역이 표시됩니다.
피벗 테이블을 맞춤 설정하여 더 깊은 인사이트 얻기
피벗 테이블의 진정한 힘은 필드(행, 열, 데이터(집계), 보고서 필터)를 구성하는 데 있습니다.
행 필드 추가
# Add 'Region' and 'Product' as row fields
row_field_region = pt.PivotFields["Region"]
row_field_region.Axis = AxisTypes.Row
row_field_product = pt.PivotFields["Product"]
row_field_product.Axis = AxisTypes.Row
print("Row fields 'Region' and 'Product' added.")열 필드 추가
# Add 'Salesperson' as a column field
col_field_salesperson = pt.PivotFields["Salesperson"]
col_field_salesperson.Axis = AxisTypes.Column
print("Column field 'Salesperson' added.")데이터 필드(집계) 추가
# Add 'Revenue' as a data field with SUM aggregation
data_field_revenue = pt.PivotFields["Revenue"]
data_field_revenue.Function = AggregateFunction.Sum
data_field_revenue.Name = "Total Revenue"
# Add 'Units Sold' as a data field with SUM aggregation
data_field_units = pt.PivotFields["Units Sold"]
data_field_units.Function = AggregateFunction.Sum
data_field_units.Name = "Total Units Sold"
print("Data fields added with SUM aggregation.")보고서 필터 추가
# Add 'Date' as a report filter
filter_field_date = pt.PivotFields["Date"]
filter_field_date.Axis = AxisTypes.Page
print("Report filter 'Date' added.")워크북 마무리 및 저장
# Optional: Refresh the pivot table to apply changes
pt.RefreshData()
pt.Calculate()
# Save the workbook with the pivot table
workbook.SaveToFile("AutomatedPivotReport.xlsx", ExcelVersion.Version2016)
print("Workbook saved as 'AutomatedPivotReport.xlsx'.")열 필드 추가 (교차표)
# Continue from the previous script
# Add 'Salesperson' as a column field
col_field_salesperson = pt.PivotFields["Salesperson"]
col_field_salesperson.Axis = AxisTypes.Column # Assign to column axis
print("Column field 'Salesperson' added.")데이터 필드 정의 (값 필드)
데이터 필드는 실제 계산이 이루어지는 곳입니다. 집계할 필드와 사용할 집계 함수를 지정합니다 (예: 합계, 개수, 평균).
# 이전 스크립트에서 계속
# 'Units Sold'를 데이터 필드로 추가하고 합계 집계 사용
data_field_units = pt.PivotFields["Units Sold"]
pt.DataFields.Add(data_field_units, "Sum of Units Sold", SubtotalTypes.Sum)
# 'Revenue'를 데이터 필드로 추가하고 합계 집계 사용
data_field_revenue = pt.PivotFields["Revenue"]
pt.DataFields.Add(data_field_revenue, "Sum of Revenue", SubtotalTypes.Sum)
# 동일한 필드를 다른 집계로 추가할 수도 있습니다. 예: 평균 Revenue
pt.DataFields.Add(data_field_revenue, "Average Revenue", SubtotalTypes.Average)
print("데이터 필드 'Units Sold'(합계)와 'Revenue'(합계, 평균)가 추가되었습니다.")보고서 필터 적용
보고서 필터를 사용하면 사용자가 특정 기준에 따라 전체 피벗 테이블을 필터링할 수 있어 인터랙티브한 데이터 탐색이 가능합니다.
# Continue from the previous script
# Add 'Date' as a report filter
report_filter_date = PivotReportFilter("Date", True)
pt.ReportFilters.Add(report_filter_date)
print("Report filter 'Date' added.")선택 사항: 레이아웃 및 스타일링
광범위한 스타일링은 Excel에서 인터랙티브하게 수행될 수 있지만, spire.xls는 기본 레이아웃 및 내장 스타일 옵션을 제공하여 가독성을 향상시킵니다.
# Continue from the previous script
# Apply a built‑in pivot table style
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12
# Set the report layout to tabular form for better readability
# This repeats item labels for better clarity in hierarchical row fields
pt.Options.RowLayout = PivotTableLayoutType.Tabular
pt.Options.RowHeaderCaption = "Region/Product" # Custom caption for row header
# Ensure item labels are repeated for clarity in tabular layout
pt.PivotFields["Region"].RepeatItemLabels = True
pt.PivotFields["Product"].RepeatItemLabels = True
# Calculate the pivot table to ensure all changes are applied
pt.CalculateData()
print("Pivot table style and layout applied.")Source:
자동 보고서 저장 및 검토
# Continue from the previous script
# Save the workbook to a file
output_file = "Automated_Sales_Report.xlsx"
workbook.SaveToFile(output_file, ExcelVersion.Version2016)
workbook.Dispose()
print(f"Pivot table report saved to '{output_file}'")
print("Automation complete!")생성된 보고서는 다음과 같습니다:
이제 Automated_Sales_Report.xlsx 파일을 열면 두 개의 시트를 확인할 수 있습니다:
- RawData – 원본 데이터셋이 포함된 시트.
- PivotReport – 지역 및 제품별로 매출 데이터를 요약하고, 영업 사원별로 세분화하며, 날짜별 필터링이 가능한 사전 구성된 피벗 테이블이 포함된 시트.
데이터 준비부터 완전하게 서식이 지정된 보고서까지, 이 전체 과정은 단일 Python 스크립트로 실행되었습니다.
결론
Python을 사용한 Excel 피벗 테이블 자동화는 데이터 분석 및 보고 효율성을 크게 향상시킵니다. 우리는 Spire.XLS for Python을 활용하여 다음을 수행하는 방법을 보여주었습니다:
- 환경을 설정하고 피벗 테이블 생성을 위한 데이터를 준비합니다.
- 기본 피벗 테이블 구조를 시작합니다.
- 행 필드, 열 필드, 다양한 집계 함수가 적용된 데이터 필드 및 보고서 필터를 추가하여 피벗 테이블을 사용자 정의합니다.
- 가독성을 높이기 위한 기본 스타일링 및 레이아웃 옵션을 적용합니다.
- 최종 자동화된 Excel 보고서를 저장합니다.
이점
- 효율성 증가.
- 수동 오류 감소.
- 재현 가능한 워크플로우.
이 자동화 기법을 여러분의 프로젝트에 통합하고, 추가 커스터마이징을 탐색하며, 고유한 데이터 세트에 적용해 보세요. 데이터 분석의 미래는 스마트 자동화에 있으며, Python이 그 열쇠입니다.