POWER QUERY – TÌM KIẾM DỮ LIỆU BẰNG PHƯƠNG PHÁP HỢP NHẤT

Khi bạn thực hiện tìm kiếm thông tin qua Power Query, bạn không có tiện ích VLOOKUP như thường lệ, vậy nên làm thế nào để tìm kiếm thông tin qua power query? Siêu Marketing sẽ làm sáng tỏ câu hỏi này thông qua bài viết chi tiết dưới đây.

Có ba phương thức tìm kiếm khác nhau về hiệu quả: tìm kiếm chính xác, tìm kiếm xấp xỉ và tìm kiếm mơ hồ.

  • Tìm kiếm chính xác là kiểu thông dụng nhất và đòi hỏi kết quả phải hoàn toàn giống nhau.
  • Tìm kiếm xấp xỉ tìm ra giá trị nằm ở phía trên (hoặc phía dưới) giá trị cần tìm.
  • Tìm kiếm mơ hồ dựa vào độ giống nhau giữa các giá trị thông qua một số thuật toán cụ thể để tìm kiếm.

Power Query có khả năng thực hiện mọi loại tìm kiếm này, mặc dù chúng ta sẽ chú ý nhiều hơn đến hai kiểu đầu tiên trong bài viết này. Chúng ta sẽ tiến hành tìm kiếm dữ liệu trong Power Query thông qua chức năng hợp nhất được tìm thấy trong thanh công cụ Home.

Tải Xuống

Để thực hành theo các ví dụ dưới đây, hãy download tệp mẫu. Click vào Click here để đăng ký và truy cập phần Tải Xuống.

Ví dụ trong bài viết này dùng tệp có tên Example 14 – Lookup Data.xlsx

Trong tệp này có ba sheet:

  • Sales – chứa dữ liệu bán hàng theo tháng
  • Customers – chứa thông tin liên lạc của khách hàng
  • Rebates – chứa các khoản giảm giá cho khách hàng dựa theo giá trị mua cụ thể

Để minh họa cách thức tìm kiếm thông tin trong Power Query, chúng ta sẽ soạn hai báo cáo.

Tổng doanh số theo Thị Trấn (tìm kiếm có kết quả khớp chính xác)

Giá trị Sales và Town của mỗi khách hàng được giữ trong hai bảng khác nhau; vì vậy, ta cần dùng đến tính năng Hợp nhất để thực hiện tìm kiếm.

Tổng số tiền hoàn lại của khách hàng (tìm kiếm có kết quả khớp xấp xỉ)

Để tính toán mức giảm giá cho mỗi Khách hàng, chúng ta cần thực hiện tìm kiếm xấp xỉ. Chúng ta không nhắm đến việc khớp một cách chính xác nhưng tìm một khoảng mà giá trị bán hàng rơi vào. Chúng ta sẽ tiếp tục sử dụng tính năng Hợp nhất nhưng cần áp dụng thêm một số biến đổi để đạt được kết quả cuối cùng mong muốn.

Nạp dữ liệu vào Power Query

Hãy bắt đầu với việc nạp ba bảng dữ liệu vào Power Query

Click vào một ô bất kỳ trong sheet Sales, rồi chọn  Data -> From Table / Range từ menu của Excel.

Cửa sổ Power Query Editor sẽ xuất hiện. Các bảng đã được cài đặt sẵn để giảm thiểu các biến đổi cần thiết, nhưng thường thì trên thực tế không đơn giản như vậy.

Trong trường hợp ví dụ này, chúng ta không cần đưa dữ liệu vào sheet tính mà chỉ cần tạo kết nối. Từ thanh công cụ Power Query, click vào Home -> Close & Load To…

Chọn Only Create Connection từ cửa sổ Import Data và sau đó nhấn OK.

Làm lại như vậy cho các sheet Customer và Rebates.

Trong Excel, mở thanh công cụ Queries & Connections (Nhấn Data -> Queries & Connections nếu nó không được hiển thị), ba truy vấn sẽ hiện thị trong đó.

Giờ đây chúng ta đã chuẩn bị xong để bắt đầu thao tác với chức năng Hợp nhất

Tìm kiếm với kết quả khớp chính xác

Để minh họa cho việc thu được kết quả khớp chính xác, chúng ta sẽ soạn một báo cáo tổng doanh số bán hàng theo Thị Trấn.

Quay lại trình soạn thảo Power Query bằng cách double-click vào truy vấn Sales trong thanh Queries and Connections.

Hãy tiến hành chọn Home -> Merge Queries (menu xổ xuống) trong biên tập Power Query.

Bạn sẽ gặp hai lựa chọn tại điểm này, là Merge QueriesMerge Queries as New. Sự phân biệt giữa chúng nằm ở việc liệu việc hợp nhất sẽ tạo một truy vấn mới hoặc chỉ thêm thành một bước mới trong truy vấn hiện tại. Chúng ta sẽ chọn tạo một truy vấn mới cho thuận tiện, bằng cách chọn Merge Queries as New.

Cửa sổ hợp nhất sẽ xuất hiện:

Nhiều bước được thực hiện ở phần này:

  1. Hãy lựa chọn truy vấn đầu tiên cần dùng – trong trường hợp này, chúng ta dùng truy vấn Sales.
  2. Tiếp theo, chọn truy vấn thứ hai – ví dụ của chúng tôi là truy vấn Customers.
  3. Chọn các cột từ cả hai truy vấn để khớp chúng lại với nhau.
  4. Loại hợp nhất có thể chọn được cung cấp ở phần Join Kind, bao gồm sáu tùy chọn khác nhau – chúng ta chọn Left Outer theo ví dụ này.
  5. Ấn vào nút OK.

Có tổng cộng sáu kiểu nối, hãy tham khảo phần sau để hiểu rõ hơn về chúng.

Một truy vấn mới được thiết lập. Truy vấn đầu tiên được chọn trong cửa sổ Merge sẽ hiện lên, đi kèm với một cột thêm vào cho bảng từ truy vấn thứ hai.

Nhấn vào icon của bảng giãn ra ở tiêu đề cột Customers. Ta không cần các cột khác ngoại trừ Customers và Town, vì thế chỉ cần chọn hai cột này, rồi tiếp tục nhấn OK.

Power Query sẽ thực hiện việc hợp nhất bằng cách map các giá trị từ bảng thứ nhất sang bảng thứ hai.

Kết quả từ sự hợp nhất chứa đầy đủ các mục từ bảng Sales cùng với những mục khớp từ bảng Customers. Mọi mục không tìm được khớp nối sẽ hiển thị là null như hình dưới đây. Mega Mart là một khách hàng nằm trong truy vấn Sales nhưng lại không xuất hiện trong truy vấn Customers, do đó một giá trị rỗng được hiển thị.

Để hoàn tất ví dụ, chúng tôi sẽ dùng cột đã được hợp nhất để tạo ra một bản báo cáo tổng hợp. Chọn cột Town và sau đó chọn Transform -> Group By từ thanh công cụ.

Trong hình dưới đây, tôi đã chọn để Sum cột Value, và tạo ra một cột mới tên là Total Sales. Khi nhấn OK, một báo cáo tổng hợp theo Town sẽ được tạo ra như hình bên dưới.

 

Các loại hợp nhất

Hãy xem xét một chút về sáu loại hợp nhất khác nhau trước khi chúng ta đi vào ví dụ khác.

Các loại nối này giúp chúng ta so sánh các danh sách và trả lại giá trị tương đương. May mắn thay, mỗi loại nối đều có mô tả ngắn gọn giải thích cách thức hoạt động của nó.

Outer Joins

Outer Joins sẽ trả lại các hàng từ một hay cả hai danh sách. Bạn có thể chọn Left, Right, hoặc Full tùy thuộc vào việc bạn muốn trả lại toàn bộ hàng từ danh sách nào.

  • Left Outer– trả về tất cả mục từ danh sách đầu với các mục tương ứng từ danh sách thứ hai.
  • Right Outer– trả về tất cả hàng từ danh sách thứ hai cùng với những hàng tương ứng từ danh sách đầu.
  • Full Outer – trả về toàn bộ các hàng từ cả hai danh sách.

Inner Join

Inner Join chỉ trả lại các giá trị có mặt trong cả hai danh sách. Mục nào thiếu trong một danh sách sẽ bị loại khỏi kết quả chung cuộc.

Anti Joins

