Všeobecný vzorec
=XLOOKUP(max,dates,results,,-1) // latest match by date
Zhrnutie
Ak chcete získať najnovšiu zhodu v množine údajov podľa dátumu, môžete použiť XLOOKUP v režime približnej zhody nastavením match_mode na -1. V zobrazenom príklade je vzorec v G5, skopírovaný nadol,:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
kde dátum (C5: C15), položka (B5: B15) a cena (D5: D15) sú pomenované rozsahy.
Vysvetlenie
XLOOKUP ponúka niekoľko funkcií, vďaka ktorým je mimoriadne vhodný pre komplikovanejšie vyhľadávanie. V tomto príklade požadujeme najnovšiu cenu položky podľa dátumu. Keby boli dáta zoradené vzostupne podľa dátumu, bolo by to veľmi jednoduché. V takom prípade sú však údaje netriedené.
V predvolenom nastavení XLOOKUP vráti prvú zhodu v množine údajov. Ak chcete získať poslednú zhodu, môžeme nastaviť voliteľný argument search_mode na hodnotu -1, čo spôsobí, že XLOOKUP vyhľadá výraz „last to first“. Tento prístup tu však nemôžeme použiť, pretože neexistuje záruka, že sa posledná cena položky zobrazí ako posledná.
Namiesto toho môžeme nastaviť voliteľný argument match_mode na -1, aby sa vynútila približná zhoda „presná alebo nasledujúca najmenšia“, a upraviť vyhľadávaciu hodnotu a vyhľadávacie pole, ako je vysvetlené nižšie. Vzorec v G5, skopírovaný nadol, je:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Pri postupnom hľadaní argumentov je lookup_value najväčší (najnovší) dátum v dátach:
MAX(date) // get max date value
Pole lookup_array je odvodené od logického výrazu typu boolean:
(item=F5)*date
Porovnaním každej položky s hodnotou vo F5 „Pás“ získame pole hodnôt TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
kde PRAVÉ hodnoty predstavujú položky pre „opasok“. Toto pole funguje ako filter. Keď sa vynásobí hodnotami v pomenovanom dátume rozsahu , hodnoty TRUE / FALSE sa vyhodnotia na 1 a 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Výsledkom je pole, ktoré obsahuje iba nuly a dátumy pre opasky:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Poznámka: sériové čísla sú platné dátumy programu Excel.
Toto pole sa dodáva priamo do XLOOKUPU ako argument lookup_array.
Return_array je pomenovaná cena rozsahu (D5: D15)
Nepovinný argument not_found nie je uvedený.
Match_mode je nastavený na -1 pre presnú zhodu alebo nasledujúcu najmenšiu položku.
Program XLOOKUP vyhľadáva maximálnu hodnotu dátumu prostredníctvom vyhľadávacieho poľa. Pretože pole už bolo filtrované, aby sa vylúčili dátumy, ktoré nie sú spojené s pásom, XLOOKUP jednoducho nájde najlepšiu zhodu (buď presný dátum, alebo najbližší najmenší dátum), ktorý zodpovedá poslednému dátumu.
Konečným výsledkom je cena spojená s posledným dátumom. Vzorec bude naďalej fungovať, keď sa dáta zoradia v ľubovoľnom poradí.
