Om nauwkeurige berekeningen in Excel te maken, is het essentieel om te begrijpen hoe de verschillende soorten celverwijzingen werken.
A1 vs. R1C1-referenties
Excel-werkbladen bevatten veel cellen en (standaard) wordt elke cel geïdentificeerd door zijn kolom letter gevolgd door het rijnummer. Dit staat bekend als A1-stijlreferentie. Voorbeelden: A1, B4, C6
A1 referentiestijl
Optioneel kunt u overschakelen naar R1C1 Referentiemodus om naar de rij van een cel te verwijzen & kolom nummer. In plaats van te verwijzen naar cel A1 zou u verwijzen naar R1C1 (rij 1, kolom 1). Cel C4 zou worden verwezen naar R4C3.
R1C1 referentiestijl
Verwijzingen in R1C1-stijl zijn uiterst ongebruikelijk in Excel. Tenzij je een goede reden hebt, zou je je waarschijnlijk aan de standaard A1-stijl referentiemodus moeten houden. Als u echter VBA gebruikt, zult u deze referentiestijl waarschijnlijk tegenkomen.
Overschakelen naar R1C1-referentiestijl
Om de referentiestijl te wijzigen, gaat u naar Bestand > Optie > Formule. Vink het vakje aan naast R1C1 referentiestijl.
Benoemde bereiken
Een van de meest onderbenutte functies van Excel is de functie Named Ranges. In plaats van naar een cel (of groep cellen) te verwijzen op basis van de cellocatie (ex B3 of R3C2), kunt u dat bereik een naam geven en eenvoudig verwijzen naar de bereiknaam in uw formules.
Een bereik een naam geven:
- Selecteer de cel of cellen die u een naam wilt geven
- Klik in het vak Bereiknaam
- Vul je gewenste naam in
- Druk op Enter
Nu kunt u naar cel A1 verwijzen door in plaats daarvan =range_name1 te typen. Dit is erg handig bij het werken met grote werkmappen met meerdere werkbladen.
Bereik van cellen
Wanneer u de ingebouwde functies van Excel gebruikt, moet u mogelijk naar celbereiken verwijzen. Celbereiken zien er zo uit 'A1:B3'. Deze verwijzing verwijst naar alle cellen tussen A1 en B3: cellen A1,A2,A3, B1, B2, B3.
Een celbereik selecteren bij het invoeren van een formule:
- Typ het bereik (scheid het begin- en eindbereik met een puntkomma)
- Gebruik uw muis om op de eerste celverwijzing te klikken, houd de muisknop ingedrukt en sleep het gewenste bereik.
- Houd de shift-toets ingedrukt en navigeer met de pijltjestoetsen om uw bereik te selecteren
Absolute (bevroren) en relatieve referenties
Bij het invoeren van celverwijzingen binnen formules kunt u relatieve of absolute (bevroren) verwijzingen gebruiken. Relatieve verwijzingen worden proportioneel verplaatst wanneer u de formule naar een nieuwe cel kopieert. Absolute referenties blijven ongewijzigd. Laten we eens kijken naar enkele voorbeelden:
Relatieve referentie:
Een relatieve verwijzing in Excel ziet er als volgt uit:
=A1
Wanneer u een formule met relatieve verwijzingen kopieert en plakt, worden de relatieve verwijzingen proportioneel verplaatst. Tenzij u anders opgeeft, zijn uw celverwijzingen standaard relatief (niet bevroren).
Voorbeeld: Als u ‘=A1’ een rij naar beneden kopieert, verandert de verwijzing in ‘=A2’.
Absolute (bevroren) celverwijzingen
Als u niet wilt dat uw celverwijzingen worden verplaatst wanneer u een formule kopieert, kunt u uw celverwijzingen "bevriezen" door dollartekens ($ s) toe te voegen voor de verwijzing die u wilt vastzetten. Wanneer u nu de formule kopieert en plakt, blijft de celverwijzing ongewijzigd. U kunt ervoor kiezen om de rijverwijzing, kolomverwijzing of beide te bevriezen.
A1: Niets is bevroren
$A1: De kolom is bevroren, maar de rij is niet bevroren
A$1: De rij is bevroren, maar de kolom is niet bevroren
$A$1: zowel de rij als de kolom zijn bevroren
Absolute referentiesnelkoppeling
Het handmatig toevoegen van dollartekens ($s) aan uw formules is niet erg praktisch. Gebruik in plaats daarvan tijdens het maken van uw formule de F4-toets om te schakelen tussen absolute/relatieve celverwijzingen.
Absoluut celreferentievoorbeeld
Wanneer zou je een celverwijzing eigenlijk moeten bevriezen? Een veelvoorkomend voorbeeld is wanneer u invoercellen hebt waarnaar vaak wordt verwezen. In het onderstaande voorbeeld willen we de omzetbelasting berekenen voor elke hoeveelheid menu-items. De btw is constant voor alle artikelen, dus we zullen herhaaldelijk naar de cel voor btw verwijzen.
Om de totale omzetbelasting te vinden, voert u de formule '=(B3*C3)*$C$1' in kolom D in en kopieert u de formule naar beneden.
Gemengde referentie
Je hebt misschien gehoord van gemengde celverwijzingen. Een gemengde verwijzing is wanneer de rij- of kolomverwijzing is vergrendeld (maar niet beide).
Gemengde referentie
Onthoud dat u door de "F4" -toets te gebruiken door uw relatieve, absolute celverwijzingen kunt bladeren.
Celverwijzingen - Rijen/kolommen invoegen en verwijderen
U vraagt zich misschien af wat er met uw celverwijzingen gebeurt wanneer u rijen/kolommen invoegt of verwijdert?
De celverwijzing wordt automatisch bijgewerkt om naar de oorspronkelijke cel te verwijzen. Dit is het geval ongeacht of de celverwijzing is bevroren.
3D-referenties
Soms moet u mogelijk met meerdere werkbladen werken met identieke gegevenspatronen. Met Excel kunt u naar meerdere bladen tegelijk verwijzen zonder dat u elk werkblad handmatig hoeft in te voeren. U kunt verwijzen naar een bereik van bladen, vergelijkbaar met hoe u naar een bereik van cellen zou verwijzen. Voorbeeld 'Blad1:Blad5!A1' zou verwijzen naar cellen A1 op alle bladen van Blad1 tot Blad5.
Laten we een voorbeeld doornemen:
U wilt het totale aantal verkochte eenheden voor elk product in alle winkels bij elkaar optellen. Elke winkel heeft zijn eigen werkblad en alle werkbladen hebben een identiek formaat. U kunt een formule maken die lijkt op deze:
Dit is niet zo moeilijk met slechts vier werkbladen, maar wat als je 40 werkbladen had? Zou je echt elke celverwijzing handmatig willen toevoegen?
In plaats daarvan kunt u een 3D-verwijzing gebruiken om gemakkelijk naar meerdere bladen tegelijk te verwijzen (vergelijkbaar met hoe u naar een celbereik kunt verwijzen).
Doe voorzichtig! De volgorde van je werkbladen is belangrijk. Als u een ander blad tussen de bladen waarnaar wordt verwezen (StoreA en StoreD), verplaatst, wordt dat blad opgenomen. Omgekeerd, als u een blad buiten het bereik van de bladen verplaatst (vóór StoreA of na StoreD), wordt het niet langer opgenomen.
Circulaire celverwijzing
Een circulaire celverwijzing is wanneer een cel naar zichzelf verwijst. Als het resultaat van cel B1 bijvoorbeeld als invoer voor cel B1 wordt gebruikt, wordt er een kringverwijzing gemaakt. De cel hoeft niet direct naar zichzelf te verwijzen. Er kunnen tussenstappen zijn.
Voorbeeld:
In dit geval is de formule voor cel B2 "A2+A3+B2". Omdat u zich in cel B2 bevindt, mag u B2 niet gebruiken in de vergelijking. Dit zou een circulaire verwijzing activeren en de waarde in cel "B2" wordt automatisch ingesteld op "0".
Meestal zijn kringverwijzingen het resultaat van een gebruikersfout, maar in sommige gevallen wilt u misschien een kringverwijzing gebruiken. Het belangrijkste voorbeeld van het gebruik van een kringverwijzing is om waarden iteratief te berekenen. Om dit te doen, moet je naar Bestand > Opties > Formules en Iteratieve berekening ingeschakeld:
Externe referenties
Soms moet u bij het berekenen van gegevens mogelijk verwijzen naar gegevens buiten uw werkmap. Dit wordt een externe verwijzing (link) genoemd.
Als u een externe referentie wilt selecteren terwijl u een formule maakt, navigeert u naar de externe werkmap en selecteert u uw referentie zoals u dat normaal zou doen.
Om naar de andere werkmap te navigeren, kunt u de sneltoets CTRL + TAB gebruiken of naar . gaan Beeld > Van venster wisselen.
Nadat u de cel hebt geselecteerd, ziet u een externe verwijzing die er als volgt uitziet:
Merk op dat de naam van de werkmap tussen haakjes [] staat.
Nadat u de werkmap waarnaar wordt verwezen sluit, toont de verwijzing de bestandslocatie:
Wanneer u de werkmap met de externe koppeling opnieuw opent, wordt u gevraagd om automatisch bijwerken van koppelingen in te schakelen. Als u dit doet, opent Excel de referentiewaarde met de huidige waarde van de werkmap. Ook als het gesloten is! Doe voorzichtig! Dit wil je misschien wel of niet.
Benoemde bereiken en externe verwijzingen
Wat gebeurt er met uw externe celverwijzing wanneer rijen/kolommen worden toegevoegd aan of verwijderd uit de naslagwerkmap? Als beide werkmappen zijn geopend, worden de celverwijzingen automatisch bijgewerkt. Als beide werkmappen echter niet zijn geopend, worden de celverwijzingen niet bijgewerkt en zijn ze niet langer geldig. Dit is een grote zorg bij het linken naar externe werkmappen. Hierdoor worden veel fouten veroorzaakt.
Als u naar externe werkmappen linkt, moet u de celverwijzing een naam geven met een benoemd bereik (zie vorige paragraaf voor meer informatie). Nu verwijst uw formule naar het benoemde bereik, ongeacht welke wijzigingen er in de externe werkmap plaatsvinden.
