---

---

Absolute en relatieve verwijzingen in Excel: dat blijft altijd even opletten!
Maar sinds we met zijn allen steeds meer gebruik maken van Excel-tabellen komt daar ineens een struikelblok(je) bij.

In dit artikel kijken we eerst in algemene zin naar de implementatie van tabel-verwijzingen (Gestructureerde verwijzingen of Structured references in Microsoft-taal); daarna komt een methode langs hoe we ook tabel-verwijzingen absoluut kunnen maken.

Voorbeeld-bestand

In het Voorbeeldbestand staat in het tabblad CelRef een overzicht van bedragen per kwartaal en regio.

Er loopt blijkbaar een afspraak dat, als het bedrag groter is dan 100, er dan een korting van 10% wordt gegeven.
De daarbij gehanteerde formule is:
=ALS(D3>100;10%;0)

Ofwel (geredeneerd vanuit cel E3) als de waarde in cel D3 groter is dan 100, dan komt in E3 de waarde 0,1 anders 0.

NB 10% is voor Excel slechts een opmaak van een numerieke waarde; intern wordt gerekend met 0,1.

De formule is F3 (het bedrag inclusief korting) kan dan zijn:
=D3*(1-E3)

Allebei de formules kunnen naar beneden gekopieerd worden; dat gaat het makkelijkst door dubbel te klikken op het vierkantje rechtsonder aan de rand van een cel (de vulgreep) als die cel is geselecteerd.

Aangezien alle cel-verwijzingen relatief zijn (er staan nergens $-tekens bij de cellen D3 en E3) zullen de formules zich automatisch aanpassen: D3 wordt D4 etc.

LET OP om kolom F niet te breed te maken is de tekst in cel F2 gescheiden door een harde return (Alt-Enter). Een kop nooit over 2 cellen verdelen; altijd een harde return gebruiken.

Willen we weten hoe de verdeling over de kwartalen en/of de regio’s is, dan ligt het maken van een draaitabel voor de hand.

LET OP een bug in Excel kan er bij het aanpassen van de opmaak van de data voor zorgen dat de kop die met een harde return is gescheiden, inhoudelijk wijzigt (alles vanaf de harde return wordt verwijderd); hier is dan een handmatige aanpassing nodig.

Tabel-verwijzingen

Wanneer we dezelfde gegevens in een Excel-tabel opnemen (zie het tabblad TablRef van het Voorbeeldbestand) dan gaat Excel bij het maken van de kortingsformules automatisch met gestructureerde verwijzingen werken in plaats van celverwijzingen:

