souhrn
Chcete-li vyhledat data na základě více komplexních kritérií, můžete použít funkci XLOOKUP s více výrazy založenými na logické logice. V zobrazeném příkladu je vzorec v G5:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
S výchozím nastavením XLOOKUP pro režim shody (přesné) a režim vyhledávání (od prvního do posledního) odpovídá vzorec prvnímu záznamu, kde:
účet začíná „x“ AND region je „východ“ a měsíc NENÍ duben.
což je čtvrtý záznam (řádek 8) v ukázaném příkladu.
Vysvětlení
Normálně je funkce XLOOKUP nakonfigurována tak, aby hledala hodnotu ve vyhledávacím poli, které existuje v listu. Když se však kritéria použitá k přiřazení hodnoty stanou složitějšími, můžete použít logickou logiku k vytvoření vyhledávacího pole za běhu složeného pouze z 1 s a 0 s, pak vyhledejte hodnotu 1. Toto je přístup použitý v tomto příkladu:
=XLOOKUP(1,boolean_array,result_array)
V tomto příkladu jsou požadovaná kritéria:
účet začíná „x“ AND region je „východ“ a měsíc NENÍ duben.
Pro každé ze tří samostatných kritérií výše používáme samostatný logický výraz. První výraz používá funkci LEFT k otestování, zda Účet začíná na „x“:
LEFT(B5:B16)="x" // account begins with "x"
Protože kontrolujeme dvanáct hodnot, výsledkem je pole s dvanácti hodnotami, jako je tato:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Druhý výraz testuje, zda je Region „východ“ pomocí operátoru rovná se (=):
C5:C16="east" // region is east
Stejně jako dříve získáme další pole s dvanácti hodnotami TRUE FALSE:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Třetí výraz musí vyloučit měsíc duben. Nejjednodušší způsob, jak to udělat, je testovat za měsíc duben přímo pomocí funkce MĚSÍC:
MONTH(D5:D16)=4 // month is April
Pak použijte funkci NOT k zvrácení výsledku:
NOT(MONTH(D5:D16)=4) // month is not April
který vytváří pole správně popisující „ne duben“:
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Dále se všechna tři pole násobí dohromady a matematická operace vynutí hodnoty TRUE a FALSE na 1 s a 0 s:
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
V booleovské aritmetice funguje násobení jako logická funkce AND, takže konečným výsledkem je jedno pole jako toto:
(0;0;0;1;1;0;0;0;0;1;0;1)
Vzorec lze nyní přepsat takto:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
S 1 jako vyhledávací hodnotou a výchozím nastavením pro režim shody (přesné) a režim vyhledávání (od prvního do posledního) odpovídá XLOOKUP první 1 (čtvrtá pozice) a vrátí odpovídající řádek v poli výsledků, což je B8: E8.
Poslední zápas
Nastavením volitelného argumentu režimu vyhledávání na -1 můžete vyhledat „poslední shodu“ se stejnými kritérii, jako je tato:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
