Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Probeer onze RefTreeAnalyser
de beste Excel formule auditing tool.

Cursussen

Excel VBA Masterclass (Engels)
Excel VBA voor Financials

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > Nederlandse site > Artikelen > Excel Namen > Dynamische naam
This page in English

Bereiknamen in Excel

Een dynamisch bereik in een naam

Vaak is het zo, dat totalen berekend moeten worden van lijsten waar op een later tijdstip nog gegevens aan toegevoegd zullen worden. Het is dan niet handig om steeds bij het toevoegen van gegevens de celbereiken in formules aan te moeten passen. Het is praktischer als dit automatisch gebeurt. Zie bijvoorbeeld Blad5 van Namen01.xls.

Door gebruik te maken van de functie VERSCHUIVING kan een bereik van cellen worden "berekend". De functie wordt als volgt opgebouwd:

=VERSCHUIVING(startadres;AantalRijenOmlaag;AantalKolommenNaarRechts;AantalRijen;AantalKolommen)

Als start adres wordt cel B2 op Blad5 genomen: Blad5!$B$2 Het aantal rijen omlaag is nul: 0 Het aantal kolommen naar rechts ook nul: 0 Het aantal te gebruiken rijen wordt uitgerekend middels de AANTALARG functie. Deze functie telt het aantal cellen in een bereik waarin iets is ingevuld (inclusief tekst): AANTALARG(Blad5!$A:$A)-1.

Omdat de rij met titels niet moet meetellen is er 1 van het resultaat afgetrokken. Ten slotte moet het aantal kolommen worden ingevuld: 1 De gehele formule zal er dan als volgt uit moeten zien:

=VERSCHUIVING(Blad5!$B$2;0;0;AANTALARG(Blad5!$A:$A)-1;1)

Deze formule is ingevuld in de naam "AppelsDynamisch", zie Afbeelding 14.


Afbeelding 14: Definitie dynamische naam

Door kolom A te gebruiken om te bepalen hoeveel rijen er zijn, zal het bereik van deze nieuwe naam altijd gebaseerd zijn op het aantal ingevulde cellen in die kolom. Deze kolom mag daarom ook geen andere ingevulde cellen bevatten buiten het bereik van de tabel met de gegevens (bijvoorbeeld cel A100), anders klopt het aantal niet. Ook lege cellen in het bereik dat wťl interessant is leiden natuurlijk tot problemen, omdat er dan te weinig regels meegenomen gaan worden.

Wanneer nu een nieuw "gebied" aan de tabel wordt toegevoegd in cel A5, bijvoorbeeld AustraliŽ, dan zal de naam AppelsDynamisch automatisch gaan verwijzen naar het bereik B2:B5. Als dus een verkoopcijfer voor appels in AustraliŽ wordt ingevoerd, dan wordt dat vanzelf in het totaal verwerkt. Om de bereiken voor peren en bananen ook dynamisch te maken, wordt weer de VERSCHUIVING functie gebruikt. Nu wordt deze echter gerelateerd aan de al bestaande dynamische naam AppelsDynamisch. PerenDynamisch wordt gedefinieerd als:

=VERSCHUIVING(AppelsDynamisch;0;1)

Ofwel: het bereik AppelsDynamisch met een verschuiving van 1 kolom naar rechts. Op soortgelijke wijze wordt BananenDynamisch gedefinieerd:

=VERSCHUIVING(AppelsDynamisch;0;2)

Ten slotte kan de gehele tabel ook nog dynamisch gemaakt worden, zodat toevoegingen in de breedte ook worden verwerkt: "Omzetdynamisch".

=VERSCHUIVING(Blad5!$B$2;0;0;AANTALARG(Blad5!$B:$B)-1; AANTALARG(Blad5!$1:$1)-1)

Hierbij wordt dus zowel het aantal rijen als het aantal kolommen waarnaar deze naam verwijst berekend.

De werking van deze namen kan worden gecontroleerd door in het werkblad Blad5 op de toets F5 (Ga Naar) te drukken, de naam in het verwijzing vak in te vullen en op OK te klikken. Als het goed is wordt het berekende bereik dat bij die naam hoort dan geselecteerd.

Een praktijkvoorbeeld van dynamische namen in combinatie met een grafiek is te zien in het bestand autochrt.zip.