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.

Menu Get Data trên tab Data đang mở, con trỏ chỉ vào From File rồi From Folder
Lệnh From Folder lấy tất cả file trong một thư mục

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.

Cửa sổ liệt kê 4 file Can Tho, Da Nang, Ha Noi, TP HCM trong thư mục Doanh thu chi nhanh T10, con trỏ chỉ vào nút Combine
Bạn kiểm tra danh sách có đủ 4 file và không có file lạ

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.

Danh sách của nút Combine đang mở, con trỏ chỉ vào Combine & Load
Combine & Load gộp file và đưa kết quả thẳng vào một sheet mới

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 Combine Files, sheet Doanh thu đang chọn, khung bên phải hiện 3 dòng của file Cần Thơ, con trỏ chỉ vào nút OK
Excel dùng tên sheet Doanh thu để lấy dữ liệu từ mọi file trong thư mục

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.

Bảng kết quả 18 dòng với cột Source.Name ghi tên file, khung Queries & Connections bên phải ghi 18 rows loaded
Bảng gộp có 18 dòng, đúng bằng 3 + 4 + 5 + 6 dòng của 4 file

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.

Power Query Editor đang mở query Doanh thu chi nhanh T10 thấy số của các cột từ Source.Name tới Số lượng, cột Doanh thu nằm ở mép phải, khung Queries bên trái đang thu gọn, khung Applied Steps bên phải có 7 bước từ Source tới Changed Type, thanh dưới cùng ghi 6 COLUMNS, 18 ROWS
Khung Applied Steps bên phải liệt kê các bước của query đang chọn

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ò

Doanh thu chi nhanh T10

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.

Sample File

Query phụ này trỏ tới file mẫu, ở đây là file đầu danh sách.

Parameter1 (Sample File)

Query phụ này là tham số truyền từng file vào các bước xử lý.

Transform Sample File

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.

Transform File

Query phụ này là hàm Excel tạo ra từ Transform Sample File để chạy cho từng file.

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.

Con trỏ chỉ vào nút Refresh All trên tab Data
Refresh All chạy lại query với các file đang có trong thư mục
Bảng kết quả sau khi làm mới có 22 dòng, 4 dòng Hai Phong nằm giữa Ha Noi và TP HCM, khung bên phải ghi 22 rows loaded
Bảng có thêm 4 dòng của Hải Phòng, tổng cộng 22 dòng

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.

Bảng kết quả sau khi làm mới, 6 dòng TP HCM từ dòng 18 tới dòng 23 có cột Doanh thu trống, ô F18 đang chọn
Power Query không tìm thấy cột Doanh thu trong file TP HCM nên để trống cột này

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.

Power Query Editor với cột tên BÁO CÁO DOANH THU THÁNG 10 - CAN THO, Column2, Column3, dòng tiêu đề Nhân viên, Mã hàng lặp lại trong dữ liệu và nhiều ô null
Dòng tên báo cáo bị lấy làm tiêu đề, còn tiêu đề cột thật nằm lẫn trong dữ liệu của từng file

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:

  1. 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.

  2. Ở 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.

  3. Trên tab Home, bạn bấm Remove Rows, chọn Remove Top Rows, nhập 2 vào ô Number of rows rồi bấm OK.

Hộp thoại Remove Top Rows, ô Number of rows đã nhập 2, con trỏ chỉ vào nút OK
Số 2 bỏ dòng tên báo cáo và dòng trống ở đầu mỗi file
  1. 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.

  2. 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ũ.

Query chính báo Expression.Error: The column 'BÁO CÁO DOANH THU THÁNG 10 - CAN THO' of the table wasn't found, bước Changed Type đang chọn
Bước Changed Type cũ vẫn tìm cột tên BÁO CÁO DOANH THU THÁNG 10 - CAN THO, nên báo lỗi
  1. 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.

  2. 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.

Power Query Editor sau khi sửa, cột Source.Name, Ngày, Nhân viên, Mã hàng, Số lượng đúng tên, cột Ngày kiểu Date, khung Queries đang thu gọn, thanh dưới cùng ghi 6 COLUMNS, 18 ROWS
Bảng gộp trở lại 18 dòng với đúng tên cột
  1. 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:

  1. Bạn mở file tổng hợp và file chi nhánh cần chép, ví dụ Ha Noi.xlsx.

  2. 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.

  3. Ở ô To book, bạn chọn file tổng hợp.

  4. Bạn tích ô Create a copy để file chi nhánh vẫn giữ sheet của mình, rồi bấm OK.

Hộp thoại Move or Copy, ô To book chọn Tong hop doanh thu T10.xlsx, ô Create a copy đã tích, con trỏ chỉ vào nút OK
Ô Create a copy giữ nguyên sheet trong file chi nhánh, Excel chỉ chép một bản sang file tổng hợp

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 Expression.Error: The key didn't match any rows in the table.

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 DataFormat.Error: External table is not in the expected format.

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ữ .xlsx, rồi bấm Insert khi Power Query hỏi có chèn bước mới không.

Lệnh làm mới báo Expression.Error: The column 'Doanh thu' of the table wasn't found.

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.