Innehåll
Vi dedikerade nyligen en artikel till en av de mest användbara Excel-funktionerna som kallas VPR och visade hur den kan användas för att extrahera den information som krävs från en databas till en kalkylbladscell. Vi nämnde också att det finns två användningsfall för funktionen VPR och endast en av dem behandlar databasfrågor. I den här artikeln kommer du att lära dig ett annat mindre känt sätt att använda funktionen VPR i Excel.
Om du inte har gjort detta ännu, se till att läsa den senaste artikeln om funktionen VPR, eftersom all information nedan förutsätter att du redan är bekant med principerna som beskrivs i den första artikeln.
När man arbetar med databaser, funktioner VPR en unik identifierare skickas, som används för att identifiera den information vi vill hitta (till exempel en produktkod eller ett kundidentifikationsnummer). Denna unika kod måste finnas i databasen, annars VPR kommer att rapportera ett fel. I den här artikeln kommer vi att titta på det här sättet att använda funktionen VPRnär id:t inte finns i databasen alls. Som om funktionen VPR växlat till ungefärligt läge och väljer vilken data vi ska tillhandahålla när vi vill hitta något. Under vissa omständigheter är det precis vad som behövs.
Ett exempel från livet. Vi satte uppgiften
Låt oss illustrera den här artikeln med ett exempel från verkligheten – beräkning av provisioner baserat på ett brett utbud av försäljningsmått. Vi börjar med ett mycket enkelt alternativ, och sedan kommer vi gradvis att komplicera det tills den enda rationella lösningen på problemet är att använda funktionen VPR. Det initiala scenariot för vår fiktiva uppgift är som följer: om en säljare tjänar mer än 30000 30 USD i försäljning på ett år, är hans provision 20 %. Annars är provisionen bara XNUMX %. Låt oss sätta det i form av en tabell:
Säljaren anger sina försäljningsdata i cell B1, och formeln i cell B2 bestämmer den korrekta provisionssatsen som säljaren kan förvänta sig. I sin tur används den resulterande kursen i cell B3 för att beräkna den totala provisionen säljaren ska få (helt enkelt multiplicera cellerna B1 och B2).
Den mest intressanta delen av tabellen finns i cell B2 - detta är formeln för att bestämma provisionssatsen. Denna formel innehåller en Excel-funktion som kallas IF (OM). För de läsare som inte är bekanta med den här funktionen kommer jag att förklara hur den fungerar:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Skick är ett funktionsargument som tar värdet av antingen SANT KOD (SANT), eller FALSK (FALSK). I exemplet ovan, uttrycket B1
Är det sant att B1 är mindre än B5?
Eller så kan du säga det annorlunda:
Stämmer det att den totala försäljningen för året är lägre än tröskelvärdet?
Om vi svarar på denna fråga JA (TRUE), sedan returnerar funktionen värde om sant (värde om TRUE). I vårt fall kommer detta att vara värdet på cell B6, dvs provisionssats när den totala försäljningen ligger under tröskeln. Om vi svarar på frågan NEJ (FALSK) kommer sedan tillbaka värde om falskt (värde om FALSK). I vårt fall är detta värdet på cell B7, dvs provisionssatsen när den totala försäljningen ligger över tröskeln.
Som du kan se, om vi tar den totala försäljningen på $20000 2, får vi en provision på 20% i cell B40000. Om vi anger ett värde på 30 XNUMX USD kommer provisionssatsen att ändras med XNUMX %:
Så här fungerar vårt bord.
Vi komplicerar uppgiften
Låt oss göra saker lite svårare. Låt oss sätta ett annat tröskelvärde: om säljaren tjänar mer än 40000 40 USD, ökar provisionssatsen till XNUMX %:
Allt verkar vara enkelt och tydligt, men vår formel i cell B2 blir märkbart mer komplicerad. Om du tittar noga på formeln kommer du att se det tredje argumentet för funktionen IF (IF) förvandlats till en annan fullvärdig funktion IF (OM). Denna konstruktion kallas inkapsling av funktioner i varandra. Excel tillåter gärna dessa konstruktioner, och de fungerar till och med, men de är mycket svårare att läsa och förstå.
Vi kommer inte att fördjupa oss i de tekniska detaljerna - varför och hur det fungerar, och vi kommer inte att gå in på nyanserna av att skriva kapslade funktioner. Det här är trots allt en artikel tillägnad funktionen VPR, inte en komplett guide till Excel.
Hur som helst, formeln blir mer komplicerad! Tänk om vi inför ett annat alternativ för en provision på 50 % för de säljare som tjänar mer än 50000 60000 USD i försäljning. Och om någon har sålt mer än $60 XNUMX, kommer de att betala XNUMX% i provision?
Nu har formeln i cell B2, även om den skrevs utan fel, blivit helt oläslig. Jag tror att det är få som vill använda formler med 4 nivåer av kapsling i sina projekt. Det måste finnas ett enklare sätt?!
Och det finns ett sådant sätt! Funktionen kommer att hjälpa oss VPR.
Vi använder VLOOKUP-funktionen för att lösa problemet
Låt oss ändra designen på vårt bord lite. Vi kommer att behålla samma fält och data, men ordna dem på ett nytt, mer kompakt sätt:
Ta en stund och se till att det nya bordet Pristabell innehåller samma data som den föregående tröskeltabellen.
Huvudtanken är att använda funktionen VPR för att bestämma den önskade tullsatsen enligt tabellen Pristabell beroende på försäljningsvolym. Observera att säljaren kan sälja varor för ett belopp som inte är lika med en av de fem tröskelvärdena i tabellen. Han kunde till exempel sälja för 34988 XNUMX $, men det finns inget sådant belopp. Låt oss se hur det fungerar VPR kan hantera en sådan situation.
Infoga en VLOOKUP-funktion
Välj cell B2 (där vi vill infoga vår formel) och hitta VLOOKUP (VLOOKUP) i Excel-funktionsbiblioteket: Formler (formler) > Funktionsbibliotek (Funktionsbibliotek) > Sökning och referens (Referenser och arrayer).
En dialogruta visas Funktionsargument (Funktionsargument). Vi fyller i värdena för argumenten en efter en, med början med Uppslagningsvärde (Uppslagsvärde). I det här exemplet är detta det totala försäljningsbeloppet från cell B1. Placera markören i fältet Uppslagningsvärde (Lookup_value) och välj cell B1.
Därefter måste du specificera funktionerna VPRvar man ska leta efter data. I vårt exempel är detta en tabell Pristabell. Placera markören i fältet Tabell_array (Tabell) och välj hela tabellen Pristabellförutom rubriker.
Därefter måste vi ange vilken kolumn vi ska extrahera data från med vår formel. Vi är intresserade av provisionssatsen, som finns i den andra kolumnen i tabellen. Därför för argumentet Col_index_num (Column_number) ange värdet 2.
Och slutligen introducerar vi det sista argumentet – Range_lookup (Interval_lookup).
Viktigt: det är användningen av detta argument som gör skillnaden mellan de två sätten att tillämpa funktionen VPR. När du arbetar med databaser, argumentet Range_lookup (range_lookup) måste alltid ha ett värde FALSK (FALSK) för att söka efter en exakt matchning. I vår användning av funktionen VPRmåste vi lämna detta fält tomt, eller ange ett värde SANT KOD (SANN). Det är extremt viktigt att välja detta alternativ korrekt.
För att göra det tydligare kommer vi att presentera SANT KOD (TRUE) i fältet Range_lookup (Interval_lookup). Även om du lämnar fältet tomt kommer detta inte att vara ett fel, eftersom SANT KOD är dess standardvärde:
Vi har fyllt i alla parametrar. Nu trycker vi på OK, och Excel skapar en formel för oss med en funktion VPR.
Om vi experimenterar med flera olika värden för det totala försäljningsbeloppet kommer vi att se till att formeln fungerar korrekt.
Slutsats
När funktionen VPR fungerar med databaser, argument Range_lookup (range_lookup) måste acceptera FALSK (FALSK). Och värdet angavs som Uppslagningsvärde (Lookup_value) måste finnas i databasen. Det är med andra ord ute efter en exakt matchning.
I exemplet vi har tittat på i den här artikeln finns det ingen anledning att få en exakt matchning. Detta är fallet när funktionen VPR måste växla till ungefärligt läge för att returnera önskat resultat.
Till exempel: Vi vill bestämma vilken kurs som ska användas i provisionsberäkningen för en säljare med en försäljningsvolym på 34988 XNUMX USD. Fungera VPR ger oss ett värde på 30 %, vilket är helt korrekt. Men varför valde formeln raden som innehåller exakt 30 % och inte 20 % eller 40 %? Vad menas med ungefärlig sökning? Låt oss vara tydliga.
När argumentet Range_lookup (interval_lookup) har ett värde SANT KOD (TRUE) eller utelämnad, funktion VPR itererar genom den första kolumnen och väljer det största värdet som inte överstiger uppslagsvärdet.
Viktig poäng: För att detta schema ska fungera måste den första kolumnen i tabellen sorteras i stigande ordning.