Share
EN VLOOKUP, kort for "vertikal oppslag" er en formel i Microsoft Excel for å matche data fra to lister. I stedet for å hoppe mellom regneark og skrive ut dine matchende data, kan du skrive en VLOOKUP formel for å automatisere prosessen.
Kombinere to lister er en perfekt situasjon for å bruke en VLOOKUP. Til venstre (på bildet over) har vi informasjon om ansattes skift. Vi ønsker å legge til ansattes jobbtittel på skiftdataene. Med en egen liste over ansatte og deres jobbtitler kan vi skrive en VLOOKUP-formel for å trekke tittelen fra en oppslagsliste.
En vellykket VLOOKUP trenger tre ting:
VLOOKUP er en Microsoft Excel-formel som er viktig for å jobbe med flere datasett. I denne opplæringen skal jeg lære deg hvordan du skal mestre og bruke den.
Eksempel VLOOKUP formel brukes til å slå opp ansattes data.Ved å bruke eksemplet ovenfor har jeg nå skrevet en VLOOKUP-formel som ser opp ansattes ID og legger inn jobbtittelen i skiftdataene. Fordi begge arkene har en ansatt-ID, kan Excel se på den tilsvarende jobben. Den beste delen av VLOOKUP er at jeg nå kan dra den samme formelen ned og det vil slå opp hver unikt jobbtittel.
Før vi går videre, må du sørge for Last ned gratis vedlegg å følge med. Det er et eksempel regneark arbeidsbok som jeg opprettet, som vi skal bruke for å gå gjennom denne opplæringen.
For den raskeste måten å lære grunnleggende av VLOOKUP-formelen, sjekk ut skjermbildet nedenfor. Videoen går gjennom flere eksempler på VLOOKUP-formelen, ved hjelp av eksempelboken.
Fortsett å lese for å gå gjennom de skriftlige instruksjonene, og lære noen ekstra teknikker som ikke er dekket av skjermbildet.
Bruk fanene "Ingredient Orders" og "Supplier List" for dette Excel VLOOKUP eksempelet.
La oss si at vi forvalter en restaurant og legger våre ukentlige bestillinger til leverandører. Kokkene har gitt oss en liste over ingredienser å bestille, og vi må legge inn informasjon om leverandøren.
Det er tre stykker vi må legge til for hver ordre:
I denne arbeidsboken er det to kategorier:
Øverst har vi våre bestillingsdata, som står på fanen "Ingredient Orders". Nedenfor er "Leverandørlisten", som vil fungere som vår oppslagsliste.Det felles feltet mellom de to tabellene er Ingrediens fane. La oss bruke den til å lete opp hver av de tre feltene og legge den til ordrelisten.
På Ingredientbestillinger kategorien, la oss klikke i den første tomme leverandørcellen, F5, og trykk på likestegnet for å starte VLOOKUP-formelen. Deretter skriver du " VLOOKUP ( " å starte formelen.
= FINN.RAD (
Husk at vår primære nøkkel - datafeltet som vises i begge lister - er ingrediens, så vi bruker den til oppslaget. Klikk enten på cellen B5, eller skriv den inn i formelen. Deretter legger du til et komma etter "B5" slik at vi kan gå inn i neste del av formelen.
= FINN.RAD (B5,
Nå må vi gi formelen vår oppslagsliste. Med formelen fortsatt åpen, klikk på Leverandørliste fane. Klikk nå på celle A3, og klikk og dra for å utheve og velg A3 til G13, hele oppslagstabellen. Pass på og trykk de F4-tast på tastaturet å gjøre formelen til en absolutt referanse (mer om dette senere). Til slutt, skriv inn et nytt komma.
Pek Excel til oppslagslisten.Når du har angitt kommaet for oppslagcellen, bytter du faner og peker Excel til oppslagslisten. Klikk og dra mellom cellene A3 og G3 for å velge dataene du vil søke etter fra. Pass på og trykk F4 på tastaturet ditt under dette trinnet for å opprette en absolutt referanse, som vil låse i cellene som skal brukes til oppslaget.
= VLOOKUP (B5, 'Leverandørliste'! $ A $ 3: $ G $ 13,
Neste opp, må vi fortelle Excel hvilken kolonne du skal trekke fra. Husk at vårt første element å sette inn er Leverandør navn, som er i den andre kolonnen i oppslagslisten. Legg til nummeret 2 til formelen for å trekke fra den andre kolonnen av oppslaget, og et annet komma.
= VLOOKUP (B5, 'Leverandørliste'! $ A $ 3: $ G $ 13,2,
Til slutt legger vi til FALSE for et nøyaktig oppslag, og lukker deretter parentesene:
= VLOOKUP (B5, 'Leverandørliste'! $ A $ 3: $ G $ 13,2, FALSE)
Vår formel fungerer perfekt, drar i leverandøren for poteter. Den gode nyheten er at vi ikke trenger å omskrive denne samme formelen om og om igjen - bare dobbeltklikk i nederste høyre hjørne av cellen F5 (Du kan se den lille grønne boksen på hjørnet av cellen) For å forlenge formelen ned (som vist ovenfor).
Formelen fungerer perfekt! Ok, nå la oss fortsette å trekke inn dataene for de to andre feltene: leverandørens telefonnummer og leveringsdag.
Fordi vi brukte en absolutt referanse, kan vi i utgangspunktet gjenbruke den samme formelen vi skrev med en mindre tweak. La oss legge til i leverandørens telefonnummer neste.
Vi starter med å kopiere og lime inn cellen F5 (vår leverandørcelle) til celle G5. Jeg bruker vanligvis bare hurtigtastene Ctrl + C og Ctrl + V å kopiere og lime inn hele cellen.I begynnelsen vil dette ikke fungere, og du vil se en N / A i cellen.
Kopier og lim inn oppslag med justeringer i Excel.Det er mye lettere å kopiere og lime inn vår formel i en annen celle, men det krever litt justering. Først vil Excel se på celle C5 for oppslaget, men vi må justere det til "B5" i formellelinjen. Når vi gjør det, vil oppslaget fungere - nesten.
Vi må gå opp til formellinjen, og endre den første delen av formelen fra C5 tilbake til B5. Da vi flyttet formel over en kolonne, Excel oppdaterte andre deler av formelen. Vi fikk en "N / A" fordi Excel forsøkte å matche mengden (kolonne C) til vår oppslagsliste, men vår oppslagsliste inneholder ikke mengden.
Så langt må vår formel være:
= VLOOKUP (B5, 'Leverandørliste'! $ A $ 3: $ G $ 13,2, FALSE)
Vær imidlertid oppmerksom på skjermbildet ovenfor at vi trekker feil data i "Telefon" kolonnen. Vi drar fortsatt den andre kolonnen i oppslagslisten med "2" i formelen. Vi må bytte dette til kolonnnummeret med leverandørens kontakttelefon.
La oss gå tilbake og sjekke ut oppslagslisten. Du vil legge merke til at telefonnummeret er i den syvende kolonnen i oppslagslisten. La oss gå tilbake til vår Excel-formel og oppdatere oppslaget for å trekke fra den syvende kolonnen.
Vi må oppdatere "2" i vår Excel-formel til en "7" for å trekke fra den syvende kolonnen i vår oppslagsliste.Alt vi trenger å gjøre er å oppdatere kolonneavsnittet i vår oppslagsliste fra en "2" til en "7", og det fungerer nå bra!
Legg merke til at leverandørtelefonen fungerer bra nå, da vi har oppdatert vår formel for å bruke den syvende kolonnen fra oppslagslisten. Nå, bare dra formelen ned for å oppdatere den for alle celler.Vår siste formel for leverandørens telefonnummeroppslag:
= VLOOKUP (B5, 'Leverandørliste'! $ A $ 3: $ G $ 13,7, FALSE)
Til slutt, la oss jobbe i Leverandør leveringsdag kolonnen. Kopier og lim inn celle G5 til celle H5. Igjen må vi fikse vår formel ved å endre C5 tilbake til B5 å bruke ingrediens som primærnøkkel. Deretter oppdaterer du "7" til en "5" for å bruke den femte kolonnen fra vårt oppslagstabell.
Endelig leverandør levering dag oppslag formel brukes.Vår siste formel for leverandørens leveringsdag:
= VLOOKUP (B5, 'Leverandørliste'! $ A $ 3: $ G $ 13,5, FALSE)
Det er det! Vi skrev i utgangspunktet en formel og tweaked den for å trekke inn alle dataene vi trenger for å plassere vår neste bestilling.
Så, du har skrevet VLOOKUP-formelen etter instruksjonene trinn for steg, men den fungerer fortsatt ikke. Excel tar ting ganske bokstavelig talt, så vi må være forsiktige med dataene og VLOOKUP-formelen. La oss ta en titt på flere ideer for å korrigere en VLOOKUP-formel som bare ikke fungerer.
Et av de vanligste problemene med VLOOKUP er når det er flere kamper i oppslagslisten din. Når du bruker en VLOOKUP, vil den matche til det første elementet som er i listen.
Våre VLOOKUP sier at Carrie Richard er direktør for markedsføring, men vi vet at hun nylig ble promotert til bedriftspresidenten. Hvorfor fungerer ikke vår oppslag?
Whoops, det ser ut til at det var to oppføringer for Carrie Richard i oppslagslisten - en for direktør for markedsføring, og en for president. Når vi slettet linjen "Direktør for markedsføring" fra oppslagslisten, er dataene våre riktige.Poenget med VLOOKUP er å slå opp et matchende element mot en liste. Oppslagslisten bør ikke inneholde duplikater for primærnøkkel, elementet du bruker for å matche. Ellers blir Excel forvirret og viser bare den første kampen.
Et annet vanlig problem er at våre data kanskje ikke samsvarer i Excels øyne. Data med et mellomrom før det sies å ha et "ledende" mellomrom, mens data med et mellomrom etter at det har en "bakre plass".
Denne VLOOKUP er perfekt skrevet, men det virker ikke. Du kan tydeligvis se navnet er i oppslagslisten, men Excel returnerer ikke en kamp. Les videre for å finne ut hvorfor.Hvis ditt matchende datafelt har en plass før eller etter det, ser Excel disse to stykkene av data som helt forskjellige, og vil ikke returnere en kamp. Excel-visninger "_Andrew", "Andrew_" og "Andrew" som tre unike data-deler som ikke vil bli matchet i VLOOKUP.
Det viser seg at det er et "bakrom" eller en enkelt plass etter navnet i cellen. Det er umulig å se, men det kan ødelegge en VLOOKUP fordi Excel behandler "Alyssa Reddall" og "Alyssa Reddall (space)" annerledes. Når du har slettet plassen, vil VLOOKUP fungere normalt.Hvis dataene dine har ledende eller bakre mellomrom, bruker du TRIM Funger i Excel for å rydde den opp. VLOOKUP skal fungere bra etter at du har brukt det for å fjerne ledende og etterfølgende mellomrom.
Som du kanskje vet, kan du dra en celle ned ved håndtaket for å lime inn formelen i andre celler. Dette bryter imidlertid noen ganger vår VLOOKUP fordi celledirekslene endres.
I dette skjermbildet trakk jeg VLOOKUP nedover listen for å bruke den til andre celler. Vær imidlertid oppmerksom på at oppslagreferansen endret fra A2 til B15, til A16 til B29, derfor hvorfor formelen ikke virker. Min oppslagsliste er faktisk i A2 til B15, så oppslaget er nå ødelagt.Når du trekker formlene ned, trekkes referansen til oppslagstabellen ut av justering. Plutselig mangler oppslagstabellen rader fra oppslagstabellen, og vår VLOOKUP virker ikke.
Løsningen er å gjøre formelen an absolutt referanse, slik at når du drar formelen nede, endrer listen som det peker på, ikke. Klikk i cellen der du har skrevet VLOOKUP, og klikk deretter et sted i oppslagslisten. Deretter trykker du på F4 . Du vil merke at formelen endres for å inkludere dollarskilt.
Legg merke til at formelen (vist øverst på dette skjermbildet i formellelinjen) nå inneholder dollarskilt i oppslagslisten, som viser at den bruker en absolutt referanse. Nå, når jeg drar formelen ned, låses det formelen for oppslagslisten og fungerer perfekt.VLOOKUP er en av de essensielle formlene for å være en produktiv Excel-bruker. Det er rett og slett ikke nok tid til å manuelt slå opp data og skrive den om og om igjen, så formler som VLOOKUP er viktige for å lære.
Hvis du har noen problemer med å bruke VLOOKUP-formelen, vær så snill å legge igjen en kommentar nedenfor for feilsøking.
Accentsconagua