Excel

Namngivna områden i Excel

Named Ranges Excel

Namngivna intervall är en av dessa knapriga gamla funktioner i Excel som få användare förstår. Nya användare kan tycka att de är konstiga och skrämmande, och även gamla händer kan undvika dem eftersom de verkar meningslösa och komplexa.



Men namngivna intervall är faktiskt en ganska cool funktion. De kan göra formler * mycket * lättare att skapa, läsa och underhålla. Och som en bonus gör de formler lättare att återanvända (mer bärbara).

Faktum är att jag använder namngivna intervall hela tiden när jag testar och prototyper formler. De hjälper mig att få formler att fungera snabbare. Jag använder också namngivna intervall eftersom jag är lat och gillar inte att skriva in komplexa referenser :)





Grunderna i namngivna intervall i Excel

Vad är ett namngivet intervall?

Ett namngivet intervall är bara ett läsbart namn för ett antal celler i Excel. Om jag till exempel heter intervallet A1: A100 'data', kan jag använda MAX för att få det maximala värdet med en enkel formel:

 
 = MAX (data) // max value

Enkelt namngivet intervall kallas



Det fina med namngivna intervall är att du kan använda meningsfulla namn i dina formler utan att tänka på cellreferenser. När du har ett namngivet intervall använder du det bara som en cellreferens. Alla dessa formler är giltiga med det angivna intervallet 'data':

 
= MAX (data) // max value = MIN (data) // min value = COUNT (data) // total values = AVERAGE (data) // min value

Video: Hur man skapar ett namngivet intervall

Det är enkelt att skapa ett namngivet område

Att skapa ett namngivet intervall är snabbt och enkelt. Välj bara ett cellområde och skriv ett namn i namnrutan. När du trycker på retur skapas namnet:

Skapa ett namnområde snabbt med namnrutan

För att snabbt testa det nya sortimentet, välj det nya namnet i rullgardinsmenyn bredvid namnrutan. Excel väljer intervallet i kalkylbladet.

Excel kan skapa namn automatiskt (ctrl + shift + F3)

Om du har välstrukturerade data med etiketter kan du låta Excel skapa namngivna intervall för dig. Välj bara data tillsammans med etiketterna och använd kommandot 'Skapa från markering' på fliken Formler i menyfliksområdet:

Skapa namn från urvalskommandot på menyfliksområdet

Du kan också använda tangentbordsgenvägskontrollen + shift + F3.

Med denna funktion kan vi skapa namngivna intervall för befolkningen i 12 stater i ett steg:

excel vba do tills cell = värde

Skapa namn från urval med data och etiketter markerade

När du klickar på OK skapas namnen. Du hittar alla nyskapade namn i rullgardinsmenyn bredvid namnrutan:

Nya namn visas också i rullgardinsmenyn med namn

Med namn skapade kan du använda dem i formler som detta

 
= SUM (MN,WI,MI)

Uppdatera namngivna intervall i Name Manager (Control + F3)

När du har skapat ett namngivet intervall använder du Namnhanterare (Control + F3) för att uppdatera efter behov. Välj det namn du vill arbeta med, ändra sedan referensen direkt (dvs. redigera 'refererar till'), eller klicka på knappen till höger och välj ett nytt intervall.

Uppdaterade namngivna intervall med Namnhanteraren

Du behöver inte klicka på knappen Redigera för att uppdatera en referens. När du klickar på Stäng uppdateras intervallnamnet.

Obs! Om du väljer ett helt namngivet område i ett kalkylblad kan du dra till en ny plats och referensen uppdateras automatiskt. Jag vet dock inte ett sätt att justera intervallreferenser genom att klicka och dra direkt på kalkylbladet. Om du vet ett sätt att göra detta, ring in nedan!

Se alla namngivna intervall (kontroll + F3)

Om du snabbt vill se alla namngivna intervall i en arbetsbok använder du rullgardinsmenyn bredvid namnrutan.

