Obecný vzorec
=XLOOKUP(max,dates,results,,-1) // latest match by date
souhrn
Chcete-li získat nejnovější shodu v sadě dat podle data, můžete použít XLOOKUP v režimu přibližné shody nastavením match_mode na -1. V zobrazeném příkladu je vzorec v G5, zkopírovaný dolů, následující:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
kde datum (C5: C15), položka (B5: B15) a cena (D5: D15) jsou pojmenovány rozsahy.
Vysvětlení
XLOOKUP nabízí několik funkcí, díky nimž je výjimečně vhodný pro složitější vyhledávání. V tomto příkladu chceme nejnovější cenu položky podle data. Pokud by data byla seřazena podle data vzestupně, bylo by to velmi jednoduché. V tomto případě však nejsou data roztříděna.
Ve výchozím nastavení vrátí XLOOKUP první shodu v datové sadě. Abychom získali poslední shodu, můžeme nastavit volitelný argument search_mode na hodnotu -1, což způsobí, že XLOOKUP vyhledá „od prvního k prvnímu“. Zde však nemůžeme použít tento přístup, protože neexistuje žádná záruka, že se poslední cena položky zobrazí jako poslední.
Místo toho můžeme nastavit volitelný argument match_mode na -1, aby vynutil přibližnou shodu „přesné nebo další nejmenší“, a upravit vyhledávací hodnotu a vyhledávací pole, jak je vysvětleno níže. Zkopírovaný vzorec v G5 je:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Při procházení argumenty jeden po druhém je lookup_value největší (nejnovější) datum v datech:
MAX(date) // get max date value
Lookup_array je odvozen od logického logického výrazu:
(item=F5)*date
Porovnáním každé položky s hodnotou v F5 „Pás“ získáme pole hodnot PRAVDA / NEPRAVDA:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
kde PRAVÉ hodnoty představují položky pro „Pás“. Toto pole funguje jako filtr. Když se vynásobí hodnotami v pojmenovaném datu rozsahu , hodnoty TRUE / FALSE se vyhodnotí na 1 a 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Výsledkem je pole, které obsahuje pouze nuly a data pro pásy:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Poznámka: sériová čísla jsou platná data aplikace Excel.
Toto pole je dodáváno přímo do XLOOKUP jako argument lookup_array.
Return_array je pojmenovaná cena rozsahu (D5: D15)
Nepovinný argument not_found není poskytnut.
Match_mode je nastaven na -1 pro přesnou shodu nebo další nejmenší položku.
XLOOKUP prohledá vyhledávací pole pro maximální hodnotu data. Protože pole již bylo filtrováno, aby vyloučilo data, která nejsou spojena s „pásem“, XLOOKUP jednoduše najde nejlepší shodu (buď přesné datum, nebo další nejmenší datum), které odpovídá nejnovějšímu datu.
Konečným výsledkem je cena spojená s posledním datem. Vzorec bude i nadále fungovat, když budou data tříděna v libovolném pořadí.
