VLOOKUP på to eller flere kriteriekolonner – Excel University
Hvis du nogensinde har forsøgt at bruge en VLOOKUP-funktion med to eller flere kriteriekolonner, har du hurtigt opdaget at det var bare ikke bygget til det formål. Heldigvis er der en anden funktion, der kan fungere som et alternativ til VLOOKUP, afhængigt af hvad du vil returnere.
Flersøjleopslagsmål
Lad os først bekræfte vores mål ved at se på et eksempel på en projektmappe. Vi har eksporteret nogle oplysninger fra vores regnskabssystem, og det opsummerer grundlæggende transaktionssummen for måneden efter klasse og konto. Nedenfor vises en prøve af eksporten:
Fra disse eksporterede data vil vi gerne hente valgte beløb baseret på klasse- og kontokolonner. Vi ønsker at hente beløbene og placere dem i vores lille rapport, afbilledet nedenfor:
Hvis du er fortrolig med VLOOKUP funktion, føles det naturligt at forsøge at opbygge rapporten med denne funktion, fordi det trods alt er en opslagsopgave. Og opslagsopgaver løses bedst med traditionelle opslagsfunktioner … ikke? Nå, det afhænger. Det afhænger af, hvad du forsøger at hente.
Betinget opsummering for opslag
Hvis du forsøger at hente en numerisk værdi, f.eks. Et beløb, er en traditionel opslagsfunktion muligvis ikke være dit bedste valg. Her er hvorfor. Begyndende med Excel 2007 inkluderede Microsoft den betingede summeringsfunktion SUMIFS. Denne summeringsfunktion med flere betingelser er designet til at tilføje en kolonne med tal og inkluderer kun rækker, der opfylder en eller flere betingelser. Begynder prikkerne at forbinde endnu?
Hvis vi anvender denne idé til vores opgave, ville vi hurtigt indse, at vi kunne bruge denne betingede summeringsfunktion til at hente vores rapportværdier.
Det første argument for SUMIFS-funktionen er sumområdet, det vil sige den talekolonne, der skal tilføjes. I vores tilfælde den kolonne, der har den værdi, vi ønsker at returnere. De resterende argumenter kommer parvis: kriterieområdet og kriterieværdien.
Det er nyttigt at tænke over funktionen i disse termer: tilføj denne kolonne (argument 1), kun inkludere de rækker, hvor denne kolonne (argument 2) er lig med denne værdi (argument 3), og hvor denne kolonne (argument 4) er lig med denne værdi (argument 5), og hvor… og så videre, op til 127 par.
For at udfylde vores rapport henter vi således mængdeværdierne fra eksporten og matcher klasse- og kontokolonnerne som vist nedenfor.
Hvis der tilfældigvis er flere rækker med samme klasse og konti, vil SUMIFS-funktionen returnere summen af alle matchende emner.
Som du kan se, hvis værdien du forsøger at returnere er et tal, så gør SUMIFS-funktionen det nemt at udføre opslag i flere kolonner. Men hvad hvis den værdi, du prøver at returnere, ikke er et tal? Nå, så bliver du nødt til at bruge en traditionel opslagsfunktion som beskrevet nedenfor.
Brug af VLOOKUP med SUMIFS-metode
En metode er at bruge VLOOKUP og SUMIFS i en enkelt formel. I det væsentlige bruger du SUMIFS som det første argument i VLOOKUP. Denne metode udforskes fuldt ud i dette Excel University-indlæg:
Brug af VLOOKUP med CONCATENATE-metode
Hvis du prøver at returnere en tekststreng snarere end et tal, eller bruger en version af Excel, der ikke har SUMIFS, så sidder du sandsynligvis ved at bruge en traditionel opslagsfunktion som VLOOKUP sammen med funktionen CONCATENATE for at generere en enkelt unik opslagskolonne. Denne tilgang er ret veldokumenteret, men grundidéen går sådan her: Opret først en enkelt opslagskolonne, og brug derefter VLOOKUP.
Vores eksempel vil være en medarbejderliste, som illustreret nedenfor:
Vi er nødt til at hente staten fra medarbejderlisten til vores lille rapport vist nedenfor:
Da den værdi, vi prøver at returnere, staten er en tekststreng og ikke et tal, er vi udelukket fra at bruge SUMIFS-funktionen. Derfor bliver vi nødt til at gå i old school med VLOOKUP og CONCATENATE.
Vi starter med at opbygge en hjælpekolonne, der grundlæggende skaber de kombinerede opslagsværdier. Dette kan let opnås med CONCATENATE-funktionen eller sammenkædningsoperatoren (&). Denne nye opslagskolonne er illustreret i kolonne B nedenfor:
Nu har vi en enkelt opslagskolonne, der kan bruges med en traditionel opslagsfunktion såsom VLOOKUP. Rapporten kan udfyldes ved at slå de kombinerede navne op inden for det nye opslagsområde, som vist nedenfor:
Den samme tilgang kan bruges, når to, tre eller flere opslagskolonner skal overvejes.
Konklusion
Ud over at være i stand til at udføre opslag i flere kolonner, når returværdien er numerisk, har SUMIFS-funktionen yderligere fordele sammenlignet med traditionelle opslagsfunktioner. For eksempel returnerer den nul, når der ikke findes nogen matchende værdi, den returnerer summen af alle matches, den understøtter sammenligningsoperatorer, og den går ikke i stykker, når en ny kolonne indsættes mellem opslags- og returkolonnerne.
Så når du skal ud med VLOOKUP-funktionen for at foretage en opslagsopgave, skal du overveje at bruge SUMIFS i stedet. Tro det eller ej, SUMIFS-funktionen er en vidunderlig opslagsfunktion.
Hvis du har andre foretrukne tilgange til opslag i flere kolonner, vil vi meget gerne høre mere … skriv en kommentar nedenfor.
Eksempelfil
Hvis du vil lege med den projektmappe, der bruges til at generere skærmbillederne ovenfor, er du velkommen til at downloade prøvefilen:
MultColumnLookup