Benoemde bereiken maken in Excel (een stapsgewijze handleiding)

Wat staat er in de naam?

Als u met Excel-spreadsheets werkt, kan dit veel tijdwinst en efficiëntie opleveren.

In deze zelfstudie leert u hoe u benoemde bereiken in Excel kunt maken en hoe u deze kunt gebruiken om tijd te besparen.

Benoemde bereiken in Excel - een inleiding

Als iemand me moet bellen of naar me moet verwijzen, zullen ze mijn naam gebruiken (in plaats van te zeggen dat een man op die en die plek verblijft met die en die lengte en gewicht).

Rechts?

Op dezelfde manier kunt u in Excel een naam geven aan een cel of een celbereik.

In plaats van de celverwijzing (zoals A1 of A1:A10) te gebruiken, kunt u nu gewoon de naam gebruiken die u eraan hebt toegewezen.

Stel dat u een gegevensset heeft zoals hieronder weergegeven:

Als u in deze gegevensset moet verwijzen naar het bereik met de datum, moet u A2:A11 in formules gebruiken. Evenzo moet u voor Sales Rep en Sales B2:B11 en C2:C11 gebruiken.

Hoewel het goed is als je maar een paar datapunten hebt, maar als je enorme complexe datasets hebt, kan het tijdrovend zijn om celverwijzingen te gebruiken om naar data te verwijzen.

Excel Named Ranges maakt het gemakkelijk om naar gegevenssets in Excel te verwijzen.

U kunt voor elke gegevenscategorie een benoemd bereik in Excel maken en vervolgens die naam gebruiken in plaats van de celverwijzingen. Datums kunnen bijvoorbeeld 'Datum' worden genoemd, gegevens van vertegenwoordigers kunnen 'SalesRep' worden genoemd en verkoopgegevens kunnen 'Sales' worden genoemd.

U kunt ook een naam voor een enkele cel maken. Als u bijvoorbeeld het percentage van de verkoopcommissie in een cel hebt staan, kunt u die cel de naam 'Commissie' geven.

Voordelen van het maken van benoemde bereiken in Excel

Dit zijn de voordelen van het gebruik van benoemde bereiken in Excel.

Namen gebruiken in plaats van celverwijzingen

Wanneer u benoemde bereiken in Excel maakt, kunt u deze namen gebruiken in plaats van de celverwijzingen.

U kunt bijvoorbeeld =SUM(SALES) gebruiken in plaats van =SUM(C2:C11) voor de bovenstaande dataset.

Bekijk de onderstaande formules. In plaats van celverwijzingen te gebruiken, heb ik de Named Ranges gebruikt.

