Vaadake videot - kuidas kasutada funktsiooni VLOOKUP mitme kriteeriumiga
Funktsioon Excel VLOOKUP saab oma põhivormil otsida ühe otsinguväärtuse ja tagastada vastava väärtuse määratud realt.
Kuid sageli on vaja kasutada Exceli VLOOKUP -i mitme kriteeriumiga.
VLOOKUPi kasutamine mitme kriteeriumiga
Oletame, et teil on andmed õpilaste nime, eksami tüübi ja matemaatika skooriga (nagu allpool näidatud):
Funktsiooni VLOOKUP kasutamine iga õpilase matemaatika skoori saamiseks vastavate eksamitasemete jaoks võib olla väljakutse.
Võib väita, et parem variant oleks andmekogumi ümberkorraldamine või Pivot -tabeli kasutamine. Kui see teie jaoks töötab, pole midagi sellist. Kuid paljudel juhtudel olete oma andmetega ummikus ja liigendtabel ei pruugi olla valik.
Sellistel juhtudel on see õpetus teie jaoks.
Nüüd on otsinguväärtuse saamiseks mitmel kriteeriumil VLOOKUP abil kaks võimalust.
- Abistajaveeru kasutamine.
- Funktsiooni CHOOSE kasutamine.
VLOOKUP mitme kriteeriumiga - abistajaveeru kasutamine
Olen Exceli abiveergude fänn.
Minu arvates on abistajate veergude kasutamisel massiivivalemite ees kaks olulist eelist:
- See hõlbustab töölehel toimuva mõistmist.
- See muudab massiivifunktsioonidega võrreldes kiiremaks (märgatav suurtes andmekogumites).
Nüüd ärge saage minust valesti aru. Ma ei ole massiivivalemite vastu. Ma armastan hämmastavaid asju, mida saab teha massiivivalemitega. Lihtsalt salvestan need erilistel puhkudel, kui kõik muud võimalused ei aita.
Tulles tagasi küsimuse juurde, on abistajate veergu vaja unikaalse kvalifikatsiooni loomiseks. Seda ainulaadset kvalifikaatorit saab seejärel kasutada õige väärtuse otsimiseks. Näiteks on andmetes kolm Matti, kuid Matt ja Unit Test või Matt ja Mid-Term on ainult üks kombinatsioon.
Siin on sammud.
- Sisestage abiveerg veergude B ja C vahele.
- Kasutage abistajaveerus järgmist valemit: = A2 & ”|” & B2
- See looks igale eksemplarile ainulaadsed täpsustajad, nagu allpool näidatud.
- See looks igale eksemplarile ainulaadsed täpsustajad, nagu allpool näidatud.
- Kasutage G3 -s järgmist valemit: VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Kopeeri kõigi lahtrite jaoks.
Kuidas see töötab?
Loome ainulaadsed kvalifikaatorid iga nime ja eksami jaoks. Siin kasutatud funktsioonis VLOOKUP muudeti otsinguväärtuseks $ F3 & ”|” & G $ 2, nii et mõlemad otsingukriteeriumid on ühendatud ja neid kasutatakse ühe otsinguväärtusena. Näiteks G2 funktsiooni VLOOKUP otsinguväärtus on Matt | Unit Test. Nüüd kasutatakse seda otsinguväärtust C2: D19 skoori saamiseks.
Täpsustused:
Tõenäoliselt tuleb teile meelde paar küsimust, seega mõtlesin, et proovin sellele vastata:
- Miks ma kasutasin | sümbol kahe kriteeriumi ühendamisel? - Mõningatel erakordselt harvadel (kuid võimalikel) tingimustel võib teil olla kaks kriteeriumi, mis on erinevad, kuid annavad kokkuvõttes sama tulemuse. Siin on väga lihtne näide (andestage mulle, et siin pole loovust):
Pange tähele, et kuigi A2 ja A3 on erinevad ning B2 ja B3 on erinevad, on kombinatsioonid lõpuks samad. Aga kui kasutate eraldajat, oleks isegi kombinatsioon erinev (D2 ja D3).
- Miks ma sisestasin abistaja veeru veergude B ja C vahele, mitte äärmusvasakusse? - Abistaja veeru sisestamine äärmusesse vasakule ei kahjusta. Tegelikult, kui te ei soovi algseid andmeid karastada, peaks see olema õige tee. Ma tegin seda, kuna see paneb mind kasutama funktsiooni VLOOKUP vähem lahtreid. Selle asemel, et tabelimassiivis oleks 4 veergu, saaksin hakkama ainult kahe veeruga. Aga see olen ainult mina.
Nüüd pole ühtegi sobivat suurust. Mõned inimesed võivad eelistada mitme kriteeriumiga VLOOKUPi kasutamisel mitte ühtegi abiveergu.
Nii et siin on teie jaoks mitte-abistaja veeru meetod.
Laadige alla näidisfail
VLOOKUP mitme kriteeriumiga - funktsiooni CHOOSE kasutamine
Massiivivalemite kasutamine abistavate veergude asemel säästab töölehe kinnisvara ja jõudlus võib olla sama hea, kui seda kasutatakse töövihikus vähem.
Arvestades sama andmekogumit, mida kasutati ülalpool, on siin valem, mis annab teile tulemuse:
= VLOOKUP ($ E3 & ”|” & F $ 2, CHOOSE ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
Kuna see on massiivivalem, kasutage seda klahvi Control + Shift + Enter asemel lihtsalt sisestusklahvi.
Kuidas see töötab?
Valemis kasutatakse ka abistajaveeru mõistet. Erinevus seisneb selles, et selle asemel, et töölehele abistaja veergu panna, arvestage seda virtuaalsete abistajaandmetega, mis on valemi osa.
Näitan teile, mida ma mõtlen virtuaalsete abistajate andmete all.
Ülaltoodud joonisel, kui valin valemi osa VALI ja vajutan klahvi F9, näitab see tulemust, mille valem VALI annaks.
Tulemuseks on {„Matt | Unit Test”, 91; „Bob | Unit Test”, 52;…}
See on massiiv, kus koma tähistab sama rea järgmist lahtrit ja semikoolon tähistab seda, et järgmises veerus on järgmised andmed. Seega loob see valem 2 andmete veergu - ühel veerul on kordumatu identifikaator ja ühel on skoor.
Nüüd, kui kasutate funktsiooni VLOOKUP, otsib see lihtsalt väärtuse (selle virtuaalse 2 veeru andmete) esimesest veerust ja tagastab vastava skoori.
Laadige alla näidisfail
Mitme kriteeriumi (nt INDEX/MATCH või SUMPRODUCT) otsimiseks võite kasutada ka teisi valemeid.
Kas on veel mõni viis, kuidas seda teha? Kui jah, siis jagage minuga kommentaaride jaotises.
Samuti võivad teile meeldida järgmised LOOKUP -i õpetused:
- VLOOKUP vs. INDEX/MATCH
- Hankige mitu otsinguväärtust ilma korduseta ühes lahtris.
- Kuidas muuta VLOOKUP tõstutundlikuks.
- Kasutage IFERRORi koos VLOOKUPiga, et vabaneda #N/A vigadest.
