VLOOKUP i Excel, del 2: Använda VLOOKUP utan en databas

I en ny artikel introducerade vi Excel-funktionen VLOOKUP och förklarade hur den kunde 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, VLOOKUP skickas 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 finnas 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 en "nära nog är bra nog" -metod för 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äljnings siffror. Vi kommer att börja med ett mycket enkelt scenario och sedan gradvis göra det mer komplext, tills den enda rationella lösningen på problemet är att använda VLOOKUP.Det ursprungliga 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%.Så här är det här ett ganska enkelt arbetsblad:

För att använda det här kalkylbladet, kommer sä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, vilket används i cell B3 för att beräknaDen 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 under tröskeln på $ 30.000, eller den ena över tröskeln. Denna formel använder Excel-funktionen IF .För de läsare som inte känner till IF fungerar det så här:

IF( -villkor, värde om det är true, värde om falskt )

Om -villkoret är ett uttryck som utvärderar till antingen -sann eller -falsk .I exemplet ovan är -villkoret uttrycket B1 & B5 , som kan läsas som "Är B1 mindre än B5?" Eller, sätt på ett annat sätt, "Är den totala försäljningen mindre än tröskeln".Om svaret på denna fråga är "ja"( sant), använder vi -värdet om den verkliga -parametern för funktionen, nämligen B6 i det här fallet - provisionprocenten om försäljningsvärdet var under tröskeln. Om svaret på frågan är "nej"( false), använder vi -värdet om falsk -parameter för funktionen, nämligen B7 i det här fallet - provisionprocenten om försäljningsvärdet var över tröskeln.

Som du ser kan du använda en försäljningsomgång på $ 20.000, vilket ger oss en procentsats på 20% i cell B2.Om vi ​​anger ett värde på $ 40.000 får vi en annan provisionskvot:

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( -värdet om falskt ) nu är en hel IF-funktion i sig. Detta kallas en inbäddad 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 på hur och varför det här fungerar, inte heller kommer vi att undersöka nyanserna för 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 omarbeta arbetsbladet lite. Vi håller alla samma siffror, men organiserar det på ett nytt sätt, en mer tabellformat -väg:

Ta en stund och verifiera själv att det nya -takten fungerar exakt samma som tröskelvärdena ovan.

Konceptuellt är vad vi ska göra, använd VLOOKUP för att leta upp säljareens försäljningsvärde( från B1) i räntetabellen och returnera till oss motsvarande procentsats. Observera att säljaren faktiskt kan ha skapat en försäljning som är inte en 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.Det är viktigt att notera att $ 34,988 visar inte i räntetabellen. Låt oss se om VLOOKUP kan lösa vårt problem ändå. ..

Vi väljer cell B2( den plats vi vill lägga vår formel) och sedan infoga VLOOKUP-funktionen från Formulas fliken:

Funktionsargument -rutan för VLOOKUP visas. Vi fyller i argumenten( parametrar) en efter en, som börjar med Lookup_value , vilket i detta fall är försäljningsvärdet från cell B1.Vi placerar markören i Lookup_value -fältet och klickar sedan en gång på cell B1:

Nästa måste vi ange för VLOOKUP vilken tabell som ska söka upp dessa data. I det här exemplet är det naturligtvis kurstabellen. Vi placerar markören i Table_array -fältet och markerar sedan hela kurstabellen - , 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 procentsatsen, som finns i den andra kolumnen i tabellen, så vi anger därför en 2 i -fältet Col_index_num :

Slutligen anger vi ett värde i Range_lookup -fältet.

Viktigt: Det är användningen av detta fält som skiljer de två sätten att använda VLOOKUP.För att använda VLOOKUP med en databas måste den här sista parametern Range_lookup alltid ställas in till FALSE , men med denna andra användning av VLOOKUP måste vi antingen lämna den tomma eller ange ett värde av TRUE .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 för true i Range_lookup -fältet. 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, kan vi försäkra oss om att formeln fungerar.

Slutsats

I databasversionen av VLOOKUP, där Range_lookup -parametern är FALSE , måste värdet som passeras i den första parametern( Lookup_value ) 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å en försäljningsomgång på $ 34,988, 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 Range_lookup är inställd på TRUE ( eller utelämnad), kommer VLOOKUP att se i kolumn 1 och matcha det högsta värdet som inte är större än Lookup_value -parametern.

Det är också viktigt att notera att för att systemet ska fungera, måste bordet sorteras i stigande ordning på kolumn 1 !

Om du vill träna med VLOOKUP kan du hämta den exempelfil som illustreras i den här artikeln.