6.5 Relatieve en absolute celverwijzingen
Bij het kopiëren/plakken van formules zal Excel automatisch de celverwijzingen aanpassen. Dit is al eerder in dit hoofdstuk uitgelegd maar we herhalen het hier nog iets uitgebreider en leggen uit wanneer dit niet werkt en wat je dan moet doen.
Gebruik het voorbeeldbestand H6-RelatiefAbsoluut om de uitleg te volgen.
6.5.1 Relatieve celverwijzingen
Bij het kopiëren en plakken van een formule zal Excel celverwijzingen aanpassen naar de locatie waar de formule staat. Dit worden ook wel relatieve celverwijzingen genoemd.
Voorbeeld
Als de formule in cel D4 (=B4*C4) naar beneden wordt gekopieerd (bijvoorbeeld met de vulgreep) worden de celverwijzingen automatisch aangepast. In de rij eronder in cel D5 staat dan =B5*C5.
- Kopieer je een formule naar beneden of boven dan worden de rijnummers aangepast.
- Kopieer je een formule naar rechts of links dan worden de kolomletters aangepast.
Je kunt de cellen waarnaar wordt verwezen in een cel waarin je formule staat visueel inzichtelijk maken met pijlen:
- Selecteer de cel met de formule.
- Klik in het tabblad Formules (Formulas) in de groep Formules controleren (Formula Auditing) op de knop [Broncellen aanwijzen] ([Trace Precedents]):
.
Het resultaat:
Je kunt ook kijken of een cel in een formule wordt gebruikt:
- Selecteer de cel.
- Klik in het tabblad Formules (Formulas) in de groep Formules controleren (Formula Auditing) op de knop [Doelcellen aanwijzen] ([Trace Dependents]):
.
Het resultaat:
Je verwijderd alle pijlen door in het tabblad Formules (Formulas) in de groep Formules controleren (Formula Auditing) op de knop [Pijlen verwijderen] ([Remove Arrows]) te klikken:
Via de lijstpijl heb je nog de keuze of je alleen de Broncel- of doelcelpijlen (Precedent of Dependent Arrows) wilt verwijderen.
| Opmerking |
|---|
| Voor het kopiëren van een formule mag je alle manieren van kopiëren en plakken gebruiken, zoals via het snelmenu, met de rechtermuisknop of de toetsencombinaties <Ctrl c> en <Ctrl v>. |
6.5.2 Absolute celverwijzingen
In sommige gevallen kun je een formule niet zomaar kopiëren en plakken. Dit is bijvoorbeeld het geval als je vanuit meerdere formules telkens naar eenzelfde cel verwijst. In onderstaande afbeelding (activeer het werkblad Absoluut) zie je hiervan een voorbeeld.
De formule in cel C6 is naar beneden gekopieerd maar dat gaat hier niet goed omdat de fomules in kolom C telkens naar eenzelfde cel (cel C3) verwijzen. Wat Excel doet is relatieve verwijzingen gebruiken. Bij het kopiëren van de formule naar beneden worden de rijnummers aangepast, dus de formules in kolom C zien er nu als volgt uit:
In de formules gaat het fout bij de celverwijzing naar het btw-percentage in cel C3. Deze mag hier niet worden aangepast. In plaats van een relatieve celverwijzing moet hier een absolute celverwijzing worden gebruikt.
Je kunt een celverwijzing op verschillende manieren absoluut maken.
Manier 1
- Selecteer cel C6
- Typ een =
- Klik op cel B6
- Typ een *.
Hiermee maak je het het begin van de formule: =B6*
- Klik op cel C3
- Druk nu op de <F4>-toets om de verwijzing naar cel C3 absoluut te maken.
- De formule ziet er nu zo uit:
=C6*$C$3.
| Tip |
|---|
|
Gebruik bij voorkeur de <F4>-toets om een celverwijzing absoluut te maken. Je mag de $-tekens ook zelf typen maar dan moeten deze wel op de juiste plek worden getypt. Heb je een laptop? Dan moet soms de <Fn>-toets gebruikt worden in combinatie met de <F4>-toets. |
- Kopieer de formule naar beneden.
De celverwijzingen in kolom C zien er nu zo uit:
In elke formule wordt nu telkens verwezen naar cel C3, de cel met het btw-percentage. Dit is een absolute celverwijzing.
Door in een of meerdere celverwijzingen in een formule op de <F4>-toets te drukken kun je de vier verschillende mogelijkheden van relatieve, absolute en gecombineerde celadresseringen weergeven.
| <F4>toets indrukken | Adressering | Voorbeeld |
|---|---|---|
| 1 maal | Kolom en rij absoluut (één cel) | $A$1 |
| 2 maal | Kolom relatief, rij absoluut (één rij) | A$1 |
| 3 maal | Kolom absoluut, rij relatief (één kolom) | $A1 |
| 4 maal | Kolom en rij relatief | A1 |
In deze cursus worden de celverwijzingen met één dollarteken niet behandeld.
Manier 2
Een andere manier om celverwijzingen absoluut te maken is door celnamen te gebruiken in de formule. Activeer hiervoor het werkblad Absoluut-naam).
In het volgende voorbeeld moeten de formules in C4:C10 allemaal verwijzen naar cel B1. Je kunt de verwijzing naar cel B1 absoluut te maken door deze cel een naam te geven voordat je de formule gaat invoeren.
- Selecteer de cel die je een naam wilt geven, in dit geval cel B1.
- Klik in het naamvak.
- Typ de naam: btw
- Druk op <Enter> om deze naam te bevestigen.
- Maak nu op de gebruikelijke manier de formules in cel C4.
- Het resultaat in cel C4:
- Kopieer de formule uit C4 naar de eronder gelegen cellen.
Alle gekopieerde formules verwijzen nu naar de cel btw (cel B1):
Als je deze manier gebruikt om een celverwijzing absoluut te maken bedenk dan dat je hier altijd twee $-tekens gebruikt: zowel de kolom- als de rijverwijzing is dus absoluut.