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

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

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.

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:

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

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:

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:

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.

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:

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:

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

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:

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:

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

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:

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?