Share
en VLOOKUP, Kort för "vertikal uppslag" är en formel i Microsoft Excel för att matcha data från två listor. I stället för att hoppa mellan kalkylblad och skriva ut dina matchande data kan du skriva en VLOOKUP-formel för att automatisera processen.
Att kombinera två listor är en perfekt situation att använda en VLOOKUP. Till vänster (i bilden ovan) har vi anställningsskiftinformation. Vi vill lägga till medarbetarens jobbtitel till skiftdata. Med en separat lista över anställda och deras jobbtitlar kan vi skriva en VLOOKUP-formel för att dra in titeln från en sökningslista.
En lyckad VLOOKUP behöver tre saker:
VLOOKUP är en Microsoft Excel-formel som är nödvändig för att arbeta med flera datasatser. I denna handledning lär jag dig hur du ska behärska och använda den.
Exempel VLOOKUP formel används för att leta upp anställda data.Med hjälp av exemplet ovan har jag nu skrivit en VLOOKUP-formel som tittar upp medarbetarens ID och sätter in jobbet i växlingsdata. Eftersom båda arken har ett anställd-ID kan Excel leta upp matchande jobbtitel. Den bästa delen av VLOOKUP är att jag nu kan dra samma formel ner och det kommer att se upp varje unikt jobbtitel.
Innan vi fortsätter, se till att Ladda ner gratis bilagan att följa med. Det är ett exempel på en kalkylark-arbetsbok som jag skapade, som vi ska använda för att gå igenom denna handledning.
För det snabbaste sättet att lära sig grunderna i VLOOKUP-formuläret, kolla in skärmbilden nedan. Videon går igenom flera exempel på VLOOKUP-formeln, med hjälp av exempelboken.
Fortsätt läsa för att gå igenom de skriftliga instruktionerna, och lära dig några ytterligare tekniker som inte omfattas av skärmen.
Använd flikarna "Ingredient Orders" och "Supplier List" för detta Excel VLOOKUP exempel.
Låt oss säga att vi hanterar en restaurang och lägger våra veckopriser till leverantörer. Kockarna har gett oss en lista över ingredienser att beställa, och vi måste infoga information om leverantören.
Det finns tre stycken vi behöver lägga till för varje beställning:
I den här arbetsboken finns det två flikar:
Överst har vi vår orderdata, som finns på fliken "Ingredient Orders". Nedan är "Leverantörslistan", som kommer att fungera som vår söklista.Det gemensamma fältet mellan de två tabellerna är Ingrediens flik. Låt oss använda den för att leta upp var och en av de tre fälten och lägga till den i orderlistan.
På Beståndsdelar Beståndsdelar fliken, låt oss klicka i den första tomma leverantörscellen, F5, och tryck på lika signalen för att starta VLOOKUP-formeln. Skriv sedan " LETARAD ( " för att starta formeln.
= LETARAD (
Kom ihåg att vår primära nyckel - den del av data som visas i båda listorna - är den ingrediens, så vi använder den för uppslaget. Klicka antingen på cellen B5, eller skriv det i formeln. Därefter lägger du till ett kommatecken efter "B5" så att vi kan komma in i nästa del av formeln.
= LETARAD (B5,
Nu måste vi ge formeln vår söklista. Med formeln fortfarande öppen, klicka på Leverantörslista flik. Klicka nu på cell A3 och klicka och dra för att markera och välj A3 till G13, hela uppslagstabellen. Kontrollera och tryck på de F4-tangenten på ditt tangentbord för att göra formuläret en absolut referens (mer om detta senare). Slutligen ange ett annat kommatecken.
Peka Excel till söklistan.När du har kommit in i kommatecken för uppslagningscellen, byter du flikar och pekar Excel på söklistan. Klicka och dra mellan cellerna A3 och G3 för att välja data som ska söka från. Kontrollera och tryck på F4 på tangentbordet under det här steget för att skapa en absolut referens, vilken kommer att låsa i cellerna som ska användas för uppslaget.
= VLOOKUP (B5, "Leverantörslista"! $ A $ 3: $ G $ 13,
Därefter måste vi berätta för Excel vilken kolumn du ska dra från. Kom ihåg att vårt första objekt att infoga är Leverantör namn, som finns i andra kolumnen i lookuplistan. Lägg till numret 2 till formeln för att dra från den andra kolumnen i uppslaget och ett annat kommatecken.
= VLOOKUP (B5, "Leverantörslista"! $ A $ 3: $ G $ 13,2,
Slutligen lägger vi till FALSE för en exakt uppslag, och stänger sedan parenteserna:
= VLOOKUP (B5, "Leverantörslista"! $ A $ 3: $ G $ 13,2, FALSE)
Vår formel fungerar perfekt och drar in leverantören för potatis. Den goda nyheten är att vi inte behöver skriva om samma formel om och om igen - bara dubbelklicka i det nedre högra hörnet av cellen F5 (Du kan se den lilla gröna rutan på cellens hörn) För att förlänga formeln (som visas ovan).
Formeln fungerar perfekt! Okej, nu går vi vidare för att dra in uppgifterna för de andra två fälten: leverantörens telefonnummer och leveransdag.
Eftersom vi använde en absolut referens kan vi i princip återanvända samma formel som vi skrev med en mindre tweak. Låt oss lägga till i leverantörsnummeret nästa.
Vi börjar med att kopiera och klistra in cellen F5 (vår leverantörscell) till cellen G5. Jag brukar bara använda tangentbordsgenvägarna Ctrl + C och Ctrl + V att kopiera och klistra in hela cellen.Först kommer det inte att fungera, och du får se en N / A i cellen.
Kopiera och klistra upp sökning med justeringar i Excel.Det är mycket lättare att kopiera och klistra in vår formel i en annan cell, men det kräver lite tweaking. Först kommer Excel att leta efter cell C5 för uppslaget, men vi måste justera det till "B5" i formulärfältet. När vi gör det så kommer uppslaget att fungera - nästan.
Vi måste gå upp till formelfältet och ändra den första delen av formeln från C5 tillbaka till B5. När vi flyttade formel över med en kolumn, uppdaterade Excel andra delar av formeln. Vi fick en "N / A" eftersom Excel försökte matcha kvantiteten (kolumn C) till vår söklista, men vår lookup list innehåller inte kvantiteten.
Hittills måste vår formel vara:
= VLOOKUP (B5, "Leverantörslista"! $ A $ 3: $ G $ 13,2, FALSE)
Observera dock i skärmbilden ovan att vi drar fel data i kolumnen "Telefon". Vi drar fortfarande den andra kolumnen i lookuplistan med "2" i formeln. Vi måste ändra detta till kolumnnumret med vår leverantörs kontakttelefon.
Låt oss gå tillbaka och kolla upp söklistan. Du kommer att märka att telefonnumret finns i sjätte kolumnen i söklistan. Låt oss gå tillbaka till vår Excel-formel och uppdatera uppslaget för att dra från den 7: e kolumnen.
Vi måste uppdatera "2" i vår Excel-formel till en "7" för att dra från den 7: e kolumnen i vår söklista.Allt vi behöver göra är att uppdatera kolumnavsnittet i vår söklista från en "2" till en "7" och det fungerar nu bra!
Observera att leverantörstelefonen fungerar bra nu när vi har uppdaterat vår formel för att använda den 7: e kolumnen från söklistan. Dra bara ned formuläret för att uppdatera det för alla celler.Vår slutliga formel för leverantörens telefonnummeruppslag:
= VLOOKUP (B5, "Leverantörslista"! $ A $ 3: $ G $ 13,7, FALSE)
Till sist, låt oss arbeta i kolumnen Leverantörsleveransdag. Kopiera och klistra in cell G5 till cell H5. Återigen måste vi fixa vår formel genom att ändra C5 tillbaka till B5 att använda ingrediensen som den primära nyckeln. Då uppdaterar du bara "7" till en "5" för att använda den 5: e kolumnen från vårt uppslagstabell.
Slutleverantörsleveransdagens uppslagstyp tillämpas.Vår sista formulär för leveransdag för leverantör:
= VLOOKUP (B5, "Leverantörslista"! $ A $ 3: $ G $ 13,5, FALSE)
Det är allt! Vi skrev i princip en formel och tweaked den för att dra in varje bit av data vi behöver för att placera vår nästa order.
Så, du har skrivit VLOOKUP-formuläret enligt instruktionerna steg för steg, men det fungerar fortfarande inte. Excel tar saker ganska bokstavligen, så vi måste vara försiktiga med data och VLOOKUP-formeln. Låt oss ta en titt på flera idéer för att korrigera en VLOOKUP-formel som bara inte fungerar.
Ett av de vanligaste problemen med VLOOKUP är när det finns flera matchningar i din söklista. När du använder en VLOOKUP, kommer den att matcha till det första objektet som finns i listan.
Vår VLOOKUP säger att Carrie Richard är vår marknadschef, men vi vet att hon nyligen föreslogs till företagets president. Varför fungerar inte vår sökning?
Whoops, det verkar som om det fanns två listor för Carrie Richard i lookuplistan - en för marknadschef och en för president. När vi raderar linjen Direktör för marknadsföring från söklistan är våra uppgifter korrekta.Poängen med VLOOKUP är att leta upp ett matchande objekt mot en lista. Lookuplistan ska inte innehålla dubbletter för primär nyckel, objektet som du använder för att matcha. Annars blir Excel förvirrad och visar bara den första matchen.
Ett annat vanligt problem är att våra data kanske inte matchar i Excel: s ögon. Data med ett mellanslag innan det sägs ha ett "ledande" utrymme, medan data med ett utrymme efter det har ett "bakomrymd".
Denna VLOOKUP är perfekt skriven, men den fungerar inte. Du kan tydligt se namnet finns i söklistan, men Excel returnerar inte en match. Läs vidare för att få reda på varför.Om din matchande data har ett mellanslag före eller efter det, ser Excel dessa två bitar av data som helt annorlunda och kommer inte att returnera en matchning. Excel-visningar "_Andrew", "Andrew_" och "Andrew" som tre unika bitar av data som inte matchas i VLOOKUP.
Visas där finns ett "bakomrymd", eller ett enda utrymme efter namnet i cellen. Det är omöjligt att se, men det kan bryta en VLOOKUP eftersom Excel behandlar "Alyssa Reddall" och "Alyssa Reddall (space)" annorlunda. När du har raderat utrymmet kommer VLOOKUP att fungera normalt.Om din data har ledande eller efterföljande utrymmen, använd TRIMMA funktion i Excel för att städa upp det. VLOOKUP ska fungera bra efter att ha använts för att ta bort ledande och bakre utrymmen.
Som du kanske vet kan du dra en cell ner genom handtaget för att klistra in formeln i andra celler. Detta bryter emellertid ibland vår VLOOKUP eftersom cellreferenserna ändras.
I den här skärmdumpen slog jag VLOOKUP-listan ner för att applicera den på andra celler. Observera dock att referensreferensen har ändrats från A2 till B15, till A16 till B29, varför formeln inte fungerar. Min uppslagningslista är faktiskt i A2 till B15, så uppslaget är nu bruten.När du drar formlerna ner, drar det referensen för lookupbordet ur justering. Plötsligt saknas uppslagstabellen rader från uppslagstabellen och vår VLOOKUP fungerar inte.
Resten är att göra formeln an absolut referens, så att när du drar formeln nere ändras inte listan som det pekar på. Klicka i cellen där du har skrivit din VLOOKUP, och klicka sedan någonstans i referenslistan. Tryck sedan på F4 . Du kommer märka att formeln ändras till att inkludera dollartecken.
Observera att formuläret (visas längst upp på skärmbilden i formulärfältet) nu innehåller dollartecken i listan om uppslagslista, vilket visar att det använder en absolut referens. Nu, när jag drar formeln ner, låser den formeln för uppslagslistan och fungerar perfekt.VLOOKUP är en av de väsentliga formlerna för att vara en produktiv Excel-användare. Det finns helt enkelt inte tillräckligt med tid för att manuellt slå upp data och skriva om det om och om igen, så formler som VLOOKUP är viktiga för att lära sig.
Om du har några problem med att använda VLOOKUP-formuläret, var god att lämna en kommentar nedan för felsökning.
Accentsconagua