In veel formules wordt gebruik gemaakt van celverwijzingen. In dit artikel leer je wat celverwijzingen en celbereiken zijn, hoe je dit toe kan passen bij het maken van formules en wat voor verschillende type celverwijzingen er zijn.
Wat is een celverwijzing?
We beginnen met de absolute basis.
Zoals je wellicht al weet begint iedere formule met het is-teken (=).
De formule =B2 verwijst bijvoorbeeld naar cel B2.
Het resultaat van deze verwijzing is de inhoud van cel B2, in dit voorbeeld is dat 50.
Als je de inhoud van twee cellen bij elkaar op wilt tellen, dan kan je dat doen door te verwijzen naar deze cellen in jouw formule.
Op het moment dat je met meerdere cellen moet werken gaat dit echter steeds meer tijd kosten en wordt de kans dat je per ongeluk een fout maakt groter.
Om dit te voorkomen is het in veel gevallen verstandiger om te werken met celbereiken.
Wat is een celbereik?
Een celbereik is een verzameling van cellen.
Je schrijft een celbereik door te beginnen met de cel linksboven, gevolgd door een dubbele punt (:) en tenslotte de cel rechtsonder.
In bovenstaand voorbeeld is het celbereik A1:D2
Als je de inhoud van al deze cellen bij elkaar op wilt tellen, dan kan dat eenvoudig en snel met de functie SOM.
Zoals je ziet maak ik in de formule in dit voorbeeld gebruik van een verwijzing naar het celbereik A1:D2.
Dit gaat veel sneller dan de formule A1+B1+C1+D1+A2+B2+C2+D2 te schrijven.
Je zal echter merken als je wat vaker met celbereiken werkt, dat niet altijd alle cellen die je wilt gebruiken aan elkaar grenzen.
Gelukkig kan je naar meerdere celbereiken verwijzen.
Je begint met het eerste celbereik te schrijven zoals je net hebt geleerd. Daarna typ je een puntkomma (;) om aan te geven dat er een volgend argument volgt. Je kan daarna gewoon het tweede celbereik schrijven en dit proces herhalen indien nodig.
Je hoeft overigens niet per definitie naar meerdere celbereiken te verwijzen.
Je kan ook een combinatie van een celbereik en een celverwijzing toepassen in je formule als dat beter uitkomt.
Het is ook mogelijk om een celbereik een naam te geven en hier naar te verwijzen.
Verwijzen naar een tabel
Je kan er ook voor kiezen om te verwijzen naar een tabel.
Zodra je met de muis een cel in je tabel selecteert verschijnt er in het lint het tabblad ”Tabelontwerp”:
Linksboven zie je vervolgens de naam van je tabel. Indien gewenst kan je dit aanpassen. In dit voorbeeld heet mijn tabel ”Tabel2”.
Als ik de gemiddelde omzet wil berekenen, kan ik nu eenvoudig naar de tabel verwijzen.
Ik typ eerst net zoals anders de functienaam van de functie GEMIDDELDE en daarna de naam van de tabel.
Je krijgt dan dus =GEMIDDELDE(Tabel2
Als je nu de de formule afsluit wordt het gemiddelde berekend van alle data in de tabel. Het is echter de bedoeling om alleen het gemiddelde van de kolom met de omzet te berekenen.
Door een vierkant haakje [ te typen kan je de gewenste kolom selecteren.
Zowel de naam van de kolom als de formule zelf moet je met de juiste haakjes afsluiten.
De formule in dit voorbeeld wordt dan dus =GEMIDDELDE(Tabel2[Omzet])
Je had natuurlijk ook de formule =GEMIDDELDE(B2:B11) kunnen gebruiken.
Het voordeel is alleen dat wanneer je de tabel aanpast, bijvoorbeeld door hierna de omzet van 11 december eronder te zetten, het gemiddelde nog steeds wordt berekent van de omzet in de tabel.
Als ik naar een specifiek celbereik had verwezen, dan blijft de formule het gemiddelde berekenen van dit specifieke celbereik.
Excel geeft wel aan dat ik in dat geval aangrenzende cellen weg heb gelaten in de formule.
Om fouten te voorkomen geef ik er echter de voorkeur aan om in dit soort gevallen te verwijzen naar een tabel.
Ben je enthousiast over wat je tot nu toe hebt geleerd?
Met mijn Formules cursus duik je dieper in de wereld van Excel-functies en leer je alle ins en outs.
Verwijzen naar cellen op een ander werkblad
Je hoeft niet perse te verwijzen naar een cel op hetzelfde werkblad als waarop je de formule invoert.
Als je naar cellen op een ander werkblad wilt verwijzen dan typ je eerst de naam van het werkblad gevolgd door een uitroepteken (!). Daarna kan je de celverwijzing typen zoals je gewend bent.
Stel je hebt gegevens op meerdere tabbladen.
Op tabblad 2 staat bijvoorbeeld in cel A1 het getal 50 en in cel B1 het getal 25.
In tabblad 1 wil je deze gegevens bij elkaar optellen.
Je verwijst hierbij naar 2 cellen en typt dus beide keer voor de verwijzing Blad2!.
De formule wordt dan: =Blad2!A1+Blad2!B1
Als je naar een celbereik verwijst, bijvoorbeeld om het celbereik A1:C2 op blad2 bij elkaar op te tellen met de functie SOM, dan krijg je de volgende formule:
=SOM(Blad2!A1:C2)
In dit voorbeeld heb ik de namen van de tabbladen niet gewijzigd en is dit dus nog ”Blad1, Blad2, Blad3, etc”. Verander je de namen van de tabbladen wel, dan moet je hier dus rekening mee houden in je formules.
Eerder heb ik al eens een blog geschreven over hoe je kan verwijzen naar cellen in een ander Excel bestand.
Verschillende type celverwijzingen
In Excel kan je gebruik maken van relatieve celverwijzingen, absolute celverwijzingen of een combinatie van beiden.
Relatieve celverwijzingen:
Stel je hebt een tabel met een aantal gegevens.
In kolom C is het de bedoeling om de inhoud van kolom A op te tellen met de inhoud van kolom B.
De formule is zoals je ziet =A2+B2
Als je deze formule kopieert naar de overige cellen waarin de som moet worden gemaakt (cel C3, C4 en C5) dan zie je dat Excel de formule automatisch aanpast.
Ondanks dat de formule gekopieerd is, past Excel de formule zelf aan en staat in cel C4 niet ”=A2+B2” maar =A4+B4”.
Excel ziet dat bij de formule in cel C2 de berekening verwijst naar de twee cellen links van de cel waarin de resultaat van de som getoond moet worden.
Excel gaat er vervolgens vanuit dat zodra je de formule naar beneden kopieert, het weer de bedoeling is dat je de twee cellen links van de cel waarin het resultaat getoond wordt bij elkaar opgeteld moeten worden.
De celverwijzingen in de formule bewegen zich dus mee. Dit wordt een relatieve celverwijzing genoemd.
Aangezien de meeste mensen gebruik maken van relatieve celverwijzingen is dit de standaard in Excel en hoef je hier niets voor te doen.
Absolute celverwijzingen
Er zijn ook momenten dat je juist niet wilt dat de celverwijzing mee beweegt.
In dit voorbeeld staat in cel B8 de korting weergegeven.
In kolom C wordt de aanbiedingsprijs berekent.
Als ik deze formule naar beneden kopieer dan verandert deze mee zoals je eerder hebt gezien bij de relatieve celverwijzing.
Cel B2 veranderd in B3.
Het probleem is alleen dat cel B8 mee veranderd in B9, terwijl de de korting in cel B8 blijft staan.
Gelukkig kan je in Excel celverwijzingen absoluut maken. Dit houdt in dat het niet mee veranderd.
Je kan ervoor kiezen om de rijverwijzing absoluut te maken, de kolomverwijzing of beide.
Dit doe je door een dollar-teken ($) voor een kolomverwijzing en/of de rijverwijzing te plaatsen.
In dit specifieke voorbeeld voldoet het om alleen een $ te plaatsen voor de rijverwijzing.
Zoals je ziet blijft het rijnummer nu 8 als ik de formule naar beneden kopieer.
Hieronder volgt een handig overzicht:
- Bij de formule =B8 kan de kolomletter (B) en het rijnummer (8) veranderen. Dit is een relatieve celverwijzing.
- Bij de formule =$B$8 blijft de kolomletter (B) en het rijnummer (8) hetzelfde. Dit is een absolute celverwijzing.
- Bij de formule =$B8 blijft de kolomletter (B) hetzelfde en kan het rijnummer (8) veranderen. Dit is een combinatie van een absolute- en een relatieve celverwijzing.
- Bij de formule =B$8 kan de kolomletter (B) veranderen en blijft het rijnummer (8) hetzelfde. Dit is een combinatie van een relatieve- en een absolute celverwijzing.
Wil je nog meer leren?
Excel biedt veel mogelijkheden om slimmer en sneller te werken. Met mijn Formules cursus leer je stap voor stap hoe je Excel optimaal benut door gebruik te maken van krachtige formules en functies. In de cursus neem ik je mee door een uitgebreide selectie aan formules, van de basis tot meer geavanceerde mogelijkheden.
Klaar om je Excel-kennis naar een hoger niveau te tillen?
Dag Gerard,
Al een week zoek ik naar een oplossing voor de volgende puzzel:
stel cel A1, B1 en C1 (en daaronder kolemmen A, B en C met gelijksoortige cijfers) zijn gevuld met cijfers. In cel D1 staat de formule (A1*B1)/C1.
Nu wil ik de argumenten (cijfers) uit de cellen (kolom) A t/m C tijdelijk verplaatsen. Nu blijft de formule kennelijk verbonden aan de argumenten en niet aan de cellen. De uitkomst blijft staan, de formulecel wordt niet leeg. Alleen als ik de cel-inhoud delete, verdwijnt de uitkomst. Maar ik wil niet deleten, ik wil de cijfers alleen tijdelijk uit de weg. Ik wil dus graag dat de formule verbonden blijft aan de gekozen cellen, niet aan hun inhoud.
Hoe krijg ik dat in Excel voor elkaar. Ook absolute cel-adressen in de formule werken niet naar wens.
Als je dit vraagstukje al eens in een blog hebt behandeld, ontvang ik graag een verwijzing daar naar.