Cuối tháng, mỗi chi nhánh gửi về một file doanh thu riêng, và bạn phải ghép tất cả thành một bảng để làm báo cáo. Bài viết này hướng dẫn bạn gộp nhiều file Excel thành 1 file bằng Power Query, để Excel tự ghép dữ liệu và tự lấy thêm file mới mỗi khi bạn bấm làm mới.
Cách làm nhanh: Bạn để các file cần gộp vào một thư mục. Trong một file Excel mới, bạn chọn tab Data, bấm Get Data, From File, From Folder, chọn thư mục rồi bấm Open. Ở cửa sổ danh sách file, bạn bấm Combine, chọn Combine & Load, bấm vào sheet cần gộp rồi bấm OK. Trong ví dụ của bài, 4 file của 4 chi nhánh có tổng cộng 18 dòng được gộp thành một bảng 18 dòng, có thêm cột Source.Name ghi tên file của từng dòng. Khi có thêm file, bạn chép file vào thư mục rồi bấm Data, Refresh All.
Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Ngày trong bảng kết quả hiện theo kiểu ngày của Windows, nên máy của bạn có thể hiện tháng/ngày/năm thay vì ngày/tháng/năm như ảnh.
Power Query là gì và nằm ở đâu trong Excel?
Power Query là công cụ lấy dữ liệu và biến đổi dữ liệu có sẵn trong Excel. Mỗi thao tác bạn làm được Excel ghi lại thành các bước của một query. Lần sau, bạn bấm làm mới và Excel chạy lại đúng các bước đó trên dữ liệu mới.
Từ Excel 2016 trở đi, Power Query có sẵn và bạn không cần bật hay cài thêm. Bảng dưới đây ghi chỗ tìm lệnh gộp thư mục theo từng phiên bản:
Phiên bản Excel | Chỗ tìm lệnh From Folder |
|---|---|
Excel 2019, 2021, 2024, Microsoft 365 trên Windows | Bạn chọn tab Data, nhóm Get & Transform Data, nút Get Data, From File, From Folder. |
Excel 2016 chưa cập nhật | Bạn chọn tab Data, nút New Query, From File, From Folder. Bản 2016 đã cập nhật có nút Get Data như bản mới. |
Excel 2010, 2013 | Bạn cần cài add-in Power Query miễn phí của Microsoft, sau đó dùng tab Power Query riêng. Microsoft đã ngừng cập nhật add-in này từ năm 2019. |
Excel cho web | Excel cho web không lấy được thư mục trên máy tính. Bạn mở file bằng Excel trên máy tính để làm. |
Bạn cần chuẩn bị các file như thế nào trước khi gộp?
Trong ví dụ, thư mục Doanh thu chi nhanh T10 chứa 4 file doanh thu tháng 10. Mỗi file có một sheet tên Doanh thu với 5 cột: Ngày, Nhân viên, Mã hàng, Số lượng, Doanh thu.
File | Số dòng | Tổng doanh thu |
|---|---|---|
Can Tho.xlsx | 3 | 2.820.000 |
Da Nang.xlsx | 4 | 3.710.000 |
Ha Noi.xlsx | 5 | 6.140.000 |
TP HCM.xlsx | 6 | 8.925.000 |
Cộng | 18 | 21.595.000 |
Bạn ghi lại số dòng và tổng doanh thu của từng file như bảng trên. Sau khi gộp, bạn so hai con số này với bảng kết quả để biết Power Query đã lấy đủ dữ liệu chưa.
Các file cần giống nhau ở 4 điểm sau:
Tên sheet phải giống nhau. Power Query lấy dữ liệu theo tên sheet bạn chọn ở file mẫu. File có sheet tên khác sẽ làm lệnh làm mới báo lỗi.
Tiêu đề cột phải nằm ở dòng 1. Nếu phía trên tiêu đề cột có dòng tên báo cáo, bảng gộp sẽ bị lặp tiêu đề. Phần sau của bài hướng dẫn cách sửa trường hợp này.
Tên cột phải viết giống hệt nhau. Power Query coi "Doanh thu", "Doanh Thu" và "Doanh thu " (có dấu cách ở cuối) là 3 cột khác nhau. Thứ tự cột thì được khác nhau, vì Power Query ghép cột theo tên chứ không theo vị trí.
Thư mục chỉ chứa file cần gộp. Power Query lấy cả file trong thư mục con, nên bạn không để thư mục con chứa file tháng cũ. Bạn cũng lưu file tổng hợp ở chỗ khác, không lưu vào chính thư mục này.
Bạn gộp nhiều file Excel thành 1 bằng Power Query như thế nào?
Bạn mở một file Excel mới để chứa bảng tổng hợp, ví dụ Tong hop doanh thu T10.xlsx, và lưu file này ở ngoài thư mục chi nhánh.
Bước 1: Bạn mở lệnh From Folder
Bạn chọn tab Data, bấm Get Data, di chuột vào From File rồi bấm From Folder.

