Kuinka etsiä useita arvoja Excelissä: vaihe vaiheelta

Kun käsittelet suuria tietojoukkoja Microsoft Excelissä, voi olla haastavaa löytää useita arvoja, jotka vastaavat useita ehtoja. Jotkut sisäänrakennetut toiminnot, kuten VLOOKUP, suunniteltiin alun perin toimimaan yksittäisten arvojen kanssa.

Useita voidaan yhdistää useiden arvojen etsimiseksi. Ne sisältävät VLOOKUP-, INDEX-, MATCH- ja IF-funktiot. Excelin nykyiset versiot voivat käyttää dynaamisia taulukoita, kun taas vanhemmat versiot käyttävät taulukkokaavoja.

Tässä artikkelissa kerrotaan, kuinka näitä funktioita käytetään kaavoissa, jotka löytävät tiedoistasi useita arvoja.

Mennään!

Sisällysluettelo

• Kuinka käyttää VLOOKUPia useilla arvoilla
  • Mitä Array-kaavat ovat?
  • VLOOKUP Esimerkki
  • Excelin uusimmat versiot
  • Vanhemmat versiot
  • Tarkka vastaavuus vs. likimääräinen vastaavuus
  • Lisätietoja VLOOKUPista
• Kuinka käyttää INDEXia ja MATCHia useiden arvojen etsimiseen
  • INDEXin ja MATCHin käyttäminen yhdessä Excel 365:ssä
  • Työskentely Excelin vanhempien versioiden kanssa
• Kuinka käyttää IF-funktiota useiden arvojen etsimiseen
  • Nykyiset Excel-versiot
  • Excelin vanhemmat versiot
• Kuinka käyttää SUMPRODUCTia useille kriteereille
• 3 yleistä virhettä hakutoiminnoissa
  • 1. #N/A Errors
  • 2. #REF! Virheet
  • 3. Pyöreät virheet
• IFERRORin käyttäminen hakutoimintojen kanssa
• 7 Kehittyneet hakutekniikat
  • 1. Suhteellisen sijainnin haku
  • 2. PIENI toiminto
  • 3. ROW-toiminto
  • 4. SUODATINtoiminto
  • 5. Apusarakkeet
  • 6. Dynaamiset taulukot
  • 7. Power Query
• Lopulliset ajatukset

Kuinka käyttää VLOOKUPia useilla arvoilla

Funktiota käytetään usein yksittäisten arvojen etsimiseen tietoalueelta. Voit kuitenkin myös etsiä useita osumia Excelissä tällä hakukaavalla.

Oletusarvoisesti se palauttaa vain ensimmäisen löytämänsä vastaavan arvon. Voit kuitenkin muokata funktiota palauttamaan useita arvoja käyttämällä taulukkokaavaa.

Mitä Array-kaavat ovat?

Taulukkokaava on kaava, joka voi suorittaa laskelmia tietotaulukoista. Sitä kutsutaan taulukkokaavaksi, koska se voi palauttaa joukon tuloksia yhden arvon sijaan.

Matriisikaavan luomisessa on useita helppoja vaiheita:

1. Valitse etsittävä solualue.

2. Syötä kaava kaavapalkkiin.

3. Viimeistele se painamalla Ctrl + Vaihto + Enter.

Taulukkokaavan syntaksi on samanlainen kuin tavallisen kaavan syntaksi, mutta se sisältää kaavan ympärillä kiharat aaltosulut {}. Aaltosulkeet osoittavat, että kaava on taulukkokaava ja että se palauttaa arvojoukon.

Tämän artikkelin esimerkit osoittavat, kuinka taulukkokaavoja käytetään oikein.

VLOOKUP Esimerkki

Esimerkissämme on viisi kohdetta laskentataulukon sarakkeessa A:

• Omena

• Banaani

• leipää

• Porkkana

• Kirsikka

Tehtävänä on tarkistaa, onko tässä luettelossa kolme tiettyä hedelmää: omena, banaani ja kirsikka.

