Có thể bạn quan tâm: Cách Thêm Năm Vào Một Ngày Trong Excel: Hướng Dẫn Chi Tiết Và Thực Tiễn
Giới thiệu
Trong một doanh nghiệp hoặc tổ chức, việc thu thập dữ liệu từ nhiều nguồn khác nhau là điều không thể tránh khỏi. Thường thì dữ liệu được lưu trữ trên các bảng tính khác nhau, mỗi bảng có thể chứa thông tin về một bộ phận, một tháng hoặc một dự án cụ thể. Khi cần tổng hợp, phân tích hay báo cáo, việc phải di chuyển thủ công dữ liệu từ từng sheet sang một sheet tổng hợp sẽ tốn thời gian, dễ gây lỗi và mất mát thông tin. Vì vậy, một kỹ thuật quan trọng mà mọi người dùng Excel cần nắm vững là consolidate data – hợp nhất dữ liệu từ nhiều worksheet thành một bảng duy nhất.
Trong bài viết này, chúng ta sẽ đi sâu vào cách thực hiện việc hợp nhất dữ liệu bằng nhiều phương pháp: sử dụng tính năng “Consolidate” trong Excel, công thức ẩn, Power Query, và thậm chí VBA. Bạn sẽ tìm thấy các ví dụ thực tế, mẹo tối ưu và những lưu ý quan trọng giúp công việc của mình trở nên chính xác và hiệu quả hơn.
Có thể bạn quan tâm: Cách Mở File Csv Trong Excel
1. Tại sao cần hợp nhất dữ liệu?
- Tiết kiệm thời gian: Tự động lấy dữ liệu từ nhiều sheet thay vì phải copy‑paste thủ công.
- Giảm thiểu sai sót: Tránh lỗi do người nhập liệu hoặc nhầm lẫn khi chuyển đổi.
- Dễ dàng phân tích: Khi dữ liệu nằm ở một nơi, các công cụ phân tích như PivotTable, biểu đồ, hay các hàm thống kê trở nên đơn giản hơn.
- Dễ bảo trì: Khi dữ liệu nguồn thay đổi, bạn chỉ cần cập nhật một sheet nguồn, dữ liệu hợp nhất sẽ tự động cập nhật.
Có thể bạn quan tâm: Gộp Nhiều File Excel Thành 1 File Nhiều Sheet: Hướng Dẫn Chi Tiết
2. Phương pháp 1: Sử dụng tính năng “Consolidate” trong Excel
2.1. Bước 1: Chuẩn bị dữ liệu
- Đảm bảo tất cả các sheet mà bạn muốn hợp nhất có cùng cấu trúc: cùng số cột, cùng thứ tự cột và tiêu đề cột nhất quán.
- Nếu dữ liệu được phân loại theo thời gian (ví dụ: mỗi sheet là một tháng), hãy đặt tên sheet theo quy tắc dễ nhận diện.
2.2. Bước 2: Mở hộp thoại Consolidate
- Chọn sheet đích (thường là sheet mới chỉ dùng để chứa dữ liệu hợp nhất).
- Vào menu Data → Consolidate.
- Trong hộp thoại, chọn Function (Sum, Count, Average, etc.) tùy nhu cầu.
- Nhấn Add và chọn vùng dữ liệu trên sheet nguồn. Lặp lại cho từng sheet.
Lưu ý: Để chọn nhiều sheet trong cùng một thao tác, giữ phím Ctrl khi click từng vùng dữ liệu.
2.3. Bước 3: Xác nhận và hoàn tất
- Chọn tùy chọn “Top row” và “Left column” nếu dữ liệu có tiêu đề.
- Đánh dấu “Use labels in” để Excel nhận dạng tên các cột và hàng.
- Nhấn OK. Excel sẽ tự động cộng dồn dữ liệu theo hàm bạn đã chọn.
2.4. Mẹo nâng cao
- Sử dụng “Use labels in” để Excel tự động điều chỉnh khi có thêm hàng/ cột mới.
- Tự động cập nhật: Khi dữ liệu nguồn thay đổi, bạn cần nhấn Refresh trong tab Data → Refresh All để cập nhật lại.
hq
3. Phương pháp 2: Sử dụng công thức ẩn (Array Formula)
3.1. Khi nào dùng công thức ẩn?
- Khi bạn cần hợp nhất dữ liệu mà không muốn sử dụng tính năng “Consolidate”.
- Khi dữ liệu có thể thay đổi thường xuyên và bạn muốn công thức tự động cập nhật.
- Khi muốn linh hoạt hơn trong việc xử lý dữ liệu (phép lọc, sắp xếp).
3.2. Công thức mẫu: INDEX + MATCH + INDIRECT
Giả sử bạn có dítě sheet có tên “Jan”, “Feb”, “Mar” và bạn muốn hợp nhất cột A của từng sheet.
=IFERROR(INDEX(INDIRECT("'"&INDEX({"Jan","Feb","Mar"},INT((ROW(A1)-1)/10)+1)&"'!A"&MOD(ROW(A1)-1,10)+1),1),"")
- Hàm
INDIRECTchuyển chuỗi thành tham chiếu sheet. - Hàm
INDEXlấy giá trị từ cột A. INTvàMODtạo ra vòng lặp số hàng.
Ghi chú현: Công thức này là một ví dụ cơ bản. Bạn có thể điều chỉnh tùy vào số sheet và số hàng.
3.3. Sử dụng hàm FILTERXML để lấy dữ liệu từ nhiều sheet
Nếu Excel bạn đang dùng hỗ trợ FILTERXML, bạn có thể:
=FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,INDIRECT("'"&{"Jan","Feb","Mar"}&"'!A1:A10")) & "</s></t>", "//s")
Công thức này sẽ nối các vùng dữ liệu thành một chuỗi XML và sau đó trích xuất từng phần tử.
4. Phương pháp 3: Power Query – Giải pháp mạnh mẽ nhất
4.1. Giới thiệu Power Query
- Power Query là một công cụ mạnh mẽ trong Excel (từ Excel 2026 trở đi với add-in, từ Excel 2026 trở đi tích hợp sẵn) cho phép bạn truy vấn, làm sạch, biến đổi và hợp nhất dữ liệu từ nhiều nguồn khác nhau (sheet, file, web, database,…).
- Nó tự động ghi lại các bước bạn thực hiện, bạn có thể chỉnh sửa lại hoặc n_ALERT.
4.2. Bước 1: Tải dữ liệu từ một sheet
- Chọn một ô trong sheet, vào Data → From Table/Range.
- Nếu dữ liệu chưa là bảng, Excel sẽ nhắc bạn tạo bảng (Table). Đánh dấu My table has headers.
- Bấm OK. Power Query Editor mở lên.
4.3. Bước 2: Thêm các sheet khác
Có thể bạn quan tâm: Cách Kết Hợp Tên Và Họ Trong Excel: Hướng Dẫn Chi Tiết Và Công Thức Thực Hành
- Trong Power Query Editor, vào Home → New Source → besz Excel File. Chọn file của chính mình.
- Trong danh sách sheet, chọn các sheet cần hợp nhất. Nếu có nhiều sheet, bạn có thể chọn Add và Combine.
- Khi chọn Combine & Load, Power Query sẽ tự động tạo một truy vấn hợp nhất.
4.4. Bước 3: Chỉnh sửa truy vấn
- Bạn có thể thực hiện các bước: loại bỏ cột, đổi tên cột, lọc dữ liệu, chuyển đổi kiểu dữ liệu, sắp xếp, nhóm dữ liệu, tính tổngzado.
- Power Query cho phép bạn Append (gắn tiếp) hoặc Merge (kết hợp theo tiêu chí).
4.5. Bước 4: Tải dữ liệu
- Khi hoàn tất, vào Home → Close & Load. Dữ liệu sẽ xuất ra một sheet mới hoặc một bảng dữ liệu trong Power Pivot.
4.6. Lợi ích của Power Query
- Tự động cập nhật: Khi dữ liệu nguồn thayρον, bạn chỉ cần nhấn Refresh.
- Tính linh hoạt: Hỗ trợ nhiều loại nguồn dữ liệu, dễ dàng kết hợp.
- Không gây lỗi: Power Query xử lý dữ liệu theo quy trình, tránh lỗi nhập liệu.
5. Phương pháp 4: VBA – Khi cần tự động hoá toàn bộ quy trình
5.1. Khi nào dùng VBA?
- Khi bạn cần tự động hoá quá trình hợp nhất hàng ngày hoặc hàng tuần.
- Khi dữ liệu nguồn có thể thay đổi!? (đổi tên sheet, thêm/loại bỏ sheet).
- Khi muốn tạo một macro có thể chạy nhanh và dễ dàng chia sẻ.
5.2. Mã VBA mẫu
Sub ConsolidateSheets() Dim wsDest As Worksheet Dim wsSrc As Worksheet Dim lastRow As Long, destLastRow As Long Dim rngSrc As Range, rngDest As Range ' Tạo sheet đích hoặc xóa cũ On Error Resume Next Set wsDest = ThisWorkbook.Worksheets("Consolidated") If wsDest Is Nothing Then Set wsDest = ThisWorkbook.Worksheets.Add wsDest.Name = "Consolidated" Else wsDest.Cells.Clear End If On Error GoTo 0 destLastRow = 1 ' Duyệt qua từng sheet For Each wsSrc In ThisWorkbook.Worksheets If wsSrc.Name <> wsDest.Name Then ' Giả sử dữ liệu bắt đầu từ hàng 2 (đầu dòng tiêu đề) lastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row Set rngSrc = wsSrc.Range("A2:C" & lastRow) ' Tùy chỉnh cột ' Copy vào sheet đích rngSrc.Copy Destination:=wsDest.Cells(destLastRow + 1, 1) destLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row End If Next wsSrc MsgBox "Data consolidated successfully!"
End Sub
Giải thích:
– Macro này lặp qua tất cả các sheet trong workbook, trừ sheet đích.
– Giả sử dữ liệu nằm ở cột A–C, bắt đầu từ hàng 2 (tiêu đề ở hàng 1).
– Dữ liệu được sao chép vào sheet “Consolidated”.
5.3. Mẹo nâng cao
- Sử dụng
Application.ScreenUpdating = Falseđể tăng tốc macro khi có nhiều sheet. - Xử lý lỗi: Thêm
On Error Resume Nextđể tránh macro bị dừng khi gặp sheet trống. - Gọi macro từ nút: Tạo nút trong sheet và gán macro cho nó, giúp người dùng cuối dễ thao tác.
6. Lưu ý khi hợp nhất dữ liệu
| # | Lưu ý | Mô tả |
|---|---|---|
| 1 | Kiểm tra tiêu đề | Đảm bảo tên cột giống nhau, tránh lỗi khi tính tổng |
| 2 | Định dạng dữ liệu | Kiểm tra kiểu dữ liệu (text, number, date) trước khi hợp nhất |
| 3 | Kiểm tra dữ liệu trùng lặp | Sử dụng Remove Duplicates hoặc hàm UNIQUE |
| 4 | Định danh dữ liệu nguồn | Thêm cột “Source Sheet” để biết dữ liệu đến từ sheet nào |
| 5 | Tự động cập nhật | Sử dụng tính năng Refresh trong Power Query hoặc macro |
| 6 | Bảo mật | Đặt sheet nguồn thành “protected” nếu không muốn người dùng sửa |
7. Ví dụ thực tế: Tổng hợp doanh thu hàng tháng
Giả sử bạn có 12 sheet, mỗi sheet chứa dữ liệu doanh thu theo ngày trong một tháng. Bạn muốn tạo một bảng tổng hợp toàn bộ dữ liệu để dễ dàng tính tổng doanh thu hàng năm.
7.1. Dữ liệu mẫu
| Ngày | Số lượng | Giá bán |
|---|---|---|
| 01/01/2026 | 100 | 50,000 |
| 02/01/2026 | 120 | 48,000 |
| … | … | … |
7.2. Bước 1: Tạo sheet “Consolidated”
- Chèn một sheet mới, đặt tên “Consolidated”.
7.3. Bước 2: Sử dụng Power Query
- Data → Get Data → From Other Sources → Blank Query.
- Trong Power Query Editor, chọn Advanced Editor và nhập đoạn code:
let Source = Excel.CurrentWorkbook(), Sheets = Table.SelectRows(Source, each Text.StartsWith(Name, "Jan")), #"Added Custom" = Table.AddColumn(Sheets, "Data", each Table.PromoteHeaders(Data, PromoteAllScalars=true)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"Date", "Quantity", "Price"}, {"Date", "Quantity", "Price"}), #"Merged Tables" = Table.Combine(#"Expanded Data")
in #"Merged Tables"
Giải thích: Code này lọc các sheet bắt đầu bằng “Jan”, “Feb”, … và kết hợp dữ liệu từ cột “Date”, “Quantity”, “Price”.
- Nhấn Close & Load. Bạn sẽ có một bảng dữ liệu hợp nhất.
7.4. Bước 3: Tính tổng doanh thu
Trong sheet “Consolidated”, thêm cột mới “Revenue”:
= Quantity Price
Sau đó, tạo PivotTable để tính tổng doanh thu theo tháng.
8. Kết luận
Việc hợp nhất dữ liệu từ nhiều worksheet trong Excel là một kỹ năng thiết yếu giúp bạn quản lý dữ liệu hiệu quả hơn. Dù bạn chọn phương pháp “Consolidate” truyền thống, occu công thức, Power Query hay VBA, điều quan trọng là:
- Chuẩn bị dữ liệu: Cùng cấu trúc, tiêu đề nhất quán.
- Chọn công cụ phù hợp: Đối với/feed dữ liệu đơn giản → Consolidate; dữ liệu phức tạp, tự động hóa → Power Query hoặc VBA.
- Tự động cập nhật: Đảm bảo dữ liệu nguồn luôn được lôi ra khi thay đổi.
Bằng cách áp dụng những phương pháp và mẹo trong bài viết này, bạn sẽ tiết kiệm được thời gian, giảm thiểu sai sót và nâng cao năng suất làm việc. Hãy thử áp dụng ngay hôm nay và trải nghiệm hiệu quả của việc hợp nhất dữ liệu trong Excel!
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ẻ.