Har du en Excel-formel som hekker flere IF-funksjoner? Når du kommer til poenget med for mange nestede IF-setninger, må du se om det hele vil bli enklere med en enkel VLOOKUP-funksjon. Jeg har sett 1000 tegnformler forenkles til en VLOOKUP-formel på 30 tegn. Det er enkelt å vedlikeholde når du må legge til nye verdier senere.
For lenge siden jobbet jeg for visepresident for salg på jobben. Jeg modellerte alltid noe nytt bonusprogram eller provisjonsplan. Jeg ble ganske vant til å bestille planer med alle slags forhold. Den nedenfor 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 $ 250K, 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.
Mini Bonus Tips
Matcher parentesene
Excel blar gjennom en rekke farger for hvert nye parentesnivå. Mens Excel gjenbruker fargene, bruker den bare svart for den innledende parentesen og for den samsvarende lukkende parentesen. Når du er ferdig med formelen nedenfor, fortsetter du bare å skrive lukkede parenteser til du skriver en svart parentes.
Tilbake til Nested Formula Tip
Hvis du bruker Excel 2003, nærmer du deg allerede formelen. Da kunne 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 bør aldri gjøre dette, 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 den uvanlige bruken for 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 i tilfelle 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.
Velg hele argumentet ved hjelp av markøren: $ E $ 13: $ F $ 18.
Trykk på F9-tasten. Excel vil legge inn oppslagstabellen som en matrisekonstant. I matrisekonstanten indikerer et semikolon en ny rad, og et komma angir en ny kolonne.
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.
Se på video
- Med et nivåert kommisjon-, bonus- eller rabattprogram må du ofte hekke IF-funksjonene dine
- Excel 2003-grensen var 7 nestede IF-setninger.
- Du kan nå rede 32, men jeg tror ikke du noen gang burde rede 32
- Når vil du noen gang bruke den omtrentlige kampversjonen av VLOOKUP? Dette er tiden.
- Oversett rabattprogrammet til en oppslagstabell
- VLOOKUP finner ikke svaret i de fleste tilfeller.
- Satt, sant på slutten vil fortelle VLOOKUP å finne verdien bare mindre.
- Dette er den eneste gangen VLOOKUP-tabellen må sorteres.
- Vil du ikke ha VLOOKUP-bordet til siden? Bygg den inn i formelen.
- F2 for å redigere formelen. Velg oppslagstabellen. Trykk på F9. Tast inn.
Videoutskrift
Lær Excel fra podcast, episode 2030 - Erstattet nestet IF med VLOOKUP!
Jeg podcaster hele denne boken, klikk på “i” øverst til høyre for å komme til spillelisten!
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. OK, dette er veldig vanlig med enten et kommisjonsprogram eller et rabattprogram eller et bonusprogram, hvor vi har nivåene, forskjellige nivåer, ikke sant? Hvis du er over $ 10000, får du 1% rabatt, $ 50000 5% rabatt. Du må være forsiktig når du bygger denne nestede IF-setningen, du starter den i toppenden hvis du leter etter større enn, eller i bunnen av hvis du leter etter mindre enn. Så B10> 50000, 20%, ellers en annen IF, B10> 250000, 25%, og så videre og så videre. Dette er bare en lang og komplisert formel, og hvis tabellen din er større, i Excel 2003, kunne du ikke hekke mer enn 7 IF-setninger, vi er nesten nær grensen her. Du kan gå til 32 nå, jeg tror ikke du skulle gå til 32, og selv om du skriker "Hei vent,hva med den nye funksjonen som nettopp kom ut i Excel 2016, utgivelsen fra februar 2016, med IFS-funksjonen? ” Ja sikkert, det er mindre parenteser her, og 87 tegn i stedet for 97 tegn, det er fortsatt et rot, la oss bli kvitt dette og gjøre det med en VLOOKUP!
OK, her er trinnene, du tar disse reglene, og du snur dem på hodet. Det første vi må si er at hvis du hadde $ 0 i salg, får du 0% rabatt, hvis du hadde $ 10000 i salg, får du 1% rabatt, hvis du har $ 50000 i salg, får du 5% rabatt . $ 100000, 10% rabatt, $ 250000, 15% rabatt, og til slutt, alt> $ 500000 får 20% rabatt, ok. Nå mange ganger, hver gang jeg snakker om VLOOKUP, sier jeg at hver VLOOKUP som du noen gang oppretter vil ende på FALSE, og folk vil si "Vel vent et sekund, hva er den SANTE versjonen der for uansett?" Det er for akkurat dette tilfellet der vi leter etter en rekkevidde, så vi ser etter denne verdien, en vanlig VLOOKUP, selvfølgelig, 2, FALSE vil ikke finne 550000, kommer til å returnere # N / A!Så i dette helt spesielle tilfellet skal vi endre det FALSE til et SANN, og det vil fortelle Excel "Gå og se etter 550000, hvis du ikke finner den, gi oss verdien som er bare mindre." Så det gir oss 20%, det er det den gjør, ok? Og dette er den eneste gangen VLOOKUP-tabellen din må sorteres, alle de andre gangene med, FALSE, det kan være slik du vil. Og ja, du kan la det, SANTE, men jeg legger det alltid der for å minne meg selv på at dette er en av de rare utrolig farlige VLOOKUP-ene, og jeg vil ikke kopiere denne formelen et annet sted. OK, så vi kopierer og limer inn spesielle formler, ok.ok? Og dette er den eneste gangen VLOOKUP-tabellen din må sorteres, alle andre gangene med, FALSE, det kan være slik du vil. Og ja, du kan la det, SANTE, men jeg legger det alltid der for å minne meg selv på at dette er en av de rare utrolig farlige VLOOKUP-ene, og jeg vil ikke kopiere denne formelen et annet sted. OK, så vi kopierer og limer inn spesielle formler, ok.ok? Og dette er den eneste gangen VLOOKUP-tabellen din må sorteres, alle andre gangene med, FALSE, det kan være slik du vil. Og ja, du kan la det, SANTE, men jeg legger det alltid der for å minne meg selv på at dette er en av de rare utrolig farlige VLOOKUP-ene, og jeg vil ikke kopiere denne formelen et annet sted. OK, så vi kopierer og limer inn spesielle formler, ok.
Neste klage: lederen din vil ikke se oppslagstabellen, han vil ha den "Bare kvitt oppslagstabellen." Greit, vi kan gjøre det ved å legge inn oppslagstabellen, sjekk dette, flott triks som jeg fikk fra Mike Girvin på ExcelIsFun. F2 for å sette formelen i redigeringsmodus, og velg deretter veldig nøyaktig området for oppslagstabellen. Trykk på F9, og det tar denne tabellen og setter den i array-nomenklatur, og så trykker jeg bare Enter sånn. Kopier det ned, Lim inn spesielle formler, og så er jeg fri til å kvitte meg med oppslagstabellen, som bare fortsetter å jobbe nede på raden. Dette er et veldig problem hvis du må komme inn og redigere dette, må du virkelig stirre på det med mye klarhet. Kommaet betyr at vi går til neste kolonne, semikolon betyr at skal neste rad, ok,men du kan i teorien komme inn dit og endre den formelen hvis et av kjedenumrene endres.
Greit, så å bruke en VLOOKUP i stedet for en nestet IF-setning er tips nr. 32 på vei til 40, "40 Greatest Excel Tips of All Time", du kan ha hele denne boka. Klikk på “i” øverst til høyre, $ 10 for en e-bok, $ 25 for utskriftsboken, ok. Så i dag prøver vi å løse en trinnvis provisjonsbonus, eller et rabattprogram. Nestede IF-er er veldig vanlige, se opp, 7 er alt du kan ha i Excel 2003, i dag kan du ha 32, men du burde aldri ha 32. Så når du skal bruke den omtrentlige MATCH-versjonen av VLOOKUP, er dette det. Ta det rabattprogrammet, snu det på hodet, gjør det til et oppslagstabell som begynner med det minste tallet og går til det største tallet. VLOOKUP, selvfølgelig, finner ikke svaret, men ved å endre VLOOKUP til, SANN på slutten, vil den fortelle det å finne verdien bare mindre,dette er den eneste gangen tabellen må sorteres. Hvis du ikke vil se den tabellen der ute, kan du legge den inn i formelen ved å bruke Mike Girvin-trikset, F2 for å redigere formelen, velge oppslagstabellen, trykke F9 og deretter Enter.
Greit hei, jeg vil takke deg for at du var innom, vi sees neste gang for nok en netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2030.xlsx