Excelin uusimmat versiot

VLOOKUP-syntaksi riippuu käyttämästäsi Excel-versiosta.

Uusimmat versiot ( Excel 365 tai Excel 2021 ) tukevat dynaamisia taulukoita. Tämän ominaisuuden avulla kaavat voivat palauttaa useita tuloksia, jotka "vyöryvät" viereisiin soluihin.

Tämä on syntaksi (yhtäsuuruusmerkki aloittaa kaavan):

=VHAKU(hakuarvo, taulukon_taulukko, sarakkeen_indeksin_määrä, [alueen_haku])

• lookup_value: Arvo, jonka haluat etsiä.

• table_array: koko tietotaulukko, josta haluat hakea.

• col_index_num: Taulukon sarakkeen numero taulukossa, joka sisältää palautettavat tiedot.

• range_lookup: Valinnainen. Määrittää, haluatko tarkan vai likimääräisen vastaavuuden.

Erityinen esimerkkimme käyttää tätä kaavaa:

=VHAKU({"omena","banaani","kirsikka"},A1:A5,1,EPÄTOSI)

Kun kaava syötetään soluun B1, tulokset leviävät soluihin C1 ja D1. Tämä kuva näyttää esimerkin toiminnassa:

Vanhemmat versiot

Jos käytät vanhempaa Excel-versiota, joka ei tue dynaamisia taulukoita (esim. Excel 2019 tai vanhempi), sinun on käytettävä hieman erilaista lähestymistapaa taulukkokaavan kanssa.

Toimi seuraavasti:

1. Napsauta solua, jossa haluat näyttää ensimmäisen kohteen tulokset (esim. sarake B).

2. Kirjoita seuraava kaava painamatta vielä Enter: =VLOOKUP({"omena","banaani","kirsikka"}, A1:A5, 1, FALSE)

3. Paina Ctrl + Shift + Enter muuttaaksesi tämän taulukkokaavaksi.

4. Kopioi solu B1 ja liitä se alla olevaan soluun (tai käytä täyttökahvoja).

Kun käytät näppäinyhdistelmää Ctrl + Shift + Enter, Excel lisää kaavan ympärille kiharat aaltosulut. Tämä osoittaa, että se on taulukkokaava.

Tarkka vastaavuus vs. likimääräinen vastaavuus

Oletusarvoisesti VLOOKUP-funktio käyttää likimääräistä vastaavuutta. Tämä tarkoittaa, että se palauttaa lähimmän löytämänsä vastaavuuden, vaikka solujen arvot eivät täsmääkään.

Jos haluat suorittaa tarkan vastaavuuden, voit asettaa range_lookup-argumentin arvoon FALSE.

Muista, että likimääräiset vastaavuudet toimivat parhaiten järjestetyillä numeerisilla arvoilla. Se ei yleensä ole sopivaa, kun solun arvo on teksti.

Lisätietoja VLOOKUPista

Jos haluat oppia lisää tästä monipuolisesta toiminnosta, tutustu näihin artikkeleihin:

Nyt kun olet määrittänyt VLOOKUP-toiminnon, katsotaanpa kahta muuta toimintoa, jotka voivat tehdä sen eri tavalla: INDEX ja MATCH.

Kuinka käyttää INDEXia ja MATCHia useiden arvojen etsimiseen

Voit yhdistää INDEX- ja MATCH-funktiot yhteen löytääksesi useita arvoja useilta riveiltä.

Excelin INDEKSI-funktio palauttaa arvon tai viittauksen tietyllä alueella olevaan soluun.

=INDEKSI(taulukko, rivin_numero, [sarakkeen_numero])

• array: Solualue, josta arvoa etsitään.

• rivin_numero: taulukon rivin numero, josta arvo palautetaan.

• sarakkeen_numero: (Valinnainen) taulukon sarakkeen numero, josta arvo palautetaan. Jos jätetään pois, funktio palauttaa koko rivin.

MATCH-funktio Excelissä palauttaa arvon sijainnin määritetyllä alueella.

