Note: The other languages of the website are Google-translated. Back to English
Logga in  \/ 
x
or
x
Registrera  \/ 
x

or

Hur skapar man ett dynamiskt namnområde i Excel?

Normalt Namngivna områden är mycket användbara för Excel-användare, du kan definiera en serie värden i en kolumn, ge den kolumnen ett namn och sedan kan du hänvisa till det intervallet med namn istället för dess cellreferenser. Men för det mesta måste du lägga till nya data för att utöka datavärdena för ditt refererade intervall i framtiden. I det här fallet måste du gå tillbaka till Formler > Namnhanterare och omdefiniera intervallet för att inkludera det nya värdet. För att undvika detta kan du skapa ett dynamiskt namnintervall vilket innebär att du inte behöver justera cellreferenser varje gång du lägger till en ny rad eller kolumn i listan.

Skapa dynamiskt namnområde i Excel genom att skapa en tabell

Skapa dynamiskt namnområde i Excel med Funktion

Skapa dynamiskt namnområde i Excel med VBA-kod

Fliken Office Aktiverar flikredigering och surfning i Office, och gör ditt arbete mycket enklare ...
Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%
  • Återanvänd allt: Lägg till de mest använda eller komplexa formlerna, diagrammen och allt annat till dina favoriter och återanvänd dem snabbt i framtiden.
  • Mer än 20 textfunktioner: Extrahera nummer från textsträng; Extrahera eller ta bort en del av texterna; Konvertera siffror och valutor till engelska ord.
  • Sammanfoga verktyg: Flera arbetsböcker och ark till en; Slå ihop flera celler / rader / kolumner utan att förlora data; Slå samman duplicerade rader och summa.
  • Dela verktyg: Dela data i flera ark baserat på värde; En arbetsbok för flera Excel-, PDF- eller CSV-filer; En kolumn till flera kolumner.
  • Klistra över Dolda / filtrerade rader; Räkna och summa av bakgrundsfärg; Skicka personliga e-postmeddelanden till flera mottagare i bulk.
  • Superfilter: Skapa avancerade filterscheman och tillämpas på alla ark; Svart per vecka, dag, frekvens och mer; Filter med fetstil, formler, kommentarer ...
  • Mer än 300 kraftfulla funktioner; Fungerar med Office 2007-2019 och 365; Stöder alla språk; Enkel distribution i ditt företag eller organisation.

pil blå höger bubbla Skapa dynamiskt namnområde i Excel genom att skapa en tabell


Om du använder Excel 2007 eller senare versioner är det enklaste sättet att skapa ett dynamiskt namnområde att skapa en namngiven Excel-tabell.

Låt oss säga att du har en rad följande data som måste bli dynamiska namngivna.

doc-dynamic-range1

1. För det första kommer jag att definiera intervallnamn för detta intervall. Välj intervallet A1: A6 och ange namnet Datum i Namn Box, Tryck sedan på ange nyckel. För att definiera ett namn för intervall B1: B6 som Salepris på samma sätt. Samtidigt skapar jag en formel = summa (salepris) i en tom cell, se skärmdump:

doc-dynamic-range2

2. Välj intervallet och klicka Insert > Bord, se skärmdump:

doc-dynamic-range3

3. I Skapa bord snabbruta, kryssa Mitt bord har rubriker (om intervallet inte har rubriker, avmarkera det), klicka på OK knappen och områdesdata har konverterats till tabell. Se skärmdumpar:

doc-dynamic-range4 -2 doc-dynamic-range5

4. Och när du anger nya värden efter data kommer det namngivna området automatiskt att justeras och den skapade formeln kommer också att ändras. Se följande skärmdumpar:

doc-dynamic-range6 -2 doc-dynamic-range7

Anmärkningar:

1. Dina nya inmatningsdata måste ligga i anslutning till ovanstående data, det betyder att det inte finns några tomma rader eller kolumner mellan de nya uppgifterna och befintliga data.

2. I tabellen kan du infoga data mellan de befintliga värdena.


pil blå höger bubbla Skapa dynamiskt namnområde i Excel med Funktion

I Excel 2003 eller tidigare version är den första metoden inte tillgänglig, så här är ett annat sätt för dig. Det följande OFFSET () funktionen kan göra denna tjänst för dig, men det är lite besvärligt. Antag att jag har en rad data som innehåller intervallnamnen som jag har definierat, till exempel A1: A6 intervallnamnet är Datumoch B1: B6 intervallnamn är Försäljningspris, samtidigt skapar jag en formel för Försäljningspris. Se skärmdump:

doc-dynamic-range2

Du kan ändra intervallnamnen till dynamiska intervallnamn med följande steg:

1. Gå till klicka Formler > Namnhanterare, se skärmdump:

doc-dynamic-range8

2. I Namnhanterare i dialogrutan, välj det objekt som du vill använda och klicka på Redigera knapp.

doc-dynamic-range9

3. I poppade ut Redigera namn dialogrutan, ange denna formel = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) i Refererar till textruta, se skärmdump:

