Innhold
I den andre delen av vår veiledning om funksjonen VPR (VLOOKUP) i Excel vil vi analysere noen få eksempler som vil hjelpe deg med å styre all kraften VPR for å løse de mest ambisiøse Excel-oppgavene. Eksemplene forutsetter at du allerede har grunnleggende kunnskap om hvordan denne funksjonen fungerer. Hvis ikke, kan du være interessert i å starte med den første delen av denne opplæringen, som forklarer syntaksen og grunnleggende bruk. VPR. Vel, la oss komme i gang.
Søk i Excel etter flere kriterier
Funksjon VPR i Excel er et veldig kraftig verktøy for å utføre søk etter en bestemt verdi i en database. Det er imidlertid en betydelig begrensning - syntaksen lar deg søke etter bare én verdi. Hva om du vil søke etter flere forhold? Du finner løsningen nedenfor.
Eksempel 1: Søk etter 2 forskjellige kriterier
Anta at vi har en liste over bestillinger og vi ønsker å finne Mengde varer (antall), basert på to kriterier – Klientens navn (Kunde) и Название продукта (Produkt). Saken kompliseres av det faktum at hver av kjøperne bestilte flere typer varer, som det fremgår av tabellen nedenfor:
vanlig funksjon VPR vil ikke fungere i dette scenariet fordi det vil returnere den første verdien den finner som samsvarer med den gitte oppslagsverdien. For eksempel hvis du vil vite mengden av en vare søtsakerbestilt av kjøper Jeremy Hill, skriv følgende formel:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– denne formelen vil returnere resultatet 15tilsvarende produktet Epler, fordi det er den første verdien som samsvarer.
Det er en enkel løsning – lag en ekstra kolonne der du kan kombinere alle de ønskede kriteriene. I vårt eksempel er dette kolonnene Klientens navn (Kunde) и Название продукта (Produkt). Ikke glem at den sammenslåtte kolonnen alltid må være kolonnen lengst til venstre i søkeområdet, siden det er venstre kolonne som funksjonen VPR ser opp når du leter etter en verdi.
Så du legger til en hjelpekolonne i tabellen og kopierer følgende formel over alle cellene: =B2&C2. Hvis du vil at strengen skal være mer lesbar, kan du skille de kombinerte verdiene med et mellomrom: =B2&» «&C2. Etter det kan du bruke 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 inneholder den sammenkoblede verdien av argumentet søkeverdi (oppslagsverdi) og 4 – Argument col_index_num (kolonne_nummer), dvs. nummeret på kolonnen som inneholder dataene som skal hentes.
Eksempel 2: VLOOKUP etter to kriterier med tabell som vises på et annet ark
Hvis du trenger å oppdatere hovedtabellen (Hovedtabell) ved å legge til data fra den andre tabellen (Oppslagstabell), som ligger på et annet ark eller i en annen Excel-arbeidsbok, kan du samle ønsket verdi direkte i formelen du setter inn inn i hovedtabellen.
Som i forrige eksempel, trenger du en hjelpekolonne i oppslagstabellen med de kombinerte verdiene. Denne kolonnen må være kolonnen lengst til venstre i søkeområdet.
Så formelen med VPR kan være slik:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Her inneholder kolonne B og C henholdsvis kundenavn og produktnavn og lenken Bestillinger!$A&$2:$D$2 definerer en tabell som skal slås opp i et annet ark.
For å gjøre formelen mer lesbar kan du gi visningsområdet et navn, og da vil formelen se mye enklere ut:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
For at formelen skal fungere, må verdiene i kolonnen lengst til venstre i tabellen du ser på kombineres på nøyaktig samme måte som i søkekriteriene. I figuren ovenfor kombinerte vi verdiene u2bu2band satte et mellomrom mellom dem, på samme måte som du må gjøre i det første argumentet til funksjonen (BXNUMX& “” & CXNUMX).
Huske! Funksjon VPR begrenset til 255 tegn, kan den ikke søke etter en verdi som er mer enn 255 tegn lang. Ha dette i bakhodet og pass på at lengden på ønsket verdi ikke overskrider denne grensen.
Jeg er enig i at å legge til en hjelpekolonne ikke er den mest elegante og ikke alltid akseptable løsningen. Du kan gjøre det samme uten hjelpekolonnen, men det vil kreve en mye mer kompleks formel med en kombinasjon av funksjoner INDEX (INDEKS) og MATCH (MER EKSPONERT).
Vi trekker ut verdiene 2., 3. osv. ved å bruke VLOOKUP
Det vet du allerede VPR kan bare returnere én samsvarende verdi, mer presist, den første som ble funnet. Men hva om denne verdien gjentas flere ganger i den viste matrisen, og du vil trekke ut den andre eller tredje av dem? Hva om alle verdier? Problemet virker komplisert, men løsningen finnes!
Anta at en kolonne i tabellen inneholder navnene på kundene (kundenavn), og den andre kolonnen inneholder produktene (produktet) som de kjøpte. La oss prøve å finne den 2., 3. og 4. varen kjøpt av en gitt kunde.
Den enkleste måten er å legge til en hjelpekolonne før kolonnen Kundenavn og fyll den med kundenavn med repetisjonsnummeret for hvert navn, for eksempel, John Doe 1, John Doe 2 osv. Vi skal gjøre susen med nummerering ved hjelp av funksjonen ANTALL.HVIS (COUNTIF), gitt at kundenavnene er i kolonne B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Etter det kan du bruke den vanlige funksjonen VPRfor å finne ønsket rekkefølge. For eksempel:
- Finn 2. th vare bestilt av kunden Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Finn 3. th vare bestilt av kunden Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Faktisk kan du angi en cellereferanse som oppslagsverdi i stedet for tekst, som vist i følgende figur:
Hvis du bare er ute etter 2-e repetisjon, kan du gjøre det uten hjelpekolonnen ved å lage en mer 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 formelen:
- $F$2 – en celle som inneholder navnet på kjøperen (det er uendret, vær oppmerksom på – koblingen er absolutt);
- $ B $ - kolonne Kundenavn;
- Table4 – Bordet ditt (dette stedet kan også være et vanlig utvalg);
- $ C16 – sluttcellen til tabellen eller området ditt.
Denne formelen finner bare den andre samsvarende verdien. Hvis du trenger å trekke ut de gjenværende repetisjonene, bruk den forrige løsningen.
Hvis du trenger en liste over alle treff – funksjonen VPR dette er ikke en hjelper, siden det bare returnerer én verdi om gangen – punktum. Men Excel har en funksjon INDEX (INDEX), som enkelt kan takle denne oppgaven. Hvordan en slik formel vil se ut, vil du lære i følgende eksempel.
Hent alle repetisjoner av ønsket verdi
Som nevnt ovenfor VPR kan ikke trekke ut alle dupliserte verdier fra det skannede området. For å gjøre dette trenger du en litt mer kompleks formel, bygd opp av flere Excel-funksjoner, som f.eks INDEX (INDEKS), LITEN (LITEN) og RAD (LINJE)
For eksempel finner formelen nedenfor alle repetisjoner av verdien fra celle F2 i området B2:B16 og returnerer resultatet fra de samme radene 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));"")}
Skriv inn denne matriseformelen i flere tilstøtende celler, for eksempel cellene F4: F8som vist i figuren nedenfor. Antall celler må være lik eller større enn maksimalt mulig antall repetisjoner av den søkte verdien. Ikke glem å klikke Ctrl + Shift + Enterfor å angi matriseformelen riktig.
Hvis du er interessert i å forstå hvordan det fungerer, la oss dykke litt ned i detaljene i formelen:
Del 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – sammenlign verdien i celle F2 med hver av verdiene i området B2:B16. Hvis et samsvar blir funnet, så uttrykket STRING(C2:C16)-1 returnerer nummeret til den tilsvarende linjen (verdi -1 lar deg ikke inkludere overskriftslinjen). Hvis det ikke er noen treff, funksjonen IF (IF) returnerer en tom streng.
Funksjonsresultat IF (IF) det vil være en slik horisontal matrise: {1,"",3,"",5,"","","","","","",12,"","",""}
Del 2:
ROW()-3
СТРОКА()-3
Her er funksjonen RAD (LINE) fungerer som en ekstra teller. Siden formelen er kopiert inn i cellene F4:F9, trekker vi fra tallet 3 fra funksjonsresultat til å få verdi 1 i cellen F4 (linje 4, trekk fra 3) for å få 2 i cellen F5 (linje 5, trekk fra 3) 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))
Funksjon LITEN (LITEN) returnerer n-å den minste verdien i datamatrisen. I vårt tilfelle bestemmes hvilken posisjon (fra den minste) som skal returneres av funksjonen RAD (LINE) (se del 2). Altså for en celle F4 funksjon SMALL({array};1) avkastning 1. th (minste) array-element, dvs 1. For celle F5 avkastning 2. th det minste elementet i matrisen, altså 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))
Funksjon INDEX (INDEX) returnerer ganske enkelt verdien til en spesifikk celle i en matrise C2:C16. For celle F4 funksjon INDEKS($C$2:$C$16) vil returnere EplerTil F5 funksjon INDEKS($C$2:$C$16) vil returnere søtsaker og så videre.
Del 5:
IFERROR()
ЕСЛИОШИБКА()
Til slutt legger vi formelen inne i funksjonen FEIL (IFERROR), fordi du sannsynligvis ikke vil være fornøyd med feilmeldingen #AT (#N/A) hvis antallet celler som formelen kopieres inn i er mindre enn antallet dupliserte verdier i området som vises.
XNUMXD søk etter kjent rad og kolonne
Å utføre et XNUMXD søk i Excel innebærer å søke etter en verdi etter et kjent rad- og kolonnenummer. Med andre ord, du trekker ut celleverdien i skjæringspunktet mellom en bestemt rad og kolonne.
Så la oss gå til tabellen vår og skrive en formel med en funksjon VPR, som vil finne informasjon om prisen på sitroner som selges i mars.
Det er flere måter å utføre et XNUMXD søk på. Sjekk ut alternativene og velg det som passer deg best.
VLOOKUP og MATCH funksjoner
Du kan bruke en rekke funksjoner VPR (VLOOKUP) og MER UTSATT (MATCH) for å finne verdien i skjæringspunktet mellom feltene Название продукта (streng) og Måned (kolonne) for den aktuelle matrisen:
=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);ЛОЖЬ)
Formelen ovenfor er en vanlig funksjon VPR, som ser etter et eksakt samsvar med verdien "Sitroner" i cellene A2 til A9. Men siden du ikke vet hvilken kolonne marssalgene er i, vil du ikke kunne angi kolonnenummeret for det tredje funksjonsargumentet. VPR. I stedet brukes funksjonen MER UTSATTfor å definere denne kolonnen.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Oversatt til menneskelig språk betyr denne formelen:
- Vi ser etter karakterene "Mar" - argument søkeverdi (oppslagsverdi);
- Ser i celler fra A1 til I1 – argument lookup_array (oppslagsmatrise);
- Returnerer eksakt samsvar – argument match_type (match_type).
Ved hjelp av 0 i det tredje argumentet sier du funksjoner MER UTSATT se etter den første verdien som samsvarer nøyaktig med verdien du leter etter. Dette tilsvarer verdien FALSK (USANN) for det fjerde argumentet VPR.
Slik kan du lage en toveis søkeformel i Excel, også kjent som todimensjonalt søk eller toveissøk.
SUMPRODUCT-funksjon
Funksjon SUMPRODUCT (SUMPRODUKT) returnerer summen av produktene til de valgte matrisene:
=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 funksjoner
I den neste artikkelen vil jeg forklare disse funksjonene i detalj, så foreløpig kan du bare kopiere denne formelen:
=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))
Navngitte områder og kryssoperatoren
Hvis du ikke er interessert i alle de komplekse Excel-formlene, vil du kanskje like denne visuelle og minneverdige måten:
- Velg tabellen, åpne fanen Formler (Formler) og klikk Opprett fra utvalg (Opprett fra utvalg).
- Merk av i boksene Øverste rad (på linjen over) og Venstre kolonne (i kolonnen til venstre). Microsoft Excel vil tildele navn til områdene fra verdiene i den øverste raden og venstre kolonne i regnearket. Nå kan du søke med disse navnene direkte uten å lage formler.
- Skriv i hvilken som helst tom celle =radnavn kolonnenavn, for eksempel slik:
=Sitroner Mar
… eller vice versa:
=Mar Sitroner
Husk at rad- og kolonnenavnene må skilles med et mellomrom, som i dette tilfellet fungerer som skjæringsoperatoren.
Når du skriver inn et navn, vil Microsoft Excel vise et verktøytips med en liste over samsvarende navn, akkurat som når du skriver inn en formel.
- Press Enter og sjekk resultatet
Generelt, uansett hvilken av metodene ovenfor du velger, vil resultatet av et todimensjonalt søk være det samme:
Bruke flere VLOOKUPs i én formel
Det hender at hovedtabellen og oppslagstabellen ikke har en eneste kolonne til felles, og dette hindrer deg i å bruke den vanlige funksjonen VPR. Det er imidlertid en annen tabell som ikke inneholder informasjonen vi er interessert i, men som har en felles kolonne med hovedtabellen og oppslagstabellen.
La oss ta en titt på følgende eksempel. Vi har en hovedtabell med en kolonne SKU (ny), hvor du vil legge til en kolonne med tilsvarende priser fra en annen tabell. I tillegg har vi 2 oppslagstabeller. Den første (oppslagstabell 1) inneholder oppdaterte tall SKU (ny) og produktnavn, og den andre (oppslagstabell 2) – produktnavn og gamle nummer SKU (gammel).
For å legge til priser fra den andre oppslagstabellen til hovedtabellen, må du utføre en handling kjent som dobbel VPR eller nestet VPR.
- Skriv en funksjon VPR, som finner produktnavnet i tabellen Oppslagstabell 1ved hjelp av SKU, som ønsket verdi:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Her Ny_SKU – navngitt rekkevidde $A:$B i bordet Oppslagstabell 1, 2 – dette er kolonne B, som inneholder navnene på varene (se bildet over)
- Skriv en formel for å sette inn priser fra en tabell Oppslagstabell 2 basert på kjente produktnavn. For å gjøre dette, lim inn formelen du opprettet tidligere som oppslagsverdi for den nye funksjonen VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Her Pris – navngitt rekkevidde $A:$C i bordet Oppslagstabell 2, 3 er kolonne C som inneholder priser.
Figuren nedenfor viser resultatet returnert av formelen vi opprettet:
Dynamisk substitusjon av data fra forskjellige tabeller ved hjelp av VLOOKUP og INDIRECT
La oss først avklare hva vi mener med uttrykket "Dynamisk substitusjon av data fra forskjellige tabeller" for å sikre at vi forstår hverandre riktig.
Det er situasjoner når det er flere ark med data i samme format, og det er nødvendig å trekke ut nødvendig informasjon fra et bestemt ark, avhengig av verdien som er lagt inn i en gitt celle. Jeg tror det er lettere å forklare dette med et eksempel.
Tenk deg at du har salgsrapporter for flere regioner med de samme produktene og i samme format. Du vil finne salgstall for en bestemt region:
Hvis du bare har to slike rapporter, så kan du bruke en skammelig enkel formel med funksjoner VPR и IF (IF) for å velge ønsket rapport å søke 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 som inneholder navnet på produktet. Merk at vi bruker absolutte referanser her for å unngå å endre oppslagsverdien når du kopierer formelen til andre celler.
- 3 USD er en celle med navnet på regionen. Vi bruker en absolutt kolonnereferanse og en relativ radreferanse fordi vi planlegger å kopiere formelen til andre celler i samme kolonne.
- FL_Sales и CA_Salg – navnene på tabellene (eller navngitte områder) som inneholder de tilsvarende salgsrapportene. Du kan selvfølgelig bruke de vanlige arknavnene og celleområdereferansene, for eksempel 'FL Sheet'!$A$3:$B$10, men navngitte områder er mye mer praktisk.
Men når det er mange slike tabeller, vil funksjonen IF er ikke den beste løsningen. I stedet kan du bruke funksjonen INDIREKTE (INDIREKTE) for å returnere ønsket søkeområde.
Som du sikkert vet, funksjonen INDIREKTE brukes til å returnere en lenke gitt av en tekststreng, som er akkurat det vi trenger nå. Så bytt ut uttrykket med funksjonen i formelen ovenfor IF å koble til funksjon INDIREKTE. Her er en kombinasjon VPR и INDIREKTE fungerer utmerket 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 uendret på grunn av den absolutte koblingen.
- 3 USD er cellen som inneholder den første delen av regionnavnet. I vårt eksempel er dette FL.
- _Salg – den felles delen av navnet på alle navngitte områder eller tabeller. Når den kombineres med verdien i celle D3, danner den det fullstendige navnet på det nødvendige området. Nedenfor er noen detaljer for de som er nye i funksjonen INDIREKTE.
Hvordan INDIREKTE og VLOOKUP fungerer
Først, la meg minne deg på syntaksen til funksjonen INDIREKTE (INDIREKTE):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Det første argumentet kan være en cellereferanse (A1- eller R1C1-stil), et områdenavn eller en tekststreng. Det andre argumentet bestemmer hvilken lenkestil som finnes i det første argumentet:
- A1hvis argumentet er det SANN KODE (TRUE) eller ikke spesifisert;
- R1C1, Hvis FAS E (FALSK).
I vårt tilfelle har lenken stilen A1, så du kan utelate det andre argumentet og fokusere på det første.
Så la oss gå tilbake til salgsrapportene våre. Hvis du husker det, er hver rapport en egen tabell som ligger på et eget ark. For at formelen skal fungere riktig, må du navngi tabellene (eller områdene), og alle navnene må ha en felles del. For eksempel slik: CA_Salg, FL_Salg, TX_Salg og så videre. Som du kan se, er "_Sales" til stede i alle navnene.
Funksjon INDIREKTE kobler verdien i kolonne D og tekststrengen "_Sales", og forteller dermed VPR i hvilken tabell du skal søke. Hvis celle D3 inneholder verdien "FL", vil formelen søke i tabellen FL_Salg, hvis "CA" - i tabellen CA_Salg og så videre.
Resultatet av funksjonene VPR и INDIREKTE vil være følgende:
Hvis dataene er plassert i forskjellige Excel-bøker, må du legge til navnet på boken før det navngitte området, for eksempel:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Hvis funksjonen INDIREKTE refererer til en annen arbeidsbok, den arbeidsboken må være åpen. Hvis den er lukket, vil funksjonen rapportere en feil. #REF! (#SSYL!).