Alles over Excel benoemde bereiken - Excel-bereiknaam

Anonim

Terwijl u aan Excel werkt, moet u hebben gehoord over benoemde bereiken in Excel. Misschien van een vriend, collega of een online tutorial. Zelfs ik heb het vaak genoemd in mijn artikelen. In dit artikel zullen we meer te weten komen over de benoemde bereiken in Excel en zullen we elk aspect ervan onderzoeken.

Wat zijn benoemde bereik in Excel?

Welnu, benoemde bereiken zijn niets anders dan enkele Excel-bereiken die zijn getagd met een betekenisvolle naam. Als u bijvoorbeeld een cel heeft, bijvoorbeeld B1, die het alledaagse doel bevat, kunt u die cel een specifieke naam geven als 'Doel'. Nu kunt u "Target" gebruiken om naar A1 te verwijzen in plaats van B1 te schrijven.
In een notendop, Benoemd bereik is slechts het benoemen van bereiken.

Hoe een bereik een naam geven in Excel?

Naam handmatig definiëren:

Om een ​​naam voor een bereik te definiëren, kunt u de sneltoets CTRL+F3 gebruiken. Of u kunt deze stappen volgen.

    • Ga naar het tabblad Formule
    • Zoek het gedeelte Gedefinieerde namen en klik op Namen definiëren. Het zal Name Manager openen.

    • Klik op Nieuw.
    • Typ de naam.

  • Selecteer het bereik (werkmap of werkblad)
  • Schrijf een reactie als je wilt.
  • In Verwijst naar vak schrijf de referentie of selecteer een bereik met de muis.
  • Druk op OK. Het is gebeurd.

Nu kunt u ernaar verwijzen door gewoon de naam te typen.
Er zijn enkele regels die u moet volgen bij het maken van namen. Zij zijn

  1. Namen mogen niet beginnen met cijfers of speciale tekens, behalve underscore (_) en backslash (\).
  2. Namen mogen geen spaties en speciale tekens bevatten, behalve _ en \.
  3. Het bereik mag niet worden genoemd als celverwijzingen. Namen van bijvoorbeeld A1, B1 of AZ100 enz. zijn ongeldig.
  4. U kunt een bereik niet de naam "r" en "c" geven, omdat deze zijn gereserveerd voor rij- en kolomverwijzingen.
  5. Twee benoemde bereiken kunnen niet dezelfde naam hebben in een werkmap.
  6. Hetzelfde bereik kan meerdere namen hebben.

Naam automatisch definiëren

Welnu, meestal werk je met een gestructureerde gegevenstabel. Ze hebben kolom en rijen met kolomkoppen en rijkoppen. En meestal zijn deze namen betekenisvol voor de gegevens en wilt u uw bereik een naam geven als deze kolomkoppen. Excel biedt een hulpmiddel om bereiken automatisch een naam te geven met behulp van titels. Volg deze stappen.

    • De bereiken die u een naam wilt geven als hun koppen
    • druk op CTRL+SHIFT+F3, of Zoek het gedeelte Gedefinieerde namen op het tabblad Formule en klik op Maken op basis van selectie.

    • Het onderstaande optievenster verschijnt. Ik heb alleen de bovenste rij geselecteerd omdat ik dit bereik een naam wil geven als kop en geen rijen een naam wil geven.

  • Klik OK.

Nu wordt elke kolom genoemd als hun kop. Telkens wanneer u typt, typt u een formule, deze naam wordt weergegeven in de te gebruiken optie.

Bereik een naam geven met Excel-tabellen

Wanneer we een gegevens als een tabel in Excel organiseren met CTRL + T, worden de kolomkoppen automatisch toegewezen als de naam van de respectieve kolom. U moet Excel-tabellen en hun voordelen verkennen.

Hoe alle benoemde bereiken te zien?

Welnu, er zullen momenten zijn waarop u alle beschikbare benoemde bereiken in de werkmap wilt zien. Alle naambereiken bekijken Druk op CTRL+F3. Of je kunt naar Tabblad Formule > Naambeheer. Het toont alle benoemde bereiken die beschikbaar zijn in de werkmap. U kunt beschikbare benoemde bereiken bewerken, ze verwijderen en nieuwe namen toevoegen.

Eén bereik Meerdere namen

