Benoemde bereiken en gestructureerde verwijzingen

Twee manieren om gegevens een naam te geven

Excel kent twee soorten namen. Een benoemd bereik (kortweg een naam) is een label dat je aan een cel, een bereik, een constante of een formule hangt, zoals VatRate voor cel $E$2.

Een tabel is een blok gegevens dat Excel als één object met een eigen naam behandelt, en waarvan je de kolommen aanspreekt op kolomkop, zoals Journal[Amount]. Die tweede vorm heet een gestructureerde verwijzing.

Beide vervangen een adres door een woord, maar ze lossen verschillende problemen op. Een naam geeft één waarde of één vast gebied een betekenis. Een tabel geeft een lijst die blijft groeien een vorm die Excel voor je bijhoudt, zodat formules compleet blijven als er rijen bijkomen.

Een naam maken en beheren

Er zijn drie plekken om met namen te werken:

• in het Naamvak (de snelste manier): selecteer de cel of het bereik, typ de naam in het Naamvak links van de formulebalk en druk op Enter
• via Formules > Naam definiëren: opent een venster waarin je ook het bereik (de geldigheid) kiest en een opmerking toevoegt
• in Namen beheren (Ctrl+F3): toont elke naam met waarde, verwijzing en bereik, en laat je namen wijzigen, hernoemen of verwijderen

Voor een naam gelden een paar regels:

• hij begint met een letter, een onderstrepingsteken of een backslash
• hij bevat geen spaties (gebruik VatRate of Vat_Rate)
• hij mag niet op een celadres lijken
• hoofdletters maken niet uit en hij mag maximaal 255 tekens hebben

Hernoem je een naam in Namen beheren, dan past Excel alle formules aan die de naam gebruiken.

Bereik: werkmap of werkblad

Elke naam heeft een bereik (de kolom Bereik in Namen beheren). Een naam met werkmapbereik werkt op elk werkblad onder hetzelfde woord.

Een naam met werkbladbereik werkt alleen op het eigen blad, en hetzelfde woord kan op meerdere bladen een andere betekenis hebben, zoals Totaal op elk maandblad.

Vanaf een ander blad bereik je een naam met werkbladbereik door het blad ervoor te zetten: Januari!Totaal.

Namen voor constanten en formules

Een naam hoeft niet naar een cel te wijzen. In het vak Verwijst naar van Namen beheren kun je een constante typen, zoals =0,21, of een formule, zoals =Blad1!$E$2*12. Het eerste geeft je een tarief zonder cel, het tweede een berekening die je onder één woord hergebruikt.

Is een invoer van belang voor de controle, geef dan de voorkeur aan een cel met label die je een naam geeft. Een beoordelaar ziet de waarde dan op het werkblad, kan haar naar de bron herleiden, en de naam levert alleen het leesbare woord. Een tarief dat alleen in Namen beheren staat, verandert alle formules en staat in geen enkele cel.

=AFRONDEN(SOM(B2:B7)*VatRate, 2)

=AFRONDEN(SOM(B2:B7)*VatRate; 2)

=SOMMEN.ALS(C2:C9, C2:C9, ">"&ConfirmLimit)

=SOMMEN.ALS(C2:C9; C2:C9; ">"&ConfirmLimit)

Excel-tabellen

Selecteer een cel in je gegevens en druk op Ctrl+T (of kies Invoegen > Tabel, of Opmaken als tabel op het tabblad Start; in Nederlandstalig Excel werkt ook Ctrl+L) en bevestig dat de gegevens kopteksten hebben.

Excel geeft het blok een naam als Tabel1; wijzig die onder Tabelontwerp in het vak Tabelnaam in iets zinvols, bijvoorbeeld Journal. Kopteksten moeten tekst en uniek zijn.

Een tabel groeit vanzelf. Typ of plak een rij direct onder de laatste en de tabel breidt uit, geeft de nieuwe rij dezelfde opmaak en vult een kolomformule door. De Totaalrij (onder Tabelontwerp) voegt een rij toe met SUBTOTAAL, dat door een filter verborgen rijen negeert.

Gestructureerde verwijzingen

Een gestructureerde verwijzing bestaat uit:

• de tabelnaam
• de kolomkop tussen vierkante haken
• eventueel een speciaal item dat een deel van de tabel kiest

