Många människor försöker använda Excel som en databas. Även om det kan fungera som en databas, är några av de uppgifter som skulle vara mycket enkla i ett databasprogram ganska komplexa i Excel. En av dessa uppgifter är att matcha två listor baserat på ett gemensamt fält; detta kan enkelt göras med hjälp av Excel VLOOKUP. Du kommer att tycka att funktionen VLOOKUP är extremt användbar så kolla in ett exempel på när och hur du använder den här funktionen nedan.
Säg att din resebyrå skickar en månadsrapport om alla platser som dina anställda har rest. Rapporten använder flygplatskoder istället för stadsnamn. Det skulle vara till hjälp om du enkelt kunde lägga in det riktiga stadens namn istället för bara koden.
På Internet hittar du och importerar en lista som visar stadens namn för varje flygplatskod.
Men hur får du denna information på varje post i rapporten?
- Använd VLOOKUP-funktionen. VLOOKUP står för "Vertical Lookup". Den kan användas när som helst när du har en lista med data med nyckelfältet i kolumnen längst till vänster.
- Börja skriva funktionen
=VLOOKUP(. Skriv Ctrl + A för att få hjälp med funktionen. - VLOOKUP behöver fyra parametrar. Först är stadskoden i originalrapporten. I detta exempel skulle det vara cell D4
- Nästa parameter är intervallet med din uppslagstabell. Markera intervallet. Var noga med att använda F4 för att göra intervallet absolut. (En absolut referens har ett dollartecken före både kolumnnumret och radnumret. När formeln kopieras fortsätter referensen att peka mot I3: J351.
- Den tredje parametern berättar för Excel i vilken kolumn stadsnamnet finns. I intervallet I3: J351 finns stadsnamnet i kolumn 2. Ange en 2 för denna parameter.
- Den fjärde parametern berättar för Excel om en ”stäng” matchning är OK. I det här fallet är det inte, så skriv in False.
- Klicka på OK för att slutföra formeln. Dra fyllningshandtaget för att kopiera formeln nedåt.
- Eftersom du noggrant har angett absoluta formler kan du kopiera kolumn E till kolumn D för att få målstaden. I det här fallet går alla avgångar från Pearson International Airport i Toronto.
Även om detta exempel fungerade perfekt, när tittarna använder VLOOKUP, betyder det vanligtvis att de matchar listor som kommer från olika källor. När listor kommer från olika källor kan det alltid finnas subtila skillnader som gör listorna svåra att matcha. Här är tre exempel på vad som kan gå fel och hur man rättar till dem.
-
En lista har bindestreck och den andra listan inte. Använd
=SUBSTITUTE()funktionen för att ta bort bindestreck. Första gången du testar VLOOKUP får du N / A-fel.För att ta bort streck med en formel, använd SUBSTITUTE-formeln. Använd 3 argument. Det första argumentet är cellen som innehåller värdet. Nästa argument är texten som du vill ändra. Det sista argumentet är ersättningstexten. I det här fallet vill du ändra bindestreck till ingenting, så formeln är
=SUBSTITUTE(A4,"-","").Du kan slå in den funktionen i VLOOKUP för att få beskrivningen.
-
Den här är subtil men mycket vanlig. En lista har ett tomt blanksteg efter posten. Använd = TRIM () för att ta bort överflödiga utrymmen. När du först anger formeln hittar du att alla svaren är N / A-fel. Du vet med säkerhet att värdena finns i listan och att allt ser bra ut med formeln.
En vanlig sak att kontrollera är att flytta till cellen med uppslagsvärdet. Tryck på F2 för att sätta cellen i redigeringsläge. När du väl är i redigeringsläge kan du se att markören ligger ett mellanslag från den sista bokstaven. Detta indikerar att det finns ett bakre utrymme i posten.
Använd TRIM-funktionen för att lösa problemet.
=TRIM(D4)tar bort ledande mellanslag, efterföljande mellanslag och ersätter alla interna dubbelrum med ett enda mellanslag. I det här fallet fungerar TRIM perfekt för att ta bort det bakre utrymmet.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)är formeln. -
Jag nämnde ett bonustips i utställningsanteckningarna: hur man ersätter # N / A-resultatet för saknade värden med ett tomt. Om ditt uppslagsvärde inte finns i uppslagstabellen kommer VLOOKUP att returnera ett N / A-fel.
Denna formel använder
=ISNA()funktionen för att upptäcka om resultatet av formeln är ett N / A-fel. Om du får felet kommer det andra argumentet i IF-funktionen att berätta för Excel att lägga i vilken text du vill.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP låter dig spara tid när du matchar datalistor. Ta dig tid att lära dig den grundläggande användningen så kommer du att kunna göra mycket kraftfullare uppgifter i Excel.
