Cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng Power Query

Việc tổ chức dữ liệu trên Excel phụ thuộc rất nhiều yếu tố: đặc thù của nguồn nhập dữ liệu, đặc thù của dạng dữ liệu… nên không phải lúc nào chúng ta cũng có được 1 bảng dữ liệu đúng ý muốn ngay từ đầu. Do đó để có thể phục vụ việc lập báo cáo trong Excel được tốt hơn thì chúng ta cần phải tổ chức lại dữ liệu. Một trong những công việc thường gặp đó là gộp dữ liệu từ nhiều bảng tính (có thể trên nhiều sheet) vào 1 bảng tính (trên 1 sheet). Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng Power Query.

gộp nhiều sheet thành 1 sheet bằng query

gộp nhiều sheet thành 1 sheet bằng query

Mục tiêu của chúng ta như sau:

1. Chuẩn bị dữ liệu

Các bạn có thể tải về file dữ liệu gốc để thực hành theo bài viết tại địa chỉ: http://bit.ly/2JBYRh1

Power Query là 1 công cụ mới có trên Office từ các phiên bản Office 2016, Office 365 (đã tích hợp sẵn). Nếu bạn sử dụng Office phiên bản thấp hơn như 2010, 2013 thì bạn có thể tải Add-in Power Query trên hệ thống của Microsoft Office

Link tải Power Query: https://www.microsoft.com/vi-vn/download/details.aspx?id=39379

2. Nguyên tắc của việc gộp dữ liệu

Để tránh các lỗi có thể xảy ra trong quá trình gộp dữ liệu thì chúng ta phải tuân thủ một số nguyên tắc sau:

  • Dữ liệu từ các bảng tính sẽ được nối tiếp nhau trong 1 bảng chung
  • Các bảng tính phải có cấu trúc giống nhau: số cột, tên các cột… để đảm bảo nguyên tắc nối tiếp nhau
  • Các bảng tính phải bao gồm dòng tiêu đề và hạn chế dòng trống xen kẽ dữ liệu (đảm bảo tính liên tục của dữ liệu)

3. Cách thực hiện gộp dữ liệu bằng Power Query

a. Chuyển các bảng cần gộp về dạng Table Data

Tại mỗi bảng dữ liệu thành phần chúng ta sẽ chuyển về dạng Table data bằng cách:

  • Chọn bảng dữ liệu đó
  • Sử dụng phím tắt Ctrl+T (phím tắt của chức năng Insert/Table)
  • Xác nhận việc chuyển đổi này

Sau khi chuyển đổi chúng ta sẽ thu được 1 Table có tên là Table1

Làm tương tự với các bảng khác, chúng ta sẽ thu được 3 table có tên lần lượt là

  • Table1 – Cửa hàng A
  • Table2 – Cửa hàng B
  • Table3 – Cửa hàng C

Tại sao lại cần chuyển về dạng Table? Bởi trong Power Query việc kết nối dữ liệu từ các Table sẽ dễ hơn, nhanh hơn. Sau khi kết nối các bảng thì chúng ta có thể trả dạng Table về dạng Range một cách dễ dàng, không bị ảnh hưởng gì.

b. Sử dụng Power Query kết nối các bảng Table

Trong thẻ Data chúng ta chọn From Table/Range

Cửa sổ Power Query sẽ mở ra. Trong cửa sổ này chúng ta chọn mục Append Queries ngay trong thẻ Home

Trong cửa sổ Append chúng ta chọn Two tables và trong mục Table to append chọn Table1 (current)  – có thể là Table khác phụ thuộc vào Table bạn chọn khi bấm Data / From Table 

See also  Hà Nội: Tuyển sinh mầm non, lớp 1, lớp 6 theo phương thức xét tuyển - Phương án tuyển sinh vào mầm non, lớp 1, lớp 6 ở Hà Nội năm 2021 có gì mới? | Bostonenglish.edu.vn

Tiếp theo chúng ta bấm vào mục Advanced Editor để sửa lại câu lệnh nối dữ liệu của Power Query

Trong câu lệnh này các bạn chú ý như sau:

  • Let / Source: là các bảng dữ liệu nguồn. Ở đây chú ý Name’”Table1″ chính là tên của các Table đã được tạo ở trên. Chúng ta sẽ thay đổi Source này thành 3 Source ứng với 3 Table
  • Let/ Appended: là lệnh nối các bảng dữ liệu. Đặt các Source vào trong câu lệnh = Table.combine({…})
  • in / Appended: trích xuất kết quả của Appended
  • Các lệnh khác có thể bỏ qua (ví dụ như changed type)

Do đó chúng ta sẽ sửa lại câu lệnh trên thành:

Bấm Done và xem kết quả

Trong mục này chúng ta có thể sắp xếp lại dữ liệu theo Ngày hoặc theo Cửa hàng tùy thích.

