✅ Kaip naudoti „VLOOKUP“ su keliais kriterijais „Excel“ | „Excel VBA“ patarimai ir gudrybės 2025

Turinys

Kaip naudoti VLOOKUP su keliais kriterijais

Žiūrėti vaizdo įrašą - kaip naudotis VLOOKUP funkcija su keliais kriterijais

Funkcija „Excel VLOOKUP“ savo pagrindine forma gali ieškoti vienos paieškos vertės ir grąžinti atitinkamą reikšmę iš nurodytos eilutės.

Tačiau dažnai reikia naudoti „Excel VLOOKUP“ su keliais kriterijais.

Kaip naudoti VLOOKUP su keliais kriterijais

Tarkime, kad turite duomenų su mokinių vardu, egzamino tipu ir matematikos balais (kaip parodyta žemiau):

Naudoti funkciją VLOOKUP, norint gauti kiekvieno studento matematikos balą atitinkamiems egzaminų lygiams, gali būti iššūkis.

Galima teigti, kad geresnis pasirinkimas būtų pertvarkyti duomenų rinkinį arba naudoti „Pivot Table“. Jei tai jums tinka, nieko panašaus. Tačiau daugeliu atvejų esate įstrigę turimuose duomenyse, o suvestinė lentelė gali būti netinkama.

Tokiais atvejais ši pamoka skirta jums.

Dabar yra du būdai, kaip gauti paieškos vertę naudojant VLOOKUP su keliais kriterijais.

Pagalbos stulpelio naudojimas.
Funkcijos CHOOSE naudojimas.

VLOOKUP su keliais kriterijais - naudojant pagalbinę stulpelį

Aš esu „Excel“ pagalbinių stulpelių gerbėjas.

Manau, kad du svarbūs pagalbinių stulpelių naudojimo pranašumai prieš masyvo formules:

Tai leidžia lengvai suprasti, kas vyksta darbalapyje.
Tai pagreitina, palyginti su masyvo funkcijomis (pastebima dideliuose duomenų rinkiniuose).

Dabar nesupraskite manęs neteisingai. Aš nesu prieš masyvo formules. Man patinka nuostabūs dalykai, kuriuos galima padaryti naudojant masyvo formules. Tiesiog aš juos išsaugoju ypatingoms progoms, kai visos kitos galimybės nepadeda.

Grįžtant prie esamo klausimo, pagalbinė skiltis reikalinga norint sukurti unikalų kvalifikaciją. Šis unikalus kvalifikatorius gali būti naudojamas norint surasti teisingą vertę. Pavyzdžiui, duomenyse yra trys „Matt“, tačiau yra tik vienas „Matt“ ir „Unit Test“ arba „Matt and Mid-Term“ derinys.

Štai šie veiksmai:

Įterpkite pagalbinį stulpelį tarp B ir C stulpelių.
Pagalbos stulpelyje naudokite šią formulę: = A2 & ”|” ir B2
- Taip kiekvienam egzemplioriui būtų sukurti unikalūs kvalifikatoriai, kaip parodyta žemiau.
Naudokite šią formulę G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
Kopijuoti visas ląsteles.

Kaip tai veikia?

Kiekvienam vardo ir egzamino egzemplioriui sukuriame unikalius kriterijus. Naudojant čia naudojamą funkciją VLOOKUP, paieškos vertė buvo pakeista į $ F3 & "|" & G $ 2, kad abu paieškos kriterijai būtų sujungti ir naudojami kaip viena paieškos vertė. Pavyzdžiui, G2 funkcijos VLOOKUP paieškos vertė yra matinė | Vieneto testas. Dabar ši paieškos vertė naudojama norint gauti balą iš C2: D19.

Paaiškinimai:

Jums gali kilti keletas klausimų, todėl maniau, kad pabandysiu atsakyti čia:

Kodėl aš naudojau | simbolis, jungiantis prie dviejų kriterijų? - Kai kuriomis ypač retomis (bet galimomis) sąlygomis galite turėti du skirtingus kriterijus, tačiau kartu duodami tą patį rezultatą. Štai labai paprastas pavyzdys (atleiskite man už kūrybiškumo trūkumą):

Atminkite, kad nors A2 ir A3 yra skirtingi, o B2 ir B3 yra skirtingi, deriniai galiausiai yra vienodi. Bet jei naudojate separatorių, net ir derinys būtų kitoks (D2 ir D3).

Kodėl aš padėjau pagalbinį stulpelį tarp B ir C stulpelių, o ne kraštutiniame kairiajame kampe? - Nėra jokios žalos įterpti pagalbinį stulpelį į kraštutinę kairę. Tiesą sakant, jei nenorite susigrąžinti pirminių duomenų, tai turėtų būti teisingas kelias. Aš tai padariau, nes tai leidžia man naudoti mažiau ląstelių VLOOKUP funkcijoje. Užuot turėjęs 4 stulpelius lentelės masyve, galėčiau valdyti tik su 2 stulpeliais. Bet tai tik aš.

Dabar nėra vieno visiems tinkančio dydžio. Kai kurie žmonės gali nenorėti naudoti jokių pagalbinių stulpelių, kai naudoja VLOOKUP su keliais kriterijais.

Taigi čia yra jums nepadedančių stulpelių metodas.

Atsisiųskite pavyzdinį failą

VLOOKUP su keliais kriterijais - naudojant funkciją CHOOSE

Jei vietoj pagalbinių stulpelių naudosite masyvo formules, sutaupysite darbalapio nekilnojamojo turto, o našumas gali būti toks pat geras, jei darbo knygoje bus naudojamas mažiau kartų.

Atsižvelgiant į tą patį duomenų rinkinį, kurį naudojote aukščiau, pateikiame formulę, kuri duos jums rezultatą:

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

Kadangi tai yra masyvo formulė, naudokite ją naudodami „Control“ + „Shift“ + „Enter“, o ne tik „Enter“.

Kaip tai veikia?

Formulėje taip pat naudojama pagalbinio stulpelio sąvoka. Skirtumas tas, kad vietoj to, kad įdėtumėte pagalbinį stulpelį į darbalapį, laikykite jį virtualiais pagalbininko duomenimis, kurie yra formulės dalis.

Leiskite parodyti, ką turiu omenyje pagal virtualius pagalbininkų duomenis.

Aukščiau esančioje iliustracijoje, kai aš pasirenku formulės dalį PASIRINKTI ir paspaudžiu F9, tai rodo rezultatą, kurį suteiktų formulė PASIRINKTI.

Rezultatas yra {„Matt | Unit Test“, 91; „Bob | Unit Test“, 52;…}

Tai masyvas, kuriame kablelis reiškia kitą tos pačios eilutės langelį, o kabliataškis reiškia, kad šie duomenys yra kitame stulpelyje. Taigi, ši formulė sukuria 2 duomenų stulpelius - viename stulpelyje yra unikalus identifikatorius, o kitame - balas.

Dabar, kai naudojate funkciją VLOOKUP, ji paprasčiausiai ieško vertės pirmame stulpelyje (iš šių virtualių 2 stulpelių duomenų) ir grąžina atitinkamą balą.

Atsisiųskite pavyzdinį failą

Taip pat galite naudoti kitas formules, kad atliktumėte paiešką pagal kelis kriterijus (pvz., INDEX/MATCH arba SUMPRODUCT).

Ar žinote kokiu nors kitu būdu tai padaryti? Jei taip, pasidalykite su manimi komentarų skiltyje.

Jums taip pat gali patikti šios LOOKUP pamokos: