Excel là công cụ quản lý và phân tích dữ liệu mạnh mẽ. Nó hoàn hảo cho báo cáo nhanh và tính toán, nhưng Python sẽ hỗ trợ khi công việc trở nên rối rắm, lặp lại hoặc quá lớn. Python mở ra khả năng tự động hóa, phân tích nâng cao và tích hợp vượt xa các tính năng tích hợp sẵn của Excel. Các thư viện Python như pandas để thao tác dữ liệu và openpyxl để xử lý trực tiếp file Excel giúp việc này trở nên liền mạch.
Trong hướng dẫn này, chúng tôi sẽ trình bày năm việc bạn có thể làm với Excel + Python.
Xét dữ liệu bán hàng mẫu để khám phá năm điều bạn có thể làm với Excel và Python.
1. Làm Sạch và Chuẩn Hóa Dữ Liệu Excel Rối Rắm (Có Thể Lặp Lại)
Dữ liệu rối rắm trong Excel là điều phổ biến, vì dữ liệu thực tế hiếm khi sạch sẽ. Thường xuyên dữ liệu chứa khoảng trắng thừa, chữ hoa/thường hỗn loạn, số được lưu dưới dạng văn bản, định dạng không nhất quán, giá trị thiếu, mục trùng lặp, hoặc dữ liệu cần cấu trúc lại trước khi phân tích. Vấn đề này làm hỏng công thức và phân tích.
Python xuất sắc trong các tác vụ làm sạch dữ liệu. Bạn có thể viết kịch bản chuẩn hóa định dạng dữ liệu trên nhiều file, điền giá trị thiếu bằng phương pháp thông minh, loại bỏ trùng lặp, tách hoặc gộp cột dựa trên mẫu, và kiểm tra dữ liệu theo quy tắc kinh doanh. Các bước này có thể yêu cầu hàng giờ thao tác tìm-thay thủ công trong Excel. Sử dụng Python, bạn có thể tạo kịch bản lặp lại xử lý hàng nghìn hàng chỉ trong vài giây.
Giả sử bạn nhận được dữ liệu bán hàng rối rắm. Hãy dùng kịch bản Python đọc dữ liệu thô, làm sạch và chuẩn hóa các cột, thêm các trường tính toán.
- Doanh thu = Số lượng * Đơn giá
- Doanh thu ròng = Doanh thu * (1 – % Giảm giá)
import pandas as pd
file_path = "SalesData.xlsx"
df = pd.read_excel(file_path, sheet_name="Sales Data")
# Làm sạch kiểu dữ liệu
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)
)
# Thêm trường tính toán
df["Revenue"] = df["Units"] * df["UnitPrice"]
df["NetRevenue"] = df["Revenue"] * (1 - df["DiscountPct"])
# Ghi ngược vào CHÍNH file đó dưới dạng sheet MỚI
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("Đã lưu sheet CleanData vào:", file_path)
Bạn sẽ có một sheet mới với tập dữ liệu sạch không làm hỏng bảng pivot, biểu đồ hoặc tra cứu. Trong Excel, bạn tiếp tục dùng pivot/biểu đồ từ dữ liệu đã làm sạch, biết rằng nó nhất quán mỗi lần chạy.
2. Tự Động Tạo Tóm Tắt (Báo Cáo Có Thể Lặp Lại)
Excel có giới hạn hàng và có thể chậm khi tính toán phức tạp. Thư viện pandas của Python xử lý tập dữ liệu lớn hiệu quả và thực hiện tính toán nhanh hơn nhiều.
Với pandas, bạn có thể làm việc với tập dữ liệu hàng triệu bản ghi, thực hiện thao tác nhóm và tổng hợp phức tạp, và chạy phân tích thống kê có thể không thực hiện được trong Excel. Python có thể tạo tóm tắt kiểu pivot và xuất ra Excel. Giả sử bạn muốn tóm tắt nhanh theo Khu vực và Danh mục, nhưng không muốn xây dựng lại pivot mỗi lần.
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"✅ Đã lưu sheet '{out_sheet}' vào: {file_path}")
Bạn sẽ có tóm tắt doanh thu theo khu vực—một sheet kiểu pivot sẵn sàng chia sẻ, cập nhật mỗi khi bạn chạy lại kịch bản.
3. Tạo Biểu Đồ Từ Dữ Liệu Excel (Không Cần Định Dạng Thủ Công)
Biểu đồ thường là phần tốn thời gian nhất trong báo cáo. Excel cung cấp biểu đồ chuẩn, nhưng các thư viện trực quan hóa của Python như Matplotlib, Seaborn và Plotly cung cấp sự linh hoạt và tinh vi hơn nhiều. Bạn có thể tạo trực quan hóa tùy chỉnh tự động cập nhật khi dữ liệu thay đổi, tạo bảng điều khiển tương tác cho người dùng khám phá, hoặc tạo đồ họa chất lượng xuất bản với kiểm soát chính xác mọi yếu tố.
Hãy trực quan hóa hiệu suất theo khu vực (Doanh thu ròng theo Khu vực).
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"
# dữ liệu + biểu đồ trong cùng một sheet
# Chuẩn bị dữ liệu biểu đồ (Doanh thu ròng theo Khu vực)
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)
)
# Ghi dữ liệu biểu đồ vào output_sheet (cùng 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)
# Thêm biểu đồ Excel gốc trên cùng sheet
wb = load_workbook(file_path)
ws = wb[output_sheet]
chart = BarChart()
chart.title = "Doanh Thu Ròng Theo Khu Vực"
chart.y_axis.title = "Doanh Thu Ròng"
chart.x_axis.title = "Khu Vực"
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") # đặt biểu đồ bên phải bảng dữ liệu
wb.save(file_path)
print(f"✅ Đã Tạo Biểu Đồ: {output_sheet}")
Bây giờ bạn có tóm tắt doanh thu theo khu vực cùng biểu đồ cột.
4. Gộp Nhiều File Excel Thành Một Bảng Chính
Việc gộp dữ liệu hàng tuần, hàng tháng hoặc hàng quý từ nhiều nguồn là điều phổ biến. Gộp file Excel từ nhiều người hoặc nhóm chậm và dễ sai. Python có thể gộp chúng trong vài giây và theo dõi file nguồn.
Hãy gộp các file hàng tuần vào thư mục Weekly Reports/ (cùng cấu trúc cột).
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("~$")] # bỏ qua file khóa Excel
print("Đang tìm trong:", folder)
print("File tìm thấy:", [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("Đã lưu: master_report.xlsx")
Bạn sẽ có một bảng tổng hợp với cột SourceFile để kiểm toán. Mỗi tuần, bạn chỉ cần chạy kịch bản.
5. Dự Đoán Điều Gì Excel Không Làm Dễ Dàng (Ví Dụ Machine Learning)
Bạn có thể ước lượng rủi ro trả hàng bằng mẫu (giảm giá, danh mục, số lượng, giá), sau đó ghi xác suất ngược lại để người dùng Excel lọc và sắp xếp. Python có thể thực hiện các phép toán machine learning này dễ dàng.
Tập dữ liệu của chúng ta nhỏ; tuy nhiên, nó vẫn minh họa quy trình.
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)
# Dự đoán xác suất trả hàng cho tất cả hàng
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)
Trong Excel, khám phá WithReturnRisk và lọc ReturnProb từ cao xuống thấp để xem đơn hàng nào rủi ro.
Python Trong Excel (Nếu Có Sẵn Trong Excel Của Bạn)
Nếu Excel trên máy bạn có Python (Preview), bạn có thể chạy Python trực tiếp trong ô và trả kết quả ra sheet. (Xem tổng quan của Microsoft về Python in Excel.) Đây là ví dụ đơn giản đọc một vùng nhỏ, làm sạch văn bản, tính Doanh thu và trả bảng sạch.
- Trong Excel, nhập tập dữ liệu của bạn
- Click vào ô trống
- Vào tab Formulas >> chọn Insert Python
- Dán kịch bản Python
import pandas as pd
# Đọc vùng Excel A1:J21 (kể cả tiêu đề)
df = xl("A1:J21", headers=True)
# Làm sạch cột văn bản
for col in ["Region", "SalesRep"]:
df[col] = df[col].astype(str).str.strip().str.title()
# Sửa kiểu dữ liệu
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)
# Thêm cột tính toán
df["Revenue"] = df["Units"] * df["UnitPrice"]
df
Nó sẽ trả về DataFrame, đối tượng bảng của Python. Excel hiển thị dưới dạng xem trước bảng (và một card).
Bây giờ "spill" đầu ra vào ô như một bảng sạch.
- Click vào Insert Data từ DataFrame >> chọn Show DataType Card để xem trước bảng
- Chọn arrayPreview để đưa bảng vào Excel
- Bạn hiện có văn bản chuẩn hóa và cột Revenue mới
Kết Luận
Bài viết này trình bày năm việc bạn có thể làm với Excel + Python. Excel trở nên mạnh mẽ hơn với Python; việc làm sạch tập dữ liệu rối rắm, tạo tóm tắt kiểu pivot, tự động hóa biểu đồ, gộp nhiều file Excel và thêm insight machine learning đơn giản trở nên dễ dàng hơn. Kết hợp Excel và Python đơn giản hóa quy trình làm việc, từ nhập/xuất dữ liệu đến tự động hóa và trực quan hóa. Hãy bắt đầu với các kịch bản nhỏ và thử nghiệm thêm thư viện.
Nhận MIỄN PHÍ Bài Tập Excel Nâng Cao Có Giải Pháp!