Om du vill se mer information öppnar du Namnhanteraren (Control + F3), som listar alla namn med referenser, och ger ett filter också:

Namnhanteraren visar alla nyskapade namn

Obs! På en Mac finns ingen namnhanterare, så du kommer att se dialogrutan Definiera namn istället.

Kopiera och klistra in alla namngivna områden (F3)

Om du vill ha en mer beständig registrering av namngivna intervall i en arbetsbok kan du klistra in hela listan med namn var du vill. Gå till Formler> Använd i formel (eller använd genvägen F3) och välj sedan Klistra in namn> Klistra in lista:

Dialogrutan Klistra in namn

När du klickar på knappen Klistra in listan ser du namnen och referenserna klistrade in i kalkylbladet:

Efter att ha klistrat in namngivna intervall i kalkylbladet

Se namn direkt på kalkylbladet

Om du ställer in zoomnivån till mindre än 40%, kommer Excel att visa intervallnamn direkt på kalkylbladet:

Vid zoomnivå <40%visar Excel intervallnamn

Tack för detta tips, Felipe!

Namn har regler

Följ dessa regler när du skapar namngivna intervall:

  1. Namn måste börja med en bokstav, en understrykning (_) eller ett snedstreck ()
  2. Namn får inte innehålla mellanslag och de flesta skiljetecken.
  3. Namn kan inte stå i konflikt med cellreferenser - du kan inte namnge ett område 'A1' eller 'Z100'.
  4. Enstaka bokstäver är OK för namn ('a', 'b', 'c', etc.), men bokstäverna 'r' och 'c' är reserverade.
  5. Namn är inte skiftlägeskänsliga-'hem', 'HOME' och 'HoMe' är alla desamma för Excel.

Namngivna intervall i formler

Namngivna intervall är enkla att använda i formler

Låt oss till exempel säga att du heter en cell i din arbetsbok 'uppdaterad'. Tanken är att du kan lägga det aktuella datumet i cellen (Ctrl +) och hänvisa till datumet någon annanstans i arbetsboken.

Använda ett namngivet intervall i en textformel

Formeln i B8 ser ut så här:

 
='Updated: '&  TEXT (updated, 'ddd, mmmm d, yyyy')

Du kan klistra in denna formel var som helst i arbetsboken så visas den korrekt. När du ändrar datumet i 'uppdaterat' uppdateras meddelandet varhelst formeln används. Ser denna sida för fler exempel.

Namngivna intervall visas när du skriver en formel

När du har skapat ett namngivet område visas det automatiskt i formler när du skriver den första bokstaven i namnet. Tryck på tabbtangenten för att ange namnet när du har en matchning och vill att Excel ska ange namnet.

Namngivna intervall visas när du anger formler

Namngivna intervall kan fungera som konstanter

Eftersom namngivna intervall skapas på en central plats kan du använda dem som konstanter utan cellreferens. Till exempel kan du skapa namn som 'MPG' (miles per gallon) och 'CPG' (kostnad per gallon) med och tilldela fasta värden:

Namngivna intervall kan fungera som konstanter, utan cellreferens

Sedan kan du använda dessa namn var som helst i formler och uppdatera deras värde på en central plats.

Använda ett namngivet intervall som en konstant i en formel

Namngivna intervall är absoluta som standard

Som standard beter sig namngivna intervall som absoluta referenser. Till exempel i detta kalkylblad skulle formeln för att beräkna bränsle vara:

 
=C5/$D

Standardformel med absolut adress

Hänvisningen till D2 är absolut (låst) så formeln kan kopieras ner utan att D2 ändras.

Om vi ​​heter D2 'MPG' blir formeln:

 
=C5/MPG

Använda ett namngivet intervall som en konstant i en formel

Eftersom MPG är absolut som standard kan formeln kopieras ner i kolumn D som den är.

Namngivna intervall kan också vara relativa

Även om namngivna områden är absoluta som standard kan de också vara relativa. Ett relativt namngivet område avser ett område som är relativt positionen för den aktiva cellen när intervallet skapas . Som ett resultat är relativa namngivna intervall användbara för att bygga generiska formler som fungerar vart de än flyttas.

