ALS-functie in Excel: voorbeelden met tekst, EN/OF en meerdere voorwaarden

Wat doet de ALS-functie in Excel?

ALS laat een formule kiezen tussen twee uitkomsten. Je geeft een test mee: klopt die, dan toont de cel het ene, klopt ze niet, dan het andere. =ALS(B2<12;"kind";"volwassene") toont kind voor wie jonger is dan 12 en volwassene voor alle anderen. Microsoft noemt ALS op zijn eigen hulppagina een van de meest gebruikte functies van Excel (Microsoft).

Wil je de functie stap voor stap leren, met uitleg op het blad zelf, volg dan de les Kiezen met ALS. Hieronder vind je de syntaxis, een voorbeeld voor elk soort test, ALS met EN en OF, meer dan twee keuzes en de fouten die het vaakst opduiken, allemaal op hetzelfde blad.

Hoe schrijf je een ALS-formule?

Een ALS-formule heeft drie argumenten, gescheiden door een puntkomma: de test, wat de cel toont als de test WAAR is, en wat ze toont als de test ONWAAR is. Alleen het laatste mag je weglaten (Microsoft).

=ALS(logische_test;waarde_als_waar;[waarde_als_onwaar])

De test is bijna altijd een vergelijking: B2<12 vraagt of B2 kleiner is dan 12, C2="Beurt" of er Beurt in C2 staat. Als uitkomst mag je tekst, een getal, een verwijzing of een berekening geven. Tekst staat tussen aanhalingstekens (Microsoft), getallen niet. Typ je de puntkomma maar niets erachter, zoals in =ALS(B2<12;"kind";), dan krijg je een 0 in plaats van ONWAAR.

In een Engelstalige Excel heet ALS gewoon IF, en WAAR en ONWAAR heten TRUE en FALSE. Het teken tussen de argumenten hangt niet af van Windows of Mac, maar van de landinstellingen van je besturingssysteem en van Excel (Microsoft). Daarom zie je op Engelstalige sites =IF(B2<12,"kind","volwassene"), met komma's.

Hoe werkt ALS op een echt blad?

Je typt de formule één keer in de eerste rij en trekt ze door. ALS beslist dan voor elke rij opnieuw, met de waarden van die rij. Neem het ledenblad van Zwembad De Plons in Turnhout, de derde klant in onze Excel-cursus. Kinderen onder de 12 krijgen de zomerpas aan halve prijs, en Dirk aan het loket wil per lid zien of het een kind is.

In F2 staat =ALS(B2<12;"kind";"volwassene"). Jens is 9, de test is WAAR, dus F2 toont kind. Trek je de formule door tot F11, dan schuift B2 mee naar B3, B4 enzovoort, en kijkt ALS telkens naar de leeftijd op de eigen rij. Alleen Jens en Rik zijn jonger dan 12. Rita en Jef zijn senioren, maar ook zij krijgen volwassene: deze formule kent maar twee uitkomsten.

Probeer het nu zelf, op het echte blad van De Plons. Deze keer test je geen getal, maar tekst, en de uitkomst is een bedrag.

Je formule rekende in je eigen browser, en we controleerden ze daarna op een tweede, verborgen versie van het blad, met andere abonnementen. Typ je de bedragen met de hand in, dan klopt het hier en valt het daar door de mand.

Welke vergelijkingen kan je in een ALS-test gebruiken?

Zes: gelijk aan, niet gelijk aan, kleiner dan, groter dan, en die laatste twee met "of gelijk aan" erbij. Elke vergelijking geeft WAAR of ONWAAR (Microsoft), en dat is precies wat ALS als eerste argument nodig heeft. Zo ziet elk teken eruit op het blad van De Plons:

Let op de grens. D2>15 en D2>=15 verschillen alleen voor wie precies 15 keer kwam, en dat is Rita. Vraagt iemand "vanaf 15 bezoeken", dan hoort ze erbij en schrijf je >=. Twijfel je, zeg de regel dan hardop voor die ene rij. Vergelijken op zich oefen je in de les Vergelijken.

Hoe test je op tekst?

Tekst staat in de test tussen aanhalingstekens, net als in de uitkomst. Hoofdletters maken daarbij niet uit: =ALS(C2="beurt";5;0) geeft bij Jens ook 5, al staat er Beurt met een hoofdletter. Moeten hoofdletters wel meetellen, zet dan GELIJK in de test, een functie die Microsoft zelf hoofdlettergevoelig noemt (Microsoft). Een spatie te veel telt wel, daarover meer bij de valkuilen.

Kan de uitkomst van ALS een berekening zijn?

Ja. Een uitkomst mag een getal, een verwijzing of een hele berekening zijn. Stel dat kinderen maar de helft betalen van het bedrag in kolom E:

=ALS(B2<12;E2/2;E2)

