Přejít k hlavnímu obsahu

Jak vytvořit dynamický pojmenovaný rozsah v aplikaci Excel?

Autor: Xiaoyang Naposledy změněno: 2013-12-09

Za normálních okolností, Pojmenované rozsahy jsou velmi užitečné pro uživatele aplikace Excel, můžete definovat řadu hodnot ve sloupci, pojmenovat tento sloupec a poté můžete odkazovat na tento rozsah podle názvu místo jeho odkazů na buňky. Většinu času však musíte přidat nová data, abyste v budoucnu rozšířili datové hodnoty vašeho doporučeného rozsahu. V takovém případě se musíte vrátit zpět na Vzorce > Správce jmen a předefinujte rozsah tak, aby zahrnoval novou hodnotu. Chcete-li tomu zabránit, můžete vytvořit dynamický pojmenovaný rozsah, což znamená, že nemusíte upravovat odkazy na buňky pokaždé, když do seznamu přidáte nový řádek nebo sloupec.

Vytvořte dynamický pojmenovaný rozsah v aplikaci Excel vytvořením tabulky

Vytvořte dynamický pojmenovaný rozsah v aplikaci Excel pomocí funkce

Vytvořte dynamický pojmenovaný rozsah v aplikaci Excel pomocí kódu VBA


šipka modrá pravá bublina Vytvořte dynamický pojmenovaný rozsah v aplikaci Excel vytvořením tabulky

Pokud používáte Excel 2007 nebo novější verze, nejjednodušší způsob, jak vytvořit dynamický pojmenovaný rozsah, je vytvořit pojmenovanou tabulku Excel.

Řekněme, že máte řadu následujících dat, která se musí stát dynamickým pojmenovaným rozsahem.

doc-dynamic-range1

1. Nejprve definuji názvy rozsahů pro tento rozsah. Vyberte rozsah A1: A6 a zadejte název Datum do Název Box, Poté stiskněte tlačítko vstoupit klíč. Stejným způsobem definujete název rozsahu B1: B6 jako Saleprice. Zároveň vytvářím vzorec = součet (saleprice) v prázdné buňce, viz screenshot:

doc-dynamic-range2

2. Vyberte rozsah a klikněte Vložit > Tabulka, viz screenshot:

doc-dynamic-range3

3. V Vytvořit tabulku zaškrtněte políčko Můj stůl má záhlaví (pokud rozsah nemá záhlaví, zrušte jeho zaškrtnutí), klikněte OK tlačítko a data rozsahu byla převedena do tabulky. Zobrazit snímky obrazovky:

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

4. A když po datech zadáte nové hodnoty, pojmenovaný rozsah se automaticky upraví a změní se také vytvořený vzorec. Viz následující screenshoty:

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

Poznámky:

1. Vaše nová zadaná data musí sousedit s výše uvedenými daty, to znamená, že mezi novými daty a existujícími daty nejsou žádné prázdné řádky nebo sloupce.

2. V tabulce můžete vložit data mezi stávající hodnoty.


šipka modrá pravá bublina Vytvořte dynamický pojmenovaný rozsah v aplikaci Excel pomocí funkce

V aplikaci Excel 2003 nebo dřívější verzi nebude první metoda k dispozici, takže je tu pro vás další způsob. Následující OFFSET () funkce může udělat tuto laskavost za vás, ale je to poněkud problematické. Předpokládám, že mám rozsah dat, který obsahuje názvy rozsahů, které jsem definoval, například A1: A6 název rozsahu je Datum, a B1: B6 název rozsahu je Prodejní cenazároveň vytvářím vzorec pro Prodejní cena. Viz snímek obrazovky:

doc-dynamic-range2

Názvy rozsahů můžete změnit na názvy dynamických rozsahů pomocí následujících kroků:

1. Přejít na kliknutí Vzorce > Správce jmen, viz screenshot:

doc-dynamic-range8

2. V Správce jmen V dialogovém okně vyberte položku, kterou chcete použít, a klikněte na Změnit .

doc-dynamic-range9

3. Ve vyskočené Upravit jméno zadejte tento vzorec = OFFSET (List1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) do Odkazuje na textové pole, viz screenshot:

