Absolute en relatieve verwijzingen maken en gebruiken

Je komt in de helpfiles van Excel en op diverse ondersteunende websites regelmatig de termen ‘absoluut’ en ‘relatief’ tegen. En je ziet in formules ook vaak (al dan niet spontaan) die dollartekens bij een kolom of cel staan (bijvoorbeeld $A$1 waar je toch echt cel A1 bedoelde).

Wat is dit en hoe gebruik je dit goed?

De absolute verwijzing is die met die dollartekens erin.
Als ik naar $A$1 verwijs in een formule en vervolgens die formule naar beneden kopieer, dan blijft die verwijzing naar A1 ($A$1 = Absoluut) in stand. In alle cellen waar de formule staat zal de berekening via die A1 verlopen.

De relatieve verwijzing is die zonder dollartekens.
Als ik naar A1 verwijs in een formule en vervolgens die formule naar beneden kopieer, dan zal de formule worden aangepast. Op het moment dat ik de formule telkens 1 rij naar beneden kopieer zal de verwijzing naar cel A2, A3, A4 etc. verschuiven. De berekeningen in de cellen zullen dus niet met de data in A1 werken, maar met die respectievelijk in A2, A3, A4, etc.

De dollartekens zijn dus gewoon een door Excel herkende schrijfwijze in een formule. Niets meer, niets minder.

Je gebruikt deze kennis vooral bij het maken van je spreadsheetmodellen. Kennis van hoe dit werkt, helpt je om formules te maken en vervolgens makkelijk door te kopiëren. Dat scheelt enorm veel invoerwerk, vooral bij wat complexere of grotere modellen.

Daarnaast kan je met deze kennis een Excelbestand dat je krijgt makkelijker doorgronden en controleren. Immers de formule =A1+A2+$B$1 is nu ‘leesbaar’: je begrijpt dat bij optellingen in dit geval altijd hetgeen in cel B1 staat ook opgeteld wordt.

In bijgaand voorbeeld staat in cel G3 het BTW-percentage waarmee gerekend moet worden op alle regels. Vanaf regel 5 (met de koppen) in de kolommen A, B en C vind je een product, een aantal en een prijs. In kolom D moet de BTW worden berekend en in kolom E de totale prijs inclusief BTW.

De formule die het meest voor de hand ligt om in D6 te gebruiken is: =B6*C6*G3 (aantal maal prijs maal BTW percentage)
De formule in E6 komt te staan is dan: =B6*C6+D6 (aantal maal prijs plus BTW bedrag) (je kan ook neerzetten =(B6*C6)+D6 als je dat duidelijker vindt, het maakt voor Excel geen verschil) (zie afbeelding)

Resultaten zullen juist zijn. Geen probleem, totdat……….

Als je de formule naar beneden kopieert (bijvoorbeeld door in D7 te gaan staan en sneltoets Ctrl+D te gebruiken (zie hier voor meer informatie over deze sneltoets), dan zie je ineens een 0,00 als bedrag aangegeven (zie afbeelding 2). Dit kan niet juist zijn?! Wat is hier gebeurd?

Alle celverwijzingen in de formule die we in D6 gebruikt hebben waren relatief (geen dollartekens). Dit betekent dat als je de formule 1 rij naar beneden kopieert, de rijen in de formule ook met 1 worden aangepast (verhoogd). In ons voorbeeld is dat in twee van de verwijzingen ook precies de bedoeling! We willen nu namelijk de aantallen maal prijs van regel 7 berekenen, dus het eerste deel van de formule (was: =B6*C6 en wordt: =B7*C7) is prima.
Het probleem zit ‘m in de verwijzing naar het BTW percentage dat in cel G3 staat. Ook die informatie is met 1 verhoogd waardoor er nu verwezen wordt naar cel G4. En omdat er geen inhoud in G4 staat wordt er met nul vermenigvuldigd en is de uitkomst dus nul!

OPLOSSING

Als we de verwijzing naar cel G3 in onze eerste formule absoluut maken ($G$3), dan zal dit blijven staan, ongeacht waar de formule heen gekopieerd wordt. En dat is precies wat we in dit geval willen.
(NB: er is overigens een heel handig trucje om die dollartekens in zo’n formule te krijgen: klik in de formulebalk bij die G of 3 en toets functietoets F4 in. 1x F4 toetsen geeft $G$3, nog een keer F4 toetsen geeft G$3, een derde keer toetsen $G3 en een vierde keer G3 en zo van voren af aan. Maar je mag ook de dollartekens typen hoor).

Het kopiëren van deze formule naar D7 levert op: =B7*C7*$G$3, precies wat we nodig hebben en er komt nu dan ook de juiste uitkomst in cel D7 te staan.

De formule kan nu gewoon naar beneden gekopieerd worden tot en met rij 12 in dit voorbeeld

Met enige kennis en vooraf nadenken kunnen we dus met 1 regel formules (in twee cellen) en wat kopiëren en plakken direct onze hele tabel vullen en later aanvullen.