Kết Hợp Nhiều Tệp Excel Vào Một Sheet: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Tạo Và Sử Dụng Column & Line Chart Trong Excel: Từ Cơ Bản Đến Nâng Cao

Mở Đầu: Tại Sao Bạn Cần Kết Hợp Nhiều Excel Vào Một Sheet?

Trong môi trường làm việc hiện đại, dữ liệu ngày càng trở nên phong phú và đa dạng. Các phòng ban, đội nhóm thường nhận được nhiều tệp Excel từ các nguồn khác nhau: báo cáo doanh số, danh sách khách hàng, kết quả khảo sát, dữ liệu tài chính,… Khi phải xử lý và phân tích những thông tin này, việc mở từng file một, sao chép thủ công vào một bảng tổng hợp không chỉ tốn thời gian mà còn dễ gây sai sót. Vì vậy, việc kết hợp (combine) nhiều file Excel vào một sheet trở thành nhu cầu thiết yếu, giúp:

• Tiết kiệm thời gian: Tự động hoá quá trình gộp dữ liệu.
• Đảm bảo tính nhất quán: Tránh lỗi nhập liệu thủ công.
• Tăng hiệu quả phân tích: Có một nguồn dữ liệu duy nhất để áp dụng các công cụ phân tích, PivotTable, Power Query, v.v.
• Cải thiện quản lý dự án: Dễ dàng theo dõi tiến độ, kiểm soát version.

Bài viết dưới đây sẽ cung cấp cho bạn hướng dẫn chi tiết (hơn 1500 chữ) về cách kết hợp nhiều file Excel thành một sheet, bao gồm:

1. Phương pháp thủ công (Copy‑Paste, Consolidate).
2. Sử dụng các công cụ tích hợp: Power Query, VBA Macro.
3. Giải pháp qua Python (pandas) cho người thích lập trình.
4. Mẹo tối ưu hoá: Xử lý dữ liệu trùng lặp, chuẩn hoá định dạng, tự động cập nhật.
5. Các trường hợp thực tế và bảng so sánh giữa các phương pháp.

Hãy cùng khám phá từng bước một, từ cơ bản đến nâng cao, để bạn có thể lựa chọn cách phù hợp nhất với nhu cầu và kỹ năng của mình.

Có thể bạn quan tâm: Code Vba Hay Cho Excel: Bộ Sưu Tập Các Mẫu Mã Đỉnh Cao Giúp Tăng Năng Suất Công Việc

1. Hiểu Rõ Cấu Trúc Dữ Liệu Trước Khi Kết Hợp

Trước khi thực hiện bất kỳ phương pháp nào, bạn cần nắm rõ cấu trúc của các file Excel muốn gộp:

Các yếu tố cần kiểm tra:

1. Tiêu đề (Header): Tất cả các file có cùng tiêu đề hay không? Nếu không, cần chuẩn hoá.
2. Định dạng dữ liệu: Ngày, số, văn bản có thống nhất không? Ví dụ ngày tháng có thể hiển thị dưới dạng dd/mm/yyyy, mm/dd/yyyy, hoặc yyyy-mm-dd.
3. Số cột và thứ tự: Cột A trong file 1 có phải là “Mã khách” trong file 2 không? Nếu không, khi gộp sẽ gây nhầm lẫn.
4. Dòng trống và dữ liệu không cần thiết: Loại bỏ những dòng chứa tiêu đề phụ, ghi chú, hoặc tổng cộng (Total) nếu không cần.

Bước chuẩn bị:

• Tạo một file mẫu (master.xlsx) để làm nơi lưu trữ dữ liệu đã gộp.
• Đặt một sheet chuẩn (ví dụ: “Data”) và tạo tiêu đề chuẩn: Mã Khách, Tên Khách, Doanh Thu, Ngày.
• Lưu ý: Khi có nhiều sheet trong một file, chỉ lấy sheet có dữ liệu thực tế (thường là Sheet1 hoặc dựa trên tên).

Có thể bạn quan tâm: Code 39 Font Cho Excel: Hướng Dẫn Chi Tiết Từ Cài Đặt Đến Ứng Dụng Thực Tiễn

2. Phương Pháp Thủ Công: Copy‑Paste và Consolidate

2.1. Copy‑Paste Thông Thường

Ưu điểm: Đơn giản, không cần kỹ năng đặc biệt.
Nhược điểm: Rất tốn thời gian, dễ sai sót khi có nhiều file.

Các bước thực hiện:

