Celverwijzingen in formules

1. Leerdoel

De student moet de relatieve en absolute wijze waarop in formules naar cellen kan worden verwezen kennen en kunnen gebruiken.

2. Werkwijze

In MS Excel kan in formules op twee verschillende wijzen naar een cel worden verwezen: een relatieve verwijzing of een absolute verwijzing.

2.1. Relatieve celverwijzing

Relatieve verwijzingen worden het meest gebruikt.  Het is overigens de standaardwijze waarop MS Excel automatisch naar een cel verwijst, zoals in de voorgaande webpagina's aangaande het invoeren van formules werd uitgelegd.  Je hoeft dus om een relatieve celverwijzing te maken niets speciaals te ondernemen.

Bij relatieve verwijzingen hangen de verwijzingen af van de rij- en kolomposities in het werkblad.  Wanneer een formule met een relatieve celverwijzing wordt gekopieerd naar een andere locatie, dan worden de verwijzingen automatisch aan de nieuwe locatie aangepast.  Een voorbeeld ter verduidelijking:

1. Open een willekeurig werkblad
   

2. Tik in cel A1 de waarde 1000
   
   
3. Tik in cel A2 de waarde 200
   
   
4. Klik in cel A3 om deze te selecteren, ten einde de formule in deze cel in te voeren
   
   
5. Tik in de formulebalk     =A1-A2
   
   
6. Klik op de knop 'Invoeren' om te beëindigen
   
   
7. Het resultaat van deze aftrekking verschijnt in cel A3

8. Tik vervolgens  in cel B1 het getal 5000
   

9. Tik in B2 het getal 2000
   
   
10. Stel dat we deze beide getallen eveneens van elkaar willen aftrekken, en daartoe de formule in A3 naar cel B3 willen kopiëren, ten einde onszelf enig tikwerk te besparen.
    
    
11. Klik daartoe, zoals eerder uitgelegd, op cel A3 en sleep de vulgreep naar B3

12. Het resultaat van de aftrekking van de getallen in B1 en B2 (3000) verschijnt in cel B3 

13. Klik op de cel B3, waardoor in de formulebalk de formule '=B1-B2' verschijnt.

14. Excel heeft in dit geval automatisch tijdens het kopiëren van cel A3 naar B3, de oorspronkelijke celverwijzingen naar A1 en A2, gewijzigd in B1 en B2.  Omwille van deze automatische aanpassing, worden dit relatieve celverwijzingen genoemd.

2.2. Absolute celverwijzing

Een absolute celverwijzingen heb je nodig wanneer je een formule naar een specifieke cel wil laten verwijzen, ongeacht naar welke cel de formule gekopieerd wordt.  Een voorbeeld ter verduidelijking.

1. Tik in cel A1 de koers van de Euro: 40,3399 en geef dit cijfer vet en rood weer
   

2. Tik in cel A2 de waarde in oude Belgische franken: 1000
   
   
3. Tik in cel A3 de formule die de waarde in A2 omzet in Euro: =A2/A1

4. Klik vervolgens in cel B2 en bemerk dat cel A3 thans de uitkomst van de formule toont: 1000 oude Belgische franken is gelijk aan 24,78935 Euro.

5. Tik de waarde 500 in cel B2.
     

6. Wanneer we vervolgens in een andere cel dezelfde omzettingsformule willen gebruiken zonder eerst de koers van de Euro opnieuw in te vullen (en dus door gebruik te maken van de koers van de Euro in cel A1), dan zou thans het kopiëren van de formule (zoals in voorgaande paragraaf) niet het gewenste resultaat opleveren. 

Het kopiëren van de formule met relatieve celverwijzingen zou namelijk na het kopiëren leiden tot de foutieve formule "=B2/B1". Deze formule is fout omdat - aangezien B1 geen waarde bevat - een deling door 0 (nul) wordt uitgevoerd.

7. Je kan een relatieve celverwijzing daartoe absoluut maken door de celverwijzing te laten voorafgaan door een dollarteken: $.  Om dus de waarde in cel B2 om te zetten in Euro, gebruikmakend van de koers van de Euro in cel A1, door de formule in A3 te kopiëren naar B3, moet de formule in A3 als volgt worden aangepast:' =A2/$A$1'.
   

8. Klik daartoe in de cel A3 en wijzig in de formulebalk de formule =A2/A1 in =A2/$A$1 en druk op de Enter-toets of de knop 'Invoeren'

9. Kopieer thans de aangepaste formule van A3 naar B3 met behulp van de vulgreep van cel A3

10. en stel vast dat de formule in B3 thans inderdaad '=B2/$A$1' is, dankzij de absolute verwijzing naar cel A1.

2.3. Van relatief naar absoluut

Het omzetten van relatieve verwijzing in een absolute celverwijzing kan gebeuren, zoals hoger beschreven door een dollarteken in te tikken.  Het is echter tevens mogelijk om eerst de celverwijzing als een relatieve celverwijzing in te tikken in de formule (bijvoorbeeld: 'A1') en vervolgens op de F4-toets te drukken: de relatieve verwijzing wordt daardoor automatisch omgezet in een absolute '$A$1'.

1. Tik in cel A6 de formule =A2/A1
   
   
2. Selecteer in de formulebalk of in de cel het celadres A1 door er over te slepen met ingedrukte linkermuisknop.

3. Druk één maal op de functietoets F4 op de bovenste rij van je toetsenbord.

4. Bemerk dat vóór de verwijzing naar de kolom ('A') een dollarteken wordt geplaatst, evenals vóór de verwijzing naar de rij ('1').

Noot: Wanneer je nogmaals op de F4-toets drukt wordt de rijverwijzing absoluut gemaakt, terwijl een derde druk F4-toets enkel de kolomverwijzing absoluut maakt.  In bepaalde gevallen kan het nuttig zijn enkel de kolom- of rijverwijzing absoluut te maken.

2.4. Verwijzing naar een cel op een ander werkblad

Om een relatieve of absolute verwijzing naar een cel op een ander werkblad in de werkmap te maken, kan je de celverwijzing laten voorafgaan door naam van het werkblad.  In onderstaand voorbeeld is de naam van het werkblad ‘Blad1’.

3. Oefeningen

3.1. Oefening 'Relatieve celverwijzing'

Klik hier om een oefening te maken aangaande absolute en relatieve celverwijzingen in formules

3.2. Oefening 'Celverwijzing op ander werkblad'

Klik hier om een oefening te maken aangaande een celverwijzing in een ander werkblad.

4. Bronnen

Helpprogramma bij Microsoft Excel 2016: zoek op 'overzicht van formules'.