Tutorial Excel Dynamische bereiken

  1. inleiding
  2. dynamisch bereik met de functie verschuiving
  3. dynamisch bereik met tabellen

1. INLEIDING

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 deze tutorial 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.

 

2. 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_bereiken1_225x300

De totaalomzet kan natuurlijk eenvoudig worden uitgerekend met =SOM(B2:B10). 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))

(NB: vervang bij Engelstalige Excel-versies SOM door SUM en VERSCHUIVING door OFFSET)

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_bereiken2_375x235

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. Selecteer hiervoor in de formulebalk de gehele formule en druk op F4 toets, waarna alle celverwijzingen automatisch absoluut worden gemaakt. Daarna kan in het werkblad dit bereik gebruikt worden in de formules. Bijvoorbeeld: =SOM(omzet)

 

3. 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_bereiken3_490x280

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_bereiken4_200x400

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.

 

Interesse in andere tutorials? Ga dan naar ons tutorials-overzicht of neem een kijkje op onze pagina met tips&trucs.

Reacties op onze tutorials en tips&trucs zijn van harte welkom. Dat kan door hieronder een reactie toe te voegen of door rechtstreeks contact met ons op te nemen.

4 reacties op Excel dynamische bereiken

  • Goedemiddag,

    Ik heb een vraag over de formules in Excel.
    Wij hebben een eigen transport bedrijf en vanaf 1 April moet er in België tol betaald worden.
    De tarieven zijn variabel naarmate van de euro-norm.
    Ik heb nu een aantal tabs gemaakt met de klanten, de tolheffing en de chauffeurs.
    In de tabbladen (klanten; a,b,c, enz…) moeten straks de tarieven komen te staan onder de rit.
    Hoe ga ik dat voor elkaar krijgen?
    Als jullie me terug mailen dan kan ik eventueel de bijlage erbij doen om het duidelijker te maken.

    Alvast bedankt voor de hulp,

    Met vriendelijke groeten, With kind regards

    Denno Verolme

    Together
    we’ll make the difference

    Verolme Transport Nieuwe Tonge B.V.

    • Is dit al beantwoord? eventueel ben ik bereid

      • Dag Pieter,
        Dit is al door mij beantwoord. Dit is toen via de mail verder afgehandeld.
        Groeten, Manfred

  • Goedemorgen,

    Mijn naam is Bjorn Koolen van Modis Engineering. Ik ben een Modelleurs (3D-tekenaars) dagplanning aan het opzetten. Iedere week is verdeeld in 5 kolommen (5 werkdagen). Bovenaan staan van links naar rechts Maandag t/m Vrijdag, daar onder de datum van de dag. Vervolgens staan er in de rij 9 cellen (4 werkuren en in het midden pauze). Daaronder staat het totaal aantal (gewerkte) uren per dag, een optelsom van ingevulde cellen. In de cellen kan de Modelleur kopiëren wat deze per werkuur ingepland heeft. Alle benodigde cellen hebben een eigen kleur en een tekst erin.

    Maar nu is de planning voor de komende vier weken opgezet, in de meest ideale situatie. Het gaat zeker gebeuren dat er dingen tussen komen, zoals besprekingen of onvoorziene werkzaamheden. Nu wil ik dat wanneer graag, dat wanneer de Modelleur iets tussen de bestaande geplande uren wil toevoegen, de rest automatisch doorschuift.

    Hoe is dit op te lossen?

    Ik ben erg benieuwd, want op het internet kan ik tot op heden nog geen bruikbare oplossing vinden

    Alvast hartelijk dank voor uw tijd en moeite,

    Met vriendelijke groet,

    Bjorn koolen

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.