Vo svojom nedávnom článku som hovoril všetko o pomenovaných rozsahoch v programe Excel. Pri skúmaní pomenovaných rozsahov vyskočila téma dynamického rozsahu. V tomto článku teda vysvetlím, ako môžete vytvoriť dynamický rozsah v programe Excel.
Čo je dynamický pomenovaný rozsah v programe Excel?
Normálny pomenovaný rozsah je statický. Ak definujete C2: C10 ako Položka, položka bude vždy odkazovať na C2: C10, pokiaľ a pokiaľ ho neupravíte ručne. Na obrázku nižšie počítame medzery v súborePoložka zoznam. Ukazuje 2. Ak by to bolo dynamické, ukázalo by to 0.
Dynamický rozsah názvov je rozsah názvov, ktorý sa podľa údajov rozširuje a zmenšuje. Napríklad, ak máte zoznam položiek v rozsahu C2: C10 a pomenujte ho Položky, mal by sa sám rozšíriť na C2: C11 ak pridáte novú položku v dosahu a mala by sa zmenšiť, ak zmenšíte, keď odstránite, ako je uvedené vyššie.
Ako vytvoriť dynamický rozsah mien
Vytvorte pomenované rozsahy pomocou tabuliek programu Excel
Áno, tabuľky programu Excel môžu vytvárať dynamické pomenované rozsahy. Každý stĺpec v tabuľke s názvom rozsah urobí vysoko dynamický.
Existuje však jedna nevýhoda názvov tabuliek, že ich nemôžete použiť pri overovaní údajov a podmienenom formátovaní. Môžu sa tam však použiť konkrétne pomenované rozsahy.
Použite NEPRIAMY a vzorec COUNTA
Aby bol rozsah názvov dynamický, môžeme použiť funkciu INDIRECT a COUNTA
. Ako? Pozrime sa.
Obecný vzorec je napísaný v časti Odkazuje na:
= NEPRIAMY ("$ startingCell: $ endsColumnLetter $" & COUNTA ($ columnLetter: $ columnLetter))
Vyššie uvedený generický vzorec môže vyzerať zložito, ale v skutočnosti je ľahký. Pozrime sa na príklad.
Základnou myšlienkou je určiť poslednú použitú bunku.
Príklad dynamického rozsahu
Vo vyššie uvedenom príklade sme mali statický názov rozsahu Položka v rozsahu C2: C10. Urobme to dynamické.
-
- Otvorte Správcu mien stlačením klávesov CTRL+F3.
- Ak už názov v rozsahu existuje, kliknite naň a potom kliknite na položku Upraviť. V opačnom prípade kliknite na položku Nové.
- Pomenujte položku.
- V časti Odkazy na: Sekcia napíšte nižšie Vzorec.
= NEPRIAMY ("$ C2: $ C $" & COUNTA ($ C: $ C))
- Stlačte tlačidlo OK.
A je hotovo. Teraz, kedykoľvek budete zadávať položku do poľa s názvom alebo do akéhokoľvek vzorca, bude odkazovať na C2 k naposledy použitej bunke v rozsahu.
Pozor: Žiadna bunka by nemala byť prázdna v rozsahu. V opačnom prípade sa rozsah zníži o počet prázdnych buniek.
Ako to funguje?
Ako som povedal, je to jediná vec, nájsť poslednú použitú bunku. V tomto prípade by žiadne bunky nemali byť prázdne. Prečo? Budete vedieť.
Funkcia INDIRECT v programe Excel prevádza text na rozsah. = NEPRIAMY („$ C $ 2: $ C $ 9“) bude odkazovať na absolútny rozsah $ C $ 2: $ C $ 10. Potrebujeme len dynamicky nájsť číslo posledného riadka (9).
Pretože všetky bunky majú určitú hodnotu v rozsahu C2: C10, môžeme na nájdenie posledného riadka použiť funkciu COUNTA.
Takže,= NEPRIAMY(„$ C2: $ C $“ & táto časť opravuje počiatočný riadok a stĺpec a COUNTA($ C: $ C) dynamický počíta posledný použitý riadok.
Takže áno, takto môžete vytvoriť najefektívnejšie dynamicky pomenované rozsahy, ktoré budú fungovať s každým vzorcom a funkciami Excelu. Pri zmene údajov nemusíte pomenovaný rozsah znova upravovať.
Stiahnuť súbor:
Dynamické pomenované rozsahy v programe ExcelAko používať pomenované rozsahy v programe Excel
17 úžasných funkcií tabuliek programu Excel
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity
Ako používať funkciu VLOOKUP v programe Excel
Ako používať funkciu COUNTIF v programe Excel
Ako používať funkciu SUMIF v programe Excel
