Kết hợp các bảng với Power Query

7/27/2020

Ý chính: Tìm hiểu cách kết hợp các bảng trong Excel bằng Power Query.

Trình độ kỹ năng: Trung bình

Kết hợp bảng

Nếu bạn có các bảng trên một số worksheet chứa cùng loại dữ liệu và bạn đang tìm cách kết hợp chúng vào một bảng chính, Power Query có thể giúp bạn thực hiện nhanh chóng và hiệu quả. Đây là một cách thay thế tuyệt vời để sao chép và dán từng mảnh dữ liệu nếu có một số bảng mà bạn muốn hợp nhất.

Chỉ có hai điều kiện cần ghi nhớ.

Điều kiện #1

Tất cả các trang tính hoặc tập dữ liệu mà bạn đang tìm cách kết hợp phải được định dạng dưới dạng Bảng Excel, không phải là dữ liệu được thiết lập theo định dạng bảng.

Để biến tập dữ liệu thành Bảng Excel, chỉ cần chọn bất kỳ ô nào trong tập hợp và sau đó chọn Format as Table trên tab Home. Thông thường nên đặt tên cho bảng sau khi bạn đã tạo/chèn nó vào bảng.

Nếu bạn chưa quen với Bảng Excel, hãy xem Hướng dẫn cho người mới bắt đầu về Excel Tables. Dưới đây là một số Mẹo & Phím tắt hữu ích để chèn Excel Tables và bài đăng này sẽ cung cấp cho bạn một số Thực hành đặt tên hiệu quả cho Excel Tables.

Điều kiện #2

Các bảng bạn đang làm việc phải chứa cùng các tiêu đề cột (column headings), mặc dù chúng không nhất thiết phải theo cùng một thứ tự. Nếu bạn đang làm việc với các cột có dữ liệu tương tự, nhưng các tiêu đề của bạn không giống nhau, Power Query sẽ đặt chúng vào các cột khác nhau khi kết hợp chúng.

Thiết lập trong Power Query

Bây giờ bạn có thể tạo queries trong Power Query. Đầu tiên chúng ta sẽ tạo queries kết nối cho mỗi bảng. Sau đó, sẽ kết hợp các queries đó với một Append query để kết hợp hoặc xếp chồng dữ liệu.

1. Tạo Connection Queries với các bảng

Để kết hợp hoặc nối các bảng của với nhau, bạn cần tạo kết nối với từng bảng trong Power Query.

Di chuyển đến Power Query editor bằng cách nhấp vào From Table/Range trên tab Data hoặc Power Query (tùy thuộc vào phiên bản Excel bạn đang sử dụng).

Để tạo kết nối:

1. Nhấp vào nửa dưới của nút Close & Load 

2. Chọn Close & Load To…

3. Cửa sổ Import Data xuất hiện. Từ đây, chọn Only Create Connection.

4. Click OK.

Bạn có thể thấy kết nối vừa tạo trong pane Queries & Connections. Nếu bạn không thấy pane Queries & Connections, bạn có thể mở nó bằng cách chọn nút đó trên tab Data trong ribbon.

Quá trình tạo kết nối này phải được lặp lại cho mỗi bảng mà bạn muốn thêm. Bạn chỉ cần thực hiện công việc này một lần cho thiết lập ban đầu. Tuy nhiên, đây là một vài mẹo để tăng tốc quá trình.

Sử dụng Macro kết nối bảng

Vì việc tạo và kết nối nhiều bảng có thể tốn thời gian, chúng ta tạo một macro tự động hóa để thực hiện nó. Các vòng lặp macro thông qua tất cả các bảng trong sổ làm việc và tạo các queries chỉ kết nối cho bất kỳ bảng nào chưa có queries.

Các macro chạy trên Active Workbook. Bạn có thể thêm macro vào Workbook Macro cá nhân và thêm nút macro vào Thanh công cụ Ribbon hoặc Quick Access Toolbar để chạy trên bất kỳ sổ làm việc đang mở nào.