Bước 2: Bạn chọn thư mục chứa các file
Hộp thoại Browse hiện ra. Bạn tìm tới thư mục Doanh thu chi nhanh T10, bấm vào thư mục đó rồi bấm Open. Bạn cũng có thể dán đường dẫn thư mục vào ô Folder rồi bấm Open.
Excel mở cửa sổ liệt kê các file trong thư mục, gồm tên file, đuôi file, ngày sửa và đường dẫn.

Bước 3: Bạn chọn Combine & Load
Bạn bấm nút Combine để mở danh sách lựa chọn, sau đó chọn Combine & Load.

Nút Combine có 3 lựa chọn:
Lựa chọn | Excel làm gì |
|---|---|
Combine & Transform Data | Lựa chọn này mở Power Query Editor để bạn xem và sửa dữ liệu trước khi đưa vào sheet. |
Combine & Load | Lựa chọn này gộp file và đưa kết quả vào một sheet mới. |
Combine & Load To | Lựa chọn này cho bạn chọn nơi đặt kết quả: bảng trên sheet, Pivot Table, hoặc chỉ tạo kết nối. |
Bước 4: Bạn chọn sheet cần gộp
Hộp thoại Combine Files hiện ra. Ô Sample File đang ghi First file, nghĩa là Excel lấy file đầu danh sách, ở đây là Can Tho.xlsx, làm file mẫu. Khung bên trái có mục Parameter1, bên trong là danh sách sheet của file mẫu. Bạn bấm vào sheet Doanh thu. Khung bên phải hiện dữ liệu của sheet này trong file mẫu. Sau đó, bạn bấm OK.

Hộp thoại có ô Skip files with errors. Ô này bỏ qua những file bị lỗi khi gộp. Bạn nên để trống ô này, vì khi Excel bỏ qua file lỗi, bạn sẽ không biết bảng gộp đang thiếu file nào.
Bước 5: Bạn kiểm tra bảng kết quả
Excel tạo một sheet mới tên Doanh thu chi nhanh T10, đặt theo tên thư mục, và đổ kết quả vào một bảng. Khung Queries & Connections bên phải hiện tên query và số dòng đã tải.

Cột Source.Name cho biết mỗi dòng lấy từ file nào. Vì mỗi chi nhánh là một file, cột này cũng cho biết dòng đó thuộc chi nhánh nào. Tổng cột Doanh thu là 21.595.000, khớp với tổng của 4 file.
Power Query đưa số vào bảng mà chưa có dấu phân cách hàng nghìn, nên doanh thu hiện 1020000. Để số dễ đọc hơn, bạn chọn các ô số của cột Doanh thu, nhấn Ctrl + 1, chọn Number, đặt Decimal places bằng 0, tích ô Use 1000 Separator (,) rồi bấm OK. Excel giữ định dạng này khi bạn làm mới bảng.
Bảng kết quả là một bảng Excel bình thường. Bạn có thể làm tiếp báo cáo bằng hàm SUMIFS hoặc bằng Pivot Table trên bảng này.
Power Query đã tạo những gì sau khi gộp?
Bạn bấm đúp vào tên query Doanh thu chi nhanh T10 trong khung Queries & Connections để mở Power Query Editor. Bạn cũng có thể chọn Data, Get Data, Launch Power Query Editor. Ảnh dưới đây thu gọn khung Queries bên trái để thấy thêm cột. Bạn bấm mũi tên ở mép trên khung Queries để thu gọn hoặc mở lại khung này.