Met Excel kunnen gebruikers hetzelfde bereik een andere naam geven. Bereik A2:A10 kan bijvoorbeeld tegelijkertijd 'Klanten' en 'Klanten' worden genoemd. Beide namen verwijzen naar hetzelfde bereik A2:A10.
Maar u kunt niet dezelfde namen hebben voor twee verschillende bereiken. Het elimineert de kans op onduidelijkheid.

Lijst met benoemde bereiken op blad ophalen

Dus als u een lijst met benoemde bereiken wilt hebben en de bereiken die ze bestrijken, kunt u deze snelkoppeling gebruiken om ze op een plaats in een blad te plakken.

    • Selecteer een cel waar u de lijst met benoemde bereiken wilt krijgen.
    • Druk op F3. Dit opent een Paste Naam: dialoogvenster.
    • Klik op lijst plakken knop.

  • De lijst wordt in de geselecteerde cel geplakt en verder.


Als u dubbelklikt op de naam van het benoemde bereik in het vak naam plakken, worden ze als formules in de cel geschreven. Probeer het.

Benoemde bereiken handmatig bijwerken

Welnu, wanneer u een cel in een benoemd bereik invoegt, wordt deze automatisch bijgewerkt en uitgebreid. Maar als u gegevens aan het einde van de tabel toevoegt, moet u het benoemde veld bijwerken. Volg deze stappen om benoemde bereiken bij te werken.

  • druk op CTRL+F3, om de naambeheerder te openen.
  • Klik op het benoemde bereik dat u wilt bewerken. Klik op Bewerken.
  • Typ in Verwijst naar kolom het bereik waarnaar u wilt uitbreiden en druk op OK.

En het is klaar. Dit is het handmatig bijwerken van benoemde bereiken. We kunnen het echter dynamisch maken door enkele formules te gebruiken.

Benoemde bereiken dynamisch bijwerken

Het is verstandig om uw benoemde bereiken dynamisch te maken, zodat u ze niet hoeft te bewerken wanneer uw gegevens het vooraf gedefinieerde bereik overschrijden.
Ik heb het behandeld in een apart artikel genaamd Dynamic Named ranges. U kunt hier de voordelen ervan in detail leren en begrijpen.

Benoemde bereiken verwijderen

Wanneer u een deel van het genoemde bereik verwijdert, wordt het bereik automatisch aangepast. Maar wanneer u het hele naambereik verwijdert, verdwijnt het uit de namenlijst. Elke formule, afhankelijk van die bereiken, geeft de #REF-fout weer, of ze geven een onjuiste uitvoer (telfuncties).
Als u om welke reden dan ook benoemde bereiken wilt verwijderen, volgt u deze stappen.

  • Druk op CTRL+F3. Naambeheerder wordt geopend.
  • Selecteer Benoemde bereiken die u wilt verwijderen.
  • Klik op de knop Verwijderen of druk op de knop Verwijderen op het toetsenbord.


Voorzichtigheid: Voordat u de benoemde bereiken verwijdert, moet u ervoor zorgen dat er geen formules afhankelijk zijn van deze namen. Als die er zijn, converteer ze dan eerst naar bereiken. Anders ziet u de #REF-fout.

Namen met fouten verwijderen

Excel biedt een hulpmiddel om namen te verwijderen die alleen fouten bevatten. U hoeft ze niet allemaal zelf te identificeren. Volg deze stappen om namen met fouten te verwijderen:

    • Open Naambeheer (CTRL+F3).
    • Klik op de vervolgkeuzelijst Filter in de rechterbovenhoek.
    • Selecteer "Naam met fouten"

  • Selecteer Alles en druk op de verwijderknop.

En ze zijn weg. Alle namen met fouten worden onmiddellijk uit het record verwijderd.

Benoemde bereiken met formules

Het beste gebruik van benoemde bereiken wordt ervaren met formules. De formules worden flexibeler en leesbaarder met Named Ranges. Laten we eens kijken hoe.

Makkelijk te schrijven formules

Laten we nu zeggen dat u een bereik hebt genoemd als "Items". Nu is de lijst met items die u wilt tellen "potloden". Met naam is het gemakkelijk om deze AANTAL.ALS-formule te schrijven. Schrijf gewoon

=AANTAL.ALS(Item, "Potlood")

Zodra u het openingshaakje van de formule schrijft, verschijnt de lijst met beschikbare benoemde bereiken. Zonder een naam zou je een gi AANTAL.ALS-functie van Excel met bereiken schrijven, waarvoor je misschien eerst naar het bereik moet kijken en vervolgens het bereik selecteert of het in de formule typt.

