Excel is uw ultieme productiviteitstool. Leer hoe u benoemde bereiken kunt maken in Excel met behulp van VBA en vind tips om tijd te besparen bij het werken met gegevens en formules.
Microsoft Excel is een krachtige softwaretoepassing die u kunt gebruiken om gegevens te creëren, bij te houden en te analyseren. Naast de standaardfuncties van Excel kun je ook VBA-macro's maken om bepaalde taken te automatiseren.
Wilt u meer weten over het maken van benoemde bereiken en het gebruik van VBA-macro's in Excel? Dan bent u bij ons aan het juiste adres. In deze blogpost bespreken we hoe u benoemde bereiken kunt maken in Excel met behulp van VBA.
We zullen ook enkele voorbeelden geven van hoe u Named Ranges in uw eigen werk kunt gebruiken. Laten we aan de slag gaan!
Wat is een benoemd bereik in Microsoft Excel?
Door een benoemd bereik te gebruiken in plaats van een kolom-/rijverwijzing, kunt u verwijzen naar een cel of groep cellen met een aangepaste naam. De mogelijkheid om de gegevens in uw cellen te specificeren is een groot voordeel van het gebruik van benoemde bereiken.
Hier is een kort voorbeeld van hoe u benoemde bereiken kunt gebruiken om te bepalen of de verzendkosten in rekening worden gebracht bij de productprijs:
- = (B7 + B5 * C4) * (1 + A3)
- =(Verzendkosten + Productprijs * Aantal) * (1 + BTW-tarief)
Optie nummer twee geeft u, zoals u kunt zien, direct een indicatie of de prijs van de producten inclusief verzendkosten is. Dit maakt het voor de gebruiker eenvoudig om te begrijpen hoe de formule werkt, zonder tijd te hoeven verspillen aan het doorzoeken van cellen om erachter te komen wat wat is.
Hoe benoemde bereiken gebruiken in Excel?
Als financieel analist wordt er van je verwacht dat je met verschillende tarieven experimenteert. Alles, van een belastingtarief tot een geprojecteerd inflatiepercentage, kan als voorbeeld worden gebruikt. U kunt benoemde bereiken gebruiken om variabelen te ordenen die zelden worden gewijzigd (zoals maand of jaar) of gedurende een lange periode statisch blijven (zoals het inflatiepercentage).
Hier is een lijst met veelgebruikte namen die u vaak kunt gebruiken bij het werken met benoemde bereiken in Excel:
- Rapportdatum
- Jaar
- Maand
- FcstID
- Belastingtarief
- Onbewerkte gegevens
Dit zijn slechts enkele van de mogelijkheden: u kunt een bereik een naam geven van alles wat u maar wilt, binnen de regels van Excel voor benoemde bereiken.
Maak unieke namen
Het maken van een benoemd bereik is een fluitje van een cent. Het enige dat u nu hoeft te doen, is de cel(len) selecteren waarnaar u wilt verwijzen en een naam typen in het naamvak.
Omdat uw naam geen spaties mag bevatten, moet u de eerste letter van elk nieuw woord met een hoofdletter schrijven of een onderstrepingsteken (_) gebruiken om termen van elkaar te scheiden. Om het instellen van het benoemde bereik te voltooien, drukt u op de ENTER-toets nadat u klaar bent met het typen van de naam.
Opmerking: elk benoemd bereik dat met het naamvak is ingesteld, heeft een werkmapbereik. Dit betekent dat elk werkblad in uw Excel-bestand toegang heeft tot het benoemde bereik.
Namen maken met Name Manager
U kunt benoemde bereiken wijzigen en maken in de Naammanager (tabblad Formules > groep Gedefinieerde namen > knop Naammanager) als u uw benoemde bereiken nog verder wilt aanpassen.
Houd er rekening mee dat u met Name Manager het volgende kunt doen:
- Wijzig de naam van een benoemd bereik dat al bestaat,
- Een wijziging in de referentieformule aanbrengen,
- Definieer de reikwijdte van welke werkbladen de naam toegankelijk is.
Nu u begrijpt wat een benoemd bereik is en hoe u er een kunt maken, gaan we eens kijken naar enkele praktische voorbeelden van hoe u deze in uw werk kunt gebruiken.
Praktische voorbeelden van het gebruik van benoemde bereiken
Voorbeeld 1: een dynamisch bereik creëren
Een dynamisch bereik is een benoemd bereik dat zich automatisch aanpast om nieuwe gegevens op te nemen die aan het werkblad worden toegevoegd. Dit is een geweldige manier om tijd te besparen als uw gegevens vaak veranderen.
Voorbeeld 2: een 3D-bereik creëren
Een 3D-bereik is een benoemd bereik dat verwijst naar gegevens in meerdere werkbladen. Dit kan een grote tijdbesparing opleveren als u met grote hoeveelheden gegevens werkt die op dezelfde manier over meerdere werkbladen zijn geordend.
Voorbeeld 3: een benoemde array maken
Een benoemde array is een type dynamisch bereik dat verwijst naar een array met waarden. Dit kan een goede manier zijn om naar gegevens te verwijzen die in tabelvorm zijn georganiseerd.
Automatiseer benoemde bereiken met VBA-macro's
Nu we een kort overzicht van benoemde bereiken hebben gehad, gaan we eens kijken naar enkele VBA-macro's waarmee u het gebruik ervan kunt automatiseren.
Voeg een benoemd bereik toe
De onderstaande VBA-code laat zien hoe u verschillende soorten benoemde bereiken kunt construeren.
|
Sub NameRange_Add() 'DOEL: Verschillende manieren om een benoemd bereik te maken 'BRON: SoftwareKeep.com Cel als bereik dimmen Dim rng als bereik Bereiknaam dimmen als tekenreeks Celnaam dimmen als tekenreeks 'Enkele celreferentie (werkmapbereik) RangeName = "Prijs" Celnaam = "D7"
Stel cel = Werkbladen("Blad1") in.Bereik(celnaam) Dezewerkmap.NamenVoeg Naam toe:=Bereiknaam, Verwijst naar:=cel 'Enkele celreferentie (werkbladbereik) RangeName = "Jaar" Celnaam = "A2"
Stel cel = Werkbladen("Blad1") in.Bereik(celnaam) Werkbladen("Blad1").NamenVoeg Naam toe:=Bereiknaam, Verwijst naar:=cel 'Celbereikreferentie (werkmapbereik) RangeName = "mijngegevens" Celnaam = "F9:J18"
Stel cel = Werkbladen("Blad1") in.Bereik(celnaam) Dezewerkmap.NamenVoeg Naam toe:=Bereiknaam, Verwijst naar:=cel 'Geheim benoemd bereik (verschijnt niet in Name Manager) RangeName = "Gebruikersnaam" Celnaam = "L45"
Stel cel = Werkbladen("Blad1") in.Bereik(celnaam) Dezewerkmap.NamenNaam toevoegen:=Bereiknaam, RefersTo:=cel, Zichtbaar:=False Einde sub |
Loop door benoemde bereiken
De volgende VBA-macrocode laat zien hoe u door de benoemde bereiken van uw spreadsheet kunt bladeren.
|
Sub NamedRange_Loop() 'DOEL: Alle benoemde bereiken in de actieve werkmap verwijderen 'BRON: SoftwareKeep.com Nm dimmen als naam 'Loop door elk benoemd bereik in de werkmap Voor elke nm in ActiveWorkbook.Namen Foutopsporing.Afdrukken nm.Naam, nm.Verwijstnaar Volgende nm
'Loop door elk benoemd bereik dat is afgestemd op een specifiek werkblad Voor elke nm in werkbladen ("Blad1").Namen Foutopsporing.Afdrukken nm.Naam, nm.Verwijstnaar Volgende nm Einde sub |
Verwijder alle benoemde bereiken
Deze VBA-functie helpt u bij het opschonen van een groot deel van de benoemde bereiken in Excel die u niet langer nodig heeft in uw project.
|
Sub NamedRange_DeleteAll() 'DOEL: Verwijder alle benoemde bereiken in de ActiveWorkbook (afdrukgebieden optioneel) 'BRON: SoftwareKeep.com Nm dimmen als naam Verwijdertelling zo lang dimmen 'PrintAreas ook verwijderen? UserAnswer = MsgBox("Wilt u afdrukgebieden overslaan?", vbYesNoCancel) If UserAnswer = vbYes Then SkipPrintAreas = True Als UserAnswer = vbCancel, sluit dan Sub af 'Error Handler voor het geval er fouten optreden bij het verwijderen van functies Bij fout Ga naar overslaan 'Loop door elke naam en verwijder Voor elke nm in ActiveWorkbook.Namen Als SkipPrintAreas = True And Right(nm.Naam, 10) = "Afdrukgebied" en vervolgens Ga naar Skip
'Error Handler voor het geval er fouten optreden bij het verwijderen van functies Bij fout Ga naar overslaan
'Verwijder benoemd bereik nm.Verwijder VerwijderAantal = VerwijderAantal + 1
Overslaan: 'Fouthandler opnieuw instellen Bij fout Ga naar 0 Volgende
'Rapportresultaat Als VerwijderAantal = 1 Dan MsgBox "[1] naam is verwijderd uit deze werkmap." Anders MsgBox "[" & DeleteCount & "] namen zijn verwijderd uit deze werkmap." Einde als Einde sub |
Verwijder benoemde bereiken met fouten
Alleen benoemde bereiken met fouten worden met deze VBA-code verwijderd. Deze problemen kunnen worden veroorzaakt door het verwijderen van werkbladen of rijen/kolommen.
|
Sub NamedRange_DeleteErrors() 'DOEL: Verwijder alle benoemde bereiken met #REF-fout in de ActiveWorkbook 'BRON: SoftwareKeep.com Nm dimmen als naam Verwijdertelling zo lang dimmen 'Loop door elke naam en verwijder Voor elke nm in ActiveWorkbook.Namen Als InStr(1, nm.Verwijst naar "#REF!") > 0 Dan 'Error Handler voor het geval er fouten optreden bij het verwijderen van functies Bij fout Ga naar overslaan
'Verwijder benoemd bereik nm.Verwijder VerwijderAantal = VerwijderAantal + 1 Einde als Overslaan: 'Fouthandler opnieuw instellen Bij fout Ga naar 0 Volgende
'Rapportresultaat Als VerwijderAantal = 1 Dan MsgBox "[1] foutieve naam is uit deze werkmap verwijderd." Anders MsgBox "[" & DeleteCount & "] foutieve namen zijn uit deze werkmap verwijderd." Einde als
Einde sub |
TL;DR
- Benoemde bereiken zijn een uitstekende manier om uw gegevens in Excel te ordenen.
- Je kunt ze gebruiken om dynamische bereiken, 3D-bereiken en benoemde arrays te maken.
- U kunt uw gebruik van benoemde bereiken ook automatiseren met VBA-macro's.
Conclusie
Excel is een geweldige tool die zelfs de meest alledaagse taken spannend kan maken om te voltooien, met uitgebreide automatiseringsfuncties. Als u uw leven gemakkelijker wilt maken, is het een goede manier om te leren werken met geavanceerde tools zoals Named Ranges met VBA.
We hopen dat dit artikel u heeft geholpen iets meer te leren over het gebruik van benoemde bereiken in Excel. Als u vragen heeft, kunt u zoals altijd hieronder een reactie achterlaten!
Bedankt voor het lezen.
Nog één ding
Op zoek naar meer tips? Bekijk onze andere handleidingen in onze Blog of bezoek ons Helpcentrum voor een schat aan informatie over het oplossen van verschillende problemen.
Meld u aan voor onze nieuwsbrief en krijg vroegtijdig toegang tot onze blogposts, promoties en kortingscodes. Bovendien ben je de eerste die op de hoogte is van onze nieuwste handleidingen, aanbiedingen en andere spannende updates!
Aanbevolen artikelen
» Voorloopnullen toevoegen en verwijderen in Excel
» De formules 'Als cel bevat' gebruiken in Excel
» De scrollvergrendeling vergrendelen en ontgrendelen in Excel?
Neem gerust contact op als u vragen of verzoeken heeft die u door ons wilt laten behandelen.