[업무자동화 with Python] 파이썬을 활용한 엑셀 자동화(2) : 엑셀 데이터 가공 + 보고서 자동 생성 + 피벗 테이블 + (히트맵 + 다중차트)

 

이번에는 지난 포스팅에 이어서 조금 더 심화된 내용을 다루고자 합니다.

 

[업무자동화 with Python] 파이썬을 활용한 엑셀 자동화 (1) : 데이터 생성 + 필터링 + 보고서 생성

[업무자동화 with Python] 파이썬을 활용한 엑셀 자동화 (1) : 데이터 생성 + 필터링 + 보고서 생성

 

 

이번에 다뤄볼 내용은

 

엑셀 데이터 가공 + 보고서 자동 생성 + 피벗 테이블 + 고급 차트 + VBA 

 

입니다.

 

 

실습 환경은 구글 코랩 입니다.

 

 

1. 파이썬을 활용한 엑셀 자동화 : 엑셀 데이터 가공 + 보고서 자동 생성 + 피벗 테이블 + 고급 차트

 

먼저 실습을 위한 샘플 데이터를 생성해보겠습니다.

 

import pandas as pd

import random

# 샘플 데이터 생성 (30일치)

dates = pd.date_range(start="2024-01-01", periods=30)

branches = ["서울", "부산", "대구", "대전", "광주"]

sales_data = []

for date in dates:

    for branch in branches:

        sales = random.randint(20000, 150000)  # 2만~15만 랜덤 매출

        quantity = random.randint(10, 100)  # 판매수량 10~100 랜덤

        sales_data.append([date, branch, sales, quantity])

df = pd.DataFrame(sales_data, columns=["날짜", "지점", "매출", "판매수량"])

df.to_excel("sales_data.xlsx", index=False)

print("sales_data.xlsx 파일이 생성되었습니다.")

 

* 아래와 같이 데이터가 잘 생성되었음을 알 수 있습니다. 30일간의 데이터입니다.

 

 

 

이제 피벗 테이블을 사용해 날짜별, 지점별 매출 합계를 요약해보겠습니다.

 

import pandas as pd

# 엑셀 데이터 불러오기

df = pd.read_excel("sales_data.xlsx")

# 피벗 테이블 생성 (날짜별, 지점별 매출 합계)

pivot_df = df.pivot_table(index="날짜", columns="지점", values="매출", aggfunc="sum")

# 엑셀로 저장

pivot_df.to_excel("pivot_report.xlsx")

print("pivot_report.xlsx 파일이 생성되었습니다.")

 

* 아래와 같이 날짜별, 그리고 지점별 매출 합계를 깔끔하게 요약한 테이블을 만들 수 있습니다.

 

 

다음은 고급 차트를 추가한 파일을 만들어보겠습니다. 가격에 따른 히트맵, 그리고 이를 요약한 다중 막대 그래프가 포함된 엑셀 파일입니다.

 

from openpyxl import load_workbook

from openpyxl.styles import PatternFill

from openpyxl.chart import BarChart, Reference

# 엑셀 파일 불러오기

wb = load_workbook("pivot_report.xlsx")

ws = wb.active

# ✅ (1) 히트맵 스타일 적용 (매출이 클수록 진한 색)

for row in ws.iter_rows(min_row=2, min_col=2, max_row=ws.max_row, max_col=ws.max_column):

    for cell in row:

        value = cell.value

        if value:

            intensity = int(255 - (value / 150000) * 255)  # 매출 비율에 따른 색상 변경

            color = f"{intensity:02X}{intensity:02X}FF"  # 파란색 계열

            cell.fill = PatternFill(start_color=color, end_color=color, fill_type="solid")

# ✅ (2) 다중 막대그래프 생성

chart = BarChart()

chart.title = "날짜별 지점 매출 비교"

chart.x_axis.title = "날짜"

chart.y_axis.title = "매출"

data = Reference(ws, min_col=2, min_row=1, max_col=ws.max_column, max_row=ws.max_row)

categories = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)

chart.add_data(data, titles_from_data=True)

chart.set_categories(categories)

ws.add_chart(chart, "G5")  # G5 위치에 차트 삽입

# 엑셀 저장

wb.save("Final_Report.xlsx")

print("Final_Report.xlsx 보고서가 생성되었습니다.")