1. Mở file master.xlsx, tạo sheet “Data”.
2. Mở file Excel đầu tiên, chọn toàn bộ dữ liệu (Ctrl+A), sao chép (Ctrl+C).
3. Quay lại master.xlsx, dán vào hàng 2 (để giữ tiêu đề ở hàng 1). Dùng “Paste Values” để tránh dán công thức.
4. Lặp lại với các file còn lại, đảm bảo dán vào hàng tiếp theo (công cụ “Go To” → Ctrl+End để xác định vị trí cuối cùng).
5. Kiểm tra lại các cột ngày tháng, sử dụng Data → Text to Columns hoặc =DATEVALUE() để chuẩn hoá.

2.2. Sử Dụng Công Cụ Consolidate

Excel cung cấp tính năng Data → Consolidate cho phép tổng hợp dữ liệu dựa trên vị trí hoặc tiêu đề.

Cách thực hiện:

1. Chuẩn bị một sheet “Template” với tiêu đề giống như các file nguồn.
2. Vào Data → Consolidate.
3. Chọn Function (SUM, COUNT, AVERAGE…) tùy mục đích. Đối với gộp dữ liệu nguyên bản, chọn None (trong một số phiên bản Excel, không có “None”, bạn có thể chọn “Sum” và sau đó xóa các giá trị tổng hợp, nhưng cách này không tối ưu).
4. Nhấn Add và duyệt tới từng file → chọn vùng dữ liệu → Add.
5. Tick Top row và Left column nếu dữ liệu có tiêu đề và nhãn.
6. Nhấn OK, Excel sẽ tạo một bảng tổng hợp.

Lưu ý: Consolidate thường dùng để tổng hợp các giá trị (ví dụ tổng doanh thu theo tháng). Nếu mục tiêu là liệt kê chi tiết từng dòng, thì phương pháp này không phù hợp.

3. Power Query: Giải Pháp Mạnh Mẽ Tích Hợp Trực Tiếp Trong Excel

3.1. Power Query Là Gì?

Power Query (trong Excel 2026 trở lên, hoặc “Get & Transform” trong Excel 2026) là một công cụ ETL (Extract, Transform, Load) cho phép:

• Kết nối tới nhiều nguồn dữ liệu (file, database, web).
• Tự động hoá quá trình làm sạch, chuẩn hoá.
• Tạo query (truy vấn) để gộp, lọc, tính toán.
• Cập nhật dữ liệu chỉ bằng một lần click “Refresh”.

3.2. Chuẩn Bị Thư Mục Chứa Các File Excel

Giả sử các file bạn muốn gộp đều nằm trong một thư mục C:\Data\Sales\. Đặt tất cả file vào đó, không có file không liên quan.

3.3. Các Bước Thực Hiện Power Query

Bước 1: Nhập Dữ Liệu Từ Thư Mục

1. Mở Excel → Data → Get Data → From File → From Folder.
2. Dẫn tới thư mục C:\Data\Sales\ và nhấn OK.
3. Power Query hiển thị danh sách các file (Tên, Đường dẫn, …). Nhấn Transform Data.

Bước 2: Mở Các File và Lấy Dữ Liệu

1. Trong Power Query Editor, bạn sẽ thấy một cột Content chứa binary data của từng file.
2. Nhấn vào biểu tượng double‑arrow (Expand) ở cột Content → Add Custom Column → =Excel.Workbook(Content).
3. Tạo cột mới “Data” chứa bảng dữ liệu của mỗi file. Nhấn Expand cột Data → chọn Sheet1 (hoặc tên sheet thực tế) → OK.
4. Lúc này, bạn sẽ nhận được các cột: Name, Extension, Date accessed, Date modified, Data (trong Data có các cột A, B, C…).

Bước 3: Lựa Chọn Sheet và Cột Cần Thiết

1. Nếu mỗi file có nhiều sheet, hãy filter cột Item (tên sheet) để chỉ giữ sheet chứa dữ liệu (ví dụ “Sheet1” hoặc “Data”).
2. Click vào Expand ở cột Data → chọn các cột thực tế (Mã Khách, Tên, Doanh Thu, Ngày) → OK.

Bước 4: Xử Lý Dữ Liệu

