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

 

이번 포스팅부터 파이썬을 활용해서 엑셀 자동화를 방법에 대해서 알아보고자 합니다.

 

오늘은 데이터 생성 + 필터링 + 보고서 생성입니다.

 

코드를 구현한 환경은 코랩이 되겠습니다.

 

1. 파이썬을 활용한 엑셀 자동화 (1) : 데이터 생성 + 필터링 + 보고서 생성

1.1 샘플 데이터 생성

- 먼저 실습에 활용할 간단한 샘플 데이터를 만들어보겠습니다.

 

import pandas as pd

# 샘플 데이터 생성

data = {

    "날짜": pd.date_range(start="2024-01-01", periods=10, freq="D"),

    "지점": ["서울", "부산", "대구", "서울", "대전", "부산", "서울", "대구", "대전", "부산"],

    "매출": [120000, 80000, 45000, 70000, 52000, 62000, 100000, 30000, 56000, 47000],

    "판매수량": [50, 40, 20, 30, 25, 35, 60, 15, 28, 22],

}

# 데이터프레임 생성

df = pd.DataFrame(data)

# 엑셀 파일 저장

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

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

 

* 생성된 파일은 다음 부분에서 확인이 가능합니다.

 

* 생성된 파일을 열어보면 아래와 같습니다.

 

 

1.2 엑셀 데이터를 불러와서 샘플링

 

- 이제 해당 파일을 불러와서 매출이 50,000 이상인 데이터만 필터링 해보겠습니다.

 

import pandas as pd

# 엑셀 파일 읽기

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

# 매출 50,000 이상 데이터 필터링

filtered_df = df[df["매출"] >= 50000]

# 결과 확인

print(filtered_df)

 

 

 

- 필터링이 파이썬 상에서 잘 된 것을 알 수 있습니다.

 

 

1.3 필터링 된 결과를 기반으로 엑셀 보고서 만들기

 

- 위에서 필터링한 결과를 바탕으로 엑셀 보고서를 만들어보겠습니다.

 

* 위에서 처리한 필터링 된 데이터는 header가 없기에 header(날짜, 지점, 매출, 판매수량)를 추가해주고

* 눈에 띄이는 표시를 위해 색상을 추가해줍니다.

* 그리고 이렇게 정리된 데이터에 더불어 막대 차트를 만들어보겠습니다. 

 

from openpyxl import Workbook

from openpyxl.styles import Font, PatternFill, Alignment

from openpyxl.chart import BarChart, Reference

# 새 엑셀 파일 생성

wb = Workbook()

ws = wb.active

ws.title = "매출 보고서"

# 헤더 추가

headers = ["날짜", "지점", "매출", "판매수량"]

ws.append(headers)

# 필터링된 데이터 추가

for row in filtered_df.itertuples(index=False):

    ws.append(row)

# 스타일 적용 (헤더)

header_fill = PatternFill(start_color="FFD700", end_color="FFD700", fill_type="solid")  # 골드 색상

header_font = Font(bold=True)

for col in range(1, 5):

    ws.cell(row=1, column=col).fill = header_fill

    ws.cell(row=1, column=col).font = header_font

    ws.cell(row=1, column=col).alignment = Alignment(horizontal="center")

# 매출 데이터를 기반으로 막대 차트 추가

chart = BarChart()

chart.title = "매출 분석"

chart.x_axis.title = "지점"

chart.y_axis.title = "매출"

data_ref = Reference(ws, min_col=3, min_row=1, max_row=ws.max_row, max_col=3)

categories_ref = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row)

chart.add_data(data_ref, titles_from_data=True)

chart.set_categories(categories_ref)

ws.add_chart(chart, "E5")

# 엑셀 파일 저장

wb.save("매출보고서.xlsx")

print("매출보고서.xlsx 파일이 생성되었습니다.")

 

* 결과 파일인 엑셀 문서는 아래와 같이 생성이 되었고

 

* 해당 파일을 열어보면 다음과 같은 결과가 나오게 되었습니다.