Werken met ranges in Excel
Een range in Excel verwijst naar een groep cellen, bijvoorbeeld A1:B10. Je kunt ranges vergrendelen, dynamisch maken en relatief of absoluut instellen met $-tekens. Hieronder leg ik alles stap voor stap uit.
1. Absolute en relatieve verwijzingen in ranges
| Type verwijzing | Notatie | Uitleg |
|---|---|---|
| Relatief | A1 | Verandert mee als je de cel kopieert naar een andere locatie. |
| Absoluut | $A$1 | Blijft altijd verwijzen naar A1, ook als je de formule kopieert. |
| Gedeeltelijk absoluut (rij vast, kolom flexibel) | A$1 | Rijen blijven vast, maar kolommen kunnen veranderen |
| Gedeeltelijk absoluut (kolom vast, rij flexibel) | $A1 | Kolommen blijven vast, maar rijen kunnen veranderen. |
Voorbeeld:
Als je in cell A3 de formule "=A1" zet en naar rechts sleept dan wordt het in C3 automatisch =C1
Als je =$A$1 gebruikt, blijft het altijd verwijzen naar A1.
2. Een hele kolom of rij vastzetten
Soms wil je in Excel een hele kolom of rij vergrendelen in een formule, dit kun je doen door het $ teken te gebruiken.
Een hele kolom vastzetten: =$A:$A
Dit zorgt ervoor dat altijd kolom A wordt gebruikt, ongeacht waar je de formule plakt.
Een hele rij vastzetten: =$1:$1
Dit zorgt ervoor dat altijd rij 1 wordt gebruikt.
Een specifieke kolom vastzetten, maar niet de rijen: =$A1
De kolom blijft vast, maar de rijen kunnen veranderen.
Een specifieke rij vastzetten, maar niet de kolommen: =A$1
De rij blijft vast, maar de kolommen kunnen veranderen.
TIP! Als je een range selecteert hoef je niet zelf het $ teken in te vullen, druk op F4 en Excel zal dit voor je doen!
1x F4 = Rij + Kolom vastzetten
2x F4 = Alleen Rij vastzetten
3x F4 = Alleen Kolom vastzetten
4x F4 = Niets vastzetten
3. Dynamische Ranges
Soms wil je een dynamisch bereik dat automatisch meegroeit met gegevens. Dit kan o.a. met functies zoals INDEX(), OFFSET() en INDIRECT(). Echter kan dit echt ook zeer gemakkelijk door het gebruik van een punt in de ranges ("verwijzingen knippen").
Omdat deze formules nogal ingewikkeld kunnen worden en door het gebruik van de nieuwe verwijzing zal ik niet verder ingaan op de formules, heb je alsnog hulp nodig met 1 of meerdere van dit soort formules? Neem dan contact op en dan kijken we wat ik voor je kan betekenen!
Het gebruik van verwijzing knippen/afkorten.
In Excel kun je verwijzingen knippen/afkorten door gebruik te maken van een punt in een range. Dit is een handige manier om een dynamische range te creëren (voor bijvoorbeeld in formules, draaitabellen of Data Validatie).
Hieronder een aantal voorbeelden
Een enkele punt
Wanneer je een punt gebruikt in een range, zoals A1:.A10, betekent dit dat de range begint bij cel A1 en eindigt bij de laatste cel in range A1:A10 die gegevens bevat. Dit is vooral nuttig wanneer je niet precies weet hoeveel rijen gegevens je hebt, maar je wilt dat de formule automatisch alle gegevens in de kolom omvat.
2 punten VS Enkele punt
Stel dat er een aantal lege regels zijn aan het begin van je data dan zal dit als leeg (0) terugkomen in een A1:.A10 verwijzing.
Dit kun je voorkomen door ook in het begin al de range te knippen door ook een punt na A1 in te vullen: A1.:.A10.
Nu worden de lege cellen aan het begin en aan het einde niet mee genomen!
Kolom verwijzing met punten
Je kan ook een punt gebruiken in een kolomverwijzing.
A.:A - Trim alle lege cellen aan het begin van de range (A2:A1048576)
Deze optie gaat dus t/m de laatste regel in de worksheet, ongeacht of er data in staat of niet!
A:.A - Trim alle lege cellen aan het einde van de range (A1:A5)
A.:.A - Trim alle lege cellen aan het begin en eind van de range (A2:A5)
4. Named Ranges
Een Named Range in Excel is een naam die je toewijst aan een specifieke cel of een groep cellen. Dit maakt het eenvoudiger om te verwijzen naar deze cellen in formules en functies. In plaats van te werken met celadressen zoals A1:B10, kun je een naam gebruiken die je zelf hebt gekozen, zoals SalesData of EmployeeList.
Named Range creëren naast formulebalk
Door een cel or range te selecteren en daarna een naam in te voeren in het referentie veld naast de formulebar kun je snel een "Named Range" maken.
Als je hierna ergens in het Workbook (ook op andere worksheets) of in formules "=Laptop" invult zal de waarde in cel B2 terug komen. Mocht de waarde in cel B2 veranderen dan zal de Named Range dit uiteraard overnemen!
Meerdere Named Ranges in dropdown
In het referentieveld kun je ook een dropdown openen en een Named Range selecteren (ook in andere worksheets zijn ze beschikbaar!), zorg altijd wel dat de namen duidelijk zijn om fouten te voorkomen!
Name Manager
Je kan de Named Ranges ook terug vinden in de Name Manager (Formulas tabblad > Name Manager). Hier kun je ze gemakkelijk aanpassen, verwijderen of een nieuwe maken!
Meerdere kolommen in je Named Range
Uiteraard kun je ook een grotere selectie in je Named Range zetten, hiermee kun je bijvoorbeeld gemakkelijk de data in een Draaitabel (PivotTable) aanspreken door simpelweg de Named Range in te vullen ("=Product_Inkoop")
Dynamische Named Range in PivotTable
Hier een voorbeeld hoe je een dynamische Named Range in een Draaitabel zou kunnen gebruiken (i.p.v. bijvoorbeeld een Table):
Named Range = "Product_Inkoop"
Verwijzing is = "=Ranges!$A.:.$B" (Worksheet heet "Ranges")
Op deze manier heb je een dynamische draaitabel die automatisch het aantal regels aanpast!
Zo zie je maar weer, een 'Range' in Excel lijkt eenvoudig, maar zodra je er dieper op ingaat, ontdek je een wereld vol mogelijkheden. Met meer kennis kun je processen optimaliseren, automatiseren en versnellen! Heb je processen of worksheets die een upgrade nodig hebben? Of wil je je Excel-vaardigheden naar een hoger niveau tillen? Neem dan vandaag nog vrijblijvend contact op voor meer informatie. Samen kunnen we jouw werk efficiënter en effectiever maken!