Wie berekeningen maakt, krijgt vanzelf te maken met relatieve en absolute celverwijzingen. Ofwel: soms moet je een $-teken zetten voor een rij- en kolomnummer of een van beide. Met dynamische bereiken hoeft dat in veel situaties niet meer. Je kunt bovendien je formules ook zo maken dat er minder bijgewerkt hoeft te worden als er nieuwe data bij komen. Er komen ook steeds meer nieuwe functies bij die als resultaat een dynamisch bereik hebben. Dat kan veel tijd schelen. Tijd voor een update dus.
Wat is een dynamisch bereik (Engels: array)?
Normaal verwijs je naar één cel ① en bij berekeningen als bijvoorbeeld SOM meestal naar een cellenbereik ②. Dat werkt al zo vanaf de “geboorte” van Excel.
In de oudere versies is het zo, dat als je zonder een functie erbij verwees naar een bereik, dat je dan een foutmelding krijgt: #WAARDE! ①. Maar in de nieuwere versies is dat niet meer zo. Je verwijst dan automatisch naar een dynamisch bereik ②.
Hoe herken je een dynamisch bereik?
- Als een cel in zo’n bereik is geselecteerd, zie je er een blauwe rand omheen ②.
In E2 is een verwijzing ingevoerd naar C2:C7 en je ziet als uitkomst ook alle waarden in dat bereik. In wezen staat de formule alleen in cel E2, de andere cellen in het omkaderde bereik zijn in feite leeg (kopieer zo’n cel maar eens, dan zie je dat vanzelf). - Wil je de berekening aanpassen, dan moet je dat doen in de cel linksboven van het dynamische bereik.
- Selecteer je een andere cel in dit blauwe omkaderde gebied, dan zie je de formule wel staan, maar is die grijs ③.
- Als je cel alleen C2 zou verplaatsen, dan gaat het hele dynamische bereik ook mee.
- Als een van de cellen in het omkaderde bereik niet leeg is, zie je de foutmelding #OVERLOPEN! (Engels #SPILL!) ④,
- Een dynamisch bereik moet altijd een aaneengesloten cellenbereik zijn (bij een meervoudige selectie krijg je de foutwaarde #WAARDE! [#VALUE!]),
- Een dynamisch bereik kun je niet opnemen in een Excel-tabel. Je krijgt dan een OVERLOPEN-foutmelding die niet opgelost kan worden!
Wat gebeurt er in oudere versies (alle versies tot en met Excel 2019)?
Maak je een dynamisch bereik, dan herken je direct of jouw versie van Excel dit kent: krijg je dan de #WAARDE!-foutmelding (Engels: #VALUE!), dan kun je dit niet gebruiken!
Wanneer je in een oudere Excel-versie een bestand opent waarin een dynamisch bereik aanwezig is (omdat het gemaakt is in een nieuwere versie), dan wordt dit dynamische bereik automatisch ‘vertaald’ naar een matrixfunctie (herkenbaar aan de accolades { } eromheen.
De foutmelding #OVERLOPEN!
Wanneer je deze foutmelding ziet, betekent dit dus dat de berekening niet kan overlopen naar de cellen die daarvoor nodig zijn. Er komt dan ook een groene driehoek bij die cel te staan ⑤. Als de cel is geselecteerd, zie je een waarschuwingspictogram ⑥. Klik je hierop, dan kun je zien wat er aan de hand is. Je kunt ook de cellen die de problemen geven laten selecteren ⑦.
Verwijzen naar een dynamisch bereik
Om te verwijzen naar een dynamisch bereik verwijs je naar de eerste cel (dat is immers de enige met de berekening) en plaats je er een hekje # achter. Vaak gebeurt dit vanzelf, bijvoorbeeld als je een functie gebruikt ①.
Maar wil je bijvoorbeeld verwijzen naar een dynamisch bereik met een gegevensvalidatie ②, dan moet je het # er wel zelf achter typen.
Formules maken met dynamische bereiken
Behalve dat je kunt verwijzen naar een dynamisch bereik, kun je het ook gebruiken in ‘normale’ berekeningen.
Hieronder zie je een voorbeeld. Normaal zou je de formule maken zoals bij ① en dan kopiëren naar de andere cellen. Maar met een dynamisch bereik kun je ook doen ②. Je maakt dan één berekening die vanzelf doorloopt in het dynamische bereik.
Wanneer je een situatie hebt als hieronder, dan moet je een absolute verwijzing maken naar de cel met het btw-percentage ($B$1). Als je dat niet doet, gaat het verkeerd met het kopiëren.
Bij een formule met een dynamische verwijzing hoeft dat niet.
Excel ‘weet’ immers dat alle cellen in het dynamische bereik vermenigvuldigd moeten worden met het btw-percentage ①.
In situatie ② wordt het nog veel aantrekkelijker om te werken met formules met dynamische bereiken. Je wilt de prijzen weten met de bijbehorende indexpercentages. Verwijzing naar de prijzen zou met een ‘normale’ formule zijn $A2 en een verwijzing naar de indexwaarde B$2.
Een ander voordeel is dus dat je niet meer hoeft te kopiëren: automatisch staat in het hele dynamische bereik dezelfde formule. Een wijziging hoeft dan ook alleen in de cel linksboven doorgevoerd te worden en loopt dan vanzelf over!
Functies die resulteren in dynamische bereiken
Zoals vermeld in de introductie zijn er de laatste tijd een groot aantal functies bijgekomen, die resulteren in een dynamisch bereik. Enkele voorbeelden:
- FILTER Maakt gefilterde subset van een lijst/tabel.
- SORTEREN Maakt gesorteerde set van een lijst/tabel.
- UNIEK Toont alleen unieke data uit een lijst/tabel
Vooral in combinatie met een verwijzing naar een Excel-tabel zijn dit heel handige functies. Een voorbeeld hiervan zie je hieronder.
Met de functie UNIEK is een lijst gemaakt van de unieke items die voorkomen in de tabel in de kolom Afdeling.
Hieronder zie je dat er een nieuwe rij aan de tabel is toegevoegd: de lijst met afdelingen is automatisch bijgewerkt.
En in de kolom erachter (aantal mw) is de functie AANTAL.ALS gebruikt om te tellen hoeveel medewerkers er zijn per afdeling. Die berekening is ook dynamisch gemaakt (zie de verwijzing naar het criterium E3#). Op deze manier wordt ook die automatisch bijgewerkt als er nieuwe medewerkers/afdelingen bijkomen.
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.
Ze geeft voor ons de Excel Expert-trainingen. Een eerdere blog ging ook al over dynamische functies in combinatie met gegevensvalidatie (https://www.accountancyvanmorgen.nl/2024/01/29/exceltip-flexibele-altijd-gesorteerde-keuzelijst/). Naast de blogs op onze website, publiceert Saskia ook wekelijks op YouTube een korte tip (maximaal 2-3 minuten) die meestal over Excel gaat.
Geef een reactie