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
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.
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:
2. Válassza ki a tartományt és kattintson betétlap > Táblázat, lásd a képernyőképet:
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:
 |
 |
 |
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:
 |
|
 |
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é.
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:
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:
2. Az Névkezelő párbeszédpanelen válassza ki a használni kívánt elemet, majd kattintson a gombra szerkesztése gombot.
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:
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:
|
|
|
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])
- = 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.
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:
 |
|
 |
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
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...
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!
