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

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.

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:

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

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:

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:

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.

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:

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

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

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

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:

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:

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])
• 
• = 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.

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:

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?