=MATCH(hakuarvo, hakutaulukko, [hakutyyppi])

• lookup_value: arvo, joka haetaan hakutaulukosta.

• lookup_array: Solualue, josta haetaan haun_arvoa.

• match_type: (Valinnainen) Suoritettavan vastaavuuden tyyppi. Jos se jätetään pois, toiminto suorittaa tarkan vastaavuuden.

INDEXin ja MATCHin käyttäminen yhdessä Excel 365:ssä

Jotta voit käyttää INDEXia ja MATCHia yhdessä useiden arvojen etsimiseen Excelissä, sinun on käytettävä taulukkokaavaa.

Tämä on Excel 365:n kaava aiempien esimerkkitietojen kanssa:

=INDEKSI(A1:A5, OSTO({"omena","banaani","kirsikka"}, A1:A5, 0))

Yllä oleva esimerkki hajoaa seuraavasti:

• INDEKSI: tämä palauttaa tietyn alueen solun arvon tietyn rivin ja sarakkeen numeron perusteella. Tässä tapauksessa se palauttaa arvon alueelta A1:A5.

• A1:A5: Tämä on määritetty taulukkoalue, josta haet arvoa ja josta tulos palautetaan.

• MATCH: tämä etsii tietyn kohteen solualueelta ja palauttaa kyseisen kohteen suhteellisen sijainnin alueella.

• {"omena","banaani","kirsikka"}: tämä on taulukkovakio, joka sisältää arvot, jotka haluat etsiä.

• A1:A5: tämä on alue, josta MATCH etsii arvoja taulukkovakiosta.

• 0: tämä on MATCH-toiminnon hakutyyppi. Tässä tapauksessa se on 0, mikä tarkoittaa, että etsit tarkkaa vastaavuutta läheisen vastaavuuden sijaan.

 Tämä kuva näyttää kaavan toiminnassa:

Työskentely Excelin vanhempien versioiden kanssa

Jos käytät vanhempaa Excel-tiedostoa, joka ei tue dynaamisia taulukoita (esim. Excel 2019 tai vanhempi), sinun on käytettävä erilaista lähestymistapaa.

Koska vanhemmat versiot eivät tue kaavojen "vuotoa" viereisiin soluihin, sinun on jaettava käyttö kolmeen erilliseen kaavaan.

Toimi seuraavasti:

1. Napsauta solua, jossa haluat ensimmäisen kohteen tuloksen (esim. solu B1)

2. Kirjoita alla oleva kaava:

3. =INDEKSI(A1:A5, MATCH("omena", A1:A5, 0))

4. Suorita kaava painamalla Enter.

5. Kirjoita tämä kaava soluun B2: =INDEKSI(A1:A5, MATCH("banaani", A1:A5, 0))

6. Kirjoita tämä kaava soluun B3: =INDEKSI(A1:A5, MATCH("kirsikka", A1:A5, 0))

Tässä kuvassa on soluviite B3:

INDEX- ja MATCH-funktiot eivät ole ainoita, joiden avulla voit löytää useita arvoja. Seuraavassa osiossa tarkastellaan, kuinka voit käyttää IF-toimintoa vaihtoehtona.

Kuinka käyttää IF-funktiota useiden arvojen etsimiseen

Toinen tapa etsiä useita soluarvoja tiettyjen kriteerien perusteella on käyttää IF-funktiota muiden funktioiden kanssa.

IF-funktion avulla voit testata useita ehtoja ja palauttaa erilaisia ​​tuloksia näiden testien tuloksista riippuen.

Oletetaan esimerkiksi, että sinulla on myyntitietojen taulukko, jossa on tuote- ja myyntisarakkeet. Haluat etsiä ja laskea yhteen myyntisumman kahdelle kolmesta tuotteesta.

Nykyiset Excel-versiot

Jos haluat löytää Myynti-sarakkeen summan, jossa tuote on joko "Apple" tai "Banaani" IF-funktiolla, voit käyttää taulukkokaavaa IF-, SUM- ja OR-funktioiden kanssa.