doc-dynamic-range10

4. Pak klikněte na tlačítko OKa potom opakujte kroky 2 a 3 a zkopírujte tento vzorec = OFFSET (List1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) do Odkazuje na textové pole pro Prodejní cena název rozsahu.

5. A byly vytvořeny dynamické pojmenované rozsahy. Když po datech zadáte nové hodnoty, pojmenovaný rozsah se automaticky upraví a změní se také vytvořený vzorec. Zobrazit snímky obrazovky:

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

Poznámka: Pokud jsou uprostřed vašeho rozsahu prázdné buňky, bude výsledek vašeho vzorce chybný. Je to proto, že neprázdné buňky se nepočítají, takže váš rozsah bude kratší, než by měl, a poslední buňky v rozsahu budou vynechány.

Tip: vysvětlení pro tento vzorec:

  • = OFFSET (reference, řádky, sloupky, [výška], [šířka])
  • -1
  • = OFFSET (List1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
  • reference odpovídá počáteční pozici buňky, v tomto příkladu List1! $ A $ 1;
  • řádek odkazuje na počet řádků, které se chystáte posunout dolů, relativně k počáteční buňce (nebo nahoru, pokud použijete zápornou hodnotu.), v tomto příkladu 0 označuje, že seznam bude začínat od prvního řádku dolů
  • sloupec odpovídá počtu sloupců, které přesunete doprava vzhledem k počáteční buňce (nebo doleva pomocí záporné hodnoty.), ve výše uvedeném vzorci vzorec 0 označuje rozbalení 0 sloupců doprava.
  • [výška] odpovídá výšce (nebo počtu řádků) rozsahu začínajícího v upravené poloze. $ A: $ A, spočítá všechny položky zadané ve sloupci A.
  • [šířka] odpovídá šířce (nebo počtu sloupců) rozsahu začínajícího v upravené poloze. Ve výše uvedeném vzorci bude seznam široký 1 sloupec.

Tyto argumenty můžete podle potřeby změnit.


šipka modrá pravá bublina Vytvořte dynamický pojmenovaný rozsah v aplikaci Excel pomocí kódu VBA

Pokud máte více sloupců, můžete opakovat a zadat individuální vzorec pro všechny zbývající sloupce, ale to by byl dlouhý a opakující se proces. Pro usnadnění práce můžete pomocí kódu automaticky vytvořit dynamický pojmenovaný rozsah.

1. Aktivujte svůj list.

2. Podržte ALT + F11 klíče a otevře se Okno Microsoft Visual Basic pro aplikace.

3, klikněte Vložit > Modula vložte následující kód do Okno modulu.

Kód VBA: vytvoření dynamického pojmenovaného rozsahu

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. Pak stiskněte tlačítko F5 klíč ke spuštění kódu a budou vygenerovány některé dynamické pojmenované rozsahy, které jsou pojmenovány s hodnotami prvního řádku a také vytvoří dynamický rozsah s názvem MyData který pokrývá celá data.

5. Když za řádky nebo sloupce zadáte nové hodnoty, rozšíří se také rozsah. Zobrazit snímky obrazovky:

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

Poznámky:

1. U tohoto kódu se názvy rozsahů nezobrazí v Název Box, abych si mohl pohodlně prohlížet a používat názvy rozsahů, nainstaloval jsem Kutools pro Excel, S jeho Navigační podokno, jsou uvedeny názvy vytvořených dynamických rozsahů.

2. S tímto kódem lze celý rozsah dat rozšířit svisle nebo vodorovně, ale nezapomeňte, že při zadávání nových hodnot by mezi daty neměly být prázdné řádky nebo sloupce.

3. Pokud použijete tento kód, měl by váš datový rozsah začínat v buňce A1.


Související článek:

Jak automaticky aktualizovat graf po zadání nových dat v aplikaci Excel?

Nejlepší nástroje pro produktivitu v kanceláři

🤖 Kutools AI asistent: Revoluční analýza dat založená na: Inteligentní provedení   |  Generovat kód  |  Vytvořte vlastní vzorce  |  Analyzujte data a generujte grafy  |  Vyvolejte funkce Kutools...
Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

Popis


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
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
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations