ALS-functie – geneste formules en het vermijden van valkuilen

Met de ALS-functie kunt u een logische vergelijking maken tussen een waarde en wat u verwacht door te testen op een voorwaarde en een resultaat te retourneren indien Waar of Onwaar.

  • =ALS(iets waar is, doe dan iets, doe anders iets anders)

Een ALS-instructie kan dus twee resultaten hebben. Het eerste resultaat is als de vergelijking Waar is, het tweede is als de vergelijking Onwaar is.

ALS-instructies zijn zeer krachtig en vormen de basis van veel werkbladmodellen, maar ze zijn ook vaak de oorzaak van problemen met werkbladen. Een ALS-instructie kan het beste worden toegepast op minimale voorwaarden, zoals Man/Vrouw, Ja/Nee/Misschien, maar mogelijk moet u soms complexere scenario's evalueren waarin meer dan drie ALS-functies moeten worden genest*.

* 'Nesten' verwijst naar het samenvoegen van meerdere functies in één formule.

Gebruik de functie ALS, een van de logische functies, om één waarde te retourneren als een voorwaarde waar is en een andere waarde als de voorwaarde onwaar is.

Syntaxis

ALS(logische_test;waarde_als_waar;[waarde_als_onwaar])

Bijvoorbeeld:

  • =ALS(A2>B2;"Budget overschreden";"OK")

  • =ALS(A2=B2;B4-A4,"")

Naam argument

Beschrijving

logische-test   

(vereist)

De voorwaarde die u wilt testen.

waarde-als-waar   

(vereist)

De waarde die u als resultaat wilt hebben als het resultaat van logische_test WAAR is.

waarde-als-onwaar   

(optioneel)

De waarde die u als resultaat wilt hebben als het resultaat van logische_test ONWAAR is.

Opmerkingen

Hoewel u in Excel wel 64 verschillende ALS-functies kunt nesten, is dit beslist niet aan te raden. Waarom?

  • Het kost veel denkwerk om meerdere ALS-instructies correct uit te werken en ervoor te zorgen dat de logica ervan via elke voorwaarde helemaal tot het eind correct kan worden berekend. Als u de formule niet 100% nauwkeurig nest, kan deze 75% van de tijd werken, maar 25% van de tijd onverwachte resultaten geven. Helaas is de kans klein dat u die 25% ontdekt.

  • Meerdere ALS-instructies kunnen zeer lastig bij te houden zijn, vooral wanneer u later probeert te achterhalen wat u, of erger nog, iemand anders probeerde te doen.

Als u bezig bent met een ALS-instructie die steeds maar groter wordt en waar geen einde aan lijkt te komen, kunt u beter stoppen en een andere strategie bedenken.

We bekijken hoe een complexe geneste ALS-instructie moet worden gemaakt met meerdere ALS-waarden en wanneer er beter een andere Excel-functie kan worden gebruikt.

Voorbeelden

Hierna volgt een voorbeeld van een redelijk standaard geneste ALS-instructie waarmee de toetsresultaten van studenten worden geconverteerd naar de equivalente letterbeoordeling.

Complexe geneste ALS-instructie: formule in E2 is =ALS(B2>97,"A+",ALS(B2>93,"A",ALS(B2>89,"A-",ALS(B2>87,"B+",ALS(B2>83,"B",ALS(B2>79,"B-",ALS(B2>77,"C+",ALS(B2>73,"C",ALS(B2>69,"C-",ALS(B2>57,"D+",ALS(B2>53,"D",ALS(B2>49,"D-","F"))))))))))))
  • =ALS(D2>89,"A",ALS(D2>79,"B",ALS(D2>69,"C",ALS(D2>59,"D","F"))))

    Deze complexe geneste ALS-instructie volgt een eenvoudige logica:

  1. Als het toetsresultaat (in cel D2) groter is dan 89, krijgt de student een A

  2. Als het toetsresultaat groter is dan 79, krijgt de student een B

  3. Als het toetsresultaat groter is dan 69, krijgt de student een C

  4. Als het toetsresultaat groter is dan 59, krijgt de student een D

  5. Anders krijgt de student een F

Dit specifieke voorbeeld is relatief veilig omdat het waarschijnlijk niet waarschijnlijk is dat de correlatie tussen toetsresultaten en lettercijfers verandert, zodat er niet veel onderhoud nodig is. Maar hier is een gedachte: wat als u de cijfers wilt segmenteren tussen A+, A en A- (en ga zo maar door)? Nu moet uw vier voorwaarde ALS-instructie worden herschreven om 12 voorwaarden te hebben! De formule ziet er nu zo uit:

  • =ALS(B2>97,"A+",ALS(B2>93,"A",ALS(B2>89,"A-",ALS(B2>87,"B+",ALS(B2>83,"B",ALS(B2>79,"B-", ALS(B2>77,"C+",ALS(B2>73,"C",ALS(B2>69,"C-",ALS(B2>57,"D+",ALS(B2>53,"D",ALS(B2>49,"D-","F"))))))))))))

Deze formule is nog steeds functioneel nauwkeurig en werkt zoals verwacht, maar het duurt erg lang om deze te schrijven en nog langer om te testen of alles klopt. Nog een probleem is dat u de resultaten en letterbeoordelingen handmatig moet invoeren. Hoe groot is de kans dat u per ongeluk een typefout maakt? Stel nu dat u dit 64 keer moet doen met nog complexere voorwaarden. Het is wel mogelijk, maar wilt u hier zo veel tijd in steken terwijl er waarschijnlijk toch fouten in komen die moeilijk terug te vinden zijn?

Tip: Voor elke functie in Excel is een haakje openen en sluiten () vereist. U wordt in Excel geholpen met het bepalen wat waar moet door de verschillende delen van de formule kleuren te geven terwijl u deze bewerkt. Als u bijvoorbeeld de bovenstaande formule bewerkt en de cursor voorbij elk haakje sluiten ')' beweegt, krijgt het bijbehorende haakje openen dezelfde kleur. Dit is vooral handig in complexe, geneste formules wanneer u erachter probeert te komen of er voldoende overeenkomende haakjes zijn.

Extra voorbeelden

Hierna ziet u een zeer gangbaar voorbeeld van het berekenen van de verkoopcommissie op basis van de niveaus van de behaalde omzet.

Formule in cel D9 is ALS(C9>15000,20%,ALS(C9>12500,17.5%,ALS(C9>10000,15%,ALS(C9>7500,12.5%,ALS(C9>5000,10%,0)))))
  • =ALS(C9>15000,20%,ALS(C9>12500,17.5%,ALS(C9>10000,15%,ALS(C9>7500,12.5%,ALS(C9>5000,10%,0)))))

Met deze formule wordt het volgende aangegeven: ALS(C9 groter is dan 15.000, wordt 20% geretourneerd, ALS(C9 groter is dan 12.500 wordt 17,5% geretourneerd enzovoort...

Hoewel deze formule opvallend veel lijkt op het eerdere cijfervoorbeeld, is deze formule een goed voorbeeld van hoe moeilijk het kan zijn om grote ALS-instructies te behouden. Wat zou u moeten doen als uw organisatie zou hebben besloten om nieuwe compensatieniveaus toe te voegen en mogelijk zelfs de bestaande dollar- of percentagewaarden te wijzigen? Dan hebt u veel werk in uw handen.

Tip: U kunt regeleinden invoegen in de formulebalk om lange formules beter leesbaar te maken. Druk op Alt+Enter vóór de tekst die u op een nieuwe regel wilt plaatsen.

Hier volgt een voorbeeld van het commissiescenario waarbij de logica niet in de goede volgorde staat:

Formule in D9 heeft een onjuiste volgorde: =ALS(C9>5000,10%,ALS(C9>7500,12.5%,ALS(C9>10000,15%,ALS(C9>12500,17.5%,ALS(C9>15000,20%,0)))))

Kunt u zien wat er mis is? Vergelijk de volgorde van de vergelijkingen opbrengst met het vorige voorbeeld. Welke kant gaat deze op? Dat klopt, het gaat van beneden naar boven ($ 5.000 tot $ 15.000), niet andersom. Maar waarom zou dat zo belangrijk zijn? Het is een groot probleem omdat de formule de eerste evaluatie niet kan door laten gaan voor een waarde van meer dan $ 5.000. Stel dat u $ 12.500 aan omzet hebt: de IF-instructie geeft 10% als rendement omdat deze groter is dan $ 5.000 en dat deze daar stopt. Dit kan zeer problematisch zijn, omdat in veel situaties dit soort fouten onopgemerkt blijven totdat ze een negatief effect hebben gehad. Dus wetende dat er enkele ernstige valkuilen zijn met complexe geneste ALS-instructies, wat kunt u doen? In de meeste gevallen kunt u de functie VERT.ZOEKEN gebruiken in plaats van een complexe formule te maken met de functie ALS. Met VERT.ZOEKENmoet u eerst een verwijzingstabel maken:

Formule in cel D2 is =VERT.ZOEKEN(C2,C5:D17,2,WAAR)
  • =VERT.ZOEKEN(C2,C5:D17,2,WAAR)

Met deze formule wordt aangegeven dat de waarde in C2 moet worden opgezocht in het bereik C5:C17. Als de waarde wordt gevonden, wordt de overeenkomende waarde uit dezelfde rij geretourneerd in kolom D.

Formule in cel C9 is =VERT.ZOEKEN(B9,B2:C6,2,WAAR)
  • =VERT.ZOEKEN(B9,B2:C6,2,WAAR)

Met deze formule wordt naar de waarde in cel B9 gezocht in het bereik B2:B22. Als de waarde wordt gevonden, wordt de overeenkomende waarde uit dezelfde rij geretourneerd in kolom C.

Opmerking: In beide VERT.ZOEKEN-formules wordt het argument WAAR gebruikt aan het einde van de formules, wat betekent dat er moeten worden gezocht naar een benadering van de zoekwaarde. Dit houdt in dat niet alleen de exacte waarden in de opzoektabel worden geretourneerd, maar ook de waarden hier tussenin. In dit geval moeten de opzoektabellen worden gesorteerd in oplopende volgorde, van kleinste naar grootste.

VERT.ZOEKEN wordt hier veel gedetailleerderbeschreven, maar dit is zeker een stuk eenvoudiger dan een complexe geneste ALS-instructie op 12 niveau! Er zijn ook andere, minder voor de hand liggende voordelen:

  • Verwijzingstabellen voor VERT.ZOEKEN zijn direct beschikbaar en goed leesbaar.

  • Tabelwaarden kunnen eenvoudig worden bijgewerkt en u hoeft de formule niet te wijzigen als de voorwaarden worden gewijzigd.

  • Als u niet wilt dat mensen de verwijzingstabel kunnen zien of deze kunnen wijzigen, plaatst u deze op een ander werkblad.

Wist u dat...

Er is nu een functie ALS.VOORWAARDEN waarmee meerdere geneste ALS-instructies kunnen worden vervangen door één functie. Dus in plaats van het eerste beoordelingsvoorbeeld, met vier geneste ALS-functies:

  • =ALS(D2>89,"A",ALS(D2>79,"B",ALS(D2>69,"C",ALS(D2>59,"D","F"))))

Kan het veel eenvoudiger worden gemaakt met één functie ALS.VOORWAARDEN:

  • =ALS.VOORWAARDEN(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",WAAR,"F")

De functie ALS.VOORWAARDEN is ideaal omdat u zich niet hoeft bezig te houden met alle ALS-instructies en haakjes.

Opmerking: Deze functie is alleen beschikbaar als u een Microsoft 365-abonnement hebt. Als u een Microsoft 365-abonnee bent, controleer dan of u de nieuwste versie van Office hebt.

Koppeling om Microsoft 365 te proberen of te kopen

Meer hulp nodig?

U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community of ondersteuning vragen in de Answers-community.

Verwante onderwerpen

Video: Geavanceerde ALS-functies
IFS, functie (Microsoft 365, Excel 2016 en hoger)
Met de functie AANTAL.ALS worden waarden geteld op basis van één criterium
Met de functie AANTAL.ALS worden waarden geteld op basis van meerdere criteria
Met de functie SOM.ALS worden waarden opgeteld op basis van één criterium
Met de functie SOMMEN.ALS worden waarden opgeteld op basis van meerdere criteria
AND, functie
OF, functie
VERT.ZOEKEN, functie
Overzicht van formules in Excel
Gebroken formules voorkomen
Fouten in formules detecteren
Logische functies
Excel functies (alfabetisch)
Excel functies (per categorie)

Meer hulp nodig?

Uw vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Microsoft insiders

Was deze informatie nuttig?

Hoe tevreden bent u met de vertaalkwaliteit?
Wat heeft uw ervaring beïnvloed?

Bedankt voor uw feedback.

×