namngivna intervall är en av dessa crusty gamla funktioner i Excel som få användare förstår. Nya användare kan hitta dem 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 vid testning och prototypformler. De hjälper mig att få formler att fungera snabbare., Jag använder också namngivna intervall eftersom jag är lat och inte gillar att skriva i komplexa referenser 🙂

grunderna för namngivna intervall i Excel

Vad är ett namngivet intervall?

ett namngivet intervall är bara ett läsbart namn för en rad celler i Excel. Om jag till exempel namnger 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






skönheten i 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änd det bara som en cellreferens. Alla dessa formler är giltiga med namnet range ”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 intervall


det är snabbt och enkelt att skapa ett namngivet intervall. Välj bara ett antal celler och skriv ett namn i rutan Namn. När du trycker på return skapas namnet:




för att snabbt testa det nya intervallet väljer du det nya namnet i rullgardinsmenyn bredvid namnrutan. Excel väljer intervallet på kalkylbladet.,


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


Om du har välstrukturerade data med etiketter kan du ha Excel skapa namngivna intervall för dig. Välj bara data tillsammans med etiketterna och använd kommandot” Skapa från val ” på fliken Formler i bandet:




Du kan också använda kortkommandot control + shift + F3.


med den här funktionen kan vi skapa namngivna intervall för befolkningen i 12 stater i ett steg:




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




med namn som skapats kan du använda dem i formler som detta


=SUM(MN,WI,MI)




uppdatera namngivna intervall i namnhanteraren (Control + F3)


