1Aug
VLOOKUP är en av Excel mest användbara funktioner, och det är också en av de minst förstådda. I denna artikel demystifierar vi VLOOKUP med hjälp av ett verkligt exempel. Vi skapar en användbar faktura mall för ett fiktivt företag.
VLOOKUP är en Excel -funktion .Den här artikeln förutsätter att läsaren redan har en förståelse för Excel-funktioner och kan använda grundläggande funktioner som SUM, AVERAGE och TODAY.I sin vanligaste användning är VLOOKUP en -databas -funktion, vilket innebär att den fungerar med databastabeller - eller mer enkelt, listar av saker i ett Excel-arbetsblad. Vad är det för saker? Tja, någon sorts sak. Det kan hända att du har ett kalkylblad som innehåller en lista över anställda, produkter eller kunder, eller cd-skivor i din cd-samling eller stjärnor i nattskyen. Det spelar ingen roll så mycket.
Här är ett exempel på en lista eller en databas. I det här fallet är det en lista över produkter som vårt fiktiva företag säljer:
Normalt listor som denna har någon form av unik identifierare för varje objekt i listan. I det här fallet är den unika identifieraren i kolumnen "Artikelnummer".Obs! För VLOOKUP-funktionen att arbeta med en databas / lista måste listan
ha en kolumn som innehåller den unika identifieraren( eller "nyckel" eller "ID") och den kolumnen måste vara den första kolumnen i tabellen.Vår databas ovan uppfyller detta kriterium.Den svåraste delen av att använda VLOOKUP är att förstå exakt vad det är för. Så låt oss se om vi kan få det klart först:
VLOOKUP hämtar information från en databas / lista baserat på en levererad förekomst av den unika identifieraren.
I exemplet ovan skulle du infoga VLOOKUP-funktionen i ett annat kalkylblad med en artikelnummer, och det kommer att returnera till dig antingen motsvarande artikelbeskrivning, dess pris eller dess tillgänglighet( dess "i lager" -mängd) som beskrivs i dinursprungliga listan. Vilka av dessa uppgifter kommer det att ge dig tillbaka? Nåväl, du bestämmer detta när du skapar formeln.
Om allt du behöver är en bit av information från databasen, skulle det vara mycket problem att gå till för att konstruera en formel med en VLOOKUP-funktion i den. Vanligtvis skulle du använda den här typen av funktionalitet i ett återanvändbart kalkylblad, till exempel en mall. Varje gång någon matar in en giltig objektkod, skulle systemet hämta all nödvändig information om motsvarande föremål.
Låt oss skapa ett exempel på detta: En Faktura Mall som vi kan återanvända om och om igen i vårt fiktiva företag.
Först börjar vi Excel, och vi skapar oss en tom faktura:
Så här kommer det att fungera: Den person som använder fakturamallen fyller i en serie av objektkoder i kolumn "A", och systemet hämtar varje objektbeskrivning och pris från vår produktdatabas. Den informationen kommer att användas för att beräkna linjotalet för varje objekt( förutsatt att vi anger en giltig mängd).
För att behålla detta exempel är det enkelt att hitta produktdatabasen på ett separat ark i samma arbetsbok:
I verkligheten är det troligare att produktdatabasen placeras i en separat arbetsbok. Det gör liten skillnad i VLOOKUP-funktionen, vilket inte bryr sig om databasen ligger på samma ark, ett annat ark eller en helt annan arbetsbok.
Så vi har skapat vår produktdatabas, som ser ut så här:
För att testa VLOOKUP-formuläret som vi ska skriva skriver vi först en giltig postkod i cell A11 i vår tomma faktura:
Nästa, viflytta den aktiva cellen till cellen där vi vill ha information som hämtas från databasen med VLOOKUP för att lagras. Intressant är detta det steg som de flesta människor blir fel på.Förklara ytterligare: Vi håller på att skapa en VLOOKUP-formel som kommer att hämta beskrivningen som motsvarar artikelnumret i cell A11.Var vill vi ha den här beskrivningen när vi får den? I cell B11, förstås. Så det är där vi skriver VLOOKUP-formeln: i cell B11.Välj cell B11 nu.
Vi måste lokalisera listan över alla tillgängliga funktioner som Excel har att erbjuda, så att vi kan välja VLOOKUP och få lite hjälp när vi fyller i formeln. Detta hittas genom att först klicka på Formulas fliken och sedan klicka på Infoga funktion :
En ruta visas som låter oss välja någon av de funktioner som finns i Excel.
För att hitta den vi letar efter kunde vi skriva ett sökord som "lookup"( eftersom funktionen vi är intresserade av är en lookup -funktion).Systemet skulle ge oss en lista över alla utseende-relaterade funktioner i Excel. VLOOKUP är den andra i listan. Välj det ett klick OK .
Fältet Funktionsargument visas och ber oss om att alla -argumenten ( eller -parametrar ) behövs för att slutföra VLOOKUP-funktionen. Du kan tänka på den här rutan som funktionen frågar oss följande frågor:
- Vilken unik identifierare tittar du upp i databasen?
- Var är databasen?
- Vilken bit av information från databasen som är associerad med den unika identifieraren, vill du ha hämtat för dig?
De tre första argumenten visas i fetstil i fetstil , vilket indikerar att de är obligatoriska -argument( VLOOKUP-funktionen är ofullständig utan dem och kommer inte att returnera ett giltigt värde).Det fjärde argumentet är inte fet, vilket betyder att det är valfritt:
Vi kommer att slutföra argumenten i ordning, uppifrån och ned.
Det första argumentet vi måste slutföra är argumentet Lookup_value .Funktionen behöver oss att berätta för var den unika identifieraren ska hittas( -postkoden i det här fallet) att den ska returnera beskrivningen av. Vi måste välja objektkoden vi skrev in tidigare( i A11).
Klicka på väljarkonen till höger om det första argumentet:
Klicka sedan en gång på cellen som innehåller objektkoden( A11) och tryck på Ange :
Värdet för "A11" infogas i det första argumentet.
Nu måste vi ange ett värde för argumentet Table_array .Med andra ord måste vi berätta för VLOOKUP var att hitta databasen / listan. Klicka på väljarsymbolen bredvid det andra argumentet:
Hitta nu databasen / listan och välj hela listan - , inte med rubrikens raden .I vårt exempel är databasen placerad i ett separat arbetsblad, så vi klickar först på det här kalkylbladet:
Nästa väljer vi hela databasen, inte med rubrikrubrik:
. .. och tryck på Ange .Sortimentet av celler som representerar databasen( i detta fall "Produktdatabas"! A2: D7 ") matas in automatiskt för oss till det andra argumentet.
Nu måste vi ange det tredje argumentet, Col_index_num .Vi använder detta argument för att specificera att VLOOKUP vilken bit av information från databasen, associerar med vår varukod i A11, vi önskar ha återlämnat till oss. I det här exemplet önskar vi att objektets -beskrivning returneras till oss. Om du tittar på databladets kalkylblad märker du att kolumnen "Beskrivning" är den andra -kolumnen i databasen. Det betyder att vi måste ange ett värde av "2" i rutan Col_index_num :
Det är viktigt att notera att vi inte skriver in en "2" här eftersom kolumnen "Beskrivning" finns i kolumnen B pådet arbetsbladet. Om databasen inträffade i kolumnen K i arbetsbladet, skulle vi fortfarande ange en "2" i det här fältet eftersom kolumnen "Beskrivning" är den andra kolumnen i uppsättningen celler som vi valde när du angav "Table_array".
Slutligen måste vi bestämma om du vill ange ett värde i det slutliga VLOOKUP-argumentet, Range_lookup .Detta argument kräver antingen ett -true eller -false -värde, eller det ska lämnas tomt. När du använder VLOOKUP med databaser( som det är sant 90% av tiden) kan sättet att bestämma vad som ska sättas i detta argument betraktas som följer:
Om den första kolumnen i databasen( kolumnen som innehåller de unika identifierarna)sorteras alfabetiskt / numeriskt i stigande ordning, då är det möjligt att ange ett värde av true i detta argument, eller lämna det tomt.
Om den första kolumnen i databasen är inte sorterad eller sorterad i fallande ordning så måste ange ett värde av falskt i detta argument
Som första kolumnen i vår databas är inte sorterad,vi anger falskt i detta argument:
Det är det! Vi har angett all information som krävs för VLOOKUP för att returnera det värde vi behöver. Klicka på OK -knappen och märk att beskrivningen som motsvarar artikelnummer "R99245" har skrivits in korrekt i cell B11:
Formeln som skapades för oss ser så här ut:
Om vi anger en -annan -kod i cellA11 börjar vi se kraften i VLOOKUP-funktionen: Beskrivningscellen ändras för att matcha den nya artikelkoden:
Vi kan utföra en liknande uppsättning steg för att få objektets -pris tillbaka till cell E11.Observera att den nya formeln måste skapas i cell E11.Resultatet kommer att se ut så här:
. .. och formeln ser så här ut:
Observera att den enda skillnaden mellan de två formlerna är det tredje argumentet( Col_index_num ) har ändrats från en "2" till en "3"( eftersomvi vill ha data som hämtas från den tredje kolumnen i databasen).
Om vi bestämde oss för att köpa 2 av dessa objekt, skulle vi ange en "2" i cell D11.Vi skulle sedan ange en enkel formel i cell F11 för att få linjen totalt:
= D11 * E11
. .. som ser ut så här. ..
Slutför fakturamallen
Vi har lärt oss mycket om VLOOKUP hittills. Vi har faktiskt lärt oss allt vi ska lära oss i den här artikeln. Det är viktigt att notera att VLOOKUP kan användas under andra omständigheter förutom databaser. Detta är mindre vanligt och kan täckas i framtida How-To Geek-artiklar.
Vår faktura mall är ännu inte färdig. För att slutföra det skulle vi göra följande:
- Vi skulle ta bort provpostkoden från cell A11 och "2" från cell D11.Detta kommer att leda till att våra nyskapade VLOOKUP-formler visar felmeddelanden:
Vi kan åtgärda detta genom en god bedömning av Excels IF() och ISBLANK() -funktioner. Vi ändrar vår formel från detta. .. = VLOOKUP( A11, "Produktdatabas"! A2: D7,2, FALSE) . .. till detta. .. = IF( ISBLANK( A11), "" VLOOKUP( A11, "Produktdatabas'! A2: D7,2, FALSE))
- Vi skulle kopiera formlerna i cellerna B11, E11 och F11 ner till resten av raderna på fakturan. Observera att om vi gör det här, kommer de resulterande formlerna inte längre korrekt att hänvisas till databastabellen. Vi kunde fixa detta genom att ändra cellreferenser för databasen till -absoluta -cellreferenser. Alternativt - och ännu bättre - kunde vi skapa ett -sortiment för hela produktdatabasen( till exempel "Produkter") och använda detta intervallnamn istället för cellreferenser. Formeln skulle ändras från detta. .. = IF( ISBLANK( A11), "VLOOKUP( A11," Produktdatabas "! A2: D7,2, FALSE)) . .. till detta. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, Produkter, 2, FALSE)) . .. och sedan kopierar formlerna ner till resten av raderna på fakturan.
- Vi skulle förmodligen "låsa" de celler som innehåller våra formler( eller hellre låsa upp andra -celler) och skydda sedan arbetsbladet för att säkerställa att våra noggrant konstruerade formler inte överskrivs av misstag när någon kommer att fyllai fakturan.
- Vi skulle spara filen som -mall så att den kan återanvändas av alla i vårt företag
Om vi kände verkligen smart skulle vi skapa en databas över alla våra kunder i ett annat arbetsblad och använd sedankund-ID som anges i cell F5 för att automatiskt fylla i kundens namn och adress i cellerna B6, B7 och B8.
Om du vill träna med VLOOKUP, eller helt enkelt se vår resulterande fakturamall, kan den hämtas härifrån.
