Heb je een Excel-formule die meerdere IF-functies nest? Wanneer u ter zake komt met te veel geneste IF-instructies, moet u zien of het geheel eenvoudiger zou worden met een eenvoudige VERT.ZOEKEN-functie. Ik heb gezien dat formules van 1000 tekens werden vereenvoudigd tot een VERT.ZOEKEN-formule van 30 tekens. Het is gemakkelijk te onderhouden wanneer u later nieuwe waarden moet toevoegen.
Lang geleden werkte ik voor de vice-president verkoop op het werk. Ik was altijd een nieuw bonusprogramma of commissieplan aan het modelleren. Ik raakte er behoorlijk aan gewend om plannen met allerlei voorwaarden te laten maken. De onderstaande is vrij tam.
De normale benadering is om te beginnen met het bouwen van een geneste IF-formule. Je begint altijd aan de hoge of lage kant van het bereik. “Als de verkoop meer dan $ 500.000 bedraagt, is de korting 20%; anders…. " Het derde argument van de ALS-functie is een geheel nieuwe ALS-functie die test voor het tweede niveau: als de verkoop meer dan $ 250K bedraagt, is de korting 15%; anders … "
Deze formules worden langer en langer naarmate er meer niveaus zijn. Het moeilijkste deel van zo'n formule is onthouden hoeveel haakjes sluiten je aan het einde van de formule moet zetten.
Mini-bonustip
Overeenkomen met de haakjes
Excel doorloopt een verscheidenheid aan kleuren voor elk nieuw niveau van haakjes. Hoewel Excel de kleuren hergebruikt, wordt zwart alleen gebruikt voor het haakje openen en voor het overeenkomende haakje sluiten. Terwijl u de onderstaande formule voltooit, blijft u haakjes sluiten typen totdat u een zwart haakje typt.
Terug naar de geneste formuletip
Als u Excel 2003 gebruikt, nadert uw formule al de limiet. Destijds kon je niet meer dan 7 IF-functies nesten. Het was een lelijke dag toen de machten het commissieplan veranderden en je een manier nodig had om een achtste ALS-functie toe te voegen. Tegenwoordig kunt u 64 IF-functies nesten. Dit zou je nooit moeten doen, maar het is fijn om te weten dat het geen probleem is om 8 of 9 te nesten.
In plaats van de geneste ALS-functie te gebruiken, kunt u het ongebruikelijke gebruik van de functie VERT.ZOEKEN proberen. Wanneer het vierde argument van VERT.ZOEKEN verandert van False in True, zoekt de functie niet langer naar een exacte match.
Welnu, eerst probeert VERT.ZOEKEN een exacte overeenkomst te vinden. Maar als er geen exacte match wordt gevonden, nestelt Excel zich net minder in de rij dan waarnaar u op zoek bent.
Beschouw de onderstaande tabel. In cel C13 zoekt Excel naar een match voor $ 28.355 in de tabel. Als 28355 niet kan worden gevonden, retourneert Excel de korting die is gekoppeld aan de waarde die net minder is. In dit geval de korting van 1% voor het $ 10K-niveau.
Wanneer u de regels naar de tabel in E13: F18 converteert, moet u beginnen vanaf het kleinste niveau en doorgaan naar het hoogste niveau. Hoewel het niet in de regels werd vermeld, is de korting 0% als iemand minder dan $ 10.000 verkoopt. U moet dit toevoegen als de eerste rij in de tabel.
Voorzichtigheid
Als u de "True" -versie van VERT.ZOEKEN gebruikt, moet uw tabel oplopend worden gesorteerd. Veel mensen denken dat alle opzoektabellen gesorteerd moeten worden. Maar een tafel hoeft alleen gesorteerd te worden in het geval van een geschatte match.
Wat als uw manager een volledig op zichzelf staande formule wil en de bonustafel aan de rechterkant niet wil zien? Nadat u de formule heeft samengesteld, kunt u de tabel rechtstreeks in de formule insluiten.
Zet de formule in de bewerkingsmodus door op de cel te dubbelklikken of door de cel te selecteren en op F2 te drukken.
Selecteer met behulp van de cursor het volledige tweede argument: $ E $ 13: $ F $ 18.
Druk op de F9-toets. Excel zal de opzoektabel insluiten als een matrixconstante. In de matrixconstante geeft een puntkomma een nieuwe rij aan en een komma een nieuwe kolom.
Druk op Enter. Kopieer de formule naar de andere cellen.
U kunt de tabel nu verwijderen. De uiteindelijke formule wordt hieronder weergegeven.
Met dank aan Mike Girvin voor het leren van de bijpassende haakjes. De VLOOKUP-techniek werd voorgesteld door Danny Mac, Boriana Petrova, Andreas Thehos en @mvmcos.
Bekijk video
- Met een gelaagde commissie, bonus of kortingsprogramma moet u vaak uw IF-functies nesten
- De Excel 2003-limiet was 7 geneste IF-instructies.
- Je kunt nu 32 nestelen, maar ik denk niet dat je ooit 32 zou moeten nestelen
- Wanneer zou u ooit de geschatte overeenkomstversie van VERT.ZOEKEN gebruiken? Dit is de tijd.
- Vertaal het kortingsprogramma naar een opzoektabel
- VERT.ZOEKEN zal in de meeste gevallen het antwoord niet vinden.
- Door aan het einde True te plaatsen, wordt VERT.ZOEKEN verteld om de waarde net minder te vinden.
- Dit is de enige keer dat de tabel VERT.ZOEKEN moet worden gesorteerd.
- Wilt u de tabel VERT.ZOEKEN niet opzij zetten? Sluit het in de formule in.
- F2 om de formule te bewerken. Selecteer de opzoektabel. Druk op F9. Enter.
Videotranscriptie
Leer Excel van podcast, aflevering 2030 - Geneste ALS vervangen door VERT.ZOEKEN!
Ik ga dit hele boek podcasten, klik op de "i" in de rechterbovenhoek om naar de afspeellijst te gaan!
Hé, welkom terug bij de netcast, ik ben Bill Jelen. Oké, dit is heel gebruikelijk bij een commissieprogramma, of een kortingsprogramma of een bonusprogramma, waar we de niveaus hebben, verschillende niveaus, toch? Als je boven de $ 10.000, - bent, krijg je 1% korting, $ 50.000 5% korting. U moet voorzichtig zijn wanneer u deze geneste IF-instructie maakt, u begint deze bovenaan als u op zoek bent naar groter dan, of onderaan als u op zoek bent naar minder dan. Dus B10> 50000, 20%, anders nog een IF, B10> 250000, 25%, enzovoort. Dit is slechts een lange en gecompliceerde formule, en als uw tabel groter is, in Excel 2003, kon u niet meer dan 7 IF-statements nesten, dan zijn we hier bijna aan de limiet. Je kunt nu naar 32 gaan, ik denk niet dat je ooit naar 32 zou moeten gaan, en zelfs als je schreeuwt "Hé wacht,hoe zit het met de nieuwe functie die net uitkwam in Excel 2016, de release van februari 2016, met de IFS-functie? " Ja zeker, er zijn hier minder haakjes, en 87 tekens in plaats van 97 tekens, het is nog steeds een puinhoop, laten we dit verwijderen en het doen met VERT.ZOEKEN!
Oké, hier zijn de stappen, je neemt die regels, en je zet ze op hun kop. Het eerste wat we te zeggen hebben is, als je $ 0 aan verkoop had, krijg je 0% korting, als je $ 10.000 aan verkoop had, krijg je 1% korting, als je $ 50000 aan verkoop had, krijg je 5% korting . $ 100.000, 10% korting, $ 250000, 15% korting, en tot slot krijgt alles> $ 500.000 de 20% korting, oké. Nu, heel vaak, elke keer als ik het over VERT.ZOEKEN heb, zeg ik dat elke VERT.ZOEKEN die je ooit maakt, zal eindigen op FALSE, en mensen zullen zeggen: "Wacht eens even, wat is daar eigenlijk de WAAR-versie voor?" Het is juist in dit geval dat we op zoek zijn naar een bereik, dus we zoeken deze waarde op, een normale VERT.ZOEKEN, natuurlijk, 2, FALSE zal 550000 niet vinden, zal de # N / A retourneren!Dus in dit ene heel speciale geval gaan we dat FALSE veranderen in TRUE, en dat zal Excel vertellen: "Ga op zoek naar de 550000, als je hem niet kunt vinden, geef ons dan de waarde die gewoon minder is." Dus het geeft ons de 20%, dat is wat het doet, oké? En dit is de enige keer dat uw VERT.ZOEKEN-tabel moet worden gesorteerd, alle andere keren met, ONWAAR, het kan zijn zoals u wilt. En ja, je zou de, TRUE uit kunnen laten, maar ik heb het daar altijd neergezet om mezelf eraan te herinneren dat dit een van die rare ongelooflijk gevaarlijke VERT.ZOEKEN is, en ik wil deze formule niet ergens anders kopiëren. Oké, dus we zullen speciale formules kopiëren en plakken, oké.oké? En dit is de enige keer dat uw VERT.ZOEKEN-tabel moet worden gesorteerd, alle andere keren met, ONWAAR, het kan zijn zoals u wilt. En ja, je zou de, TRUE uit kunnen laten, maar ik heb het daar altijd neergezet om mezelf eraan te herinneren dat dit een van die rare ongelooflijk gevaarlijke VERT.ZOEKEN is, en ik wil deze formule niet ergens anders kopiëren. Oké, dus we zullen speciale formules kopiëren en plakken, oké.oké? En dit is de enige keer dat uw VERT.ZOEKEN-tabel moet worden gesorteerd, alle andere keren met, ONWAAR, het kan zijn zoals u wilt. En ja, je zou de, TRUE uit kunnen laten, maar ik heb het daar altijd neergezet om mezelf eraan te herinneren dat dit een van die rare ongelooflijk gevaarlijke VERT.ZOEKEN is, en ik wil deze formule niet ergens anders kopiëren. Oké, dus we zullen speciale formules kopiëren en plakken, oké.
Volgende klacht: uw manager wil de opzoektabel niet zien, hij wil hem 'Weg met die opzoektabel'. Oké, we kunnen dat doen door de opzoektabel in te sluiten, bekijk deze geweldige truc die ik kreeg van Mike Girvin bij ExcelIsFun. F2 om de formule in de bewerkingsmodus te zetten en selecteer vervolgens heel zorgvuldig alleen het bereik voor de opzoektabel. Druk op F9, en het neemt deze tabel en plaatst het in de array-nomenclatuur, en dan druk ik gewoon op Enter. Kopieer dat naar beneden, Plak speciale formules, en dan ben ik vrij om de opzoektabel te verwijderen, die allemaal gewoon doorgaat met werken in de rij. Dit is een enorm gedoe als je terug moet komen en dit moet bewerken, je moet er echt met veel duidelijkheid naar staren. De komma betekent dat we naar de volgende kolom gaan, de puntkomma betekent dat we naar de volgende rij gaan, oké,maar in theorie zou je daar weer in kunnen komen en die formule kunnen veranderen als een van de kettingnummers verandert.
Oké, dus het gebruik van een VERT.ZOEKEN in plaats van een geneste IF-instructie is tip 32 op weg naar 40, "40 beste Excel-tips aller tijden", je kunt dit hele boek hebben. Klik op die "i" in de rechterbovenhoek, $ 10 voor een e-boek, $ 25 voor het gedrukte boek, oké. Dus vandaag proberen we een gelaagde commissiebonus of kortingsprogramma op te lossen. Geneste IF's zijn heel gebruikelijk, pas op, 7 is alles wat je kunt hebben in Excel 2003, vandaag kun je 32 hebben, maar je zou nooit 32 moeten hebben. Dus wanneer je de geschatte MATCH-versie van VERT.ZOEKEN moet gebruiken, dit is het dan. Neem dat kortingsprogramma, draai het ondersteboven, maak er een opzoektabel van, beginnend met het kleinste nummer en naar het grootste nummer. VERT.ZOEKEN zal het antwoord natuurlijk niet vinden, maar door VERT.ZOEKEN aan het einde te veranderen in TRUE, zal het het vertellen om de waarde net minder te vinden,dit is de enige keer dat de tafel moet worden gesorteerd. Als u die tabel daar niet wilt zien, kunt u deze in de formule insluiten met behulp van de Mike Girvin-truc, F2 om de formule te bewerken, de opzoektabel selecteren, op F9 drukken en vervolgens op Enter.
Oké, ik wil je bedanken voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
Download bestand
Download het voorbeeldbestand hier: Podcast2030.xlsx
