Preskoči na glavno vsebino

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


puščica modri desni mehurček 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.

doc-dinamično območje1

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:

doc-dinamično območje2

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

doc-dinamično območje3

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:

doc-dinamično območje4 -2 doc-dinamično območje5

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

doc-dinamično območje6 -2 doc-dinamično območje7

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.


puščica modri desni mehurček 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:

doc-dinamično območje2

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

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

doc-dinamično območje8

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

doc-dinamično območje9

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:

doc-dinamično območje10

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:

doc-dinamično območje6 -2 doc-dinamično območje7

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


puščica modri desni mehurček 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:

doc-dinamično območje12
-1
doc-dinamično območje13

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?

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
really, really not helpful
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Thanks for good article
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations