Ugrás a tartalomra

Hogyan lehet dinamikus nevű tartományt létrehozni az Excelben?

Normális esetben, Nevezett tartományok nagyon hasznosak az Excel felhasználók számára, meghatározhat egy értéksorozatot egy oszlopban, megadhat annak az oszlopnak nevet, majd a cellahivatkozások helyett név szerint hivatkozhat az adott tartományra. De legtöbbször új adatokat kell hozzáadnia a hivatkozott tartomány adatértékeinek bővítéséhez a jövőben. Ebben az esetben vissza kell térnie képletek > Névkezelő és újradefiniálja a tartományt az új érték felvételére. Ennek elkerülése érdekében létrehozhat egy dinamikus elnevezett tartományt, ami azt jelenti, hogy nem kell minden alkalommal módosítania a cellahivatkozásokat, amikor új sort vagy oszlopot ad hozzá a listához.

Táblázat létrehozásával hozzon létre dinamikus nevű tartományt az Excelben

Hozzon létre dinamikus nevű tartományt az Excelben a Funkcióval

Hozzon létre dinamikus nevű tartományt az Excelben VBA kóddal


nyíl kék jobb buborék Táblázat létrehozásával hozzon létre dinamikus nevű tartományt az Excelben

Ha Excel 2007 vagy újabb verziókat használ, akkor a dinamikus nevű tartomány létrehozásának legegyszerűbb módja egy megnevezett Excel táblázat létrehozása.

Tegyük fel, hogy van egy sor következő adata, amelyeknek dinamikus nevű tartományokká kell válnia.

doc-dynamic-range1

1. Először meghatározom a tartomány nevét ehhez a tartományhoz. Válassza ki az A1: A6 tartományt, és írja be a nevet találka be a név Box, Majd nyomja meg a gombot belép kulcs. A B1: B6 tartomány nevének meghatározása ugyanúgy Saleprice néven. Ugyanakkor létrehozok egy képletet = összeg (Saleprice) egy üres cellában lásd a képernyőképet:

doc-dynamic-range2

2. Válassza ki a tartományt és kattintson betétlap > Táblázat, lásd a képernyőképet:

doc-dynamic-range3

3. Az Táblázat létrehozása prompt doboz, jelölje be Az asztalom fejlécekkel rendelkezik (ha a tartománynak nincsenek fejlécei, törölje a jelölést), kattintson a gombra OK gombot, és a tartományadatok táblává lettek konvertálva. Képernyőképek megtekintése:

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

4. És amikor új értékeket ad meg az adatok után, a megnevezett tartomány automatikusan beáll, és a létrehozott képlet is megváltozik. Lásd a következő képernyőképeket:

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

Megjegyzések:

1. Az új bevitt adatoknak a fenti adatok mellett kell lenniük, ez azt jelenti, hogy az új és a meglévő adatok között nincs üres sor vagy oszlop.

2. A táblázatban beillesztheti az adatokat a meglévő értékek közé.


nyíl kék jobb buborék Hozzon létre dinamikus nevű tartományt az Excelben a Funkcióval

Az Excel 2003 vagy korábbi verzióiban az első módszer nem lesz elérhető, ezért íme egy másik módszer az Ön számára. A következő OFFSET () function megteheti ezt a szívességet az Ön számára, de kissé zavaró. Tegyük fel, hogy van egy adattartományom, amely tartalmazza az általam definiált tartományneveket, például A1: A6 a tartomány neve találkaés B1: B6 tartomány neve Eladási ár, ugyanakkor létrehozok egy képletet a Eladási ár. Lásd a képernyőképet:

doc-dynamic-range2

A tartományneveket dinamikus tartománynévvé változtathatja a következő lépésekkel:

1. Kattintson a gombra képletek > Névkezelő, lásd a képernyőképet:

doc-dynamic-range8

2. Az Névkezelő párbeszédpanelen válassza ki a használni kívánt elemet, majd kattintson a gombra szerkesztése gombot.

doc-dynamic-range9

3. A kiugrott Név szerkesztése párbeszédpanelen írja be ezt a képletet = ELTÉRÉS (1. lap! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) be a Utal rá szövegmező, lásd a képernyőképet:

doc-dynamic-range10

