Python cho Excel: tự động hóa báo cáo bán hàng

Dùng Python cho Excel nghĩa là để một đoạn code mở các file Excel, gộp lại, tính tổng và ghi ra một file báo cáo đã định dạng sẵn. Với 12 file bán hàng của một năm, đoạn code trong bài này chạy xong trong 0,47 giây trên máy Mac M4 Pro của mình. Tháng sau có file mới, bạn chạy lại đúng lệnh đó là có báo cáo mới.

Mình làm thử trên dữ liệu cửa hàng "Tiệm Mây" của khóa Phân tích dữ liệu với Python. Đây là dữ liệu mô phỏng, tải công khai ở lab.aidev.edu.vn/k2. Mình tách 3.778 đơn hàng năm 2025 thành 12 file .xlsx, mỗi tháng một file, giống file mà phần mềm bán hàng xuất ra hằng tháng.

Mấy điều cần nắm trước khi đọc tiếp:

• Bài dùng hai library (thư viện code viết sẵn) của Python: pandas đọc, gộp và tính; openpyxl lo phần định dạng ô, độ rộng cột và biểu đồ trong file Excel.

• Báo cáo năm 2025 chỉ nên cộng đơn "Hoàn tất". Nếu cộng cả đơn hủy và đổi trả, doanh thu bị đội thêm 1,55 tỷ đồng.

• Đọc file .xlsx là bước chậm nhất. Với 377.800 dòng, bước này mất khoảng 15 giây, còn đọc cùng dữ liệu đó từ CSV chỉ mất 0,1 giây.

• Không cần cài Excel để chạy code. Mình chỉ mở Excel để xem kết quả.

Python Excel làm được những gì?#

Người ta hay tìm Python Excel vì ba việc: gộp nhiều file thành một bảng, tính lại cùng một báo cáo mỗi tuần hoặc mỗi tháng, và xuất kết quả ra file có định dạng để gửi sếp. Mỗi việc trong bảng dưới đây có một library làm hợp nhất:

Bài này dùng pandas và openpyxl vì hai library này đủ cho việc gộp file và làm báo cáo. Phiên bản trong bảng là bản mới nhất trên PyPI ngày 23/10/2026.

Python in Excel chạy theo cách khác hẳn bốn library trên. Theo trang hướng dẫn của Microsoft, code chạy trong một container (môi trường chạy tách riêng) trên đám mây của Microsoft, chỉ đọc được dữ liệu trong sheet hoặc dữ liệu nạp qua Power Query (công cụ nhập dữ liệu của Excel). Các hàm pandas.read_csv và pandas.read_excel không dùng được trong đó. Vì vậy, muốn gộp 12 file trong một thư mục thì bạn vẫn cần Python chạy trên máy.

Cần cài gì trước khi bắt đầu?#

Bạn cần Python 3 và hai package (gói cài bằng lệnh pip) là pandas và openpyxl. Mình tạo một virtual environment (môi trường Python riêng của thư mục làm việc) để các package cài vào không đụng tới Python của hệ thống:

python3 -m venv .venv
source .venv/bin/activate        # Windows: .venv\Scripts\activate
pip install pandas openpyxl

Bạn phải cài openpyxl riêng vì lệnh cài pandas không kéo theo openpyxl, dù hàm pd.read_excel của pandas cần openpyxl để đọc file .xlsx. Bài Python là gì có ảnh chụp lỗi ImportError khi quên bước này. Nếu lệnh pip install pandas báo lỗi, xem bài cài pandas và sửa lỗi No module named pandas. Muốn hiểu kỹ hơn virtual environment ở đoạn lệnh trên, bài môi trường ảo Python (venv) chạy đủ vòng tạo, kích hoạt, cài package và xóa trên Windows lẫn Ubuntu.

Gộp 12 file Excel thành một bảng bằng pandas#

Thư mục ban_hang/ có 12 file từ ban_hang_2025_01.xlsx đến ban_hang_2025_12.xlsx. Mỗi file có một sheet với các cột Mã đơn, Ngày đặt, Mã KH, Mã SP, Danh mục, Số lượng, Đơn giá, Kênh, Trạng thái và Doanh thu. Tháng 1 có 574 đơn, gấp đôi các tháng khác, vì Tết Ất Tỵ rơi vào ngày 29/01/2025.

