Arvojen Haku
Pyyhkäise näyttääksesi valikon
Haku-funktiot ratkaisevat yhden taulukkolaskennan yleisimmistä ongelmista: sinulla on tietoja yhdessä paikassa ja tarvitset niihin liittyvää tietoa toisesta paikasta vastaavan arvon perusteella.
Annetaan funktiolle etsittävä arvo, määritetään mistä etsitään ja kerrotaan, mitä palautetaan, kun vastaavuus löytyy. Excel hoitaa haun automaattisesti jokaiselle riville aina, kun tiedot muuttuvat.
=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found])
Tarkka vastaavuus
Tarkka vastaavuus edellyttää, että hakuarvo vastaa täydellisesti jotakin viitetaulukon arvoa — tämä on XLOOKUP-funktion oletus.
Likimääräinen vastaavuus
Likimääräinen vastaavuus etsii lähimmän arvon, joka ei ylitä hakuarvoa, ja sitä käytetään esimerkiksi porrastetuissa järjestelmissä, kuten veroluokissa tai alennusrajoissa.
Tämä toiminta riippuu match_mode-asetuksesta. Yllä oleva kuvaus pätee vain, kun match_mode = -1, jolloin funktio palauttaa seuraavan pienemmän arvon, jos tarkkaa vastaavuutta ei löydy. Muilla match_mode-asetuksilla tulos määräytyy eri vastaavuussääntöjen mukaan.
XLOOKUP-funktiossa likimääräinen haku määritetään match_mode-argumentilla — tämä on neljäs valinnainen argumentti if_not_found-argumentin jälkeen. Kun asetat arvoksi -1, Excel hakee: "jos täsmällistä osumaa ei löydy, palauta seuraavaksi pienempi arvo."
=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; -1)
0 | -1 | 1 | 2 |
|---|---|---|---|
Exact match | Next smaller | Next larger | Wildcard |
Default. Returns error if value not found exactly. Use for text categories. | If no exact match, returns result for the largest value ≤ lookup value. Use for tiered systems. | If no exact match, returns result for the smallest value ≥ lookup value. | Allows * and ? as wildcards in the lookup value for partial text matching. |
Likimääräinen haku vaatii, että hakutaulukko on lajiteltu nousevaan järjestykseen. Lajittelematon taulukko tuottaa vääriä tuloksia ilman varoitusta.
VLOOKUP
VLOOKUP on yhä käytössä miljoonissa taulukoissa ja siihen törmää säännöllisesti. Sen keskeinen rajoitus on rakenteellinen: hakuarvon täytyy aina sijaita taulukon ensimmäisessä sarakkeessa, ja palautus tapahtuu sarakeindeksin perusteella. Jos lisäät sarakkeen hakusarakeen ja palautussarakkeen väliin, indeksinumero muuttuu vääräksi — VLOOKUP palauttaa väärän tiedon ilman varoitusta. XLOOKUP viittaa palautussarakkeeseen suoraan, joten se ei kärsi tästä ongelmasta.
=VLOOKUP(search_key; range; index; [is_sorted])
VLOOKUP toimii yhä ja siihen törmää perityissä tiedostoissa. Sinun täytyy osata lukea sitä. Kaikkiin itse kirjoittamiisi kaavoihin kannattaa kuitenkin käyttää XLOOKUP-funktiota — se on luotettavampi, helpompi lukea ja käsittelee likimääräiset haut selkeämmin.
-
Kuukausibudjetin hakeminen kategorian mukaan
Luo viitettaulukko Yhteenveto-osion alle sarakkeilla Kategoria ja Kuukausibudjetti.
Syötä seuraavat arvot:- Vuokra — 1 500,00
- Ruokaostokset — 400,00
- Sähkölaskut — 250,00
- Liikenne — 200,00
- Ravintolat — 150,00
Muotoile arvot valuuttana.
Hae budjetti jokaiselle riville Menot-taulukossa käyttämällä:
=XLOOKUP(B9;I12:I20;J12:J20)
Tämä palauttaa budjetin kullekin kategorialle.
- Puuttuvien kategorioiden käsittely
Päivitä kaava virheiden välttämiseksi, kun kategoriaa ei löydy:
=XLOOKUP(B9;I12:I20;J12:J20;"No budget defined")
Tämä pitää taulukon selkeänä ja korostaa puuttuvat vastineet.
- Hakutaulukon lukitseminen
Lukitse hakuvälit painamalla F4, jotta ne eivät siirry kaavaa kopioitaessa.
- Likimääräinen haku alennusportaissa
Luo uusi taulukko sarakkeilla Kulutusrajat ja Alennusarvot.
Syötä seuraavat arvot:
- 0 — 0
- 500 — 5
- 1000 — 10
- 2000 — 20
Muotoile alennusarvot prosentteina.
Laske sitten alennusporras jokaiselle menolle käyttämällä:
=XLOOKUP(D9;12:M16;N12:N16;"No tier";-1)
Kiitos palautteestasi!
Kysy tekoälyä
Kysy tekoälyä
Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme
