benoemde bereiken zijn een van deze crusty oude functies in Excel die weinig gebruikers begrijpen. Nieuwe gebruikers kunnen ze raar en eng vinden, en zelfs oude handen kunnen ze vermijden omdat ze zinloos en complex lijken.
maar benoemde bereiken zijn eigenlijk een leuke functie. Ze kunnen formules *veel* gemakkelijker maken om te maken, te lezen en te onderhouden. En als bonus, ze maken formules gemakkelijker te hergebruiken (meer draagbaar).
in feite gebruik ik de hele tijd benoemde bereiken bij het testen en prototyperen van formules. Ze helpen me formules sneller te laten werken., Ik gebruik ook benoemde bereiken omdat ik lui ben en niet graag complexe referenties typ:)
de basisprincipes van benoemde bereiken in Excel
Wat is een benoemde bereik?
een benoemd bereik is slechts een voor mensen leesbare naam voor een celbereik in Excel. Als ik bijvoorbeeld het bereik A1:A100 “data” noem, kan ik MAX gebruiken om de maximale waarde te krijgen met een eenvoudige formule:
=MAX(data) // max value
het mooie van benoemde bereiken is dat u betekenisvolle namen in uw formules kunt gebruiken zonder na te denken over celverwijzingen., Zodra u een benoemd bereik hebt, gebruikt u het gewoon als een celverwijzing. Al deze formules zijn geldig met het benoemde bereik “data”:
=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min valueVideo: Hoe maak je een benoemde bereik
een benoemde bereik maken is eenvoudig
een benoemde bereik maken is snel en eenvoudig. Selecteer gewoon een celbereik en typ een naam in de naam doos. Wanneer u op return drukt, wordt de naam aangemaakt:
om snel het nieuwe bereik te testen, kiest u de nieuwe naam in het vervolgkeuzemenu naast het vak Naam. Excel selecteert het bereik op het werkblad.,
Excel kan automatisch Namen maken (ctrl + shift + F3)
Als u goed gestructureerde gegevens met labels hebt, kunt u Excel benoemde bereiken voor u laten maken. Selecteer gewoon de data, samen met de labels, en gebruik het “Create from Selection” commando op het tabblad Formules van het lint:
U kunt ook de sneltoets control + shift + F3 gebruiken.
Met deze functie kunnen we benoemde bereiken maken voor de populatie van 12 staten in één stap:
wanneer u op OK klikt, worden de namen gemaakt., U vindt alle nieuw aangemaakte namen in het vervolgkeuzemenu naast het naamveld:
Met aangemaakt Namen kunt u deze gebruiken in formules als deze
=SUM(MN,WI,MI)benoemde bereiken bijwerken in de Naambeheerder (Control + F3)
zodra u een benoemde bereik, gebruik de naam Manager (Control + F3) om te updaten als dat nodig is. Selecteer de naam waarmee u wilt werken, verander dan de referentie direct (dat wil zeggen bewerken “verwijst naar”), of klik op de knop rechts en selecteer een nieuw bereik.,
Het is niet nodig om op de knop Bewerken te klikken om een referentie bij te werken. Wanneer u op Sluiten klikt, wordt de bereiknaam bijgewerkt.
Opmerking: Als u een volledig benoemde bereik op een werkblad selecteert, kunt u naar een nieuwe locatie slepen en wordt de referentie automatisch bijgewerkt. Ik weet echter geen manier om bereikverwijzingen aan te passen door direct op het werkblad te klikken en te slepen. Als je een manier Weet om dit te doen, klokkenspel in hieronder!
Zie alle benoemde bereiken (control + F3)
om snel alle benoemde bereiken in een werkmap te zien, gebruikt u het vervolgkeuzemenu naast het naamvak.,
Als u meer details wilt zien, open dan de Name Manager (Control + F3), die alle namen met referenties weergeeft, en biedt ook een filter:
opmerking: op een Mac is er geen Name Manager, dus u zult in plaats daarvan het dialoogvenster Naam definiëren zien.
kopieer en plak alle benoemde bereiken (F3)
Als u een meer aanhoudende record van benoemde bereiken in een werkmap wilt, kunt u de volledige lijst met namen overal plakken waar u maar wilt., Ga naar Formules > Gebruiken in Formule (of gebruik de sneltoets F3), kies Plakken namen > Plakken Lijst:
Wanneer u op de Plakken met de knop Lijst, ziet u de namen en verwijzingen geplakt in het werkblad:
Zie namen direct op het werkblad
Als u het zoomniveau instellen op minder dan 40%, Excel toon bereik namen direct op het werkblad:
Bedankt voor deze tip, Felipe!,
namen hebben regels
bij het maken van benoemde bereiken, volg deze regels:
benoemde bereiken in formules
benoemde bereiken zijn eenvoudig te gebruiken in formules
bijvoorbeeld, laten we zeggen dat u een cel in uw werkmap een naam “bijgewerkt”geeft. Het idee is dat u de huidige datum in de cel kunt plaatsen (Ctrl + 😉 en naar de datum elders in de werkmap kunt verwijzen.
De formule in B8 ziet er als volgt uit:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")U kunt deze formule overal in de werkmap plakken en deze wordt correct weergegeven., Wanneer u de datum in “Bijgewerkt” wijzigt, wordt het bericht bijgewerkt waar de formule wordt gebruikt. Zie deze pagina voor meer voorbeelden.
benoemde bereiken verschijnen bij het typen van een formule
zodra u een benoemde bereik hebt gemaakt, verschijnt het automatisch in formules wanneer u de eerste letter van de naam typt. Druk op de tab-toets om de naam in te voeren wanneer u een overeenkomst hebt en wilt u dat Excel de naam invoert.,
benoemde bereiken kunnen werken als constanten
omdat benoemde bereiken op een centrale locatie worden gemaakt, kunt u ze gebruiken als constanten zonder celverwijzing. U kunt bijvoorbeeld namen maken zoals ” MPG “(mijlen per gallon) en” CPG”(kosten per gallon) met en vaste waarden toewijzen:
dan kunt u deze namen gebruiken waar u maar wilt in formules, en hun waarde bijwerken op één centrale locatie.,
benoemde bereiken zijn standaard absoluut
standaard gedragen benoemde bereiken zich als absolute referenties. In dit werkblad zou de formule om brandstof te berekenen bijvoorbeeld zijn:
=C5/$D$2
de verwijzing naar D2 is absoluut (vergrendeld) zodat de formule kan worden gekopieerd zonder dat D2 wordt gewijzigd.,
als we D2 “MPG” noemen wordt de formule:
=C5/MPG
aangezien MPG standaard absoluut is, kan de formule in kolom D worden gekopieerd als-is.
benoemde bereiken kunnen ook relatief zijn
hoewel benoemde bereiken standaard absoluut zijn, kunnen ze ook relatief zijn. Een relatief benoemde bereik verwijst naar een bereik dat relatief is ten opzichte van de positie van de actieve cel op het moment dat het bereik wordt gemaakt. Als gevolg hiervan zijn relatieve benoemde bereiken nuttig om generieke formules te bouwen die overal werken waar ze worden verplaatst.,
bijvoorbeeld, u kunt een generiek “CellAbove” – bereik met de naam als volgt maken:
- selecteer cel A2
- Control + F3 om Naambeheer
- tabblad te openen in de sectie ‘verwijst naar’, typ dan: =A1
CellAbove zal nu de waarde ophalen uit de bovenstaande cel waar deze ook wordt gebruikt.
belangrijk: controleer of de actieve cel zich op de juiste locatie bevindt voordat u de naam maakt.
benoemde bereiken toepassen op bestaande formules
Als u bestaande formules hebt die geen benoemde bereiken gebruiken, kunt u Excel vragen om de benoemde bereiken in de formules voor u toe te passen., Begin met het selecteren van de cellen die formules bevatten die u wilt bijwerken. Voer vervolgens formules uit > Namen definiëren > Namen toepassen.
Excel zal dan verwijzingen die een overeenkomstig benoemde bereik hebben vervangen door de naam zelf.
u kunt ook namen toepassen met zoeken en vervangen:
belangrijk: sla een reservekopie op van uw werkblad en selecteer alleen de cellen die u wilt wijzigen voordat u zoeken en vervangen op formules gebruikt.,belangrijkste voordelen van benoemde bereiken
benoemde bereiken maken formules gemakkelijker te lezen
het grootste voordeel van benoemde bereiken is dat ze formules gemakkelijker te lezen en te onderhouden maken. Dit komt omdat ze cryptische verwijzingen vervangen door betekenisvolle namen. Denk bijvoorbeeld aan dit werkblad met gegevens over planeten in ons zonnestelsel., Zonder benoemde bereiken is een VLOOKUP-formule om “Position” uit de tabel op te halen vrij cryptisch:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
echter, met B3:E11 genaamd “data” en H4 genaamd “planet”, kunnen we formules als volgt schrijven:
=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites
in één oogopslag ziet u het enige verschil in deze formules in de kolomindex.
benoemde bereiken maak formules draagbaar en herbruikbaar
benoemde bereiken kan het veel gemakkelijker maken om een formule in een ander werkblad te hergebruiken., Als u namen van tevoren in een werkblad definieert, kunt u een formule plakken die deze namen gebruikt en deze zal “gewoon werken”. Dit is een geweldige manier om snel een formule werkend te krijgen.
deze formule telt bijvoorbeeld unieke waarden in een bereik van numerieke gegevens:
=SUM(--(FREQUENCY(data,data)>0))om deze formule Snel naar uw eigen werkblad te “porteren”, een bereik “data” te noemen en de formule in het werkblad te plakken. Zolang “data” numerieke waarden bevat, werkt de formule meteen.,
Tip: ik raad u aan de benodigde bereiknamen *eerst* in de doelwerkmap te maken en vervolgens in de formule alleen als tekst te kopiëren (dus kopieer de cel die de formule in een ander werkblad bevat niet, kopieer gewoon de tekst van de formule). Dit stopt Excel van het maken van namen on-the-fly en kunt u de volledige controle van de naam creatie proces. Als u alleen formuletekst wilt kopiëren, kopieert u tekst vanuit de formulebalk of kopieert u deze via een andere toepassing (bijvoorbeeld browser, teksteditor, enz.).
benoemde bereiken kunnen worden gebruikt voor navigatie
benoemde bereiken zijn geweldig voor snelle navigatie., Selecteer het vervolgkeuzemenu naast het vak naam en kies een naam. Wanneer u de muis loslaat, wordt het bereik geselecteerd. Wanneer een benoemd bereik op een ander blad bestaat, wordt u automatisch naar dat blad gebracht.
benoemde bereiken werken goed met hyperlinks
benoemde bereiken maken hyperlinks gemakkelijk. Bijvoorbeeld, als u A1 in Sheet1 “home” noemt, kunt u ergens anders een hyperlink maken die u daar terug brengt.,
om een benoemd bereik binnen de HYPERLINKFUNCTIE te gebruiken, voegt u een pound-symbool toe vóór het benoemde bereik:
=HYPERLINK("#home","take me home")opmerking: vreemd genoeg kunt u niet hyperlink naar een tabel zoals u kunt een normale bereiknaam. U kunt echter een naam definiëren die gelijk is aan een tabel (d.w.z. =Table1) en een hyperlink daarnaar. Als iemand een manier Weet om een tafel direct te koppelen, klokkenspel in!,
benoemde bereiken voor gegevensvalidatie
Namen bereiken werken goed voor gegevensvalidatie, omdat ze u een logisch benoemde referentie laten gebruiken om invoer te valideren met een drop-down menu. Hieronder krijgt het bereik G4:G8 de naam “statuslist”, pas dan gegevensvalidatie toe met een lijst die als volgt is gekoppeld:
het resultaat is een vervolgkeuzemenu in kolom E dat alleen waarden in het benoemde bereik toestaat:
dynamische benoemde bereiken
naambereiken zijn uiterst nuttig wanneer ze zich automatisch aanpassen aan nieuwe gegevens in een werkblad., Een bereik dat op deze manier is ingesteld wordt aangeduid als een “dynamisch benoemde bereik”. Er zijn twee manieren om een bereik dynamisch te maken: formules en tabellen.
dynamisch benoemde bereik met een tabel
een tabel is de makkelijkste manier om een dynamisch benoemde bereik aan te maken. Selecteer een cel in de gegevens, gebruik dan de sneltoets Control + t:
wanneer u een Excel-tabel maakt, wordt automatisch een naam gemaakt (bijvoorbeeld Table1), maar u kunt de tabel hernoemen zoals u wilt. Zodra u een tabel hebt gemaakt, wordt deze automatisch uitgebreid wanneer gegevens worden toegevoegd.,
dynamisch benoemde bereik met een formule
U kunt ook een dynamisch benoemde bereik maken met formules, met behulp van functies zoals OFFSET en INDEX. Hoewel deze formules matig complex zijn, bieden ze een lichtgewicht oplossing wanneer u geen tabel wilt gebruiken., De links hieronder geven voorbeelden met volledige uitleg:
- voorbeeld van dynamische bereikformule met INDEX
- voorbeeld van dynamische bereikformule met OFFSET
tabelnamen in gegevensvalidatie
aangezien Excel-Tabellen een automatisch dynamisch bereik bieden, lijken ze een natuurlijke fit te zijn voor gegevensvalidatieregels, waarbij het doel is te valideren tegen een lijst die altijd kan veranderen. Een probleem met tabellen is echter dat u gestructureerde referenties niet direct kunt gebruiken om gegevensvalidatie of voorwaardelijke opmaakregels te maken., Met andere woorden, u kunt geen tabelnaam gebruiken in invoergebieden voor voorwaardelijke opmaak of gegevensvalidatie.
als tijdelijke oplossing kunt u echter een benoemde bereik definiëren dat naar een tabel wijst en vervolgens het benoemde bereik gebruiken voor gegevensvalidatie of voorwaardelijke opmaak. De video hieronder loopt in detail door deze aanpak.
Video: Hoe benoemde bereiken te gebruiken met tabellen
benoemde bereiken verwijderen
Opmerking: Als u formules hebt die verwijzen naar benoemde bereiken, wilt u misschien eerst de formules bijwerken voordat u Namen verwijdert. Anders zie je #NAME?, fouten in formules die nog steeds verwijzen naar verwijderde namen. Sla altijd uw werkblad op voordat u benoemde bereiken verwijdert voor het geval u problemen hebt en naar het origineel moet terugkeren.
benoemde bereiken worden aangepast bij het verwijderen en invoegen van cellen
wanneer u *deel* van een benoemd bereik verwijdert, of als u cellen/rijen/kolommen invoegt binnen een benoemd bereik, wordt de bereikreferentie dienovereenkomstig aangepast en blijft geldig. Als u echter alle cellen verwijdert die een benoemd bereik omsluiten, verliest het benoemde bereik de referentie en wordt een #REF-fout weergegeven., Als ik bijvoorbeeld A1 “test” noem en kolom A verwijder, zal de naambeheerder “refereert naar” tonen als:
=Sheet1!#REF!Verwijder namen met Name Manager
om benoemde bereiken handmatig uit een werkmap te verwijderen, opent u de naambeheerder, selecteert u een bereik en klikt u op de knop Verwijderen. Als u meer dan één naam tegelijkertijd wilt verwijderen, kunt u Shift + Click or Ctrl + Click om meerdere namen te selecteren en vervolgens in één stap verwijderen.,
namen met fouten verwijderen
Als u veel namen met referentiefouten hebt, kunt u de filterknop in de name manager gebruiken om namen met fouten te filteren:
dan shift+klik om alle namen te selecteren en te verwijderen.
benoemde bereiken en bereik
benoemde bereiken in Excel hebben iets genaamd “bereik”, wat bepaalt of een benoemde bereik lokaal is voor een bepaald werkblad, of globaal over de hele werkmap. Globale namen hebben een bereik van “werkmap” en lokale namen hebben een bereik dat gelijk is aan de bladnaam waarop ze bestaan., Bijvoorbeeld, het bereik voor een lokale naam zou kunnen zijn “Sheet2”.
het doel van bereik
benoemde bereiken met een globaal bereik zijn nuttig als u wilt dat alle werkbladen in een werkmap toegang hebben tot bepaalde gegevens, variabelen of constanten. U kunt bijvoorbeeld een globaal benoemd bereik gebruiken, een aanname van het belastingtarief dat in verschillende werkbladen wordt gebruikt.
lokaal bereik
lokaal bereik betekent dat een naam alleen werkt op het werkblad waarop het is gemaakt. Dit betekent dat u meerdere werkbladen in dezelfde werkmap kunt hebben die allemaal dezelfde naam gebruiken., Misschien hebt u bijvoorbeeld een werkmap met maandelijkse volgbladen (één per maand) die benoemde bereiken met dezelfde naam gebruiken, allemaal lokaal. Hierdoor kunt u dezelfde formules in verschillende bladen hergebruiken. Met het lokale bereik kunnen de namen in elk werkblad correct werken zonder te botsen met namen in de andere werkbladen.
om naar een naam met een lokaal bereik te verwijzen, kunt u de bladnaam voorvoegen in de bereiknaam:
Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenuebereiknamen die met het vak Naam worden gemaakt, hebben automatisch een globaal bereik., Om dit gedrag te negeren, voegt u de bladnaam toe bij het definiëren van de naam:
Sheet3!my_new_nameglobaal bereik
globaal bereik betekent dat een naam overal in een werkmap zal werken. U kunt bijvoorbeeld een cel een naam geven “last_update”, een datum invoeren in de cel. Vervolgens kunt u de onderstaande formule gebruiken om de datum voor het laatst bijgewerkt in een werkblad weer te geven.
=last_updateglobale namen moeten uniek zijn binnen een werkmap.
lokaal bereik
lokaal bereik benoemde bereiken zijn zinvol voor werkbladen die benoemde bereiken alleen gebruiken voor lokale aannames., Misschien hebt u bijvoorbeeld een werkmap met maandelijkse volgbladen (één per maand) die benoemde bereiken met dezelfde naam gebruiken, allemaal lokaal. Met het lokale bereik kunnen de namen in elk werkblad correct werken zonder te botsen met namen in de andere werkbladen.
managing named range scope
standaard zijn nieuwe Namen die met het namebox worden gemaakt globaal en kunt u de scope van een benoemde bereik niet bewerken nadat het is gemaakt. Als tijdelijke oplossing kunt u echter een naam met het gewenste bereik verwijderen en opnieuw maken.,
Als u meerdere namen tegelijk wilt wijzigen van globaal naar lokaal, is het soms zinvol om het werkblad met de namen te kopiëren. Wanneer u een werkblad dupliceert dat benoemde bereiken bevat, kopieert Excel de benoemde bereiken naar het tweede blad en wijzigt u tegelijkertijd het bereik in lokaal. Nadat u het tweede blad met lokaal vastgestelde Namen hebt, kunt u het eerste blad optioneel verwijderen.
Jan Karel Pieterse en Charles Williams hebben een utility ontwikkeld genaamd De Name Manager die veel nuttige bewerkingen biedt voor named ranges. U kunt de Name Manager utility hier downloaden.,
