Katso video – VLOOKUP-toiminnon käyttäminen useiden kriteerien kanssa

Excel VLOOKUP -funktio voi perusmuodossaan etsiä yhden hakuarvon ja palauttaa vastaavan arvon määritetyltä riviltä.

Mutta usein on tarpeen käyttää Excel VLOOKUPia useilla ehdoilla.

Kuinka käyttää VLOOKUPia useilla ehdoilla

Oletetaan, että sinulla on tietoja, joissa on opiskelijoiden nimi, kokeen tyyppi ja matemaattiset pisteet (kuten alla on esitetty):

VLOOKUP-toiminnon käyttäminen matemaattisten pisteiden saamiseksi jokaiselle opiskelijalle kullekin tenttitasolle voi olla haaste.

Voidaan väittää että parempi vaihtoehto olisi rakentaa tietojoukko uudelleen tai käyttää pivot-taulukkoa. Jos se toimii sinulle, ei mitään sellaista. Mutta monissa tapauksissa olet jumissa omistamiesi tietojen kanssa ja kääntötaulukko ei välttämättä ole vaihtoehto.

Tällaisissa tapauksissa tämä opetusohjelma on tarkoitettu sinulle.

Nyt on kahdella tapaa saada hakuarvo käyttämällä VLOOKUPia, jossa on useita ehtoja.

• Apusarakkeen käyttäminen.
• CHOOSE-toiminnon käyttäminen.

VLOOKUP useilla ehdoilla – Helper-sarakkeen käyttö

Olen Excel-sarakkeiden fani.

Minusta on kaksi merkittävää etua auttajasarakkeiden käytöstä matriisikaavoihin nähden:

• Sen avulla on helppo ymmärtää, mitä laskentataulukossa tapahtuu.
• Se tekee siitä nopeamman kuin matriisitoiminnot (havaittavissa suurissa tietojoukoissa).

Älä nyt ymmärrä minua väärin. En ole matriisikaavoja vastaan. Rakastan hämmästyttäviä asioita, joita voidaan tehdä matriisikaavoilla. Tallennan ne vain erityistilaisuuksia varten, kun kaikista muista vaihtoehdoista ei ole apua.

Palataksemme kysymykseen, auttaja-sarake tarvitaan ainutlaatuisen karsinnan luomiseen. Tätä ainutlaatuista karsintaa voidaan sitten käyttää oikean arvon hakemiseen. Esimerkiksi tiedoissa on kolme Mattia, mutta Matt- ja Unit Test- tai Matt- ja Mid-Term-yhdistelmiä on vain yksi.

Tässä ovat vaiheet:

Kuinka tämä toimii?

Luomme yksilölliset karsinnat kullekin nimen ja kokeen esiintymälle. Tässä käytetyssä VLOOKUP-funktiossa hakuarvoksi muutettiin $ F3 & ”|” & G $ 2, jotta molemmat hakukriteerit ovat yhdistettyjä ja niitä käytetään yhtenä hakuarvona. Esimerkiksi V2-funktion hakuarvo G2: ssa on Matt | Unit Test. Nyt tätä hakuarvoa käytetään pisteiden saamiseen C2: D19: stä.

Selvennykset:

Sinulle todennäköisesti tulee pari kysymystä, joten ajattelin, että yrittää vastata siihen täällä:

• miksi olen käyttänyt | symboli liittyessään kahteen kriteeriin? – Joissakin poikkeuksellisen harvoissa (mutta mahdollisissa) olosuhteissa sinulla voi olla kaksi erilaista kriteeriä, mutta saat lopulta saman tuloksen yhdistettynä. Tässä on hyvin yksinkertainen esimerkki (anna anteeksi luovuuteni puute täällä):

Huomaa, että vaikka A2 ja A3 ovat erilaisia ja B2 ja B3 ovat erilaisia, yhdistelmät ovat samat. Mutta jos käytät erotinta, jopa yhdistelmä olisi erilainen (D2 ja D3).

• Miksi lisäsin auttajasarakkeen sarakkeiden B ja C väliin enkä vasempaan reunaan? – Apupylvään asettaminen vasemman reunaan ei ole haittaa. Itse asiassa, jos et halua hillitä alkuperäisiä tietoja, sen pitäisi olla oikea tapa edetä. Tein sen, koska se saa minut käyttämään vähemmän soluja VLOOKUP-toiminnossa. Sen sijaan, että taulukon taulukossa olisi 4 saraketta, voisin hallita vain kahdella sarakkeella. Mutta se olen vain minä.

Nyt ei ole yhtä kokoa, joka sopisi kaikille. Jotkut ihmiset saattavat haluta olla käyttämättä mitään auttaja saraketta käyttäessään VLOOKUPia useilla ehdoilla.

Joten tässä on sinulle ei-auttaja-sarakkeen menetelmä.

Lataa esimerkkitiedosto

VLOOKUP useilla ehdoilla – CHOOSE-toiminnon käyttäminen

Taulukko-kaavojen käyttäminen auttajan sijasta sarakkeet säästävät laskentataulukkoasi, ja suorituskyky voi olla yhtä hyvä, jos sitä käytetään vähemmän kertaa työkirjassa.

Kun otetaan huomioon sama tietojoukko kuin yllä, tässä on kaava, joka antaa sinulle tuloksen :

= VLOOKUP ($ E3 & ”|” & F $ 2, VALITSE ({1,2} , $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19 ), 2,0)

Koska tämä on matriisikaava, käytä sitä Control + Vaihto + Enter-näppäimillä pelkän Enterin sijaan.

Kuinka tämä toimii?

Kaavassa käytetään myös auttaja-sarakkeen käsitettä. Erona on, että sen sijaan, että laittaisit auttaja-sarakkeen laskentataulukkoon, pidä sitä virtuaalisena auttajana, joka on osa kaavaa.

Haluan näyttää, mitä tarkoitan virtuaalisten auttajien tiedoilla.

Kun valitsen yllä olevassa kuvassa kaavan VALITSE osan ja painan F9, se osoittaa tuloksen, että VALITSE kaava antaa.

Tulos on {”Matt | Yksikkötesti”, 91; ”Bob | Yksikkötesti”, 52; ……}

Se on taulukko, jossa pilkku edustaa saman solun seuraavaa solua ja puolipiste edustaa sitä, että seuraavat tiedot ovat seuraavassa sarakkeessa. Siksi tämä kaava luo kaksi tietosaraketta – yhdellä sarakkeella on yksilöllinen tunniste ja toisella pisteet.

Nyt , kun käytät VLOOKUP-toimintoa, se vain etsii arvon ensimmäisestä sarakkeesta (tämän virtuaalisen 2 sarakkeen datasta) ja palauttaa vastaavan pistemäärän.

Lataa Esimerkkitiedosto

Voit myös käyttää muita kaavoja etsimään useita ehtoja (kuten INDEX / MATCH tai SUMPRODUCT).