Het kan steeds vaker voorkomen dat je in Excel dit ziet staan: het #-teken achter een celadres.
Wat houdt dat in? Waarom verschijnt dat?

Het heeft te maken met ‘dynamische bereiken’ (Engels: dynamic arrays) die gebruikt worden. Daarom eerst een uitleg over dat onderwerp.

Video bij Blog

Bij dit blog is ook een videotip gemaakt. Dit is de link https://youtu.be/NoBwsSEXZOY

Wat is een dynamisch bereik?

Je verwacht waarschijnlijk dat in cel D2 staat: =B2*C2> En dat die formule is gekopieerd naar de andere cellen eronder. Ze werkt Excel al vanaf het begin!

Maar je kunt dit tegenwoordig ook anders maken. Je wilt immers dat in de D-kolom alle prijzen van B vermenigvuldigd worden met alle aantallen in C op dezelfde rij! Dan hoef je maar één berekening te maken waarin je dat aangeeft: =B2:B6 * C2:C6

Als je dit invoert ziet het er zo uit.

De berekening staat in één keer in D2:D6. En er staat een blauwe rand om die cellen. Als je heel goed kijkt, zie je dat de berekening in de formulebalk in D2 zwart is, en in de andere cellen grijs.

Dat betekent dat de formule alleen in D2 staat en ‘overloopt’ in de andere cellen van het blauw omrande gebied: het overloopgebied of het dynamische bereik.
Zou er iets staan in een van die cellen, dan krijg je een foutmelding: #OVERLOPEN! (Engels: #SPILL!) Maak je de cel weer leeg dan is het probleem opgelost!

Een dynamisch bereik is dus een gebied waarin één formule staat (in de cel linksboven), die overloopt in de andere cellen van het blauw omrande gebied. Je ziet die blauwe rand als je een cel in dat gebied selecteert.

Berekening maken met een dynamisch bereik

Ga je nu een totaaltelling maken in D8 ①, dan zie je dat zodra je het overloopgebied exact selecteert ②, dat niet meer het gebruikelijke D2:D6 verschijnt, maar D2#.
Dat betekent “het overloopgebied dat begint in D2”. Dat moet in dit geval (SOM) worden opgeteld.

Nieuwe functies met dynamische bereiken

In bovenstaande situatie kies je er zelf voor om een formule te maken met een dynamisch bereik. Maar er zijn intussen ook al veel functies die als uitkomst altijd een dynamisch bereik hebben.

Een voorbeeld is de fdunctie FILTER(). Die geeft als resultaat een gefilterde lijst in een dynamisch gebied.
In de afbeelding wordt links hierbij een normaal cellenbereik gebruikt ① en rechts een tabel ②.

=FILTER() heeft hier 2 argumenten.

• Het eerste is het tabelbereik (bij ① A3:C17 en bij ② de naam van de tabel Tabel1).
• Het tweede argument is de kolom waarop gefilterd moet worden (bij ① B3:B17 en bij ② de naam van de kolom Cat. van Tabel1: Tabel1[Cat.]). Die kolom moet gelijk zijn aan een B (=”B”).

Je ziet dat in beide gevallen het resultaat een dynamisch bereik is.

Een ander voorbeeld van een dynamische functie is UNIEK (Engels UNIQUE). Die geeft als resultaat een lijst met de unieke items uit een gebied.

De functie heeft hier 1 argument.

• Het bereik waar de unieke items uit moeten komen.
  Bij ① B3:B17 en bij ② de kolom Land van herkomst van de tabel Tabel2 (Tabel2[Land van herkomst]).

Weer de #

Zou ik bij de functie FILTER een optelling maken van het dynamische bereik, dus =SOM(E3#), dan zullen alle data in de cellen E3:G8 opgeteld worden: 404,8. Daarbij zullen alleen getallen van G3:G8 opgeteld worden (SOM slaat niet numerieke gegevens over bij het optellen).

Zou ik de functie AANTALARG (Engels COUNTA) gebruik bij het dynamische bereik van de uniek-functie, dan staat er dus =AANTALARG(D3#) en is de uitkomst 3.

Voordelen van een tabel versus bereik

Een dynamische functie kan dus zowel overweg met een cellenbereik als met een Excel-tabel. Maar dat laatste is flexibeler!
Stel dat er bij het voorbeeld van de filterfunctie een artikel categorie B bij komt. Dan zal bij ① het cellenbereik handmatig aangepast moeten worden (je moet er dus zelf aan denken en actie ondernemen!). Bij een tabel wordt de tabel vanzelf groter en past het resultaat zich vanzelf aan ②! Minder kans op fouten dus!

Bij UNIEK hetzelfde: wordt het bereik groter, dan breidt de tabelformule zich automatisch uit en de bereikformule doet dat niet.

Samenvatting

De # kom je dus tegen in berekeningen waarbij verwezen wordt naar een dynamisch bereik.
Zo’n bereik is herkenbaar aan de blauwe rand eromheen, als je een cel in dat bereik selecteert.
De berekening staat alleen in de cel linksboven van dat bereik en ‘loopt over’ in de andere cellen (die cellen moeten leeg zijn).
Een dynamisch bereik komt het beste tot zijn rechts als je die gebruikt met een Excel-tabel. Bij het groter (en kleiner) worden van de tabel, past het bereik zich automatisch aan omdat er tabelformules gebruikt worden, zoals je ziet in de laatste afbeelding.