Cài đặt Close & Load

Nếu bạn không muốn sử dụng macro, bạn cũng có thể rút ngắn quy trình bằng cách thay đổi cài đặt của nút Close & Load. Mặc định cho nửa trên của nút đó sẽ tải bảng đầu ra sang một trang mới, nhưng bạn có thể điều chỉnh các cài đặt để nó chỉ tạo kết nối thay thế. Để thay đổi cài đặt:

1. 1.  

1. Di chuyển đến menu File.

2. Chọn Options and Settings.

3. Chọn Query Options.

4. Xuất hiện cửa sổ Query Options, nơi bạn có thể chọn Specify custom default load settings.

5. Bỏ chọn tùy chọn Load to worksheet.

6. Nhấn OK.

Chỉ cần nhớ thay đổi cài đặt này trở lại sau khi bạn kết nối tất cả các bảng của mình.

2. Kết hợp các bảng được kết nối với Append

Khi tất cả các bảng của bạn được kết nối, chúng ta bắt đầu hợp nhất chúng:

1. 1. 1.  

1. Di chuyển đến tab Data.

2. Click chọn nút Get Data.

3. Chọn Combine Queries.

4. Chọn Append.

Xuất hiện cửa sổ Append, chúng ta có thể chọn 3 hoặc nhiều bảng. Điều này cho phép chúng ta di chuyển bất kỳ hoặc tất cả các bảng mà chúng ta đã kết nối từ các Available tables (ở bên trái) vào danh sách Tables to append (ở bên phải).

Bạn có thể chọn tất cả các bảng Available bằng cách chọn bảng đầu tiên, giữ Shift, sau đó chọn bảng cuối cùng trong danh sách. Sau đó nhấp vào nút Add >> để di chuyển chúng sang bên phải.

Khi bạn nhấn OK, bạn sẽ trở lại trình soạn thảo Power Query, nơi bạn có thể xem bản xem trước của các bảng kết hợp. Bạn có thể thực hiện điều chỉnh và chuyển đổi dữ liệu trước khi đóng trình chỉnh sửa và tải dữ liệu sang bảng tính mới.

Cập nhật & Làm mới dữ liệu

Một điều tuyệt vời về Power Query là bạn có thể làm mới bảng đầu ra bất cứ khi nào có thay đổi được thực hiện đối với bất kỳ tập dữ liệu nào. Chỉ cần nhấp chuột phải vào bất cứ nơi nào trên bảng đầu ra đó và chọn Refresh.

Điều này có nghĩa là chúng ta đã hoàn toàn tự động quá trình này. Bạn KHÔNG cần lặp lại các bước trên mỗi khi dữ liệu của bạn thay đổi hoặc bạn nhận được các hàng mới trong các bảng của mình.

Thêm bảng mới

Nếu bạn muốn thêm các bảng mới vào query (hoặc loại trừ các bảng hiện có), bạn có thể mở lại cửa sổ Append bằng cách:

1. 1. 1. 1.  

1. Nhấp vào query đầu ra trong pane Queries & Connections.

2. Mở pane Query Settings nếu nó chưa hiển thị (tab View, sau đó là Query Settings).

3. Nhấp vào biểu tượng bánh răng nhỏ bên cạnh Source.

1.  

4. Điều này sẽ mở cửa sổ Append, nơi bạn có thể thêm hoặc xóa các bảng.

Thêm cột mới

Một lợi thế của việc sử dụng Tables cho kỹ thuật này là bạn KHÔNG phải thực hiện bất kỳ thay đổi nào đối với query khi các cột mới được thêm vào Tables. Power Query sẽ tự động bao gồm các cột mới trong query và xuất chúng trong query được nối thêm.

Các cột mới sẽ vẫn cần có cùng tên tiêu đề cột trên mỗi trang. Nếu bất kỳ bảng nào bị thiếu cột, thì Power Query sẽ điền vào các hàng cho bảng đó bằng các giá trị trống (null) trong bảng đầu ra và append query.

Nguồn: Excel Campus