Kako ustvariti dinamični imenovani obseg v Excelu?

običajno Imenski obsegi so zelo uporabni za uporabnike Excela, lahko v stolpcu določite vrsto vrednosti, mu daste ime in se nato na ta obseg sklicujete po imenu namesto na sklice na celice. Toda večino časa morate dodati nove podatke, da v prihodnosti razširite vrednosti podatkov iz navedenega obsega. V tem primeru se morate vrniti na Formule > Upravitelj imen in ponovno določite obseg, da vključite novo vrednost. Da bi se temu izognili, lahko ustvarite dinamični imenovani obseg, kar pomeni, da vam ni treba vsakič prilagoditi sklicev na celice, ko na seznam dodate novo vrstico ali stolpec.

Ustvarite dinamični imenovani obseg v Excelu z ustvarjanjem tabele

Ustvarite dinamični imenovani obseg v Excelu s funkcijo

Ustvarite dinamični imenovani obseg v Excelu s kodo VBA

Ustvarite dinamični imenovani obseg v Excelu z ustvarjanjem tabele

Če uporabljate Excel 2007 ali novejšo različico, je najlažji način za ustvarjanje dinamičnega imenovanega obsega ustvariti poimenovano tabelo Excel.

Recimo, da imate nabor naslednjih podatkov, ki morajo postati dinamični imenovani obseg.

1. Najprej bom določil imena obsegov za ta obseg. Izberite obseg A1: A6 in vnesite ime Datum v ime Box, Nato pritisnite Vnesite tipko. Če želite na enak način določiti ime za obseg B1: B6 kot prodajno ceno. Hkrati ustvarim formulo = vsota (prodajna cena) v prazni celici si oglejte posnetek zaslona:

2. Izberite obseg in kliknite Vstavi > Tabela, glej posnetek zaslona:

3. v Ustvari tabelo potrditveno polje, potrdite Moja tabela ima glave (če obseg nima glav, ga počistite), kliknite OK in podatki o obsegu so pretvorjeni v tabelo. Oglejte si posnetke zaslona:

4. In ko vnesete nove vrednosti po podatkih, se imenovani obseg samodejno prilagodi in spremeni se tudi ustvarjena formula. Oglejte si naslednje posnetke zaslona:

Opombe:

1. Novi vneseni podatki morajo biti v bližini zgornjih podatkov, kar pomeni, da med novimi in obstoječimi podatki ni praznih vrstic ali stolpcev.

2. V tabelo lahko vstavite podatke med obstoječe vrednosti.

Ustvarite dinamični imenovani obseg v Excelu s funkcijo

V Excelu 2003 ali starejši različici prva metoda ne bo na voljo, zato je tukaj še en način za vas. Naslednji OFFSET () funkcija vam lahko naredi to uslugo, vendar je nekoliko težavno. Recimo, da imam obseg podatkov, ki vsebuje imena obsegov, ki sem jih definiral, na primer A1: A6 ime obsega je Datumin B1: B6 ime obsega je Prodajna cena, hkrati ustvarim formulo za Prodajna cena. Oglejte si posnetek zaslona:

Imena obsegov lahko spremenite v imena dinamičnega obsega z naslednjimi koraki:

1. Pojdi na klik Formule > Upravitelj imen, glej posnetek zaslona:

2. v Upravitelj imen v pogovornem oknu izberite element, ki ga želite uporabiti, in kliknite Uredi gumb.

3. V izskočil Uredi ime vnesite to formulo = OFFSET (List1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) v Se nanaša na besedilno polje, glej posnetek zaslona:

4. Nato kliknite OKin nato ponovite 2. in 3. korak, da kopirate to formulo = OFFSET (List1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) v Se nanaša na besedilno polje za Prodajna cena ime obsega.

5. In dinamični imenovani obsegi so bili ustvarjeni. Ko po podatkih vnesete nove vrednosti, se imenovani obseg samodejno prilagodi in spremeni se tudi ustvarjena formula. Oglejte si posnetke zaslona:

Opomba: Če so na sredini vašega območja prazne celice, bo rezultat formule napačen. To je zato, ker se neprazne celice ne štejejo, zato bo vaš obseg krajši, kot bi moral, in zadnje celice v obsegu bodo izključene.

Nasvet: razlaga za to formulo:

• = OFFSET (sklic, vrstice, stolpci, [višina], [širina])
• 
• = OFFSET (List1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)

• reference v tem primeru ustreza začetnemu položaju celice List1! $ A $ 1;
• vrstica se nanaša na število vrstic, ki jih boste premaknili navzdol glede na začetno celico (ali navzgor, če uporabljate negativno vrednost.), v tem primeru 0 pomeni, da se bo seznam začel od prve vrstice navzdol
• stolpec ustreza številu stolpcev, ki jih boste premaknili v desno, glede na začetno celico (ali v levo z negativno vrednostjo.), v zgornjem primeru formula 0 pomeni razširitev 0 stolpcev na desno.
• [višina] ustreza višini (ali številu vrstic) obsega, ki se začne na prilagojenem položaju. $ A: $ A, štelo bo vse postavke, vnesene v stolpec A.
• [premer] ustreza širini (ali številu stolpcev) obsega, ki se začne na prilagojenem položaju. V zgornji formuli bo seznam širok 1 stolpec.

Te argumente lahko spremenite po svoji potrebi.

Ustvarite dinamični imenovani obseg v Excelu s kodo VBA

Če imate več stolpcev, lahko ponovite in vnesete posamezne formule za vse preostale stolpce, vendar bi bil to dolg, ponavljajoč se postopek. Za lažje stvari lahko s kodo samodejno ustvarite dinamični imenovani obseg.

1. Aktivirajte svoj delovni list.

2. Držite tipko ALT + F11 in odpre tipko Okno Microsoft Visual Basic for Applications.

3. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Okno modula.

Koda Vba: ustvari dinamični imenovani obseg

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. Nato pritisnite F5 tipka za zagon kode in ustvarilo se bo nekaj dinamičnih imenovanih obsegov, ki so poimenovani s prvimi vrednostmi vrstic, ustvarja pa tudi dinamični obseg, imenovan Moji podatki ki zajema celotne podatke.

5. Ko po vrsticah ali stolpcih vnesete nove vrednosti, se razširi tudi obseg. Oglejte si posnetke zaslona:

Opombe:

1. S to kodo imena obsegov niso prikazana v ime Box, da sem si lažje ogledal in uporabil imena obsegov, sem namestil Kutools za Excel, Z njegovim Podokno za krmarjenje, navedena so ustvarjena imena dinamičnega obsega.

2. S to kodo lahko celoten obseg podatkov razširite navpično ali vodoravno, vendar ne pozabite, da med vnosom novih vrednosti med podatki ne sme biti praznih vrstic ali stolpcev.

3. Ko uporabljate to kodo, se mora obseg podatkov začeti v celici A1.

Povezani članek:

Kako samodejno posodobiti grafikon po vnosu novih podatkov v Excelu?