• Loại bỏ tiêu đề trùng lặp: Khi mỗi file có tiêu đề, Power Query sẽ tự động nhận chúng là hàng dữ liệu. Dùng Remove Top Rows → 1 (nếu tiêu đề ở hàng đầu) và Promote Headers để đặt lại tiêu đề.
• Chuẩn hoá định dạng ngày: Chọn cột ngày → Transform → Data Type → Date. Nếu Excel không nhận đúng, dùng Add Column → Custom Column với công thức =Date.FromText(Ngày).
• Xóa dòng trống: Home → Remove Rows → Remove Blank Rows.
• Xử lý dữ liệu trùng lặp: Home → Remove Rows → Remove Duplicates (chọn cột “Mã Khách” hoặc toàn bộ hàng tùy mục tiêu).

Bước 5: Load Kết Quả Vào Sheet

1. Khi đã hài lòng, nhấn Home → Close & Load → Close & Load To….
2. Chọn Table và Existing Worksheet → chọn ô A1 trong sheet “Data” của master.xlsx.
3. Nhấn OK. Bây giờ, dữ liệu đã được gộp vào một bảng duy nhất.

Bước 6: Tự Động Cập Nhật

• Khi có file mới được thêm vào thư mục, chỉ cần Refresh (Data → Refresh All) và Power Query sẽ tự động nhập file mới vào bảng.

3.4. Lưu Ý Khi Sử Dụng Power Query

4. VBA Macro: Tự Động Hóa Quá Trình Gộp File

4.1. Khi Nào Nên Dùng VBA?

• Khi cần tích hợp vào một workflow nội bộ, chạy một nút bấm.
• Khi muốn điều chỉnh chi tiết từng bước (ví dụ: bỏ qua các file có tên chứa “_old”).
• Khi môi trường không hỗ trợ Power Query (Excel 2026 hoặc 2026 không có tính năng này).

4.2. Mã VBA Cơ Bản

Sub CombineExcelFiles() Dim FolderPath As String Dim FileName As String Dim wbSrc As Workbook Dim wsSrc As Worksheet Dim wsDest As Worksheet Dim LastRow As Long Dim HeaderCopied As Boolean ' Đặt đường dẫn thư mục chứa các file FolderPath = "C:\Data\Sales\" FileName = Dir(FolderPath & ".xlsx") Set wsDest = ThisWorkbook.Sheets("Data") ' Sheet đích wsDest.Cells.Clear ' Xóa dữ liệu cũ nếu cần HeaderCopied = False Do While FileName <> "" Set wbSrc = Workbooks.Open(FolderPath & FileName, ReadOnly:=True) ' Giả sử dữ liệu ở Sheet1 Set wsSrc = wbSrc.Sheets(1) If Not HeaderCopied Then ' Sao chép tiêu đề wsSrc.Rows(1).Copy Destination:=wsDest.Rows(1) HeaderCopied = True LastRow = 2 Else LastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1 End If ' Xác định phạm vi dữ liệu (bỏ qua tiêu đề) Dim srcLastRow As Long srcLastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row If srcLastRow > 1 Then wsSrc.Range("A2", wsSrc.Cells(srcLastRow, wsSrc.UsedRange.Columns.Count)).Copy _ Destination:=wsDest.Cells(LastRow, 1) End If wbSrc.Close SaveChanges:=False FileName = Dir() ' Lấy file tiếp theo Loop MsgBox "Hoàn thành! Đã gộp dữ liệu vào sheet 'Data'.", vbInformation
End Sub

Giải Thích Mã:

• FolderPath là đường dẫn thư mục chứa các file.
• Dir(FolderPath & ".xlsx") lấy tên file đầu tiên, vòng lặp Do While duyệt toàn bộ file.
• HeaderCopied đảm bảo chỉ sao chép tiêu đề một lần.
• srcLastRow xác định dòng cuối cùng của mỗi file, sao chép dữ liệu từ A2 tới cột cuối cùng.
• MsgBox thông báo hoàn thành.

4.3. Nâng Cao Mã VBA

• Bỏ qua file không cần: Thêm điều kiện If InStr(FileName, "_old") > 0 Then GoTo NextFile.
• Xử lý nhiều sheet: Dùng vòng lặp For Each wsSrc In wbSrc.Worksheets.
• Kiểm tra trùng lặp: Tạo một Dictionary lưu các Mã Khách đã xuất hiện, chỉ copy nếu chưa tồn tại.
• Định dạng ngày: wsDest.Columns("D").NumberFormat = "dd/mm/yyyy".

4.4. Lưu Ý Khi Dùng VBA

5. Sử Dụng Python (pandas) Để Kết Hợp Excel

Có thể bạn quan tâm: Cách Kết Hợp Hai Biểu Đồ Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

