POWER QUERY – TRA CỨU GIÁ TRỊ BẰNG HỢP NHẤT

Nếu bạn dùng Truy vấn nguồn Cách tra cứu dữ liệu? Không có hàm Vlookup, vì vậy làm thế nào chúng ta có thể Tra cứu dữ liệu bằng truy vấn nguồn? Học Excel Online Câu hỏi này sẽ được giải đáp trong một bài viết cụ thể ngay sau đây.

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ờ.

  • Đối sánh 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.
  • Đối sánh gần đúng tìm giá trị trên (hoặc dưới) giá trị tra cứu.
  • So khớp mờ tìm các 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 tôi 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 tôi sẽ sử dụng phép chuyển đổi hợp nhất nằm trên thanh công cụ Trang chủ.

Tải xuống

Để làm việc với các ví dụ dưới đây, hãy tải xuống các tệp mẫu. Nhấp vào Nhấp vào đây để đăng ký và có quyền truy cập vào phần Tải xuống.

Các ví dụ trong bài đăng này sử dụng một tệp có tên Ví dụ 14 – Tra cứu Data.xlsx

Tải game crack việt hoá tại: https://daominhha.com

Trong tệp có ba bảng:

  • Bán hàng – Chứa dữ liệu bán hàng hàng tháng
  • Khách hàng – Chứa thông tin liên hệ của khách hàng
  • Các khoản giảm giá – 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 tôi có thể tra cứu dữ liệu trong Power Query, chúng tôi sẽ tạo hai báo cáo.

Tổng doanh số bán hàng theo thị trấn (tra cứu với kết quả khớp chính xác)

Giá trị Doanh số và Thị trấn của mỗi khách hàng nằm trong các bảng riêng biệt; vì vậy chúng ta cần sử dụng hàm 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 chiết khấu cho mỗi Khách hàng, chúng tôi cần thực hiện một đối sánh gần đúng. Chúng tôi không nhất thiết muốn so khớp giá trị chính xác, nhưng để tìm phạm vi mà giá trị bán hàng giảm xuống. Chúng tôi sẽ vẫn sử dụng chức năng Hợp nhất, nhưng chúng tôi sẽ cần thêm một vài phép biế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 Bán hàng, sau đó chọn Dữ liệu -> Từ Bảng / Phạm vi từ menu Excel.

Power Query Editor sẽ tải. Tôi đã cố gắng thiết lập các bảng sao cho chúng yêu cầu các phép biến đổi tối thiểu, nhưng trong thực tế, nó hiếm khi đơn giản như vậy.

Đối với ví dụ của chúng tôi, chúng tôi không cần tải dữ liệu vào trang tính, thay vào đó chúng tôi có thể tạo kết nối. Từ ruy-băng Power Query, hãy nhấp vào Trang chủ -> Đóng & Tải đến…

Lựa chọn Chỉ tạo kết nối từ cửa sổ Nhập dữ liệu, sau đó nhấp vào VÂNG.

Lặp lại hành động này với bảng Khách hàng và Giảm giá.

Trong Excel, mở ngăn Truy vấn & Kết nối (Nhấn Dữ liệu -> Truy vấn & Kết nối nếu nó không hiển thị), ba truy vấn sẽ được liệt kê.

Bây giờ chúng tôi đã sẵn sàng để bắt đầu sử dụng tính năng Hợp nhất

Tra cứu với kết quả khớp chính xác

Để minh họa kết quả khớp chính xác, chúng tôi sẽ tạo 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 bấm đúp vào truy vấn Bán hàng trong ngăn Truy vấn và kết nối.

Trong trình chỉnh sửa Power Query, hãy chọn Trang chủ -> Hợp nhất Truy vấn (trình đơn thả xuống).

Ở đây có hai lựa chọn, Hợp nhất các truy vấn và Hợp nhất Hợp nhất các truy vấn làm mới. 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 chuyển tiếp trong một truy vấn hiện có. Để dễ dàng, chúng tôi sẽ sử dụng một truy vấn mới. Chọn tùy chọn Hợp nhất các truy vấn làm mới.

Cửa sổ Hợp nhất sẽ mở ra:

Rất nhiều điều xảy ra ở đây:

  1. Chọn truy vấn đầu tiên được sử dụng – ví dụ của chúng tôi là truy vấn Bán hàng.
  2. Chọn truy vấn thứ hai sẽ được sử dụng – trong ví dụ của chúng tôi là truy vấn Khách hàng.
  3. Chọn các cột từ hai bảng để được so khớp.
  4. Join Kind cung cấp sáu kiểu hợp nhất khác nhau – Chọn Left Outer nếu làm việc với ví dụ.
  5. Bấm OK.

Có 6 kiểu nối, hãy xem bên dưới để biết các kiểu làm khác nhau ở điểm nào.

Một truy vấn mới được tạo. Truy vấn đầu tiên được chọn trong cửa sổ Hợp nhất đượ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 được mở rộng trong tiêu đề của cột Khách hàng. Chúng tôi chỉ cần các cột Khách hàng và Thị trấn, 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 nhập từ bảng Bán hàng và các mục nhập khớp từ bảng Khách hàng. Bất kỳ mục nhập nào không khớp sẽ hiển thị null, như thể hiện trong hình bên dưới. Khách hàng của Mega Mart tồn tại trong truy vấn Bán hàng, nhưng không tồn tại trong truy vấn Khách hàng, do đó null được hiển thị.

