Hàm tra cứu làm cho 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à chọn đúng chiến lược có thể khó khăn. Việc chọn cách tiếp cận tối ưu mang lại kết quả 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 đúng chiến lược. Khung này giúp người dùng có kinh nghiệm quyết định cách nào phù hợp nhất dựa trên hình dạng dữ liệu, quy mô và ý định.
Bạn có thể làm theo sơ đồ trên để chọn đúng chiến lược tra cứu.
XLOOKUP: Tốt Nhất Cho Tra Cứu Một-Một (Excel Hiện Đại)
Trước tiên, quyết định bạn đang tìm gì. Bạn đang truy xuất một bản ghi khớp duy nhất, hay bạn cần tất cả bản ghi khớp với tiêu chí của mình?
Dùng XLOOKUP khi bạn muốn một kết quả duy nhất, công thức dễ đọc, và xử lý "không tìm thấy" tùy chọn.
Xét tập dữ liệu bán hàng mẫu nơi đơn 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 ta khám phá chiến lược tra cứu để truy xuất thông tin bán hàng từ các bảng khác nhau.
Tra UnitPrice vào Orders:
=XLOOKUP(D2, Products!$A$2:$A$7, Products!$D$2:$D$7, "Not found")
Công Tương Tự Dạng Bảng:
=XLOOKUP(Orders[@SKU], Products[SKU], Products[UnitPrice], "SKU not found")
Tìm CustomerName Trong Orders:
=XLOOKUP(Orders[@CustomerID], Customers[CustomerID], Customers[CustomerName], "Customer not found")
Các công thức này tra cứu 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 cho tra cứu một-một trong Excel hiện đại. Nó cung cấp:
- Cú pháp sạch, dễ đọc với xử lý lỗi tích hợp qua đối số if_not_found
- Hỗ trợ gốc cho tra cứu ngược và hai chiều
- Chế độ khớp chính xác và xấp xỉ mà không cần tham số match-type khó hiểu
Dùng XLOOKUP khi bạn có Microsoft 365 hoặc Excel 2021+ và cần truy xuất giá trị đơn giản.
INDEX/MATCH: Tốt Nhất Cho Tương Thích Ngược Vẫn Mạnh Mẽ
Chọn INDEX/MATCH nếu file của bạn phải hoạt động trong Excel cũ, hoặc khi tiêu chuẩn của nhóm bạn là INDEX/MATCH.
UnitPrice Qua INDEX/MATCH:
=INDEX(Products[UnitPrice], MATCH(Orders[@SKU], Products[SKU], 0))
Công thức này vẫn là lựa chọn vững chắc cho các phiên bản cũ, nhưng nó dễ đọc sai và khó bảo trì ở quy mô lớn hơn XLOOKUP đối với nhiều nhóm.
FILTER: Tốt Nhất Cho Kết Quả Một-Nhiều (Danh Sách Tràn)
Chọn FILTER khi bạn muốn danh sách động các bản ghi tự động cập nhật. Nó trích xuất tập con dữ liệu dựa trên tiêu chí và giữ kết quả đó cập nhật tự động. FILTER được xây dựng chuyên dụng cho truy xuất một-nhiều:
- Trả về tất cả hàng khớp dưới dạng mảng tràn động (dynamic spill array)
- Xử lý nhiều tiêu chí với logic Boolean AND/OR
- Tự động mở rộng và co lại 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 Tất Cả Đơn Hàng Của Một CustomerID:
=FILTER(Orders, Orders[CustomerID]=H2, "No orders found")
Tất Cả Đơn Hàng Khu Vực West 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ề giá trị đơn—nó trả về câu trả lời hình bảng đã tràn. Đó là công việc khác biệt so với XLOOKUP/INDEX-MATCH.
Power Query: Tốt Nhất Khi Tra Cứu Thực Chất Là "Join" (Mô Hình 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 biến đổi trên kết quả lọc. Dùng nó khi,
- Dữ liệu lớn,
- Dữ liệu đến từ nguồn bên ngoài và phải làm mới,
- Bạn muốn đường ống biến đổi lặp lại được,
- Bạn muốn xây dựng bảng gộp sạch cho PivotTables/dashboard.
Hãy tạo một bảng bao gồm cột Sản phẩm và Khách hàng (UnitPrice, Product, CustomerName, Segment).
Các Bước (Merge/Join):
- Chuyển vùng thành bảng (nhấn CTRL+T). Dữ liệu của chúng ta đã ở định dạng bảng.
- Vào tab Data >> chọn Get Data >> chọn From Table/Range (bắt đầu với Orders).
- Trong Power Query Editor: Home >> chọn Merge Queries
- Merge Orders với bảng Products
- Chọn Orders[SKU] và Products[SKU]
- Join kind: Chọn Left Outer (giữ tất cả Orders)
- Click OK
-
- Click biểu tượng Expand >> chọn Product, Category, và UnitPrice
- Click OK
- Merge Orders với bảng Customers
- Khớp Orders[CustomerID] với Customers[CustomerID]
-
- Click biểu tượng Expand >> chọn CustomerName, Segment
- Click OK
- Click Close & Load để tải dữ liệu vào Excel.
Đây là bảng Orders đã làm giàu, bao gồm chi tiết sản phẩm và khách hàng.
Bạn merge một lần, làm mới bất cứ khi nào, và tránh lặp lại tra cứu trên hàng nghìn hàng và nhiều cột.
Chọn Giữa FILTER Và Power Query Cho Một-Nhiều
Cả FILTER và Power Query đều cung cấp kết quả nâng cao, tự động. Đôi khi người dùng gặp khó khăn khi chọn một cho một-nhiều.
- Chọn FILTER khi cần trải nghiệm sheet tương tác. Thay đổi ô tiêu chí, và danh sách cập nhật ngay lập tức. Hữu ích cho dashboard tương tác.
- Chọn Power Query khi cần tập dữ liệu có thể làm mới để cấp báo cáo (PivotTables, biểu đồ, bảng mô hình), đặc biệt với dữ liệu lớn.
Mẹo Và Chiêu Bài
- Nếu đầu ra là một ô, chọn XLOOKUP (hoặc INDEX/MATCH cho tương thích).
- Nếu đầu ra là danh sách/bảng nên tự động mở rộng, chọn FILTER.
- Nếu nhiệm vụ về cơ bản là nối tập dữ liệu và phải có thể làm mới/mở rộng, dùng Power Query.
- Nếu bạn làm cùng tra cứu trên nhiều cột (giá, danh mục, tên, segment…), chúng tôi mạnh mẽ khuyên dùng Power Query merge thay vì lặp lại công thức.
Kết Luận
Sơ đồ hướng dẫn này giúp bạn chọn đúng chiến lược tra cứu. Quyết định trước loại tra cứu dữ liệu của bạn yêu cầu. Không có hàm tra cứu "tốt nhất" chung—bối cảnh quyết định cách tiếp cận tối ưu. Hãy thành thạo khung làm việc, không chỉ các hàm, và chiến lược tra cứu của bạn sẽ mở rộng theo nhu cầu. Phát triển tư duy chiến lược để chọn đúng phương pháp tra cứu, và giải pháp của bạn sẽ nhanh hơn để xây dựng, dễ bảo trì hơn, và mạnh mẽ hơn khi yêu cầu phát triển.
Nhận MIỄN PHÍ Bài Tập Excel Nâng Cao Có Giải Pháp!