5.1. Khi Nào Nên Chọn Python?

• Khi dữ liệu khổng lồ (hàng triệu) mà Excel gặp giới hạn (1,048,576 hàng).
• Khi cần xử lý phức tạp: merge, join, pivot, tính thống kê nâng cao.
• Khi muốn tích hợp vào pipeline dữ liệu (ETL) hoặc chạy trên server.

5.2. Cài Đặt Môi Trường

pip install pandas openpyxl

• pandas là thư viện phân tích dữ liệu.
• openpyxl hỗ trợ đọc/ghi file .xlsx.

5.3. Mã Python Đơn Giản

import pandas as pd
import os folder = r"C:\Data\Sales"
all_files = f for f in os.listdir(folder) if f.endswith('.xlsx') df_list = for f in all_files: path = os.path.join(folder, f) # Giả sử dữ liệu ở Sheet1, bỏ qua hàng đầu nếu là tiêu đề df = pd.read_excel(path, sheet_name=0, header=0) df_list.append(df) # Gộp tất cả DataFrame
combined = pd.concat(df_list, ignore_index=True) # Xử lý ngày tháng (giả sử cột 'Ngày')
combined'Ngày' = pd.to_datetime(combined'Ngày', errors='coerce', dayfirst=True) # Loại bỏ trùng lặp dựa trên 'Mã Khách'
combined = combined.drop_duplicates(subset='Mã Khách') # Lưu kết quả
combined.to_excel(r"C:\Data\Sales\master.xlsx", sheet_name='Data', index=False)
print("Hoàn thành! Dữ liệu đã được lưu vào master.xlsx")

Giải Thích:

• os.listdir lấy danh sách file.
• pd.read_excel đọc từng file, header=0 để dùng hàng đầu làm tiêu đề.
• pd.concat gộp danh sách DataFrame.
• pd.to_datetime chuẩn hoá ngày tháng, dayfirst=True cho định dạng dd/mm/yyyy.
• drop_duplicates loại bỏ bản sao.
• to_excel ghi ra file master.

5.4. Mở Rộng: Xử Lý Các File Có Cấu Trúc Khác Nhau

for f in all_files: path = os.path.join(folder, f) # Kiểm tra sheet có tên "Data" hoặc "Sheet1" xls = pd.ExcelFile(path) sheet = 'Data' if 'Data' in xls.sheet_names else xls.sheet_names0 df = pd.read_excel(xls, sheet_name=sheet, header=0) # Đổi tên cột nếu không khớp df.rename(columns={'CustomerID':'Mã Khách', 'CustomerName':'Tên Khách', 'Revenue':'Doanh Thu', 'Date':'Ngày'}, inplace=True) df_list.append(df)

5.5. Ưu Nhược Điểm So Sánh Với Excel

6. Xử Lý Dữ Liệu Trùng Lặp và Làm Sạch Sau Khi Gộp

Khi dữ liệu được gộp, khả năng xuất hiện bản sao là cao, đặc biệt khi:

• Các file chứa cùng một khách hàng trong nhiều tháng.
• Có lỗi nhập liệu (ví dụ “Nguyễn Văn A” vs “Nguyễn V. A”).

6.1. Loại Bỏ Dòng Trùng Lặp Dựa Trên Một Cột

• Excel: Data → Remove Duplicates → Chọn cột “Mã Khách”.
• Power Query: Home → Remove Rows → Remove Duplicates.
• VBA: Sử dụng Dictionary như đã mô tả.
• Python: df.drop_duplicates(subset='Mã Khách', keep='first').

6.2. Chuẩn Hoá Dữ Liệu Văn Bản

6.3. Kiểm Tra Tính Toàn Vẹn

• Số lượng bản ghi: So sánh tổng số dòng trước và sau khi gộp.
• Tổng doanh thu: Kiểm tra tổng Doanh Thu có khớp với tổng của các file riêng lẻ.
• Kiểm tra ngày: Đảm bảo không có giá trị #VALUE! hoặc ngày “1900‑01‑00”.

7. Tự Động Hóa Quy Trình Định Kỳ

Nếu công việc gộp dữ liệu là định kỳ (hàng tuần, hàng tháng), bạn nên thiết lập tự động hoá:

7.1. Power Query + Task Scheduler

1. Tạo Power Query như trong mục 3.
2. Lưu file master.xlsx.
3. Tạo một file batch (refresh.bat):