När du har skapat ett namngivet område använder du namnhanteraren för att skapa ett namn. (kontroll + 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.,






det finns ingen anledning att klicka på knappen Redigera för att uppdatera en referens. När du klickar på Stäng uppdateras områdesnamnet.


Obs! Om du väljer ett helt namngivet område i ett kalkylblad kan du dra till en ny plats och referensen uppdateras automatiskt. Men jag vet 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, chime in nedan!


 se alla namngivna intervall (control + F3)


 för att snabbt se alla namngivna intervall i en arbetsbok, använd rullgardinsmenyn bredvid namnrutan.,


om du vill se mer i detalj, öppna namnhanteraren (Control + F3), som listar alla namn med referenser, och ger ett filter också: 




Obs: på en Mac finns det ingen Namnhanterare, så du ser dialogrutan Definiera namn istället.


kopiera och klistra in alla namngivna intervall (F3)


 om du vill ha en mer bestående post med 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:




När du klickar på knappen Klistra in lista ser du namnen och referenserna som klistras in i kalkylbladet:




se namn direkt på kalkylbladet


om du ställer in zoomnivån till mindre än 40%, visar Excel Områdesnamn direkt på kalkylbladet:




Tack för det här tipset, Felipe!,


namn har regler 


När du skapar namngivna intervall följer du dessa regler: 


namngivna intervall i formler 


namngivna intervall är lätta att använda i formler 


låt till exempel säga att du namnger en cell i din arbetsbok ”uppdaterad”. Tanken är att du kan sätta det aktuella datumet i cellen (Ctrl + 😉 och hänvisa till datumet någon annanstans i arbetsboken.




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 och den visas korrekt., När du ändrar datumet i ”uppdaterad” uppdateras meddelandet varhelst formeln används. Se den här sidan för fler exempel.


 namngivna intervall visas när du skriver en formel


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




namngivna intervall kan fungera som konstanter 


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






då kan du använda dessa namn var du vill i formler och uppdatera deras värde på en central plats.,




namngivna intervall är som standard absoluta 


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$2




 


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


om vi namnger D2 ”MPG” blir formeln: 


=C5/MPG






eftersom MPG är absolut som standard kan formeln kopieras ned i kolumn D som den är.


namngivna intervall kan också vara relativa 


även om namngivna intervall är absoluta som standard kan de också vara relativa. Ett relativt namngivet intervall avser ett intervall som är relativt positionen för den aktiva cellen vid den tidpunkt då intervallet skapas. Som ett resultat är relativa namngivna intervall användbara bygga generiska formler som fungerar var de flyttas.,


till exempel kan du skapa ett generiskt ”CellAbove” namngivet intervall så här:


    

  1. välj cell A2
    2. 

  2. kontroll + F3 för att öppna namnhanteraren
    3. 

  3. flik i ”refererar till” avsnitt, skriv sedan: =A1
    4. 



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


viktigt: kontrollera att den aktiva cellen är på rätt plats innan du skapar namnet.


använd namngivna intervall till befintliga formler 


Om du har befintliga formler som inte använder namngivna intervall kan du be Excel att använda namngivna intervall i formlerna för dig., Börja med att välja de celler som innehåller formler som du vill uppdatera. Kör sedan formler > Definiera namn > tillämpa namn.




Excel kommer sedan att ersätta referenser som har ett motsvarande namngivet intervall med själva namnet.


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




viktigt: spara en säkerhetskopia av kalkylbladet och välj bara de celler du vill ändra innan du använder Sök och ersätt på formler.,


viktiga fördelar med namngivna intervall 


namngivna intervall gör formlerna enklare att läsa 


den största enskilda fördelen med namngivna intervall är att de gör formlerna 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å detta kalkylblad 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$4,$B$3:$E$11,2,0)






men med B3:E11 som heter ”data” och H4 som 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








i korthet 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 före tid i ett kalkylblad kan du klistra in i en formel som använder dessa namn och det kommer att ”bara fungera”. Detta är ett bra sätt att snabbt få en formel som fungerar.


denna formel räknar till exempel unika värden i en rad numeriska data: 


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




för att snabbt ”port” denna formel till ditt eget kalkylblad, namnge ett intervall ”data” och klistra in formeln i kalkylbladet. Så länge som” 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, kopiera sedan i 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åter dig helt styra namnskapande processen. För att kopiera endast formeltext, kopiera text från formelfältet, eller kopiera via ett annat program (dvs. webbläsare, textredigerare, etc.).


namngivna intervall 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 ett namngivet intervall finns på ett annat ark tas du automatiskt till det arket.




namngivna intervall fungerar bra med hyperlänkar 


namngivna intervall gör hyperlänkar enkelt. Om du till exempel heter A1 i Arket1 ”hem” kan du skapa en hyperlänk någon annanstans som tar dig tillbaka dit.,






om du vill använda ett namngivet område i HYPERLÄNKFUNKTIONEN lägger du till en pundssymbol framför det namngivna området: 


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




Obs: märkligt nog kan du inte hyperlänka till en tabell som du kan en normal räckvidd namn. Du kan dock definiera ett namn som är lika med en tabell (dvs. = Table1) och hyperlänk till det. Om någon vet ett sätt att länka direkt ett bord, chime in!,


namngivna intervall för datavalidering 


Namnintervall 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 ”statuslist”, använd sedan datavalidering med en lista som är länkad så här:




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






dynamiska namngivna intervall


namnområden är extremt användbart när de automatiskt anpassar sig till nya data i ett kalkylblad., Ett intervall som ställs in på detta sätt kallas ett ”dynamiskt namngivet intervall”. Det finns två sätt att göra en rad dynamisk: formler och tabeller.


dynamiskt namngivet område med en tabell 


en tabell är det enklaste sättet att skapa ett dynamiskt namngivet område. Välj vilken cell som helst i data, använd sedan genvägskontrollen + T:




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 kommer den att expandera automatiskt när data läggs till.,




dynamiskt namngivet område med en formel 


Du kan också skapa ett dynamiskt namngivet område med formler, med hjälp av 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å dynamic range formula med INDEX 
    • 

  • exempel på dynamic range formula med OFFSET 
    • 



tabellnamn i datavalidering


 eftersom Excel-tabeller ger 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 inmatningsområden för villkorlig formatering eller datavalidering.


som en lösning kan du definiera ett namngivet intervall som pekar på en tabell och sedan använda det namngivna området 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


radera namngivna intervall


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


namngivna intervall justerar vid radering och infogning av celler 


När du tar bort *del* av ett namngivet område, eller om Infoga celler/rader / kolumner i ett namngivet område, justeras områdesreferensen i enlighet därmed och förblir giltig. Men om du tar bort alla celler som omsluter ett namngivet område kommer det namngivna området att förlora referensen och visa ett #REF-fel., Om jag till exempel heter A1 ”test” och sedan tar bort kolumn A visas namnhanteraren ”refererar till” som:




=Sheet1!#REF!




ta bort namn med Namnhanterare


om du vill ta bort namngivna intervall från en arbetsbok manuellt öppnar du namnhanteraren, väljer ett intervall och klickar på Delete-knappen. Om du vill ta bort mer än ett namn samtidigt kan du flytta + klicka eller Ctrl + klicka för att välja flera namn och sedan ta bort i ett steg.,


ta bort namn med fel 


om du har många namn med Referensfel kan du använda filterknappen i namnhanteraren för att filtrera på namn med fel: 




Skift+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 till ett visst kalkylblad eller globalt över hela arbetsboken. Globala namn har en omfattning av ”arbetsbok”, och lokala namn har ett område som är lika med det arknamn de finns på., Till exempel kan utrymmet för ett lokalt namn vara ”Sheet2”. 


syftet med omfattning 


namngivna intervall med ett globalt tillämpningsområde är användbart när du vill att alla ark i en arbetsbok ska ha tillgång till vissa data, variabler eller konstanter. Du kan till exempel använda ett globalt namngivet intervall ett skattesatsantagande som används i flera flera kalkylblad.


lokalt omfång 


lokalt omfång innebär att ett namn endast fungerar på arket som det skapades på. Det innebär 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 scoped lokalt. Detta kan göra att du kan återanvända samma formler i olika ark. Den lokala räckvidden gör att namnen i varje ark fungerar korrekt utan att kollidera med namn i de andra Arken.


för att referera till ett namn med ett lokalt omfång kan du prefixa tabellnamnet till områdesnamnet:


Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenue




Områdesnamn som skapats med namnrutan har automatiskt global räckvidd., Om du vill åsidosätta detta beteende lägger du till bladnamnet när du definierar namnet:


Sheet3!my_new_name




Global räckvidd


Global räckvidd innebär att ett namn fungerar var som helst i en arbetsbok. Du kan till exempel namnge en cell ”last_update”, ange ett datum i cellen. Då kan du använda formeln nedan för att visa det datum som senast uppdaterades i något kalkylblad.


=last_update




globala namn måste vara unika i en arbetsbok.


lokal räckvidd 


lokalt scoped namngivna intervall är meningsfullt 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 scoped lokalt. Den lokala räckvidden gör att namnen i varje ark fungerar korrekt utan att kollidera med namn i de andra Arken.


hantera namngiven räckvidd 


som standard är nya namn som skapats med nameboxen globala och du kan inte redigera omfattningen av ett namngivet område efter att det har skapats. Men som en lösning kan du ta bort och återskapa ett namn med önskad räckvidd.,


 om du vill ändra flera namn på en gång från global till lokal, är det ibland vettigt att kopiera arket som innehåller namnen. När du duplicerar ett kalkylblad som innehåller namngivna intervall kopierar Excel de namngivna intervallen till det andra arket och ändrar omfattningen till lokal samtidigt. När du har det andra arket med lokalt scoped 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 Namnet Manager här.,