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.
Technische gegevens
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 logical_test WAAR is. |
|
waarde-als-onwaar (optioneel) |
De waarde die u als resultaat wilt hebben als het resultaat van logical_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.
Laten we eens kijken hoe een complexe geneste ALS-instructie op de juiste manier kan worden gemaakt met meerdere ALS-waarden en wanneer we kunnen inzien dat het tijd is om een andere functie in uw Excel-arsenaal te gebruiken.
Voorbeelden
Hierna volgt een voorbeeld van een redelijk standaard geneste ALS-instructie waarmee de toetsresultaten van studenten worden geconverteerd naar de equivalente letterbeoordeling.
- =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:
- Als het toetsresultaat (in cel D2) groter is dan 89, krijgt de student een A
- Als het toetsresultaat groter is dan 79, krijgt de student een B
- Als het toetsresultaat groter is dan 69, krijgt de student een C
- Als het toetsresultaat groter is dan 59, krijgt de student een D
- Anders krijgt de student een F
Dit voorbeeld is relatief veilig omdat de correlatie tussen toetsscores en beoordelingen (door middel van letters) niet snel zal veranderen. Daarom is er niet veel onderhoud nodig. Maar hier is een gedachte: wat als u de cijfers moet segmenteren tussen 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"))))))))))))
Het is nog steeds functioneel nauwkeurig en werkt zoals verwacht, maar het duurt erg lang om het 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.
- =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 opmerkelijk 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 doen als uw organisatie zou besluiten om nieuwe beloningsniveaus toe te voegen en mogelijk zelfs de bestaande euro- of percentagewaarden te wijzigen? Je zou veel werk te doen hebben!
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:
Is het duidelijk wat er mis is? Vergelijk de volgorde van de inkomstenvergelijkingen 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'n groot probleem zijn? Dit is een groot probleem omdat de formule de eerste beoordeling voor een waarde boven $ 5.000 niet kan doorstaan. Stel dat u $ 12.500 aan opbrengst hebt, dan zal het ALS-overzicht 10% retourneren omdat het groter is dan $ 5.000, en daar blijft het bij. Dit kan ongelooflijk problematisch zijn, omdat dit soort fouten in veel gevallen onopgemerkt blijven totdat ze een negatief effect hebben gehad. Dus wetende dat er een aantal 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.ZOEKEN moet u eerst een verwijzingstabel maken:
- =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.
- =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 gedetailleerder besproken, maar dit is beslist 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 Microsoft 365-abonnement hebt. Als u een Microsoft 365subscriber bent, controleer dan of u de nieuwste versie van Office hebt.
Meer hulp nodig?
U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community of ondersteuning vragen in Community's.