Phần đầu của bao_cao.py đọc cả 12 file rồi nối thành một DataFrame (kiểu bảng dữ liệu của pandas, giống một sheet):

import time
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, PatternFill

t0 = time.perf_counter()

cac_file = sorted(Path("ban_hang").glob("ban_hang_2025_*.xlsx"))
df = pd.concat([pd.read_excel(f) for f in cac_file], ignore_index=True)
t1 = time.perf_counter()
print(f"Đọc {len(cac_file)} file, {len(df)} dòng: {t1 - t0:.2f} giây")

Path.glob lấy danh sách file theo mẫu tên, sorted xếp theo thứ tự tháng. pd.concat nối 12 bảng thành một. Tham số ignore_index=True đánh số lại các dòng từ 0 đến 3.777. Nếu thiếu tham số này, bảng gộp có 12 dòng mang số 0, 12 dòng mang số 1 và cứ thế tiếp. pandas tự nhận cột Ngày đặt là kiểu ngày tháng, nên bước sau lấy được tháng mà không cần đổi kiểu. Nếu chưa quen DataFrame và Series, bài pandas là gì giải thích hai khái niệm này qua 10 dòng code đầu tiên.

Mình để mẫu tên là ban_hang_2025_*.xlsx thay vì *.xlsx vì đã gặp lỗi khi một file tháng đang mở trong Excel. Mục "Lỗi khi một file tháng đang mở trong Excel" phía sau kể lại chuyện này.

Vì sao phải lọc trạng thái đơn trước khi cộng doanh thu?#

File bán hàng thường chứa cả đơn đã hủy và đơn đổi trả. Trong 3.778 đơn năm 2025 có 3.255 đơn Hoàn tất, 334 đơn Hủy và 189 đơn Đổi trả. Cộng cả cột Doanh thu thì ra 10,05 tỷ đồng. Chỉ tính đơn Hoàn tất thì còn 8,50 tỷ đồng. Chênh lệch là 1,55 tỷ đồng, tức báo cáo đội lên khoảng 18%.

Phần thứ hai của script lọc đơn trước rồi mới tính:

xong = df[df["Trạng thái"] == "Hoàn tất"].copy()
xong["Tháng"] = xong["Ngày đặt"].dt.strftime("%m/%Y")
theo_thang = xong.pivot_table(index="Tháng", columns="Kênh", values="Doanh thu",
                              aggfunc="sum", fill_value=0, margins=True, margins_name="Tổng")
san_pham = pd.read_csv("goc/san_pham.csv")[["ma_sp", "ten_sp"]]
top_sp = (xong.groupby("Mã SP")["Doanh thu"].sum().nlargest(10).reset_index()
          .merge(san_pham, left_on="Mã SP", right_on="ma_sp")[["Mã SP", "ten_sp", "Doanh thu"]]
          .rename(columns={"ten_sp": "Tên sản phẩm"}))
t2 = time.perf_counter()
print(f"Tính bảng tổng hợp: {t2 - t1:.2f} giây")

pivot_table làm đúng việc của PivotTable trong Excel: tháng nằm ở dòng, kênh bán nằm ở cột, ô giữa là tổng doanh thu. margins=True thêm dòng và cột "Tổng". Bảng thứ hai lấy 10 sản phẩm có doanh thu cao nhất rồi ghép tên sản phẩm từ file san_pham.csv, cũng là file của bộ dữ liệu Tiệm Mây.

Định dạng báo cáo Excel bằng openpyxl thế nào?#

to_excel của pandas chỉ ghi dữ liệu, không định dạng: số không có dấu ngăn nghìn, cột hẹp, dòng tiêu đề không tô màu. Phần cuối của script ghi file rồi mở lại bằng openpyxl để sửa những chỗ đó:

with pd.ExcelWriter("bao_cao_2025.xlsx") as w:
    theo_thang.to_excel(w, sheet_name="Theo tháng")
    top_sp.to_excel(w, sheet_name="Top sản phẩm", index=False)

wb = load_workbook("bao_cao_2025.xlsx")
for ws in wb.worksheets:
    for o in ws[1]:
        o.font = Font(bold=True, color="FFFFFF")
        o.fill = PatternFill("solid", fgColor="0056D2")
    for cot in ws.iter_cols(min_row=2):
        for o in cot:
            if isinstance(o.value, (int, float)):
                o.number_format = "#,##0"
    for cot in ws.columns:
        ws.column_dimensions[cot[0].column_letter].width = 16
    ws.freeze_panes = "A2"
