Gộp nhiều file Excel thành một bằng Python pandas: code mẫu
Mục lục (11 phần)
Sáng thứ Hai, ba chi nhánh gửi ba tệp báo cáo bán hàng. Bạn mở từng tệp, chép dán vào một trang tổng, thêm cột chi nhánh, rồi làm PivotTable. Ba tệp thì mất mười lăm phút, ba mươi tệp thì mất cả buổi và dễ sót. Bài này đưa bạn một đoạn code Python khoảng 30 dòng dùng thư viện pandas để gộp toàn bộ tệp Excel trong một thư mục, tổng hợp và xuất ra một tệp báo cáo nhiều trang. Code đã chạy thử với pandas 2.3, kết quả trong bài là kết quả thật.
Chuẩn bị
- Cài Python 3, sau đó mở cửa sổ dòng lệnh và chạy: pip install pandas openpyxl.
- pandas dùng để xử lý bảng dữ liệu. openpyxl là thư viện pandas dùng mặc định để đọc và ghi tệp .xlsx. Thiếu nó, bạn sẽ gặp lỗi kiểu "Missing optional dependency 'openpyxl'".
- Đặt các tệp cần gộp chung một thư mục, đặt tên theo một mẫu. Ví dụ thư mục du_lieu gồm ban-hang-ha-noi.xlsx, ban-hang-da-nang.xlsx, ban-hang-can-tho.xlsx. Tệp code gop.py để ngay cạnh thư mục đó.
Dữ liệu mẫu
Công ty văn phòng phẩm Bình An (hư cấu) có ba chi nhánh. Mỗi tệp có cùng bốn cột, ví dụ tệp Hà Nội:
| Ngày | Sản phẩm | Số lượng | Đơn giá |
|---|---|---|---|
| 02/11/2026 | Bút bi | 120 | 5000 |
| 03/11/2026 | Giấy A4 | 30 | 75000 |
| 05/11/2026 | Bìa hồ sơ | 50 | 8000 |
Tệp Đà Nẵng có 2 dòng, tệp Cần Thơ có 3 dòng. Điều kiện quan trọng nhất: các tệp phải có cùng tên cột. Lệch một dấu cách là pandas coi như hai cột khác nhau.
Code đầy đủ
from pathlib import Path
import pandas as pd
thu_muc = Path("du_lieu")
cac_bang = []
for tep in sorted(thu_muc.glob("ban-hang-*.xlsx")):
bang = pd.read_excel(tep)
bang["Chi nhánh"] = tep.stem.replace("ban-hang-", "")
cac_bang.append(bang)
print(tep.name, len(bang), "dòng")
tong = pd.concat(cac_bang, ignore_index=True)
print("Tổng cộng:", len(tong), "dòng")
tong["Doanh thu"] = tong["Số lượng"] * tong["Đơn giá"]
theo_chi_nhanh = (
tong.groupby("Chi nhánh", as_index=False)["Doanh thu"]
.sum()
.sort_values("Doanh thu", ascending=False)
)
bang_xoay = tong.pivot_table(
index="Sản phẩm", columns="Chi nhánh",
values="Doanh thu", aggfunc="sum", fill_value=0
)
with pd.ExcelWriter("tong-hop-tuan.xlsx") as tep_ra:
tong.to_excel(tep_ra, sheet_name="Dữ liệu gộp", index=False)
theo_chi_nhanh.to_excel(tep_ra, sheet_name="Theo chi nhánh", index=False)
bang_xoay.to_excel(tep_ra, sheet_name="Sản phẩm x chi nhánh")
print("Kiểm:", tong["Doanh thu"].sum(), theo_chi_nhanh["Doanh thu"].sum())
Chạy python gop.py, màn hình in ra:
ban-hang-can-tho.xlsx 3 dòng
ban-hang-da-nang.xlsx 2 dòng
ban-hang-ha-noi.xlsx 3 dòng
Tổng cộng: 8 dòng
Kiểm: 6895000 6895000
Cạnh tệp code xuất hiện tong-hop-tuan.xlsx có ba trang: dữ liệu gộp 8 dòng, doanh thu theo chi nhánh (ha-noi 3250000, da-nang 1900000, can-tho 1745000, lấy đúng phần tên tệp làm tên chi nhánh) và bảng sản phẩm theo chi nhánh.
Giải thích từng đoạn
Tìm tệp: glob
thu_muc.glob("ban-hang-*.xlsx") lấy mọi tệp có tên bắt đầu bằng "ban-hang-". Dấu sao nghĩa là "bất kỳ ký tự nào". sorted giúp thứ tự đọc tệp cố định, dễ dò lỗi.
Đọc và đánh dấu nguồn
pd.read_excel(tep) đọc trang đầu tiên của tệp thành một bảng (DataFrame). Dòng tiếp theo thêm cột "Chi nhánh" lấy từ tên tệp: tep.stem là tên tệp không có đuôi, ví dụ "ban-hang-ha-noi", bỏ phần "ban-hang-" còn "ha-noi". Đừng bỏ bước này: gộp xong mà không biết dòng nào của chi nhánh nào thì bảng tổng mất nửa giá trị.
Gộp: concat
pd.concat xếp các bảng chồng lên nhau, giống chép dán nối đuôi trong Excel. ignore_index=True đánh lại số thứ tự dòng từ 0 cho liền mạch.
Tổng hợp: groupby và pivot_table
groupby giống SUMIFS: cộng doanh thu theo từng chi nhánh. pivot_table giống PivotTable: sản phẩm theo hàng, chi nhánh theo cột. fill_value=0 điền 0 vào ô không có số liệu, ví dụ Đà Nẵng tuần này không bán bìa hồ sơ.
Xuất nhiều trang: ExcelWriter
Mỗi lệnh to_excel bên trong khối with ghi một trang. index=False bỏ cột số thứ tự. Bảng xoay thì giữ index vì cột "Sản phẩm" chính là index.
Bảng phiên dịch Excel sang pandas
| Việc bạn làm trong Excel | Lệnh pandas tương ứng |
|---|---|
| Mở tệp | pd.read_excel("tep.xlsx") |
| Chép dán nối nhiều bảng | pd.concat([bang1, bang2]) |
| Thêm cột công thức | bang["Doanh thu"] = bang["Số lượng"] * bang["Đơn giá"] |
| Lọc dòng | bang[bang["Sản phẩm"] == "Giấy A4"] |
| SUMIFS theo nhóm | bang.groupby("Chi nhánh")["Doanh thu"].sum() |
| PivotTable | bang.pivot_table(index=..., columns=..., values=..., aggfunc="sum") |
| XLOOKUP sang bảng khác | bang.merge(bang_gia, on="Sản phẩm", how="left") |
| Lưu tệp | bang.to_excel("ket-qua.xlsx", index=False) |
Biến thể: gộp nhiều sheet trong một tệp
Nếu mỗi chi nhánh là một trang trong cùng một tệp, dùng sheet_name=None. pandas trả về một từ điển: khoá là tên trang, giá trị là bảng.
import pandas as pd
cac_sheet = pd.read_excel("bao-cao-thang.xlsx", sheet_name=None)
for ten, bang in cac_sheet.items():
bang["Chi nhánh"] = ten
tong = pd.concat(cac_sheet.values(), ignore_index=True)
Lỗi hay gặp và cách xử lý
- Tên cột có dấu cách thừa, như "Số lượng " ở một tệp. Thêm ngay sau read_excel: bang.columns = bang.columns.str.strip().
- Dòng tiêu đề không nằm ở dòng 1, vì phía trên có tên báo cáo. Dùng pd.read_excel(tep, header=2) nếu tiêu đề nằm ở dòng 3 (pandas đếm từ 0).
- Số lưu dạng chữlàm phép nhân sai. Dùng pd.to_numeric(bang["Số lượng"], errors="coerce"): ô nào không đổi được sẽ thành trống (NaN), sau đó đếm số ô trống để biết có bao nhiêu dòng cần kiểm tay.
- Tệp tạm của Excel.Khi một tệp đang mở, Excel tạo tệp ẩn tên bắt đầu bằng "~$". Mẫu "*.xlsx" sẽ vơ cả tệp đó và báo lỗi; mẫu có tiền tố như "ban-hang-*.xlsx" thì tránh được.
Kiểm kết quả bằng hai đường
Code tự động không có nghĩa là đúng. Trước khi gửi, kiểm ba điều:
- Số dòng bảng gộp bằng tổng số dòng các tệp (3 + 2 + 3 = 8).
- Tổng doanh thu tính trên bảng gộp bằng tổng của bảng theo chi nhánh (dòng "Kiểm" in ra hai số giống nhau).
- Mở một tệp gốc, cộng tay một chi nhánh, so với kết quả.
Học tiếp
Đây là một trong những việc khoá Python cho người đi làm dạy kỹ: gộp hàng trăm tệp Excel, làm sạch dữ liệu bẩn, tạo báo cáo Excel có định dạng và biểu đồ, mỗi lệnh pandas đặt cạnh thao tác Excel bạn đã quen. Nếu bạn còn phân vân có nên học Python không, đọc thêm bài Học Python cho người đi làm: từ Excel sang pandas.
Câu hỏi thường gặp
Không biết lập trình có dùng được đoạn code này không?
Được. Bạn chỉ cần đổi tên thư mục, mẫu tên tệp và tên cột cho khớp dữ liệu của mình. Khi muốn sửa sâu hơn, như thêm bước làm sạch, bạn cần nắm vài khái niệm cơ bản: biến, vòng lặp, DataFrame.
Excel có tự gộp nhiều tệp được không?
Có. Power Query trong Excel có lệnh Data > Get Data > From File > From Folder để gộp tệp trong một thư mục. Python hợp hơn khi bạn cần thêm nhiều bước xử lý, gửi email báo cáo, hoặc chạy theo lịch mà không mở Excel.
Gộp tệp CSV thì sửa thế nào?
Đổi mẫu tìm thành "ban-hang-*.csv" và dùng pd.read_csv(tep) thay cho read_excel. Nếu gặp lỗi UnicodeDecodeError, tệp không được lưu theo bảng mã UTF-8: mở tệp bằng Excel, lưu lại với định dạng CSV UTF-8 rồi chạy lại.
Dữ liệu công ty có bị gửi lên mạng khi chạy code không?
Không. Đoạn code này chạy hoàn toàn trên máy bạn, đọc và ghi tệp trong thư mục của bạn. Chỉ cẩn thận khi dán dữ liệu thật vào chatbot để nhờ viết code.