• Aantal verkopen met een waarde van meer dan 500: =AANTAL.ALS(Verkoop,">500″)
• Som van alle verkopen gedaan door Tom: =SUMIF(SalesRep,”Tom”,Sales)
• Commissie verdiend door Joe (verkoop door Joe vermenigvuldigd met commissiepercentage):
  =SOM.ALS(Verkoopvertegenwoordiger,"Joe",Verkoop)*Commissie

U bent het ermee eens dat deze formules gemakkelijk te maken en te begrijpen zijn (vooral wanneer u ze met iemand anders deelt of ze zelf opnieuw bekijkt.

U hoeft niet terug te gaan naar de dataset om cellen te selecteren

Een ander belangrijk voordeel van het gebruik van benoemde bereiken in Excel is dat u niet terug hoeft te gaan om de celbereiken te selecteren.

U kunt gewoon een paar alfabetten van dat benoemde bereik typen en Excel toont de overeenkomende benoemde bereiken (zoals hieronder weergegeven):

Benoemde bereiken maken formules dynamisch

Door benoemde bereiken in Excel te gebruiken, kunt u Excel-formules dynamisch maken.

In het geval van verkoopcommissie kunt u bijvoorbeeld in plaats van de waarde 2,5% de Named Range gebruiken.

Als uw bedrijf later besluit om de commissie te verhogen tot 3%, kunt u eenvoudig het Named Range bijwerken en alle berekeningen worden automatisch bijgewerkt om de nieuwe commissie weer te geven.

Benoemde bereiken maken in Excel

Hier zijn drie manieren om benoemde bereiken in Excel te maken:

Methode #1 - Naam definiëren gebruiken

Hier zijn de stappen om benoemde bereiken in Excel te maken met Define Name:

• Selecteer het bereik waarvoor u een benoemd bereik in Excel wilt maken.
• Ga naar Formules -> Definieer naam.
• Typ in het dialoogvenster Nieuwe naam de naam die u aan het geselecteerde gegevensbereik wilt toewijzen. U kunt het bereik specificeren als de volledige werkmap of een specifiek werkblad. Als u een bepaald blad selecteert, is de naam niet beschikbaar op andere bladen.
• Klik OK.

Hiermee wordt een Named Range SALESREP gemaakt.

Methode #2: Het naamvak gebruiken

• Selecteer het bereik waarvoor u een naam wilt maken (selecteer geen koppen).
• Ga naar het naamvak aan de linkerkant van de formulebalk en typ de naam van de waarmee u het benoemde bereik wilt maken.
• Houd er rekening mee dat de hier gemaakte naam beschikbaar zal zijn voor de hele werkmap. Als u het wilt beperken tot een werkblad, gebruikt u methode 1.

Methode #3: Gebruik maken van selectieoptie

Dit is de aanbevolen manier wanneer u gegevens in tabelvorm hebt en u voor elke kolom/rij een benoemd bereik wilt maken.

Als u bijvoorbeeld in de onderstaande dataset snel drie benoemde bereiken wilt maken (Datum, Sales_Rep en Sales), kunt u de onderstaande methode gebruiken.

Dit zijn de stappen om snel benoemde bereiken te maken op basis van een gegevensset:

• Selecteer de volledige dataset (inclusief de headers).
• Ga naar Formules -> Maken vanuit selectie (Sneltoets - Control + Shift + F3). Het opent het dialoogvenster 'Namen maken uit selectie'.
• Vink in het dialoogvenster Namen van selectie maken de opties aan waar u de koppen hebt. In dit geval selecteren we alleen de bovenste rij omdat de koptekst in de bovenste rij staat. Als u koppen heeft in zowel de bovenste rij als de linkerkolom, kunt u beide kiezen. Evenzo, als uw gegevens zijn gerangschikt wanneer de koppen alleen in de linkerkolom staan, vinkt u alleen de optie Linkerkolom aan.

Hiermee worden drie benoemde bereiken gemaakt: Datum, Verkoop_Rep en Verkoop.

Merk op dat het automatisch namen uit de headers oppikt. Als er een spatie tussen woorden is, wordt een onderstrepingsteken ingevoegd (omdat u geen spaties in benoemde bereiken kunt hebben).

Naamgevingsconventie voor benoemde bereiken in Excel

Er zijn bepaalde naamgevingsregels die u moet kennen bij het maken van benoemde bereiken in Excel:

• Het eerste teken van een benoemd bereik moet een letter en een onderstrepingsteken (_) of een backslash (\) zijn. Als het iets anders is, wordt er een fout weergegeven. De overige tekens kunnen letters, cijfers, speciale tekens, punten of onderstrepingstekens zijn.
• U kunt in Excel geen namen gebruiken die ook celverwijzingen vertegenwoordigen. U kunt AB1 bijvoorbeeld niet gebruiken omdat het ook een celverwijzing is.
• U kunt geen spaties gebruiken tijdens het maken van benoemde bereiken. U kunt bijvoorbeeld geen verkoopvertegenwoordiger als benoemd bereik hebben. Als u twee woorden wilt combineren en een benoemd bereik wilt maken, gebruikt u een onderstrepingsteken, punt of hoofdletters om het te maken. U kunt bijvoorbeeld Sales_Rep, SalesRep of SalesRep hebben.
  • Bij het maken van benoemde bereiken behandelt Excel hoofdletters en kleine letters op dezelfde manier. Als u bijvoorbeeld een benoemd bereik VERKOOP maakt, kunt u geen ander benoemd bereik maken, zoals 'verkoop' of 'Verkoop'.
• Een benoemd bereik kan maximaal 255 tekens lang zijn.

Te veel benoemde bereiken in Excel? Maak je geen zorgen

Soms kunt u in grote gegevenssets en complexe modellen uiteindelijk veel benoemde bereiken in Excel maken.

Wat als u de naam van het Named Range dat u hebt gemaakt niet meer weet?

Maak je geen zorgen - hier zijn enkele nuttige tips.

De namen van alle benoemde bereiken verkrijgen

Dit zijn de stappen om een ​​lijst te krijgen van alle benoemde bereiken die u hebt gemaakt:

• Ga naar het tabblad Formules.
• Klik in de groep Gedefinieerd genoemd op Gebruik in formule.
• Klik op 'Namen plakken'.

Dit geeft u een lijst met alle benoemde bereiken in die werkmap. Dubbelklik erop om een ​​benoemd bereik te gebruiken (in formules of een cel).

De overeenkomende benoemde bereiken weergeven

• Als je een idee hebt over de naam, typ dan een paar begintekens en Excel toont een vervolgkeuzelijst met de overeenkomende namen.

Benoemde bereiken in Excel bewerken

Als u al een benoemd bereik heeft gemaakt, kunt u dit als volgt bewerken:

• Ga naar het tabblad Formules en klik op Naambeheer.
• In het dialoogvenster Name Manager worden alle benoemde bereiken in die werkmap weergegeven. Dubbelklik op het benoemde bereik dat u wilt bewerken.
• Breng de wijzigingen aan in het dialoogvenster Naam bewerken.
• Klik OK.
• Sluit het dialoogvenster Naambeheer.

Handige benoemde bereiksnelkoppelingen (de kracht van F3)

Hier zijn enkele handige sneltoetsen die van pas kunnen komen wanneer u met Named Ranges in Excel werkt:

• Om een ​​lijst van alle benoemde bereiken te krijgen en deze in de formule te plakken: F3
• Om een ​​nieuwe naam te creëren met behulp van Name Manager Dialogue Box: Controle + F3
• Benoemde bereiken maken op basis van selectie: Besturing + Shift + F3

Dynamische benoemde bereiken maken in Excel

Tot nu toe hebben we in deze zelfstudie statische benoemde bereiken gemaakt.

Dit betekent dat deze benoemde bereiken altijd naar dezelfde dataset verwijzen.

Als A1:A10 bijvoorbeeld is genoemd als 'Verkoop', verwijst dit altijd naar A1:A10.

Als u meer verkoopgegevens toevoegt, moet u handmatig de referentie in het genoemde bereik bijwerken.

In de wereld van steeds groter wordende datasets kan dit veel van uw tijd in beslag nemen. Elke keer dat u nieuwe gegevens krijgt, moet u mogelijk de benoemde bereiken in Excel bijwerken.

Om dit probleem aan te pakken, kunnen we dynamische benoemde bereiken in Excel maken die automatisch rekening houden met aanvullende gegevens en deze opnemen in het bestaande benoemde bereik.

Als ik bijvoorbeeld twee extra verkoopgegevenspunten toevoeg, verwijst een dynamisch benoemd bereik automatisch naar A1:A12.

Dit soort dynamisch benoemd bereik kan worden gemaakt met behulp van de Excel INDEX-functie. In plaats van de celverwijzingen op te geven tijdens het maken van het Benoemde bereik, specificeren we de formule. De formule wordt automatisch bijgewerkt wanneer de gegevens worden toegevoegd of verwijderd.

Laten we eens kijken hoe u dynamische benoemde bereiken in Excel kunt maken.

Stel dat we de verkoopgegevens in cel A2:A11 hebben.

Dit zijn de stappen om dynamische benoemde bereiken in Excel te maken:

1. 1. Ga naar het tabblad Formule en klik op Naam definiëren.
   2. Typ het volgende in het dialoogvenster Nieuwe naam:
      • Naam: Verkoop
      • Toepassingsgebied: werkboek
      • Verwijst naar: =$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,””&””))
   3. Klik OK.

Klaar!

Je hebt nu een dynamisch benoemd bereik met de naam 'Verkoop'. Dit wordt automatisch bijgewerkt wanneer u er gegevens aan toevoegt of er gegevens uit verwijdert.

Hoe werken dynamische benoemde bereiken?

Om uit te leggen hoe dit werkt, moet u iets meer weten over de Excel INDEX-functie.

De meeste mensen gebruiken INDEX om een ​​waarde uit een lijst te retourneren op basis van het rij- en kolomnummer.

Maar de INDEX-functie heeft ook een andere kant.

Het kan worden gebruikt om een celverwijzing teruggeven wanneer het wordt gebruikt als onderdeel van een celverwijzing.

Hier is bijvoorbeeld de formule die we hebben gebruikt om een ​​dynamisch benoemd bereik te maken:

=$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,""&""))

INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,””&””) -> Dit deel van de formule zal naar verwachting een waarde retourneren (wat de 10e waarde uit de lijst zou zijn, aangezien er tien items zijn).

Echter, wanneer gebruikt voor een referentie (=$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,””&””))) het retourneert de verwijzing naar de cel in plaats van de waarde.

Daarom retourneert het hier =$A$2:$A$11

Als we twee extra waarden aan de verkoopkolom toevoegen, zou het =$A$2:$A$13 . opleveren

Wanneer u nieuwe gegevens aan de lijst toevoegt, retourneert de Excel AANTAL.ALS-functie het aantal niet-lege cellen in de gegevens. Dit nummer wordt door de INDEX-functie gebruikt om de celverwijzing van het laatste item in de lijst op te halen.

Opmerking:

• Dit zou alleen werken als er geen lege cellen in de gegevens zijn.
• In het bovenstaande voorbeeld heb ik een groot aantal cellen (A2:A100) toegewezen voor de Named Range-formule. U kunt dit aanpassen op basis van uw dataset.

U kunt ook de OFFSET-functie gebruiken om dynamische benoemde bereiken in Excel te maken, maar aangezien de OFFSET-functie vluchtig is, kan dit leiden tot een trage Excel-werkmap. INDEX is daarentegen semi-vluchtig, waardoor het een betere keuze is om dynamische benoemde bereiken in Excel te maken.

Misschien vind je de volgende Excel-bronnen ook leuk:

• Gratis Excel-sjablonen.
• Gratis online Excel-training (7-delige online videocursus).
• Handige Excel-macrocodevoorbeelden.
• 10 Geavanceerde Excel VERT.ZOEKEN Voorbeelden.
• Een vervolgkeuzelijst maken in Excel.
• Een benoemd bereik maken in Google Spreadsheets.
• Hoe te verwijzen naar een ander blad of werkmap in Excel