For lenge siden jobbet jeg for visepresident for salg i et selskap. Jeg modellerte alltid noe nytt bonusprogram eller provisjonsplan. Jeg ble ganske vant til å bestille planer med alle slags forhold. Den som vises i dette tipset er ganske tam.
Den normale tilnærmingen er å begynne å bygge en nestet IF-formel. Du begynner alltid i den høye eller lave enden av området. “Hvis salget er over $ 500 000, er rabatten 20%; ellers,… ." Det tredje argumentet til IF-funksjonen er en helt ny IF-funksjon som tester for andre nivå: "Hvis salget er over $ 250 000, er rabatten 15%; ellers …"
Disse formlene blir lenger og lenger ettersom det er flere nivåer. Den tøffeste delen av en slik formel er å huske hvor mange parenteser du skal sette på slutten av formelen.
Hvis du bruker Excel 2003, nærmer du deg allerede formelen. Med den versjonen kan du ikke hekke mer enn 7 IF-funksjoner. Det var en stygg dag da maktene som endret kommisjonsplanen, og du trengte en måte å legge til en åttende IF-funksjon. I dag kan du hekke 64 IF-funksjoner. Du skal aldri hekke så mange, men det er hyggelig å vite at det ikke er noe problem å hekke 8 eller 9.
I stedet for å bruke den nestede IF-funksjonen, kan du prøve å bruke VLOOKUP-funksjonen. Når det fjerde argumentet til VLOOKUP endres fra False til True, leter funksjonen ikke lenger etter en nøyaktig samsvar. Vel, først prøver VLOOKUP å finne en nøyaktig samsvar. Men hvis det ikke blir funnet et nøyaktig samsvar, legger Excel seg i raden bare mindre enn det du søker etter.
Tenk på tabellen nedenfor. I celle C13 vil Excel lete etter en kamp for $ 28,355 i tabellen. Når den ikke finner 28355, returnerer Excel rabatten assosiert med verdien som er bare mindre - i dette tilfellet, 1% rabatt for $ 10K-nivået.
Når du konverterer reglene til tabellen i E13: F18, må du starte fra det minste nivået og fortsette til det høyeste nivået. Selv om det ikke var angitt i reglene, vil rabatten være 0% hvis noen er under $ 10.000 i salg. Du må legge til dette som den første raden i tabellen.
Forsiktighet
Når du bruker den "sanne" versjonen av VLOOKUP, må tabellen din sorteres stigende. Mange tror at alle oppslagstabeller må sorteres. Men et bord må bare sorteres for en omtrentlig kamp.
Hva om lederen din vil ha en helt selvstendig formel og ikke vil se bonusbordet til høyre? Etter å ha bygget formelen kan du legge inn tabellen rett inn i formelen. Sett formelen i redigeringsmodus ved å dobbeltklikke på cellen eller ved å velge cellen og trykke F2. Bruk markøren til å velge hele det andre argumentet: $ E $ 13: $ F $ 18.
Trykk på F9-tasten. Excel bygger oppslagstabellen som en matrisekonstant. I matrisekonstanten indikerer et semikolon en ny rad, og et komma angir en ny kolonne. Se Forstå matrisekonstanter.
Trykk enter. Kopier formelen ned til de andre cellene.
Du kan nå slette tabellen. Den endelige formelen er vist nedenfor.
Takk til Mike Girvin for at du lærte meg om de parentesene som passer. VLOOKUP-teknikken ble foreslått av Danny Mac, Boriana Petrova, Andreas Thehos og @mvmcos.
