엑셀(Excel)은 강력한 데이터 관리·분석 도구입니다. 간단한 보고서 작성이나 계산에는 최적이지만, 업무가 복잡해지고 반복되며 데이터 규모가 커지기 시작하면 파이썬(Python)의 차례입니다. 파이썬은 엑셀 기본 기능만으로는 불가능한 자동화, 고급 분석, 시스템 통합의 가능성을 열어줍니다. 데이터 조작을 위한 pandas, 엑셀 파일을 직접 다루는 openpyxl 같은 라이브러리 덕분에 두 도구의 연동도 매끄럽습니다.
이 튜토리얼에서는 엑셀 + 파이썬 조합으로 할 수 있는 5가지 작업을 소개합니다.
샘플 판매 데이터를 활용해 엑셀과 파이썬으로 수행할 수 있는 다섯 가지 작업을 하나씩 살펴보겠습니다.
1. 지저분한 엑셀 데이터를 (반복 가능하게) 정제하고 표준화하기
실제 데이터는 깨끗한 경우가 드물기 때문에 엑셀에서 데이터가 지저분한 것은 흔한 일입니다. 불필요한 공백, 일관성 없는 대소문자, 텍스트로 저장된 숫자, 제각각인 서식, 누락된 값, 중복 항목, 분석 전에 재구조화가 필요한 데이터 등이 포함되곤 하며, 이런 문제들은 수식과 분석 전체를 망칠 수 있습니다.
파이썬은 데이터 정제 작업에서 뛰어난 성능을 발휘합니다. 여러 파일에 걸쳐 데이터 형식을 표준화하고, 지능적인 방법으로 누락값을 채우고, 중복을 제거하고, 패턴에 따라 열을 분할하거나 병합하고, 비즈니스 규칙에 맞게 데이터를 검증하는 스크립트를 작성할 수 있습니다. 이런 작업을 엑셀에서 찾아 바꾸기(find & replace)로 수작업 처리하려면 몇 시간이 걸릴 수 있지만, 파이썬으로는 수천 행을 몇 초 만에 처리하는 재사용 가능한 스크립트를 만들 수 있습니다.
지저분한 판매 데이터를 받았다고 가정해 보겠습니다. 해당 데이터를 읽어 열을 정제·표준화하고 계산 필드를 추가하는 파이썬 스크립트를 사용해 보겠습니다.
- Revenue = Units × UnitPrice
- NetRevenue = Revenue × (1 − DiscountPct)
import pandas as pd
file_path = "SalesData.xlsx"
df = pd.read_excel(file_path, sheet_name="Sales Data")
# Clean types
df["Units"] = pd.to_numeric(df["Units"], errors="coerce").fillna(0).astype(int)
df["UnitPrice"] = pd.to_numeric(df["UnitPrice"], errors="coerce").fillna(0.0)
df["DiscountPct"] = pd.to_numeric(df["DiscountPct"], errors="coerce").fillna(0.0)
df["Returned"] = (
df["Returned"].astype(str).str.strip().str.lower()
.map({"yes": True, "no": False})
.fillna(False)
)
# Add calculated fields
df["Revenue"] = df["Units"] * df["UnitPrice"]
df["NetRevenue"] = df["Revenue"] * (1 - df["DiscountPct"])
# Write back into the SAME file as a NEW sheet
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
df.to_excel(writer, sheet_name="CleanData", index=False)
print("Saved CleanData sheet inside:", file_path)
실행하면 피벗, 차트, 조회 함수가 깨지지 않는 깔끔한 데이터셋이 담긴 새 시트가 생성됩니다. 정제된 데이터를 바탕으로 엑셀에서 피벗과 차트를 계속 활용할 수 있으며, 매번 실행할 때마다 결과가 일관되게 유지된다는 점이 보장됩니다.
2. 요약 자동 생성 (반복 가능한 리포트)
엑셀에는 행 수 제한이 있으며, 복잡한 계산에서는 속도가 느려질 수 있습니다. 파이썬의 pandas 라이브러리는 대용량 데이터셋을 효율적으로 처리하고 훨씬 빠르게 계산을 수행합니다.
pandas를 사용하면 수백만 건의 레코드로 된 데이터셋을 다루고, 복잡한 그룹화·집계 연산을 수행하며, 엑셀에서는 사실상 비현실적인 통계 분석까지 실행할 수 있습니다. 또한 피벗 스타일의 요약표를 생성해 엑셀로 내보낼 수도 있습니다. 지역(Region)과 카테고리(Category)별 빠른 요약이 필요하지만 매번 피벗을 다시 만들고 싶지 않다고 가정해 보겠습니다.
import pandas as pd
file_path = "SalesData.xlsx"
clean_sheet = "CleanData"
out_sheet = "Summary"
df = pd.read_excel(file_path, sheet_name=clean_sheet)
summary = (
df.groupby(["Region", "Category"], as_index=False)
.agg(
Orders=("OrderID", "count"),
Units=("Units", "sum"),
NetRevenue=("NetRevenue", "sum"),
Returns=("Returned", "sum")
)
)
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
summary.to_excel(writer, sheet_name=out_sheet, index=False)
print(f"✅ Saved '{out_sheet}' sheet inside: {file_path}")
그러면 지역별 매출 요약표, 즉 스크립트를 다시 실행할 때마다 자동으로 갱신되는 바로 공유 가능한 피벗 스타일 시트를 얻게 됩니다.
3. 엑셀 데이터로 차트 생성 (수작업 서식 없이)
보고서 작업에서 차트는 가장 시간이 많이 드는 부분입니다. 엑셀은 표준 차트를 제공하지만, Matplotlib, Seaborn, Plotly 같은 파이썬 시각화 라이브러리는 훨씬 더 유연하고 정교한 기능을 제공합니다. 데이터가 변경될 때 자동으로 갱신되는 맞춤형 시각화를 만들거나, 사용자가 직접 탐색할 수 있는 인터랙티브 대시보드를 구축하거나, 모든 요소를 세밀하게 제어하는 출판 수준의 그래픽을 제작할 수 있습니다.
이번에는 지역별 실적(Region별 NetRevenue)을 시각화해 보겠습니다.
import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
file_path = "SalesData.xlsx"
source_sheet = "CleanData"
output_sheet = "RegionChart" # data + chart in this one sheet
# Prepare chart data (NetRevenue by Region)
df = pd.read_excel(file_path, sheet_name=source_sheet)
chart_data = (
df.groupby("Region", as_index=False)["NetRevenue"]
.sum()
.sort_values("NetRevenue", ascending=False)
)
# Write chart data into output_sheet (same workbook)
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
chart_data.to_excel(writer, sheet_name=output_sheet, index=False)
# Add the native Excel chart on the same sheet
wb = load_workbook(file_path)
ws = wb[output_sheet]
chart = BarChart()
chart.title = "Net Revenue by Region"
chart.y_axis.title = "Net Revenue"
chart.x_axis.title = "Region"
values = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
labels = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart.add_data(values, titles_from_data=True)
chart.set_categories(labels)
ws.add_chart(chart, "D2") # place chart to the right of the data table
wb.save(file_path)
print(f"✅ Chart Created: {output_sheet}")
이제 지역별 매출 요약과 함께 막대 차트가 완성됩니다.
4. 여러 엑셀 파일을 하나의 마스터 테이블로 병합하기
주간, 월간, 분기별 데이터를 서로 다른 소스에서 취합하는 일은 매우 흔합니다. 여러 사람이나 팀이 만든 엑셀 파일을 병합하는 작업은 느리고 오류가 발생하기 쉽습니다. 파이썬은 이런 파일들을 몇 초 만에 합치고 출처 파일까지 추적할 수 있습니다.
이번에는 동일한 열 구조를 가진 주간 파일들이 들어 있는 Weekly Reports/ 폴더의 파일들을 병합해 보겠습니다.
import pandas as pd
from pathlib import Path
base_folder = Path(__file__).resolve().parent
folder = base_folder / "Weekly Reports"
files = sorted(folder.glob("*.xlsx"))
files = [f for f in files if not f.name.startswith("~$")] # ignore Excel lock files
print("Looking in:", folder)
print("Files found:", [f.name for f in files])
frames = []
for f in files:
temp = pd.read_excel(f)
temp["SourceFile"] = f.name
frames.append(temp)
master = pd.concat(frames, ignore_index=True)
master.to_excel(base_folder / "master_report.xlsx", index=False)
print("Saved: master_report.xlsx")
감사(auditing)를 위한 SourceFile 열이 포함된 하나의 통합 테이블을 얻게 됩니다. 매주 스크립트만 실행하면 됩니다.
5. 엑셀이 쉽게 못 하는 예측 수행하기 (머신러닝 예제)
할인율, 카테고리, 수량, 가격 같은 패턴을 이용해 반품 위험을 추정한 뒤, 그 확률 값을 다시 엑셀에 기록하면 엑셀 사용자가 필터링하고 정렬할 수 있습니다. 파이썬은 이런 머신러닝 작업을 손쉽게 수행할 수 있습니다.
여기서 사용하는 데이터셋은 작지만, 전체 워크플로를 보여주기에는 충분합니다.
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import OneHotEncoder
from sklearn.compose import ColumnTransformer
from sklearn.pipeline import Pipeline
from sklearn.linear_model import LogisticRegression
file_path = "SalesData.xlsx"
df = pd.read_excel(file_path, sheet_name="CleanData")
X = df[["Region", "SalesRep", "Category", "Units", "UnitPrice", "DiscountPct"]]
y = df["Returned"].astype(int)
cat_cols = ["Region", "SalesRep", "Category"]
num_cols = ["Units", "UnitPrice", "DiscountPct"]
preprocess = ColumnTransformer(
transformers=[
("cat", OneHotEncoder(handle_unknown="ignore"), cat_cols),
("num", "passthrough", num_cols),
]
)
model = Pipeline(steps=[
("prep", preprocess),
("clf", LogisticRegression(max_iter=1000))
])
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)
model.fit(X_train, y_train)
# Predict probability of return for all rows
df["ReturnProb"] = model.predict_proba(X)[:, 1]
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
df.to_excel(writer, sheet_name="WithReturnRisk", index=False)
엑셀에서 WithReturnRisk 시트를 열고 ReturnProb를 내림차순으로 필터링하면 어떤 주문이 위험해 보이는지 한눈에 확인할 수 있습니다.
엑셀 안에서 파이썬 실행하기 (사용 가능한 경우)
사용 중인 엑셀에 Python(미리 보기) 기능이 있다면, 셀에서 직접 파이썬 코드를 실행하고 결과를 시트로 반환할 수 있습니다. (마이크로소프트의 Python in Excel 공식 개요 문서를 참고하세요.) 아래는 작은 범위를 읽어 텍스트를 정제하고 Revenue를 계산한 후 깔끔한 표를 반환하는 간단한 예제입니다.
- 엑셀에 데이터셋 입력
- 빈 셀 클릭
- 수식(Formulas) 탭 → Python 삽입(Insert Python) 선택
- 파이썬 스크립트 붙여넣기
import pandas as pd
# Read the Excel range A1:J21 (including headers)
df = xl("A1:J21", headers=True)
# Clean text columns
for col in ["Region", "SalesRep"]:
df[col] = df[col].astype(str).str.strip().str.title()
# Fix data types
df["OrderDate"] = pd.to_datetime(df["OrderDate"], errors="coerce")
df["Units"] = pd.to_numeric(df["Units"], errors="coerce").fillna(0).astype(int)
df["UnitPrice"] = pd.to_numeric(df["UnitPrice"], errors="coerce").fillna(0.0)
# Add a calculated column
df["Revenue"] = df["Units"] * df["UnitPrice"]
df
결과로 파이썬의 테이블 객체인 DataFrame이 반환되며, 엑셀에는 표 미리보기(및 카드) 형태로 표시됩니다.
이제 출력을 깔끔한 표 형태로 셀에 'spill'(넘침 출력)해 보겠습니다.
- DataFrame에서 Insert Data 클릭 → Show DataType Card를 선택해 표 미리보기
- arrayPreview를 선택해 표를 엑셀로 가져오기
- 이제 표준화된 텍스트와 새로운 Revenue 열이 생깁니다
마무리
이 글에서는 엑셀 + 파이썬으로 할 수 있는 다섯 가지 작업을 살펴봤습니다. 파이썬과 함께라면 엑셀은 한층 더 강력해집니다. 지저분한 데이터셋 정제, 피벗 스타일 요약 생성, 차트 자동화, 여러 엑셀 파일 병합, 간단한 머신러닝 인사이트 추가까지 훨씬 쉬워집니다. 엑셀과 파이썬의 결합은 데이터 가져오기/내보내기부터 자동화, 시각화에 이르는 워크플로 전반을 간소화합니다. 작은 스크립트부터 시작해 더 많은 라이브러리를 실험해 보세요.
Get FREE Advanced Excel Exercises with Solutions!