For længe siden arbejdede jeg for vicepræsident for salg hos en virksomhed. Jeg modellerede altid noget nyt bonusprogram eller kommissionsplan. Jeg blev temmelig vant til at bestille planer med alle mulige forhold. Den der er vist i dette tip er temmelig tam.
Den normale tilgang er at begynde at opbygge en indlejret IF-formel. Du starter altid i enten den høje eller den lave ende af området. “Hvis salget er over $ 500.000, er rabatten 20%; Ellers,… ." Det tredje argument for IF-funktionen er en helt ny IF-funktion, der tester for det andet niveau: "Hvis salget er over $ 250.000, er rabatten 15%; ellers …"
Disse formler bliver længere og længere, da der er flere niveauer. Den hårdeste del af en sådan formel er at huske, hvor mange lukkende parenteser der skal placeres i slutningen af formlen.
Hvis du bruger Excel 2003, nærmer din formel allerede grænsen. Med den version kan du ikke rede mere end 7 IF-funktioner. Det var en grim dag, hvor beføjelserne, der ændrede kommissionsplanen, og du havde brug for en måde at tilføje en ottende IF-funktion på. I dag kan du rede 64 IF-funktioner. Du skal aldrig rede så mange, men det er rart at vide, at der ikke er noget problem at rede 8 eller 9.
I stedet for at bruge den indlejrede IF-funktion, så prøv at bruge VLOOKUP-funktionen. Når det fjerde argument i VLOOKUP skifter fra False til True, er funktionen ikke længere på udkig efter et nøjagtigt match. Først forsøger VLOOKUP at finde et nøjagtigt match. Men hvis der ikke findes et nøjagtigt match, afregner Excel sig i rækken bare mindre end det, du leder efter.
Overvej tabellen nedenfor. I celle C13 vil Excel være på udkig efter et match for $ 28.355 i tabellen. Når den ikke kan finde 28355, returnerer Excel den rabat, der er knyttet til den værdi, der bare er mindre - i dette tilfælde 1% rabat for $ 10K-niveauet.
Når du konverterer reglerne til tabellen i E13: F18, skal du starte fra det mindste niveau og fortsætte til det højeste niveau. Selvom det ikke var angivet i reglerne, vil rabatten være 0%, hvis nogen er under $ 10.000 i salg. Du skal tilføje dette som den første række i tabellen.
Advarsel
Når du bruger den “sande” version af VLOOKUP, skal din tabel sorteres stigende. Mange mennesker tror, at alle opslagstabeller skal sorteres. Men et bord skal kun sorteres for en omtrentlig kamp.
Hvad hvis din manager ønsker en helt selvstændig formel og ikke ønsker at se bonustabellen til højre? Når du har bygget formlen, kan du integrere tabellen lige ind i formlen. Sæt formlen i redigeringstilstand ved at dobbeltklikke på cellen eller ved at vælge cellen og trykke på F2. Brug markøren til at vælge hele det andet argument: $ E $ 13: $ F $ 18.
Tryk på F9-tasten. Excel indlejrer opslagstabellen som en matrixkonstant. I matrixkonstanten angiver et semikolon en ny række, og et komma angiver en ny kolonne. Se Forståelse af matrixkonstanter.
Tryk på Enter. Kopier formlen ned til de andre celler.
Du kan nu slette tabellen. Den endelige formel er vist nedenfor.
Tak til Mike Girvin for at lære mig om de matchende parenteser. VLOOKUP-teknikken blev foreslået af Danny Mac, Boriana Petrova, Andreas Thehos og @mvmcos.
