Indhold
I anden del af vores tutorial om funktionen VPR (VLOOKUP) i Excel, vil vi analysere et par eksempler, der vil hjælpe dig med at styre al magten VPR at løse de mest ambitiøse Excel-opgaver. Eksemplerne forudsætter, at du allerede har en grundlæggende viden om, hvordan denne funktion fungerer. Hvis ikke, kan du være interesseret i at starte med den første del af denne tutorial, som forklarer syntaksen og den grundlæggende brug. VPR. Nå, lad os komme i gang.
Søg i Excel efter flere kriterier
Funktion VPR i Excel er et virkelig kraftfuldt værktøj til at udføre søgninger efter en bestemt værdi i en database. Der er dog en betydelig begrænsning - dens syntaks giver dig mulighed for kun at søge efter én værdi. Hvad hvis du vil søge efter flere betingelser? Du finder løsningen nedenfor.
Eksempel 1: Søg efter 2 forskellige kriterier
Antag, at vi har en liste over ordrer, og vi ønsker at finde Mængde af varer (Antal), baseret på to kriterier – Kundenavn (Kunde) и Название продукта (Produkt). Sagen kompliceres af, at hver af køberne bestilte flere typer varer, som det kan ses af nedenstående tabel:
almindelig funktion VPR vil ikke fungere i dette scenarie, fordi det vil returnere den første værdi, den finder, der matcher den givne opslagsværdi. For eksempel hvis du vil vide mængden af en vare Søde sager'bestilt af køber Jeremy Hill, skriv følgende formel:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– denne formel vil returnere resultatet 15svarende til produktet æbler, fordi det er den første værdi, der matcher.
Der er en enkel løsning – opret en ekstra kolonne, hvor du kan kombinere alle de ønskede kriterier. I vores eksempel er disse kolonner Kundenavn (Kunde) и Название продукта (Produkt). Glem ikke, at den flettede kolonne altid skal være kolonnen længst til venstre i søgeområdet, da det er den venstre kolonne, som funktionen VPR kigger op, når du leder efter en værdi.
Så du tilføjer en hjælpekolonne til tabellen og kopierer følgende formel over alle dens celler: =B2&C2. Hvis du ønsker, at strengen skal være mere læsbar, kan du adskille de kombinerede værdier med et mellemrum: =B2&» «&C2. Derefter kan du bruge følgende formel:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Hvor er cellen B1 indeholder den sammenkædede værdi af argumentet opslagsværdi (opslagsværdi) og 4 – Argument col_index_num (kolonne_nummer), dvs. nummeret på den kolonne, der indeholder de data, der skal hentes.
Eksempel 2: VOPSLAG efter to kriterier med tabel vist på et andet ark
Hvis du skal opdatere hovedtabellen (Hovedtabel) ved at tilføje data fra den anden tabel (Opslagstabel), som er placeret på et andet ark eller i en anden Excel-projektmappe, så kan du samle den ønskede værdi direkte i formlen, som du indsætter ind i hovedtabellen.
Som i det foregående eksempel skal du bruge en hjælpekolonne i opslagstabellen med de kombinerede værdier. Denne kolonne skal være kolonnen længst til venstre i søgeområdet.
Altså formlen med VPR kunne være sådan her:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Her indeholder kolonne B og C henholdsvis kundenavne og produktnavne samt linket Ordrer!$A&$2:$D$2 definerer en tabel, der skal slås op i et andet ark.
For at gøre formlen mere læsbar kan du give visningsområdet et navn, og så vil formlen se meget enklere ud:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
For at formlen skal fungere, skal værdierne i kolonnen længst til venstre i tabellen, du kigger på, kombineres på nøjagtig samme måde som i søgekriterierne. I figuren ovenfor kombinerede vi værdierne u2bu2band satte et mellemrum mellem dem, på samme måde som du skal gøre i det første argument af funktionen (BXNUMX& “” & CXNUMX).
Husk! Funktion VPR begrænset til 255 tegn, kan den ikke søge efter en værdi, der er mere end 255 tegn lang. Husk dette og sørg for, at længden af den ønskede værdi ikke overstiger denne grænse.
Jeg er enig i, at tilføjelse af en hjælpekolonne ikke er den mest elegante og ikke altid acceptable løsning. Du kan gøre det samme uden hjælpekolonnen, men det ville kræve en meget mere kompleks formel med en kombination af funktioner INDEX (INDEX) og MATCH (MERE UDSAT).
Vi udtrækker 2., 3. osv. værdier ved hjælp af VLOOKUP
Det ved du allerede VPR kan kun returnere én matchende værdi, mere præcist, den første fundet. Men hvad nu hvis denne værdi gentages flere gange i det viste array, og du vil udtrække den 2. eller 3. af dem? Hvad hvis alle værdier? Problemet virker kompliceret, men løsningen findes!
Antag, at en kolonne i tabellen indeholder navnene på kunderne (Kundenavn), og den anden kolonne indeholder de produkter (Produkt), som de har købt. Lad os prøve at finde den 2., 3. og 4. vare købt af en given kunde.
Den nemmeste måde er at tilføje en hjælpekolonne før kolonnen Kundenavn og udfyld den med kundenavne med gentagelsesnummeret for hvert navn, f.eks. John Doe 1, John Doe 2 osv. Vi vil gøre tricket med nummerering ved hjælp af funktionen TÆL.HVIS (COUNTIF), givet at kundenavnene er i kolonne B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Derefter kan du bruge den normale funktion VPRfor at finde den ønskede ordre. For eksempel:
- Finde 2 vare bestilt af kunden Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Finde 3 vare bestilt af kunden Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Faktisk kan du indtaste en cellereference som opslagsværdi i stedet for tekst, som vist i følgende figur:
Hvis du kun leder efter 2-e gentagelse, kan du gøre det uden hjælpekolonnen ved at oprette en mere kompleks formel:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
I denne formel:
- $ F $ 2 – en celle med navnet på køberen (det er uændret, bemærk venligst – linket er absolut);
- $ B $ – kolonne Kundenavn;
- Table4 – Dit bord (dette sted kan også være et almindeligt udvalg);
- $ C16 – slutcellen i din tabel eller dit område.
Denne formel finder kun den anden matchende værdi. Hvis du har brug for at udtrække de resterende gentagelser, skal du bruge den forrige løsning.
Hvis du har brug for en liste over alle kampe – funktionen VPR dette er ikke en hjælper, da det kun returnerer én værdi ad gangen – punktum. Men Excel har en funktion INDEX (INDEX), som sagtens kan klare denne opgave. Hvordan en sådan formel vil se ud, lærer du i det følgende eksempel.
Hent alle gentagelser af den ønskede værdi
Som nævnt ovenfor VPR kan ikke udtrække alle duplikerede værdier fra det scannede område. For at gøre dette skal du bruge en lidt mere kompleks formel, der er opbygget af flere Excel-funktioner, som f.eks INDEX (INDEKS), LILLE (LILLE) og RÆKKE (LINE)
For eksempel finder formlen nedenfor alle gentagelser af værdien fra celle F2 i området B2:B16 og returnerer resultatet fra de samme rækker i kolonne C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Indtast denne matrixformel i flere tilstødende celler, såsom cellerne F4: F8som vist i nedenstående figur. Antallet af celler skal være lig med eller større end det maksimalt mulige antal gentagelser af den søgte værdi. Glem ikke at klikke Ctrl + Skift + Enterfor at indtaste matrixformlen korrekt.
Hvis du er interesseret i at forstå, hvordan det virker, så lad os dykke lidt ned i detaljerne i formlen:
Del 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – sammenlign værdien i celle F2 med hver af værdierne i området B2:B16. Hvis der findes et match, så udtrykket STRING(C2:C16)-1 returnerer nummeret på den tilsvarende linje (værdi -1 giver dig mulighed for ikke at inkludere overskriftslinjen). Hvis der ikke er nogen match, funktionen IF (IF) returnerer en tom streng.
Funktionsresultat IF (IF) der vil være sådan et vandret array: {1,"",3,"",5,"","","","","","",12,"","",""}
Del 2:
ROW()-3
СТРОКА()-3
Her er funktionen RÆKKE (LINE) fungerer som en ekstra tæller. Da formlen er kopieret ind i cellerne F4:F9, trækker vi tallet fra 3 fra funktionsresultat til at få værdi 1 i cellen F4 (linje 4, træk 3 fra) for at få 2 i cellen F5 (linje 5, træk 3 fra) og så videre.
Del 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funktion LILLE (SMÅ) vender tilbage n-åh den mindste værdi i dataarrayet. I vores tilfælde bestemmes hvilken position (fra den mindste) der skal returneres af funktionen RÆKKE (LINE) (se del 2). Altså for en celle F4 funktion SMALL({matrix},1) afkast 1 (mindste) array-element, dvs 1. Til celle F5 afkast 2 det mindste element i arrayet, dvs 3Osv
Del 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funktion INDEX (INDEX) returnerer blot værdien af en specifik celle i en matrix C2:C16. Til celle F4 funktion INDEX($C$2:$C$16) kommer tilbage æblerTil F5 funktion INDEX($C$2:$C$16) kommer tilbage Søde sager' og så videre.
Del 5:
IFERROR()
ЕСЛИОШИБКА()
Til sidst sætter vi formlen inde i funktionen FEJL (IFERROR), fordi du sandsynligvis ikke vil være tilfreds med fejlmeddelelsen #AT (#N/A), hvis antallet af celler, som formlen kopieres ind i, er mindre end antallet af duplikerede værdier i det område, der vises.
XNUMXD søgning efter kendt række og kolonne
Udførelse af en XNUMXD søgning i Excel involverer at søge efter en værdi efter et kendt række- og kolonnenummer. Med andre ord udtrækker du celleværdien ved skæringspunktet mellem en bestemt række og kolonne.
Så lad os vende tilbage til vores tabel og skrive en formel med en funktion VPR, som vil finde oplysninger om prisen på citroner solgt i marts.
Der er flere måder at udføre en XNUMXD søgning på. Tjek mulighederne ud og vælg den, der passer dig bedst.
VLOOKUP og MATCH funktioner
Du kan bruge en masse funktioner VPR (VLOOKUP) og MERE UDSAT (MATCH) for at finde værdien i skæringspunktet mellem felterne Название продукта (streng) og Måned (kolonne) for det pågældende array:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Formlen ovenfor er en regulær funktion VPR, som leder efter et nøjagtigt match af værdien "Citroner" i cellerne A2 til A9. Men da du ikke ved, hvilken kolonne marts-salget er i, vil du ikke kunne indstille kolonnenummeret for det tredje funktionsargument. VPR. I stedet bruges funktionen MERE UDSATfor at definere denne kolonne.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Oversat til menneskeligt sprog betyder denne formel:
- Vi leder efter karaktererne "Mar" - argument opslagsværdi (opslagsværdi);
- Ser i celler fra A1 til I1 – argument opslag_array (opslagsmatrix);
- Returnerer eksakt match – argument match_type (match_type).
Ved brug af 0 i det tredje argument siger du funktioner MERE UDSAT se efter den første værdi, der nøjagtigt matcher den værdi, du leder efter. Dette svarer til værdien FALSK (FALSK) for det fjerde argument VPR.
Sådan kan du oprette en to-vejs søgeformel i Excel, også kendt som todimensionel søgning eller tovejssøgning.
SUMPRODUCT funktion
Funktion SUMPRODUCT (SUMPRODUKT) returnerer summen af produkterne fra de valgte arrays:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
INDEX og MATCH funktioner
I den næste artikel vil jeg forklare disse funktioner i detaljer, så indtil videre kan du bare kopiere denne formel:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Navngivne områder og vejkrydsoperatøren
Hvis du ikke er til alle de komplekse Excel-formler, kan du måske lide denne visuelle og mindeværdige måde:
- Vælg tabellen, åbn fanen formler (Formler) og klik Opret fra markering (Opret fra udvalg).
- Marker kasserne Øverste række (på linjen ovenfor) og Venstre kolonne (i kolonnen til venstre). Microsoft Excel vil tildele navne til områderne fra værdierne i den øverste række og venstre kolonne i dit regneark. Nu kan du søge ved at bruge disse navne direkte uden at oprette formler.
- Skriv i enhver tom celle =rækkenavn kolonnenavn, for eksempel sådan her:
=Citroner Mar
… eller omvendt:
=Mar Citroner
Husk at række- og kolonnenavnene skal adskilles af et mellemrum, som i dette tilfælde fungerer som skæringsoperatoren.
Når du indtaster et navn, viser Microsoft Excel et værktøjstip med en liste over matchende navne, ligesom når du indtaster en formel.
- Presse Indtast og tjek resultatet
Generelt, uanset hvilken af ovenstående metoder du vælger, vil resultatet af en todimensionel søgning være det samme:
Brug af flere VLOOKUPs i én formel
Det sker, at hovedtabellen og opslagstabellen ikke har en enkelt kolonne til fælles, og det forhindrer dig i at bruge den sædvanlige funktion VPR. Der er dog en anden tabel, som ikke indeholder de oplysninger, vi er interesserede i, men som har en fælles kolonne med hovedtabellen og opslagstabellen.
Lad os tage et kig på følgende eksempel. Vi har en hovedtabel med en kolonne SKU (ny), hvor du vil tilføje en kolonne med de tilsvarende priser fra en anden tabel. Derudover har vi 2 opslagstabeller. Den første (opslagstabel 1) indeholder opdaterede tal SKU (ny) og produktnavne, og det andet (Opslagstabel 2) – produktnavne og gamle numre SKU (gammel).
For at tilføje priser fra den anden opslagstabel til hovedtabellen skal du udføre en handling kendt som dobbelt VPR eller indlejret VPR.
- Skriv en funktion VPR, som finder produktnavnet i tabellen Opslagstabel 1ved brug af SKU, som den ønskede værdi:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Her Ny_SKU – navngivet rækkevidde $A:$B i bordet Opslagstabel 1, 2 – dette er kolonne B, som indeholder varernes navne (se billedet ovenfor)
- Skriv en formel for at indsætte priser fra en tabel Opslagstabel 2 baseret på kendte produktnavne. For at gøre dette skal du indsætte den formel, du oprettede tidligere, som opslagsværdi for den nye funktion VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Her Pris – navngivet rækkevidde $ A: $ C i bordet Opslagstabel 2, 3 er kolonne C, der indeholder priser.
Figuren nedenfor viser resultatet returneret af den formel, vi oprettede:
Dynamisk substitution af data fra forskellige tabeller ved hjælp af VLOOKUP og INDIREKTE
Lad os først præcisere, hvad vi mener med udtrykket "Dynamisk substitution af data fra forskellige tabeller" for at sikre, at vi forstår hinanden korrekt.
Der er situationer, hvor der er flere ark med data af samme format, og det er nødvendigt at udtrække de nødvendige oplysninger fra et bestemt ark, afhængigt af den værdi, der er indtastet i en given celle. Jeg tror, det er nemmere at forklare dette med et eksempel.
Forestil dig, at du har salgsrapporter for flere regioner med de samme produkter og i samme format. Du vil finde salgstal for en bestemt region:
Hvis du kun har to sådanne rapporter, så kan du bruge en skandaløst simpel formel med funktioner VPR и IF (IF) for at vælge den ønskede rapport, der skal søges i:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Hvor:
- D$ 2 er en celle, der indeholder produktets navn. Bemærk, at vi bruger absolutte referencer her for at undgå at ændre opslagsværdien, når formlen kopieres til andre celler.
- $ D3 er en celle med navnet på regionen. Vi bruger en absolut kolonnereference og en relativ rækkereference, fordi vi planlægger at kopiere formlen til andre celler i samme kolonne.
- FL_Sales и CA_Salg – navnene på de tabeller (eller navngivne områder), der indeholder de tilsvarende salgsrapporter. Du kan selvfølgelig bruge de sædvanlige arknavne og celleområdereferencer f.eks 'FL Sheet'!$A$3:$B$10, men navngivne områder er meget mere bekvemme.
Men når der er mange sådanne tabeller, vil funktionen IF er ikke den bedste løsning. I stedet kan du bruge funktionen INDIREKTE (INDIREKTE) for at returnere det ønskede søgeområde.
Som du sikkert ved, er funktionen INDIREKTE bruges til at returnere et link givet af en tekststreng, hvilket er præcis, hvad vi har brug for nu. Så erstat udtrykket i ovenstående formel dristigt med funktionen IF at forbinde med funktion INDIREKTE. Her er en kombination VPR и INDIREKTE fungerer fint med:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Hvor:
- D$ 2 – dette er en celle med navnet på produktet, den er uændret på grund af det absolutte link.
- $ D3 er den celle, der indeholder den første del af områdenavnet. I vores eksempel er dette FL.
- _Salg – den fælles del af navnet på alle navngivne områder eller tabeller. Når den kombineres med værdien i celle D3, danner den det fuldt kvalificerede navn på det påkrævede område. Nedenfor er nogle detaljer for dem, der er nye til funktionen INDIREKTE.
Hvordan INDIREKTE og VLOOKUP virker
Lad mig først minde dig om syntaksen for funktionen INDIREKTE (INDIREKTE):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Det første argument kan være en cellereference (stil A1 eller R1C1), et områdenavn eller en tekststreng. Det andet argument bestemmer, hvilken type link der er indeholdt i det første argument:
- A1hvis argumentet er SAND KODE (TRUE) eller ikke specificeret;
- R1C1, Hvis FAS E (FALSK).
I vores tilfælde har linket stilen A1, så du kan udelade det andet argument og fokusere på det første.
Så lad os vende tilbage til vores salgsrapporter. Hvis du husker det, så er hver rapport en separat tabel placeret på et separat ark. For at formlen skal fungere korrekt, skal du navngive dine tabeller (eller områder), og alle navne skal have en fælles del. For eksempel sådan her: CA_Salg, FL_Salg, TX_Salg og så videre. Som du kan se, er "_Sales" til stede i alle navnene.
Funktion INDIREKTE forbinder værdien i kolonne D og tekststrengen "_Sales", og fortæller derved VPR i hvilken tabel der skal søges. Hvis celle D3 indeholder værdien "FL", vil formlen søge i tabellen FL_Salg, hvis "CA" - i tabellen CA_Salg og så videre.
Resultatet af funktionerne VPR и INDIREKTE vil være følgende:
Hvis dataene er placeret i forskellige Excel-bøger, skal du tilføje bogens navn før det navngivne område, for eksempel:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Hvis funktionen INDIREKTE henviser til en anden projektmappe, skal denne projektmappe være åben. Hvis den er lukket, vil funktionen rapportere en fejl. #REF! (#SSYL!).