Absolute en relatieve celverwijzing Excel

Geschreven door:
Jan Bolhuis, 18 november 2015

Als ik iedere keer een euro zou ontvangen wanneer ik een Excel gebruiker tegenkom die niet weet wat er bedoeld wordt met een absolute, relatieve of gemixte celverwijzing, dan zat ik nu redelijk in de slappe was.

Misschien denk jij nu ook bij jezelf: ‘Waar heeft hij het over’.

Wees dan niet ongerust en schaam je niet, want met jou zijn er velen die op dit moment hetzelfde denken.

Toch behoort deze kennis tot de basis. Zowel in theorie als in praktijk. En ik adviseer je nu al dat wanneer je niet of nauwelijks weet waar ik het over heb, dit artikel goed te bestuderen en vanaf morgen toe te passen.

En zoals zo vaak zal ik aan het einde van dit artikel weer een handige sneltoets met je delen waarmee je ontzettend snel kunt switchen tussen alle soorten celverwijzingen.

Download hier het oefenbestand

Tip: lees eerst het artikel helemaal door voordat je het oefenbestand opent. Helemaal onderaan dit artikel tref je namelijk het doel van dit bestand aan.

Wat is een celverwijzing?
Een celverwijzing is niets anders dan dat je in een formule verwijst naar een andere cel.

Stel dat je in cel A3 de prijs van een artikel hebt en in cel B3 de verkochte aantallen. Dan kun je de omzet berekenen door gebruik te maken van de formule:

= A3 * B3

Het gebruik van cel A3 en B3 in de formule noemt men dan een celverwijzing.

Vier soorten celverwijzingen
De zojuist genoemde celverwijzing kent vier verschillende soorten, namelijk:

1. Een relatieve celverwijzing;
2. Een absolute celverwijzing;
3. Een gemixte celverwijzing (kolom relatief / rij absoluut) en
4. Een gemixte celverwijzing (kolom absoluut / rij relatief).

Omdat formules vaak doorgevoerd worden (van boven naar beneden of van links naar rechts), is het van belang dat je een goed begrip hebt van alle vier celverwijzingen en weet wanneer je welke moet toepassen.

Dit bespaart niet alleen tijd en neemt jouw frustraties weg maar veel belangrijker nog, het voorkomt fouten in jouw spreadsheet of rekenmodel. Fouten die van cruciaal belang kunnen zijn wanneer er beslissingen worden genomen op jouw Excelmodel.

Een relatieve celverwijzing
Notatie: A3

Een relatieve celverwijzing is niets anders dan dat de cel waarnaar je verwijst, meebeweegt in de richting van hoe je de formule doorvoert (doortrekt).

Voer je de formule = A3 * B3 door van cel C3tot en met cel C6, dan zal de formule in cel D3 er als volgt uit zien: = A4 * B4. De rij ‘beweegt’ dus mee.


Een relatieve celverwijzing van boven naar beneden

Wanneer je de formule doortrekt van links naar rechts, dan zal de formule = G2 * G3 veranderen in = H2 * H3. Met andere woorden. In zo’n situatie ‘beweegt’ de kolom mee.


Een relatieve celverwijzing van links naar rechts

Absolute celverwijzing
Notatie: $A$3

Wanneer je een absolute celverwijzing toepast, houdt dit in dat er altijd naar die ene cel wordt verwezen. Het maakt niet uit hoe je de formule doorvoert. Of dat nu van boven naar beneden is of van links naar rechts. Altijd zal er worden gekeken in die éne cel die je hebt vastgezet . Je blokkeert als het ware de cel wanneer je een celverwijzing absoluut maakt.

Een absolute celverwijzing kun je altijd herkennen aan de dollartekens.

We zullen het voorbeeld met de relatieve celverwijzing uitbreiden door er een kolom aan toe te voegen waarin we de prijs exclusief 21% BTW gaan berekenen.

De formule om het gewenst resultaat (prijs exclusief 21% BTW) te bereiken kunnen we als volgt uitschrijven:

Prijs inclusief BTW gedeeld door ( 1 + 21% ) = prijs exclusief BTW.

Het herleiden van deze formule leidt tot de volgende formule: Prijs inclusief BTW / 1,21 = prijs exclusief BTW.

Omdat de prijs van het eerste product in cel C14 staat, ligt het voor de hand om te kiezen voor de formule = C14 / 1,21. Immers het BTW percentage ligt vast.

Maar wat nu wanneer dit percentage gaat veranderen? Je zult dan alle formules waarin je handmatig 1,21 hebt gebruikt, een voor een langs moeten gaan om het aan te passen, met daarbij een hele grote kans op fouten.