doc-dynamic-range10

4. Klicka sedan OKoch upprepa sedan steg 2 och steg 3 för att kopiera den här formeln = OFFSET (Sheet1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) in i Refererar till textruta för Försäljningspris intervallnamn.

5. Och de dynamiska namngivna områdena har skapats. När du anger nya värden efter data kommer det namngivna området automatiskt att justeras och den skapade formeln kommer också att ändras. Se skärmdumpar:

doc-dynamic-range6 -2 doc-dynamic-range7

Notera: Om det finns tomma celler i mitten av ditt intervall blir resultatet av din formel fel. Det beror på att de icke-tomma cellerna inte räknas, så ditt intervall blir kortare än det borde, och de sista cellerna i intervallet kommer att släppas.

Tips: förklaring till denna formel:

  • = OFFSET (referens, rader, kolar, [höjd], [bredd])
  • -1
  • = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
  • referens motsvarar startcellens position, i detta exempel Sheet1! $ A $ 1;
  • v refererar till antalet rader du ska flytta nedåt, relativt startcellen (eller uppåt, om du använder ett negativt värde.), i det här exemplet indikerar 0 att listan börjar från första raden nedåt
  • kolumn motsvarar antalet kolumner du kommer att flytta till höger, relativt startcellen (eller till vänster, med ett negativt värde.), i ovanstående formel anger 0 att expandera 0 kolumner till höger.
  • [höjd] motsvarar höjden (eller antalet rader) för intervallet som börjar vid den justerade positionen. $ A: $ A, det räknar alla objekt som anges i kolumn A.
  • [bredd] motsvarar bredden (eller antalet kolumner) för intervallet som börjar vid den justerade positionen. I ovanstående formel är listan 1 kolumn bred.

Du kan ändra dessa argument efter dina behov.


pil blå höger bubbla Skapa dynamiskt namnområde i Excel med VBA-kod

Om du har flera kolumner kan du upprepa och ange enskild formel för alla återstående kolumner, men det skulle vara en lång, repetitiv process. För att göra det enklare kan du använda en kod för att automatiskt skapa det dynamiska namngivna området.

1. Aktivera kalkylbladet.

2. Håll ner ALT + F11 knapparna och det öppnar Microsoft Visual Basic for Applications-fönstret.

3. Klicka Insert > Modulernaoch klistra in följande kod i Modulfönster.

Vba-kod: skapa dynamiskt namnområde

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

4. Tryck sedan på F5 nyckel för att köra koden, och det kommer att genereras några dynamiska namngivna intervall som namnges med de första radvärdena och det skapar också ett dynamiskt intervall som kallas Min data som täcker hela data.

5. När du anger nya värden efter raderna eller kolumnerna utökas också intervallet. Se skärmdumpar:

doc-dynamic-range12
-1
doc-dynamic-range13

Anmärkningar:

1. Med denna kod visas intervallnamnen inte i Namn Box, för att enkelt kunna se och använda intervallnamnen har jag installerat Kutools för Excel, med dess Navigeringsfönstret, listas de skapade dynamiska intervallnamnen.

2. Med den här koden kan hela utbudet av data utvidgas vertikalt eller horisontellt, men för att komma ihåg bör det inte ha tomma rader eller kolumner mellan data när du anger nya värden.

3. När du använder den här koden bör ditt dataområde börja i cell A1.


Relaterad artikel:

Hur uppdaterar jag automatiskt ett diagram efter att du har angett nya data i Excel?


De bästa Office-produktivitetsverktygen

Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%

  • återanvändning: Sätt snabbt i komplexa formler, diagram och allt som du har använt tidigare; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
  • Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
  • Sammanfoga celler / rader / kolumner utan att förlora data; Delat cellinnehåll; Kombinera duplicerade rader / kolumner... Förhindra duplicerade celler; Jämför intervall...
  • Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
  • Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
  • Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
  • Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
  • Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
  • Mer än 300 kraftfulla funktioner. Stöder Office / Excel 2007-2019 och 365. Stöder alla språk. Enkel distribution i ditt företag eller organisation. Fullständiga funktioner 30-dagars gratis provperiod. 60-dagars pengarna tillbaka-garanti.
kte-flik 201905

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50%och minskar hundratals musklick för dig varje dag!
officetab botten
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    mozart777 · 1 years ago
    really, really not helpful
  • To post as a guest, your comment is unpublished.
    loyiso · 5 years ago
    please help i am trying to create a dynamic named range on excel 2016 typing "=offset(DATAENTRY!$B$6,,,counta(DATAENTRY!$B$6:$B$13))" but still it gives me an error saying it is not a formula.
  • To post as a guest, your comment is unpublished.
    marge · 6 years ago
    You are a very good teacher: 1) step-by-step approach; 2) you do not bore the student with obvious material or conclusions; 3) yet you include all necessary material. I look forward to more tutorials from you.
  • To post as a guest, your comment is unpublished.
    Iran · 7 years ago
    Thanks for good article