Khi mở, khung Queries liệt kê 5 query giống khung Queries & Connections ở ảnh trước. Query chính nằm trong nhóm Other Queries, 4 query phụ nằm trong nhóm Transform File from Doanh thu chi nhanh T10:
Query | Vai trò |
|---|---|
| Query chính lấy danh sách file, chạy query phụ trên từng file rồi ghép kết quả. Bảng trên sheet lấy dữ liệu từ query này. |
| Query phụ này trỏ tới file mẫu, ở đây là file đầu danh sách. |
| Query phụ này là tham số truyền từng file vào các bước xử lý. |
| Query phụ này chứa các bước xử lý một file: mở sheet Doanh thu và lấy dòng 1 làm tiêu đề cột. Bạn sửa ở đây khi muốn mọi file được xử lý giống nhau trước khi ghép. |
| Query phụ này là hàm Excel tạo ra từ |
Query chính có 7 bước trong khung Applied Steps. Bước Source lấy danh sách file trong thư mục. Bước Filtered Hidden Files1 bỏ các file ẩn. Bước Expanded Table Column1 bung dữ liệu của từng file thành các cột. Bước Changed Type đặt kiểu dữ liệu cho từng cột, ví dụ cột Ngày là kiểu ngày.
Khi gộp các file chuẩn như ví dụ, bạn không cần sửa query nào. Bạn bấm nút đóng ở góc trên bên phải để đóng Power Query Editor.
Bạn cập nhật bảng tổng hợp khi có thêm file mới như thế nào?
Đây là điểm khác lớn nhất giữa Power Query và cách sao chép tay. Giả sử chi nhánh Hải Phòng gửi file muộn. Bạn chép file Hai Phong.xlsx vào thư mục Doanh thu chi nhanh T10. Sau đó, trong file tổng hợp, bạn chọn tab Data và bấm Refresh All, hoặc nhấn Ctrl + Alt + F5.


Bảng có 22 dòng và tổng doanh thu là 24.765.000, tức là thêm đúng 4 dòng và 3.170.000 của Hải Phòng. Các dòng Hải Phòng nằm giữa Hà Nội và TP HCM, vì Windows liệt kê các file trong thư mục theo tên. Với thư mục mạng hoặc thư mục SharePoint, thứ tự file có thể khác, nên file mẫu, tức là file đầu danh sách, cũng có thể khác.
Bạn có 2 cách làm cho tháng sau:
Bạn dùng lại thư mục cũ. Bạn thay các file tháng 10 trong thư mục bằng file tháng 11, rồi bấm Refresh All.
Bạn tạo thư mục mới. Bạn chọn Data, Get Data, Data Source Settings, chọn đường dẫn thư mục cũ, bấm Change Source, chọn thư mục mới, bấm OK, bấm Close, rồi bấm Refresh All.
Nếu muốn file tổng hợp tự làm mới mỗi lần mở, bạn chuột phải vào query trong khung Queries & Connections, chọn Properties, rồi tích ô Refresh data when opening the file.
Vì sao cột Doanh thu của một file bị trống sau khi gộp?
Giả sử nhân viên TP HCM sửa tiêu đề cột "Doanh thu" thành "Doanh số" trong file của mình. Bạn bấm Refresh All, Excel không báo lỗi nào, nhưng 6 dòng TP HCM có cột Doanh thu bị trống.