c. Xuất dữ liệu từ Power Query về 1 Sheet kết quả

Để trích xuất dữ liệu từ Power Query về Sheet thì chúng ta làm như sau:

  • Bấm vào mục Close & Load trong thẻ Home và chọn Close & Load to

  • Trong cửa sổ Close & Load to chọn:

Select how you want to view this data in your workbook: Chọn dạng dữ liệu muốn hiển thị. Thông thường có thể chọn Table

Where do you want to put the data? Chọn nơi bạn muốn đặt bảng dữ liệu kết quả này. Nếu đã có vị trí tại 1 sheet xác định thì chọn Existing worksheet, còn nếu muốn đưa vào 1 sheet mới thì chọn New worksheet.

Bấm OK để hoàn tất thao tác và nhận kết quả.

Để chuyển từ dạng Table về dạng bảng thường (Range) thì chúng ta bấm vào mục Convert to range trong thẻ Table Tools

Như vậy là chúng ta đã hoàn thành được thao tác ghép nối dữ liệu từ nhiều bảng vào 1 bảng bằng cách sử dụng Power Query rồi. Việc ghép nối này khi mới tìm hiểu có thể khiến bạn mất nhiều thời gian, nhưng với những bảng dữ liệu lớn, liên kết hàng trăm bảng tính vào 1 bảng thì việc sử dụng Power Query trở nên rất có hiệu quả.

Ngoài ra bạn có thể tham khảo thêm: cách kết nối dữ liệu từ nhiều bảng vào 1 bảng sử dụng VBA

CÁCH GỘP NHIỀU SHEET THÀNH 1 SHEET BẰNG CÁCH THỦ CÔNG

Để có thể gộp nhiều sheet thành 1 sheet bằng cách thủ công, chúng ta cần phải thao tác khá nhiều bước, cụ thể như sau:

 

Bước 1: Đầu tiên, chúng ta cần tạo Table cho từng Sheet cần gộp bằng cách chọn Insert rồi nhấn chọn Table, sau đó nhấn OK. Tiếp theo, chúng ta tiến hành đặt tên ở góc trên màn hình sau khi nhấn OK rồi đặt lại tên xong nhấn Enter và kết quả của tạo Table như hình minh họa dưới đây:

 

cách gộp nhiều sheet thành 1 sheet

Bước 2: Tiếp theo, chúng ta vào thẻ Data, tiếp đó chọn From Table, khi đó một cửa sổ mới hiện ra, chúng ta kích chọn Close & Load rồi nhấn chọn Close & Load to.

cách gộp nhiều sheet thành 1 sheet

Sau đó, khi cửa sổ nhỏ hiện ra chúng ta  tích chọn Only Create Connection rồi nhấn chọn Load.

cách gộp nhiều sheet thành 1 sheet

Bước 3: Và chúng ta cũng làm tương tự cho các sheet cần gộp còn lại, lúc này ở mục Workbook Queries sẽ chứa tất cả các Sheet mà chúng ta muốn gộp. Tuy nhiên, hãy chú ý nếu chúng ta tổng hợp 10 sheet thì trong mục Workbook Queries cũng phải có đủ 10 Sheet thì mới chính xác.

 

Bước 4: Tiếp theo, chúng ta kích chọn thẻ Data, chọn Get Data, rồi nhấn chọn Combine Queries và chọn Append.

cách gộp nhiều sheet thành 1 sheet

Lúc này, cửa sổ Append hiện ra, chúng ta tích vào mục Three Or More Table rồi tích chọn cột Available Table, tiếp đó nhấn chọn Add để cho các sheet hiển thị ở cột Tables To Append rồi nhấn Ok.

cách gộp nhiều sheet thành 1 sheet

Như vậy, lúc này các Sheet đã được tổng hợp vào cửa sổ mới, chúng ta chọn Close & Load rồi chọn Close & Load To.

See also  Cách sắp xếp tên theo abc trong excel 2010 và các phiên bản khác | Bostonenglish.edu.vn

cách gộp nhiều sheet thành 1 sheet

Khi đó, cửa sổ Import Data hiện ra, chúng ta tích Table và tích New Worksheet trong trường hợp nếu chúng ta muốn tạo mới hoặc Existing Worksheet trong trường hợp nếu chúng ta muốn tạo vào một Sheet có sẵn, sau đó nhấn Ok.

cách gộp nhiều sheet thành 1 sheet

Cuối cùng thì chúng ta đã có thể gộp được các Sheet thành một Sheet một cách thủ công mà không cần dùng bất kỳ công thức nào như hình minh họa dưới đây:

cách gộp nhiều sheet thành 1 sheet

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

CÁCH GỘP NHIỀU SHEET THÀNH 1 SHEET BẰNG VBA

