voor de financieel professional met ambitie
Een basale maar heel belangrijke functie binnen Excel is ALS (Engels: IF). Hierbij wordt een logische vergelijking bekeken en aan de hand van diens uitkomst – waar of onwaar – worden twee verschillende acties uitgevoerd. Maar dat is slechts het begin, de functie is relatief eenvoudig uit te breiden waardoor hij veel programmeerbaarder is.
Met de functie ALS is een logische vergelijking op te stellen, een formule die een ‘als-dit-dan-dat’ uitvoert. De bekendste toepassing is het instellen van een drempelwaarde waarbij een bepaalde actie wordt uitgevoerd, bijvoorbeeld het invullen van een celwaarde of het toepassen van voorwaardelijke opmaak. De syntaxis komt neer op =ALS(voorwaarde;actie1;actie2) (=IF(voorwaarde,actie1,actie2)) waarbij actie1 wordt uitgevoerd als de vergelijking WAAR oplevert en actie2 wordt uitgevoerd bij een ONWAAR.Interactieve financiële rapportagesIn deze meerdaagse training laat Excel-MVP Tony de Jonker u de ins en outs zien
>> Meer over logische vergelijkingen leest u in het artikel: Voer conditionele opdrachten uit met booleaanse waarden.
Een simpel voorbeeld van Microsoft zelf is een statusveld dat automatisch wordt ingevuld aan de hand van een logische vergelijking. In dit geval gaat het om een beoordeling of een bedrag binnen het budget valt. In het voorbeeld is het gebudgetteerde bedrag opgenomen in kolom B en het werkelijke bedrag in kolom C. Deze twee kunnen eenvoudig met elkaar worden vergeleken om in kolom D aan te geven of de uitgave volgens begroting is geweest.
De logische vergelijking is dan =ALS(C2>B2;“JA”;“NEE”), waarbij tijdens de eerste parameter wordt vergeleken of C[celnummer] hoger is dan B[celnummer]. Is dat een WAAR oplevert, wordt actie1 uitgevoerd en de tekst ‘NEE’ ingevuld, om aan te geven dat het budget is overschreden. Bij een ONWAAR wordt actie2 uitgevoerd, het invullen van ‘JA’. Door deze cel te selecteren en naar beneden te trekken, wordt de formule toegepast op alle geselecteerde cellen in kolom D.
De simpelste toepassing van de functie. Deze formule vergelijkt kolommen B en C en geeft het resultaat weer in kolom D.
Nog een stapje verder: u heeft niet alleen verschillende voorwaarden, maar ook verschillende uitkomsten per voorwaarde. Daar biedt nesting een oplossing, waarbij een ALS-functie binnen een ALS-functie kan worden gebruikt. Als voorbeeld een simpele glijdende temperatuurschaal, waarbij alles onder 0 graden Celsius als ‘Vriezend’ wordt weergegeven, tussen 1 en 15 als ‘Normaal’, 16-24 als ‘Kamertemperatuur’, 25-39 als ‘Warm’, 40-59 als ‘Heet’, 60-79 als ‘Zeer heet’ en alles boven de 80 als ‘Kokend’.
Dé nieuwsbrief voor de financial die meer wil weten over Excel. In de maandelijkse nieuwsbrief staat informatie over nieuwe toepassingen, handigheidjes, tips en trucs en opleidingen.
Schrijf u vandaag nog in voor onze gratis Excel nieuwsbrief!
Daarbij wordt elke vervolgstap in de schaal een voorwaarde die gekoppeld is aan actie2. Als een waarde wordt gevonden: actie1. Als het iets anders is: actie2, wat opnieuw een logische vergelijking is. Met daarin opnieuw een logische vergelijking waarin actie2 (of actie3 in dat geval) wéér een logische vergelijking is. Dit kunt u als een matroesjka dieper en dieper doorvoeren, tot aan een maximum van 64 functies. Een simpel begin zou zijn met drie verschillende resultaten: Vriezend, Nominaal of Kokend. U formuleert dat als =ALS(B2>80;”Kokend”;ALS(B2>0;”Nominaal”;ALS(B2<0;”Vriezend”)))
U begint u de schaal met temperaturen boven de 80 graden: =ALS ([cel]>80;”Kokend”;actie2). Dat betekent dat als een waarde boven de 80 wordt gevonden, het veld wordt ingevuld met de tekst ‘Kokend’. Zo niet, dan wordt actie2 uitgevoerd en dat is in dit geval een geneste ALS-functie om de volgende stap te evalueren. Dat wordt ‘Zeer heet’ met de waardes tot en met 60 graden Celsius. Dus =ALS([cel]>80;”Kokend”;ALS([cel]>60;”Zeer heet”;actie3)) waarbij actie3 de volgende stap wordt. Let daarbij op dat elke geneste functie zijn eigen haakjes heeft, wat betekent dat het aantal sluitende haakjes overeenkomt met het aantal ALS-functies dat u gebruikt. Excel geeft deze haakjes in de formulebalk gekleurd per geneste functie weer, om het overzichtelijker te maken.
In dit voorbeeld wordt de formule dan als volgt:
=ALS(B2>=80;”Kokend”;ALS(B2>=60;”Zeer heet”;ALS(B2>=40;”Heet”;ALS(B2>=25;”Warm”;ALS(B2>=16;”Kamertemperatuur”;ALS(B2>=1;”Normaal”;”Vriezend”))))))
In dit voorbeeld is de cel met formule geselecteerd (C2) en wordt weergegeven welke cel de input vormt (B2)
Er is ook een alternatief voor gebruikers van Microsoft 365 in de vorm van ALS.VOORWAARDEN (IFS). De logica hiervan is hetzelfde, maar daarbij hoeven geen geneste ALS-functies worden gebruikt en kunnen ze worden geketend. De formule wordt op ongeveer dezelfde manier geëvalueerd als hierboven: als een vergelijking WAAR oplevert, wordt de daarachter gedefinieerde actie uitgevoerd. Bij het laatste element geeft u ;WAAR;[“Tekst”] (,TRUE,[“Tekst”]) die wordt weergegeven als er niet is voldaan aan de voorgaande voorwaarden. In her eerdere voorbeeld met de temperatuurschaal zou dat dan de volgende formule opleveren:
=ALS.VOORWAARDEN(B2>=80;”Kokend”;B2>=60;”Zeer heet”;B2>=40;”Heet”;B2>=25;”Warm”;B2>=16;”Kamertemperatuur”;B2>=1;”Normaal”;WAAR;”Vriezend”)
Hetzelfde resultaat als hierboven, maar dan met een beter leesbare formule. Deze variant is alleen beschikbaar voor gebruikers met een Microsoft 365-abonnement
Op deze manier is ALS in te zetten om foute cellen op te sporen. Daarbij gebruikt u ISFOUT (ISERROR) binnen een ALS-functie. Actie1 is daarbij wat wordt uitgevoerd wanneer een fout wordt aangetroffen en actie2 wanneer de fout niet wordt gevonden. Met een formule als =ALS(ISFOUT(A1,Z10);“Er is een fout gevonden”;[alternatieve berekening]) (of =IF(ISERROR(A1,Z10),”Er is een fout gevonden”, [alternatieve berekening])) wordt gecontroleerd of een cel een fout bevat. Als dat zo is wordt actie1 uitgevoerd, wat het bericht ‘Er is een fout gevonden’ oplevert. Als alles in orde is wordt actie2 uitgevoerd. De functie ISFOUT2 doet hetzelfde als ISFOUT, maar zondert niet beschikbare velden – #N/B (#N/A) – uit.
Categorie: Nieuws, Verdieping Tags: Automatisering, Excel, Software, technologie Dossier: Automatisering
Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *
Regeltjes, regeltjes en nog eens regeltjes, het lijkt er volgens hoogleraar Compliance Sylvie Bleker-Van Eyk aan de Vrije Universiteit sterk op dat de burden of compliance vrijwel grenzeloos is. Maar … [Lees meer...]
IT-systemen en data zijn alom aanwezig in organisaties. Behalve dat IT de interne processen van bedrijven ondersteunt, maken IT en data ook steeds vaker deel uit van het producten- en … [Lees meer...]
24 december 2021 door Tony De Jonker 0
Ik heb een tabel met in kolom H een hulpkolom waarin ik een VERT.ZOEKEN formule heb staan. Ik wil de formule in H2 kopiëren naar cellen H3 tot en met H9000. Normaliter ga ik naar de vulgreep en trek … [Lees meer...]
Data-analytics: er valt een hele wereld mee te winnen, stelt prof.dr. Jacques de Swart MBA, een van de sprekers bij de Collegereeks Data Driven Finance. Zijn voorliefde voor wiskunde is daarbij … [Lees meer...]
Datagedreven organisaties spelen sneller en efficiënter in op grote veranderingen, bijvoorbeeld een pandemie. Hoe verandert een organisatie naar zo’n modern bedrijf? Een stappenplan. In veel … [Lees meer...]
Masterclass Fusies & Overnames 17 & 18 januari 2022 – Nyenrode, Breukelen
Collegereeks Accountancy & Controlling Start 8 maart 2022 – Nyenrode, Breukelen
Collegereeks Data Driven Finance Start 9 maart 2022 – Zeist
Collegereeks Risk & Compliance Start 10 maart 2022 – Nyenrode, Breukelen
Collegereeks Fraudebeheersing, Integriteit & Ethiek Start 7 april 2022 – Zeist
Leergang Financieel Leiderschap Start 12 april 2022 – Nyenrode, Breukelen
Collegereeks Bedrijfskunde voor Financials Start 9 mei 2022 – Zeist
Collegereeks Turnaround Management & Restructuring Start 17 mei 2022 – Nyenrode, Breukelen
Collegereeks Controller als Business Partner Start 18 mei 2022 – Nyenrode, Breukelen
Masterclass Fusies & Overnames 9 & 10 juni 2022 – Nyenrode, Breukelen
Nationale Controllersdag 16 juni 2022 – locatie nog niet bekend
Masterclass Strategisch Performance Management voor Financials 16 & 17 juni 2022 – Zeist
Collegereeks Risk & Compliance Start 6 september 2022 – Nyenrode, Breukelen
Collegereeks Turnaround Management & Restructuring Start 21 september 2022 – Nyenrode, Breukelen
Collegereeks Accountancy & Controlling Start 22 september 2022 – Nyenrode, Breukelen
Collegereeks Data Driven Finance Start 26 september 2022 – Zeist
Collegereeks Controller als Business Partner Start 1 november 2022 – Nyenrode, Breukelen
Leergang Financieel Leiderschap Start 3 november 2022 – Nyenrode, Breukelen
Masterclass Fusies & Overnames 28 & 29 november 2022 – Nyenrode, Breukelen