Bendroji formulė
=XLOOKUP(max,dates,results,,-1) // latest match by date
Santrauka
Norėdami gauti naujausią duomenų rinkinio atitiktį pagal datą, galite naudoti „XLOOKUP“ apytiksliame atitikties režime nustatydami „match_mode“ į -1. Parodytame pavyzdyje G5 formulė, nukopijuota žemiau, yra:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
kur data (C5: C15), prekė (B5: B15) ir kaina (D5: D15) yra įvardijami diapazonai.
Paaiškinimas
„XLOOKUP“ siūlo keletą funkcijų, dėl kurių jis ypač naudingas sudėtingesnėms paieškoms. Šiame pavyzdyje norime naujausios prekės kainos pagal datą. Jei duomenys būtų rūšiuojami pagal datą didėjimo tvarka, tai būtų labai paprasta. Tačiau šiuo atveju duomenys nerūšiuojami.
Pagal numatytuosius nustatymus „XLOOKUP“ grąžins pirmąją duomenų rinkinio atitiktį. Norėdami gauti paskutinę atitiktį, galime nustatyti pasirinktinį argumentą „search_mode“ į -1, kad XLOOKUP ieškotų „last to first“. Tačiau čia negalime naudoti šio požiūrio, nes nėra jokios garantijos, kad paskutinė prekės kaina bus paskutinė.
Vietoj to, mes galime nustatyti pasirinktinį argumentą „match_mode“ į -1, kad priverstume apytikslę „tiksli arba kita mažiausia“ atitiktį, ir pakoreguoti paieškos vertę ir paieškos masyvą, kaip paaiškinta toliau. G5 formulė, nukopijuota žemyn, yra tokia:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Tiriant argumentus po vieną, paieškos vertė yra didžiausia (paskutinė) data duomenyse:
MAX(date) // get max date value
„Lookup_array“ gaunamas su logine logine išraiška:
(item=F5)*date
Palyginę kiekvieną elementą su verte F5 „Diržas“, gauname TIKRŲ / NETIESIŲ reikšmių masyvą:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
kur TRUE reikšmės reiškia „diržo“ įrašus. Šis masyvas veikia kaip filtras. Padauginus iš nurodyto diapazono datos verčių, TRUE / FALSE reikšmės įvertinamos į 1 ir 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Rezultatas yra masyvas, kuriame yra tik nuliai ir diržų datos:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Pastaba: serijos numeriai yra galiojančios „Excel“ datos.
Šis masyvas pristatomas tiesiai į „XLOOKUP“ kaip argumentą „lookup_array“.
Return_array yra įvardyta diapazono kaina (D5: D15)
Nepateikiamas pasirenkamas argumentas not_found.
„Match_mode“ yra nustatytas į -1, jei tiksli atitiktis arba kitas mažiausias elementas.
XLOOKUP per paieškos masyvą ieško maksimalios datos vertės. Kadangi masyvas jau buvo filtruotas, kad būtų neįtrauktos datos, nesusijusios su „Diržu“, XLOOKUP paprasčiausiai randa geriausią atitikimą (tikslią datą arba kitą mažiausią datą), kuri atitinka paskutinę datą.
Galutinis rezultatas yra kaina, susieta su paskutine data. Formulė veiks ir toliau, kai duomenys bus rūšiuojami bet kokia tvarka.
