wijzig taal:

Wat zijn dynamische bereiken? Dat zijn celbereiken in Excel die automatisch aangepast worden op het moment dat gegevens worden toegevoegd (of worden weggehaald). Dat is erg handig omdat dan de bereiken in formules niet meer hoeven te worden aangepast op het moment dat er gegevens worden toegevoegd of weggehaald. In dit artikel zal worden uitgelegd hoe deze dynamische bereiken kunnen worden gemaakt. Er komen 2 technieken aan de orde. De eerste techniek met de functie VERSCHUIVING kan in alle Excel-versies worden toegepast en de tweede techniek met tabellen, die veel eenvoudiger is, vanaf Excel versie 2007.

Dynamisch bereik met de functie VERSCHUIVING

Met hulp van de functie VERSCHUIVING kan een dynamisch bereik worden gemaakt. Vanaf Excel-versie 2007 is er nog een andere gemakkelijkere manier. Dit zal in de volgende paragraaf aan de orde komen. De uitleg wordt gebaseerd op onderstaand voorbeeld:

dynamische bereiken 1

De totale omzet kan natuurlijk eenvoudig worden uitgerekend met =SOM(B2:B9). Maar telkens als er een waarde wordt toegevoegd, dan moet deze formule worden aangepast. Met onderstaande formule kan het bereik van deze formule dynamisch worden gemaakt:

=SOM(VERSCHUIVING(B2;0;0;AANTAL(B:B);1))

De functie VERSCHUIVING kan gebruikt worden om een cel of celbereik te verplaatsen en of om de afmeting van een celbereik in te stellen. Het resultaat van deze functie is een verwijzing. Argumenten 2 en 3 worden gebruikt voor het verplaatsen (aantal rijen en aantal kolommen). Dat is hier niet aan de orde, vandaar dat twee keer een nul is ingevuld. De laatste 2 argumenten van deze functie worden gebruikt om de hoogte en breedte van het bereik in te stellen. De hoogte wordt hier ingesteld met de functie AANTAL(B:B), waarbij het aantal getallen in kolom B wordt geteld en de breedte is ingesteld op 1. Op deze manier komt het bereik altijd precies overeen met het aantal getallen in kolom B en is daarmee dynamisch gemaakt.

Nog fraaier en handiger is om het dynamisch bereik een naam te geven. De formules in het werkblad worden hierdoor korter en veel leesbaarder. Deze naam kan ingesteld worden bij de tab formules→namen beheren (voor Excel 2003 kies Invoegen→Naam). Kies dan voor nieuw en een volgend invulformulier verschijnt:

dynamische bereiken 2

Geef het bereik de naam omzet en vul bij ‘Verwijst naar’ de weergegeven formule in. Let er hierbij wel op dat alle verwijzingen absoluut moeten worden gemaakt en dus voorzien moeten worden van dollartekens. Daarna kan in het werkblad dit bereik gebruikt worden in de formules. Bijvoorbeeld: =SOM(verkopen)

Dynamisch bereik met tabellen

Vanaf Excel-versie 2007 is het veel eenvoudiger geworden om dynamische bereiken in te stellen. De methode uit de voorgaande paragraaf werk ook wel, maar eenvoudiger is om te werken met tabellen. Eerst moeten daarom de ingevoerde gegevens omgezet worden in een tabel. Selecteer alle gegevens inclusief de kolomkoppen en kies bij de tab Invoegen voor Tabel:

dynamische bereiken 3

Druk op OK en het gekozen bereik wordt automatisch veranderd in een tabel. Te zien is dat de regels om en om zijn gekleurd en dat de kolomkoppen zijn voorzien van filterknoppen:

dynamische bereiken 4

Een groot voordeel van tabellen is dat deze automatisch worden uitgebreid op het moment dat er gegevens aan worden toegevoegd. Type bijvoorbeeld in A11 een nieuwe datum en te zien is dat dan automatisch deze regel onderdeel wordt van de tabel. Bij het invoegen van de tabel krijgt deze automatisch een naam. Deze kan eenvoudige gewijzigd worden door bij tabelnaam de naam te wijzigen (rood omkaderd). Wijzig deze naam in een relevante naam, bijvoorbeeld in verkopen.

Tenslotte kan gebaseerd op deze tabel een formule gemaakt worden met de som van de omzet. Dat kan op verschillende manieren. Een eenvoudige manier is om eerst in te tikken =SOM( en daarna de relevante getallen uit de tabel te selecteren (dus vanaf B2 tot einde tabel). Sluit vervolgens af met een ) en de formule ziet er dan als volgt uit: =SOM(verkopen[omzet]). De verwijzing naar de tabel is dus tabelnaam gevolgd door de kolomnaam tussen vierkante haken.

Dit is een dynamisch bereik, want door het toevoegen van een nieuwe regel aan de tabel zal de tabel automatisch uitgebreid worden en daarmee ook de som automatisch aangepast worden.

Vragen / suggesties

Hopelijk heeft dit artikel geholpen bij het maken van dynamische bereiken in Excel. Als er verdere vragen over dit onderwerp zijn of suggesties voor verbetering, plaats dan een reactie hieronder.

arrow_up