Để hoàn thành ví dụ của chúng tôi, chúng tôi sẽ sử dụng cột đã hợp nhất để tạo báo cáo tóm tắt. Chọn cột Thị trấn, sau đó nhấp vào Chuyển đổi -> Nhóm Theo từ menu.

Trong hình trên, tôi đã chọn Tổng giá trị cột, thành một cột được gọi là Tổng doanh số. Sau khi tôi nhấp vào OK, nó sẽ tạo ra một báo cáo tóm tắt về doanh số bán hàng theo Thị trấn (xem hình ả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.

Tham gia bên ngoài

Các phép nối bên ngoài 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 Left, Right hoặc All, tùy thuộc vào danh sách nào trả về tất cả các hàng của nó.

  • Bên ngoài trái – 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.
  • Bên phải bên ngoài – 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.

Tham gia bên trong

Tham gia bên trong 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 chứa các mục không có trong danh sách kia, các mục đó sẽ bị loại khỏi kết quả cuối cùng.

Chống tham gia

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.
  • Phải Chống – 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ả các bằng chứng cho thấy Power Query có thể hiệu quả hơn VLOOKUP.

Tra cứu với kết quả phù hợp gần đúng

Bây giờ là lúc để tìm hiểu đối sánh gần đúng. Trước khi chúng tôi 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ề Đối sánh gần đúng.

Trong ví dụ này, chúng tôi đang tính toán giá trị chiết khấu do khách hàng đưa ra dựa trên giá trị bán hàng. Những khách hàng có doanh số bán hàng lớn hơn:

  • $ 500 được chiết khấu 2%
  • $ 750 được chiết khấu 5%
  • 1.000 đô la được giảm giá 10%

Các ngưỡng này được hiển thị trong một 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 hợp nhất phức tạp hơn để tính chiết khấu 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 Bán hàng 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 Chuyển đổi -> Nhóm Theo.

Cửa sổ Group By sẽ mở ra, thực hiện các lựa chọn sau:

  • Nhóm theo: Khách hàng
  • Tên cột mới: Tổng doanh số
  • Hoạt động: Tổng
  • Cột: Giá trị

Bấm OK. Cửa sổ Xem trước 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 tôi đã tạo Hợp nhất 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 chuột Trang chủ -> Hợp nhất các truy vấn.

Trong cửa sổ Hợp nhất, chúng tôi muốn sử dụng kết hợp Bên ngoài Toàn bộ giữa cột Tổng Doanh số của bảng Doanh số và Ban nhạc giảm giá của cái bàn Các khoản giảm giá.

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

Mở rộng cột Giảm giá, 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 tôi có thể sử dụng tính năng Cột có điều kiện, nhưng tôi thấy viết nó dưới dạng công thức dễ dàng hơn. Nhấp chuột Thêm cột -> Cột tùy chỉnh.

Cửa sổ Cột tùy chỉnh 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 tôi sẽ đề cập chi tiết hơn về 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 chuẩn trong Excel với những thay đổi sau:

  • Bỏ dấu ngoặc
  • Thay dấu phẩy đầu tiên bằng từ sau đó
  • Thay dấu phẩy cuối cùng bằng từ khác
>>> Xem thêm:  Cách dùng hàm ghép chuỗi, nối chữ theo ô, vùng hoặc theo nhiều điều kiện trong Excel

Sắp xếp cột mới bằng cách chọn tiêu đề cột, sau đó nhấp vào Trang chủ -> AZ.

Tiếp theo, chọn cột Giá trị hoàn lại và bấm vào Chuyển đổi -> Điền (thả xuống) -> Xuống.

Cửa sổ Xem trước sẽ trông như thế này:

Bây giờ chỉ còn vài bước để dọn dẹp bàn:

  • Lọc ra các sản phẩm không có giá trị từ Khách hàng. cột
  • Xóa tất cả các cột ngoại trừ Khách hàng, Tổng doanh số, Giá trị hoàn lại.

Bây giờ chúng ta hãy kết thúc bằng cách tính toán giá trị chiết khấu. Chọn cột Tổng doanh số và Giá trị hoàn lại, nhấp chuột Thêm cột -> Chuẩn -> Nhân.

Truy vấn hiển thị kết quả cuối cùng

Nhiều trận đấu

Điều gì sẽ xảy ra nếu có nhiều mặt hàng có thể được kết hợp? Trong trường hợp này, hàm VLOOKUP trong Excel trả về chính xác mục đầu tiên mà nó tìm thấy trong danh sách. Hợp nhất các hoạt động khác.

Hợp nhất 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 Sản phẩm và một bảng có dữ liệu Kho về các sản phẩm đó.

Nếu chúng ta chỉ Hợp nhất trên cột Kích thước, 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 kỳ 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 Hợp nhất.

Tra cứu nhiều tiêu chí

Tin tốt 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 ảnh dưới đây. Các cột được chọn trong bảng đầu tiên là Màu sắc, kích thước, rồi đến Vị trí (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 theo cùng một thứ tự, Màu sắc, Kích thước và Vị trí.

Điều đó thật dễ dàng làm sao!

Tra cứu trận đấu mờ

Khi nhìn vào cửa sổ Hợp nhất trong các ví dụ của chúng tôi, bạn có nhận thấy tùy chọn “Sử dụng kết hợp mờ để thực hiện hợp nhất” không? Đây là một lựa chọn hợp lý mới, nó phù hợ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 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 thêm thông tin trong các bài viết sau:

>>> Xem thêm:  Hướng dẫn sử dụng hàm VLOOKUP trả về mảng giá trị trong Excel

Nguồn : Blog hocexcel