ws = wb["Theo tháng"]
ws.column_dimensions["A"].width = 10
bd = BarChart()
bd.title = "Doanh thu đơn hoàn tất theo tháng"
bd.type, bd.grouping, bd.overlap = "col", "stacked", 100
bd.add_data(Reference(ws, min_col=2, max_col=5, min_row=1, max_row=13), titles_from_data=True)
bd.set_categories(Reference(ws, min_col=1, min_row=2, max_row=13))
bd.width, bd.height = 19, 9
bd.x_axis.delete = bd.y_axis.delete = False  # openpyxl 3.1 mặc định ẩn hai trục
bd.y_axis.number_format = '#,##0,," tr"'
bd.legend.position = "b"
bd.legend.overlay = False
ws.add_chart(bd, "A16")
wb["Top sản phẩm"].column_dimensions["B"].width = 30
wb.save("bao_cao_2025.xlsx")
t3 = time.perf_counter()
print(f"Ghi và định dạng: {t3 - t2:.2f} giây")
print(f"Tổng: {t3 - t0:.2f} giây")
print(theo_thang.loc["Tổng"].map("{:,.0f}".format).to_string())

Mã định dạng #,##0 là mã của chính Excel. Máy đặt kiểu số Việt Nam sẽ hiện dấu chấm ngăn nghìn, máy kiểu Mỹ hiện dấu phẩy. Mã #,##0,," tr" trên trục biểu đồ chia số cho một triệu rồi thêm chữ "tr", nên trục hiện 200 tr, 400 tr thay vì 200.000.000, 400.000.000.

Khi chạy script trong Terminal, cả ba bước xong trong 0,20 giây:

Mở bao_cao_2025.xlsx bằng Excel for Mac 16.114.1, sheet "Theo tháng" trông như sau:

Biểu đồ cho thấy tháng 1 vượt hẳn các tháng còn lại với 1,21 tỷ đồng, vì có Tết. Website là kênh mang về nhiều tiền nhất năm, 2,88 tỷ đồng. Sheet "Top sản phẩm" có 10 dòng, đứng đầu là đồng hồ thông minh bản thường với 460,5 triệu đồng:

Lần chạy đầu, biểu đồ của mình không có trục: phía dưới không có tên tháng, bên trái không có mốc tiền, còn chú thích thì đè lên cột của tháng 11 và tháng 12.

Với openpyxl 3.1.5, biểu đồ tạo mới để trống thuộc tính delete của hai trục (giá trị None), và Excel for Mac 16.114.1 hiểu là ẩn cả hai trục. Dòng bd.x_axis.delete = bd.y_axis.delete = False bật lại hai trục. Hai dòng legend.position = "b" và legend.overlay = False đưa chú thích xuống dưới, không cho đè lên cột.

Chạy mất bao lâu khi file to lên?#

Con số 0,20 giây ở trên chỉ tính thời gian bên trong script. Tính cả lúc khởi động Python và nạp pandas, lệnh python bao_cao.py mất 0,47 giây, trung vị của ba lần chạy. Để xem script chạy ra sao khi file lớn hơn, mình chép lặp các dòng trong mỗi file tháng cho số dòng tăng gấp 10, 30 và 100 lần, rồi chạy lại đúng script đó:

Thời gian tăng gần như tỉ lệ thuận với số dòng, và gần hết thời gian nằm ở bước đọc file. File .xlsx thực chất là một file nén chứa XML, nên openpyxl phải giải nén rồi mới đọc được từng ô. Cùng 377.800 dòng đó lưu dưới dạng CSV thì pandas đọc nhanh hơn khoảng 150 lần:

Nếu phần mềm bán hàng của bạn xuất được cả CSV, hãy chọn CSV cho phần dữ liệu thô. Khi đọc CSV, cột ngày chưa được đổi sang kiểu ngày tháng, nên bạn thêm pd.to_datetime trước khi lấy tháng. Excel chỉ chứa được 1.048.576 dòng trong một sheet theo giới hạn chính thức của Microsoft. Dữ liệu lớn hơn mức đó thì bạn chỉ nên ghi bảng tổng hợp ra Excel, còn dữ liệu thô để ở CSV.

