Ve svém nedávném článku jsem mluvil o pojmenovaných rozsazích v aplikaci Excel. Při zkoumání pojmenovaných rozsahů vyskočilo téma dynamického rozsahu. V tomto článku tedy vysvětlím, jak můžete v aplikaci Excel vytvořit dynamický rozsah.
Co je dynamický pojmenovaný rozsah v aplikaci Excel?
Normální pojmenovaný rozsah je statický. Pokud definujete C2: C10 tak jako Položka, položka bude vždy odkazovat na C2: C10, dokud a pokud jej neupravíte ručně. Na obrázku níže počítáme mezery v souboruPoložka seznam. Zobrazuje 2. Pokud by byl dynamický, ukázal by 0.
Dynamický rozsah jmen je rozsah názvů, který se podle dat rozšiřuje a zmenšuje. Pokud například máte seznam položek v rozsahu C2: C10 a pojmenujte jej Položky, měl by se sám rozšířit na C2: C11 pokud přidáte novou položku v dosahu a měla by se zmenšit, pokud zmenšíte, když odstraníte, jak je uvedeno výše.
Jak vytvořit dynamický rozsah jmen
Vytvářejte pojmenované rozsahy pomocí tabulek aplikace Excel
Ano, tabulky Excelu mohou vytvářet dynamické pojmenované rozsahy. Každý sloupec vytvoří v tabulce s názvem rozsah, který je vysoce dynamický.
Existuje však jedna nevýhoda názvů tabulek, že je nemůžete použít při ověřování dat a podmíněném formátování. Lze tam ale použít konkrétní pojmenované rozsahy.
Použijte NEPŘÍMÝ a COUNTA vzorec
Aby byl rozsah názvů dynamický, můžeme použít funkci INDIRECT a COUNTA
. Jak? Uvidíme.
Obecný vzorec bude napsán v části Odkazuje na:
= NEPŘÍMÝ ("$ startingCell: $ endsColumnLetter $" & COUNTA ($ columnLetter: $ columnLetter))
Výše uvedený obecný vzorec může vypadat složitě, ale ve skutečnosti je jednoduchý. Podívejme se na příklad.
Základní myšlenkou je určit poslední použitou buňku.
Příklad dynamického rozsahu
Ve výše uvedeném příkladu jsme měli statický název rozsahu Položka v rozsahu C2: C10. Udělejme to dynamické.
-
- Otevřete Správce jmen stisknutím kláves CTRL+F3.
- Pokud již název v rozsahu existuje, klikněte na něj a poté klikněte na Upravit. Jinak klikněte na Nový.
- Pojmenujte to Item.
- V Odkazuje na: Část napište pod vzorec.
= NEPŘÍMÝ ("$ C2: $ C $" & COUNTA ($ C: $ C))
- Stiskněte tlačítko OK.
A je hotovo. Nyní, kdykoli zadáte položku do pole názvu nebo do jiného vzorce, bude odkazovat na C2 na poslední použitou buňku v rozsahu.
Pozor: Žádná buňka by neměla být prázdná mezi rozsahem. V opačném případě se rozsah sníží o počet prázdných buněk.
Jak to funguje?
Jak jsem řekl, jeho jedinou věcí je najít poslední použitou buňku. V tomto případě by žádné buňky neměly být prázdné. Proč? Budete vědět.
Funkce INDIRECT v aplikaci Excel převádí text na rozsah. = NEPŘÍMÝ („$ C $ 2: $ C $ 9“) bude odkazovat na absolutní rozsah $ C $ 2: $ C $ 10. Musíme jen dynamicky najít číslo posledního řádku (9).
Protože všechny buňky mají nějakou hodnotu v rozsahu C2: C10, můžeme k vyhledání posledního řádku použít funkci COUNTA.
Tak,= NEPŘÍMÝ(„$ C2: $ C $“ & tato část opravuje počáteční řádek a sloupec a COUNTA($ C: $ C) dynamicky vypočítá poslední použitý řádek.
Takže ano, takto můžete vytvořit nejefektivnější dynamicky pojmenované rozsahy, které budou fungovat s každým vzorcem a funkcemi Excelu. Při změně dat nemusíte pojmenovaný rozsah znovu upravovat.
Stáhnout soubor:
Dynamicky pojmenované rozsahy v aplikaci ExcelJak používat pojmenované rozsahy v aplikaci Excel
17 Úžasné funkce tabulek aplikace Excel
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity
Jak používat funkci VLOOKUP v Excelu
Jak používat funkci COUNTIF v aplikaci Excel
Jak používat funkci SUMIF v aplikaci Excel