Các hàm tra cứu Excel thành thạo:Khung đã được chứng minh để chọn chiến lược tốt nhất

Chức năng tra cứu giúp việc truy xuất dữ liệu hiệu quả. Excel cung cấp nhiều tùy chọn tra cứu và việc chọn chiến lược phù hợp có thể khó khăn. Việc lựa chọn phương pháp tối ưu sẽ mang lại kết quả đáng tin cậy nhất. Trong hướng dẫn này, chúng tôi tạo khung hàm tra cứu Excel để chọn chiến lược phù hợp. Khung này giúp người dùng có kinh nghiệm quyết định phương pháp nào phù hợp nhất dựa trên hình dạng, quy mô và mục đích của dữ liệu.

Bạn có thể làm theo sơ đồ trên để chọn chiến lược tra cứu phù hợp.

XLOOKUP:Tốt nhất để tra cứu từng người một (Excel hiện đại)

Đầu tiên, hãy quyết định những gì bạn đang tìm kiếm. Bạn đang truy xuất một bản ghi phù hợp hay bạn cần tất cả các bản ghi phù hợp với tiêu chí của mình?

Sử dụng XLOOKUP khi bạn muốn có một kết quả duy nhất, công thức có thể đọc được và xử lý "không tìm thấy" tùy chọn.

Hãy xem xét một tập dữ liệu bán hàng mẫu trong đó đơn đặt hàng, chi tiết sản phẩm và thông tin khách hàng nằm trong các bảng riêng biệt. Chúng tôi khám phá các chiến lược tra cứu để lấy thông tin bán hàng từ nhiều bảng khác nhau.

Tra cứu Đơn giá thành Đơn hàng:

=XLOOKUP(D2, Products!$A$2:$A$7, Products!$D$2:$D$7, "Not found")

Công thức tương tự ở định dạng bảng:

=XLOOKUP(Orders[@SKU], Products[SKU], Products[UnitPrice], "SKU not found")

Tìm Tên khách hàng trong Đơn hàng:

=XLOOKUP(Orders[@CustomerID], Customers[CustomerID], Customers[CustomerName], "Customer not found")

Các công thức này tra cứu các giá trị trong các bảng khác nhau và trả về một bản ghi khớp duy nhất. XLOOKUP có thể là lựa chọn mặc định của bạn để tra cứu từng cái một trong Excel hiện đại. Nó cung cấp:

• Cú pháp rõ ràng, dễ đọc với tính năng xử lý lỗi tích hợp thông qua if_not_found lý lẽ
• Hỗ trợ riêng cho việc tra cứu sang trái và hai chiều
• Chế độ đối sánh chính xác và gần đúng không có tham số loại đối sánh phức tạp

Sử dụng XLOOKUP khi bạn có Microsoft 365 hoặc Excel 2021+ và cần truy xuất một giá trị đơn giản.

INDEX/MATCH:Tốt nhất về khả năng tương thích và vẫn mạnh mẽ

Chọn INDEX/MATCH nếu tệp của bạn phải hoạt động trong các phiên bản Excel cũ hơn hoặc khi tiêu chuẩn nhóm của bạn là INDEX/MATCH.

Đơn giá qua INDEX/MATCH:

=INDEX(Products[UnitPrice], MATCH(Orders[@SKU], Products[SKU], 0))

Công thức này vẫn là một lựa chọn chắc chắn cho các phiên bản cũ hơn, nhưng nó dễ đọc sai hơn và khó duy trì trên quy mô lớn hơn XLOOKUP đối với nhiều nhóm.

BỘ LỌC:Tốt nhất cho kết quả một-nhiều (Danh sách tràn)

Chọn LỌC khi bạn muốn có danh sách động các bản ghi cập nhật tự động. Nó trích xuất một tập hợp con dữ liệu dựa trên các tiêu chí và giữ cho các kết quả đó được cập nhật tự động. FILTER được xây dựng có mục đích để truy xuất một-nhiều:

• Trả về tất cả các hàng phù hợp dưới dạng mảng tràn động
• Xử lý nhiều tiêu chí bằng logic Boolean AND/OR
• Tự động mở rộng và thu gọn khi dữ liệu nguồn thay đổi
• Tích hợp liền mạch với các hàm mảng động khác

Hiển thị tất cả đơn đặt hàng cho ID khách hàng:

=FILTER(Orders, Orders[CustomerID]=H2, "No orders found")

Tất cả các đơn đặt hàng ở khu vực phía Tây cho SKU P-101:

=FILTER(Orders, (Orders[Region]=H9)*(Orders[SKU]=H7), "No matches")

Các công thức này khác với XLOOKUP vì FILTER không trả về một giá trị duy nhất—nó trả về một câu trả lời có dạng bảng, bị đổ. Đó là công việc khác với XLOOKUP/INDEX-MATCH.

Power Query:Tốt nhất khi tra cứu thực sự là "Tham gia" (Mô hình hóa dữ liệu)

Power Query trở thành lựa chọn tốt hơn khi bạn cần thực hiện các phép biến đổi trên kết quả được lọc. Hãy sử dụng nó khi,

• Dữ liệu lớn,
• Dữ liệu đến từ các nguồn bên ngoài và phải được làm mới,
• Bạn muốn có một quy trình chuyển đổi có thể lặp lại,
• Bạn muốn tạo một bảng hợp nhất, rõ ràng cho PivotTable/trang tổng quan.

Hãy tạo một bảng bao gồm các cột Sản phẩm và Khách hàng (Đơn giá, Sản phẩm, Tên khách hàng, Phân khúc).

Các bước (Hợp nhất/Tham gia):

• Chuyển đổi phạm vi thành bảng (nhấn CTRL+T ). Dữ liệu của chúng tôi đã ở định dạng bảng.
• Đi tới Dữ liệu tab>> chọn Nhận dữ liệu >> chọn Từ Bảng/Phạm vi (bắt đầu bằng Đơn hàng ).

• Trong Power Query Editor:Trang chủ >> chọn Hợp nhất truy vấn
  • Hợp nhất Đơn hàng với Sản phẩm bàn
  • Chọn Đơn hàng[SKU] và Sản phẩm[SKU]
  • Hình thức tham gia: Chọn Trái ngoài (giữ tất cả các Đơn hàng)
  • Nhấp vào OK

• • Nhấp vào Mở rộng biểu tượng>> chọn Sản phẩm, Danh mục và Đơn giá
  • Nhấp vào OK

• Hợp nhất Đơn hàng với Khách hàng bảng
  • Khớp Đơn hàng[CustomerID] tới Khách hàng[CustomerID]

• • Nhấp vào Mở rộng biểu tượng>> chọn Tên khách hàng , Phân đoạn
  • Nhấp vào OK

• Nhấp vào Đóng và tải để tải dữ liệu trong Excel.

Đây là bảng giàu đơn đặt hàng mới, bao gồm thông tin chi tiết về sản phẩm và khách hàng.

Bạn hợp nhất một lần, làm mới bất kỳ lúc nào và tránh lặp lại việc tra cứu trên hàng nghìn hàng và nhiều cột.

Chọn giữa LỌC và Power Query cho một-nhiều

Cả LỌC và Power Query đều cung cấp kết quả tự động, nâng cao. Đôi khi người dùng gặp phải tình huống khó xử khi chọn một cho nhiều người.

• Chọn LỌC khi cần trải nghiệm trang tính tương tác. Thay đổi ô tiêu chí và danh sách sẽ cập nhật ngay lập tức. Nó rất hữu ích cho các trang tổng quan tương tác.
• Chọn Power Query khi cần tập dữ liệu có thể làm mới để cung cấp báo cáo (PivotTable, biểu đồ, bảng mô hình), đặc biệt là với dữ liệu lớn hơn.

Mẹo và thủ thuật

• Nếu đầu ra là một ô, hãy chọn XLOOKUP (hoặc INDEX/MATCH để tương thích).
• Nếu đầu ra là một danh sách/bảng sẽ tự động phát triển, hãy chọn LỌC.
• Nếu nhiệm vụ về cơ bản là nối các tập dữ liệu và phải có khả năng làm mới/mở rộng quy mô, hãy sử dụng Power Query.
• Nếu bạn đang thực hiện tra cứu tương tự trên nhiều cột (giá, danh mục, tên, phân khúc…), chúng tôi khuyên bạn nên hợp nhất Power Query thay vì lặp lại công thức.

Kết luận

Lưu đồ hướng dẫn này giúp bạn chọn chiến lược tra cứu phù hợp. Trước tiên, hãy quyết định loại tra cứu nào mà dữ liệu của bạn yêu cầu. Không có chức năng tra cứu “tốt nhất” phổ quát nào cả - bối cảnh sẽ xác định cách tiếp cận tối ưu. Nắm vững khung, không chỉ các chức năng và chiến lược tra cứu của bạn sẽ mở rộng theo nhu cầu của bạn. Phát triển ý thức chiến lược để chọn phương pháp tra cứu phù hợp và các giải pháp của bạn sẽ được xây dựng nhanh hơn, dễ bảo trì hơn và mạnh mẽ hơn khi yêu cầu phát triển.