Voor Jens geeft dat 7,5 euro en voor Rik 12,5 euro. Alle anderen houden gewoon het bedrag uit kolom E. Zet er geen aanhalingstekens rond: "E2/2" is tekst, en dan toont de cel letterlijk E2/2 in plaats van een bedrag.

Hoe laat je een cel leeg met ALS?

Met twee aanhalingstekens zonder iets ertussen. Volgens Microsoft betekent "" in feite "niets" (Microsoft). Deze formule toont alleen iets bij wie 10 keer of vaker kwam:

=ALS(D2>=10;"vaste klant";"")
=ALS(D2="";"onbekend";D2)

Noor, Rita, Jef en Hanne krijgen vaste klant, de anderen een cel die leeg lijkt. Andersom test je met "" of een cel leeg is. De tweede formule toont onbekend bij Lotte, van wie niemand de bezoeken invulde, en anders gewoon het aantal. Sara krijgt 0: haar cel is niet leeg, er staat een 0 in.

Hoe gebruik je ALS met EN en OF?

Je zet EN of OF in de test van ALS. EN geeft WAAR als alle voorwaarden kloppen, OF als er minstens één klopt, en ALS kiest dan de uitkomst (Microsoft). Op het tweede blad van De Plons houdt Dirk de diploma's van de zwemlessen bij, als WAAR of ONWAAR. In het diepe bad mag je pas met diploma A én B. Stel dat voor de clubwedstrijd één diploma volstaat:

=ALS(EN(C2;D2);"diep";"ondiep")
=ALS(OF(C2;D2);"mag mee";"nog niet")

Met EN mogen vier kinderen in het diepe bad: Ilias, Bas, Tuur en Senne, die allebei de diploma's hebben. Met OF mogen er zes mee, want Ella en Amira hebben diploma A, en dat volstaat. Omdat C en D al WAAR of ONWAAR bevatten, hoef je ze niet meer te vergelijken. Op het ledenblad schrijf je de vergelijkingen gewoon als argumenten: EN(C2="Jaar";D2>=20) klopt alleen voor Noor.

Twee dingen lopen vaak mis. De voorwaarden gaan als argumenten in de functie: ALS(C2 EN D2;…) bestaat niet in Excel. En kies de juiste: =EN(A2="Ella";A2="Bas") is altijd ONWAAR, want één cel kan niet twee namen tegelijk bevatten. Bedoel je "Ella en Bas", dan bedoel je OF. NIET, dat een test omkeert, oefen je samen met deze twee in de les EN, OF en NIET.

Hoe maak je een ALS met meer dan twee uitkomsten?

Je zet een tweede ALS op de plaats van het derde argument. Klopt de eerste test niet, dan rekent Excel de tweede ALS uit, en zo krijg je drie uitkomsten. De zomerpas krijgt drie prijzen: voor kinderen, voor senioren vanaf 65 en voor alle anderen. Beide formules hieronder geven elk lid zijn categorie:

=ALS(B2<12;"kind";ALS(B2>=65;"senior";"volwassene"))
=ALS.VOORWAARDEN(B2<12;"kind";B2>=65;"senior";WAAR;"volwassene")

Jens en Rik worden kind, Rita en Jef senior, en de anderen volwassene, op één na: Hanne wordt senior, terwijl ze 12 is. Dat komt door de leeftijd die als tekst in de cel staat, en daar gaat de volgende sectie over.

ALS.VOORWAARDEN zet de tests na elkaar, telkens een test en een uitkomst, en Microsoft noemt het veel makkelijker te lezen (Microsoft). Klopt geen enkele test, dan geeft het #N/B. Daarom staat er als laatste test WAAR, die altijd klopt: dat is je standaarduitkomst. ALS.VOORWAARDEN werkt vanaf Office 2019 en met Microsoft 365. Opent iemand je bestand in een oudere Excel, kies dan de geneste ALS.

Bij een geneste ALS en bij ALS.VOORWAARDEN wint de eerste test die klopt. Zet de strengste test daarom eerst.

Hoe je beide vormen opbouwt, oefen je in de les Meer dan twee keuzes.

Wat is het verschil tussen ALS en ALS.FOUT?

ALS test een voorwaarde die jij kiest. ALS.FOUT kijkt alleen of een formule een foutwaarde geeft, en toont dan iets anders (Microsoft). Dirk wil weten wat elk lid per bezoek betaalt: het bedrag gedeeld door het aantal bezoeken. Bij Sara, die nog niet kwam, en bij Lotte, van wie niets is ingevuld, geeft die deling #DEEL/0!.

=ALS(D2=0;"-";E2/D2)
=ALS.FOUT(E2/D2;"-")