1. selecteer cel E3
2. tik in =als(
3. klik met de muis in cel D3; Excel plaatst automatisch de tekst [@Bedrag] in de formule
4. maak de formule af en druk op Enter
5. Excel zal automatisch de formule naar de overige cellen van dezelfde kolom kopiëren.

Gelukkig hoeven we de gestructureerde verwijzingen dus niet zelf te typen; de notatie daarvan is niet altijd even eenvoudig.
De vierkante haken om de diverse onderdelen van een formule maken voor Excel het verschil tussen gestructureerde en cel-verwijzingen.
Het ‘apestaartje’ (@) voor een kolomnaam betekent dat de waarde uit dezelfde rij uit de betreffende kolom wordt opgehaald.

LET OP in cel F3 staat de formule =[@Bedrag]*(1-[@[Korting%]]); aangezien het %-teken in Excel diverse betekenissen kan hebben zijn nog eens extra vierkante haken om de kolomnaam geplaatst.
Andere speciale tekens die eenzelfde behandeling nodig hebben:
tab, nieuwe regel, Enter-teken, komma (,), dubbele punt (:), punt (.), vierkante haak openen ([), vierkante haak sluiten (]), hekje (#), enkel aanhalingsteken (‘), dubbel aanhalingsteken (“), accolade openen ({), accolade sluiten (}), dollarteken ($), accent circonflexe (^), en-teken (&), sterretje (*), plusteken (+), gelijkteken (=), minteken (-), groter dan (>), kleiner dan (<) en het deelteken (/).

Totalen

Aan een tabel kunnen snel totalen toegevoegd worden:

1. klik op een cel van de tabel
2. kies in de menutab Hulpmiddelen voor tabellen de optie Ontwerpen
3. en vink de optie Totaalrij aan
4. in de nieuwe regel onderaan de tabel kan dan per kolom het soort totalen gekozen worden (Som, Aantal, Gemiddelde etc.)

Een draaitabel die gebaseerd is op een tabel met totalen, zal deze totalen buiten beschouwing laten (gelukkig!); zie tabblad TablRefTot van het Voorbeeldbestand.

Externe verwijzing naar een tabel

Wanneer u een bepaald overzicht wilt genereren en geen gebruik wilt/kunt maken van draaitabellen dan kunt u via externe verwijzingen gebruik maken van de tabel-gegevens:

1. selecteer cel J21 in het tabblad TablRefTot
2. tik in =som.als(
3. wijs met de muis de bovenkant van de kolom Regio van de tabel daarnaast aan (de cursor wordt dan een pijltje naar beneden) en klik; Excel plaats automatisch de tekst tblKwReg2[Regio] in de formule
4. tik in ;J$20; en voeg op dezelfde manier de kolom Bedrag toe

NB omdat het verwijzingen buiten de tabel zijn plaatst Excel automatisch de naam van de betreffende tabel voor de rechte haken.

Door de formule nu naar rechts te kopiëren krijgen we ook het totaal-bedrag van regio Zuid te zien. Tenminste: als we op de juiste manier kopiëren!
Selecteer cel J21, klik op Ctrl-C, selecteer cel K21 en klik Ctrl-V en alles gaat goed.
Maar wanneer de vulgreep rechtsonder cel J21 wordt gebruikt, gaat het mis! Dan wordt de verwijzing naar Regio ineens Bedrag en Bedrag wordt Korting%!
DUS: met kopiëren/plakken worden de externe verwijzingen als absoluut beschouwd, maar gebruiken we de vulgreep dan ziet Excel de verwijzingen als relatief!

Ditzelfde effect zien we ook als we verwijzingen naar de tabellen-koppen maken (niet de bovenkant van een kolom aanklikken, maar de cel zelf) en die kopiëren (zie cellen I34 tot en met K35 van het tabblad TablRefTot van het Voorbeeldbestand).

NB wanneer gegevens aan de tabel worden toegevoegd (selecteer cel F32 en druk op Tab; de totalen schuiven automatisch naar beneden) zullen alle overzichten automatisch de nieuwe gegevens meenemen (wel nog via rechtsklikken de draaitabel Vernieuwen).
Dit geldt ook voor formules die naar de totaalrijen verwijzen.

Absoluut en relatief in gestructureerde verwijzingen

In het tabblad TablRefTot2 van het Voorbeeldbestand is een resultaat-overzicht per regio en kwartaal opgenomen. Dit overzicht is zelf ook weer een tabel (met de naam tblResult). Om dit te realiseren wordt de functie SOMMEN.ALS gebruikt.

LET OP de syntax is echt anders dan van SOM.ALS.

De formule in cel J26 berekent de som van die bedragen uit de kolom Bedrag van de tabel tblKwReg2b, waarbij de Regio-kolom van die tabel de koptekst van de kolom Noord van tblResult bevat en waarbij de waarde in de Kwartaal-kolom van de tabel tblKwRegTot2b dezelfde is als die in de Kwartaal-kolom in dezelfde tabel en rij waar deze formule staat.

De formule in J26 kopiëren naar J27 levert geen problemen op welke methode ook gehanteerd wordt (kopiëren/plakken of de vulgreep).

Maar: in het voorbeeld is J26 naar K26 gekopieerd met behulp van de vulgreep; de verwijzingen worden dan als relatief beschouwd. Dat klopt voor de regio-kop in de resultaat-tabel (die moet Zuid worden) maar niet voor de verwijzingen naar de bron-tabel tblKwRegTot2.
Cel J27 is via kopiëren/plakken naar K27 gekopieerd. De verwijzingen blijven absoluut, maar dat klopt dan niet voor de regio-kop!

Oplossing: verwijzingen die absoluut moeten blijven moeten dubbel in de formule worden opgenomen, gescheiden door een dubbele punt (:).
De formule die hier nodig is wordt dan (zie cel J31):
=SOMMEN.ALS(tblKwReg2b[[Bedrag]:[Bedrag]];
tblKwReg2b[[Regio]:[Regio]]; tblResult2[[#Kopteksten];[Noord]];
tblKwReg2b[[Kwartaal]:[Kwartaal]]; tblResult2[@[Kwartaal]:[Kwartaal]])

Wanneer deze formule via de vulgreep naar rechts en naar beneden wordt gekopieerd, krijgen we in alle cellen de juiste resultaten!

NB1 alle kolomnamen hebben vierkante haken, maar de dubbelen krijgen daar omheen nog een extra set rechte haken. Let ook op de notatie bij de @.

NB2 ook de laatste parameter (Kwartaal) moet in dit geval een tabel-aanduiding krijgen, ook al is de verwijzing binnen de tabel zelf.

---