Deze instructie behandelt het onderwerp absolute en relatieve cel verwijzingen in Excel. Wanneer je dit onder de knie hebt ben je in staat om het aantal handmatige handelingen sterk te reduceren. Daarnaast verklein je de kans op fouten drastisch. Essentiële kennis bij het beheersen van Excel.
Wat is een cel verwijzing?
Een cel verwijzing is een verwijzing van een cel naar een (of meerdere) andere cel. Cel verwijzingen worden zeer veel toegepast. Zeker naarmate Excel modellen groter worden.
Voorbeeld 1:
In dit voorbeeld heb ik in een Excel werkblad informatie opgenomen over het aantal verkochte stuks fruit in een periode. Een cel verwijzing is heel eenvoudig te maken.
Ga in een lege cel staan. Ik kies voor cel A6. Vul daar in: = en selecteer vervolgens de cel waarnaar je wilt verwijzen. In dit geval selecteer ik met de muis cel A1 en druk op ‘enter’. In veld A6 verschijnt vervolgens de waarde die in cel A1 staat (Productcategorie). U heeft uw eerste cel verwijzing gemaakt.
Voorbeeld 2:
We doen nog een oefening. Voeg in cel D1 het kopje “Omzet” toe. Nu gaan we de omzet van de verkochte appels berekenen. In veld D2 maken we hiervoor 2 cel verwijzingen, één naar cel B2 en één naar cel C2. Vul in cel D2 de volgende formule in: =B2*C2 en druk op ‘enter’
Door het vermenigvuldigen van de twee cellen waar ik naar heb verwezen verschijnt de omzet (14) in cel D2.
Wat is een relatieve cel verwijzing?
De cel verwijzingen uit het vorige voorbeeld zijn allen relatief. Wanneer de formule gekopieerd wordt naar een andere cel zal de verwijzing relatief mee bewegen. Het volgende voorbeeld maakt veel duidelijk.
Voorbeeld 3:
We hebben in het vorige voorbeeld in cel D2 de omzet van appels berekend. Selecteer cel D2 met de muis. Rechtsonder in de cel zie je een klein vierkantje. Selecteer deze en trek de formule door naar beneden tot cel D4. Nu wordt automatisch de omzet van de verkopen van alle fruitsoorten berekend. Omdat het een relatieve verwijzing is schuift de formule steeds één regel naar beneden gelijk aan de verschuiving van het veld waarin het resultaat van de berekening komt.
Je hebt nu geleerd een relatieve cel verwijzing te maken
Wat is een absolute cel verwijzing?
Bij een absolute cel verwijzing varieert de verwijzing niet mee wanneer de cel met de verwijzing verschoven wordt. Een absolute cel verwijzing wordt gemaakt door in de cel waarnaar verwezen wordt dollartekens te gebruiken ($).
TIP! De dollartekens kunnen handmatig ingevuld worden maar ik adviseer om altijd de functie toets F4 te gebruiken. Druk de F4 toets in zodra de cel is ingevoerd. Je ziet dat de dollartekens automatisch toegevoed worden.
Voorbeeld 4:
Om dit te illustreren gebruiken we het bestaande voorbeeld. Ik veronderstel dat de berekende omzet exclusief BTW is. We gaan nu voor iedere regel het BTW bedrag berekenen. In cel B6 heb ik het BTW percentage van 21% ingevuld.
Ieder omzet bedrag moet (absoluut) naar cel B6 verwijzen. Hiervoor gebruiken we dollartekens ($). In cel F2 voeren we de volgende formule in: =D2*$B$6. Trek vervolgens deze cel naar beneden tot en met rij 4. Elke regel verwijst nu absoluut naar B6. Vergeet niet de dollartekens door middel van functie toets F4 toe te voegen.
Indien we de dollartekens ($) niet gebruikt hadden zou alleen de eerste regel naar B6 verwijzen. De overige rijen zouden altijd verwijzen naar een regel lager.
Gedeeltelijk absolute cel verwijzing
Nu je hebt geleerd om relatieve en absolute cel verwijzingen te maken is het ook mogelijk een combinatie van beiden toe te passen.
Gedeeltelijk absolute cel verwijzing door de rij absoluut te maken
Voorbeeld 5:
Ik illustreer dit aan de hand van een nieuw voorbeeld. In dit voorbeeld worden jaarlijks appels, peren en mandarijnen ingekocht. Ik veronderstel dat de winstmarge door slimmer inkopen ieder jaar iets hoger wordt.
We kunnen nu eenvoudig de winstmarge per fruitsoort bepalen door de rij waarin het % winstmarge staat absoluut te maken ($) en de kolom relatief te laten. Ik begin met de winst op appels in cel B9
In cel B9 vullen we de volgende formule in =B2*E$6
Trek de formule door naar beneden (vierkantje rechtsonder in de cel) en trek de formule naar rechts tot en met kolom F. De verwijzing naar de winstmarge rij (6) blijft gelijk maar de kolom varieert wel mee met het percentage behorende bij het betreffende jaar. Ongeacht de fruitsoort.
Gedeeltelijk absolute cel verwijzing door de kolom absoluut te maken
Andersom is het ook mogelijk om de kolom absoluut te maken.
Voorbeeld 6:
In dit voorbeeld ga ik ervan uit dat het winst percentage weliswaar jaarlijks gelijk blijft maar per fruitsoort varieert.
We zetten nu niet de rij vast maar de kolom H. We beginnen met het berekenen van de winstmarge in cel J2
In cel J2 vullen we nu de volgende formule in =B2*$H2
Trek vervolgens de formule naar beneden voor alle fruitsoorten en vervolgens. Trek vervolgens alles naar rechts tot en met 2017. Je zult zien dat de verwijzing naar de regels varieert maar te allen tijde absoluut verwezen blijft worden naar kolom H.
Je hebt nu alles geleerd over relatieve en absolute cel verwijzingen. Oefen hier mee om dit verder onder de knie te krijgen. Dit gaat je heel veel tijd besparen en het aantal fouten drastisch beperken.
Gebruik ten slotte altijd de F4 toets
- Eenmaal de F4 toets zorgt voor een volledig absolute cel (bijv. $A$1)
- Tweemaal de F4 toets zorgt voor een gedeeltelijk absolute cel verwijzing op rij (bijv. A$1)
- Driemaal de F4 toets zorgt voor een gedeeltelijk absolute cel verwijzing op kolom (bijv. $A1)
Heel veel succes met Excelleren
