Yleinen kaava
=XLOOKUP(max,dates,results,,-1) // latest match by date
Yhteenveto
Saadaksesi viimeisimmän tietojoukon päivämäärän mukaan, voit käyttää XLOOKUPia likimääräisessä ottelutilassa asettamalla match_mode arvoksi -1. Esitetyssä esimerkissä G5: n kaava, kopioitu alaspäin, on:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
missä päivämäärä (C5: C15), kohde (B5: B15) ja hinta (D5: D15) on nimetty alueiksi.
Selitys
XLOOKUP tarjoaa useita ominaisuuksia, jotka tekevät siitä poikkeuksellisen hyvän monimutkaisemmissa hauissa. Tässä esimerkissä haluamme viimeisimmän hinnan tuotteelle päivämäärän mukaan. Jos tiedot lajitellaan päivämäärän mukaan nousevassa järjestyksessä, se olisi hyvin suoraviivaista. Tässä tapauksessa tietoja ei kuitenkaan lajitella.
Oletuksena XLOOKUP palauttaa tietojoukon ensimmäisen vastaavuuden. Viimeisen ottelun saamiseksi voimme asettaa valinnaisen argumentin search_mode arvoksi -1, jotta XLOOKUP hakee "last to first". Emme kuitenkaan voi käyttää tätä lähestymistapaa täällä, koska ei ole takeita siitä, että tuotteen viimeisin hinta näkyy viimeisenä.
Sen sijaan voimme asettaa valinnaisen argumentin match_mode arvoksi -1 pakottaaksesi likimääräisen "täsmällisen tai seuraavaksi pienimmän" vastaavuuden ja säätämään hakuarvoa ja hakutaulukkoa alla kuvatulla tavalla. G5: n kaava kopioituna alaspäin on:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Työskentelemällä argumentteja yksitellen, hakuarvo on datan suurin (viimeisin) päivämäärä:
MAX(date) // get max date value
Hakupyyntö johdetaan loogisella loogisella lausekkeella:
(item=F5)*date
Vertaamalla kutakin kohdetta F5: n "Vyö" -arvoon saadaan joukko TOSI / EPÄTOSI-arvoja:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
jossa TOSI-arvot edustavat "Vyö" -merkintöjä. Tämä taulukko toimii kuin suodatin. Kun se kerrotaan nimetyn alueen päivämäärän arvoilla , TOSI / EPÄTOSI-arvot arvioidaan 1- ja 0-arvoiksi:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Tuloksena on taulukko, joka sisältää vain nollat ja päivämäärät vyöille:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Huomaa: sarjanumerot ovat kelvollisia Excel-päivämääriä.
Tämä taulukko toimitetaan suoraan XLOOKUP: lle argumenttina lookup_array.
Return_array on nimetty alue hinta (D5: D15)
Valinnaista argumenttia not_found ei anneta.
Match_mode on asetettu arvoon -1 tarkan haun tai seuraavaksi pienimmän kohteen kohdalla.
XLOOKUP etsii hakuryhmästä enimmäispäivämäärän. Koska taulukko on jo suodatettu sulkemaan pois päivämäärät, jotka eivät liity "Vyöhön", XLOOKUP löytää vain parhaan vastaavuuden (joko tarkka päivämäärä tai seuraava pienin päivämäärä), joka vastaa viimeisintä päivämäärää.
Lopputulos on viimeisimpään päivämäärään liittyvä hinta. Kaava toimii edelleen, kun tiedot lajitellaan missä tahansa järjestyksessä.
