Có thể bạn quan tâm: Lỗi “sharing Violation” Trong Excel: Nguyên Nhân, Hậu Quả Và Giải Pháp Chi Tiết
1. Giới thiệu chung về việc merge Excel files
Trong môi trường làm việc hiện đại, dữ liệu luôn là tài sản quý giá. Đặc biệt trong các doanh nghiệp, tổ chức hay các dự án nghiên cứu, chúng ta thường phải làm việc với rất nhiều file Excel chứa các bảng dữ liệu khác nhau. Khi các file này được tạo ra riêng lẻ, việc tổng hợp, so sánh, phân tích toàn bộ dữ liệu sẽ trở nên khó khăn và tốn thời gian. Vì vậy, merge Excel files into one (gộp các file Excel thành một) trở thành một nhu cầu thiết yếu.
Việc merge không chỉ giúp:
- Tiết kiệm thời gian: Thay vì mở từng file, sao chép và dán thủ công, bạn chỉ cần một thao tác tự động.
- Đảm bảo tính nhất quán: Các cột, tiêu đề và định dạng sẽ được đồng nhất, giảm thiểu lỗi gây ra bởi thao tác thủ công.
- Dễ dàng phân tích: Khi dữ liệu nằm trong một file duy nhất, các công cụ phân tích (PivotTable, Power Query, Power BI…) sẽ hoạt động hiệu quả hơn.
- Quản lý dữ liệu tốt hơn: Giảm bớt số lượng file, tránh việc lưu trữ lãng phí và khó tìm kiếm.
Bài viết này sẽ cung cấp cho bạn hướng dẫn chi tiết cách merge Excel files into one bằng nhiều phương pháp khác nhau: sử dụng tính năng tích hợp của Excel (Power Query), viết macro VBA, dùng Python, và các công cụ trực tuyến hoặc phần mềm thứ ba. Ngoài ra, chúng ta sẽ khám phá các mẹo hay để xử lý dữ liệu phức tạp, như hợp nhất dữ liệu từ các sheet khác nhau, loại bỏ trùng lặp, và chuẩn hoá dữ liệu trước khi gộp.
Có thể bạn quan tâm: Sửa Lỗi “name Conflict” Trong Excel: Nguyên Nhân, Cách Khắc Phục Và Những Lưu Ý Quan Trọng
2. Chuẩn bị dữ liệu trước khi merge
2.1. Kiểm tra cấu trúc dữ liệu
Trước khi thực hiện gộp, bạn cần xác định rằng các file Excel có cấu trúc đồng nhất:
- Tiêu đề cột: Các file nên có cùng tên cột và cùng thứ tự. Nếu có sự khác nhau, bạn cần chuẩn hoá trước.
- Định dạng dữ liệu: Ví dụ, cột ngày tháng phải cùng định dạng (dd/mm/yyyy hoặc yyyy-mm-dd). Cột số cần được định dạng là Number, không phải Text.
- Số lượng sheet: Nếu mỗi file có nhiều sheet, xác định sheet nào cần được gộp. Thông thường, chúng ta gộp sheet có cùng tên (ví dụ “Data”).
2.2. Loại bỏ dữ liệu thừa và trùng lặp
- Xóa hàng trống: Hàng trống có thể gây lỗi khi sử dụng Power Query hoặc VBA.
- Xóa cột không cần thiết: Giảm tải dữ liệu, giúp quá trình gộp nhanh hơn.
- Kiểm tra trùng lặp: Nếu các file có dữ liệu chồng chéo, bạn có thể muốn giữ lại một bản duy nhất. Sử dụng công cụ “Remove Duplicates” trong Excel hoặc viết script để lọc.
2.3. Đặt tên file và thư mục hợp lý
- Tên file: Nên có quy tắc như
Sales_2023_Q1.xlsx,Sales_2023_Q2.xlsx… Giúp script dễ nhận dạng. - Thư mục chứa file: Đặt tất cả file cần gộp trong một thư mục duy nhất (ví dụ
C:\Data\Sales\) để script chỉ cần đọc từ một đường dẫn.
Có thể bạn quan tâm: Cách Định Dạng Dấu Chấm Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
3. Phương pháp 1: Sử dụng Power Query (Get & Transform)
Power Query là công cụ mạnh mẽ tích hợp trong Excel 2026 trở lên (và trong Excel cho Office 365). Nó cho phép nhập, chuyển đổi và gộp dữ liệu một cách trực quan mà không cần lập trình.
3.1. Bước 1: Mở Power Query
- Mở một file Excel mới (hoặc file mà bạn muốn chứa dữ liệu gộp).
- Vào tab Data → Get Data → From File → From Folder.
3.2. Bước 2: Chỉ định thư mục chứa các file
- Trong cửa sổ “Folder”, nhập đường dẫn tới thư mục chứa các file Excel cần gộp (ví dụ
C:\Data\Sales\). - Nhấn OK. Power Query sẽ liệt kê tất cả các file trong thư mục.
3.3. Bước 3: Xác định sheet cần gộp
- Trong cửa sổ Folder, nhấn Transform Data.
- Bạn sẽ thấy bảng chứa các cột
Content,Name,Extension, …. - Chọn cột Content, nhấn Add Column → Custom Column.
- Đặt tên cột mới (ví dụ
Data), công thức:=Excel.Workbook(Content). - Nhấn OK. Cột
Datasẽ chứa bảng các sheet và dữ liệu trong mỗi file.
3.4. Bước 4: Mở rộng dữ liệu sheet
- Nhấn biểu tượng mở rộng (hai mũi tên) ở cột
Data. - Chọn Sheet (hoặc
Datatùy vào tên cột) và Data. - Đánh dấu Use original column name as prefix để tránh trùng tên.
- Nhấn OK. Bây giờ bạn có một bảng liệt kê các sheet và nội dung của chúng.
3.5. Bước 5: Lọc sheet cần gộp
- Nếu các file có nhiều sheet nhưng bạn chỉ muốn gộp sheet có tên “Data”, hãy lọc cột Name (hoặc Sheet) bằng cách nhấp vào mũi tên lọc và chọn “Data”.
3.6. Bước 6: Mở rộng dữ liệu thực tế
- Nhấn mở rộng trên cột Data (lại là hai mũi tên).
- Chọn các cột dữ liệu cần (thường là toàn bộ các cột). Đảm bảo Use original column name as prefix được bỏ chọn để có tiêu đề gốc.
- Nhấn OK.
3.7. Bước 7: Xử lý tiêu đề và loại bỏ hàng không cần
- Thông thường, hàng đầu tiên của mỗi sheet sẽ là tiêu đề. Để làm tiêu đề chung, chọn Home → Use First Row as Headers.
- Nếu có hàng trống hoặc thông tin thừa, sử dụng Remove Rows → Remove Blank Rows.
3.8. Bước 8: Loại bỏ trùng lặp (nếu cần)
- Chọn toàn bộ cột, vào Home → Remove Duplicates.
3.9. Bước 9: Tải dữ liệu về Excel
- Khi mọi thứ đã ổn, nhấn Home → Close & Load.
- Dữ liệu gộp sẽ được tải vào một sheet mới trong file hiện tại.
Ưu điểm của Power Query
- Giao diện trực quan, không cần viết code.
- Tự động cập nhật: Khi bạn thêm file mới vào thư mục, chỉ cần refresh query để dữ liệu cập nhật.
- Xử lý lớn: Có khả năng xử lý hàng chục nghìn dòng mà không gây treo Excel.
Nhược điểm
- Yêu cầu Excel 2026 trở lên (hoặc Office 365). Phiên bản Excel 2026/2026 cần cài thêm add‑in.
- Đôi khi gặp lỗi khi các file có cấu trúc không đồng nhất (cột thiếu hoặc thứ tự khác nhau).
4. Phương pháp 2: Sử dụng VBA Macro
Nếu bạn làm việc trong môi trường không có Power Query hoặc muốn tích hợp vào một macro hiện có, VBA là lựa chọn tốt. Dưới đây là một đoạn code mẫu để merge Excel files into one.
4.1. Mã VBA mẫu
Sub MergeExcelFiles() Dim FolderPath As String Dim FileName As String Dim wbSource As Workbook Dim wsSource As Worksheet Dim wsDest As Worksheet Dim LastRowDest As Long Dim HeaderCopied As Boolean '--- Thiết lập đường dẫn thư mục chứa các file --- FolderPath = "C:\Data\Sales\" ' <<--- Thay đổi đường dẫn của bạn If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\" '--- Tạo sheet đích (nếu chưa có) --- Set wsDest = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) wsDest.Name = "MergedData" HeaderCopied = False '--- Lặp qua từng file trong thư mục --- FileName = Dir(FolderPath & ".xlsx") ' Chỉ lấy file .xlsx, có thể thay .xls hoặc .xlsm Do While FileName <> "" Set wbSource = Workbooks.Open(Filename:=FolderPath & FileName, ReadOnly:=True) ' Giả sử dữ liệu cần gộp nằm ở sheet đầu tiên, hoặc có tên cụ thể: Set wsSource = wbSource.Sheets(1) ' hoặc wbSource.Sheets("Data") ' Xác định vị trí cuối cùng của dữ liệu đích If HeaderCopied = False Then ' Sao chép tiêu đề (hàng 1) wsSource.Rows(1).Copy Destination:=wsDest.Rows(1) HeaderCopied = True LastRowDest = 2 Else LastRowDest = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 1 End If ' Sao chép dữ liệu (bắt đầu từ hàng 2, bỏ qua tiêu đề) wsSource.Range("A2", wsSource.Cells(wsSource.Rows.Count, wsSource.UsedRange.Columns.Count).End(xlUp)).Copy _ Destination:=wsDest.Cells(LastRowDest, 1) wbSource.Close SaveChanges:=False FileName = Dir ' Lấy tên file tiếp theo Loop MsgBox "Hoàn tất gộp dữ liệu! Tổng số dòng: " & wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row - 1, vbInformation
End Sub
4.2. Giải thích chi tiết
| Phần | Mô tả |
|---|---|
FolderPath | Đường dẫn tới thư mục chứa các file Excel. |
Dir(FolderPath & ".xlsx") | Lấy danh sách các file có phần mở rộng .xlsx. Thay thành .xls để bao gồm .xls và .xlsm. |
Workbooks.Open(..., ReadOnly:=True) | Mở file ở chế độ chỉ đọc, tránh thay đổi dữ liệu gốc. |
wsSource = wbSource.Sheets(1) | Chọn sheet cần gộp (có thể thay bằng tên sheet). |
HeaderCopied | Cờ để sao chép tiêu đề chỉ một lần. |
wsSource.Rows(1).Copy Destination:=wsDest.Rows(1) | Sao chép tiêu đề. |
wsSource.Range("A2"... | Sao chép dữ liệu từ hàng 2 trở xuống, tránh tiêu đề trùng. |
LastRowDest | Xác định vị trí dòng cuối cùng hiện tại để dán dữ liệu tiếp theo. |
MsgBox | Thông báo hoàn thành và số dòng đã gộp. |
4.3. Những lưu ý khi dùng VBA
- Định dạng cột: Khi sao chép, Excel sẽ giữ định dạng gốc. Nếu các file có định dạng khác nhau, hãy chuẩn hoá sau khi gộp (sử dụng
NumberFormat,TextToColumns, …). - Hiệu suất: Đối với hàng triệu dòng, VBA có thể chậm. Bạn có thể tắt
ScreenUpdating,CalculationvàEnableEventstrong quá trình chạy:vba Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False '... code ... Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True - Xử lý lỗi: Sử dụng
On Error GoTo ErrHandlerđể bắt lỗi khi mở file bị hỏng hoặc sheet không tồn tại. - Đa sheet: Nếu muốn gộp nhiều sheet, lặp qua
wbSource.Sheetsvà tạo sheet đích tương ứng.
5. Phương pháp 3: Dùng Python (pandas) để merge Excel files
Có thể bạn quan tâm: Cách Sử Dụng Hàm Max Kèm Điều Kiện Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Python, đặc biệt là thư viện pandas, là công cụ mạnh mẽ cho việc xử lý dữ liệu lớn. Khi bạn cần merge Excel files into one trong môi trường tự động hoá, Python là lựa chọn tối ưu.
5.1. Cài đặt môi trường
pip install pandas openpyxl
pandasđể đọc/ghi dữ liệu.openpyxlđể làm việc với định dạng .xlsx.
5.2. Script mẫu
import os
import pandas as pd # Thư mục chứa các file Excel
folder_path = r"C:\Data\Sales" # Danh sách các file .xlsx trong thư mục
excel_files = f for f in os.listdir(folder_path) if f.endswith('.xlsx') # Danh sách DataFrame để lưu trữ dữ liệu
df_list = for file in excel_files: file_path = os.path.join(folder_path, file) # Đọc sheet đầu tiên (hoặc chỉ định sheet_name='Data') df = pd.read_excel(file_path, sheet_name=0) # hoặc sheet_name='Data' # Thêm một cột ghi nguồn (tùy chọn) df'SourceFile' = file df_list.append(df) # Gộp tất cả DataFrame lại với nhau (theo chiều dọc)
merged_df = pd.concat(df_list, ignore_index=True) # Loại bỏ trùng lặp (nếu muốn)
merged_df.drop_duplicates(inplace=True) # Lưu ra file Excel mới
output_path = os.path.join(folder_path, "Merged_Sales_Data.xlsx")
merged_df.to_excel(output_path, index=False) print(f"Đã gộp {len(excel_files)} file, tổng cộng {merged_df.shape0} dòng.")
5.3. Giải thích chi tiết
| Bước | Mô tả |
|---|---|
os.listdir | Lấy danh sách file trong thư mục. |
pd.read_excel(..., sheet_name=0) | Đọc sheet đầu tiên; có thể thay bằng sheet_name='Data'. |
df'SourceFile' = file | Thêm cột nguồn để biết dữ liệu đến từ file nào (rất hữu ích cho kiểm tra). |
pd.concat(df_list, ignore_index=True) | Gộp các DataFrame lại thành một. |
drop_duplicates | Loại bỏ các bản ghi trùng lặp dựa trên toàn bộ cột. |
to_excel | Ghi ra file Excel mới. |
5.4. Khi nào nên dùng Python?
- Dữ liệu lớn: Hàng trăm nghìn đến hàng triệu dòng, pandas xử lý nhanh hơn VBA.
- Tự động hoá: Bạn muốn chạy script hàng ngày/tuần trên server.
- Xử lý phức tạp: Thêm các bước làm sạch, chuyển đổi dữ liệu (ví dụ, tách cột, thay đổi định dạng ngày, tính toán mới) trước khi gộp.
- Nhiều định dạng: Bạn có thể đọc CSV, JSON, SQL và gộp chung với Excel.
5.5. Những lưu ý
- Phiên bản Excel:
openpyxlchỉ hỗ trợ .xlsx, .xlsm. Đối với .xls cũ, cần càixlrd(phiên bản < 2.0) hoặc chuyển sang .xlsx. - Bộ nhớ: Khi gộp rất nhiều file lớn, pandas sẽ tải toàn bộ vào RAM. Nếu RAM không đủ, có thể dùng
chunksizehoặc thư viện dask để xử lý phân đoạn. - Định dạng: Khi ghi ra, pandas sẽ mất một số định dạng (màu, font). Nếu cần giữ định dạng, có thể sử dụng
openpyxlđể áp dụng lại sau khi ghi.
6. Phương pháp 4: Công cụ và phần mềm thứ ba
Nếu bạn không muốn viết code, có một số công cụ đồ họa hỗ trợ merge Excel files:
| Công cụ | Nền tảng | Đặc điểm nổi bật |
|---|---|---|
| Kutools for Excel | Windows | Thêm tính năng “Combine Worksheets” cho phép chọn thư mục và gộp nhanh. |
| Ablebits Consolidate Worksheets Wizard | Windows | Gộp dữ liệu, loại bỏ trùng lặp, tùy chọn hợp nhất theo cột khóa. |
| RPA tools (UiPath, Power Automate Desktop) | Windows | Tự động hoá quy trình kéo file, copy‑paste, lưu thành file tổng. |
| Online services (Combine Sheets, Merge Excel Online) | Web | Không cần cài đặt, chỉ tải lên file, nhận file gộp. Thường có giới hạn kích thước. |
| Google Sheets + Apps Script | Web | Nhập file Excel vào Google Drive, dùng Apps Script để gộp vào một sheet. |
6.1. Ví dụ nhanh với Kutools
- Cài đặt Kutools, mở Excel.
- Vào Kutools → Combine → Combine Worksheets.
- Chọn “Combine files in a folder” → Chỉ định thư mục.
- Chọn sheet cần gộp (hoặc tất cả), chọn “Add a column to identify the source file” nếu muốn.
- Nhấn OK, kết quả sẽ được tạo ra trong một sheet mới.
6.2. Khi nào nên dùng công cụ thứ ba?
- Không quen viết code và cần giải pháp nhanh.
- Yêu cầu giao diện người dùng để chọn file, cột, tùy chỉnh.
- Công việc không quá phức tạp (không cần xử lý dữ liệu lớn, không cần tự động hoá định kỳ).
7. Xử lý các trường hợp đặc biệt
7.1. Gộp các sheet có cấu trúc khác nhau
Nếu các file có cột khác nhau, bạn có thể:
- Hợp nhất cột: Khi dùng Power Query, chọn Append Queries → Three or more tables, Power Query sẽ tự động tạo cột tổng hợp, các giá trị còn thiếu sẽ để
null. - Trong VBA, bạn có thể tạo một danh sách các tiêu đề duy nhất, sau đó copy dữ liệu vào cột phù hợp bằng cách tìm vị trí tiêu đề trong sheet đích.
- Trong pandas, dùng
pd.concat(..., sort=True). Tham sốsort=Truesẽ sắp xếp các cột và chèn NaN cho các cột thiếu.
merged_df = pd.concat(df_list, ignore_index=True, sort=True)
7.2. Gộp dữ liệu theo ngày tháng hoặc khóa
Nếu muốn gộp dữ liệu dựa trên một khóa chung (ví dụ, EmployeeID), bạn có thể:
- Power Query: Sử dụng Merge Queries (inner/left join) để kết hợp dữ liệu dựa trên khóa.
- VBA: Dùng Dictionary để lưu trữ dữ liệu đã xuất hiện, tránh trùng.
- pandas: Sử dụng
groupbyhoặcmerge:python final_df = pd.concat(df_list).groupby('EmployeeID', as_index=False).sum()
7.3. Loại bỏ trùng lặp dựa trên một cột
Trong Excel, sau khi gộp, chọn cột khóa → Data → Remove Duplicates → Chỉ chọn cột đó.
Trong VBA:
wsDest.Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1), Header:=xlYes
Trong pandas:
merged_df.drop_duplicates(subset='EmployeeID', keep='first', inplace=True)
7.4. Chuẩn hoá dữ liệu ngày tháng
- Power Query: Chọn cột ngày → Transform → Data Type → Date.
- VBA: Dùng
CDatehoặcDateValue. - pandas:
df'Date' = pd.to_datetime(df'Date', dayfirst=True).
8. Tối ưu hoá hiệu suất khi merge hàng triệu dòng
| Phương pháp | Mẹo tối ưu |
|---|---|
| Power Query | – Tắt tính năng “Enable Load to Data Model” nếu không dùng. – Sử dụng “Load To… Only Create Connection” để giảm tải. |
| VBA | – Tắt ScreenUpdating, EnableEvents, Calculation. – Thay vì copy‑paste, dùng Array để truyền dữ liệu nhanh hơn: myArray = wsSource.Range(...).Value. |
| Python pandas | – Sử dụng chunksize để đọc file theo khối. – Nếu RAM hạn chế, dùng dask.dataframe hoặc modin. |
| Công cụ thứ ba | – Kiểm tra giới hạn dòng của phần mềm, một số công cụ chỉ hỗ trợ 1‑2 triệu dòng. |
9. Tự động hoá quy trình merge định kỳ
9.1. Sử dụng Task Scheduler (Windows)
- Lưu macro VBA vào một file
.xlsm. - Tạo file batch:
bat "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" "C:\Scripts\MergeMacro.xlsm" /e - Trong Task Scheduler, tạo task chạy batch vào thời gian mong muốn (hàng ngày, hàng tuần).
9.2. Sử dụng cron + Python (Linux/macOS)
0 2 /usr/bin/python3 /home/user/scripts/merge_excel.py >> /home/user/logs/merge.log 2>&1
9.3. Power Automate (Office 365)
- Tạo flow: When a file is created (OneDrive/SharePoint) → List rows present in a table (Excel) → Append to another Excel file. Flow này sẽ tự động lấy dữ liệu mới và gộp vào file tổng.
10. Kiểm tra và xác thực dữ liệu sau khi merge
Sau khi gộp thành công, việc kiểm tra chất lượng là bước không thể bỏ qua:
- Kiểm tra số dòng: Đảm bảo tổng số dòng bằng tổng các dòng trong các file nguồn (trừ tiêu đề). Sử dụng công thức
=SUM(COUNTIF(...))hoặc trong pandaslen(df). - Kiểm tra trùng lặp: Dùng
Remove Duplicateshoặcdrop_duplicates. - Kiểm tra giá trị ngoại lệ: Ví dụ, ngày tháng không hợp lệ, số âm trong cột doanh thu. Dùng conditional formatting trong Excel hoặc
df.describe()trong pandas. - Kiểm tra định dạng: Đảm bảo cột ngày tháng là Date, cột tiền tệ là Currency.
- Lưu bản sao: Luôn giữ một bản sao của file gốc và file đã merge, để có thể quay lại nếu phát hiện lỗi.
11. Tổng kết
Việc merge Excel files into one không còn là công việc tẻ nhạt hay mất thời gian nếu bạn biết sử dụng đúng công cụ và quy trình. Bài viết đã trình bày chi tiết:
- Chuẩn bị dữ liệu: Kiểm tra cấu trúc, loại bỏ trùng lặp, chuẩn hoá định dạng.
- Power Query: Giải pháp trực quan, tự động cập nhật.
- VBA Macro: Tùy biến cao, phù hợp cho môi trường không có Power Query.
- Python (pandas): Xử lý dữ liệu lớn, tự động hoá và mở rộng.
- Công cụ thứ ba: Giải pháp nhanh cho người dùng không muốn viết code.
- Xử lý trường hợp đặc biệt, tối ưu hoá hiệu suất và tự động hoá định kỳ.
- Kiểm tra chất lượng dữ liệu sau khi gộp.
Bạn có thể lựa chọn phương pháp phù hợp với môi trường làm việc, khối lượng dữ liệu và mức độ tự động hoá mong muốn. Khi đã nắm vững các kỹ thuật trên, việc gộp Excel sẽ trở nên nhanh chóng, chính xác và giảm thiểu tối đa lỗi do thao tác thủ công.
Chúc bạn thành công trong việc merge Excel files into one và khai thác dữ liệu hiệu quả hơn!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.