Introduktion til VLOOKUP -funktion
VLOOKUP -funktionen i Excel er uden tvivl den mest brugte og talte funktion. I ethvert jobsamtale, der kræver Excel -færdigheder, er dette det mest stillede spørgsmål, hvis du ved, hvordan du bruger VLOOKUP -funktionen.
VLOOKUP -funktionen hører under opslagskategorien excel -funktioner. V'en i VLOOKUP står for lodret. Funktionen bruges til at slå data op, der er struktureret lodret. Til vandret opslag er der en funktion kaldet HLOOKUP.
Excel VLOOKUP -funktion leder efter det første match af en given værdi kaldet opslagsværdi i en datatabel og returnerer værdien fra det givne kolonneindeks. Kampen kan være omtrentlig eller præcis.
Selvom VLOOKUP er let at bruge, har det sine egne begrænsninger og baggrunde. Vi vil diskutere alt inklusive funktionens styrker og svagheder.
Syntaks:
= VLOOKUP (opslagsværdi, tabel_array, col_index_number, [interval_lookup])
Hvad er opslagsværdi
Opslag_værdi: Det er den værdi, du vil kigge efter i et tabel array.
For eksempel, hvis du leder efter rulle nr. 110 i elevens bord derefter dens 110 er opslagsværdi.
Table_array: Tabellen, hvorfra du vil søge efter opslagsværdi.
For eksempel, hvis du leder 110 i tabellen array B5: E17. Derefter B5: E17 er dit bordarray.
col_index_number: Kolonnetallet i tabel -array, hvorfra du vil hente resultater.
For eksempel hvis det er i tabel array B5: E17 du vil have værdi fra kolonne D så vil dit kolonneindeks være 3 (tæller fra B til D (B, C, D)).
[range_lookup]: FALSKT, hvis du vil søge efter den nøjagtige værdi, SAND, hvis du vil have et omtrentligt match.
Sådan bruges VLOOKUP -funktionen
Når du har en struktureret data, og du vil søge efter data i disse data, er VLOOKUP -funktionen løsningen.
VLOOKUP matchende tilstande
VLOOKUP har to matchningstilstande, omtrentlig match og præcis match. Som standard udfører VLOOKUP et omtrentligt match. Men hvis du vil tvinge VLOOKUP -funktionen til at foretage et eksakt match, kan du også gøre det. De fleste gange, jeg og jeg tror stærkt på, at andre også prøver at lave en nøjagtig match ved hjælp af VLOOKUP -funktionen. Jeg forstår ikke, hvorfor excel -udviklere tror, at brugere mest gerne vil bruge den omtrentlige match.
1. VLOOKUP Omtrentlig match
= VLOOKUP (opslagsværdi, tabel_array, col_index_number, 1)
I VLOOKUP omtrentlig match, hvis en værdi ikke findes, så er sidste værdi, der er mindre end opslagsværdien, matches, og værdien fra et givet kolonneindeks returneres.
VLOOKUP som standard matcher omtrentligt, hvis vi udelader variabel for opslag i rækkevidde. En omtrentlig match er nyttig, når du vil lave en omtrentlig match og når du har dit bord array sorteret i stigende rækkefølge.
Vlookup leder efter værdien, og hvis den ikke finder værdien i tabel array, matcher den værdien, der er mindre end den værdi i tabell array. Lad os forstå det ved et eksempel.
Eksempel 1
Vi har en elevliste her. Hver elev har scoret nogle karakterer i en test. Nu vil jeg give karakter i henhold til deres karakterer.
Enhver elev, der scorede mindre end 40, skal markeres F, elev, der scorer mellem 40 og 60, skal markeres C osv. Som vist på billedet herunder.
Vi ville skrive denne VLOOKUP -formel i E2 og trække den ned.
= OPLYSNING (D2, $ H $ 2: $ I $ 6,2, SAND)
Hvordan det virker?
I ovenstående eksempel er vores opslagsværdier i kolonne D, startende fra D2. Tabellen Array er H2: I6 (bemærk, at den er sorteret i stigende rækkefølge og absolut). Den kolonne, vi får data fra, er 2. Og matchtype, vi har defineret omtrentligt ved at sende SAND (intet og 1 vil betyde det samme).
Så lad os undersøge frist -opslagsværdien D2, der indeholder 40.
- VLOOKUP søger efter 40 i første kolonne (H) i tabel array H2: I6.
- Den finder 40 på anden position i celle H3.
- Nu flytter den til anden kolonne fra H3 til I3 og returnerer D.
I dette tilfælde fandt vlookup det nøjagtige match. Lad os se næste sag.
Den anden opslagsværdi D3, der indeholder 36.
- VLOOKUP søger efter 36 i første kolonne i tabel array H2: I6.
- VLOOKUP kunne ikke finde 36 nogen steder i første kolonne.
- Da VLOOKUP ikke kunne finde 36, den matcher den med sidst fundet værdi, der er mindre end opslagsværdien.
- Den første værdi, der er mindre end 36, er 0, derfor returnerer den F, der er relateret til 0.
Dette sker for hvert tilfælde her. For 92 er sidste værdi, der er mindre end 92, 90. Derfor returneres A for omtrentlig match.
2. VLOOKUP Præcis match
Denne mest brugte form for VLOOKUP og kan også være mest nyttig. Når du vil lede efter den nøjagtige værdi i første kolonne i tabellarrayet, bruger du nøjagtig match. For eksempel, hvis du laver et opslag fra et ID, end du allerhelst vil have et eksakt match i stedet for en værdi, der er mindre end denne værdi.
For at tvinge VLOOKUP til et eksakt match i excel sender vi 0 eller FALSE som parameter_lookup parameter.
= VLOOKUP (opslagsværdi, table_array, col_index_number, 0)
Hvis værdien ikke findes i første kolonne i tabel array, returnerer formlen #N/A fejl.
Lad os se et eksempel.
Eksempel 2
I dette eksempel vil jeg kun skrive rullelement af studerende i A2 og i B2 vil jeg hente Elevens navn og i C2 hans karakter. Enkel. Tabellen array er B5: D17. Hvorfor?
Så for at gøre dette skal du skrive disse to formler i cellen i henholdsvis celle B2 og C2.
= VLOOKUP (A2, $ B $ 5: $ E $ 17,2,0)
= VLOOKUP (A2, $ B $ 5: $ E $ 17,3,0)
Uanset hvilket ID du skriver i celle A2, søger VLOOKUP -funktionen efter det nøjagtige match i kolonnen B og returnerer værdien fra den givne col_index.
Hvordan fungerer eksakt match VLOOKUP?
Exact Match VLOOKUP i excel er enkel. Det leder efter opslagsværdien i første kolonne i et givet tabel_array og returnerer værdien fra værdien fra det givne kolonneindeksnummer. Hvis værdien ikke findes, returnerer VLOOKUP #N/A fejl.
Bedste anvendelser af VLOOKUP -funktion
Vlookup har mange anvendelsesmuligheder. Jeg vil diskutere nogle mest nyttige brugssager.
3. Brug Excel VLOOKUP til bedømmelse i stedet for kompleks indlejret IF -funktion
Brug VLOOKUP omtrentlig match til at erstatte indlejret IF -funktion. I eksempel 1 så vi, hvordan vi kan bruge vlookup til at bedømme elever i henhold til deres karakterer.
Den omtrentlige matchning af VLOOKUP er hurtigere end indlejret, hvis den bruger binær søgning internt.
4. Brug VLOOKUP til at hente data
Denne mest almindelige opgave, der let kan udføres ved hjælp af VLOOKUP -funktion i Excel. I eksempel 2 gjorde vi dette for at hente elevers data ved hjælp af deres roll nr.
5. Kontroller, om der er en værdi i en post ved hjælp af Vlookup.
VLOOKUP kan bruges til at kontrollere, om en værdi er på en liste eller ej.
I eksempel 2, når vi skriver 152, returnerer det #N/A fejl. Hvis VLOOKUP returnerer #N/A, betyder det, at den ikke fandt den givne værdi nogen steder i givne data.
Jeg har udarbejdet en kort artikel om dette Du kan læse det her.
Brug af en VLOOKUP -formel til at kontrollere, om der findes en værdi
Få kolonneindeks automatisk
Jeg tilhører den gruppe mennesker, der ikke ønsker at gentage den samme opgave igen og igen. Så jeg irriterer mig igen og igen ved at ændre col_index -nummer, og jeg undgår det altid. Faktisk undgår jeg enhver form for redigering i mine formler, når jeg har skrevet det, medmindre og indtil der ikke er nogen anden mulighed.
Kolonneindekset kan beregnes automatisk i Excel. Der er flere måder at gøre det på. Lad os se nogle af dem her.
6. Brug VLOOKUP -funktion med MATCH -funktion
Så som vi ved, at MATCH -funktionen returnerer det fundne indeks for matchet værdi.
Så hvis vi har nøjagtig samme overskrifter på opslagsplacering som kildedata, end vi kan bruge til at få col_index. Hvordan? Lad os se…
I nedenstående data vil vi bare skrive rulle nummer i G2 og vil hente elevens navn, karakterer og karakter i H2, uanset hvilken overskrift vi skriver der.
Skriv denne formel i celle H2
= OPLYSNING (G2, B2: E14, MATCH (H1, B1: E1,0), 0)
Når du nu ændrer overskrift i H1, vil værdien i H2 blive vist fra den kolonne.
Du kan bruge en rullemenu ved hjælp af datavalidering, der alle har overskrift fra tabellen, for at gøre den mere brugervenlig.
Her laver VLOOKUP nøjagtig match. Nu gør VLOOKUP sine sædvanlige ting med at trække data. Magien udføres af MATCH -funktionen ved col_index -position.
Her ser VLOOKUP efter enhver værdi i G2 i første kolonne i tabel array B2: E14. Nu på stedet for col_index har vi MATCH (H1, B1: E1,0).
MATCH -funktion søger enhver værdi i H1 inden for rækkevidde B1: E1 og returnerer indeks for matchværdi.
For eksempel når vi skriver studerende i H1 ser det efter det inden for rækkevidde B1: E1. Det findes i C2. derfor returnerer den 2. Hvis vi skriver karakter det vil returnere elevens karakter.
Bemærk, at når vi skriver Region, returnerer den #N/A. Fordi det er uden for omfanget af tabellen. Vi vil tale om det sidstnævnte i artiklen.
Dette er den bedste måde at gøre VLOOKUP automatisk. Der er andre metoder til, men de er ikke så fleksible end dette.
7. Brug VLOOKUP -funktion med COLUMN -funktion
Kolonnefunktionen returnerer kolonnummeret for den medfølgende reference. Og hvad har vi brug for til at hente data ved hjælp af VLOOKUP? Col_index. Så ved hjælp af COLUMN -funktionen kan vi naturligvis hente data fra ethvert datasæt.
Lad os tage ovenstående eksempel. Men nu skal vi hente hele data. Ikke kun en kolonne.
Skriv denne formel i H2 og kopier i I2 og J2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, COLUMN (B1), 0)
Dette vil give dig et dynamisk resultat. Hvordan? Lad os se.
VLOOKUP fungerer som normalt. På column_index har vi skrevet KOLONNE (B1), som vil oversætte til 2, da vi har elevnavn i 2 kolonne fra kolonne B, returnerer det elevens navn.
Vigtig: Dette fungerer, fordi vi har vores tabel fra B -kolonne. Hvis dit bord er midt på arket, skal du trække fra eller tilføje nogle tal, så det matcher dit krav.
For eksempel, hvis ovenstående tabel startede fra C1 i samme struktur, fungerede alle nedenstående formler fint.
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (B1), 0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (C1) -1,0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (A1) +1,0)
8. VLOOKUP for at flette data
I de fleste databaser er der en forespørgsel, der fusionerer til tabeller, for det meste kaldet en joinforespørgsel, men MS Excel i ingen database, derfor har den ikke joinforespørgsel. Men det betyder ikke, at vi ikke kan forbinde to tabeller, hvis vi har nogle fælles kolonner i to tabeller.
Så når du får nogle data fra to afdelinger i samme virksomhed, vil du gerne flette dem for at analysere disse data.
For eksempel, når du får karakterer af studerende fra lærere og derefter fremmødesrekord fra administrativ afdeling, vil du gerne se, hvilken elev der fik hvilken karakter, og hvad hans deltagelsesprocent er, ét sted.
Vi skal flette dem i nedenstående tabel.
Mærker, vi skal komme fra mærketabellen og fremmøde fra fremmødedata.
Skriv nedenfor formler i celle D19 for at få mærker og trække ned.
= VLOOKUP (B19, $ B $ 2: $ D $ 15,3,0)
Skriv under formlen i celle E19
= VLOOKUP (B19, $ G $ 3: $ I $ 15,3,0)
Og vi har flettet to borde til et bord. Du kan naturligvis tilføje mere end to borde. Du skal bare hente data fra alle de tabeller, du ønsker, ét sted ved hjælp af VLOOKUP.
Bedste praksis med VLOOKUP
Så alle ovenstående eksempler brugte vi lige vlookup med rådata. I alle ovenstående eksempler var vi forsigtige med de data, vi brugte. Vi skulle være forsigtige, mens vi gav referencer til bordarrays. Men der er visse måder, der kan reducere denne indsats i vid udstrækning.
9. Brug absolutte referencer med VLOOKUP
Du har måske bemærket, at jeg i nogle artikler har brugt absolutte intervaller (område $ tegn). Absolut raseri bruges, når vi ikke ønsker, at rækkevidde skal ændres, mens formelcelle kopieres til andre celler.
For eksempel, hvis jeg har = VLOOKUP (G2,B2: E14, 2,0), 0) i celle H2, og hvis jeg kopierer eller trækker den ned i celle H3, ændres formlen til = VLOOKUP (G3,B3: E15, 2,0). Alle referencer ændres relativt. Her vil vi måske ændre referencen for opslagsværdi, men ikke tabel arrayet. Fordi B2 kommer ud eller rækker, og vi måske altid vil have det i vores bordarrays.
Så for at forhindre, at vi bruger absolutte referencer. Vi gør disse intervaller absolutte, som vi ikke vil ændre, som tabel_array i H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). Når du kopierer H2 i H3, vil formlen være = VLOOKUP (G3,$ B $ 2: $ E $ 14, 2,0), 0). Bemærk, at opslagsværdien er ændret, men ikke table_array.
10. Brug navngivne intervaller med VLOOKUP
I ovenstående eksempel brugte vi absolut henvisning til fastsættelse af table_array. Det ser ganske uklart og svært at læse. Hvad med hvis du bare kan skrive 'data' i stedet for $ B $ 2: $ E $ 14 og det ville henvise til $ B $ 2: $ E $ 14 altid. Det vil gøre det endnu lettere at læse og skrive formlen.
Så vælg bare området og navngiv det 'data'.
Vælg området for at navngive et område. Højreklik, klik på Definer navn, og navngiv det område, du ønsker. Nu bare = VLOOKUP (G3,data,2,0), 0) formlen fungerer nøjagtigt som tidligere.
Du kan læse mere om navngivne områder i Excel. Jeg har forklaret hvert eneste aspekt af navngivet område her.
11. Jokertegn til delvis matchning ved hjælp af VLOOKUP
VLOOKUP tillader delvis matchning. Ja, mens du laver den nøjagtige matchning af VLOOKUP, kan du bruge jokertegnoperatører til at foretage delvis matchning. Hvis du f.eks. Vil slå en værdi op, der starter med 'Gil', kan du bruge wildcard -operator * med 'Gil' som "Gil *". Lad os se et eksempel.
Her vil jeg få karakterer af studerende, hvis navn starter med Gil. For at få det vil jeg skrive nedenstående formel.
= OPLYSNING ("*Gill", C2: D14,2,0)
Der er to jokertegn tilgængelige i excel til brug med VLOOKUP -funktion.
Stjernetegn (*) jokertegn. Dette bruges, når brugeren ikke kender antallet af tegn, der er, og kun kender nogle matchende tegn. Hvis brugeren f.eks. Kender et navn, der starter med "Sm", skriver han "Sm*". Hvis brugeren ved, at en opslagsværdi, der ender med "123", skriver han "*123". (wild card fungerer kun med tekster). Og hvis han ved, at “se” kommer inde i en tekst, så skriver han “*se*”.
Spørgsmålstegn ("?"). Dette bruges, når brugeren kender antallet af tegn, der mangler. For eksempel, hvis jeg vil slå en værdi op, der har 4 tegn i den, men jeg ikke ved, hvad de er, vil jeg simpelthen skrive "????" på stedet for opslagsværdien.
Vlookup returnerer den respektive værdi af først fundet værdi, der har præcis fire tegn i den.
Vigtig: Wild card fungerer kun med tekstværdier. Konverter tal til tekst, hvis du vil bruge jokertegn med dem.
Svaghederne ved VLOOKUP -funktionen
VLOOKUP er virkelig en kraftfuld og let funktion til at hente værdier, men der er mange områder, hvor VLOOKUP bare ikke er så nyttigt. Hvis du arbejder på excel, skal du også kende dem, og hvordan du udfører opgave, som VLOOKUP ikke kan udføre.
12. Kan ikke hente værdi fra venstre i Table_Array
Den største fejl ved VLOOKUP -funktionen er, at den ikke kan hente værdi fra venstre i tabellarrayet. VLOOKUP søger kun efter opslagets værdi.
Dette er fordi, VLOOKUP søger efter givet opslagsværdi i den givne første kolonne bord array. Det vil aldrig returnere en værdi uden for bordet. Og hvis du prøver at beholde venstre kolonne i tabel array, bliver det den første kolonne i tabel array, derfor vil opslagsværdi blive søgt i dette område. Hvilket sandsynligvis vil generere fejl.
For at opslå værdier fra venstre for en opslagsværdi bruger vi i stedet INDEX-MATCH. INDEX-MATCH-funktionen kan slå værdier op fra enhver kolonne på ark. Faktisk er opslagsområde og det område, hvorfra du vil søge værdier, helt uafhængige, hvilket gør indeksmatch meget tilpasselig.
VLOOKUP returnerer #N/A, selv når der findes opslagsværdi.
#N/A -fejl returneres af VLOOKUP -funktionen, når opslagsværdien ikke findes. Du kan finde det irriterende, når der findes en værdi i området, men VLOOKUP returnerer #N/A fejl.
14. Når tal formeres som tekst
Dette sker for det meste, når tal er formateret som tekst. Når du finder brug af CTRL+F kommando, vil excel finde det, men når du prøver at bruge VLOOKUP, returnerer det #N/A. Denne opgave er givet i de fleste interviews som trick -spørgsmål.
I billedet ovenfor kan du se, at 101 er lige der, men formlen returnerer en fejl. Hvordan håndterer du det? Der er to metoder.
1. konverter tekst til nummer i data
Du kan se det grønne mærke i venstre øverste hjørne i rollno -kolonnen. De angiver, at noget er usædvanligt i værdien af celle. Når du markerer cellen, viser det, at nummeret er formateret som tekst.
Når du klikker på udråbstegnet (!), Viser det dig muligheden for Konverter til nummer. Marker alle celler, og klik på denne mulighed. Alle værdier konverteres til tekst.
2. Konverter dit opslagsnummer til tekst i formlen
I ovenstående eksempel muterede vi originaldata. Du vil muligvis ikke ændre noget i originaldata. Så du vil gøre dette i formel i stedet. For at ændre nummer til tekst i VLOOKUP -formel skal du skrive dette.
= OPLYSNING (TEKST (G2, "0"), B2: D14,2,0)
Her ændrer formel dynamisk tal til tekst og matcher det derefter med et givet område.
15. Tekst med ledende og efterfølgende mellemrum
Nogle data fra operatører af dataindtastning og data fra internettet er ikke rene. De kan have efterfølgende mellemrum eller nogle tegn, der ikke kan udskrives. Når du laver opslag ved hjælp af disse værdier, kan du se en #N/A -fejl. For at undgå dette skal du først rense dine data. Brug TRIM -funktionen til at fjerne mellemrum. TRIM fjerner et vilkårligt antal ledende eller efterfølgende mellemrum fra tekst.
Så jeg foreslår, at du tilføjer en ny kolonne og får dine rensede data der. Værd derefter indsæt den. Hvis du vil, kan du nu slippe af med den originale kolonne.
= TRIM (RENGØR (tekst))
Håndteringsfejl ved VLOOKUP
Som du ved, finder VLOOKUP ikke en værdi, den returnerer en #N/A fejl. Godt det er ok at få #N/A fejl, måske har du nogle gange tænkt dig at få det, f.eks. Når du vil kontrollere, om a -værdien allerede findes på listen eller ej. Der betyder #N/A, at værdien ikke er der.
Men #N/A ser grimt ud. Hvad med at få et brugervenligt output, f.eks. "Id ikke fundet" eller "Kunden blev ikke fundet". Vi kan bruge IFERROR med VLOOKUP -funktion for at undgå #N/A.
Brug nedenstående formel til at fange fejl i VLOOKUP.
= HVISFEJL (VLOOKUP (150, B2: E14,2,0), "Rulle findes ikke")
16. VLOOKUP med MULTIPLE Criterias
VLOOKUP kan ikke fungere med flere kriterier.Ja, du kan kun have ét kriterium for at slå værdier op ved hjælp af VLOOKUP -formlen.
For eksempel, hvis du har fornavn og efternavn i to separate kolonner.
Og nu hvis du vil slå værdi op, hvis fornavn er John og efternavn Dave, så kan du ikke gøre det normalt.
Men der er en løsning på dette. Du kan oprette en separat kolonne ved at sammenkæde fornavn og efternavn og derefter slå op på den for- og efternavnskolonne.
Der er en INDEX-MATCH alternativ, der kan slå flere kriterier op uden hjælpende kolonne.
17. VLOOKUP Henter kun først fundet værdi
Forestil dig, at du har nogle data i den centrale region. Nu vil du få de første 5 dataregistre i den centrale region. VLOOKUP returnerer kun den første centrale værdi i alle fem poster. Dette er en vigtig baggrund.
Men det betyder ikke, at vi ikke kan opnå dette. Vi kan bruge en kompleks matrixformel som nedenfor.
= INDEX ($ C $ 2: $ C $ 14, LILLE (HVIS (G2 = $ A $ 2: $ A $ 14, RÆK ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1), RÆK (1: 1)))
Skriv denne formel, og tryk på CTRL+SKIFT+ENTER.
Og det er gjort.
Jeg har forklaret det i artiklen, hvordan man LOOKUP flere værdier i Excel.
Så ja fyre, i denne artikel har jeg dækket alle mulige aspekter af VLOOKUP -funktion efter min viden. Hvis du tror, jeg har savnet noget, så lad mig det vide i kommentarfeltet herunder.
Vlookup Top 5 værdier med dublerede værdier ved hjælp af INDEX-MATCH i Excel
VLOOKUP Flere værdier
VLOOKUP med Dynamic Col Index
Delvis match med VLOOKUP -funktion
Brug VLOOKUP fra to eller flere opslagstabeller
Vlookup efter dato i Excel
Brug af en VLOOKUP -formel til at kontrollere, om der findes en værdi
Sådan VLOOKUP fra forskellige Excel -ark
VLOOKUP med tal og tekst
IF, ISNA og VLOOKUP funktion
ISNA og VLOOKUP funktion
IFERROR og VLOOKUP funktion