ALS.FOUT en ALS.NB in Excel: fouten opvangen zonder echte fouten te verstoppen

Wat doen ALS.FOUT en ALS.NB in Excel?

ALS.FOUT toont iets anders als een formule een foutwaarde geeft, en anders gewoon de uitkomst. =ALS.FOUT(E11/D11;"-") deelt E11 door D11 en toont een streepje als dat niet lukt. ALS.NB doet hetzelfde, maar alleen bij de fout #N/B, die je krijgt als een zoekfunctie niets vindt. In een Engelstalige Excel heten ze IFERROR en IFNA.

Beide zijn handig, en allebei kun je er iets mee verstoppen wat je juist had moeten zien. Daarom rekenen we hieronder op twee bladen uit onze Excel-cursus: dat van Zwembad De Plons in Turnhout en dat van Fietskoeriers De Vliegende Velo in Antwerpen. Wil je het stap voor stap leren, volg dan de les over foutwaarden.

Hoe schrijf je ALS.FOUT en ALS.NB?

Beide functies hebben twee verplichte argumenten: de formule die je wilt controleren, en wat er moet verschijnen als die een fout geeft (Microsoft). Bij ALS.NB is dat tweede argument wat je toont bij #N/B (Microsoft).

=ALS.FOUT(waarde;waarde_indien_fout)
=ALS.NB(waarde;waarde_indien_niet_bekend)

In een Engelstalige Excel schrijf je =IFERROR(E11/D11,"-") en =IFNA(…,"-"). Rekent de formule gewoon, dan doet de functie niets en zie je de uitkomst zelf. Leeg je het tweede argument, dan geeft ALS.FOUT een lege tekst terug (Microsoft).

Welke fouten vangt ALS.FOUT op?

Alle foutwaarden die Excel kent. In een Nederlandstalige Excel zien ze er zo uit, met de Engelse naam ernaast (Microsoft). Lees ze eerst, voor je ze wegwerkt: elke fout zegt wat er mis is.

Hoe vang je #DEEL/0! op?

Beheerder Dirk van De Plons wil weten wat elk lid per bezoek betaalt: het bedrag in kolom E gedeeld door de bezoeken in kolom D. Sara kwam nog nooit en bij Lotte staat niets ingevuld, dus bij hen geeft de deling #DEEL/0!. Een lege cel telt in een berekening als 0.

Dat is een van de nette toepassingen van ALS.FOUT. De fout is geen vergissing: er valt voor Sara niets uit te rekenen, en Dirk wil een rustig blad. Probeer het zelf in de rij van Sara.

Je formule rekende in je eigen browser, op een echte rekenmotor, en daarna nog eens op een verborgen versie van het blad, met andere bedragen.

Wil je preciezer zijn, test dan de noemer met de functie ALS: =ALS(D11=0;"-";E11/D11). Dat vangt alleen de deling door nul op. Microsoft waarschuwt dat ALS.FOUT alle fouten onderdrukt, niet alleen #DEEL/0! (Microsoft).

Hoe vang je #N/B op bij VERT.ZOEKEN?

Met ALS.NB, rond de zoekfunctie. Dispatcher Kyle van De Vliegende Velo zoekt bij elke rit de gemeente van de klant op. De klantenlijst staat in L2:M6: de code in kolom L, de gemeente in kolom M. Rit R10 is voor een nieuwe klant, K06, die nog niet in de lijst staat. =VERT.ZOEKEN(B11;L2:M6;2;ONWAAR) geeft daar #N/B.

Dat is geen fout van Kyle, maar een echt antwoord: deze klant is er nog niet. Laten we het netjes zeggen. Is VERT.ZOEKEN nieuw voor je, dan legt de les het rustig uit, en de hints hieronder helpen je hier op weg.

X.ZOEKEN heeft dat ingebouwd, als vierde argument: =X.ZOEKEN(B11;L2:L6;M2:M6;"Nieuwe klant") doet hetzelfde zonder ALS.NB. Dat is korter en het blijft even veilig. Hoe beide zoekfuncties verschillen, lees je in VERT.ZOEKEN of X.ZOEKEN.

Wanneer verstopt ALS.FOUT een echte fout?

Telkens wanneer je formule zelf kapot is. ALS.FOUT weet niet waarom er een fout is, alleen dát er een is. Stel dat Kyle per ongeluk kolom 3 vraagt uit een lijst van twee kolommen. Bij ALS.FOUT merkt hij niets, bij ALS.NB wel:

Met ALS.FOUT staat er bij elke rit "Nieuwe klant", ook bij je trouwste klanten, en niemand krijgt een waarschuwing. Hetzelfde gebeurt bij een tikfout: =ALS.FOUT(SOMM(E2:E11);0) toont 0 euro in plaats van #NAAM?, en het blad ziet er kerngezond uit.

Schrijf eerst je formule zonder ALS.FOUT en kijk of ze klopt. Zet het vangnet er pas omheen als je weet welke fout je verwacht.

Wat zet je in de plaats van de fout?

Dat is geen detail, want wat je erin zet, rekent verder. Dirk wil het gemiddelde per bezoek uit kolom G. Staat er bij Lotte en Sara een streepje, dan slaat GEMIDDELDE die tekst over en krijgt hij 7,43 euro. Zet hij er een 0, dan krijgt hij 5,95 euro: twee leden die niets zeggen over de prijs, trekken het gemiddelde naar beneden.

Kies dus een 0 alleen als nul echt het antwoord is. Een leeg ogende cel met "" bevat tekst, en daar kun je niet mee vermenigvuldigen. Meer over wat een gemiddelde wel en niet meeneemt, lees je in het artikel over GEMIDDELDE.

Welke functies horen hierbij?

Rond een foutwaarde staat een klein gezelschap, en elk lid heeft zijn eigen taak. Je ziet ze hieronder met het voorbeeld dat er het best bij past.

Een gemiddelde zonder rijen geeft ook #DEEL/0!, zoals je ziet in GEMIDDELDE.ALS en GEMIDDELDEN.ALS. Je vindt alle functies met hun Engelse naam op het Excel-spiekbriefje, en een bredere start in de 12 belangrijkste Excel-formules. Wil je foutwaarden leren lezen op een echt blad, volg dan module 3 van de Excel-cursus. Die is gratis.