Nếu bạn sử dụng Power Query để tra cứu dữ liệu thì sao? Không có hàm VLOOKUP, vậy làm cách nào chúng ta có thể tra cứu dữ liệu bằng power query?
Về hiệu quả, có ba loại tra cứu: tra cứu chính xác, tra cứu gần đúng và tra cứu mờ.
- Khớp chính xác là phổ biến nhất và yêu cầu giá trị tra cứu phải giống nhau.
- Khớp gần đúng tìm giá trị trên (hoặc dưới) giá trị tra cứu.
- Khớp mờ tìm giá trị dựa trên mức độ tương tự của chúng với các giá trị khác bằng cách sử dụng một số dạng thuật toán.
Power Query có thể thực hiện tất cả các loại này, mặc dù trong bài đăng này, chúng ta sẽ tập trung chủ yếu vào hai loại đầu tiên. Để thực hiện tra cứu dữ liệu trong Power Query, chúng ta sẽ sử dụng phép chuyển đổi hợp nhất nằm trên thanh công cụ Home.
Downloads
Để làm việc cùng với các ví dụ bên dưới, hãy tải xuống các tệp mẫu. Nhấp vào Click here để đăng ký và có quyền truy cập vào phần Downloads.
Các ví dụ trong bài đăng này sử dụng tệp có tên Example 14 – Lookup Data.xlsx
Trong tệp có ba bảng:
- Sales – Chứa dữ liệu bán hàng hàng tháng
- Customers – Chứa thông tin liên hệ khách hàng
- Rebates – Giảm giá cho khách hàng khi mua hàng trên các giá trị cụ thể
Để minh họa cách chúng ta có thể tra cứu dữ liệu trong Power Query, chúng ta sẽ tạo hai báo cáo.
Tổng doanh số theo Town (tra cứu với kết quả khớp chính xác)
Giá trị Sales và Town của mỗi khách hàng nằm 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.
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)
Để nhận được 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. Chúng ta không nhất thiết muốn khớp với giá trị chính xác, nhưng để tìm dải mà giá trị bán hàng giảm xuống. Chúng ta vẫn sẽ sử dụng hàm Merge, nhưng chúng ta sẽ cần thêm một vài phép chuyển đổi để có được kết quả cuối cùng chính xác.
Tải dữ liệu vào Power Query
Bắt đầu bằng cách tải ba bảng vào Power Query
Bấm vào ô bất kỳ trong bảng Sales, sau đó chọn Data -> From Table / Range từ menu Excel.
Power Query Editor sẽ tải. Tôi đã cố gắng set-up các bảng để chúng yêu cầu các phép chuyển đổi tối thiểu, nhưng trong thực tế thì điều đó hiếm khi đơn giản như vậy.
Ví dụ của chúng ta, chúng ta không cần tải dữ liệu vào trang tính, thay vào đó chúng ta có thể tạo kết nối. Từ ribbon Power Query, nhấp vào Home -> Close & Load To…
Chọn Only Create Connection từ cửa sổ Import Data, sau đó click OK.
Lặp lại hành động này với bảng Customer và Rebates.
Trong Excel, mở ngăn Queries & Connections (Bấm Data -> Queries & Connections nếu nó không hiển thị), ba truy vấn sẽ được liệt kê.
Bây giờ chúng ta đã sẵn sàng để bắt đầu sử dụng tính năng Merge 🙂
Tra cứu với khớp chính xác
Để minh họa một kết quả khớp chính xác, chúng ta sẽ tạo một báo cáo tổng doanh số bán hàng theo Town.
Hãy quay lại trình soạn thảo Power Query bằng cách nhấp đúp vào truy vấn Sales trong ngăn Queries and Connections.
Trong trình soạn thảo Power Query, chọn Home -> Merge Queries (drop-down).
Ở đây có hai tùy chọn, Merge Queries và Hợp nhất Merge Queries as New. Sự khác biệt giữa chúng là liệu Merge sẽ tạo ra một truy vấn mới hay được thêm vào dưới dạng một bước chuyển đổi trong một truy vấn hiện có. Để dễ dàng, chúng ta sẽ sử dụng một truy vấn mới. Chọn tùy chọn Merge Queries as New.
Cửa sổ Merge sẽ mở:
Rất nhiều điều diễn ra ở đây:
- Chọn truy vấn đầu tiên được sử dụng – ví dụ của chúng ta, đó là truy vấn Sales.
- Chọn truy vấn thứ hai sẽ được sử dụng – trong ví dụ của chúng ta, đó là truy vấn Customers.
- Chọn cột từ hai bảng sẽ được khớp với nhau.
- Join Kind cung cấp sáu loại hợp nhất khác nhau – Chọn Left Outer nếu làm việc cùng với ví dụ.
- Nhấp OK.
Có 6 kiểu nối, hãy xem phần bên dưới để tìm hiểu những gì các kiểu làm khác nhau.
Một truy vấn mới được tạo. Truy vấn đầu tiên được chọn trong cửa sổ Merge được hiển thị, với một cột bổ sung chứa bảng của truy vấn thứ hai.
Nhấp vào biểu tượng bảng mở rộng trong tiêu đề của cột Customers. Chúng ta chỉ cần các cột Customers và Town, vì vậy hãy chọn những cột đó, sau đó nhấp vào OK.
Power Query sẽ hợp nhất các truy vấn, bằng cách tra cứu từ bảng đầu tiên vào bảng thứ hai.
Kết hợp đã chọn bao gồm tất cả các mục từ bảng Sales và các mục phù hợp từ bảng Customers. Bất kỳ mục nào không có kết quả phù hợp sẽ hiển thị null, như thể hiện hình bên dưới. Khách hàng Mega Mart tồn tại trong truy vấn Sales, nhưng không tồn tại trong truy vấn Customers, do đó giá trị rỗng được hiển thị.
Để hoàn thành ví dụ của chúng ta, chúng ta sẽ sử dụng cột được hợp nhất để tạo báo cáo tóm tắt. Chọn cột Town, sau đó nhấp vào Transform -> Group By từ menu.
Trong hình ở trên, tôi đã chọn Tính tổng cột Value, thành một cột được gọi là Total Sales. Sau khi tôi nhấp vào OK, nó sẽ tạo một báo cáo tóm tắt về doanh số bán hàng theo Town (xem hình bên dưới).
Các loại liên kết
Trước khi xem xét một ví dụ khác, hãy dành vài phút để suy nghĩ về sáu loại phép nối khác nhau.
Các phép nối cho phép chúng ta so sánh các danh sách, sau đó trả về các giá trị tương ứng. Rất may, các mô tả được cung cấp cho mỗi liên kết là một bản tóm tắt về những gì nó hoạt động.
Outer Joins
Outer joins trả về tất cả các hàng từ một hoặc cả hai danh sách. Chúng ta có thể chọn Trái, Phải hoặc Toàn bộ, tùy thuộc vào danh sách nào sẽ trả về tất cả các hàng của nó.
- Left Outer – tất cả các mục từ danh sách đầu tiên được trả lại, cùng với các mục phù hợp từ danh sách thứ hai.
- Right Outer – tất cả các mục từ danh sách thứ hai được trả lại, cùng với các mục phù hợp từ danh sách đầu tiên.
- Full Outer – tất cả các mục từ cả hai danh sách.
Inner Join
Inner Join chỉ trả về các giá trị tồn tại trên cả hai danh sách. Nếu danh sách đầu tiên hoặc danh sách thứ hai có các mục không có trong danh sách khác, thì những mục này sẽ bị loại khỏi kết quả cuối cùng.
Anti Joins
Anti Joins trả về các mục không khớp với bất kỳ giá trị nào trong danh sách khác.
- Left Anti – trả về bất kỳ mục nào trong danh sách đầu tiên không khớp với danh sách thứ hai.
- Right Anti – bất kỳ mục nào trong danh sách thứ hai không khớp với danh sách đầu tiên.
Đây là tất cả những bằng chứng rằng Power Query có thể hiệu quả hơn VLOOKUP.
Tra cứu với kết quả khớp gần đúng
Bây giờ là lúc để tìm hiểu khớp gần đúng. Trước khi chúng ta bắt đầu, nếu bạn không chắc điều này có nghĩa là gì, vui lòng đọc bài đăng của tôi về Khớp gần đúng.
Trong ví dụ này, chúng ta đang tính toán giá trị của khoản giảm giá do một khách hàng dựa trên giá trị bán hàng. Khách hàng có doanh số bán hàng lớn hơn:
- $500 nhận được chiết khấu 2%
- $750 nhận được chiết khấu 5%
- $1,000 nhận được chiết khấu 10%
Các ngưỡng này được hiển thị trong bảng trên trang tính.
Trước tiên, chúng ta cần tính toán tổng doanh số bán hàng của khách hàng trước, sau đó thực hiện phép hợp nhất phức tạp hơn để tính giá trị giảm giá dựa trên tổng doanh số bán hàng.
Thêm một phiên bản khác của bảng Sales vào Power Query bằng cách làm theo các bước tương tự ở trên.
Trong trình soạn thảo Power Query, hãy nhấp vào Transform -> Group By.
Cửa sổ Group By sẽ mở ra, thực hiện các lựa chọn sau:
- Group by: Customer
- New column name: Total Sales
- Operation: Sum
- Column: Value
Bấm OK. Cửa sổ Xem trước sẽ hiển thị bảng sau, với tổng doanh số của khách hàng.
Trong ví dụ cuối cùng, chúng ta đã tạo Merge dưới dạng truy vấn mới; lần này, chúng ta có thể thêm Merge như một bước khác. Nhấp vào Home -> Merge Queries.
Trong cửa sổ Merge, chúng ta muốn sử dụng một phép nối Full Outer giữa cột Total Sales của bảng Sales và Rebate Band của bảng Rebates.
Bấm OK để đóng và quay lại Cửa sổ xem trước.
Mở rộng cột Rebates, bao gồm cả hai cột, sau đó bấm OK.
Cửa sổ Xem trước bây giờ trông giống như sau:
Bây giờ chúng ta sẽ viết một câu lệnh if. Chúng ta có thể sử dụng tính năng Conditional Column, nhưng tôi thấy viết nó dưới dạng công thức dễ dàng hơn. Nhấp vào Add Column -> Custom Column.
Cửa sổ Custom Column mở ra. Đặt tên mới cho cột và nhập văn bản sau vào hộp công thức:
Chúng ta sẽ đề cập chi tiết hơn các câu lệnh if trong một bài đăng trong tương lai. Bây giờ, hãy nghĩ về nó như là hàm IF tiêu chuẩn của Excel với những thay đổi sau:
- Loại bỏ dấu ngoặc
- Thay dấu phẩy đầu tiên bằng từ then
- Thay đấu phẩy cuối cùng bằng từ else
Sắp xếp cột mới bằng cách chọn tiêu đề cột, sau đó nhấp vào Home -> A-Z.
Tiếp theo, chọn cột Rebate Value và nhấp vào Transform -> Fill (dropdown) -> Down.
Cửa sổ Xem trước sẽ giống như sau:
Bây giờ chỉ còn lại một vài bước để dọn dẹp bảng:
- Lọc ra các giá trị rỗng từ cột Customers
- Loại bỏ tất cả các cột ngoại trừ Customer, Total Sales, và Rebate Value.
Bây giờ chúng ta hãy kết thúc bằng cách tính toán giá trị giảm giá. Chọn cột Total Sales và Rebate Value, nhấp vào Add Column -> Standard -> Multiply.
Truy vấn hiển thị kết quả cuối cùng
Multiple matches
Điều gì xảy ra nếu có nhiều mục có thể khớp? Trong trường hợp này, khớp chính xác hàm VLOOKUP trong Excel trả về mục đầu tiên mà nó tìm thấy trong danh sách. Merge hoạt động khác.
Merge sẽ trả về từng phiên bản của một mục phù hợp. Ví dụ: giả sử chúng ta có hai bảng một có thông tin Product và một bảng khác có dữ liệu Stock về các sản phẩm đó.
Nếu chúng ta chỉ Merge trên cột Size, các mục có kích thước M sẽ được sao chép vì có hai chữ M trong bảng đang được tra cứu.
Nếu vì bất cứ lý do gì, bạn chỉ muốn khớp một mục, hãy xóa các mục trùng lặp khỏi một trong các bảng trước khi thực hiện Merge.
Tra cứu nhiều tiêu chí
Tin vui là Power Query không giới hạn bạn trong một danh sách. Giả sử bạn muốn khớp ba cột? Không sao đâu.
Thứ tự bạn chọn các cột xác định cột nào được khớp. Nhìn vào hình bên dưới. Các cột được chọn trong bảng đầu tiên là Color, Size, sau đó là Location (theo thứ tự đó). Các số trong tiêu đề cột xác định thứ tự các mục được chọn. Các cột được chọn trong bảng thứ hai có cùng thứ tự, Color, Size và Location.
Điều đó dễ dàng làm sao!
Tra cứu fuzzy match
Khi nhìn vào cửa sổ Merge trong các ví dụ của chúng ta, bạn có nhận thấy tùy chọn “Sử dụng khớp mờ để thực hiện hợp nhất” không? Đây là một tùy chọn mới hợp lý, nó khớp với các giá trị tương tự. Ví dụ: nó sẽ khớp “Power Query” với “power-query”. Bạn không thể làm điều đó với VLOOKUP! Chúng tôi thậm chí có thể thay đổi các ngưỡng của các giá trị tương tự trước khi chúng khớp với nhau.
Vì đây là một tính năng mới nên tôi sẽ không đi vào chi tiết ở đây, nhưng bạn có thể tìm hiểu thêm thông tin trong các bài viết sau:
- Excel University – Fuzzy match with Power Query
- Microsoft – Fuzzy match support for Get & Transform (Power Query)