Excel schrijft haar voor je uit zodra je tijdens het typen van een formule op een tabelkolom klikt. De verwijzing omvat precies de gegevensrijen, dus ze groeit en krimpt mee en pakt nooit per ongeluk de kop of de totaalrij mee.

Voor de haken gelden een paar regels:

• een kop met een spatie heeft geen extra vierkante haken nodig: Journal[Net amount]
• extra haken zijn nodig voor speciale tekens in de kop (komma, punt, #, haken, apostrof) en voor een kop met een spatie na @: [@[Net amount]]
• in de tabel zelf betekent [@Amount] de cel van deze rij, zodat een berekende kolom er zo uitziet: =[@[Net amount]]*VatRate

Nederlandstalig Excel toont de speciale items vertaald, zoals de tabel laat zien; de kolomkoppen en je tabelnaam blijven zoals je ze typte. De formules op deze site staan in Engelse vorm.

In de formules op deze pagina hebben de namen, tabellen en kolommen Engelse namen (VatRate, Journal, Amount), ook waar het voorbeeldraster Nederlandse kopteksten toont; in een Nederlandstalig bestand van een cliënt zijn het meestal Nederlandse namen, zoals BtwTarief en Journaal[Bedrag].

=SOM(Journal[Amount])

=SOM(Journal[Amount])

=SOMMEN.ALS(Journal[Amount], Journal[Account], 4500)

=SOMMEN.ALS(Journal[Amount]; Journal[Account]; 4500)

=Journal[[#Totals],[Amount]]

=Journal[[#Totals],[Amount]]

Wanneer wat te gebruiken

Gebruik een tabel als de gegevens een lijst zijn die langer wordt: een dagboek, een factuurlijst, een steekproef. Gebruik een naam voor een enkele waarde of een vast gebied dat meerdere formules delen: een tarief, een materialiteit, een balansdatum. Ze combineren goed: een tabel voor de gegevens en namen voor de invoer waarmee je ze toetst.

De uitgewerkte voorbeelden hieronder gebruiken getypte adressen, zodat deze site ze kan narekenen en controleren. Vervang in je eigen werkmap het adres door de naam of de gestructureerde verwijzing die in de uitwerking staat.

De controle-invalshoek

Namen maken formules van de cliënt leesbaar, maar verbergen waar een waarde staat: =B2*VatRate laat niet zien welke cel of constante VatRate is. Open op elke werkmap van de cliënt eerst Namen beheren. Controleer daarna:

• de kolom Verwijst naar bij elke naam
• namen met fouten (filter daarop)
• namen die naar #VERW! of naar een andere werkmap verwijzen

Een verwijderd werkblad laat precies zulke namen achter.

Controleer ook dit:

• wat een naam bevat: een constante die in Namen beheren is getypt is een invoer zonder cel, en een benoemde formule kan een berekening verbergen
• verborgen namen: een macro kan namen onttrekken aan Namen beheren; gedraagt een werkmap zich vreemd, bekijk dan ook de formules zelf (Ctrl+` toont ze allemaal)
• tabellen: of een totaalrij, een filter of een verborgen kolom verandert wat een gestructureerde verwijzing ziet

Verwante onderwerpen

• Celverwijzingen: relatief, absoluut en gemengdEen celverwijzing is het adres dat een formule leest. Dollartekens bepalen of het adres meeschuift als je de formule kopieert, en dat goed doen laat een formule een hele kolom of tabel vullen.
• CriteriaEen criterium is de voorwaarde waaraan een cel moet voldoen om te worden geteld, opgeteld of gemiddeld: hoe je exacte, vergelijkings-, datum- en leeg-criteria schrijft en hoe je er meerdere combineert.
• Overloop en dynamische matricesOverloop ontstaat als één formule meerdere waarden teruggeeft: die lopen door naar de cellen eronder en ernaast. Lees welke Excel dit kan, hoe je met # naar het resultaat verwijst en wat #OVERLOPEN! betekent.
• FoutwaardenEen foutwaarde is een resultaat dat met # begint en aangeeft wat er misging, zoals #N/B of #DEEL/0!. Ze plant zich voort door formules, en wie ze verbergt, kan een gat in het controlebewijs verbergen.