Hur man skapar namngivna områden i Excel (en steg-för-steg-guide)

Vad står i namnet?

Om du arbetar med Excel -kalkylblad kan det betyda mycket tidsbesparing och effektivitet.

I den här självstudien lär du dig hur du skapar namngivna områden i Excel och hur du använder det för att spara tid.

Namngivna intervall i Excel - En introduktion

Om någon måste ringa mig eller hänvisa till mig, kommer de att använda mitt namn (istället för att säga att en man bor på så och så ställe med så och så längd och vikt).

Rätt?

På samma sätt kan du i Excel ge ett namn till en cell eller ett cellområde.

Nu, istället för att använda cellreferensen (som A1 eller A1: A10), kan du helt enkelt använda namnet som du tilldelade den.

Anta till exempel att du har en datamängd som visas nedan:

I den här datamängden, om du måste referera till intervallet som har datumet, måste du använda A2: A11 i formler. På samma sätt måste du för säljare och säljare använda B2: B11 och C2: C11.

Även om det är okej när du bara har ett par datapunkter, men om du har enorma komplexa datamängder kan det vara tidskrävande att använda cellreferenser för att hänvisa till data.

Excel Named Ranges gör det enkelt att hänvisa till datamängder i Excel.

Du kan skapa ett namngivet område i Excel för varje datakategori och sedan använda det namnet istället för cellreferenser. Till exempel kan datum få namnet "Datum", data från säljare kan kallas "försäljningsrepp" och säljdata kan kallas "försäljning".

Du kan också skapa ett namn för en enda cell. Till exempel, om du har försäljningsprovisionsprocenten i en cell, kan du namnge den cellen som "provision".

Fördelar med att skapa namngivna intervall i Excel

Här är fördelarna med att använda namngivna intervall i Excel.

Använd namn istället för cellreferenser

När du skapar namngivna områden i Excel kan du använda dessa namn istället för cellreferenser.

Till exempel kan du använda = SUMMA (FÖRSÄLJNING) istället för = SUMMA (C2: C11) för ovanstående datamängd.

Ta en titt på formlerna nedan. Istället för att använda cellreferenser har jag använt Named Ranges.

• Antal försäljningar med ett värde mer än 500: = RÄNTA (försäljning, ”> 500 ″)
• Summan av all försäljning som gjorts av Tom: = SUMIF (SalesRep, ”Tom”, Sales)
• Provision intjänad av Joe (försäljning med Joe multiplicerad med provisionprocent):
  = SUMIF (SalesRep, ”Joe”, Sales)*Provision

