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 listor av saker i ett Excel-kalkylblad. 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.

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:

Vanligtvis är listor som här en 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 måste ha en kolumn som innehåller den unika identifieraren (eller "nyckeln" eller "ID") och den kolumnen måste vara den första kolumnen i tabellen . Vårt provdatabas 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 tillförd instans av den unika identifieraren.

I exemplet ovan skulle du infoga VLOOKUP-funktionen i ett annat kalkylblad med en produktkod, och det skulle återgå till dig, antingen motsvarande produktets beskrivning, dess pris eller dess tillgänglighet (dess "i lager" mängd) som beskrivs i din ursprungliga lista. Vilka av dessa uppgifter kommer det att ge dig tillbaka? Tja, du får bestämma detta när du skapar formeln.

Om allt du behöver är en bit information från databasen, skulle det vara mycket svårt att gå till att konstruera en formel med en VLOOKUP-funktion i Det. 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 skriver in en giltig objektkod, kommer systemet att hämta all nödvändig information om motsvarande objekt.

Låt oss skapa ett exempel på detta: En Faktura Mall som vi kan återanvända om och om i vår fiktivt företag.

Först börjar vi Excel, och vi skapar oss en tom faktura:

Så här fungerar det: Den person som använder fakturamallen fyller i en serie med punktkoder i kolumn "A" och systemet hämtar varje objekts beskrivning och pris från vår produktdatabas. Den här informationen kommer att användas för att beräkna linjotalet för varje objekt (förutsatt att vi anger en giltig kvantitet).

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 ligger 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, vilken 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 flyttar vi 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 hitta 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 formuläret. Detta hittas genom att först klicka på fliken Formler och sedan 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 kan vi skriva ett sökord som "lookup" (eftersom funktionen vi är intresserade av är en uppslagning -funktion). Systemet skulle ge oss en lista över alla sökfunktioner 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 parametrarna ) 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:

1. Vilken unik identifierare tittar du upp i databasen?
2. Var är databasen?
3. Vilken bit av information från databasen som är associerad med

De första tre argumenten visas med 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 som vi behöver fylla i är argumentet Lookup_value . Funktionen behöver oss att berätta om var den unika identifieraren ska hittas ( -kod i det här fallet) att den ska returnera beskrivningen av. Vi måste välja objektkoden vi angav 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 Enter :

Värdet av "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äljarkonen bredvid det andra argumentet:

Hitta nu databasen / listan och välj hela listan - , inte inklusive rubriklinjen . I vårt exempel ligger databasen på ett separat arbetsblad, så vi klickar först på det här kalkylbladet:

Nästa väljer vi hela databasen, inte inklusive rubrikrad:

... och trycker på Enter . Det antal celler som representerar databasen (i detta fall "Produktdatabas"! A2: D7 ") matas automatiskt in för oss i 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 kolumnen andra 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" är i kolumnen B på det här arbetsbladet. Om databasen inträffade i kolumnen K i kalkylbladet, 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 värde true eller false , eller det ska vara 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 ses som följer:

Om den första kolumnen i databasen (kolumnen som innehåller den unika identifierare) sorteras alfabetiskt / numeriskt i stigande ordning, då är det möjligt att ange ett värde av sant i detta argument, eller lämna det tomt.

Om den första kolumnen i databasen är inte sorterad, eller sorteras i fallande ordning, måste du måste ange ett värde av false i detta argument

Eftersom den första kolumnen i vår databas är inte sorterad, anger vi false 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å knappen OK 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 ​​går in en different -kod i cell A11 börjar vi se effekten av 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 återvände 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 "(eftersom vi 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

... vilket ser ut så här ...

Slutför fakturamallen

Vi har lärt oss mycket om VLOOKUP än så länge. 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 av framtida How-To Geek-artiklar.

Vår faktura mall är ännu inte klar. För att slutföra det skulle vi göra följande:

1. 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-formuleringar visar felmeddelanden:
   

   

   
   Vi kan åtgärda detta genom en god bedömning av Excel: s funktioner i IF () och ISBLANK () . 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))
2. Vi skulle kopiera formlerna i cellerna B11, E11 och F11 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 kan fixa detta genom att ändra cellreferenser för databasen till absolut cellreferenser. Alternativt - och ännu bättre - kunde vi skapa ett intervallnamn för hela produktdatabasen (t.ex. "Produkter") och använda det här intervallnamnet istället för cellreferenserna. 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 kopiera formlerna ner till resten av fakturatraderna. skulle förmodligen "låsa" de celler som innehåller våra formler (eller snarare
3. låsa upp de andra -celler) och skydda sedan arbetsbladet för att säkerställa att våra noggrant konstruerade formler inte är av misstag överskrivas när någon kommer att fylla i fakturaen. Vi skulle spara filen som en
4. mall så att den kan återanvändas av alla i vårt företag. Om vi ​​kände oss

verkligen smart, vi skulle skapa en databas med alla våra kunder i ett annat kalkylblad och använd sedan kund-ID som anges i cell F5 för att automatiskt fylla i kundens namn och adress i cellerna B6, B7 och B8. Om du skulle vilja öva med VLOOKUP, eller helt enkelt se vår resulterande faktura T emplate, det kan laddas ner härifrån.

Så här skapar du egna teckensnitt och redigerar TrueType-teckensnitt

Skulle det inte vara snyggt om du kan skapa egna TrueType-teckensnitt, namnge dem vad du vill, och faktiskt använda dem i program som Word, Photoshop, etc? Lyckligtvis finns det några riktigt coola programvaror där ute som du kan använda för att inte bara skapa egna Windows-teckensnitt, utan också att redigera de teckensnitt som för närvarande finns på ditt system!I det hä

(How-to)

Hur handlar du i dina gamla gadgets för kontanter (så att du kan köpa nya gadgets)

Om du vill sälja din gamla smartphone för att betala för den nya, lägg till lite pengar till din roliga pengar stapel, eller för att sätta vinsten mot jul, vi är här för att hjälpa. Läs vidare när vi skisserar de bästa sätten att förvandla ditt gamla redskap till pengar. RELATED: Hur man förbereder en dator, en tablett eller en telefon innan den säljs När det gäller att göra dina äldre gadgets till kontanter , det finns tre huvudsakliga platser att utforska: inlämningsprogram, auktionsplatser och lokal försäljning.

(how-to)