Hvordan oprettes dynamisk navngivet rækkevidde i Excel?
Normalt Navngivne områder er meget nyttige for Excel-brugere, kan du definere en række værdier i en kolonne, give den kolonne et navn, og derefter kan du henvise til det område ved navn i stedet for dets cellereferencer. Men mest tid skal du tilføje nye data for at udvide dataværdierne for dit henviste område fremover. I dette tilfælde skal du gå tilbage til formler > Navn Manager og omdefiner området for at inkludere den nye værdi. For at undgå dette kan du oprette et dynamisk navngivet interval, hvilket betyder at du ikke behøver at justere cellereferencer hver gang du tilføjer en ny række eller kolonne til listen.
Opret dynamisk navngivet interval i Excel ved at oprette en tabel
Opret dynamisk navngivet interval i Excel med funktion
Opret dynamisk navngivet interval i Excel med VBA-kode
Opret dynamisk navngivet interval i Excel ved at oprette en tabel
Hvis du bruger Excel 2007 eller nyere versioner, er den nemmeste måde at oprette et dynamisk navngivet interval på at oprette en navngivet Excel-tabel.
Lad os sige, at du har en række af følgende data, der skal blive dynamisk navngivet rækkevidde.
1. For det første vil jeg definere rækkevidden for dette interval. Vælg området A1: A6, og indtast navnet Dato ind i Navn Box, Og tryk derefter på Indtast nøgle. Sådan defineres et navn for område B1: B6 som Salepris på samme måde. Samtidig opretter jeg en formel = sum (Salepris) i en tom celle, se skærmbillede:
2. Vælg området, og klik indsatte > Bordlampe, se skærmbillede:
3. I Opret tabel hurtigboks, tjek Mit bord har overskrifter (hvis området ikke har overskrifter, skal du fjerne markeringen), skal du klikke på OK knappen, og områdedataene er konverteret til en tabel. Se skærmbilleder:
 |
 |
 |
4. Og når du indtaster nye værdier efter dataene, justeres det navngivne interval automatisk, og den oprettede formel ændres også. Se følgende skærmbilleder:
 |
|
 |
Bemærkninger:
1. Dine nye indtastningsdata skal være ved siden af ovenstående data, det betyder, at der ikke er tomme rækker eller kolonner mellem de nye data og de eksisterende data.
2. I tabellen er du i stand til at indsætte data mellem de eksisterende værdier.
Opret dynamisk navngivet interval i Excel med funktion
I Excel 2003 eller tidligere version er den første metode ikke tilgængelig, så her er en anden måde for dig. Det følgende OFFSET () funktion kan gøre denne tjeneste for dig, men det er noget besværligt. Antag, at jeg har en række data, der indeholder de rækkevidde, som jeg har defineret, for eksempel A1: A6 området er Datoog B1: B6 rækkevidde navn er Udsalgspris, på samme tid opretter jeg en formel til Udsalgspris. Se skærmbillede:
Du kan ændre navne på rækkevidde til navne på dynamisk område med følgende trin:
1. Gå til klik formler > Navn Manager, se skærmbillede:
2. I Navn Manager i dialogboksen, skal du vælge det emne, du vil bruge, og klikke på Redigere .
3. I poppet ud Rediger navn dialog, skal du indtaste denne formel = OFFSET (Ark1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) ind i Hentyder til tekstboks, se skærmbillede:
4. Klik derefter på OK, og gentag derefter trin2 og trin3 for at kopiere denne formel = OFFSET (Ark1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) ind i Hentyder til tekstfelt til Udsalgspris rækkevidde navn.
5. Og de dynamiske navngivne områder er oprettet. Når du indtaster nye værdier efter dataene, justeres det navngivne interval automatisk, og den oprettede formel ændres også. Se skærmbilleder:
|
|
|
Bemærk: Hvis der er tomme celler i midten af dit interval, vil resultatet af din formel være forkert. Det skyldes, at de ikke-tomme celler ikke tælles, så dit interval vil være kortere, end det burde, og de sidste celler i området vil blive udeladt.
Tip: forklaring på denne formel:
- = OFFSET (reference, rækker, cols, [højde], [bredde])
- = OFFSET (Ark1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
- henvisningen svarer til startcellepositionen, i dette eksempel Ark1! $ A $ 1;
- række refererer til antallet af rækker, du vil bevæge dig nedad i forhold til startcellen (eller opad, hvis du bruger en negativ værdi.), i dette eksempel indikerer 0, at listen starter fra første række ned
- kolonne svarer til antallet af kolonner, du vil flytte til højre i forhold til startcellen (eller til venstre ved hjælp af en negativ værdi.), i ovenstående eksempelformel angiver 0 udvid 0 kolonner til højre.
- [højde] svarer til højden (eller antallet af rækker) af området, der starter ved den justerede position. $ A: $ A, det tæller alle de poster, der er indtastet i kolonne A.
- [bredde] svarer til bredden (eller antallet af kolonner) i området, der starter ved den justerede position. I ovenstående formel er listen 1 kolonne bred.
Du kan ændre disse argumenter til dit behov.
Opret dynamisk navngivet interval i Excel med VBA-kode
Hvis du har flere kolonner, kan du gentage og indtaste individuel formel for alle de resterende kolonner, men det ville være en lang, gentagen proces. For at gøre tingene nemmere kan du bruge en kode til automatisk at oprette det dynamiske navngivne interval.
1. Aktivér dit regneark.
2. Hold nede ALT + F11 tasterne, og det åbner Vinduet Microsoft Visual Basic til applikationer.
3. Klik indsatte > Moduler, og indsæt følgende kode i Modul vindue.
Vba-kode: Opret dynamisk navngivet rækkevidde
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. Tryk derefter på F5 nøgle til at køre koden, og der genereres nogle dynamiske navngivne områder, der er navngivet med de første rækkeværdier, og det skaber også et dynamisk område kaldet Mydata der dækker hele dataene.
5. Når du indtaster nye værdier efter rækkerne eller kolonnerne, udvides området også. Se skærmbilleder:
 |
|
 |
Bemærkninger:
1. Med denne kode vises områdets navne ikke i Navn Box, for at se og bruge rækkevidden nemt, har jeg installeret Kutools til Excel, Med Navigationsrudevises de oprettede navne på det dynamiske område.
2. Med denne kode kan hele dataområdet udvides lodret eller vandret, men for at huske, skal der ikke være tomme rækker eller kolonner mellem dataene, når du indtaster nye værdier.
3. Når du bruger denne kode, skal dit dataområde starte i celle A1.
Relateret artikel:
Hvordan opdateres et diagram automatisk efter indtastning af nye data i Excel?
Bedste kontorproduktivitetsværktøjer
Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid. Klik her for at få den funktion, du har mest brug for...
Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere
- Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
- Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
- Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
