Viimeisen täsmäävän arvon etsiminen
Palauta viimeisin osuma käänteisen haun avulla
Viimeisen täsmäävän arvon etsiminen on ilmainen Excel Formulas Academy-oppitunti CoddyKitissä. Tämä on oppitunti 2/4. Voit lukea tästä oppimispolusta kokonaan mitkä tahansa 3 oppituntia ilmaiseksi — sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä käytännön harjoittelun sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. Oppitunti kuuluu Excel Formulas Academy-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. Excel Formulas Academy-kurssilla on yhteensä 4 oppituntia.
Viimeisen osuman ongelma
Useimmat haut palauttavat löytämänsä ensimmäisen osuman. Joskus tarvitaan kuitenkin viimeinen osuma: tuotteen viimeisin hinta, uusin tilapäivitys tai asiakkaan viimeinen merkintä.
Kun luettelo kasvaa ajan mittaan ja sama avain esiintyy useita kertoja, alimmalla rivillä oleva merkintä on yleensä tuorein. Tavallinen VLOOKUP tai tarkkaa hakua käyttävä MATCH poimii sen sijaan itsepintaisesti ylimmän rivin.
Tässä oppitunnissa esitellään useita luotettavia tapoja viimeisen osuman arvon hakemiseen.
Miksi tarkkaa hakua käyttävä MATCH löytää ensimmäisen osuman
MATCH(value, range, 0) käy alueen läpi ylhäältä alas ja pysähtyy ensimmäiseen täsmälliseen osumaan. Jos "Apple" esiintyy riveillä 2, 5 ja 9, MATCH palauttaa arvon 2.
Tämä toimii erinomaisesti, kun avaimet ovat yksilöllisiä, mutta uudemmat rivit jäävät huomiotta. Viimeisen esiintymän löytämiseksi tarvitaan tekniikka, joka hakee alhaalta ylöspäin tai palauttaa viimeisen osuman sijainnin.
=MATCH("Apple", A2:A10, 0)XLOOKUP käänteisellä haulla
Jos käytössänne on Excelin tai Google Sheetsin uudempi versio, XLOOKUP tekee tästä helppoa. Sen viides ja kuudes argumentti määrittävät hakutilan ja hakusuunnan.
Välittäkää hakutila-argumentiksi -1, jolloin haku tehdään viimeisestä ensimmäiseen. XLOOKUP palauttaa tällöin alimman vastaavan avaimen yhteydessä olevan arvon.
Tässä tuote haetaan solusta G1 alueelta A2:A10, ja vastaava hinta palautetaan alueelta B2:B10. Haku aloitetaan alhaalta.
=XLOOKUP(G1, A2:A10, B2:B10, "Not found", 0, -1)Perinteinen LOOKUP-temppu
Vanhemmissa taulukkolaskentaohjelmissa tunnettu temppu käyttää LOOKUP-funktiota luvun 2 ja näppärän ehdolla jakamisen kanssa.
Lauseke 1/(A2:A10=G1) tuottaa osuville riveille arvon 1 ja muille jakovirheen. Kun LOOKUP etsii arvoa 2, joka on suurempi kuin mikään alueella oleva arvo, se ohittaa virheet ja päätyy viimeiseen kelvolliseen ykköseen. Tällöin se palauttaa vastaavan arvon alueelta B2:B10.
=LOOKUP(2, 1/(A2:A10=G1), B2:B10)Miten LOOKUP-temppu toimii
Käydään läpi lauseke 1/(A2:A10=G1):
- Riveillä, joilla avain täsmää, saadaan
1/TRUE= 1. - Riveillä, joilla avain ei täsmää, saadaan
1/FALSE= #DIV/0!-virhe.
LOOKUP ohittaa virheet ja palauttaa kohdistetun tuloksen viimeisestä virheettömästä merkinnästä, kun se ei löydä kohdearvoaan (2). Koska kaikki osumat ovat ykkösiä, viimeinen ykkönen voittaa, joten saatte viimeisen osuman rivin arvon.
=LOOKUP(2, 1/(A2:A10=G1), B2:B10)Viimeinen osuma INDEX- ja MATCH-funktioilla
Voitte käyttää myös INDEX-MATCH-perheeseen kuuluvaa ratkaisua. Ideana on löytää viimeisen osuman sijainti ja välittää se sitten INDEX-funktiolle.
Käyttäkää samaa jakotemppua MATCH-funktion sisällä ja hakekaa arvoa 2 lausekkeesta 1/(A2:A10=G1), jolloin saatte viimeisen osuman rivisijainnin. Välittäkää tämä sijainti palautussaraketta käyttävälle INDEX-funktiolle.
=INDEX(B2:B10, MATCH(2, 1/(A2:A10=G1)))Miksi MATCH(2, ...) löytää viimeisen osuman
Kun MATCH-funktion kolmas argumentti jätetään pois, sen oletusarvo on 1, mikä tarkoittaa likimääräistä hakua nousevasti järjestetyistä tiedoista. MATCH etsii tällöin suurimman arvon, joka on enintään 2.
Taulukko 1/(A2:A10=G1) sisältää vain ykkösiä ja virheitä. Suurin arvo, joka on enintään 2, on 1, ja MATCH palauttaa tällaisen viimeisen ykkösen sijainnin. Tämä sijainti on täsmälleen viimeisen osuman rivi.
=MATCH(2, 1/(A2:A10=G1))Konkreettinen esimerkki
Oletetaan, että A2:A10 sisältää ajan mittaan tallennetut "Order-7"-tilauksen tilat ja B2:B10 sisältää tilatekstit. "Order-7" esiintyy riveillä 3, 6 ja 9.
- Osumataulukko merkitsee rivit 3, 6 ja 9 ykkösillä ja muut virheillä.
- MATCH(2, ...) palauttaa viimeisen osuman sijaintina arvon 9 (laskettuna alueen alusta).
- INDEX palauttaa lopullisen rivin tilan eli uusimman tilan.
=INDEX(B2:B10, MATCH(2, 1/(A2:A10=G1)))Oikean menetelmän valinta
Mitä lähestymistapaa kannattaa käyttää?
- XLOOKUP arvolla -1: selkein ja luettavin, jos sovelluksenne tukee sitä.
- LOOKUP(2, 1/...): toimii lähes kaikkialla eikä vaadi tiettyä versiota.
- INDEX-MATCH(2, 1/...): kätevä, kun tarvitsette myös sijainnin tai haluatte palauttaa arvon toisesta sarakkeesta.
Kaikki kolme palauttavat saman vastauksen. Valitkaa menetelmä työkalujenne ja kaavan halutun luettavuuden perusteella.
Yleiset sudenkuopat
Kiinnittäkää huomiota seuraaviin ongelmiin:
- Eri kokoiset alueet: ehtoalueen ja palautusalueen on oltava yhtä korkeita, jotta rivit kohdistuvat oikein.
- Piilotetut kaksoiskappaleet: loppuun jäävät välilyönnit voivat erottaa merkkijonot "Apple " ja "Apple" toisistaan. Siistikaa teksti ensin TRIM-funktiolla.
- Osumia ei ole: temppu palauttaa virheen, jos osumia ei löydy. Ystävällisen oletustuloksen saamiseksi kääritkää se
IFERROR-funktion sisään.
=IFERROR(LOOKUP(2, 1/(A2:A10=G1), B2:B10), "Not found")Viimeinen osuma useilla ehdoilla
Voitte yhdistää viimeisen osuman tempun kahteen ehtoon. Kertokaa ehdot jakolaskun sisällä, jolloin vain molemmat ehdot täyttävät rivit tuottavat ykkösen.
Voitte esimerkiksi etsiä viimeisimmän hinnan, jossa tuote vastaa solua G1 ja alue vastaa solua G2. LOOKUP(2, ...) -temppu päätyy edelleen viimeiselle ehdot täyttävälle riville.
Tämä on hyödyllistä aikaleimatuissa lokeissa, joissa sama tuote esiintyy useilla alueilla.
=LOOKUP(2, 1/((A2:A10=G1)*(B2:B10=G2)), C2:C10)Pikatesti
Testatkaa, miten hyvin hallitsette viimeisen osuman haut.
Oppitunnin kertaus
Jos haluatte palauttaa viimeisen osuman ensimmäisen sijaan:
- Käyttäkää tarvittaessa
XLOOKUP(..., -1)-funktiota hakuun alhaalta ylöspäin. - Käyttäkää perinteistä
LOOKUP(2, 1/(range=key), result)-temppua kaikissa versioissa. - Käyttäkää
INDEX(result, MATCH(2, 1/(range=key)))-ratkaisua, kun tarvitsette myös sijainnin.
Muistakaa pitää alueet samankokoisina, poistaa ylimääräiset välilyönnit ja kääriä kaava turvallisuuden vuoksi IFERROR-funktion sisään.
=XLOOKUP(G1, A2:A10, B2:B10, "Not found", 0, -1)
Opi Excel tekoälytuutorin avulla — ilmaiseksi
Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.
- Kurssit
- 30
- Oppitunnit
- 120
Usein kysytyt kysymykset
Onko oppitunti ”Viimeisen täsmäävän arvon etsiminen” ilmainen?
Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa Excel Formulas Academy-oppimispolun 3 oppituntia, myös oppitunnin “Viimeisen täsmäävän arvon etsiminen”. Sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä interaktiiviset harjoitukset sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. Excel Formulas Academy-kurssilla on yhteensä 4 oppituntia.
Mitä opin oppitunnilla ”Viimeisen täsmäävän arvon etsiminen”?
Palauta viimeisin osuma käänteisen haun avulla Harjoittelet Excel Formulas Academy-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.
Tarvitsenko kokemusta aloittaakseni Excel Formulas Academy-opiskelun?
Aiempi kokemus ei ole tarpeen. CoddyKitin Excel Formulas Academy-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 2/4.
Kuinka kauan ”Viimeisen täsmäävän arvon etsiminen”-oppitunnin suorittaminen kestää?
Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.
Voinko kirjoittaa ja suorittaa koodia tällä Excel Formulas Academy-oppitunnilla?
Kyllä. Jokainen Excel Formulas Academy-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.
Kaikki tämän kurssin oppitunnit
- Kaksiulotteiset haut INDEX-MATCH-MATCH-funktioilla
- Viimeisen täsmäävän arvon etsiminen
- Monikriteeriset haut INDEX-MATCH-funktioilla
- Likimääräinen haku porrastetuista taulukoista