›13:31:32 20-05-2024 | Lượt xem: 1541
Hướng dẫn này chỉ ra cách sử dụng Excel XLOOKUP với nhiều tiêu chí và giải thích những ưu điểm cũng như hạn chế của phương pháp này.
Trong Excel, có hàm tuyệt vời này được gọi là XLOOKUP, giúp bạn dễ dàng tìm thấy các giá trị cụ thể trong bảng của mình. Nó không chỉ tìm kiếm một thứ mà còn có thể tìm kiếm bằng nhiều điều kiện khác nhau. Trong bài viết này, sẽ chỉ cho bạn cách kết hợp các tiêu chí khác nhau để tìm ra kết quả phù hợp hoàn hảo cho dữ liệu của bạn. Bạn sẽ ngạc nhiên trước những gì bạn có thể làm được với chức năng này!
Trước khi đi sâu vào nhiều tiêu chí, hãy xem nhanh cú pháp XLOOKUP, tập trung vào các yếu tố cần thiết:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Chúng ta đặc biệt quan tâm đến ba đối số đầu tiên:
lookup_value - giá trị bạn đang tìm kiếm.
lookup_array - phạm vi mà bạn muốn tìm kiếm giá trị tra cứu.
return_array - phạm vi từ đó trả về giá trị tương ứng.
Cách dễ nhất để sử dụng XLOOKUP với nhiều tiêu chí là áp dụng logic Boolean. Thuật ngữ này chỉ đơn giản nói rằng mọi thứ đều đúng hoặc sai. Trong XLOOKUP, điều này có nghĩa là:
XLOOKUP(1, ( lookup_array1 = lookup_value1 ) * ( lookup_array2 = lookup_value2 ) * (…), return_array )
XLOOKUP tìm kiếm số 1 trong khi tạo một mảng tra cứu tạm thời chứa đầy các số 0 (không khớp) và 1 (khớp). Trước tiên, bạn kiểm tra từng giá trị tra cứu với tất cả các giá trị trong mảng tra cứu tương ứng, tạo ra một mảng giá trị TRUE và FALSE. Sau đó, bạn nhân các mảng này, biến TRUE và FALSE thành 1 và 0 và tạo thành một mảng tra cứu duy nhất. Mảng cuối cùng này có 1 cho các mục đáp ứng tất cả tiêu chí và XLOOKUP trả về kết quả khớp được tìm thấy đầu tiên.
Ví dụ: để tìm nhà cung cấp mặt hàng mục tiêu trong khu vực mục tiêu, công thức chung sẽ là:
=XLOOKUP(1, (Items=Target_Item) * (Regions=Target_Region), Suppliers)
Một cách tiếp cận khác bao gồm việc kết hợp tất cả các giá trị đích (điều kiện) thành một lookup_value duy nhất bằng cách sử dụng toán tử nối (&). Sau đó, tìm kiếm giá trị đó trong lookup_array được nối:
XLOOKUP( lookup_value1 & lookup_value2 & …, lookup_array1 & lookup_array2 & …, return_array )
Ví dụ: để biết nhà cung cấp của một sản phẩm cụ thể dựa trên tên và khu vực của sản phẩm đó, bạn có thể sử dụng công thức:
=XLOOKUP(Target_Item & Target_Region , Items & Regions, Suppliers)
Mặc dù công thức này có vẻ đơn giản nhưng nó có thể gặp rắc rối trong các tình huống phức tạp hơn, đặc biệt là khi xử lý các toán tử logic hoặc logic OR. Do đó, bạn nên sử dụng phương pháp logic Boolean vì tính linh hoạt và độ tin cậy của nó.
Bây giờ chúng ta đã đề cập đến công thức cơ bản, hãy đi sâu vào ứng dụng thực tế. Hãy tưởng tượng nhiệm vụ của bạn là tìm nhà cung cấp dựa trên ba tiêu chí: tên mặt hàng, khu vực và hình thức giao hàng. Nhiệm vụ có thể được thực hiện bằng hai công thức khác nhau được nêu chi tiết dưới đây. Mặc dù cả hai công thức đều dẫn đến cùng một kết quả nhưng chúng có những con đường khác nhau.
Đối với tập dữ liệu mẫu này, hãy sử dụng công thức sau để tìm nhà cung cấp dựa trên 3 tiêu chí trong các ô G4, G5 và G6:
=XLOOKUP(1, (A3:A22=G4) * (B3:B22=G5) * (C3:C22=G6), D3:D22)
Dưới đây là bảng phân tích về cách thức hoạt động của công thức này:
1. Kiểm tra các điều kiện riêng lẻ
Đầu tiên, công thức so sánh mục đích trong ô G4 với tất cả các mục trong phạm vi A3:A22. Tương tự, nó kiểm tra khu vực trong G5 với tất cả các khu vực trong B3:B22 và loại giao hàng trong G6 so với tất cả các dịch vụ giao hàng trong C3:C22. Những so sánh này tạo ra ba mảng giá trị TRUE và FALSE như sau:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;…} * {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;… } * {FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;…}
2. Phép nhân
Phép nhân chuyển đổi các giá trị TRUE và FALSE tương ứng thành 1 và 0, tạo thành một mảng tra cứu duy nhất. Nhân với 0 đảm bảo chỉ những mục đáp ứng tất cả các tiêu chí mới được biểu thị bằng 1.
{0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0}
3. XLOOKUP đang hoạt động
Mảng này trở thành mảng lookup_array cho XLOOKUP, trong đó nó đang tìm kiếm số 1. Giá trị thứ 10 trong mảng là 1 tương ứng với mục nhập thứ 10 trong tập dữ liệu. XLOOKUP tìm thấy nó và trả về giá trị thứ 10 trong return_array (D3:D22), là "Elijah".
Nhiệm vụ tương tự có thể được thực hiện với công thức này:
=XLOOKUP(G4 & G5 & G6, A3:A22 & B3:B22 & C3:C22, D3:D22)
Đây là sự cố cho phương pháp này:
1. Ghép nối các giá trị tra cứu
Ghép nối cả ba giá trị tra cứu (G4, G5 và G6) thành một lookup_value duy nhất bằng cách sử dụng toán tử ghép nối. Nói một cách đơn giản, chúng ta đang tạo một chuỗi kết hợp để tìm kiếm: "OrangesWestExpeded".
2. Ghép nối các mảng tra cứu
Ghép nối các phạm vi tương ứng A3:A22, B3:B22 và C3:C22 để tạo một mảng tra cứu duy nhất , chẳng hạn như:
{"ApplesEastStandard";"ApplesEastExpeded";"ApplesEastOvernight";"ApplesWestStandard";"ApplesWestExpeded";"ApplesWestOvernight";"OrangesEastStandard";"OrangesEastExpeded";"OrangesWestStandard";"OrangesWestExpededed"; …}
3. XLOOKUP tại dịch vụ của bạn
XLOOKUP tìm kiếm giá trị tra cứu được nối trong mảng tra cứu kết hợp. Khi xác định được hàng phù hợp, nó sẽ trả về giá trị tương ứng từ mảng trả về (D3:D22).
Mở rộng phạm vi của nhiều tiêu chí XLOOKUP, bạn có thể vượt xa các kiểm tra đẳng thức đơn giản bằng cách kết hợp các toán tử logic khác nhau . Các toán tử này cho phép bạn kiểm tra các điều kiện như lớn hơn, nhỏ hơn hoặc không bằng các giá trị cụ thể.
Ví dụ: hãy xem xét kịch bản tìm được nhà cung cấp cho mặt hàng ở G4, khu vực không khớp với G5 và mức chiết khấu lớn hơn G6. Công thức để đạt được điều này như sau:
=XLOOKUP(1, (A3:A22=G4) * (B3:B22<>G5) * (C3:C22>G6), D3:D22)
Công thức XLOOKUP cơ bản có thể tìm kiếm kết quả khớp chính xác hoặc gần đúng, được kiểm soát bởi đối số thứ 5, match_mode. Khi xử lý nhiều điều kiện, thách thức nảy sinh là tìm một giá trị gần khớp với một trong các tiêu chí.
Giải pháp này trước tiên bao gồm việc lọc ra các mục không đáp ứng điều kiện khớp chính xác, đạt được thông qua hàm IF hoặc FILTER. Sau đó, mảng đã lọc được cung cấp cho XLOOKUP, nhắc đến kết quả khớp gần đúng - bạn chọn giữa mục nhỏ hơn gần nhất ( match_mode được đặt thành -1) hoặc mục lớn nhất gần nhất ( match_mode được đặt thành 1).
Trong một tình huống ví dụ với tên mặt hàng ở cột A, số lượng ở cột B và mức giảm giá ở cột C, nhằm tìm mức giảm giá cho một mặt hàng cụ thể trong ô F4 và số lượng ở F5, công thức được xây dựng như sau:
=XLOOKUP(F5, IF(A3:A22=F4, B3:B22), C3:C22,, -1)
Chia nhỏ nó ra, logic bên trong sẽ lọc các mục khớp với F4 và số lượng tương ứng của chúng:
IF(A3:A22=F4, B3:B22)
Điều này dẫn đến một mảng bao gồm số lượng cho các mục phù hợp và FALSE cho các mục không khớp:
{…;FALSE;FALSE;FALSE;20;50;100;150;200;250;FALSE;FALSE;FALSE;…}
Với số lượng mục tiêu là 75 trong F5, XLOOKUP với match_mode được đặt thành -1, tìm kiếm mục nhỏ hơn tiếp theo trong mảng trên, tìm 50 và trả về mức giảm giá tương ứng từ cột C (3%).
Ngoài ra, bạn có thể thực hiện lọc bằng chức năng LỌC:
=XLOOKUP(F5, FILTER(B3:B22, A3:A22=F4), FILTER(C3:C22, A3:A22=F4),, -1)
Trong phiên bản này, bạn lọc số lượng (B3:B22) dựa trên mục tiêu (A3:A22=F4) cho mảng tra cứu và đối với mảng trả về, bạn lọc giảm giá (C3:C22) cho cùng mục tiêu.
Trong các ví dụ trước, chúng ta đã đi sâu vào logic AND, tìm giá trị đáp ứng tất cả các tiêu chí đã chỉ định. Bây giờ, hãy khám phá cách sử dụng XLOOKUP với logic OR, tìm các giá trị đáp ứng ít nhất một trong các điều kiện.
Tùy thuộc vào tiêu chí của bạn nằm trong cùng một cột hay ở các cột khác nhau, có 2 biến thể của công thức.
Công thức này sử dụng logic Boolean với phép toán cộng (+) biểu thị logic OR:
XLOOKUP(1, ( lookup_array = lookup_value1 ) + ( lookup_array = lookup_value2 ) + (…), return_array )
Nói một cách đơn giản, khi bạn nhân các mảng có giá trị TRUE và FALSE từ các lần kiểm tra tiêu chí riêng lẻ, việc nhân với 0 sẽ đảm bảo rằng chỉ các mục đáp ứng tất cả các tiêu chí mới có số 1 trong mảng tra cứu cuối cùng (VÀ logic). Mặt khác, việc sử dụng phép cộng đảm bảo rằng các mục đáp ứng bất kỳ tiêu chí đơn lẻ nào đều được biểu thị bằng 1 (OR logic). Kết quả là, một công thức XLOOKUP có giá trị tra cứu được đặt thành 1 sẽ tìm nạp giá trị mà bất kỳ điều kiện nào đều đúng một cách hiệu quả.
Ví dụ: để truy xuất bản ghi đầu tiên trong tập dữ liệu bên dưới có vùng là G4 hoặc I4, công thức là:
=XLOOKUP(1, (B3:B22=G4) + (B3:B22=I4), A3:D22)
Khi xử lý một số tiêu chí OR trong một cột, kết quả kiểm tra rất rõ ràng - chỉ một kiểm tra có thể trả về TRUE. Sự đơn giản này cho phép cộng các phần tử của mảng kết quả, tạo ra mảng cuối cùng chỉ có 0 (không có tiêu chí nào đúng) và 1 (một trong các tiêu chí là đúng), căn chỉnh hoàn hảo với giá trị tra cứu 1.
Tuy nhiên, khi kiểm tra nhiều cột, mọi thứ trở nên phức tạp hơn. Các thử nghiệm không loại trừ lẫn nhau vì nhiều cột có thể đáp ứng các tiêu chí, dẫn đến nhiều thử nghiệm logic trả về TRUE. Do đó, mảng cuối cùng có thể chứa các giá trị lớn hơn 1.
Để giải quyết vấn đề này, hãy điều chỉnh công thức như sau:
XLOOKUP(1, --(( lookup_array1 = lookup_value1 ) + ( lookup_array2 = lookup_value2 ) + (…) > 0), return_array )
Trong quá trình điều chỉnh này, bạn cộng các mảng trung gian rồi kiểm tra xem các giá trị trong mảng kết quả có lớn hơn 0 hay không. Điều này mang lại cho chúng ta một mảng mới chỉ bao gồm các giá trị TRUE và FALSE. Phủ định kép (--) thay đổi các TRUE và FALSE này thành 1 và 0, đảm bảo giá trị tra cứu 1 của chúng ta vẫn thực hiện công việc của nó một cách suôn sẻ.
Ví dụ: để tìm nạp bản ghi đầu tiên từ A3:B22 có “Có” ở cột C hoặc D hoặc ở cả hai cột, bạn có thể sử dụng công thức như sau:
=XLOOKUP(1, --((C3:C22 = "Yes") + (D3:D22 = "Yes") >0), A3:B22)
Đương nhiên, bạn có thể tự do điều chỉnh logic khi cần để nhắm mục tiêu dữ liệu mong muốn của mình.
Trong những trường hợp phức tạp hơn, bạn có thể cần kết hợp logic AND và OR. Ví dụ: để biết nhà cung cấp cho mặt hàng trong G4 và khu vực trong G5 hoặc I5, hãy sử dụng công thức sau:
=XLOOKUP(1, (A3:A22=G4) * ((B3:B22=G5) + (B3:B22=I5)), D3:D22)
Ở đâu:
(A3:A22=G4) kiểm tra xem mục trong phạm vi tra cứu có khớp với tên mục đích trong ô G4 hay không.
((B3:B22=G5) + (B3:B22=I5)) thực hiện logic OR bằng cách kiểm tra xem vùng đó là G5 hay I5.
(A3:A22=G4) * ((B3:B22=G5) + (B3:B22=I5)) triển khai logic AND cho tên mục và khu vực.
D3:D22 trả về nhà cung cấp tương ứng trong phạm vi này.
Công thức tổng thể xác định thành công kết quả khớp đầu tiên trong đó đáp ứng cả tiêu chí mục và khu vực, áp dụng logic AND và OR cho các tiêu chí khác nhau.
Sử dụng XLOOKUP với nhiều tiêu chí mang lại cả ưu điểm và hạn chế đáng để xem xét.
Lợi ích của nhiều tiêu chí XLOOKUP là:
Tìm chi tiết cụ thể một cách dễ dàng. Với XLOOKUP, việc tìm chính xác những gì bạn đang tìm kiếm trong dữ liệu của mình sẽ dễ dàng hơn, đặc biệt khi bạn có nhiều điều kiện. Điều này có nghĩa là bạn có thể rất cụ thể về thông tin bạn muốn truy xuất.
Linh hoạt về tiêu chí. Bạn có thể sử dụng bao nhiêu điều kiện tùy thích. Chỉ cần đảm bảo rằng tất cả các mảng tra cứu đều có cùng kích thước.
Mảng động. Bạn có thể sử dụng XLOOKUP với mảng động, điều đó có nghĩa là bạn có thể tràn kết quả ra nhiều ô mà không cần sử dụng kiểu cũ Ctrl + Shift + Enter các công thức mảng.
Dễ hiểu. Công thức XLOOKUP được viết theo cách dễ đọc và dễ hiểu. Điều này không chỉ hữu ích cho bạn mà còn cho những người khác có thể làm việc với trang tính Excel của bạn.
Những hạn chế của XLOOKUP với nhiều tiêu chí là:
Kết hợp tiêu chí độc đáo. Bạn cần có sự kết hợp duy nhất các điều kiện cho các giá trị tra cứu của mình, nếu không XLOOKUP sẽ trả về lỗi hoặc kết quả khớp đầu tiên.
Kích thước mảng nhất quán. Mảng tra cứu và trả về phải có cùng số hàng hoặc số cột. Kích thước không khớp sẽ gây ra lỗi.
Tóm lại, bạn có thể làm được những điều tuyệt vời với việc phân tích dữ liệu bằng cách sử dụng hàm XLOOKUP của Excel với nhiều tiêu chí. Nó cho phép bạn tìm kiếm chính xác những gì bạn cần, giúp công việc của bạn có tổ chức và hiệu quả hơn. Điều quan trọng là đảm bảo rằng mỗi mục bạn muốn tìm có sự kết hợp tiêu chí duy nhất và tất cả dữ liệu bạn đang tìm kiếm đều được sắp xếp nhất quán. Vì vậy, hãy tiếp tục, thử những mẹo này và làm cho các tác vụ Excel của bạn hiệu quả và thú vị hơn!