Ngoài cách gộp nhiều Sheet thành một Sheet trong Excel một cách thủ công, chúng ta có thể sử dụng code VBA. Mã VBA có thể giúp chúng ta lấy dữ liệu của tất cả sheet tính từ file tính đang hoạt động và kết hợp chúng thành trang tính lớn.

Ví dụ: Chúng ta có ba sheet tính Grade1, Grade2, Grade3 là các sheet chứa thông tin của các bạn học sinh, chúng ta hãy gộp các sheet này thành một sheet như hình minh họa bên phải dưới đây:

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

cách gộp nhiều sheet thành 1 sheet

 

 

Để có thể gộp nhiều Sheet trong ví dụ trên thành một Sheet lớn bằng VBA thì chúng ta hãy lần lượt thực hiện các bước như sau:

Bước 1: Đầu tiên, chúng ta cần nhấn giữ Alt + F11, khi đó hộp thoại Microsoft Visual Basic for Applications sẽ mở ra.

Bước 2: Sau đó, chúng ta nhấn chọn Insert, tiếp đó chọn Module và thực hiện dán đoạn mã code sau vào cửa sổ Module.

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

Sub Combine()

Dim J As Integer

On Error Resume Next

Sheets(1).Select

Worksheets.Add

Sheets(1).Name = “Combined”

Sheets(2).Activate

Range(“A1”).EntireRow.Select

Selection.Copy Destination:=Sheets(1).Range(“A1”)

For J = 2 To Sheets.Count

Sheets(J).Activate

Range(“A1”).Select

Selection.CurrentRegion.Select

Selection.Offset(1, 0).Resize(Selection.Rows.Count – 1).Select

Selection.Copy Destination:=Sheets(1).Range(“A65536”).End(xlUp)(2)

Next

End Sub

Bước 3: Cuối cùng, chúng ta nhấn F5 để chạy mã. Lúc này tất cả dữ liệu trong bảng tính đã được hợp nhất thành trang tính Combined và được đặt trước các trang tính khác.

MỘT SỐ LƯU Ý KHI GỘP NHIỀU SHEET THÀNH 1 SHEET BẰNG CODE VBA

  • Để có thể thực hiện gộp nhiều sheet thành 1 sheet bằng code VBA thì hãy đảm bảo rằng tất cả dữ liệu, trang tính của bạn phải có cùng cấu trúc, cùng tiêu đề cột và thứ tự cột.
  • Khi thực hiện gộp nhiều sheet thành 1 sheet bằng code VBA thì dữ liệu cần phải bắt đầu từ ô A1, còn nếu không thì đoạn mã này sẽ không hoạt động.
  • Ngoài ra, đoạn mã code VBA này chỉ có thể kết hợp tất cả trang tính của một bảng tính, còn trong trường hợp nếu chúng ta muốn gộp nhiều sheet thành nhiều sheet khác nhau thì đoạn mã này sẽ không hoạt động.

Tự Động Copy Nhiều Sheet Vào 1 Sheet Trong Excel

Copy nhiều Sheet vào một Sheet trong Excel.

Đầu tiên bạn phải tạo Table cho từng Sheet. Chọn Insert -> Table -> ok.

tạo table trong excel

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

Đặt tên ở góc trên màn hình sau khi nhấn ok đặt lại tên xong nhấn Enter. Và kết quả của tạo Table.

kết quả của tạo table

Vào thẻ Data -> From Table -> sau đó một cửa sổ mới hiện ra -> kích chọn Close & Load -> Close & Load to.

tổng hợp nhiều sheet trong excel

Cửa sổ nhỏ hiện ra bạn tích chọn Only Create Connection -> Load.

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

sheet trong excel

Và bạn cũng làm tương tự cho các sheet còn lại. Ở mục Workbook Queries sẽ chứa tất cả các Sheet bạn muốn tổng hợp. Bạn chú ý nếu bạn tổng hợp 10 sheet thì trong mục Workbook Queries cũng phải có đủ 10 Sheet thì mới đúng.

tổng hợp sheet excel

Tiếp tục kích chọn thẻ Data -> Get Data -> Combine Queries -> Append.

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

sheet excel tổng hợp

Cửa sổ Append hiện ra tích vào Three Or More Table -> tích chọn bênh cột Available Table -> Add -> hiển thị ở cột Tables To Append -> ok.

See also  Hãy nêu ý nghĩa của các câu tục ngữ, thành ngữ, ca dao dưới đây: | Bostonenglish.edu.vn

sheet cho excel

Như vậy các Sheet đã được tổng hợp vào cửa sổ mới bạn chọn Close & Load -> Close & Load To.

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

excel và tổng hợp sheet