Till exempel kan du skapa ett generiskt 'CellAbove' -namnintervall så här:

  1. Välj cell A2
  2. Ctrl + F3 för att öppna Name Manager
  3. Tab i avsnittet 'Hänvisar till' och skriv sedan: = A1

CellAbove hämtar nu värdet från cellen ovan var det än används.

Viktigt: se till att den aktiva cellen är på rätt plats innan du skapar namnet.

Tillämpa namngivna intervall på befintliga formler

Om du har befintliga formler som inte använder namngivna intervall kan du be Excel att tillämpa de namngivna intervallerna i formlerna för dig. Börja med att markera cellerna som innehåller formler som du vill uppdatera. Kör sedan Formler> Definiera namn> Tillämpa namn.

Dialogrutan Tillämpa namn

Excel ersätter sedan referenser som har ett motsvarande namnområde med själva namnet.

Du kan också använda namn med Sök och ersätt:

Tillämpa namn varierar med hitta och ersätt

Viktigt: Spara en säkerhetskopia av ditt kalkylblad och välj bara de celler du vill ändra innan du använder hitta och ersätt på formler.

Viktiga fördelar med namngivna intervall

Namngivna intervall gör formler lättare att läsa

Den största fördelen med namngivna intervall är att de gör formler lättare att läsa och underhålla. Detta beror på att de ersätter kryptiska referenser med meningsfulla namn. Tänk till exempel på det här kalkylbladet med data om planeter i vårt solsystem. Utan namngivna intervall är en VLOOKUP -formel för att hämta 'Position' från tabellen ganska kryptisk:

 
= VLOOKUP ($H,$B:$E,2,0)

Utan namngivna intervall kan formler vara kryptiska

hur formaterar subtotalkommandot listdata?

Men med B3: E11 som heter 'data' och H4 heter 'planet' kan vi skriva formler så här:

 
= VLOOKUP (planet,data,2,0) // position = VLOOKUP (planet,data,3,0) // diameter = VLOOKUP (planet,data,4,0) // satellites

Med namngivna intervall kan formler vara enkla

Med en blick kan du se den enda skillnaden i dessa formler i kolumnindexet.

Namngivna intervall gör formler bärbara och återanvändbara

Namngivna intervall kan göra det mycket lättare att återanvända en formel i ett annat kalkylblad. Om du definierar namn i förväg i ett kalkylblad kan du klistra in en formel som använder dessa namn och det kommer 'bara att fungera'. Detta är ett bra sätt att snabbt få en formel att fungera.

Till exempel räknar den här formeln unika värden i ett antal numeriska data:

 
= SUM (--( FREQUENCY (data,data)>0))

För att snabbt 'porta' denna formel till ditt eget kalkylblad, namnge ett område 'data' och klistra in formeln i kalkylbladet. Så länge 'data' innehåller numeriska värden fungerar formeln direkt.

Tips: Jag rekommenderar att du skapar de nödvändiga intervallnamnen * först * i målarbetsboken, kopierar sedan in formeln som endast text (dvs kopiera inte cellen som innehåller formeln i ett annat kalkylblad, kopiera bara texten i formeln ). Detta hindrar Excel från att skapa namn on-the-fly och l ger dig full kontroll över skapandet av namn. Om du bara vill kopiera formeltext kopierar du text från formelfältet eller kopierar via ett annat program (t.ex. webbläsare, textredigerare, etc.).

Namngivna områden kan användas för navigering

Namngivna intervall är bra för snabb navigering. Välj bara rullgardinsmenyn bredvid namnrutan och välj ett namn. När du släpper musen väljs intervallet. När det finns ett namngivet intervall på ett annat blad kommer du automatiskt till det bladet.

Namngivna intervall möjliggör enkel navigering

Namngivna intervall fungerar bra med hyperlänkar