Anti Joins sẽ đưa ra các hàng không khớp với bất kỳ hàng nào trong danh sách còn lại.

  • Left Anti – trả lại các mục từ danh sách đầu tiên không có phần khớp trong danh sách thứ hai.
  • Right Anti
    • Các mục không tương ứng từ danh sách thứ hai so với danh sách thứ nhất.

    Những dẫn chứng này chứng minh rằng Power Query có thể làm việc hiệu quả hơn VLOOKUP.

    Kiểm tra với kết quả tìm kiếm gần chính xác

    Lúc này, ta sẽ khám phá việc kiểm tra kết quả tìm kiếm gần chính xác. Nếu bạn chưa rõ nghĩa của nó là gì, bạn có thể tham khảo vào bài viết của tôi về kiểm tra gần chính xác.

    Ví dụ ở đây, ta đang xác định giá trị cho giảm giá mà một khách hàng nhận được, dựa trên doanh số bán hàng của họ. Doanh số càng cao, khách hàng nhận được mức chiết khấu sau:

    • Doanh số $500 sẽ được chiết khấu 2%
    • Doanh số $750 sẽ được chiết khấu 5%
    • Doanh số $1,000 sẽ được chiết khấu 10%

    Chúng ta thể hiện các mức ngưỡng này trong một bảng trên trang tính.

    Đầu tiên cần làm là tính tổng số doanh số bán hàng cho mỗi khách hàng, sau đó làm hợp nhất dữ liệu phức tạp hơn để xác định giá trị giảm giá dựa trên tổng doanh số đã tính được.

    Thêm lại bảng Sales vào Power Query như đã làm theo hướng dẫn trên.

    Trong editor Power Query, chọn Transform -> Group By.

    Cửa sổ Group By hiện lên, chọn như sau:

    • Nhóm theo: Customer
    • Tên cột mới: Total Sales
    • Phép tính: Sum
    • Cột: Value

    Ấn OK. Kết quả là bảng xem trước hiển thị tổng doanh số cho mỗi khách hàng.

    Trong ví dụ trước, ta đã thực hiện Merge dưới dạng một truy vấn mới; nhưng bây giờ, ta có thể thêm nó như một bước nối tiếp. Chọn Home -> Merge Queries.

    Trong cửa sổ Merge, chọn nối Full Outer giữa cột Total Sales từ bảng Sales và Rebate Band từ bảng Rebates.

    Ấn OK để đóng sổ và quay lại Cửa sổ xem trước.

    Giãn cột Rebates, bao gồm cả hai cột, rồi ấn OK.

    Bảng xem trước giờ sẽ trông như sau:

    Giờ ta sẽ tạo ra một lệnh if. Ta có thể sử dụng chức năng Conditional Column, nhưng tôi thích soạn thảo nó thành công thức hơn. Chọn vào Add Column -> Custom Column.

    Cửa sổ Custom Column hiện ra. Điền tên mới cho cột và nhập công thức sau:

    Chúng ta sẽ giải thích chi tiết về lệnh if trong một bài viết sau. Hiện giờ, hãy xem nó như hàm IF thông thường của Excel với những điều chỉnh sau: 

    • Bỏ dấu ngoặc
    • Đổi dấu phẩy đầu tiên thành từ then
    • Đổi dấu phẩy cuối cùng thành từ else

    Sắp xếp cột mới bằng cách chọn tiêu đề cột và nhấp vào Home -> A-Z.

    Tiếp đó, chọn cột Rebate Value rồi nhấp vào Transform -> Fill (dropdown) -> Down.

    Bảng xem trước sẽ hiển thị như sau:

    Các bước cuối cùng để làm sạch bảng bao gồm:

    • Lọc bỏ các giá trị trống trong cột Customers
    • Xóa bỏ tất cả các cột trừ Customer, Total Sales, Rebate Value.

    Giờ ta tính toán giá trị giảm giá. Chọn cột Total Sales và Rebate Value, rồi chọn Add Column -> Standard -> Multiply.

    Bảng truy vấn sau cùng sẽ thể hiện kết quả.

    Kết thúc cuộc tìm kiếm

    Trường hợp có nhiều kết quả khớp

    Khi có sự xuất hiện của nhiều kết quả khớp với một tiêu chí, hàm VLOOKUP của Excel sẽ chọn và trả về kết quả đầu tiên nó tìm thấy trong dãy dữ liệu. Trong khi đó, Merge lại có cách thức hoạt động khác biệt.

    Chức năng Merge lại cho phép bạn nhận được mỗi biến thể khớp của một kết quả. Chẳng hạn, giả sử ta đang có hai bảng, một bảng về thông tin Sản phẩm và một bảng khác liên quan đến dữ liệu Kho hàng.

    Khi chỉ thực hiện Merge dựa trên cột Kích thước, các mục có kích thước M sẽ được nhân bản do có sự xuất hiện của hai chữ M trong bảng đang được xem xét.

    Trong trường hợp bạn mong muốn chỉ khớp duy nhất một mục, bạn nên loại bỏ những mục lặp lại trong một trong những bảng trước khi tiến hành Merge.

    Phương pháp tra cứu dựa trên nhiều điều kiện

    Bạn sẽ mừng rỡ khi biết rằng Power Query không hạn chế việc bạn cần tra cứu một loạt các điều kiện. Điều gì xảy ra nếu bạn cần khớp cùng lúc ba cột? Điều đó không phải là vấn đề.

    Trật tự mà bạn chọn cột sẽ quyết định xem cột nào sẽ được ghép nối. Nhìn vào ví dụ minh họa, các cột được chọn là Color, Size và tiếp theo là Location (theo thứ tự đã nêu). Các con số trong tiêu đề cột chỉ thứ tự ghép nối các mục được chọn. Bảng thứ hai cũng chọn các cột theo đúng trật tự tương ứng: Color, Size và Location.

    Thao tác thật sự đơn giản!

    Khớp mờ trong tra cứu

    Khi quan sát cửa sổ Merge trong những ví dụ trình bày, có thể bạn đã chú ý đến lựa chọn “Khớp mờ khi thực hiện hợp nhất”. Đây là một tính năng đáng chú ý, giúp icánh ghép những giá trị tương tự nhau. Ví dụ: nó có khả năng ghép “Power Query” với “power-query”, một điều mà bạn không thể thực hiện với hàm VLOOKUP! Ta còn có thể chỉnh sửa ngưỡng giá trị tương tự trước khi việc khớp mờ diễn ra.

    Vì đây là một tính năng mới, tôi sẽ không đề cập chi tiết tại đây, nhưng bạn có thể tìm hiểu thêm qua các nguồn tài liệu sau:

    • Excel University – Khớp mờ với Power Query
    • Microsoft – Hỗ trợ khớp mờ cho Get & Transform (Power Query)
    Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop