För länge sedan arbetade jag för vice vd för försäljning hos ett företag. Jag modellerade alltid något nytt bonusprogram eller provision. Jag blev ganska van vid att beställa planer med alla möjliga förhållanden. Den som visas i detta tips är ganska tam.
Den normala metoden är att börja bygga en kapslad IF-formel. Du börjar alltid i antingen high end eller low end i intervallet. “Om försäljningen är över 500 000 USD, är rabatten 20%; annars, …. ” Det tredje argumentet för IF-funktionen är en helt ny IF-funktion som testar för andra nivån: "Om försäljningen är över $ 250 000, är rabatten 15%; annars …"
Dessa formler blir längre och längre eftersom det finns fler nivåer. Den tuffaste delen av en sådan formel är att komma ihåg hur många parenteser som ska läggas i slutet av formeln.
Om du använder Excel 2003 närmar sig formeln redan gränsen. Med den versionen kan du inte bygga fler än 7 IF-funktioner. Det var en ful dag då befogenheterna som ändras kommissionsplanen och du behövde ett sätt att lägga till en åttonde IF-funktion. Idag kan du bygga 64 IF-funktioner. Du borde aldrig bo så många, men det är trevligt att veta att det inte är något problem att häcka 8 eller 9.
Istället för att använda den kapslade IF-funktionen, försök använda VLOOKUP-funktionen. När det fjärde argumentet i VLOOKUP ändras från False till True, letar funktionen inte längre efter en exakt matchning. Tja, först försöker VLOOKUP hitta en exakt matchning. Men om en exakt matchning inte hittas, placerar Excel sig i raden bara mindre än vad du letar efter.
Tänk på tabellen nedan. I cell C13 letar Excel efter en matchning för $ 28 355 i tabellen. När den inte kan hitta 28355 returnerar Excel rabatten som är associerad med det värde som bara är mindre - i detta fall, 1% rabatt för $ 10K-nivån.
När du konverterar reglerna till tabellen i E13: F18 måste du börja från den minsta nivån och fortsätta till den högsta nivån. Även om det inte anges i reglerna kommer rabatten att vara 0% om någon är under 10 000 USD i försäljning. Du måste lägga till detta som den första raden i tabellen.
Varning
När du använder den ”sanna” versionen av VLOOKUP måste din tabell sorteras stigande. Många tror att alla uppslagstabeller måste sorteras. Men en tabell behöver bara sorteras för en ungefärlig matchning.
Vad händer om din chef vill ha en helt fristående formel och inte vill se bonustabellen till höger? Efter att ha byggt formeln kan du bädda in tabellen direkt i formeln. Sätt formeln i redigeringsläge genom att dubbelklicka på cellen eller genom att markera cellen och trycka på F2. Använd markören för att välja hela det andra argumentet: $ E $ 13: $ F $ 18.
Tryck på F9-tangenten. Excel bäddar in uppslagstabellen som en arraykonstant. I arraykonstanten indikerar ett semikolon en ny rad och ett komma indikerar en ny kolumn. Se Förstå arraykonstanter.
Tryck enter. Kopiera formeln till de andra cellerna.
Du kan nu radera tabellen. Den slutliga formeln visas nedan.
Tack till Mike Girvin för att du lärde mig om matchande parenteser. VLOOKUP-tekniken föreslogs av Danny Mac, Boriana Petrova, Andreas Thehos och @mvmcos.