Power Query lấy danh sách cột theo file mẫu Can Tho.xlsx. File TP HCM không còn cột tên "Doanh thu", nên Power Query để trống cột này. Cột "Doanh số" cũng không được lấy, vì file mẫu không có cột đó. Tổng doanh thu lúc này chỉ còn 15.840.000, thiếu đúng 8.925.000 của TP HCM.
Chuyện này cũng xảy ra khi tên cột chỉ khác chữ hoa, ví dụ "Doanh Thu", hoặc có dấu cách thừa ở cuối, ví dụ "Doanh thu ".
Để sửa, bạn mở file TP HCM, đổi tiêu đề cột về đúng "Doanh thu", lưu file, rồi bấm Refresh All. Bảng trở lại 22 dòng với tổng 24.765.000.
Để phát hiện lỗi này sớm, sau mỗi lần làm mới, bạn làm một trong hai việc dưới đây:
Bạn bấm nút lọc ở tiêu đề cột Doanh thu và xem có mục (Blanks) hay không.
Bạn so tổng doanh thu của bảng gộp với tổng của từng file chi nhánh.
Trường hợp chính file mẫu, tức là file đầu danh sách, bị đổi tên cột thì lệnh làm mới báo lỗi Expression.Error: The column 'Doanh thu' of the table wasn't found. Bạn sửa tên cột trong file đó rồi làm mới lại.
Vì sao bảng gộp bị lặp tiêu đề và có cột tên Column2?
Nhiều chi nhánh làm báo cáo có dòng tên báo cáo ở ô A1, ví dụ "BÁO CÁO DOANH THU THÁNG 10 - CAN THO", để trống dòng 2, rồi mới ghi tiêu đề cột ở dòng 3. Trong ví dụ này, 4 file như vậy nằm trong thư mục Bao cao chi nhanh T10, và bạn gộp bằng Combine & Transform Data để xem kết quả trong Power Query Editor trước. Power Query lấy dòng 1 làm tiêu đề, nên bảng gộp gặp 3 vấn đề:
Tên cột thành "BÁO CÁO DOANH THU THÁNG 10 - CAN THO", "Column2", "Column3"...
Mỗi file có thêm 1 dòng trống và 1 dòng tiêu đề cột nằm lẫn trong dữ liệu. Với 4 file, bảng có 26 dòng thay vì 18 dòng.
Cột đầu tiên của các file Đà Nẵng, Hà Nội, TP HCM bị trống, vì dòng tên báo cáo của mỗi file khác nhau.

Cách sửa dễ nhất là thống nhất mẫu báo cáo: bạn xóa dòng tên báo cáo và dòng trống, để tiêu đề cột nằm ở dòng 1, rồi gộp lại.
Nếu bạn không sửa được file của chi nhánh, bạn sửa trong Power Query Editor như sau:
Bạn chọn query
Transform Sample Fileở khung Queries bên trái. Query này có 3 bước: Source, Navigation và Promoted Headers.Ở khung Applied Steps, bạn bấm vào bước Promoted Headers, rồi bấm dấu X bên trái tên bước hoặc nhấn phím Delete để xóa bước này.
Trên tab Home, bạn bấm Remove Rows, chọn Remove Top Rows, nhập
2vào ô Number of rows rồi bấm OK.

Trên tab Home, bạn bấm Use First Row as Headers. Power Query thêm bước Promoted Headers và một bước Changed Type tự đặt kiểu dữ liệu.
Bạn chọn query chính
Bao cao chi nhanh T10ở khung Queries. Bước cuối Changed Type của query này báo lỗi, vì bước này vẫn ghi tên cột cũ.

Bạn bấm dấu X cạnh bước Changed Type của query chính hoặc nhấn phím Delete để xóa bước này.
Bạn bấm vào tiêu đề cột Ngày, trên tab Home bấm Data Type rồi chọn Date. Nếu bỏ qua bước này, cột Ngày trên sheet sẽ hiện số, ví dụ 46298 thay vì ngày 03/10/2026.

Trên tab Home, bạn bấm Close & Load để đưa bảng vào sheet.
Các bước bạn sửa ở Transform Sample File được áp dụng cho mọi file trong thư mục, nên tháng sau bạn chỉ cần thay file và bấm Refresh All.
Bạn gộp nhiều file Excel thành 1 file có nhiều sheet như thế nào?
Một số người cần giữ mỗi file chi nhánh thành một sheet riêng trong file tổng, thay vì gộp thành một bảng. Power Query không làm việc này. Bạn dùng lệnh Move or Copy như sau:
Bạn mở file tổng hợp và file chi nhánh cần chép, ví dụ
Ha Noi.xlsx.Trong file chi nhánh, bạn chuột phải vào tên sheet ở dưới cùng và chọn Move or Copy. Bạn cũng có thể chọn tab Home, bấm Format, chọn Move or Copy Sheet.
Ở ô To book, bạn chọn file tổng hợp.
Bạn tích ô Create a copy để file chi nhánh vẫn giữ sheet của mình, rồi bấm OK.

