VLOOKUP i Excel, del 2 Använda VLOOKUP utan databas
I en ny artikel presenterade vi Excel-funktionen som heter VLOOKUP och förklarade hur det skulle kunna användas för att hämta information från en databas till en cell i ett lokalt arbetsblad. I den artikeln nämnde vi att det fanns två användningsområden för VLOOKUP, och endast en av dem handlade med frågande databaser. I den här artikeln, den andra och sista i VLOOKUP-serien, undersöker vi denna andra, mindre kända användning för VLOOKUP-funktionen.
Om du inte redan har gjort det, läs den första VLOOKUP artikeln - den här artikeln antar att många av de begrepp som förklaras i den artikeln är kända för läsaren.
Vid arbete med databaser skickas VLOOKUP en "unik identifierare" som tjänar till att identifiera vilken dataregister vi vill hitta i databasen (t ex en produktkod eller kund-ID). Denna unika identifierare måste finns i databasen, annars ger VLOOKUP oss ett fel. I den här artikeln kommer vi att undersöka ett sätt att använda VLOOKUP där identifieraren inte behöver existera i databasen alls. Det är nästan som om VLOOKUP kan anta ett "nära nog är tillräckligt bra" när det gäller att returnera de data vi letar efter. Under vissa omständigheter är detta exakt vad vi behöver.
Vi kommer att illustrera den här artikeln med ett verkligt exempel - det vill säga att beräkna de provisioner som genereras på en uppsättning försäljningsuppgifter. Vi börjar med ett mycket enkelt scenario och sedan gradvis göra det mer komplicerat tills den enda rationella lösningen på problemet är att använda VLOOKUP. Det initiala scenariot i vårt fiktiva företag fungerar så här: Om en säljare skapar mer än 30 000 dollar av försäljningen under ett visst år, är den provision de tjänar på den försäljningen 30%. Annars är deras provision endast 20%. Hittills är det här ett ganska enkelt arbetsblad:
För att använda detta arbetsblad går försäljaren in i sina försäljningsuppgifter i cell B1, och formeln i cell B2 beräknar den korrekta procentsats som de har rätt att ta emot, vilken används i cell B3 för att beräkna den totala provision som säljaren är skyldig (vilket är en enkel multiplikation av B1 och B2).
Cellen B2 innehåller den enda intressanta delen av detta arbetsblad - formeln för att bestämma vilken kommissionshastighet som ska användas: den ena Nedan tröskeln på $ 30.000, eller den ena ovan tröskeln. Denna formel använder sig av den Excel-funktion som heter OM. För de läsare som inte känner till IF fungerar det så här:
OM(villkor, värde om det är sant, värde om det är felaktigt)
Där det tillstånd är ett uttryck som utvärderar till antingen Sann eller falsk. I exemplet ovan tillstånd är uttrycket B1
Som du ser kan du använda en försäljningsomgång på 20 000 dollar, vilket ger oss en procentsats på 20% i cell B2. Om vi anger ett värde på $ 40.000 får vi en annan provisionsats:
Så vårt kalkylblad arbetar.
Låt oss göra det mer komplext. Låt oss införa ett andra tröskelvärde: Om säljaren tjänar mer än 40 000 dollar, ökar kommissionens provision till 40%:
Lätt nog att förstå i den verkliga världen, men i cell B2 blir vår formel mer komplex. Om du tittar noga på formeln ser du att den tredje parametern för den ursprungliga IF-funktionen (den värde om det är felaktigt) är nu en hel IF-funktion i sig. Detta kallas a nestad funktion (en funktion inom en funktion). Det är helt giltigt i Excel (det fungerar även!), Men det är svårare att läsa och förstå.
Vi kommer inte att gå in i muttrar och bultar om hur och varför det här fungerar, och vi kommer inte heller att undersöka nyanserna av kapslade funktioner. Detta är en handledning om VLOOKUP, inte på Excel i allmänhet.
Hur som helst blir det värre! Vad sägs om när vi bestämmer att om de tjänar mer än 50 000 dollar då har de rätt till 50% provision, och om de tjänar mer än 60 000 dollar då har de rätt till 60% provision?
Nu har formeln i cell B2, medan den är korrekt, blivit nästan oläslig. Ingen borde skriva formulär där funktionerna är kapslade fyra nivåer djupt! Visst måste det finnas ett enklare sätt?
Det är säkert. VLOOKUP till räddningen!
Låt oss omskapa kalkylbladet lite. Vi håller alla samma siffror, men organiserar det på ett nytt sätt, en mer tabell sätt:
Ta en stund och verifiera själv det nya Betygsätt tabell fungerar exakt samma som serien av trösklar ovan.
Konceptuellt, vad vi ska göra är att använda VLOOKUP för att leta upp säljareens försäljnings totalt (från B1) i satsen och returnera till oss motsvarande procentsats. Observera att säljaren faktiskt kan ha skapat försäljning som är inte ett av de fem värdena i räntetabellen ($ 0, $ 30,000, $ 40,000, $ 50,000 eller $ 60,000). De kan ha skapat en försäljning på 34,988 dollar. Det är viktigt att notera att $ 34,988 gör inte visas i siffertabellen. Låt oss se om VLOOKUP kan lösa vårt problem ändå ...
Vi väljer cell B2 (den plats vi vill sätta vår formel), och sedan sätta in VLOOKUP-funktionen från formler flik:
De Funktionsargument rutan för VLOOKUP visas. Vi fyller i argumenten (parametrar) en efter en, som börjar med letauppvärde, vilket är i detta fall försäljningen totalt från cell B1. Vi placerar markören i letauppvärde fält och klicka sedan en gång på cell B1:
Nästa måste vi ange för VLOOKUP vilken tabell för att söka upp dessa data. I det här exemplet är det naturligtvis kurstabellen. Vi placerar markören i Tabellmatris fält och markera sedan hela siffertabellen - exklusive rubrikerna:
Nästa måste vi ange vilken kolumn i tabellen som innehåller den information vi vill att vår formel ska återvända till oss. I det här fallet vill vi ha kommissionsfrekvensen, som finns i den andra kolumnen i tabellen, så vi skriver därför in en 2 in i kolumnindex fält:
Slutligen anger vi ett värde i ungefärlig fält.
Viktigt: Det är användningen av det här fältet som skiljer de två sätten att använda VLOOKUP. För att använda VLOOKUP med en databas, den här sista parametern, ungefärlig, måste alltid vara inställd på FALSK, men med denna andra användning av VLOOKUP måste vi antingen lämna den tomma eller ange ett värde av SANN. När du använder VLOOKUP är det viktigt att du gör rätt val för den här sista parametern.
För att vara explicit kommer vi att ange ett värde av Sann i ungefärlig fält. Det skulle också vara bra att lämna den tom, eftersom det här är standardvärdet:
Vi har slutfört alla parametrar. Vi klickar nu på ok knappen, och Excel bygger vår VLOOKUP-formel för oss:
Om vi experimenterar med några olika försäljningsbelopp totalt kan vi försäkra oss om att formeln fungerar.
Slutsats
I "databas" versionen av VLOOKUP, där ungefärlig parametern är FALSK, värdet passerat i den första parametern (letauppvärde) måste vara närvarande i databasen. Med andra ord söker vi efter en exakt matchning.
Men i denna andra användning av VLOOKUP söker vi inte nödvändigtvis en exakt matchning. I det här fallet är "nära nog tillräckligt bra". Men vad menar vi med "nära nog"? Låt oss använda ett exempel: När vi söker efter en provisionprocent på totalt 34,988 USD kommer vår VLOOKUP-formel att ge oss ett värde på 30%, vilket är det rätta svaret. Varför valde den raden i tabellen med 30%? Vad betyder i själva verket "nära nog" i det här fallet? Låt oss vara exakta:
När ungefärlig är satt till SANN (eller utelämnas), kommer VLOOKUP att se i kolumn 1 och matcha det högsta värdet som inte är större än de letauppvärde parameter.
Det är också viktigt att notera att för detta system att fungera, bordet måste sorteras i stigande ordning i kolumn 1!
Om du vill öva med VLOOKUP kan du hämta den exempelfil som illustreras i den här artikeln.