4. Ezután kattintson OK, majd ismételje meg a 2. és a 3. lépést a képlet másolásához = ELTOLÁS (1. lap! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) a Utal rá szövegmező a Eladási ár tartomány neve.

5. És létrehozták a dinamikus nevű tartományokat. Amikor az adatok után új értékeket ad meg, a megnevezett tartomány automatikusan beáll, és a létrehozott képlet is megváltozik. Képernyőképek megtekintése:

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

Jegyzet: Ha üres cellák vannak a tartomány közepén, a képlet eredménye rossz lesz. Ez azért van, mert a nem üres cellákat nem számoljuk, így a tartománya rövidebb lesz, mint kellene, és a tartomány utolsó cellái kimaradnak.

Tipp: a képlet magyarázata:

  • = OFFSET (referencia, sorok, oszlopok, [magasság], [szélesség])
  • -1
  • = ELTÉRÉS (1. lap! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
  • referencia megfelel a kiindulási sejt helyzetének, ebben a példában 1. lap! $ A $ 1;
  • sor a lefelé mozgatni kívánt sorok számára vonatkozik a kezdő cellához képest (vagy felfelé, ha negatív értéket használ.), ebben a példában 0 azt jelzi, hogy a lista az első sorból lefelé indul
  • oszlop megfelel az oszlopok számának, amelyeket jobbra, a kezdő cellához (vagy balra, negatív értéket használva) viszünk át. A fenti példa képletében 0 azt jelzi, hogy a 0 oszlop kibővül jobbra.
  • [magasság] megfelel a beállított pozíciótól kezdődő tartomány magasságának (vagy sorainak számának). $ A: $ A, az A oszlopba beírt összes elemet megszámolja.
  • [szélesség] megfelel a tartomány szélességének (vagy oszlopainak számának) a beállított pozíciótól kezdve. A fenti képletben a lista 1 oszlop széles lesz.

Ezeket az érveket igény szerint módosíthatja.


nyíl kék jobb buborék Hozzon létre dinamikus nevű tartományt az Excelben VBA kóddal

Ha több oszlopa van, megismételheti és megadhatja az összes többi oszlop egyedi képletét, de ez hosszú, ismétlődő folyamat lenne. A dolgok megkönnyítése érdekében egy kód segítségével automatikusan létrehozhatja a dinamikus nevű tartományt.

1. Aktiválja a munkalapot.

2. Tartsa lenyomva a ALT + F11 gombokat, és ez megnyitja a Microsoft Visual Basic for Applications ablak.

3. Kattints betétlap > Modulok, és illessze be a következő kódot a Modul ablak.

Vba-kód: dinamikus nevű tartomány létrehozása

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. Ezután nyomja meg a gombot F5 kulcs a kód futtatásához, és generálódnak néhány dinamikus elnevezett tartományok, amelyeket az első sor értékekkel neveznek el, és létrehoz egy dinamikus tartományt, Adataim amely a teljes adatot lefedi.

5. Amikor a sorok vagy oszlopok után új értékeket ad meg, a tartomány is kibővül. Képernyőképek megtekintése:

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

Megjegyzések:

1. Ezzel a kóddal a tartománynevek nem jelennek meg a név Box, a tartománynevek kényelmes megtekintéséhez és használatához telepítettem Kutools az Excel számára, Annak navigációs ablak, a létrehozott dinamikus tartománynevek fel vannak sorolva.

2. Ezzel a kóddal az adatok teljes tartománya függőlegesen vagy vízszintesen bővíthető, de ne feledje, hogy új értékek megadásakor nem szabad üres sorokat vagy oszlopokat elhelyezni az adatok között.

3. A kód használatakor az adattartománynak az A1 cellában kell kezdődnie.


Kapcsolódó cikk:

Hogyan lehet automatikusan frissíteni a diagramot az új adatok Excelbe történő bevitele után?

A legjobb irodai hatékonyságnövelő eszközök

Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup    Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ....
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ....
Oszlopkezelő: Adjon meg egy adott számú oszlopot  |  Oszlopok mozgatása  |  Kapcsolja be a Rejtett oszlopok láthatósági állapotát  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár    Munkafüzet és lapkezelő   |  Erőforrás-könyvtár (Auto szöveg)   |  Dátumválasztó   |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása    E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt/áthúzott szűrés...) ...
A 15 legjobb eszközkészlet12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   ... és több

Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz.  Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...

kte lap 201905


Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
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