Vấn đề thường gặp khi sao chép các yếu tố từ worksheet này sang worksheet khác là bạn có thể không giữ được định dạng thực tế của các yếu tố đó. Việc copy-paste số lượng lớn dữ liệu trong Microsoft Excel cũng tốn nhiều thời gian. Trong bài viết này, tôi sẽ hướng dẫn cách Power Query kết hợp nhiều file Excel và cách gộp dữ liệu từ nhiều worksheet vào một worksheet duy nhất mà không cần sao chép dán.
Bạn có thể tải miễn phí file Excel tại đây để tự thực hành.
3 Cách Dễ Dàng Hợp Nhất Worksheet Excel Không Cần Copy-Paste
Bài viết giới thiệu 3 phương pháp dễ dàng. Phương pháp 1: Tổng hợp dữ liệu từ nhiều sheet vào một sheet. Phương pháp 2: Sử dụng công cụ Power Query của Excel. Phương pháp 3: Áp dụng mã VBA để gộp nhiều worksheet.
Để minh họa, tôi sử dụng 2 tập dữ liệu mẫu trên 2 sheet khác nhau.
1. Tổng Hợp Dữ Liệu Từ Nhiều Worksheet (Tính Năng Consolidate)
Giả sử bạn có 2 worksheet cùng tên sản phẩm, số lượng bán trong 3 tháng. Bạn muốn tính tổng doanh số mỗi sản phẩm mỗi tháng. Lúc này tính năng Consolidate của Excel sẽ giúp ích. Các bước thực hiện:
Bước 1:
- Mở một worksheet trống trong cùng workbook, chọn ô bắt đầu hiển thị kết quả.
- Vào tab Data trên Ribbon, chọn Consolidate trong nhóm Data Tools.
Bước 2:
- Hộp thoại Consolidate hiện ra.
- Tại Function chọn Sum.
- Ở ô Reference, nhập phạm vi dữ liệu của tập dữ liệu đầu tiên.
- Đánh dấu cả 2 tùy chọn dưới Use labels in.
- Nhấn Add.
Bước 3:
- Tiếp tục tại ô Reference, nhập phạm vi dữ liệu của tập dữ liệu thứ 2.
- Nhấn Add rồi OK.
Bước 4:
- Kết quả: Dữ liệu được chèn vào worksheet mới, chứa tên sản phẩm kèm doanh số tháng đã được cộng tổng.
Bước 5:
- Tiêu đề Product Name bị thiếu do ô tham chiếu (B4) không hiển thị text khi Consolidate.
- Hãy định dạng lại worksheet kết quả theo ý muốn.
Đọc thêm: Sử Dụng VLOOKUP Để Gộp 2 Sheet Trong Excel (Các Bước Dễ Dàng)
2. Sử Dụng Công Cụ Power Query Để Hợp Nhất Worksheet
Excel giới thiệu Power Query – tính năng mạnh mẽ cho phép kết hợp dữ liệu từ nhiều worksheet vào một, cũng như gộp 2 sheet Excel dựa trên cột chung. Các bước thực hiện:
Bước 1: Chuyển cả 2 tập dữ liệu thành Table. Chọn phạm vi B4:E8 tập dữ liệu 1 → tab Insert → Table.
Bước 2: Hộp thoại Create Table hiện ra → xác nhận phạm vi → OK.
Bước 3: Làm tương tự cho tập dữ liệu thứ 2.
Bước 4: Mở worksheet mới → tab Data → nhóm Get & Transform Data → Get Data.
Bước 5: Chọn From File → From Excel Workbook.
Bước 6: Duyệt đến file workbook chứa các sheet cần gộp → chọn file → Import.
Bước 7: Hộp thoại Navigator hiện ra → đánh dấu Select multiple items → chọn tất cả table cần gộp → Load.
Bước 8: Cửa sổ Queries & Connections xuất hiện.
Bước 9: Lại tab Data → Get Data → Combine Queries → Merge.
Bước 10: Cửa sổ Merge → chọn 2 table cần gộp → chọn cột chung → OK.
Bước 11: Dữ liệu đã gộp hiển thị. Table 2 chưa mở rộng → click biểu tượng góc trên bên phải tiêu đề để mở rộng.
Bước 12: Hộp thoại mới → bỏ chọn tiêu đề cột trùng lặp → bỏ chọn Use original column name as prefix → OK.
Bước 13: Sheet đã gộp hiển thị trong Power Query → chọn Close & Load để đưa ra worksheet.
Bước 14: Kết quả: Dữ liệu từ các worksheet đã gộp dựa trên cột chung.
Bước 15-18 (Append – Gộp Theo Hàng): Nếu muốn gộp theo hàng (append), sau khi chuyển thành Table, mở worksheet mới → Get Data (tab Data, không phải Insert) → Combine Queries → Append → chọn các table theo thứ tự → OK → Close & Load.
Đọc thêm: Cách Gộp Các Sheet Excel Vào Một Workbook (4 Cách Phù Hợp)
3. Áp Dụng Mã VBA Để Hợp Nhất Worksheet
Phương pháp này dùng mã VBA tự động gộp mà không cần tính năng hay công cụ. Các bước:
Bước 1: Mở worksheet mới → tab Developer → nhóm Code → Visual Basic.
Bước 2: Cửa sổ VBA → tab Insert → Module.
Bước 3: Copy mã sau vào module và lưu:
'Tên thủ tục Sub
Sub Merge_Excel_Worksheets()
'Khai báo biến
Dim x As Integer
'Bật xử lý lỗi
On Error Resume Next
'Chọn worksheet mới để hiển thị kết quả
Sheets(1).Select
Worksheets.Add
'Đặt tên worksheet mới
Sheets(1).Name = "Merged"
'Chọn sheet đầu tiên để gộp
Sheets(2).Activate
'Xác định phạm vi ô
Range("B4:E8").EntireRow.Select
'Chọn đích paste
Selection.Copy Destination:=Sheets(1).Range("B4:E8")
'Vòng lặp For
For x = 2 To Sheets.Count
'Kích hoạt sheet thứ x
Sheets(x).Activate
'Chọn phạm vi dữ liệu
Range("B4:E8").Select
'Chọn vùng hiện tại
Selection.CurrentRegion.Select
'Bỏ qua hàng tiêu đề
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
'Gộp dữ liệu vào sheet Merged
Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(5)
Next
End Sub
Giải Thích Mã VBA:
Sub Merge_Excel_Worksheets()– Khai báo thủ tục.Dim x As Integer– Khai báo biến đếm.On Error Resume Next– Bỏ qua lỗi.Sheets(1).Select / Worksheets.Add– Tạo sheet mới.Sheets(1).Name = "Merged"– Đặt tên sheet kết quả.Sheets(2).Activate– Kích hoạt sheet dữ liệu đầu tiên.Range("B4:E8").EntireRow.Select– Chọn toàn bộ hàng dữ liệu.Selection.Copy Destination:=Sheets(1).Range("B4:E8")– Copy tiêu đề và dữ liệu đầu tiên.For x = 2 To Sheets.Count– Lặp qua tất cả sheet.Sheets(x).Activate / Range("B4:E8").Select– Kích hoạt và chọn dữ liệu từng sheet.Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select– Bỏ hàng tiêu đề.Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(5)– Paste nối tiếp dữ liệu.
Bước 4: Nhấn F5 hoặc nút Run (giữ con trỏ trong module).
Bước 5: Worksheet mới "Merged" xuất hiện với dữ liệu gộp thô.
Bước 6: Thêm tiêu đề và định dạng lại theo ý muốn.
Sử Dụng Power Query Gộp Nhiều File Excel Từ Thư Mục
Phần này hướng dẫn gộp nhiều file Excel riêng biệt trong một thư mục bằng Power Query.
Bước 1: Mở workbook chứa dữ liệu tháng 1 → chọn phạm vi B4:C18 → tab Insert → nhóm Tables → Table.
Bước 2: Hộp thoại Create Table → OK.
Bước 3: Dữ liệu chuyển thành bảng → lưu workbook vào thư mục riêng.
Bước 4: Đặt tất cả workbook cần gộp vào cùng thư mục, đều đã chuyển thành Table.
Bước 5: Mở worksheet mới → tab Data → Get Data → From File → From Folder.
Bước 6: Cửa sổ thư mục hiện ra → chọn Combine and Transform Data.
Bước 7: Cửa sổ Combine Files → tại Parameter chọn Table (thay vì tên sheet) → OK.
Bước 8: Tất cả workbook đã gộp → Close & Load.
Bước 9: Kết quả worksheet mới với dữ liệu gộp từ nhiều file.
Sử Dụng CMD Để Gộp File Excel Thành Một
Phương pháp này dùng CMD (Command Prompt), yêu cầu chuyển file từ .xlsx sang .csv.
Bước 1-4: Mở từng workbook → tab File → Save As → tạo thư mục ở ổ C: → lưu dạng CSV.
Bước 5: Copy đường dẫn thư mục (mở rộng Ribbon → click chuột phải thư mục → Copy path).
Bước 6: Win + R → gõ cmd → OK.
Bước 7: Trong CMD: gõ cd + space + dán đường dẫn → Enter.
Bước 8: Gõ: copy *.csv Merged.csv (có space sau "copy") → Enter.
Bước 9-14: File Merged.csv xuất hiện trong thư mục → mở file (dữ liệu thô) → lưu lại dạng .xlsx → mở Excel và định dạng lại.
Kết Luận
Trên là các phương pháp gộp worksheet Excel không cần copy-paste. Hy vọng bài viết hữu ích. Hãy để lại bình luận nếu có thắc mắc, đội ngũ ExcelDemy sẽ hỗ trợ sớm nhất.
Bài Viết Liên Quan
- Cách Gộp Tất Cả Sheet Thành Một Trong Excel (6 Cách Nhanh)