Excel bedient de beschikbare naambereiken.

De namen van bereiken worden weergegeven als suggesties wanneer u een letter typt na het =-teken. Hetzelfde als Excel toont de lijst met formules. Als u bijvoorbeeld =u typt, wordt elke methode en benoemd bereik weergegeven, beginnend met u, zodat u ze gemakkelijk kunt gebruiken.

Constanten maken met benoemde bereiken

Tot nu toe hebben we geleerd over het benoemen van bereiken, maar u kunt ook waarden noemen. Als uw klantnaam bijvoorbeeld Sunder Pichai is, kunt u een naam "Client" maken en deze verwijst naar het schrijven van "Sundar Pichai". Wanneer u nu =Client in een cel schrijft, wordt Sundar Pichai weergegeven.


Niet alleen tekst, maar je kunt ook een getal toewijzen als constante om mee te werken. U definieert bijvoorbeeld een doel. Of de waarde van iets dat niet zal veranderen.
Absoluut en relatief verwijzen met benoemde bereiken

De verwijzing met Named ranges in is zeer flexibel. Als u bijvoorbeeld de naam van een benoemd bereik in een relatieve cel schrijft, gedraagt ​​het zich als een overeenkomstige verwijzing. Zie onderstaande afbeelding.

Maar wanneer u het met formules gebruikt, zal het zich als absoluut gedragen. Nou, meestal gebruik je ze met formules, dus je kunt zeggen dat ze standaard Absoluut zijn, maar eigenlijk zijn ze flexibel.

Maar we kunnen ze ook relatief maken.

Hoe relatieve benoemde bereiken in Excel te maken?

Laten we zeggen dat als ik een bereik "Before" wil noemen, dat verwijst naar de cel links naar waar het ook is geschreven. Hoe doe ik dat? Volg deze stappen:

  • Druk op CTRL+F3
  • Klik op Nieuw
  • Typ 'Voor' in het gedeelte 'Naam'.
  • Schrijf in het gedeelte 'Verwijst naar:' het adres van de cel aan de linkerkant. Als u zich bijvoorbeeld in cel B1 bevindt, schrijft u "=A2" in het gedeelte 'Verwijst naar:'. Zorg ervoor dat het geen $-teken heeft.


Waar u nu ook "Befor" in de formule schrijft, het verwijst naar de cel die eraan is gelaten.

Hier heb ik eerder in de COLUMN-functie gebruikt. De formule retourneert het kolomnummer van de linkercel waar het is geschreven. Tot mijn verbazing toont A1 het kolomnummer van de laatste kolom. Wat betekent dat het blad rond is. Ik dacht dat het een #REF-fout zou tonen.

Naam geven aan vaak gebruikte formules?

Nu is deze geweldig. Vaak gebruikt u dezelfde formule keer op keer in een werkblad. U wilt bijvoorbeeld controleren of een naam in uw klantenlijst staat of niet. En deze behoefte kan vele malen voorkomen. Hiervoor schrijf je elke keer dezelfde complexe formule.

=ALS(AANTAL.ALS(Klant,I3),"In lijst","Niet in lijst")

Hoe zit het als u gewoon '=IsInCustomer' in een cel typt en u ziet of de waarde in de linkercel in de klantenlijst staat of niet?

Ik heb hier bijvoorbeeld een tafel klaargemaakt. Nu wil ik gewoon "=IsInCustomer" typen in J5 en ik zou graag willen zien of de waarde in I5 staat in Klantenlijst of Niet. Volg hiervoor deze stappen.

    • Druk op CTRL+F3
    • Klik op Nieuw
    • Schrijf in Naam: ‘IsInCustomer’
    • In 'Verwijst naar' schrijf je je formule. =ALS(AANTAL.ALS(Klant,I5),"In lijst","Niet in lijst")

  • Druk op de OK-knop.

Waar u nu ook 'IsInCustomer' typt, het zal de waarde in de linkercel in de klantenlijst controleren.

Dit voorkomt dat je jezelf keer op keer herhaalt.

Benoemde bereiken toepassen op formules

Zo vaak definiëren we namen voor onze bereiken nadat we al formules hebben geschreven op basis van bereiken. Ik heb bijvoorbeeld de totale prijs als cellen = E2 * F2. Hoe kunnen we het veranderen in Eenheden*Eenheid_Kosten.

    • Selecteer de formules.
    • Ga naar het formuletabblad. Klik op de vervolgkeuzelijst Naam definiëren.

  • Klik op Namen toepassen.
  • Er verschijnt een lijst met alle benoemde bereiken. Kies de juiste namen en druk op ok.


