Oglejte si video - Kako uporabljati funkcijo VLOOKUP z več merili
Excel VLOOKUP funkcija v svoji osnovni obliki lahko poišče eno iskalno vrednost in vrne ustrezno vrednost iz podane vrstice.
Pogosto pa je treba uporabiti Excel VLOOKUP z več merili.
Kako uporabljati VLOOKUP z več merili
Recimo, da imate podatke z imenom študentov, vrsto izpita in oceno matematike (kot je prikazano spodaj):
Uporaba funkcije VLOOKUP za pridobivanje ocene iz matematike za vsakega študenta za ustrezne izpitne ravni je lahko izziv.
Lahko bi trdili, da bi bila boljša možnost prestrukturiranje nabora podatkov ali uporaba vrtilne tabele. Če vam to ustreza, nič takega. Toda v mnogih primerih ste obtičali s podatki, ki jih imate, in vrtilna tabela morda ni možnost.
V takih primerih je ta vadnica za vas.
Zdaj obstajata dva načina, kako lahko dobite iskalno vrednost z uporabo VLOOKUP z več merili.
- Uporaba stolpca za pomoč.
- Uporaba funkcije IZBERI.
VLOOKUP z več merili - uporaba stolpca za pomoč
Sem ljubitelj pomožnih stolpcev v Excelu.
Ugotavljam dve pomembni prednosti uporabe pomožnih stolpcev pred formulami matrike:
- Olajša razumevanje dogajanja na delovnem listu.
- To je hitrejše v primerjavi s funkcijami matrike (opazno pri velikih naborih podatkov).
Ne razumite me narobe. Nisem proti formulam matrike. Všeč so mi neverjetne stvari, ki jih je mogoče narediti z matričnimi formulami. Samo shranim jih za posebne priložnosti, ko vse druge možnosti niso v pomoč.
Če se vrnem na vprašanje, je stolpec pomočnik potreben za ustvarjanje edinstvenega kvalifikatorja. Ta edinstven kvalifikator se nato lahko uporabi za iskanje pravilne vrednosti. Na primer, v podatkih so trije Matt, vendar je samo ena kombinacija Matt in Unit Test ali Matt in Mid-Term.
Tu so koraki:
- Vstavite pomožni stolpec med stolpca B in C.
- V pomožnem stolpcu uporabite naslednjo formulo: = A2 & ”|” & B2
- To bi ustvarilo edinstvene kvalifikatorje za vsak primerek, kot je prikazano spodaj.
- To bi ustvarilo edinstvene kvalifikatorje za vsak primerek, kot je prikazano spodaj.
- Uporabite naslednjo formulo v G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Kopija za vse celice.
Kako to deluje?
Ustvarjamo edinstvene kvalifikatorje za vsak primerek imena in izpita. V funkciji VLOOKUP, ki je bila uporabljena tukaj, je bila vrednost iskanja spremenjena v $ F3 & ”|” & G $ 2, tako da sta oba kriterija iskanja združena in se uporabljata kot ena sama vrednost iskanja. Na primer, iskalna vrednost za funkcijo VLOOKUP v G2 je Matt | Unit Test. Zdaj se ta vrednost iskanja uporablja za pridobitev rezultata iz C2: D19.
Pojasnila:
Nekaj vprašanj vam bo verjetno padlo na pamet, zato sem mislil, da bom tukaj poskusil odgovoriti:
- Zakaj sem uporabil | simbol, ko se pridružujete obema meriloma? - V nekaterih izjemno redkih (vendar možnih) pogojih lahko imate dva različna merila, ki pa v kombinaciji data enak rezultat. Tu je zelo preprost primer (oprostite mi za pomanjkanje ustvarjalnosti tukaj):
Upoštevajte, da sta A2 in A3 različni, B2 in B3 pa različni, vendar sta kombinaciji enaki. Če pa uporabite ločilo, bi bila tudi kombinacija drugačna (D2 in D3).
- Zakaj sem pomožni stolpec vstavil med stolpca B in C in ne v skrajni levi? - Vstavljanje stolpca za pomoč v skrajni levi ni škodljivo. Pravzaprav, če se ne želite umiriti s prvotnimi podatki, bi to moralo biti tako. To sem storil, ker zaradi tega uporabljam manj celic v funkciji VLOOKUP. Namesto 4 stolpcev v matriki tabel bi lahko upravljal samo z 2 stolpcema. Ampak to sem samo jaz.
Zdaj ni ene velikosti, ki bi ustrezala vsem. Nekateri ljudje raje ne uporabljajo nobenega stolpca za pomoč, medtem ko uporabljajo VLOOKUP z več merili.
Tukaj je torej metoda pomožnih stolpcev za vas.
Prenesite datoteko z vzorcem
VLOOKUP z več merili - uporaba funkcije IZBERI
Z uporabo matričnih formul namesto pomožnih stolpcev prihranite nepremičnine na delovnem listu, uspešnost pa je lahko enako dobra, če jo v delovnem zvezku uporabite manjkrat.
Glede na isti niz podatkov, kot je bil uporabljen zgoraj, je tukaj formula, ki vam bo dala rezultat:
= VLOOKUP ($ E3 & ”|” & F $ 2, IZBERITE ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
Ker je to formula matrike, jo uporabite s tipkama Control + Shift + Enter, namesto samo Enter.
Kako to deluje?
Formula uporablja tudi koncept pomožnega stolpca. Razlika je v tem, da namesto da stolpec pomočnika vnesete na delovni list, ga upoštevajte kot podatke navideznega pomočnika, ki so del formule.
Naj vam pokažem, kaj mislim s podatki virtualnih pomočnikov.
Na zgornji sliki, ko izberem del formule IZBERI in pritisnem F9, se prikaže rezultat, ki bi ga dala formula IZBERI.
Rezultat je {"Matt | Unit Test", 91; "Bob | Unit Test", 52;…}
To je matrika, kjer vejica predstavlja naslednjo celico v isti vrstici, podpičje pa pomeni, da so naslednji podatki v naslednjem stolpcu. Ta formula torej ustvarja 2 stolpca podatkov - en stolpec ima edinstven identifikator, drugi pa rezultat.
Zdaj, ko uporabljate funkcijo VLOOKUP, preprosto poišče vrednost v prvem stolpcu (teh navideznih podatkov 2 stolpcev) in vrne ustrezno oceno.
Prenesite datoteko z vzorcem
Za iskanje po več merilih (na primer INDEX/MATCH ali SUMPRODUCT) lahko uporabite tudi druge formule.
Ali obstaja kakšen drug način za to? Če da, delite z mano v razdelku za komentarje.
Morda vam bodo všeč tudi naslednji vadniki LOOKUP:
- VLOOKUP vs. INDEX/MATCH
- Pridobite več vrednosti iskanja brez ponavljanja v eni celici.
- Kako narediti VLOOKUP občutljiv na velike in male črke.
- Uporabite IFERROR z VLOOKUP -om, da se znebite #N/A napak.