Lỗi khi một file tháng đang mở trong Excel#

Lúc thử viết gọn mẫu tên thành glob("*.xlsx"), mình đang mở file tháng 3 trong Excel để xem. Script dừng ngay:

['~$ban_hang_2025_03.xlsx']
ValueError: Excel file format cannot be determined, you must specify an engine manually.

Khi bạn mở một file, Excel tạo thêm một file khóa cùng thư mục, tên bắt đầu bằng ~$ (ở đây là ~$ban_hang_2025_03.xlsx). Mẫu *.xlsx bắt luôn file này. pandas đọc thử, không nhận ra định dạng nên báo lỗi. Cách sửa là dùng mẫu tên cụ thể như ban_hang_2025_*.xlsx, hoặc bỏ các file có tên bắt đầu bằng ~$:

cac_file = [f for f in sorted(Path("ban_hang").glob("*.xlsx")) if not f.name.startswith("~$")]

Khi nào không nên dùng Python cho Excel?#

• Báo cáo chỉ làm một lần, dữ liệu vài trăm dòng: PivotTable trong Excel nhanh hơn việc ngồi viết script.

• Người nhận cần sửa công thức trong file. Script này ghi ra số đã tính sẵn, file báo cáo không còn công thức nào để người khác lần theo.

• Máy công ty không cho cài Python. Khi đó Power Query trong Excel for Windows gộp được cả thư mục file mà không cần code. Bài Excel, Power BI hay Python cho phân tích dữ liệu so ba công cụ trên cùng một câu hỏi với 6 file đơn hàng mẫu, trong đó Power BI gộp cả thư mục bằng Get data > Folder.

Ngược lại, nếu tháng nào bạn cũng lặp lại cùng một chuỗi thao tác trên nhiều file, một script Python Excel viết một lần sẽ đỡ cho bạn nhiều công nhất: các tháng sau chỉ cần chạy lại.

Câu hỏi thường gặp#

Python có thay được Excel không?#

Không hẳn. Python lo phần lặp lại: gộp file, lọc, tính, xuất báo cáo. Excel vẫn là nơi người nhận mở file, xem và ghi chú. Trong bài này, Excel chỉ dùng để mở file kết quả, còn mọi phép tính đều chạy trong pandas.

Có cần cài Microsoft Excel để chạy code không?#

Không. pandas và openpyxl đọc, ghi thẳng file .xlsx, không cần Excel trên máy. Chỉ xlwings mới cần Excel vì library này điều khiển cửa sổ Excel đang mở.

File .xls cũ có đọc được không?#

pd.read_excel đọc được file .xls kiểu cũ qua package xlrd, theo tài liệu của pandas. openpyxl chỉ làm việc với .xlsx. Mình chưa thử file .xls trong bài này.

Python in Excel có thay được cách làm trong bài không?#

Chưa thay được cho việc gộp file. Python in Excel chỉ đọc dữ liệu đã nằm trong sheet hoặc nạp qua Power Query, code không truy cập được file trên máy. Theo trang hỗ trợ của Microsoft, tính năng này có trong Microsoft 365, với Excel for Mac từ bản 16.96 ở kênh Current Channel cho gói Enterprise và Business.

Đọc tiếp#

• Học Python từ số 0: lộ trình cho người chưa biết code, nên đọc trước nếu bạn chưa tự viết được script như trong bài.

• Data Analyst cần học gì: nếu việc làm báo cáo khiến bạn muốn đi tiếp sang nghề dữ liệu.

Gộp file, lọc, pivot_table và merge trong bài này là các kỹ thuật được dạy kỹ trong ebook Phân tích dữ liệu với Python. Sách viết cho pandas 3, có 89 bài tập tự chấm và dùng chính bộ dữ liệu Tiệm Mây ở trên. Nếu muốn học theo một đề cương có project được chấm điểm, khóa Phân tích dữ liệu với Python là chặng thứ hai của lộ trình AI Engineer từ số 0.

Code chạy ngày 23/10/2026 trên Mac M4 Pro, macOS 27.0.1, Python 3.14.7, pandas 3.1.0, openpyxl 3.1.5; file kết quả mở bằng Excel for Mac 16.114.1. Dữ liệu Tiệm Mây là dữ liệu mô phỏng của AIDev (giấy phép MIT).