En de namen worden nu toegepast. Je kunt het zien in de formulebalk.

Gemakkelijk te lezen formules met benoemde bereiken

Zoals je hebt gezien, maken benoemde bereiken het gemakkelijk om de formules te lezen. Als ik =AANTAL.ALS(“A2:A100”,B2) schrijf, zal niemand begrijpen wat ik probeer te tellen, totdat ze de gegevens zien of iemand het hun uitlegt.

Maar als ik = AANTAL.ALS (regio,'oost') schrijf, zullen de meeste gebruikers onmiddellijk begrijpen dat we het voorkomen van 'oost' tellen in het gebied met de naam bereik.

Draagbare formules

Benoemde bereiken maken het heel eenvoudig om formules te kopiëren en te plakken zonder dat u zich zorgen hoeft te maken over het wijzigen van verwijzingen. En u kunt de ene formule van de ene werkmap naar de andere brengen en het werkt prima totdat en tenzij de bestemmingswerkmap dezelfde namen heeft.

Als u bijvoorbeeld een formule heeft =AANTAL.ALS(regio,oost) in Distributietabel en je hebt een andere werkmap, zeg klanten dat ook een genoemd bereik "Regio" heeft. Als u deze formule nu rechtstreeks ergens in die werkmap kopieert, wordt u de juiste informatie weergegeven. De structuur van de gegevens doet er niet toe. Het maakt niet uit waar die kolom in je werkmap is. Dit zal correct werken.

In de bovenstaande afbeelding heb ik exact dezelfde formule gebruikt in twee verschillende bestanden om het aantal of het oosten te tellen dat voorkomt in de regiolijst. Nu staan ​​ze in verschillende kolommen, maar aangezien beide als regio worden genoemd, zal het perfect werken.

Gemakkelijk navigeren in de werkmap

Het wordt eenvoudiger om in een werkmap te navigeren met benoemde bereiken. U hoeft alleen de naam van het vak met de naam in te typen. Excel brengt u naar het bereik, het maakt niet uit waar u zich in de werkmap bevindt. Aangezien het genoemde bereik van het bereik van de werkmap is.
Als u bijvoorbeeld op blad 10 staat en u wilt een klantenlijst hebben, en u weet niet op welk blad het staat. Ga gewoon naar het naamvak en typ 'klant'. U wordt in een fractie van een seconde naar het genoemde bereik geleid.


Het zal de moeite van het onthouden van de reeksen verminderen.

Navigeren met behulp van hyperlinks met benoemd bereik

Wanneer uw blad groot is en u vaak van het ene punt naar het andere gaat, gebruikt u graag hyperlinks om gemakkelijk te navigeren. Well Named Ranges kan perfect werken met hyperlinks. Volg deze stappen om hyperlinks toe te voegen met benoemde bereiken.

    • Kies een cel waar je een hyperlink wilt
    • Druk op CTRL+K of ga naar Tabblad invoegen> Hyperlink om het dialoogvenster Hyperlink invoegen te openen.

    • Klik op Plaats in dit document.
    • Scroll naar beneden om beschikbare benoemde bereiken te zien onder Gedefinieerde namen

  • Selecteer het Benoemde bereik om een ​​hyperlink naar dat bereik in te voegen.

En het is klaar. U hebt uw hyperlink naar het door u gekozen benoemde bereik. Hiermee kunt u een index van benoemde bereiken maken die u kunt zien en klikken om er rechtstreeks naartoe te navigeren. Dit maakt uw werkmap echt gebruiksvriendelijk.

Benoemd bereik en gegevensvalidatie

Benoemde bereiken en gegevensvalidatie zijn min of meer voor elkaar gemaakt. Benoemde bereiken maken gegevensvalidatie in hoge mate aanpasbaar. Het wordt een stuk eenvoudiger om een ​​validatie uit een lijst toe te voegen met een benoemd bereik. Laten we eens kijken hoe…

    • Ga naar het tabblad Gegevens
    • Klik op Gegevensvalidatie
    • Selecteer Lijst in het gedeelte 'Toestaan:'
    • Typ in het gedeelte 'Bron:' "= Klant" (schrijf het genoemde bereik dat u heeft)

  • Druk op OK

Nu zal deze cel namen hebben van klanten die deel uitmaken van het bereik Klant genoemd. Makkelijk, nietwaar.

