Santrauka
Norėdami ieškoti duomenų pagal kelis sudėtingus kriterijus, galite naudoti funkciją XLOOKUP su daugybe išraiškų, pagrįstų logine logika. Parodytame pavyzdyje formulė G5 yra:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
Naudojant „XLOOKUP“ numatytuosius atitikties režimo (tikslaus) ir paieškos režimo (nuo pirmo iki paskutinio) nustatymus, formulė atitinka pirmąjį įrašą, kuriame:
sąskaita prasideda „x“ IR regionas yra „rytai“, o mėnuo NE balandžio mėn.
kuris yra ketvirtas parodytas pavyzdys (8 eilutė).
Paaiškinimas
Paprastai funkcija XLOOKUP yra sukonfigūruota taip, kad ieškotų vertės darbalapyje esančiame paieškos masyve. Tačiau kai kriterijai, naudojami reikšmei suderinti, tampa sudėtingesni, galite naudoti loginę logiką kurdami tiesioginį paieškos masyvą, susidedantį tik iš 1s ir 0s, tada ieškokite reikšmės 1. Šis metodas naudojamas šiame pavyzdyje:
=XLOOKUP(1,boolean_array,result_array)
Šiame pavyzdyje būtini kriterijai yra šie:
sąskaita prasideda „x“ IR regionas yra „rytai“, o mėnuo NE balandžio mėn.
Kiekvienam iš trijų aukščiau išvardytų kriterijų naudojame atskirą loginę išraišką. Pirmoji išraiška naudoja LEFT funkciją, kad patikrintų, ar paskyra prasideda „x“:
LEFT(B5:B16)="x" // account begins with "x"
Kadangi tikriname dvylika reikšmių, rezultatas yra masyvas su dvylika tokių reikšmių:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Antroji išraiška tikrina, ar regionas yra „rytai“, naudodamas operatorių „lygu“ (=):
C5:C16="east" // region is east
Kaip ir anksčiau, gauname kitą masyvą su dvylika TRUE FALSE reikšmių:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Trečiajame posakyje reikia neįtraukti balandžio mėnesio. Lengviausias būdas tai padaryti yra išbandyti balandžio mėnesį tiesiogiai naudojant funkciją MĖNESIS:
MONTH(D5:D16)=4 // month is April
Tada naudokite NOT funkciją, kad pakeistumėte rezultatą:
NOT(MONTH(D5:D16)=4) // month is not April
sukuria masyvą, teisingai apibūdinantį „ne balandį“:
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Tada visi trys masyvai padauginami kartu, o matematikos operacija PATIKIMOSIOS ir NETIESOS reikšmės priverčia 1s ir 0s:
(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)
Būlio aritmetikoje daugyba veikia kaip loginė funkcija AND, todėl galutinis rezultatas yra vienas toks masyvas:
(0;0;0;1;1;0;0;0;0;1;0;1)
Dabar formulę galima perrašyti taip:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
Kai paieškos reikšmė yra 1 ir numatytieji atitikties režimo (tiksli) ir paieškos režimo (nuo pirmo iki paskutinio) nustatymai, XLOOKUP sutampa su pirmuoju 1 (ketvirta pozicija) ir grąžina atitinkamą rezultatų masyvo eilutę, kuri yra B8: E8.
Paskutinės rungtynės
Nustatę pasirinktinį paieškos režimo argumentą į -1, galite rasti „paskutinę atitiktį“ pagal tuos pačius kriterijus kaip šis:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
