Tillämpning och definition av denna Excel -funktion
VLOOKUP är en Excel -funktion med vilken användaren kan söka efter och utvärdera tabellinnehåll. Denna funktion är tillgänglig i versioner från Excel 2007 för Windows och Mac.
Vad är VLOOKUP?
De möjliga användningarna av VLOOKUP ska förklaras här med hjälp av ett exempel: I den här är du en stor fan av litteratur och har därför skapat ditt eget Excel -kalkylblad där du noggrant kan sortera de böcker du har samlat. Varje verk skrivs in med information om följande kategorier:
-
författare
-
titel
-
Sidonummer
-
Utgivningsår
Nu skulle du vilja ge en vän ett boktips att ta med dig på ditt nästa möte. Tyvärr kan du bara tänka på författaren, inte bokens titel. Det är här VLOOKUP spelar in, eftersom det kan använda detta inmatningsvärde för att kasta ut informationen du letar efter i ett svep.
Hur används VLOOKUP?
Innan man ens tänker på att formulera formler, bör det avgöras var inmatningsfältet och de olika utmatningsfälten kommer att placeras senare. För att göra detta är det vettigt att skapa en separat tabell som initialt är tom och därmed ger utrymme för den nämnda informationen. Om du designar det här nya bordet baserat på exemplet på den befintliga tabellen kommer du att ha en tidsbesparande fördel senare.
På grundval av detta kan VLOOKUP -formeln antingen skapas manuellt eller automatiskt genereras av Excel. För nybörjare är det värt att använda det senare tillvägagångssättet för att gradvis lära känna strukturen och effekten av formeln. För att göra detta väljs knappen för "Infoga funktion" på fliken "Formler". VLOOKUP är dolt i fönstret som öppnas. Efter bekräftelse öppnas ett fönster igen där de fyra parametrarna i formeln kan fyllas i. Dessa är:
-
Sökkriterium
-
matris
-
Kolumnindex
-
Area_reference
Det råa utkastet till formeln ser därför ut så här:
= VLOOKUP (sökkriterium, matris, kolumnindex, intervall_länk)
och i en möjlig applikation så här:
= VISNING (H3; A3: E40; 5)
Sökkriterium
Så att funktionen vet vilket värde som ska användas som utgångspunkt noteras raden som valdes som inmatningsfält två steg tidigare i fältet "Sökkriterium". I vårt exempel anges namnet på bokförfattaren "Phillip Pulmann" där. Detta gör formeln flexibel och behöver inte justeras igen så snart det angivna värdet ändras.
matris
Inmatningsfältet "Matris" beskriver tabellen där informationen som ska matas ut kan hittas. Denna speciella matris innehåller således också kolumnerna för boktiteln, sidnumret och publiceringsåret.
Matrisen väljs helt en gång utan rubrikerna från övre vänstra till nedre högra marginalen. På detta sätt vet Excel vilket innehåll som måste beaktas vid utvärderingen.
Kolumnindex
Inmatningsfältet för "kolumnindex" uppmanar användaren att definiera kolumnen i matrisen där endast det efterfrågade värdet anges. Tilldelningen av kolumnerna numreras kronologiskt. Det betyder att den första kolumnen i tabellen får värdet 1, den andra värdet 2, etc. I vårt exempel motsvarar detta kolumnindex 1 för författaren, kolumnindex 2 för titeln, kolumnindex 3 för sidnumret och kolumnindex 4 för publiceringsåret.
För att tabellen ska vara så flexibel som möjligt kan kolumnrubriken länkas istället för numret. Detta har fördelen att formeln också kan överföras till andra rader utan problem, eftersom kolumnrubriken flexibelt kan anpassas varje gång.
Uppmärksamhet: VLOOKUP läser matrisen från vänster till höger, varför kolumnindex måste placeras till höger om kolumnen för att sökkriteriet ska kunna beaktas av funktionen!
Area_reference
Parametern "Range_Lookup" kompletterar VLOOKUP -formeln genom att ange noggrannheten med vilken tabellen utvärderas. Det skiljer sig dock från de tidigare nämnda komponenterna i formeln eftersom det är valfritt. Om värdet 0 anges för "felaktigt" söker Excel bara efter det värde som angavs som sökkriterium. Med värdet 1 för "true" fortsätter dock sökningen efter uppenbara värden om det exakta värdet inte kunde hittas.
Att ange denna parameter är valfritt eftersom värdet 1 är inställt som standard. Denna inställning kommer att vara användbar senare i den avancerade sökningen med flera sökkriterier.
Sammanslagningen
Så snart alla nödvändiga parametrar har ställts in kan VLOOKUP användas. Efter att du har angett sökkriteriet och bekräftat funktionen visas det värde du letar efter på raden som har definierats som ett utmatningsfält.
I vårt exempel visas nu boktiteln ”Den gyllene kompassen”, vilket motsvarar den inskrivna författaren. För att snabbt ta reda på sidnumret och publiceringsåret behöver inget mer göras än att dra den befintliga VLOOKUP -formeln till de efterföljande cellerna. Detta är så enkelt eftersom kolumnindexet i VLOOKUP har länkats till kolumnrubriken i den första tabellen och den andra tabellen är också strukturerad i samma ordning.
Om tabellerna skulle skilja sig från varandra eller att ett fel uppstår trots allt kan VLOOKUP -formeln också ändras manuellt. För att göra detta måste den näst sista siffran för kolumnindex matchas med kolumnen för det nya värdet som ska matas ut.
VLOOKUP med flera sökkriterier
Ofta händer det att ett enda sökkriterium inte räcker för att noggrant utvärdera en stor Excel -tabell. Då är det vettigt att köra VLOOKUP med flera sökkriterier. För att göra detta måste den befintliga formeln kompletteras med en ytterligare IF -funktion. På detta sätt kan upp till åtta olika sökkriterier beaktas under ansökan.
VLOOKUP i flera Excel -kalkylblad
Om sökkriteriet inte bara kan hittas i en tabell utan möjligen också i en annan kan VLOOKUP -formeln justeras därefter. För detta måste både en if -funktion och en FEL -funktion placeras framför den befintliga formeln. Fem parametrar krävs för detta:
-
Sökkriterium
-
Matrix1 och Matrix 2
-
Kolumnindex1 och kolumnindex2
Resultatet ser ut så här:
= IF (FEL (VLOOKUP (sökkriterium, matris1, kolumnindex1, 0));
VLOOKUP (sökkriterium; matris2; kolumnindex2,0); VLOOKUP (sökkriterium; matris1; kolumnindex1;))
och i en möjlig applikation så här:
= OM (FEL (VISNING (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Sökkriteriet används för att infoga värdet som ska sökas efter i de två tabellerna. Matrix1 och Matrix2 definierar respektive cellområden i de två tabellerna. Kolumnindex1 och kolumnindex2 används för att närmare definiera vilka kolumner i respektive tabell som ska sökas.
Om värdet du letar efter förekommer i båda tabellerna kommer Excel att mata ut resultatet från den första tabellen. Men om värdet inte finns i någon av de två tabellerna visas ett felmeddelande. Fördelen med formeln är att de två listorna inte behöver ha samma struktur eller vara lika stora.
Tilldela värden till kategorier med hjälp av VLOOKUP
En ytterligare funktion med VLOOKUP gör att listade värden automatiskt kan delas upp i bokstäver och predikat efter eget val. I vårt tidigare exempel bör en ytterligare tabellkolumn infogas för boktypen. Böckerna med en längd på upp till 50 sidor bör tillhöra novellgenren, medan böcker från 51 till 150 sidor tilldelas novellen och från 151 sidor till romanen. För att göra detta möjligt krävs ingen ytterligare formel i VLOOKUP, bara användningen av lockiga parenteser “{}”. Den färdiga formeln ser ut så här:
= VLOOKUP (B1; {1. "Novell"; 51. "Novella"; 151. "Novel"}; 2)
Innehållet i de lockiga parenteserna indikerar en matris som definierar området för en respektive boktyp. Tilldelningen av sidlängden till det lämpliga släktet är därför inrymt inom de lockiga parenteserna. Formeln använder par av värden, var och en åtskild med en punkt. Matrisen {1. "Novell"; 51. "Novella"; 151. "Roman"} läses enligt följande:
"Från 1 visa en novell, från 51 visa en novell, från 151 visa en roman."
Denna matris kan enkelt anpassas till olika uppgif.webpter. Detta gäller å ena sidan storleken och antalet matriser samt deras beteckning. Så det är möjligt att mata ut strängar eller siffror som ett resultat istället för enskilda bokstäver. Allt du behöver göra är att justera bokstäverna i formeln.
VLOOKUP över flera kalkylblad
En annan funktion i VLOOKUP gör att dess användare kan länka innehåll som finns på olika kalkylblad. I vårt exempel kan det här alternativet vara användbart när information först sorteras i olika kalkylblad och sedan uppdateras i en sammanfattningstabell.
Föreställ dig att du förutom dina böcker också listar dina samlade filmer i ett Excel -kalkylblad. Du kombinerar sedan båda samlingarna i ett stort bord.
Fördelen med detta förfarande ligger inte bara i den ökade ordningen, utan också i att undvika eventuella fel. Om du vill skapa en ny post eller uppdatera en befintlig, behöver du inte söka i den stora tabellen, utan kan istället komma åt de mindre. Värdena överförs sedan automatiskt till den sammanfattande Excel -tabellen. Detta gör omskrivning i det stora bordet överflödigt, vilket i bästa fall undviker ett olyckligt drag och en efterföljande kedja av felmeddelanden.
Hur ser formeln ut?
Denna funktion görs möjlig igen genom att infoga en annan formel. Vid sökning med flera kriterier krävs en ytterligare IF -formel, men att arbeta med flera kalkylblad kräver en INDIRECT -formel. Detta gör att ett intervall från ett annat kalkylblad kan specificeras för VLOOKUP -matrisen.
= VLOOKUP (sökkriterium; INDIRECT (matris); kolumnindex; range_link)
Uppmärksamhet: Denna formel fungerar bara om de enskilda tabellerna i de olika arken har samma namn som kolumnrubrikerna i den allmänna tabellen. Hela tabeller kan namnges i "Namnfält" högst upp till vänster ovanför cellnätet. Tabeller som redan har fått namn kan ses med tangentkombinationen Ctrl + F3.
Hantera nya felmeddelanden
Att arbeta med länkade Excel -tabeller kan leda till oönskade problem. Detta inkluderar särskilt utmatning av felaktiga värden. Om fel värde 0 matas ut finns det ett litet problem i inställningarna i Excel, som snabbt kan åtgärdas.
Det vanliga felmeddelandet #NV, å andra sidan, är en avsiktlig funktion av VLOOKUP, vilket indikerar för användaren att det erforderliga värdet inte är tillgängligt. Denna lapp kan utformas annorlunda med hjälp av en formel.
VLOOKUP - en översikt
VLOOKUP är en användbar Excel -funktion som kan användas för att söka och utvärdera tabeller. Dess fördelar är uppenbara i den användarvänliga och flexibla applikationen. På så sätt kan alla som regelbundet arbetar med Excel -tabeller dra nytta av funktionen. Det är den privata samlaren som skapar sina egna små bord, eller det stora företaget som behandlar betydligt mer omfattande datamängder.
Om du å andra sidan fortfarande har obesvarade förfrågningar som VLOOKUP inte kunde tillgodose kan du se fram emot ytterligare ett Excel-alternativ: Microsoft har erbjudit Excel 365-användare den nya XLOOKUP sedan början av 2022-2023. Detta bygger på VLOOKUP: s kompetenser och kompletterar dem med ytterligare, ibland ännu enklare funktioner. Därför öppnas också en ny rutin inom datavärdering vid denna tidpunkt.
