Power Query cung cấp khả năng tra cứu dữ liệu linh hoạt và mạnh mẽ hơn hẳn so với hàm VLOOKUP truyền thống. Có ba loại tra cứu chính: khớp chính xác, khớp gần đúng và khớp mờ. Power Query hỗ trợ cả ba loại này, bài viết này sẽ tập trung vào hai loại đầu tiên: khớp chính xác và khớp gần đúng. Chúng ta sẽ sử dụng phép chuyển đổi “Hợp nhất” (Merge) trên thanh công cụ Home để thực hiện tra cứu.
Tổng Doanh số theo Khu vực (Tra cứu với Kết quả Khớp Chính Xác)
Dữ liệu doanh số và khu vực của mỗi khách hàng được lưu trữ trong các bảng riêng biệt. Do đó, chúng ta cần sử dụng chức năng Merge để thực hiện tra cứu.
Minh họa giao diện Power Query
Tổng Số tiền Hoàn lại của Khách hàng (Tra cứu với Kết quả Khớp Gần Đúng)
Để tính toán khoản giảm giá cho mỗi khách hàng, chúng ta cần thực hiện khớp gần đúng. Mục tiêu không phải là khớp với giá trị chính xác, mà là tìm dải giá trị bán hàng tương ứng. Chúng ta vẫn sẽ sử dụng hàm Merge, nhưng cần thêm một vài phép chuyển đổi để có kết quả chính xác.
Minh họa bảng dữ liệu trong Excel
Để bắt đầu, hãy tải ba bảng dữ liệu (Sales, Customers, Rebates) vào Power Query bằng cách chọn Data -> From Table/Range. Trong Power Query Editor, bạn có thể tạo kết nối đến các bảng dữ liệu thay vì tải trực tiếp vào trang tính. Từ ribbon Power Query, chọn Home -> Close & Load To… và chọn Only Create Connection. Lặp lại thao tác này cho cả ba bảng.
Tra cứu với Khớp Chính Xác
Để minh họa tra cứu khớp chính xác, chúng ta sẽ tạo báo cáo tổng doanh số theo khu vực. Mở Power Query Editor bằng cách nhấp đúp vào truy vấn “Sales”. Chọn Home -> Merge Queries (drop-down) -> Merge Queries as New.
Trong cửa sổ Merge, chọn truy vấn “Sales” làm truy vấn đầu tiên và “Customers” làm truy vấn thứ hai. Chọn cột khớp tương ứng ở cả hai bảng (ví dụ: “Customer ID”). Chọn kiểu nối “Left Outer” và nhấn OK.
Một truy vấn mới sẽ được tạo. Mở rộng cột “Customers” và chọn các cột cần thiết (ví dụ: “Customer Name”, “Town”). Power Query sẽ hợp nhất các truy vấn, thực hiện tra cứu từ bảng “Sales” sang bảng “Customers”.
Để tạo báo cáo tóm tắt, chọn cột “Town” và nhấp vào Transform -> Group By. Chọn phép tính tổng cho cột “Value” và đặt tên cột mới là “Total Sales”.
Các Loại Liên Kết
Power Query cung cấp sáu loại liên kết: Left Outer, Right Outer, Full Outer, Inner, Left Anti, và Right Anti. Mỗi loại liên kết phục vụ một mục đích khác nhau, cho phép bạn linh hoạt trong việc kết hợp dữ liệu.
Tra cứu với Kết quả Khớp Gần Đúng
Trong ví dụ này, chúng ta sẽ tính toán giá trị giảm giá dựa trên tổng doanh số của khách hàng. Tạo một phiên bản khác của bảng “Sales” trong Power Query. Sử dụng Transform -> Group By để tính tổng doanh số theo khách hàng.
Thực hiện Merge giữa cột “Total Sales” của bảng “Sales” và cột “Rebate Band” của bảng “Rebates” sử dụng kiểu nối “Full Outer”. Mở rộng cột “Rebates” và chọn các cột cần thiết.
Sử dụng Add Column -> Custom Column để viết một câu lệnh if để tính toán giá trị giảm giá dựa trên dải doanh số. Sử dụng các hàm và toán tử trong Power Query để xây dựng công thức.
Sau khi hoàn thành, lọc bỏ các giá trị rỗng và loại bỏ các cột không cần thiết. Cuối cùng, tính toán giá trị giảm giá bằng cách nhân cột “Total Sales” với cột “Rebate Value” sử dụng Add Column -> Standard -> Multiply.
Tra cứu Nhiều Tiêu Chí
Power Query cho phép tra cứu dựa trên nhiều cột. Thứ tự chọn cột sẽ quyết định thứ tự khớp.
Tra cứu Fuzzy Match
Power Query cũng hỗ trợ tra cứu fuzzy match, cho phép khớp các giá trị tương tự nhau. Tính năng này đặc biệt hữu ích khi xử lý dữ liệu không nhất quán.
Discussion about this post