Oletetaan, että tietosi alkavat solusta A1, käytä seuraavaa kaavaa:

=SUMMA(JOS((A2:A4=”omena”)+(A2:A4=”banaani”), B2:B4, 0))

Osio (A2:A4=”Omena”)+(A2:A4=”Banaani”) luo taulukon, jonka arvo on 1, jos alueen A2:A4 solussa on ”omena” tai ”banaani” ja muussa tapauksessa 0 .

IF-käsky tarkistaa taulukkoargumentin jokaisen elementin. Jos arvo on 1 (eli tuote on joko "omena" tai "banaani"), se ottaa vastaavan arvon Myynti-sarakkeessa (alue B2:B4); muuten se kestää 0.

SUMMA-funktio laskee yhteen IF-funktion arvot ja summaa tehokkaasti sekä Applen että Banaanin myyntiarvot.

Tämä kuva näyttää kaavan toiminnassa hakualueella:

Excelin vanhemmat versiot

Excel 2019:ssä tai aiemmassa versiossa sinun on käytettävä taulukkokaavaa. Toimi seuraavasti:

1. Kirjoita kaava, mutta älä paina Enter-näppäintä.

2. Paina Ctrl + Vaihto + Enter tehdäksesi siitä taulukkokaavan.

Excel lisää kaavan ympärille hakasulkeet {}, mikä osoittaa, että se on taulukkokaava.

Seuraavaksi tarkastelemme, kuinka voit käyttää SUMPRODUCTia useiden arvojen etsimiseen kriteeriesi perusteella. Mennään!

Kuinka käyttää SUMPRODUCTia useille kriteereille

SUMPRODUCT-toiminnon avulla voit myös etsiä useita arvoja useiden kriteerien perusteella.

Koska se ei vaadi taulukkokaavan käyttöä, syntaksi on sama Excelin versiosta riippumatta.

Käyttämällä samoja tietoja kuin edellisessä esimerkissä, kaava näyttää tältä:

=SUMMATUOTE((A2:A4=”omena”)+(A2:A4=”banaani”), B2:B4)

Osio (A2:A4=”Omena”)+(A2:A4=”Banaani”) luo taulukon, jonka arvo on 1, jos alueen A2:A4 solussa on ”omena” tai ”banaani” ja muussa tapauksessa 0 .

SUMMA-funktio kertoo taulukon elementit vastaavilla Myynti-sarakkeen elementeillä (alue B2:B4). Sitten se laskee yhteen saadut arvot ja laskee tehokkaasti yhteen sekä Applen että Banaanin myyntiarvot.

Alla oleva kaava näyttää sen toiminnassa:

Excel-toiminnot ovat uskomattomia, kun ne toimivat odotetulla tavalla, mutta joskus saatat kohdata virheitä. Seuraavassa osiossa käsittelemme joitain yleisiä virheitä ja kuinka voit käsitellä niitä.

3 yleistä virhettä hakutoiminnoissa

Hakutoiminnot voivat joskus palauttaa virheitä, joiden vianmääritys voi olla turhauttavaa ja aikaa vievää. Kolme yleisintä virhettä, joita kohtaat, ovat:

1. #N/A Virheet

2. #VIITE! Virheet

3. Pyöreät virheet

1. #N/A Errors

#N/A -virhe ilmenee, kun hakuarvoa ei löydy hakutaulukosta.

On useita syitä, miksi tämä virhe voi tapahtua, mukaan lukien:

• hakuarvo on kirjoitettu väärin tai virheellinen.

• hakutaulukkoa ei ole lajiteltu nousevaan järjestykseen.

• hakuarvo ei ole tietojoukossa.

Kun hakuarvo ei ole tietojoukossa, tämä on hyödyllistä tietoa. Kokemattomat Excel-käyttäjät saattavat kuitenkin ajatella, että #N/A tarkoittaa, että kaavassa on jotain mennyt pieleen. Seuraava osio näyttää, kuinka voit tehdä tästä käyttäjäystävällisemmän.

