Arvojen Haku
Pyyhkäise näyttääksesi valikon
Haku-funktiot ratkaisevat yhden taulukkolaskennan yleisimmistä ongelmista: sinulla on dataa yhdessä paikassa ja tarvitset siihen 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 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. Tätä 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)
Likimääräinen haku vaatii, että hakutaulukko on lajiteltu nousevaan järjestykseen. Lajittelematon taulukko tuottaa vääriä tuloksia ilman varoitusta.
VLOOKUP
VLOOKUP löytyy yhä miljoonista taulukoista ja siihen törmää usein. Sen keskeinen rajoitus on rakenteellinen: hakuarvon on aina oltava taulukon ensimmäisessä sarakkeessa, ja funktio palauttaa arvon sarakeindeksin perusteella. Jos lisäät sarakkeen haku- 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, selkeämpi ja käsittelee likimääräiset haut yksiselitteisemmin.
Tehtävä
-
Hae kuukausittainen budjetti kategorian mukaan
Luo viitettaulukko Yhteenveto-osion alapuolelle sarakkeilla Category ja Monthly Budget.
Syötä seuraavat arvot:- Rent — 1,500.00
- Groceries — 400.00
- Utilities — 250.00
- Transport — 200.00
- Dining — 150.00
Muotoile nämä arvot valuuttamuotoon.
Hae budjetti jokaiselle riville Menot-taulukossa käyttämällä:
=XLOOKUP(B9;I12:I20;J12:J20)
Tämä palauttaa budjetin, joka vastaa kutakin kategoriaa.
- Käsittele puuttuvat kategoriat
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 yhdistelmät.
- Lukitse viitettaulukko
Lukitse viitealueet painamalla F4, jotta ne eivät siirry kaavaa kopioitaessa.
- Käytä likimääräistä vastaavuutta alennustasoille
Luo uusi taulukko sarakkeilla Spending thresholds ja Discount values.
Syötä seuraavat arvot:
- 0 — 0
- 500 — 5
- 1000 — 10
- 2000 — 20
Muotoile alennusarvot prosentteina.
Laske sitten alennustaso 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
