Har du en Excel-formel som kapar flera IF-funktioner? När du kommer till punkten med för många kapslade IF-uttalanden måste du se om det hela skulle bli enklare med en enkel VLOOKUP-funktion. Jag har sett formler på 1000 tecken förenklas till en VLOOKUP-formel på 30 tecken. Det är lätt att underhålla när du måste lägga till nya värden senare.
För länge sedan arbetade jag för vice vd för försäljning på jobbet. 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 nedan ä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.
Mini Bonus Tips
Matcha parenteserna
Excel går igenom olika färger för varje ny nivå inom parentes. Medan Excel återanvänder färgerna, använder den bara svart för den inledande parentesen och för den matchande stängande parentesen. När du avslutar formeln nedan fortsätter du bara att skriva stängande parenteser tills du skriver en svart parentes.
Tillbaka till Nested Formula Tip
Om du använder Excel 2003 närmar sig formeln redan gränsen. Då kunde du inte bygga mer ä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 göra det här, men det är trevligt att veta att det inte finns några problem att häcka 8 eller 9.
Istället för att använda den kapslade IF-funktionen, försök använda den ovanliga användningen för 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 den rabatt som är associerad med det värde som är mindre. I det här fallet 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 om du gö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.
Välj hela det andra argumentet med hjälp av markören: $ E $ 13: $ F $ 18.
Tryck på F9-tangenten. Excel kommer att bädda in uppslagstabellen som en arraykonstant. I arraykonstanten indikerar ett semikolon en ny rad och ett komma indikerar en ny kolumn.
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.
Kolla på video
- Med ett differentierat provision-, bonus- eller rabattprogram måste du ofta bygga upp dina IF-funktioner
- Gränsen för Excel 2003 var 7 kapslade IF-uttalanden.
- Du kan nu bo 32, men jag tror inte att du någonsin borde bo 32
- När skulle du någonsin använda den ungefärliga versionen av VLOOKUP? Det är dags.
- Översätt rabattprogrammet till en uppslagstabell
- VLOOKUP hittar inte svaret i de flesta fall.
- Att sätta, sant i slutet kommer att berätta för VLOOKUP att hitta värdet bara mindre.
- Det här är enda gången VLOOKUP-tabellen måste sorteras.
- Vill du inte ha VLOOKUP-bordet åt sidan? Bädda in den i formeln.
- F2 för att redigera formeln. Välj uppslagstabellen. Tryck på F9. Stiga på.
Videoutskrift
Lär dig Excel från podcast, avsnitt 2030 - Ersatt Nested IF med VLOOKUP!
Jag podcastar hela denna bok, klicka på "i" i det övre högra hörnet för att komma till spellistan!
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Okej, detta är verkligen vanligt med antingen ett provisionsprogram eller ett rabattprogram eller ett bonusprogram, där vi har nivåerna, olika nivåer, eller hur? Om du överstiger 10000 $ får du 1% rabatt, 50000 $ 5% rabatt. Du måste vara försiktig när du bygger detta kapslade IF-uttalande, du startar det i den övre änden om du letar efter större än eller i nedre änden om du letar efter mindre än. Så B10> 50000, 20%, annars en annan IF, B10> 250000, 25%, och så vidare och så vidare. Det här är bara en lång och komplicerad formel, och om din tabell är större, i Excel 2003, kunde du inte bygga mer än 7 IF-uttalanden, vi är nästan nära gränsen här. Du kan gå till 32 nu, jag tror inte att du någonsin ska gå till 32, och även om du skriker "Hej vänta,hur är det med den nya funktionen som just kom ut i Excel 2016, utgåvan i februari 2016, med IFS-funktionen? ” Ja visst, det finns mindre parenteser här och 87 tecken istället för 97 tecken, det är fortfarande en röra, låt oss bli av med detta och göra det med en VLOOKUP!
Okej, här är stegen, du tar dessa regler och vänder dem på huvudet. Det första vi måste säga är att om du hade $ 0 i försäljning, får du 0% rabatt, om du hade $ 10000 i försäljning, får du 1% rabatt, om du har $ 50000 i försäljning, får du 5% rabatt . 100000 $, 10% rabatt, 250000 $, 15% rabatt, och äntligen får allt> 500000 $ 20% rabatt, okej. Nu många gånger, varje gång jag talar om VLOOKUP, säger jag att varje VLOOKUP som du någonsin skapar kommer att sluta på FALSE, och folk kommer att säga "Tja, vänta en sekund, vad är den SANNA versionen där för i alla fall?" Det är just för detta fall där vi letar efter ett intervall, så vi letar upp detta värde, en vanlig VLOOKUP, naturligtvis, 2, FALSE kommer inte att hitta 550000, kommer att returnera # N / A!Så i det här mycket speciella fallet kommer vi att ändra det FALSKA till SANT, och det kommer att säga till Excel "Gå leta efter 550000, om du inte hittar den, ge oss det värde som är bara mindre." Så det ger oss 20%, det är vad det gör, okej? Och det här är den enda gången din VLOOKUP-tabell måste sorteras, alla andra gånger med, FALSE, det kan vara hur du vill. Och ja, du kan lämna den, SANTA, men jag lägger den alltid där bara för att påminna mig själv om att det här är en av de konstiga otroligt farliga VLOOKUP: erna, och jag vill inte kopiera denna formel någon annanstans. Okej, så vi kopierar och klistrar in specialformler, okej.OK? Och det här är den enda gången din VLOOKUP-tabell måste sorteras, alla andra gånger med, FALSE, det kan vara hur du vill. Och ja, du kan lämna, SANT, men jag lägger det alltid där för att påminna mig själv om att detta är en av de konstiga otroligt farliga VLOOKUP: erna, och jag vill inte kopiera denna formel någon annanstans. Okej, så vi kopierar och klistrar in specialformler, okej.OK? Och det här är den enda gången din VLOOKUP-tabell måste sorteras, alla andra gånger med, FALSE, det kan vara hur du vill. Och ja, du kan lämna den, SANTA, men jag lägger den alltid där bara för att påminna mig själv om att det här är en av de konstiga otroligt farliga VLOOKUP: erna, och jag vill inte kopiera denna formel någon annanstans. Okej, så vi kopierar och klistrar in specialformler, okej.
Nästa klagomål: din chef vill inte se uppslagstabellen, han vill ha den "Bli bara av med den uppslagstabellen." Okej, vi kan göra det genom att bädda in uppslagstabellen, kolla in det här, bra trick som jag fick från Mike Girvin på ExcelIsFun. F2 för att sätta formeln i redigeringsläge, och välj sedan mycket noggrant bara intervallet för uppslagstabellen. Tryck på F9 så tar den här tabellen och placerar den i arraynomenklaturen, och sedan trycker jag bara på Enter så. Kopiera det ner, klistra in specialformler, och då är jag fri att bli av med uppslagstabellen, så att allt bara fortsätter att fungera på raden. Det här är ett stort besvär om du måste komma in igen och redigera detta, du måste verkligen stirra på det med mycket tydlighet. Kommat betyder att vi går till nästa kolumn, semikolon betyder att vi kommer till nästa rad, okej,men du kan i teorin komma tillbaka dit och ändra den formeln om ett av kedjanumren ändras.
Okej, så att använda en VLOOKUP istället för ett kapslat IF-uttalande är tips nr 32 på väg till 40, "40 Greatest Excel Tips of All Time", du kan få hela denna bok. Klicka på "i" i det övre högra hörnet, $ 10 för en e-bok, $ 25 för den tryckta boken, okej. Så idag försöker vi lösa en differentierad provision eller rabattprogram. Kapslade IF är riktigt vanliga, se upp, 7 är allt du kan ha i Excel 2003, idag kan du ha 32, men du borde aldrig ha 32. Så när du ska använda den ungefärliga MATCH-versionen av VLOOKUP är det här. Ta det rabattprogrammet, vänd det upp och ner, gör det till en uppslagstabell som börjar med det minsta antalet och går till det största antalet. VLOOKUP kommer naturligtvis inte att hitta svaret, men genom att ändra VLOOKUP till, SANT i slutet, kommer det att säga att det ska hitta värdet bara mindre,det är enda gången tabellen måste sorteras. Om du inte vill se den tabellen där ute kan du bädda in den i formeln med Mike Girvin-tricket, F2 för att redigera formeln, välja uppslagstabellen, trycka på F9 och sedan på Enter.
Okej hej, jag vill tacka dig för att du kom förbi, vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2030.xlsx
