Exceltip | Welke ALS-variant gebruik je?

De ALS-functie (Engels: IF)

Eerst nog even een uitleg van de ALS-functie aan de hand van enkele voorbeelden.

Voorbeeld 1

Je wilt dat de tekst Voldoende bij de score komt als de evaluatie een A is. In de andere gevallen moet er Onvoldoende komen.

In de afbeelding hieronder zie je hoe dat eruitziet.

• Bij ① is in C4 de ALS-formule geplaatst. Wat er staat zie je in E4.
• Bij ② zie je die formule in het dialoogvenster Functieargumenten.
• Uitleg:
  De bewering (Excel noemt dat Logische_test) B4=”A” is in C4 inderdaad WAAR, dus wordt het tweede argument gebruikt als resultaat van de berekening.
  Bij B5 is die bewering ONWAAR, dus krijg je het derde argument als resultaat.
  Omdat het eindresultaat een tekst is, moet je die tussen dubbele aanhalingstekens ” ” zetten.

Voorbeeld 2

Je wilt in de C-kolom dat het bedrag wordt weergegeven als een positieve of negatieve waarde. Als in de A-kolom het woord “Af” staat moet het bedrag van de B-kolom negatief worden, anders kan het bedrag van de B-kolom overgenomen worden.

In de afbeelding hieronder staat de ALS-formule in de C-kolom. In de E-kolom zie je de bijbehorende formule.

• Uitleg:
  De Logische_test A12=”af” is voor B12 inderdaad WAAR, dus wordt het tweede argument gebruikt als resultaat van de berekening: -B12 (dus een minteken voor de inhoud van B12).
  Bij A13 is die bewering ONWAAR, dus krijg je het derde argument als resultaat.
  In dit geval zijn de resultaten berekeningen, dus staan ze niet tussen ” ”. Het woord “af” moet echter wel tussen die aanhalingstekens, want het is een tekst.

Voorbeeld 3

Je wilt in de C-kolom de btw-uitgerekend hebben. Die is afhankelijk van de code die staat in de B-kolom. Welk tarief bij welke code hoort staat in de G-kolom.

Hieronder weer de afbeelding met de formule.

• Uitleg:
  De Logische_test B21=1 is voor B12 inderdaad WAAR, dus wordt het tweede argument gebruikt als resultaat. Dit is een berekening van de BTW: bedrag*juiste btw-percentage.
  Bij B22 is die bewering ONWAAR, dus krijg je het derde argument als resultaat.
  In dit geval staat er in de logische_test een getal (1), dus moet het niet tussen ” ”. Ook de berekening staat natuurlijk niet tussen ” ”.

Wat doe je als er meer dan 2 mogelijke resultaten zijn?

Bekijk dit voorbeeld maar, een variant op 1. Er zijn 5 mogelijke scores met bijbehorende teksten.
Je kunt dan niet zomaar een ALS-gebruiken, want die kan maar 2 resultaten geven.