Du håller med om att dessa formler är lätta att skapa och lätta att förstå (särskilt när du delar den med någon annan eller återkommer till den själv.

Du behöver inte gå tillbaka till databasen för att välja celler

En annan stor fördel med att använda Named Ranges i Excel är att du inte behöver gå tillbaka och välja cellområden.

Du kan bara skriva ett par alfabet med det namngivna intervallet och Excel visar matchande namngivna intervall (som visas nedan):

Namngivna områden gör formler dynamiska

Genom att använda namngivna intervall i Excel kan du göra Excel -formler dynamiska.

Till exempel, när det gäller försäljningsprovision, kan du använda Named Range istället för att använda värdet 2,5%.

Om ditt företag senare beslutar att öka provisionen till 3%kan du helt enkelt uppdatera det namngivna intervallet och all beräkning uppdateras automatiskt för att återspegla den nya provisionen.

Hur man skapar namngivna intervall i Excel

Här är tre sätt att skapa namngivna intervall i Excel:

Metod #1 - Använda definiera namn

Här är stegen för att skapa namngivna intervall i Excel med Definiera namn:

• Välj det intervall som du vill skapa ett namngivet intervall för i Excel.
• Gå till Formler -> Definiera namn.
• I dialogrutan Nytt namn skriver du det namn du vill tilldela det valda dataområdet. Du kan ange omfattningen som hela arbetsboken eller ett specifikt kalkylblad. Om du väljer ett visst blad skulle namnet inte vara tillgängligt på andra blad.
• Klicka på OK.

Detta skapar ett Named Range SALESREP.

Metod 2: Använda namnrutan

• Välj det område som du vill skapa ett namn för (välj inte rubriker).
• Gå till namnrutan till vänster om formelfältet och skriv namnet på det som du vill skapa det namngivna intervallet med.
• Observera att namnet som skapats här är tillgängligt för hela arbetsboken. Om du vill begränsa det till ett kalkylblad använder du metod 1.

Metod #3: Använda alternativet Skapa från markering

Detta är det rekommenderade sättet när du har data i tabellform och du vill skapa namngivna intervall för varje kolumn/rad.

Till exempel, i datauppsättningen nedan, om du snabbt vill skapa tre namngivna intervall (Datum, Sales_Rep och Sales), kan du använda metoden som visas nedan.

Här är stegen för att snabbt skapa namngivna intervall från en datamängd:

• Välj hela datamängden (inklusive rubriker).
• Gå till formler -> Skapa från urval (Kortkommando - Control + Shift + F3). Det öppnar dialogrutan "Skapa namn från urval".
• Markera alternativen där du har rubrikerna i dialogrutan Skapa namn från urval. I det här fallet väljer vi endast den översta raden eftersom rubriken är i den översta raden. Om du har rubriker i både översta raden och vänstra kolumnen kan du välja båda. På samma sätt, om dina data är ordnade när rubrikerna bara finns i den vänstra kolumnen, markerar du bara alternativet Vänster kolumn.

Detta skapar tre namngivna intervall - Datum, Sales_Rep och Sales.

Observera att det automatiskt hämtar namn från rubrikerna. Om det finns ett mellanslag mellan ord infogar det en understrykning (eftersom du inte kan ha mellanslag i namngivna intervall).

Namnkonvention för namngivna intervall i Excel

Det finns vissa namnregler du behöver veta när du skapar namngivna områden i Excel:

• Det första tecknet i ett namngivet intervall bör vara en bokstav och understrykningstecken (_) eller ett bakåtstreck (\). Om det är något annat kommer det att visa ett fel. De återstående tecknen kan vara bokstäver, siffror, specialtecken, punkt eller understrykning.
• Du kan inte använda namn som också representerar cellreferenser i Excel. Till exempel kan du inte använda AB1 eftersom det också är en cellreferens.
• Du kan inte använda mellanslag när du skapar namngivna intervall. Till exempel kan du inte ha säljare som ett namngivet intervall. Om du vill kombinera två ord och skapa ett namngivet intervall använder du en understrykning, punkt eller stor bokstav för att skapa det. Du kan till exempel ha Sales_Rep, SalesRep eller SalesRep.
  • När du skapar namngivna intervall behandlar Excel stora och små bokstäver på samma sätt. Om du till exempel skapar ett namngivet intervall SALES, kommer du inte att kunna skapa ett annat namngivet intervall, till exempel "försäljning" eller "försäljning".
• Ett namngivet område kan vara upp till 255 tecken långt.

För många namngivna områden i Excel? Oroa dig inte

Ibland i stora datamängder och komplexa modeller kan du hamna med att skapa många namngivna områden i Excel.

Vad händer om du inte kommer ihåg namnet på Named Range du skapade?

Oroa dig inte - här är några användbara tips.

Få namnen på alla namngivna områden

Här är stegen för att få en lista över alla namngivna intervall du skapade:

• Gå till fliken Formler.
• Klicka på Använd i formel i gruppen Definierad namngiven.
• Klicka på "Klistra in namn".

Detta ger dig en lista över alla namngivna områden i den arbetsboken. För att använda ett namngivet intervall (i formler eller en cell), dubbelklicka på det.

Visar matchande namngivna intervall

• Om du har en aning om namnet skriver du in några initialtecken, så visas en rullgardinsmeny med matchande namn i Excel.

Så här redigerar du namngivna intervall i Excel

Om du redan har skapat ett namngivet område kan du redigera det med följande steg:

• Gå till fliken Formler och klicka på Namnhanteraren.
• Dialogrutan Namnhanterare visar alla namngivna områden i arbetsboken. Dubbelklicka på Named Range som du vill redigera.
• Gör ändringarna i dialogrutan Redigera namn.
• Klicka på OK.
• Stäng dialogrutan Namnhanterare.

Praktiska genvägar med namngivet område (kraften hos F3)

Här är några användbara tangentbordsgenvägar som kommer att vara praktiska när du arbetar med namngivna områden i Excel:

• För att få en lista över alla namngivna områden och klistra in den i Formel: F3
• Så här skapar du ett nytt namn med Namnhanterarens dialogruta: Control + F3
• Så här skapar du namngivna intervall från urval: Control + Shift + F3

Skapa dynamiska namngivna intervall i Excel

Hittills i denna handledning har vi skapat statiska namngivna intervall.

Det betyder att dessa namngivna områden alltid hänvisar till samma dataset.

Till exempel, om A1: A10 har namngetts som "Försäljning", skulle det alltid hänvisa till A1: A10.

Om du lägger till mer försäljningsdata måste du manuellt gå och uppdatera referensen i det angivna intervallet.

I en värld av ständigt växande datamängder kan det ta mycket av din tid. Varje gång du får ny data kan du behöva uppdatera namngivna områden i Excel.

För att lösa detta problem kan vi skapa dynamiska namngivna intervall i Excel som automatiskt tar hänsyn till ytterligare data och inkluderar det i det befintliga namngivna intervallet.

Till exempel, om jag till exempel lägger till ytterligare två försäljningsdatapunkter, hänvisar ett dynamiskt namnområde automatiskt till A1: A12.

Denna typ av dynamiskt namngivet område kan skapas med hjälp av Excel INDEX -funktionen. I stället för att ange cellreferenser medan du skapar det namngivna intervallet anger vi formeln. Formeln uppdateras automatiskt när data läggs till eller tas bort.

Låt oss se hur du skapar dynamiska namngivna intervall i Excel.

Antag att vi har försäljningsdata i cell A2: A11.

Här är stegen för att skapa dynamiska namngivna intervall i Excel:

1. 1. Gå till fliken Formel och klicka på Definiera namn.
   2. Skriv följande i dialogrutan Nytt namn:
      • Namn: Försäljning
      • Omfattning: Arbetsbok
      • Avser: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Klicka på OK.

Gjort!

Du har nu ett dynamiskt namnområde med namnet ‘Försäljning’. Detta uppdateras automatiskt när du lägger till data till det eller tar bort data från det.

Hur fungerar Dynamic Named Ranges?

För att förklara hur detta fungerar måste du veta lite mer om Excel INDEX -funktionen.

De flesta använder INDEX för att returnera ett värde från en lista baserat på rad och kolumnnummer.

Men INDEX -funktionen har också en annan sida.

Det kan användas för att returnera en cellreferens när den används som en del av en cellreferens.

Här är till exempel formeln som vi har använt för att skapa ett dynamiskt namnområde:

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

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Denna del av formeln förväntas returnera ett värde (vilket skulle vara det tionde värdet från listan, med tanke på att det finns tio objekt).

Men när den används framför en referens (=$ A $ 2:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) returnerar referensen till cellen istället för värdet.

Därför returnerar den här = $ A $ 2: $ A $ 11

Om vi ​​lägger till ytterligare två värden i försäljningskolumnen returnerar den = $ A $ 2: $ A $ 13

När du lägger till ny data i listan returnerar Excel RÄNTA-funktionen antalet icke-tomma celler i data. Detta nummer används av INDEX -funktionen för att hämta cellreferensen för det sista objektet i listan.

Notera:

• Detta fungerar bara om det inte finns några tomma celler i data.
• I exemplet ovan har jag tilldelat ett stort antal celler (A2: A100) för Named Range -formeln. Du kan justera detta baserat på din datamängd.

Du kan också använda OFFSET -funktionen för att skapa ett dynamiskt namngivet område i Excel, men eftersom OFFSET -funktionen är flyktig kan det leda till en långsam Excel -arbetsbok. INDEX, å andra sidan, är halvflyktigt, vilket gör det till ett bättre val att skapa dynamiska namngivna intervall i Excel.

Du kanske också gillar följande Excel -resurser:

• Gratis Excel -mallar.
• Gratis Excel-utbildning online (7-delars online videokurs).
• Användbara Excel -makrokodsexempel.
• 10 Avancerade Excel VLOOKUP Exempel.
• Skapa en rullgardinsmeny i Excel.
• Skapa ett namngivet intervall i Google Kalkylark.
• Hur man hänvisar till ett annat blad eller en arbetsbok i Excel