Een werkblad in Excel bestaat uit cellen. Er kan naar deze cellen worden verwezen door de rijwaarde en de kolomwaarde op te geven.
A1 zou bijvoorbeeld verwijzen naar de eerste rij (aangegeven als 1) en de eerste kolom (aangegeven als A). Evenzo zou B3 de derde rij en tweede kolom zijn.
De kracht van Excel ligt in het feit dat je deze celverwijzingen in andere cellen kunt gebruiken bij het maken van formules.
Er zijn nu drie soorten celverwijzingen die u in Excel kunt gebruiken:
- Relatieve celverwijzingen
- Absolute celverwijzingen
- Gemengde celverwijzingen
Als u deze verschillende soorten celverwijzingen begrijpt, kunt u met formules werken en tijd besparen (vooral bij het kopiëren en plakken van formules).
Wat zijn relatieve celverwijzingen in Excel?
Laat me een eenvoudig voorbeeld nemen om het concept van relatieve celverwijzingen in Excel uit te leggen.
Stel dat ik een dataset heb die hieronder wordt weergegeven:
Om het totaal voor elk artikel te berekenen, moeten we de prijs van elk artikel vermenigvuldigen met de hoeveelheid van dat artikel.
Voor het eerste item zou de formule in cel D2 B2* C2 zijn (zoals hieronder weergegeven):
In plaats van de formule voor alle cellen één voor één in te voeren, kunt u eenvoudig cel D2 kopiëren en in alle andere cellen plakken (D3:D8). Wanneer u dit doet, zult u merken dat de celverwijzing zich automatisch aanpast om naar de overeenkomstige rij te verwijzen. De formule in cel D3 wordt bijvoorbeeld B3*C3 en de formule in D4 wordt B4*C4.
Deze celverwijzingen die zichzelf aanpassen wanneer de cel wordt gekopieerd, worden genoemd relatieve celverwijzingen in Excel.
Wanneer relatieve celverwijzingen in Excel gebruiken?
Relatieve celverwijzingen zijn handig wanneer u een formule voor een celbereik moet maken en de formule moet verwijzen naar een relatieve celverwijzing.
In dergelijke gevallen kunt u de formule voor één cel maken en deze in alle cellen kopiëren en plakken.
Wat zijn absolute celverwijzingen in Excel?
In tegenstelling tot relatieve celverwijzingen, veranderen absolute celverwijzingen niet wanneer u de formule naar andere cellen kopieert.
Stel dat u de onderstaande gegevensset hebt, waarbij u de commissie voor de totale verkoop van elk artikel moet berekenen.
De commissie is 20% en wordt vermeld in cel G1.
Om het commissiebedrag voor elke artikelverkoop te krijgen, gebruikt u de volgende formule in cel E2 en kopieert u deze voor alle cellen:
=D2*$G$1
Merk op dat er twee dollartekens ($) in de celverwijzing staan met de commissie - $G$2.
Wat doet het dollarteken ($)?
Een dollarteken, wanneer toegevoegd voor het rij- en kolomnummer, maakt het absoluut (d.w.z. voorkomt dat het rij- en kolomnummer verandert wanneer het naar andere cellen wordt gekopieerd).
Als ik in het bovenstaande geval bijvoorbeeld de formule kopieer van cel E2 naar E3, verandert deze van =D2*$G$1 in =D3*$G$1.
Merk op dat terwijl D2 verandert in D3, $G$1 niet verandert.
Omdat we een dollarteken hebben toegevoegd voor 'G' en '1' in G1, zou de celverwijzing niet veranderen wanneer deze wordt gekopieerd.
Dit maakt de celverwijzing dus absoluut.
Wanneer gebruik ik absolute celverwijzingen in Excel?
Absolute celverwijzingen zijn handig als u niet wilt dat de celverwijzing verandert terwijl u formules kopieert. Dit kan het geval zijn wanneer u een vaste waarde heeft die u in de formule moet gebruiken (zoals belastingtarief, commissietarief, aantal maanden, etc.)
Hoewel u deze waarde ook hard kunt coderen in de formule (d.w.z. gebruik 20% in plaats van $ G $ 2), als u deze in een cel plaatst en vervolgens de celverwijzing gebruikt, kunt u deze op een toekomstige datum wijzigen.
Als uw commissiestructuur bijvoorbeeld verandert en u nu 25% in plaats van 20% uitbetaalt, kunt u eenvoudig de waarde in cel G2 wijzigen en worden alle formules automatisch bijgewerkt.
Wat zijn gemengde celverwijzingen in Excel?
Gemengde celverwijzingen zijn wat lastiger dan de absolute en relatieve celverwijzingen.
Er kunnen twee soorten gemengde celverwijzingen zijn:
- De rij is vergrendeld terwijl de kolom verandert wanneer de formule wordt gekopieerd.
- De kolom is vergrendeld terwijl de rij verandert wanneer de formule wordt gekopieerd.
Laten we eens kijken hoe het werkt aan de hand van een voorbeeld.
Hieronder vindt u een gegevensset waarin u de drie commissieniveaus moet berekenen op basis van de procentuele waarde in cel E2, F2 en G2.
Nu kunt u de kracht van gemengde referentie gebruiken om al deze commissies te berekenen met slechts één formule.
Voer de onderstaande formule in cel E4 in en kopieer deze voor alle cellen.
=$B4*$C4*E$2
De bovenstaande formule gebruikt beide soorten gemengde celverwijzingen (een waarbij de rij is vergrendeld en een waarbij de kolom is vergrendeld).
Laten we elke celverwijzing analyseren en begrijpen hoe het werkt:
- $B4 (en $C4) - In deze referentie staat het dollarteken vlak voor de kolomnotatie, maar niet voor het rijnummer. Dit betekent dat wanneer u de formule naar de cellen aan de rechterkant kopieert, de verwijzing hetzelfde blijft als de kolom vast staat. Als u bijvoorbeeld de formule van E4 naar F4 kopieert, verandert deze verwijzing niet. Wanneer u het echter naar beneden kopieert, verandert het rijnummer omdat het niet is vergrendeld.
- € 2 - In deze referentie staat het dollarteken vlak voor het rijnummer en heeft de kolomnotatie geen dollarteken. Dit betekent dat wanneer u de formule naar beneden in de cellen kopieert, de verwijzing niet verandert omdat het rijnummer is vergrendeld. Als u de formule echter naar rechts kopieert, verandert het kolomalfabet omdat het niet is vergrendeld.
Hoe de referentie wijzigen van relatief naar absoluut (of gemengd)?
Om de verwijzing van relatief naar absoluut te veranderen, moet u het dollarteken vóór de kolomnotatie en het rijnummer toevoegen.
A1 is bijvoorbeeld een relatieve celverwijzing en wordt absoluut als u er $A$1 van maakt.
Als u slechts een paar referenties hoeft te wijzigen, vindt u het misschien gemakkelijk om deze referenties handmatig te wijzigen. U kunt dus naar de formulebalk gaan en de formule bewerken (of de cel selecteren, op F2 drukken en deze vervolgens wijzigen).
Een snellere manier om dit te doen is echter door de sneltoets F4 te gebruiken.
Wanneer u een celverwijzing selecteert (in de formulebalk of in de cel in de bewerkingsmodus) en op F4 drukt, wordt de verwijzing gewijzigd.
Stel dat je de verwijzing =A1 in een cel hebt.
Dit is wat er gebeurt als u de referentie selecteert en op de F4-toets drukt.
- Druk eenmaal op de F4-toets: De celverwijzing verandert van A1 in $A$1 (wordt 'absoluut' van 'relatief').
- Druk twee keer op de F4-toets: De celverwijzing verandert van A1 in A$1 (verandert in gemengde verwijzing waar de rij is vergrendeld).
- Druk drie keer op de F4-toets: De celverwijzing verandert van A1 in $A1 (verandert in gemengde verwijzing waar de kolom is vergrendeld).
- Druk vier keer op de F4-toets: De celverwijzing wordt weer A1.