Vaak wordt er dan een ALS in een ALS gebruikt. In dit voorbeeld zijn er 5 mogelijke resultaten, dus moeten er 4 ALS-en in elkaar gebruikt worden (dat wordt dan ook wel een ‘geneste als’ genoemd.
Als de Logische_test WAAR oplevert, krijg je als resultaat het tweede argument. Als het ONWAAR is gaat er een nieuwe ALS-gestart worden. En dat dan een aantal keren.
Het maakt de formule wat lastiger te maken (zeker met al die haakjes aan het einde) en het is ook niet zo handig terug te lezen wat er precies gebeurt.

Hieronder zie je enkele alternatieven die je zou kunnen gebruiken.
In de E-kolom zie je steeds weer de gebruikte functie ①.
Voor de formules op rij 36 en 37 zijn de cellen F40:G44 nodig ②.

Voor elk alternatief zie je hierna een uitleg.

ALS.VOORWAARDEN (Engels: IFS)

In C34 wordt ALS.VOORWAARDEN gebruikt. Bij deze functie zijn er steeds 2 argumenten die bij elkaar horen: Logische_test_nr  en Waarde_indien_waar_nr.
Zodra een test WAAR oplevert, worden de resterende argumenten genegeerd.

In dit voorbeeld komt WAAR pas bij de 4^e logische test.

SCHAKELEN (Engels: SWITCH)

In C35 wordt de minder bekende functie SCHAKELEN gebruikt. Hier geef je steeds op welke uitkomst je wilt hebben bij welke waarde (bij 5 hoort uitmuntend, bij 4 hoort goed, enz.)

Bij deze functie Hier geef je als eerste argument Expressie op welke cel je wilt testen (hier B35).
Vervolgens horen er steeds 2 argumenten bij elkaar: bij welke inhoud van die expressie hoort welk resultaat. Als Waarde1 gelijk is aan een 5 zal Resultaat1 verschijnen (uitmuntend). Vanaf daar wordt er gekeken naar Standaard_of_waarde2 met een bijbehorende Resultaat2.enz.
Als geen van de opties voorkomt, zal het laatste argument gebruikt worde: Standaard_of_waarde_#.

VERT.ZOEKEN (Engels: VLOOKUP)

In C36 zie je de veel gebruikte functie VERT.ZOEKEN. Hierbij zoek je de evaluatiescore op in een tabel en haalt het resultaat ook uit die tabel. Daarom moet je ergens die tabel maken.

Bij deze functie

• Het eerste argument Zoekwaarde is hier de evaluatiewaarde die je wilt opzoeken. Die staat hier in B36.
• Het tweede argument Tabelmatrix geeft aan waar je de tabel hebt gemaakt. In dit voorbeeld in F40:G44.
• Het derde argument Kolomindex_getal is het kolomnummer in die tabel waar het resultaat uit moet komen. De voorbeeldtabel bestaat uit 2 kolommen. De Zoekwaarde wordt ALTIJD gezocht in de eerste kolom van die Tabelmatrix en het resultaat komt hier uit de tweede kolom: dus een 2.
• Het laatste argument Benaderen moet hier ONWAAR zijn. Dat moet je altijd opgeven als de Zoekwaarde exact zo moet voorkomen on de 1^e kolom van de tabel.

X.ZOEKEN (Engels:XLOOKUP)

In C37 wordt de X.ZOEKEN gebruikt.
Die werkt ook met een Zoekwaarde, maar niet met een tabelmatrix.
Bij deze functie is het tweede argument Zoeken_matrix de kolom waar de zoekwaarde in opgezocht moet worden (hier F40:F44).
Het derde argument is de kolom waaruit je het resultaat wilt ophalen (hier dus G40:G44).

Een onverwachte situatie in het voorbeeld

Stel dat er een evaluatie voorkomt in de B-kolom, dan niet verwacht wordt, bijvoorbeeld een 6. Wat gebeurt er dan?

Je ziet dat ze in feite allemaal verkeerd gaan! De gecombineerde ALS geeft als resultaat slecht en de andere geven een foutmelding.

Is dat op te lossen?

• ALS: je moet er een extra ALS in zetten waarin je bijvoorbeeld aangeeft dat er “onbekend” of iets dergelijks komt te staan.
  
  
  
• ALS.VOORWAARDEN: deze wordt ook groter omdat je die extra voorwaarden ook moet toevoegen.
  Hiervoor zijn in dit voorbeeld 2 varianten mogelijk.
  Variant 1: je benoemt de situatie als de score groter is dan 5 (dan “onbekend”) en ook de situatie als die kleiner is dan 1 (ook “onbekend”).
  
  
  Variant 2:
  Je voegt een extra argument toe als laatste WAAR. Die laatste Logische_test noem je WAAR (=alle overige situaties) en je geeft het resultaat als dat zich voordoet.
  
  
  
• SCHAKELEN heeft hiervoor een ingebouwde oplossing.
  Hier kun je namelijk als laatst gebruikte argument een waarde opgeven die er moet komen als geen van de andere opties resultaat oplevert.
  
  
  
• VERT.ZOEKEN: hier moet je een extra functie omheen bouwen om dit te bereiken. Bijvoorbeeld de functie ALS.FOUT (Engels IFERROR).
  
  
  
• X.ZOEKEN: heeft voor dit probleem ook een ingebouwde oplossing, nl. het 4^e argument.
  

De functies vergeleken

• De ALS-in-een-ALS (in-een-ALS, enz.) is het lastigste te maken en terug te lezen.
• Zowel bij ALS als bij ALS.VOORWAARDEN moet je steeds het zoekcriterium (zoekcel en bijhorende waarde) opnieuw noemen.
  Bij SCHAKELEN hoeft dat maar één keer, nl. als eerste argument.
• Kijk je naar de functie zelf, dan zie je dat met en zonder die ‘foutafhandeling’ erbij in alle gevallen X.ZOEKEN verreweg het kortste is.
  

VERT.ZOEKEN en X.ZOEKEN hebben een extra tabel nodig, waar de gegevens uitgehaald worden. Dit is geen voor- of nadeel maar een gegeven.
Vergelijk je deze functies onderling, dan ‘wint’ wat mij betreft X.ZOEKEN.

• X.ZOEKEN gaat ervanuit dat de zoekwaarde exact zo moet voorkomen in de kolom, zodat je daar niets voor hoeft op te nemen (en vanuit mijn trainingservaring is dat waar deze functie het meest voor wordt gebruikt).
  • Bij X.ZOEKEN kun je veel eenvoudiger een melding opnemen voor het geval het niet gevonden kan worden.
  • Bij X.ZOEKEN hoeft de zoekkolom niet per se vóór de resultaatkolom te staan.

Ik heb hierover eerder een blog gemaakt (zie https://www.accountancyvanmorgen.nl/2023/09/04/exceltip-waarom-je-x-zoeken-moet-gaan-gebruiken) en een filmpje (https://youtu.be/xaDOwShXXYw)

Saskia Jacobsen is directeur-eigenaar van Toels-PC en helpt bedrijven en vooral hun gebruikers om slimmer met de apps op hun pc te werken. Zij geeft voor ons de Excel-Expert-trainingen.