반응형
숫자는 있는데, 보고할 게 없는 느낌.
데이터는 쌓여 있습니다. 그런데 그게 무엇을 의미하는지 한눈에 보이지 않죠. 합계를 내고, 평균을 구하고, 차트를 만드는 과정을 매번 손으로 반복하고 있다면, 그 시간은 분석이 아니라 단순 작업에 쓰이고 있는 겁니다.
이 글에서는 파이썬으로 엑셀 데이터를 자동으로 집계하고, 차트까지 삽입된 완성된 엑셀 파일을 만드는 방법을 다룹니다. openpyxl 하나로 데이터 읽기, 수식 삽입, 서식 적용, 차트 생성까지 모두 처리합니다.
사전 준비
필요한 라이브러리 설치
pip install openpyxl- openpyxl: .xlsx 파일을 읽고 쓰는 것은 물론, 셀 서식·차트·수식까지 제어할 수 있는 라이브러리입니다. pandas와 함께 쓰면 데이터 처리는 pandas가, 엑셀 꾸미기는 openpyxl이 담당하는 구조로 사용할 수 있습니다.
예제 데이터 구조
이 글 전체에서 아래와 같은 구조의 엑셀 파일을 기준으로 설명합니다.
# data.xlsx — 1번 시트 (Sheet1)
날짜 항목 금액
2024-01-03 A항목 120000
2024-01-15 B항목 85000
2024-02-07 A항목 200000
2024-02-20 C항목 60000
2024-03-11 B항목 150000
... ... ...
Case 1. 데이터 읽고 합계·평균 집계하기
openpyxl로 엑셀 파일을 열고, 특정 열의 데이터를 읽어 합계와 평균을 계산하는 기본 방법입니다.
# read_and_sum.py
from openpyxl import load_workbook
wb = load_workbook("data.xlsx")
ws = wb.active # 첫 번째 시트
# 헤더 확인
headers = [cell.value for cell in ws[1]]
print(f"컬럼 목록: {headers}")
# '금액' 열 데이터 수집 (2행부터 끝까지)
amounts = []
for row in ws.iter_rows(min_row=2, values_only=True):
날짜, 항목, 금액 = row
if 금액 is not None:
amounts.append(금액)
total = sum(amounts)
average = total / len(amounts) if amounts else 0
maximum = max(amounts)
minimum = min(amounts)
print(f"합계 : {total:,}원")
print(f"평균 : {average:,.0f}원")
print(f"최댓값: {maximum:,}원")
print(f"최솟값: {minimum:,}원")
Case 2. 집계 결과를 새 시트에 정리하기
항목별로 금액을 집계하고, 결과를 새 시트에 표 형태로 정리합니다. 헤더에 배경색, 숫자에 천 단위 서식까지 적용합니다.
# summarize.py
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, numbers
from openpyxl.utils import get_column_letter
from collections import defaultdict
wb = load_workbook("data.xlsx")
ws = wb.active
# 항목별 금액 집계
item_totals = defaultdict(int)
for row in ws.iter_rows(min_row=2, values_only=True):
날짜, 항목, 금액 = row
if 항목 and 금액:
item_totals[항목] += 금액
# 집계 시트 생성
if "집계" in wb.sheetnames:
del wb["집계"]
ws_summary = wb.create_sheet("집계")
# 헤더 작성
headers = ["항목", "합계 금액"]
header_fill = PatternFill(fill_type="solid", fgColor="FFE699") # 노란 배경
for col, header in enumerate(headers, start=1):
cell = ws_summary.cell(row=1, column=col, value=header)
cell.font = Font(bold=True)
cell.fill = header_fill
cell.alignment = Alignment(horizontal="center")
# 데이터 작성
for row_idx, (item, total) in enumerate(sorted(item_totals.items()), start=2):
ws_summary.cell(row=row_idx, column=1, value=item)
amount_cell = ws_summary.cell(row=row_idx, column=2, value=total)
amount_cell.number_format = "#,##0" # 천 단위 쉼표
amount_cell.alignment = Alignment(horizontal="right")
# 합계 행 추가
last_row = len(item_totals) + 2
ws_summary.cell(row=last_row, column=1, value="합계").font = Font(bold=True)
total_cell = ws_summary.cell(row=last_row, column=2, value=sum(item_totals.values()))
total_cell.number_format = "#,##0"
total_cell.font = Font(bold=True)
total_cell.alignment = Alignment(horizontal="right")
# 열 너비 자동 조정
ws_summary.column_dimensions["A"].width = 15
ws_summary.column_dimensions["B"].width = 18
wb.save("result.xlsx")
print("집계 시트 생성 완료 → result.xlsx")
Case 3. 차트 생성해서 엑셀에 삽입하기
집계한 데이터를 바탕으로 막대 차트를 만들어 엑셀 시트에 직접 삽입합니다. 차트 제목, 축 레이블까지 지정할 수 있습니다.
# add_chart.py
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
wb = load_workbook("result.xlsx")
ws = wb["집계"]
# 차트 데이터 범위 설정
# 항목(A열)과 금액(B열) — 합계 행 제외
data_rows = ws.max_row - 1 # 마지막 합계 행 제외
data = Reference(ws, min_col=2, min_row=1, max_row=data_rows) # 금액 (헤더 포함)
labels = Reference(ws, min_col=1, min_row=2, max_row=data_rows) # 항목명
# 막대 차트 생성
chart = BarChart()
chart.type = "col" # 세로 막대 (가로 막대는 "bar")
chart.title = "항목별 합계 금액"
chart.y_axis.title = "금액 (원)"
chart.x_axis.title = "항목"
chart.style = 10 # 차트 스타일 (1~48)
chart.width = 20 # 차트 가로 크기 (cm)
chart.height = 12 # 차트 세로 크기 (cm)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
# 차트를 시트에 삽입 (D2 셀 위치부터)
ws.add_chart(chart, "D2")
wb.save("result.xlsx")
print("차트 삽입 완료 → result.xlsx")
지원하는 차트 종류
- BarChart: 막대 차트.
type="col"은 세로 막대,type="bar"는 가로 막대입니다. - LineChart: 꺾은선 차트. 시간 흐름에 따른 추이를 보여줄 때 적합합니다.
- PieChart: 원형 차트. 항목별 비율을 보여줄 때 적합합니다.
# 꺾은선 차트로 변경하려면
from openpyxl.chart import LineChart
chart = LineChart()
# 원형 차트로 변경하려면
from openpyxl.chart import PieChart
chart = PieChart()
실전에서 바로 쓰는 완성 코드
데이터 읽기 → 항목별 집계 → 집계 시트 생성 → 차트 삽입까지 한 번에 처리하는 완성본입니다.
실행 예시
python summarize.py --input data.xlsx --output result.xlsx --item-col 2 --amount-col 3# summarize.py
import argparse
from collections import defaultdict
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, Reference
def get_args():
p = argparse.ArgumentParser(description="엑셀 데이터 자동 집계 및 차트 생성")
p.add_argument("--input", required=True, help="원본 엑셀 파일 경로")
p.add_argument("--output", required=True, help="결과 엑셀 파일 경로")
p.add_argument("--item-col", type=int, default=2, help="항목 열 번호 (기본값: 2)")
p.add_argument("--amount-col", type=int, default=3, help="금액 열 번호 (기본값: 3)")
p.add_argument("--sheet", default=None, help="읽을 시트 이름 (없으면 첫 번째 시트)")
return p.parse_args()
def read_data(ws, item_col: int, amount_col: int) -> dict:
"""항목별 금액 합계를 딕셔너리로 반환"""
totals = defaultdict(int)
for row in ws.iter_rows(min_row=2, values_only=True):
try:
item = row[item_col - 1]
amount = row[amount_col - 1]
if item and isinstance(amount, (int, float)):
totals[str(item)] += amount
except IndexError:
continue
return dict(sorted(totals.items()))
def write_summary(wb, totals: dict) -> object:
"""집계 결과를 새 시트에 작성하고 시트 객체 반환"""
if "집계" in wb.sheetnames:
del wb["집계"]
ws = wb.create_sheet("집계")
header_fill = PatternFill(fill_type="solid", fgColor="FFE699")
center = Alignment(horizontal="center")
right = Alignment(horizontal="right")
# 헤더
for col, text in enumerate(["항목", "합계 금액"], start=1):
cell = ws.cell(row=1, column=col, value=text)
cell.font = Font(bold=True)
cell.fill = header_fill
cell.alignment = center
# 데이터 행
for i, (item, total) in enumerate(totals.items(), start=2):
ws.cell(row=i, column=1, value=item)
cell = ws.cell(row=i, column=2, value=total)
cell.number_format = "#,##0"
cell.alignment = right
# 합계 행
last = len(totals) + 2
total_label = ws.cell(row=last, column=1, value="합계")
total_label.font = Font(bold=True)
total_val = ws.cell(row=last, column=2, value=sum(totals.values()))
total_val.number_format = "#,##0"
total_val.font = Font(bold=True)
total_val.alignment = right
ws.column_dimensions["A"].width = 15
ws.column_dimensions["B"].width = 18
return ws
def add_chart(ws, data_rows: int):
"""막대 차트 생성 후 시트에 삽입"""
data = Reference(ws, min_col=2, min_row=1, max_row=data_rows)
labels = Reference(ws, min_col=1, min_row=2, max_row=data_rows)
chart = BarChart()
chart.type = "col"
chart.title = "항목별 합계 금액"
chart.y_axis.title = "금액"
chart.x_axis.title = "항목"
chart.style = 10
chart.width = 20
chart.height = 12
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
ws.add_chart(chart, "D2")
def main():
args = get_args()
wb = load_workbook(args.input)
ws = wb[args.sheet] if args.sheet else wb.active
print(f"[INFO] 입력 파일 : {args.input}")
print(f"[INFO] 시트 : {ws.title}")
print(f"[INFO] 항목 열 : {args.item_col}번")
print(f"[INFO] 금액 열 : {args.amount_col}번\n")
totals = read_data(ws, args.item_col, args.amount_col)
if not totals:
print("[오류] 집계할 데이터가 없습니다.")
return
print("[ 항목별 집계 결과 ]")
for item, total in totals.items():
print(f" {item:<12}: {total:>12,}")
print(f" {'합계':<12}: {sum(totals.values()):>12,}\n")
ws_summary = write_summary(wb, totals)
add_chart(ws_summary, data_rows=len(totals) + 1) # 헤더 포함
wb.save(args.output)
print(f"[완료] 저장 완료 → {args.output}")
if __name__ == "__main__":
main()
코드 핵심 옵션 설명
- --input: 원본 데이터가 담긴 엑셀 파일 경로를 지정합니다.
- --output: 집계 결과와 차트가 삽입된 결과 파일 이름을 지정합니다.
- --item-col: 항목명이 있는 열 번호를 지정합니다. A열은 1, B열은 2입니다. 기본값은 2입니다.
- --amount-col: 금액이 있는 열 번호를 지정합니다. 기본값은 3입니다.
- --sheet: 읽을 시트 이름을 지정합니다. 지정하지 않으면 첫 번째 시트를 읽습니다.
- number_format "#,##0": 숫자에 천 단위 쉼표 서식을 적용합니다. 엑셀에서 직접 서식을 적용한 것과 동일하게 동작합니다.
자주 발생하는 문제와 해결법
문제 1. 숫자인데 문자열로 읽힐 때
엑셀에서 숫자처럼 보여도 셀 서식이 텍스트로 지정된 경우, 파이썬에서 문자열로 읽힙니다. 집계 전에 타입을 확인하고 변환합니다.
for row in ws.iter_rows(min_row=2, values_only=True):
item, amount = row[1], row[2]
try:
amount = float(str(amount).replace(",", "")) # 쉼표 제거 후 숫자 변환
except (ValueError, TypeError):
continue # 변환 실패 시 해당 행 건너뜀
문제 2. 차트가 삽입됐는데 데이터가 비어 보일 때
Reference의 행 범위가 잘못 지정된 경우입니다. 헤더 행을 포함하는지, 합계 행을 제외하는지 확인합니다. 집계 결과가 담긴 시트를 저장한 뒤 차트를 삽입하면 범위 오류를 줄일 수 있습니다.
# max_row 확인 후 합계 행 제외
data_rows = ws.max_row - 1 # 마지막 합계 행 제외
data = Reference(ws, min_col=2, min_row=1, max_row=data_rows)
문제 3. 파일을 열어놓은 상태에서 저장하면 오류가 날 때
결과 파일을 엑셀에서 열어둔 채로 파이썬에서 덮어 쓰려고 하면 PermissionError가 발생합니다. 저장 전에 엑셀을 닫거나, 출력 파일 이름을 다르게 지정하세요.
# 타임스탬프로 파일명 충돌 방지
from datetime import datetime
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
wb.save(f"result_{timestamp}.xlsx")
문제 4. 대용량 파일에서 속도가 느릴 때
수만 행 이상의 대용량 파일은 openpyxl 단독으로 처리하면 느릴 수 있습니다. 데이터 집계는 pandas로 처리하고, 결과 파일 꾸미기만 openpyxl로 담당하는 역할 분리가 효과적입니다.
<code">import pandas as pd
from openpyxl import load_workbook
# pandas로 빠르게 집계
df = pd.read_excel("data.xlsx")
summary = df.groupby("항목")["금액"].sum().reset_index()
summary.to_excel("result.xlsx", index=False)
# openpyxl로 서식과 차트만 추가
wb = load_workbook("result.xlsx")
# 이후 서식·차트 코드 적용...
마치며..
오늘은 openpyxl로 엑셀 데이터를 집계하고 차트까지 삽입하는 방법을 살펴보았습니다. 기술의 목적은 결국 인간의 시간을 더 가치 있는 곳에 쓰게 하는 것입니다. 숫자를 더하고 차트를 그리는 일은 파이썬에게 맡기고, 여러분은 그 결과가 말하는 것에 집중하시길 바랍니다.
집계할 원본 데이터가 여러 파일에 흩어져 있다면, 이전 글 파이썬으로 엑셀 파일 100개 한번에 합치기를 먼저 참고해 보세요. 파일을 합친 뒤 이 글의 코드를 이어서 실행하면 수집부터 보고서 생성까지 하나의 흐름으로 완성됩니다.
관련 내용
- [VS Code/Anaconda] Visual Studio Code 프로젝트에 conda 가상환경 적용
- [Anaconda/Windows] Anaconda 다운로드 및 설치
- [Pytorch] ImportError: No module named 'torch' (라이브러리 인식 문제 해결법)
본 포스팅의 코드는 자유롭게 수정 및 배포가 가능하나, 상업적 이용 시에는 출처를 반드시 밝혀주시기 바랍니다.
반응형