Cửa sổ Import Data hiện ra -> tích Table và tích New Worksheet (nếu bạn muốn tạo mới) hoặc Existing Worksheet (Nếu muốn tạo vào một Sheet có sẵn) -> Ok.

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

tổng hợp sheet trong excel

Kết quả khi tổng hợp các Sheet vào Sheet tổng hợp.

sheet tổng hợp

Như vậy các Sheet bạn cần tổng hợp đã được tổng hợp thành một Sheet mới mà bạn không cần phải dùng VBA hay bất cứ công thức nào.

Thêm dữ liệu mới vào Sheet và được thêm vào Sheet tổng hợp.

Dữ  liệu khi được thêm vào.

thêm dữ liệu vào sheet

Khi thêm dữ liệu vào Sheet và bạn muốn được tổng hợp vào Sheet tổng hợp. Thực hiện như sau: sau khi thêm dữ liệu vào Sheet -> bạn qua Sheet tổng hợp kích vào một vị trí bất kỳ trong Sheet tổng hợp -> nhấn chuột phải chọn Refresh. Sau đó những dữ liệu bạn mới thêm vào đã có ở Sheet tổng hợp.

chọn thêm dữ liệu mới vào sheet

kết quả khi bạn tổng hợp nhiều Sheet vào một Sheet và thêm dữ liệu vào Sheet.

kết qua sau khi thêm sheet trong excel

Macro gộp dữ liệu nhiều sheets Excel vào 1 sheet

Ví dụ Đề bài

Giả sử chúng ta có 3 sheets với cấu trúc giống nhau và chỉ khác nhau về dữ liệu như sau:

Các bước để tổng hợp Sheet1, Sheet2, Sheet3 vào Sheet “Tong Hop” như sau:

Bước 1: Mở file Tong hop.xlsm

Bước 2: Bấm tổ hợp phím ALT + F11 để mở trình soạn thảo VBA

cach-mo-trinh-soan-thao-vba

Bước 3: Mở Project Explorer: Bấm vào View > Project Explorer

Mở Project Explorer

Bước 4: Trong cửa sổ Project Explorer, tạo 1 Module mới

Bước 5: Copy code sau vào trong module mới vừa tạo ra:

 

Sub MergeSheets()
Dim x As Integer
Dim ws As Worksheet
On Error GoTo ErrHandler
Application.ScreenUpdating = False
x = 1
For Each ws In ThisWorkbook.Sheets
If ws.Name <> “Tong Hop” Then
If x = 1 Then
ws.UsedRange.Copy Sheets(“Tong Hop”).Range(“A1”)
Else
lr = Sheets(“Tong Hop”).UsedRange.Rows.Count
ws.UsedRange.Offset(1).Copy Sheets(“Tong Hop”).Range(“A” & lr + 1)
End If
x = x + 1
End If
Next ws
ErrHandler:
Debug.Print Err.Description
End Sub

 

Bước 6: Bấm phím F5 và chiêm ngưỡng kết quả.

Đến đây bạn đã có thể bắt đầu tìm hiểu những điều thú vị của VBA được rồi. VBA giúp ích rất nhiều trong công việc: giúp bạn tăng tốc trong quá trình xử lý trên Excel, giúp lập các báo cáo tự động, điều khiển các chương trình khác như Word, Power Point, Outlook … giúp biến file Excel của bạn trở thành 1 phần mềm chuyên nghiệp …

Lời kết

Qua bài viết trên chia sẻ trên đây của mình chắc hẳn các bạn đã hiểu rõ về các cách gộp nhiều sheet thành 1 sheet trong Excel bằng cách thủ công hay bằng code VBA. Khi các bạn thực hiện gộp nhiều sheet thành 1 sheet bằng code VBA sẽ nhanh hơn khá nhiều so với cách thủ công thông thường. Ngoài ra, VBA có thể sẽ giúp các bạn tăng tốc trong quá trình xử lý trên excel, giúp bạn lập các báo cáo tự động,…Nếu có thắc mắc hay câu hỏi nào liên quan đến cách gộp nhiều sheet thành 1 sheet trong Excel thì hãy liên hệ qua website này nhé.

Bostonenglish.edu.vn cũng giúp giải đáp những vấn đề sau đây: 

  • Cách gộp nhiều sheet thành 1 sheet trong Excel 2010
  • Gộp nhiều sheet thành 1 sheet
  • Macro gộp sheet trong Excel
  • Gộp nhiều sheet thành 1 sheet bằng VBA
  • Gộp dữ liệu từ nhiều sheet trong Excel có cấu trúc giống nhau
  • Cách gộp nhiều sheet thành 1 sheet trong Excel 2016
  • Gộp nhiều sheet thành 1 sheet trong Google Sheet
  • Gộp dữ liệu nhiều Sheet thành 1 Sheet

 

See more articles in the category: Wiki

Leave a Reply