Namngivna intervall gör hyperlänkar enkla. Om du till exempel heter A1 i Sheet1 'hem' kan du skapa en hyperlänk någon annanstans som tar dig tillbaka dit.

Skapa en hyperlänk till ett namngivet intervall

Exempel på namngiven intervallhyperlänk i kalkylbladet

Om du vill använda ett namngivet intervall i HYPERLINK -funktionen lägger du till en pund -symbol framför det angivna intervallet:

 
= HYPERLINK ('#home','take me home')

Obs: märkligt nog kan du inte länka till en tabell som du kan med ett normalt intervallnamn. Du kan dock definiera ett namn som är lika med en tabell (dvs. = tabell1) och en hyperlänk till det. Om någon vet ett sätt att länka ett bord direkt, hör av dig!

Namngivna områden för datavalidering

Namnområden fungerar bra för datavalidering, eftersom de låter dig använda en logiskt namngiven referens för att validera inmatning med en rullgardinsmeny. Nedan heter intervallet G4: G8 statuslista och applicera sedan datavalidering med en lista som är länkad så här:

Använda ett namngivet intervall för datavalidering med lista

Resultatet är en rullgardinsmeny i kolumn E som endast tillåter värden i det angivna intervallet:

Datavalidering med exempel på namnområde

Dynamiska namngivna områden

Namnområden är extremt användbara när de automatiskt anpassar sig till ny data i ett kalkylblad. Ett område som ställs in på detta sätt kallas ett 'dynamiskt namngivet område'. Det finns två sätt att göra ett intervall dynamiskt: formler och tabeller.

Dynamiskt namngivet område med en tabell

En tabell är det enklaste sättet att skapa ett dynamiskt namnområde. Markera vilken cell som helst i data och använd sedan genvägen Ctrl + T:

Skapa am Excel -tabell

När du skapar en Excel -tabell skapas ett namn automatiskt (t.ex. tabell1), men du kan byta namn på tabellen som du vill. När du har skapat en tabell expanderar den automatiskt när data läggs till.

Tabeller kommer att expanderas automatiskt och kan byta namn

Dynamiskt namngivet område med en formel

Du kan också skapa ett dynamiskt namnområde med formler med funktioner som OFFSET och INDEX. Även om dessa formler är måttligt komplexa, ger de en lätt lösning när du inte vill använda ett bord. Länkarna nedan ger exempel med fullständiga förklaringar:

  • Exempel på dynamiskt intervall med INDEX
  • Exempel på formel för dynamiskt intervall med OFFSET

Tabellnamn i datavalidering

Eftersom Excel -tabeller tillhandahåller ett automatiskt dynamiskt intervall verkar de vara en naturlig passform för datavalideringsregler, där målet är att validera mot en lista som alltid kan förändras. Ett problem med tabeller är dock att du inte kan använda strukturerade referenser direkt för att skapa datavalidering eller villkorliga formateringsregler. Med andra ord kan du inte använda ett tabellnamn i villkorlig formatering eller inmatningsområden för datavalidering.

Som en lösning kan du dock definiera namngiven ett namngivet område som pekar på en tabell och sedan använda det namngivna intervallet för datavalidering eller villkorlig formatering. Videon nedan går igenom detta tillvägagångssätt i detalj.

Video: Så här använder du namngivna intervall med tabeller

Tar bort namngivna intervall

Obs! Om du har formler som hänvisar till namngivna intervall, kanske du vill uppdatera formlerna innan du tar bort namn. Annars ser du #NAME? fel i formler som fortfarande hänvisar till raderade namn. Spara alltid ditt kalkylblad innan du tar bort namngivna områden om du har problem och behöver återgå till originalet.

Namngivna områden justeras när du tar bort och sätter in celler

När du tar bort * del * av ett namngivet område, eller om du sätter in celler/rader/kolumner i ett namngivet område, kommer områdesreferensen att justeras därefter och förbli giltig. Men om du tar bort alla celler som innehåller ett namngivet intervall kommer det namngivna intervallet att tappa referensen och visa ett #REF -fel. Om jag till exempel heter A1 'test', sedan tar bort kolumn A, kommer namnhanteraren att visa 'hänvisar till' som:

 
=Sheet1!#REF!

