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.

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 

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

Học Excel Online xin giới thiệu với các bạn “Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm“. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để đáp ứng yêu cầu công việc tổ chức, quản lý dữ liệu và lập báo cáo trên Excel. Chi tiết khóa học xem tại: