ALS-functie – geneste formules en het vermijden van valkuilen

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 voorbeeld is relatief veilig omdat de correlatie tussen toetsresultaten en beoordelingen (door middel van letters) niet snel zal veranderen. Daarom is hier niet veel onderhoud nodig. Maar wat nu als u de beoordelingen wilt opsplitsen in A+, A en A- (enzovoort)? Nu moet de ALS-instructie met vier voorwaarden worden herschreven, zodat deze 12 voorwaarden krijgt. De formule ziet er nu als volgt 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 dit veel lijkt op het eerdere beoordelingsvoorbeeld, is deze formule een goed voorbeeld van hoe moeilijk het kan zijn om grote ALS-instructies te onderhouden. Wat moet u bijvoorbeeld doen als uw organisatie zou besluiten om nieuwe beloningsniveaus toe te voegen en misschien zelfs de bestaande euro- of percentagewaarden te wijzigen? U zou aardig wat tijd kwijt zijn.

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 niet klopt? Vergelijk de volgorde van de omzetvergelijkingen met het vorige voorbeeld. Welke kant gaat deze op? Deze gaat inderdaad van onder naar boven (€ 5000 naar € 15.000) in plaats van andersom. Maar waarom is dat een probleem? Het is een probleem omdat de formule niet voorbij de eerste evaluatie voor elke waarde van meer dan € 5000 kan komen. Als u bijvoorbeeld een omzet van € 12.500 hebt, wordt door de ALS-instructie 10% geretourneerd omdat dat groter is dan € 5000. De formule gaat niet meer verder. Dit kan zeer problematisch zijn omdat dit type fouten in veel gevallen pas worden opgemerkt als ze een negatieve invloed hebben gehad. Wat kunt u doen nu u weet dat complexe geneste ALS-instructies grote problemen kunnen opleveren? In de meeste gevallen kunt u de functie VERT.ZOEKEN gebruiken in plaats van een complexe formule met de ALS-functie. Als u VERT.ZOEKEN gebruikt, moet 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 uitgebreider beschreven, maar dit is een stuk eenvoudiger dan een complexe geneste ALS-instructie met 12 niveaus. 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 Office 365-abonnement hebt. Controleer in dat geval of u de nieuwste versie van Office hebt.

Office 365 of de laatste versie van Excel proberen

Meer hulp nodig?

U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community, ondersteuning vragen in de Answer-community of een nieuwe functie of verbetering voorstellen in Excel User Voice.

Verwante onderwerpen

Video: Geavanceerde ALS-functies
ALS.VOORWAARDEN, functie (Office 365, Excel 2016 en hoger)
Met de functie AANTAL.ALS worden waarden geteld op basis van één criterium
Met de functie AANTALLEN.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
EN, functie
OF, functie
VERT.ZOEKEN, functie
Overzicht van formules in Excel
Niet-werkende formules voorkomen
Foutcontrole gebruiken om fouten in formules te detecteren
Logische functies
Excel-functies (alfabetisch)
Excel-functies (per categorie)

Uw vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×