2. #REF! Virheet

#REF! virhe tapahtuu, kun hakutaulukko tai paluutaulukko poistetaan tai siirretään.

Tämä virhe voidaan korjata päivittämällä soluviittaukset hakufunktiossa.

3. Pyöreät virheet

Kun yhdistät funktioita monimutkaisiin kaavoihin, Excel saattaa kertoa, että sinulla on pyöreä viittaus.

Näitä on helpompi tutkia käyttämällä opastamme .

IFERRORin käyttäminen hakutoimintojen kanssa

IFERROR-toiminto on hyödyllinen työkalu hakufunktioiden virheiden käsittelyyn. Sen avulla voit määrittää palautettavan arvon tai kaavan, jos hakufunktio palauttaa virheen.

IFERROR-funktion syntaksi on seuraava:

=IFERROR(arvo, arvo_jos_virhe)

· arvo: arvo tai kaava, jonka haluat arvioida.

· Arvo_jos_virhe: arvo tai kaava, joka palautetaan, jos ensimmäinen argumentti palauttaa virheen.

Oletetaan esimerkiksi, että sinulla on VLOOKUP-funktio, joka etsii useita arvoja taulukosta. Alla olevassa kuvassa yhtä arvoista ei ole haetulla tietoalueella.

Kuten näet, #N/A-virhe tulee näkyviin, mikä voi olla hämmentävää kokemattomille Excel-käyttäjille.

Sen sijaan voit käyttää IFERROR-komentoa näyttääksesi tyhjän solun tai viestin, jossa lukee "Ei löydy" tällä syntaksilla:

=IFERROR(VHAKU(hakuarvo, taulukon_taulukko, sarakkeen_indeksi, EPÄTOSI), "Ei löytynyt"

Jos tässä esimerkissä VHAKU-funktio palauttaa virheen, IFERROR-funktio palauttaa sen sijaan viestin "Ei löydy".

Tämä kuva näyttää kaavan toiminnassa. Sarakkeesta B puuttuu arvo, kun taas sarakkeessa C ja sarakkeessa D on löydetty osumat.

Olemme käsitelleet paljon asiaa tähän mennessä, ja olet vihdoin valmis oppimaan edistyneempiä hakutekniikoita, mikä on seuraavan osion aiheena.

7 Kehittyneet hakutekniikat

Useiden arvojen etsiminen Excelistä voi olla haastava tehtävä, varsinkin kun käsitellään suuria tietojoukkoja. Saatat kohdata suorituskykyongelmia hitaalla käsittelyllä.

On olemassa seitsemän kehittynyttä hakutekniikkaa, joiden avulla voit tehdä prosessista helpomman ja tehokkaamman.

• Suhteellisen sijainnin haku

• PIENI toiminto

• ROW-toiminto

• FILTER-toiminto

• Apusarakkeet

• Dynaamiset taulukot

• Power Query

1. Suhteellisen sijainnin haku

Yksi yksinkertaisimmista on käyttää suhteellisen sijainnin hakua. Tämä edellyttää rivien ja sarakkeiden siirtymien määrittämistä tietystä solusta halutun arvon löytämiseksi.

Jos sinulla on esimerkiksi tietotaulukko ja haluat etsiä arvon, joka on kaksi riviä alaspäin ja kolme saraketta tietyn solun oikealla puolella, voit käyttää seuraavaa kaavaa:

=SIIRTO(solu, 2, 3)

2. PIENI toiminto

Toinen hyödyllinen tekniikka usean arvon hakuun on SMALL-funktion käyttö. Tämä funktio palauttaa n:nneksi pienimmän arvon solualueella.

Yhdistämällä tämän toiminnon muihin hakutoimintoihin, kuten INDEX ja MATCH, voit etsiä useita arvoja tiettyjen kriteerien perusteella.

Esimerkiksi seuraava kaava etsii toiseksi pienimmän arvon solualueelta, joka täyttää tietyn ehdon:

=INDEKSI(tiedot, VASTAAVA(PIENI(JOS(ehdot, alue), 2), alue, 0))

3. ROW-toiminto

ROW-funktiota voidaan käyttää myös useiden arvojen hakuun Excelissä. Tämä funktio palauttaa tietyn solun rivinumeron, jota voidaan käyttää viittaamaan tietotaulukon soluihin.

Esimerkiksi seuraava kaava etsii arvon taulukosta yksilöllisen tunnisteen perusteella:

=INDEKSI(data, VASTUU(yksilöllinen_tunniste, data[yksilöllinen_tunniste], 0), sarakkeen_indeksinumero)

4. SUODATINtoiminto

SUODATIN-toiminto ei ole käytettävissä Excelin vanhemmissa versioissa.

Excel 365:ssä voit käyttää sitä solualueen suodattamiseen tiettyjen ehtojen perusteella ja palauttaa vain arvot, jotka täyttävät nämä ehdot. Tämä on syntaksi ja kolme argumenttia:

=SUODATIN(joukko, sisältää, [jos_tyhjä])

• array: Tietyt tiedot, jotka haluat suodattaa.

• include: Sisällytä-argumentti on kriteerit tai ehdot, joita haluat soveltaa taulukkoon.

• [if_empty] (valinnainen): Arvo, joka palautetaan, jos yksikään rivi tai sarake ei täytä sisällytysargumentissa määritettyjä ehtoja.

Esimerkiksi seuraava kaava toimii esimerkkitiedoissa löytääkseen vastaavuuksia kahdelle ensimmäisen sarakkeen kolmesta kohteesta ja summatakseen niitä vastaavat arvot toisessa sarakkeessa.

=SUMMA(SUODATIN(B2:B4, (A2:A4=”omena”)+(A2:A4=”Banaani”))))