Ta bort namn med Name Manager

Om du vill ta bort namngivna intervall från en arbetsbok manuellt öppnar du namnhanteraren, väljer ett område och klickar på knappen Ta bort. Om du vill ta bort mer än ett namn samtidigt kan du Shift + Klicka eller Ctrl + Klicka för att välja flera namn och sedan radera i ett steg.

Radera namn med fel

Om du har många namn med referensfel kan du använda filterknappen i namnhanteraren för att filtrera efter namn med fel:

Namnhanterarens filtermeny

Skifta sedan+klicka för att välja alla namn och radera.

Namngivna intervall och omfattning

Namngivna intervall i Excel har något som kallas 'scope', som avgör om ett namngivet intervall är lokalt för ett givet kalkylblad eller globalt i hela arbetsboken. Globala namn har en omfattning av 'arbetsbok' och lokala namn har ett omfång som är lika med det bladnamn de finns på. Till exempel kan omfattningen för ett lokalt namn vara 'Sheet2'.

Syftet med omfattningen

Namngivna intervall med ett globalt omfång är användbara när du vill att alla blad i en arbetsbok ska ha tillgång till vissa data, variabler eller konstanter. Till exempel kan du använda ett globalt namngivet intervall ett antagande om skattesats som används i flera flera kalkylblad.

Lokalt omfång

Lokalt omfång betyder att ett namn bara fungerar på det blad det skapades på. Det betyder att du kan ha flera kalkylblad i samma arbetsbok som alla använder samma namn. Till exempel kanske du har en arbetsbok med månatliga spårningsblad (en per månad) som använder namngivna intervall med samma namn, alla omfattade lokalt. Detta kan tillåta dig att återanvända samma formler i olika blad. Den lokala omfattningen gör att namnen i varje blad fungerar korrekt utan att kollidera med namn i de andra arken.

För att hänvisa till ett namn med ett lokalt omfång kan du prefixa bladnamnet till intervallnamnet:

 
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue

Områdesnamn skapade med namnlåda automatiskt ha globalt omfång. För att åsidosätta detta beteende, lägg till bladnamnet när du definierar namnet:

 
Sheet3!my_new_name

Globalt omfång

Global omfattning innebär att ett namn fungerar var som helst i en arbetsbok. Du kan till exempel namnge en cell 'sista_uppdatering', ange ett datum i cellen. Sedan kan du använda formeln nedan för att visa datumet senast uppdaterat i valfritt arbetsblad.

 
=last_update

Globala namn måste vara unika i en arbetsbok.

Lokalt omfång

Lokalt omfattade namngivna intervall är vettiga för kalkylblad som endast använder namngivna intervall för lokala antaganden. Till exempel kanske du har en arbetsbok med månatliga spårningsblad (en per månad) som använder namngivna intervall med samma namn, alla omfattade lokalt. Den lokala omfattningen gör att namnen i varje blad fungerar korrekt utan att kollidera med namn i de andra arken.

Hantera namngivet intervall

Som standard är nya namn skapade med namnboxen globala och du kan inte redigera omfattningen av ett namngivet område efter att det har skapats. Som en lösning kan du dock ta bort och återskapa ett namn med önskat omfång.

Om du vill ändra flera namn samtidigt från globalt till lokalt, är det ibland vettigt att kopiera arket som innehåller namnen. När du kopierar ett kalkylblad som innehåller namngivna intervall, kopierar Excel de namngivna intervallerna till det andra arket och ändrar omfattningen till lokal samtidigt. När du har det andra arket med lokalt omfattande namn kan du eventuellt ta bort det första arket.

Jan Karel Pieterse och Charles Williams har utvecklat ett verktyg som heter Name Manager som ger många användbara operationer för namngivna intervall. Du kan ladda ner Name Manager -verktyget här .

Författare Dave Bruns


^