Op dit blad geven ze hetzelfde: 5 euro per bezoek voor Jens, 16 euro voor Rita, en een streepje bij Lotte en Sara. Het verschil zit in wat ze opvangen. ALS vangt precies het geval dat je test. ALS.FOUT vangt elke foutwaarde, ook de #NAAM? van een tikfout in je eigen formule, die je dan niet meer ziet. Zet het er dus pas omheen als de formule werkt, zoals je oefent in de les Foutwaarden opvangen.

Waarom werkt mijn ALS-formule niet?

Meestal door een van deze zes dingen. De eerste zie je meteen aan een foutwaarde, de andere geven een uitkomst die er geloofwaardig uitziet, en die zijn gevaarlijker.

Tekst zonder aanhalingstekens geeft #NAAM?

=ALS(B2<12;kind;volwassene) geeft in elke rij #NAAM?. Zonder aanhalingstekens leest Excel kind als een naam, zoekt die en vindt ze niet (Microsoft). Zet elke tekst tussen aanhalingstekens. Dezelfde fout krijg je bij een tikfout in de functienaam, of als je IF typt in een Nederlandstalige Excel.

Een getal dat als tekst in de cel staat

Hannes leeftijd werd als tekst getypt, en zo'n cel herken je vaak aan de linkse uitlijning en een groen driehoekje. =ALS(B10>=65;"senior";"gewoon") geeft voor haar senior: Excel vergelijkt de tekst "12" niet als het getal 12. Met B10<12 kreeg ze volwassene, maar dat klopte bij toeval. Zulke getallen kunnen onverwachte resultaten geven, en je zet ze om met Converteren naar getal of de functie WAARDE (Microsoft).

Een spatie te veel

Staat er in C2 "Beurt " met een spatie achteraan, dan geeft C2="Beurt" ONWAAR: voor Excel is dat een andere tekst. Zo'n spatie zie je niet, en ze komt makkelijk mee met gegevens die je ergens anders kopieert. SPATIES.WISSEN verwijdert alle spaties behalve de enkele tussen woorden (Microsoft), dus met =ALS(SPATIES.WISSEN(C2)="Beurt";5;0) klopt de test weer.

De volgorde van geneste ALS-functies

Excel loopt de tests af en stopt bij de eerste die klopt. In =ALS(B2>=12;"volwassene";ALS(B2>=65;"senior";"kind")) worden Rita en Jef volwassene: 67 en 72 zijn ook 12 of meer, dus de tweede test komt nooit aan de beurt. In Microsofts eigen voorbeeld krijgt een omzet van 12.500 zo de laagste commissie (Microsoft). Zet de strengste test eerst.

Komma's in een formule van een Engelstalige site

Kopieer je =IF(B2<12,"child","adult") van een Engelstalige site, dan pas je in een Nederlandstalige Excel twee dingen aan: IF wordt ALS, en de komma's worden puntkomma's. Welk scheidingsteken je Excel verwacht, hangt af van de landinstellingen (Microsoft), niet van de taal. Met een Engelse Excel en Belgische of Nederlandse instellingen schrijf je dus =IF(B2<12;"child";"adult"): Engelse namen, maar puntkomma's.

Te veel ALS-functies in elkaar

Excel aanvaardt tot 64 geneste ALS-functies, maar Microsoft noemt dat "beslist niet aan te raden" (Microsoft): zo'n formule is moeilijk juist te bouwen en te onderhouden. Kies bij veel uitkomsten ALS.VOORWAARDEN, of zet de grenzen in een tabel en zoek ze op met VERT.ZOEKEN, zoals Microsoft zelf voorstelt.

Welke functies lijken op ALS?

ALS beslist per cel tussen twee uitkomsten. Voor wat daarbuiten valt, heeft Excel functies uit dezelfde familie, elk met een Engelse naam die je op Engelstalige sites tegenkomt:

SCHAKELEN past als je één cel met een vaste lijst vergelijkt. =SCHAKELEN(C2;"Beurt";5;"Maand";2;0) geeft 5 bij een beurt, 2 bij een maandabonnement en 0 voor de rest. Het laatste argument is de standaarduitkomst; zonder dat argument geeft SCHAKELEN #N/B als er niets past (Microsoft). Net als ALS.VOORWAARDEN werkt het met Office 2019 of Microsoft 365.

Wil je geen uitkomst per lid maar één totaal, dan heb je ALS niet nodig. =AANTAL.ALS(B2:B11;"<12") telt de kinderen: 2. =SOM.ALS(C2:C11;"Jaar";E2:E11) telt op wat de jaarabonnees betaalden: 960 euro. Meer voorbeelden vind je in AANTAL.ALS uitgelegd en SOM.ALS met voorbeelden.

De formules die je het vaakst nodig hebt, staan in de 12 belangrijkste Excel-formules, en elke functie met haar Engelse naam op het Excel-spiekbriefje. ALS leer je het best door het zelf te typen: in module 3 van onze Excel-cursus voor beginners doe je dat bij De Plons, met oefeningen die we op hun uitkomst controleren.