Daarom mag je ook nooit harde waarden in een formule gebruiken maar moet je altijd gebruik maken van celverwijzingen.

Opmerking:
Je mag in enkele gevallen gebruik maken van harde waarden, wanneer je zeker weet dat deze nooit zullen veranderen. Je moet hierbij denken aan 12 maanden per jaar, 24 uur per dag, 7 dagen per week etc.

Omdat we nu geen gebruik mogen maken van een harde waarde, gaan we gebruik maken van een extra cel. Namelijk cel B11. In deze cel voer je het getal 0,21 in (= 21%).

Vervolgens voer je in cel D14 de volgende formule in: = C14 / ( 1 + $B$11 ).

Wanneer je nu de formule naar beneden doorvoert (t/m cel D17), zal C14 mee veranderen naar D14omdat deze celverwijzing namelijk relatief is gebleven. Echter de verwijzing naar cel B11 zal niet veranderen, omdat zowel de kolom als de rij geblokkeerd is (let op de dollartekens).

In cel D15 ziet de formule er dan ook als volgt uit:

= C15 / ( 1 + $B$11 )


Een absolute celverwijzing naar B11

Moet ik die dollartekens telkens handmatig invoeren?


Sneltoets om een celverwijzing absoluut te maken

Nee! Want we willen snel en efficiënt werken en daarom gaan we gebruik maken van een sneltoets. Namelijk de functietoets F4.

De handelingen zijn dan als volgt:

Je typt het = teken gevolgd door een celverwijzing naar C14.
Daarna type je / ( 1 + gevolgd door een celverwijzing naar B11. Nu druk je direct één keer op de functietoets F4 waardoor Excel automatisch de celverwijzing absoluut maakt.
Mocht je in een situatie terecht komen, dat je een celverwijzing niet absoluut wilt maken maar je hebt toch de functietoets F4 gebruikt, dan druk je nog drie keer op F4 en de cel is weer relatief.

Dit moet je gewoon een paar keer oefenen. Doen dus!

Gemixte celverwijzing
Kolom relatief, rij absoluut
Notatie: A$3 (sneltoets: 2 keer functietoets F4)

In deze gemixte celverwijzing, blijft de kolom relatief en blokkeer je de rij (absoluut).

Met andere woorden: wanneer je de formule doortrekt van boven naar beneden, wordt er altijd verwezen naar cel . Immers de rij is geblokkeerd en je blijft in kolom A.


Een gemixte celverwijzing. Kolom relatief ; Cel Absoluut

Maar wanneer je de formule doortrekt van links naar rechts, dan zal de kolomletter wel veranderen. Namelijk in B$3, C$3, D$3 etc.

Kolom absoluut, rij relatief
Notatie: $A3 (sneltoets drie keer functietoets F4)

Wanneer je een formule door wilt voeren van links naar rechts en er moet steeds naar dezelfde cel in die rij gekeken worden, dan maak je de kolom absoluut en hou je de rij relatief.


Een gemixte celverwijzing met een absolute kolom en relatieve rij

Sneltoetsen samengevat

• 1 keer F4 Absolute celverwijzing ($A$3)
• 2 keer F4 Kolom relatief, rij absoluut (A$3)
• 3 keer F4 Kolom absoluut, rij relatief ($A3
• 4 keer F4 Relatieve celverwijzing – de beginsituatie (A3)

Conclusie
Wanneer je het verschil kent tussen de verschillende soorten celverwijzingen en het gebruik ervan goed toepast, zorg je ervoor dat je niet alleen veel minder kans op fouten in je spreadsheet hebt maar win je ook nog eens heel veel tijd, gooi je weer een frustratie over boord en heb je een grote voorsprong op je collega’s.

Extra oefening
Om de gemixte celverwijzingen te oefenen, kun je gebruik maken van het oefenbestand dat je hier kunt downloaden. Het oefenbestand bestaat uit vier bladen.

1. Blog: op dit blad tref je de gebruikte voorbeelden uit dit blogartikel aan
2. Opgave: op dit blad tref je de opgave aan. Ik adviseer je er goed over na te denken voordat je naar het antwoord gaat kijken.
3. Antwoord: op dit blad tref je de uitwerking aan.
4. DrieHandelingen: op dit blad wordt uigelegd hoe je met drie handelingen tot het resultaat komt zoals je in het blad met antwoord terug kunt vinden.

Een oefening om gemixte celverwijzingen te oefenen

Natuurlijk ben ik benieuwd of dit artikel je heeft geholpen en of je dit ook gaat toepassen. Of misschien al toepaste. Laat daarom gerust een reactie achter.

Wilt u nog meer blogberichten lezen? Ga dan naar de pagina Blog/kennisbank.