Tämä kuva näyttää, kuinka monta riviä täsmäävät ja eri arvojen summat:

5. Apusarakkeet

Voit liittää useita kenttiä yhteen funktiossa, kuten VLOOKUP, käyttämällä apusaraketta.

Oletetaan, että käsittelet etu- ja sukunimiä erillisellä taulukolla. Voit ketjuttaa ne apusarakkeeseen, johon viitataan lopullisessa kaavassa.

6. Dynaamiset taulukot

Kuten olet oppinut aiemmista esimerkeistä, Microsoft 365:n käyttäjät voivat hyödyntää dynaamisia taulukoita useiden arvojen hakuun Excelissä.

Dynaamisten taulukoiden avulla voit palauttaa useita arvoja yhdestä kaavasta, mikä helpottaa suurten tietomäärien etsimistä.

7. Power Query

Power Query on tehokas Excelin työkalu, jonka avulla voidaan palauttaa arvoja useiden kriteerien perusteella.

Tämä video esimerkiksi löytää sotkuista dataa laskentataulukosta ja puhdistaa sen.

Lopulliset ajatukset

Tämä päättää syvällisen sukelluksemme useiden arvojen etsimiseen Excelissä! Kun olet hallinnut VLOOKUP-, INDEX-, MATCH- ja taulukkokaavat, löydät itsesi sekaisin monimutkaisten tietojoukkojen läpi kuin kuuma veitsi voita.

Tärkeintä on ymmärtää kunkin kaavan syntaksi ja logiikka. Muista, että nämä kaavat voivat olla monimutkaisia, joten on tärkeää käyttää aikaa ja testata kaavojasi perusteellisesti, ennen kuin luotat niihin tärkeässä data-analyysissä.

Excel on tehokas työkalu, mutta se on vain yhtä hyvä kuin käyttäjä. Jatka siis näiden taitojen hiomista, jatka kokeilua, niin sinusta tulee pian moniarvohakujen mestari. Seuraavaan kertaan asti, jatka näiden numeroiden murskaamista ja anna Excelin tehdä kova työ puolestasi!