VLOOKUP på to eller flere kriteriekolonner – Excel University (Norsk)
Hvis du noen gang har prøvd å bruke en VLOOKUP-funksjon med to eller flere kriteriekolonner, har du raskt oppdaget at det var bare ikke bygget for det formålet. Heldigvis er det en annen funksjon som kan fungere som et alternativ til VLOOKUP, avhengig av hva du vil returnere.
Flersøyleoppslagsmål
La oss først bekrefte vårt mål ved å se på en prøve arbeidsbok. Vi har eksportert litt informasjon fra vårt regnskapssystem, og det oppsummerer i utgangspunktet transaksjonssummene for måneden etter klasse og etter konto. Et eksempel på eksporten er vist nedenfor:
Fra disse eksporterte dataene vil vi hente utvalgte mengder basert på klasse- og kontokolonner. Vi vil hente beløpene og plassere dem i vår lille rapport, avbildet nedenfor:
Hvis du er kjent med VLOOKUP funksjon, føles det naturlig å prøve å bygge rapporten med denne funksjonen, fordi det tross alt er en oppslagsoppgave. Og oppslagsoppgaver løses best med tradisjonelle oppslagsfunksjoner … ikke sant? Vel, det kommer an på. Det avhenger av hva du prøver å hente.
Betinget oppsummering for oppslag
Hvis du prøver å hente en numerisk verdi, for eksempel et beløp, er det ikke sikkert at en tradisjonell oppslagsfunksjon være din beste innsats. Her er hvorfor. Fra og med Excel 2007 inkluderte Microsoft den betingede summeringsfunksjonen SUMIFS. Denne summeringsfunksjonen for flere betingelser er designet for å legge til en kolonne med tall, og inkluderer bare rader som oppfyller en eller flere betingelser. Begynner punktene å koble seg til ennå?
Hvis vi bruker denne ideen på vår oppgave, vil vi raskt innse at vi kan bruke denne betingede summeringsfunksjonen til å hente rapportverdiene.
Det første argumentet til SUMIFS-funksjonen er sumområdet, det vil si kolonnen med tall som skal legges til. I vårt tilfelle kolonnen som har verdien vi ønsker å returnere. De gjenværende argumentene kommer i par: kriterieområdet og kriterieverdien.
Det er nyttig å tenke på funksjonen i disse vilkårene: legg til denne kolonnen (argument 1), bare inkluder de radene der denne kolonnen er (argument 2) er lik denne verdien (argument 3), og hvor denne kolonnen (argument 4) er lik denne verdien (argument 5), og hvor… og så videre, opp til 127 par.
For å fylle ut rapporten vår, henter vi dermed beløpsverdiene fra eksporten og samsvarer med klasse- og kontokolonnene, som vist nedenfor.
Hvis det tilfeldigvis er flere rader med samme klasse og kontoer, vil SUMIFS-funksjonen returnere summen av alle samsvarende elementer.
Som du ser, hvis verdien du prøver å returnere er et tall, så gjør SUMIFS-funksjonen det enkelt å utføre flerkolonnesøk. Men hva om verdien du prøver å returnere ikke er et tall? Vel, så må du bruke en tradisjonell oppslagsfunksjon som diskutert nedenfor.
Bruk av VLOOKUP med SUMIFS-metode
En metode er å bruke VLOOKUP og SUMIFS i en enkelt formel. I hovedsak bruker du SUMIFS som det første argumentet til VLOOKUP. Denne metoden blir utforsket i sin helhet i dette Excel University-innlegget:
Bruk av VLOOKUP med CONCATENATE Method
Hvis du prøver å returnere en tekststreng i stedet for et tall, eller bruker en versjon av Excel som ikke har SUMIFS, så er du sannsynligvis fast med å bruke en tradisjonell oppslagsfunksjon som VLOOKUP sammen med CONCATENATE-funksjonen for å generere en enkelt unik oppslagskolonne. Denne tilnærmingen er ganske godt dokumentert, men grunnideen går slik: Lag en enkelt oppslagskolonne først, og bruk deretter VLOOKUP.
Eksemplet vårt vil være en ansatteliste, som illustrert nedenfor:
Vi må hente staten fra medarbeiderlisten for vår lille rapport vist nedenfor:
Siden verdien vi prøver å returnere, staten, er en tekststreng og ikke et tall, er vi utelukket fra å bruke SUMIFS-funksjonen. Dermed må vi gå på skolen med VLOOKUP og CONCATENATE.
Vi begynner med å bygge en hjelpekolonne som i utgangspunktet skaper de kombinerte oppslagsverdiene. Dette kan enkelt oppnås med CONCATENATE-funksjonen eller sammenkoblingsoperatøren (&). Denne nye oppslagskolonnen er illustrert i kolonne B nedenfor:
Nå har vi en enkelt oppslagskolonne som kan brukes med en tradisjonell oppslagsfunksjon som VLOOKUP. Rapporten kan fylles ut ved å slå opp de kombinerte navnene i det nye oppslagsområdet, som vist nedenfor:
Den samme tilnærmingen kan brukes når to, tre eller flere oppslagskolonner må vurderes.
Konklusjon
I tillegg til å kunne utføre flerkolonnesøk når returverdien er numerisk, har SUMIFS-funksjonen flere fordeler sammenlignet med tradisjonelle oppslagsfunksjoner. For eksempel returnerer den null når det ikke finnes noen samsvarende verdi, den returnerer summen av alle treff, den støtter sammenligningsoperatorer, og den går ikke i stykker når en ny kolonne settes inn mellom oppslag- og returkolonnene.
Så, når du er i ferd med å bytte ut VLOOKUP-funksjonen for å utføre en oppslagsoppgave, bør du vurdere å bruke SUMIFS i stedet. Tro det eller ei, SUMIFS-funksjonen er en fantastisk oppslagsfunksjon.
Hvis du har noen andre foretrukne tilnærminger til flerkolonnesøk, vil vi gjerne høre mer … vennligst legg inn en kommentar nedenfor.
Eksempelfil
Hvis du vil spille med arbeidsboken som ble brukt til å generere skjermbildene ovenfor, er du velkommen til å laste ned eksempelfilen:
MultColumnLookup