Nếu file tổng hợp đã có sheet trùng tên, Excel thêm số vào cuối tên sheet mới, ví dụ "Doanh thu (2)". Bạn đổi tên sheet theo tên chi nhánh để dễ tìm. Cách này phải làm lại bằng tay cho từng file và không tự cập nhật khi file chi nhánh thay đổi.
Bạn nên gộp file bằng Power Query, VBA hay sao chép tay?
Cách | Khi nào nên dùng | Điểm cần lưu ý |
|---|---|---|
Sao chép tay | Bạn chỉ gộp 2 đến 3 file một lần. | Bạn dễ dán lệch cột hoặc dán trùng, và phải làm lại từ đầu khi có file mới. |
Power Query | Bạn gộp nhiều file cùng cấu trúc và gộp lặp lại hằng tháng. | Bạn không cần viết code. Khi có file mới, bạn chỉ cần bấm Refresh All. |
VBA | Bạn cần xử lý riêng mà Power Query không làm được, ví dụ định dạng lại từng file. | File phải lưu đuôi .xlsm và người dùng phải bật macro. Nhiều đoạn code trên mạng ghép theo vị trí cột, và chạy lại sẽ gộp trùng nếu code không xóa dữ liệu cũ. |
Công cụ gộp online | Dữ liệu không có thông tin nội bộ. | Bạn phải tải file lên trang web bên ngoài, nên không hợp với dữ liệu lương, doanh thu của công ty. |
Nếu bạn dùng VBA, bạn có thể đọc thêm bài cách bật macro trong Excel để chạy được file .xlsm.
Bạn hay gặp lỗi gì khi gộp file bằng Power Query?
Dấu hiệu | Nguyên nhân | Cách sửa |
|---|---|---|
Lệnh làm mới báo | Một file có sheet tên khác với file mẫu, ví dụ "Sheet1" thay vì "Doanh thu". Lỗi này cũng xảy ra khi file tổng hợp được lưu trong chính thư mục cần gộp. | Bạn đổi tên sheet cho giống file mẫu, và lưu file tổng hợp ra ngoài thư mục. |
Lệnh làm mới báo | Thư mục có file không phải Excel, ví dụ một file ghi chú .txt. | Bạn chuyển file đó ra khỏi thư mục. Nếu không chuyển được, bạn mở Power Query Editor, chọn bước Source của query chính, lọc cột Extension chỉ giữ |
Lệnh làm mới báo | File mẫu, tức là file đầu danh sách, đã đổi tên cột. | Bạn sửa tên cột trong file mẫu cho đúng rồi làm mới. |
Một file có cột bị trống mà không báo lỗi | Tên cột của file đó lệch với file mẫu, ví dụ "Doanh số" hay "Doanh Thu". | Bạn sửa tên cột trong file gốc rồi bấm Refresh All. |
Bảng gộp có nhiều dòng hơn tổng các file | Thư mục có thư mục con chứa file cũ, hoặc một file có dòng "Tổng cộng" ở cuối. Ví dụ, với 4 file ban đầu, file Đà Nẵng có thêm dòng Tổng cộng làm bảng thành 19 dòng thay vì 18 dòng, và tổng doanh thu tăng thêm 3.710.000. | Bạn chuyển thư mục con ra ngoài và xóa dòng tổng trong file chi nhánh. Bạn tự tính tổng trên bảng gộp. |
Cột chỉ có ở một file bị mất | Power Query lấy danh sách cột theo file mẫu. Cột "Ghi chú" chỉ có trong file TP HCM sẽ không được lấy. | Bạn thêm cột đó vào file mẫu, có thể để trống, rồi làm mới. |
Bảng không có dữ liệu mới | Bạn chưa làm mới sau khi thêm hoặc sửa file. | Bạn bấm Data, Refresh All. |
Nếu các bảng cần gộp nằm ở nhiều sheet trong cùng một file, bạn có thể đọc bài hợp nhất dữ liệu trong Excel từ nhiều sheet bằng Consolidate và VSTACK.
Bạn muốn học Excel bài bản hơn?
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.