Se video - Slik bruker du VLOOKUP -funksjon med flere kriterier
Excel VLOOKUP -funksjon, i sin grunnleggende form, kan se etter en oppslagsverdi og returnere den tilsvarende verdien fra den angitte raden.
Men ofte er det behov for å bruke Excel VLOOKUP med flere kriterier.
Slik bruker du VLOOKUP med flere kriterier
Anta at du har data med elevenes navn, eksamenstype og matematikkpoengsummen (som vist nedenfor):
Det kan være en utfordring å bruke VLOOKUP -funksjonen for å få matematikkpoengene for hver elev for de respektive eksamensnivåene.
Man kan argumentere for at et bedre alternativ ville være å omstrukturere datasettet eller bruke et pivottabell. Hvis det fungerer for deg, ingenting lignende. Men i mange tilfeller sitter du fast med dataene du har, og pivottabellen er kanskje ikke et alternativ.
I slike tilfeller er denne opplæringen noe for deg.
Nå er det to måter du kan få oppslagsverdien ved å bruke VLOOKUP med flere kriterier.
- Bruke en hjelperkolonne.
- Bruke CHOOSE -funksjonen.
VLOOKUP med flere kriterier - Bruke en hjelperkolonne
Jeg er en fan av hjelperkolonner i Excel.
Jeg finner to betydelige fordeler ved å bruke hjelperkolonner fremfor matriseformler:
- Det gjør det enkelt å forstå hva som skjer i regnearket.
- Det gjør det raskere sammenlignet med matrisefunksjonene (merkbar i store datasett).
Nå, ikke misforstå meg. Jeg er ikke imot matriseformler. Jeg elsker de fantastiske tingene som kan gjøres med matriseformler. Det er bare det at jeg lagrer dem til spesielle anledninger når alle andre alternativer ikke hjelper.
For å komme tilbake til spørsmålet i punkt, er hjelperkolonnen nødvendig for å skape en unik kvalifisering. Denne unike kvalifikatoren kan deretter brukes til å slå opp riktig verdi. For eksempel er det tre Matt i dataene, men det er bare en kombinasjon av Matt og Unit Test eller Matt og Mid-Term.
Her er trinnene:
- Sett inn en hjelperkolonne mellom kolonne B og C.
- Bruk følgende formel i hjelperkolonnen: = A2 & ”|” og B2
- Dette vil skape unike kvalifiseringer for hver forekomst som vist nedenfor.
- Dette vil skape unike kvalifiseringer for hver forekomst som vist nedenfor.
- Bruk følgende formel i G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Kopier for alle cellene.
Hvordan virker dette?
Vi lager unike kvalifiseringer for hver forekomst av et navn og eksamen. I VLOOKUP -funksjonen som brukes her, ble oppslagsverdien endret til $ F3 & ”|” & G $ 2 slik at begge oppslagskriteriene kombineres og brukes som en enkelt oppslagsverdi. For eksempel er oppslagsverdien for VLOOKUP -funksjonen i G2 Matt | Unit Test. Nå brukes denne oppslagsverdien for å få poengsummen fra C2: D19.
Avklaringer:
Det er sannsynligvis et par spørsmål du vil tenke på, så jeg tenkte jeg skal prøve å svare på det her:
- Hvorfor har jeg brukt | symbol mens du slutter deg til de to kriteriene? - I noen eksepsjonelt sjeldne (men mulige) tilstander kan du ha to kriterier som er forskjellige, men ender opp med å gi det samme resultatet når de kombineres. Her er et veldig enkelt eksempel (tilgi meg for min mangel på kreativitet her):
Vær oppmerksom på at mens A2 og A3 er forskjellige og B2 og B3 er forskjellige, blir kombinasjonene de samme. Men hvis du bruker en separator, ville selv kombinasjonen være annerledes (D2 og D3).
- Hvorfor satte jeg inn hjelpekolonnen mellom kolonne B og C og ikke ytterst til venstre? - Det er ingen skade ved å sette inn hjelpekolonnen ytterst til venstre. Faktisk, hvis du ikke vil temperere med de originale dataene, bør det være veien å gå. Jeg gjorde det fordi det får meg til å bruke mindre antall celler i VLOOKUP -funksjonen. I stedet for å ha 4 kolonner i tabellmatrisen, kunne jeg klare meg med bare 2 kolonner. Men det er bare meg.
Nå er det ingen størrelse som passer alle. Noen mennesker foretrekker kanskje å ikke bruke noen hjelperkolonne mens de bruker VLOOKUP med flere kriterier.
Så her er ikke-hjelper-kolonnemetoden for deg.
Last ned eksempelfilen
VLOOKUP med flere kriterier - Bruke VELG -funksjonen
Ved å bruke matriseformler i stedet for hjelperkolonner sparer du regnearkseiendom, og ytelsen kan være like god hvis den brukes færre ganger i en arbeidsbok.
Med tanke på det samme datasettet som brukt ovenfor, her er formelen som vil gi deg resultatet:
= VLOOKUP ($ E3 & ”|” & F $ 2, VELG ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
Siden dette er en matriseformel, bruker du den med Control + Shift + Enter, i stedet for bare Enter.
Hvordan virker dette?
Formelen bruker også begrepet en hjelperkolonne. Forskjellen er at i stedet for å sette hjelperkolonnen i regnearket, betrakter den som virtuelle hjelperedata som er en del av formelen.
La meg vise deg hva jeg mener med virtuelle hjelperdata.
I illustrasjonen ovenfor, mens jeg velger VELG delen av formelen og trykker på F9, viser det resultatet som VELG formelen ville gi.
Resultatet er {"Matt | Unit Test", 91; "Bob | Unit Test", 52;…}
Det er en matrise der et komma representerer neste celle i samme rad og semikolon representerer at følgende data er i den neste kolonnen. Derfor oppretter denne formelen 2 kolonner med data - En kolonne har den unike identifikatoren og en har poengsummen.
Når du bruker VLOOKUP -funksjonen, ser den ganske enkelt etter verdien i den første kolonnen (av disse virtuelle 2 -kolonnedataene) og returnerer den tilsvarende poengsummen.
Last ned eksempelfilen
Du kan også bruke andre formler til å gjøre et oppslag med flere kriterier (for eksempel INDEX/MATCH eller SUMPRODUCT).
Er det noen annen måte du vet å gjøre dette på? Hvis ja, del med meg i kommentarfeltet.
Du kan også like følgende opplæringsopplæringer:
- VLOOKUP Vs. INDEKS/MATCH
- Få flere oppslagsverdier uten gjentagelse i en enkelt celle.
- Slik gjør du VLOOKUP Case Sensitive.
- Bruk IFERROR med VLOOKUP for å bli kvitt #N/A -feil.