Afhankelijke of trapsgewijze gegevensvalidatie met benoemde bereiken

Wat nu als u een trapsgewijze of afhankelijke gegevensvalidatie wilt. Als u bijvoorbeeld een vervolgkeuzelijst wilt met categorieën, Fruit en Groenten. Als u nu fruit kiest, moet in een andere vervolgkeuzelijst alleen de optie fruit worden weergegeven en als u Groente kiest, dan alleen groenten.

Dit kan eenvoudig worden bereikt door benoemde bereiken te gebruiken. Leren hoe.

  • Afhankelijke vervolgkeuzelijst met benoemd bereik
  • Andere manieren voor trapsgewijze gegevensvalidatie

Geen gegevensvalidatie met namen van weergegeven gegevens

Hoewel Excel-tabellen gestructureerde namen bieden, kunnen ze niet worden gebruikt met gegevensvalidatie en voorwaardelijke opmaak. Ik weet niet waarom Excel dit niet toestaat.
Maar het betekent niet dat het niet kan. U kunt bereiken binnen een tabel een naam geven en deze vervolgens gebruiken voor validatie. Excel heeft er geen enkel probleem mee.

Bereik van benoemde bereiken

Tot nu toe hadden we het over benoemde bereiken met werkmapbereik. Wat? We hebben het er niet over gehad? Ok, dus laten we snel begrijpen wat het bereik van benoemde bereiken is.

Wat is de reikwijdte van een naambereik?

Well scope definieert waar een naambereik kan worden herkend. Elke naam kan niet worden herkend buiten de reikwijdte ervan. Een naam in werkmap1 kan bijvoorbeeld niet worden herkend in een andere werkmap. Excel biedt twee opties voor het bereik van benoemde bereiken Werkblad en Werkmap.

Hoe definieer je een bereik van een benoemd bereik?

Wanneer u een nieuw naambereik maakt, ziet u een gedeelte 'Bereik:'. Klik op de vervolgkeuzelijst en kies het bereik voor uw naambereik. U kunt het bereik niet wijzigen nadat u een benoemd bereik hebt gemaakt. Doe het dus beter eerder. Standaard is dit een werkmap.

Werkmapbereik

Dit is het standaardbereik voor een benoemd bereik. Een naam die is gedefinieerd met het bereik van de werkmap kan worden gebruikt in de hele werkmap waarin deze is gedefinieerd (niet in andere werkmappen).
Alle bovenstaande voorbeelden hadden een werkmapbereik.

Werkbladbereik

Een naam die is gedefinieerd met een werkbladbereik kan alleen worden gebruikt op een werkblad definiëren. Als ik bijvoorbeeld 'Totaal' definieer voor een totale cel met het bereik van blad1. Dan wordt het totaal alleen op blad1 herkend. Andere bladen zullen het niet herkennen.

Ik wil een Excel-bereik

Excel heeft geen Global of zeg Excel Scope. Eigenlijk zou ik graag enkele namen willen definiëren die in alle werkmappen op mijn systeem kunnen worden herkend. Als iemand weet hoe we dit kunnen doen, laat het me weten.

Bereik bewerken na het maken van namen

Dat kan niet. Excel staat u niet toe om het bereik van een benoemd bereik te bewerken nadat u het hebt gemaakt. Aangezien alle benoemde bereiken op een werkblad standaard Workbook Scoped zijn, wilt u misschien hun bereik wijzigen in een werkblad.
Om dit te doen, maakt u gewoon een kopie van dat blad en Excel maakt elke naam op dat blad lokaal om dubbelzinnigheid te voorkomen. U kunt nu desgewenst het originele blad verwijderen.

Naambereik knippen plakken

Wanneer u een benoemd bereik van de ene bestemming naar de andere knipt en plakt, verandert de verwijzing naar een nieuwe locatie. Als u bijvoorbeeld een genoemd bereik "Klant" hebt in A2:A10 en dit knipt en plakt in B2:B10, dan verwijst de klantnaam naar de nieuwe locatie B2:B10.

Dynamische benoemde bereiken in Excel

17 geweldige functies van Excel-tabellen

Populaire artikels:

50 Excel-snelkoppelingen om uw productiviteit te verhogen

Hoe de VERT.ZOEKEN-functie in Excel te gebruiken?

Hoe de AANTAL.ALS-functie in Excel te gebruiken?

Hoe de SUMIF-functie in Excel te gebruiken?