@echo off
start "" "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" "C:\Data\Sales\master.xlsx" /e
timeout /t 10
powershell -command "(New-Object -ComObject Excel.Application).Workbooks.Open('C:\Data\Sales\master.xlsx').RefreshAll(); (New-Object -ComObject Excel.Application).Quit()"

1. Dùng Task Scheduler → Create Task → chạy refresh.bat vào thời gian mong muốn.

7.2. VBA + Workbook_Open

Private Sub Workbook_Open() Application.Run "CombineExcelFiles"
End Sub

Khi mở file, macro tự động chạy.

7.3. Python + Cron (Linux) hoặc Task Scheduler (Windows)

# file: combine_sales.sh
#!/bin/bash
source /home/user/miniconda3/bin/activate myenv
python /home/user/scripts/combine_sales.py

Thêm vào crontab:

0 2 1 /home/user/scripts/combine_sales.sh >> /home/user/logs/combine.log 2>&1

• Lệnh trên sẽ chạy mỗi Thứ Hai lúc 2h sáng.

8. Các Trường Hợp Thực Tế và Giải Pháp Tối Ưu

8.1. Trường Hợp 1: Gộp Báo Cáo Doanh Số Hàng Tháng

• Mỗi file: Sales_2023_01.xlsx, Sales_2023_02.xlsx, …
• Yêu cầu: Gộp vào một sheet, thêm cột “Tháng”.
• Giải pháp: Power Query → Add Custom Column với công thức =Text.Middle(Name,6,2) để lấy tháng từ tên file, sau đó Append.

8.2. Trường Hợp 2: Kết Hợp Danh Sách Khách Hàng Từ Nhiều Chi Nhánh

• Mỗi chi nhánh: file có cấu trúc cột khác nhau, ví dụ chi nhánh A có “SĐT”, chi nhánh B không.
• Giải pháp: Dùng VBA để tạo một mảng cột chuẩn, sau đó Loop qua từng file, kiểm tra If wsSrc.Columns("D").Value = "" Then wsDest.Columns("D").Value = "N/A".

8.3. Trường Hợp 3: Dữ Liệu Quá Lớn (> 2 triệu bản ghi)

• Giới hạn Excel: Không thể chứa.
• Giải pháp: Sử dụng Python (pandas) để gộp, sau đó lưu thành CSV hoặc Parquet. Nếu vẫn muốn xem trong Excel, chia thành nhiều file (partition) hoặc dùng Power Pivot.

8.4. Trường Hợp 4: Cập Nhật Theo Thời Gian Thực

• Yêu cầu: Khi một file mới được thêm vào thư mục, bảng tổng hợp tự động cập nhật ngay.
• Giải pháp: Power Query + Refresh on Open (tùy chọn trong Data → Queries & Connections → Properties → Refresh data when opening the file). Hoặc dùng FileSystemWatcher trong Python để theo dõi thư mục và tự chạy script.

9. Bảng So Sánh Các Phương Pháp

10. Kết Luận: Lựa Chọn Phương Pháp Phù Hợp

Sau khi đã nắm rõ các công cụ và quy trình, quyết định sử dụng phương pháp nào phụ thuộc vào:

1. Khối lượng dữ liệu: Dưới 100.000 dòng → Power Query hoặc VBA đủ. Trên 1 triệu → Python.
2. Tần suất gộp: Định kỳ hàng ngày/tuần → Power Query + Refresh hoặc VBA tự động.
3. Kỹ năng người dùng: Người không quen lập trình → Power Query. Người có kiến thức VBA → Macro. Người làm việc trong môi trường dữ liệu lớn → Python.
4. Môi trường làm việc: Nếu công ty đã có sẵn Power BI, Power Query là bước đầu tốt để sau này chuyển sang Power BI.

Lời khuyên cuối cùng: Bắt đầu từ Power Query, vì nó cung cấp giao diện trực quan, khả năng tự động cập nhật và không yêu cầu viết mã. Khi nhu cầu phức tạp hơn (xử lý hàng triệu bản ghi, tích hợp với hệ thống), chuyển sang Python. Đối với các công việc nhanh, một macro VBA đơn giản cũng đủ.

Hy vọng bài viết đã cung cấp cho bạn một hướng dẫn toàn diện từ cách làm thủ công, qua các công cụ tích hợp trong Excel, đến việc dùng lập trình để xử lý dữ liệu chuyên sâu. Hãy áp dụng ngay vào công việc thực tế, tối ưu hoá quy trình và nâng cao hiệu suất làm việc của bạn!

Tiến sĩ Lê Thống Nhất

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