Obsah
Máte v Excelu tabulky s daty, u kterých lze měnit velikost, tj. počet řádků (sloupců) se může v průběhu práce zvětšovat nebo zmenšovat? Pokud se velikost tabulky „vznáší“, budete muset tento okamžik neustále sledovat a opravovat:
- odkazy ve vzorcích přehledů, které odkazují na naši tabulku
- počáteční rozsahy kontingenčních tabulek, které jsou sestaveny podle naší tabulky
- počáteční rozsahy grafů sestavených podle naší tabulky
- rozsahy pro rozevírací seznamy, které používají naši tabulku jako zdroj dat
To vše vás v součtu nenechá nudit 😉
Mnohem pohodlnější a správnější bude vytvořit dynamický „gumový“ rozsah, který se automaticky přizpůsobí velikosti skutečnému počtu řádků a sloupců dat. Chcete-li to provést, existuje několik způsobů.
Metoda 1. Inteligentní tabulka
Zvýrazněte svůj rozsah buněk a vyberte na kartě Domů – Formátovat jako tabulku (Domů – Formátovat jako tabulku):
Pokud nepotřebujete pruhovaný design, který je přidán do tabulky jako vedlejší efekt, můžete jej vypnout na zobrazené kartě Konstruktor (design). Každá takto vytvořená tabulka dostane název, který lze na stejném místě na kartě nahradit nějakým pohodlnějším Konstruktor (design) v oboru Název tabulky (název tabulky).
Nyní můžeme použít dynamické odkazy na naši „chytrou tabulku“:
- Tabulka 1 – odkaz na celou tabulku kromě řádku záhlaví (A2:D5)
- Tabulka1[#All] – odkaz na celou tabulku (A1:D5)
- Tabulka1 [Petr] – odkaz na sloupec rozsahu bez záhlaví první buňky (C2:C5)
- Tabulka1[#Headers] – odkaz na „záhlaví“ s názvy sloupců (A1:D1)
Takové odkazy fungují skvěle ve vzorcích, například:
= SUM (Tabulka1 [Moskva]) – výpočet součtu pro sloupec „Moskva“
or
=VPR(F5;Tabulka 1;3;0) – vyhledejte v tabulce měsíc z buňky F5 a zadejte pro něj petrohradský součet (co je SVYHLEDAT?)
Takové odkazy lze úspěšně použít při vytváření kontingenčních tabulek výběrem na kartě Vložit – kontingenční tabulka (Vložit – kontingenční tabulka) a zadání názvu chytré tabulky jako zdroje dat:
Pokud vyberete fragment takové tabulky (například první dva sloupce) a vytvoříte diagram libovolného typu, budou při přidávání nových řádků automaticky přidány do diagramu.
Při vytváření rozbalovacích seznamů nelze použít přímé odkazy na prvky chytré tabulky, ale toto omezení snadno obejdete pomocí taktického triku – použijte funkci NEPŘÍMÝ (NEPŘÍMÝ), který změní text na odkaz:
Tito. odkaz na chytrou tabulku v podobě textového řetězce (v uvozovkách!) se změní v plnohodnotný odkaz a rozevírací seznam to normálně vnímá.
Metoda 2: Dynamický pojmenovaný rozsah
Pokud je přeměna vašich dat na chytrou tabulku z nějakého důvodu nežádoucí, pak můžete použít trochu složitější, ale mnohem rafinovanější a univerzálnější metodu – vytvořit v Excelu dynamický pojmenovaný rozsah, který odkazuje na naši tabulku. Název vytvořeného rozsahu pak můžete stejně jako v případě chytré tabulky libovolně používat v libovolných vzorcích, sestavách, grafech apod. Začněme jednoduchým příkladem:
Úkol: vytvořit dynamický pojmenovaný rozsah, který by odkazoval na seznam měst a automaticky se roztahoval a zmenšoval při přidávání nových měst nebo jejich odstraňování.
Budeme potřebovat dvě vestavěné funkce Excelu dostupné v jakékoli verzi − POICPOZ (ZÁPAS) k určení poslední buňky rozsahu a INDEX (INDEX) vytvořit dynamický odkaz.
Nalezení poslední buňky pomocí MATCH
MATCH(hledaná_hodnota, rozsah, typ_shody) – funkce, která hledá danou hodnotu v rozsahu (řádku nebo sloupci) a vrací pořadové číslo buňky, kde byla nalezena. Například vzorec MATCH(“March”;A1:A5;0) ve výsledku vrátí číslo 4, protože slovo “March” se nachází ve čtvrté buňce ve sloupci A1:A5. Poslední argument funkce Match_Type = 0 znamená, že hledáme přesnou shodu. Není-li tento argument uveden, funkce se přepne do režimu hledání nejbližší nejmenší hodnoty – přesně to lze úspěšně použít k nalezení poslední obsazené buňky v našem poli.
Podstata triku je jednoduchá. MATCH hledá buňky v rozsahu shora dolů a teoreticky by se měl zastavit, když najde nejbližší nejmenší hodnotu k dané hodnotě. Pokud jako požadovanou hodnotu zadáte hodnotu, která je zjevně větší než kterákoli dostupná v tabulce, pak MATCH dosáhne úplného konce tabulky, nic nenajde a uvede pořadové číslo poslední vyplněné buňky. A my to potřebujeme!
Pokud jsou v našem poli pouze čísla, můžeme jako požadovanou hodnotu zadat číslo, které je samozřejmě větší než kterékoli z hodnot v tabulce:
Pro záruku lze použít číslo 9E + 307 (9 krát 10 na mocninu 307, tedy 9 s 307 nulami) – maximální číslo, se kterým Excel v zásadě umí pracovat.
Pokud jsou v našem sloupci textové hodnoty, pak jako ekvivalent největšího možného čísla můžete vložit konstrukci REPEAT(“i“, 255) – textový řetězec skládající se z 255 písmen „i“ – poslední písmeno abeceda. Vzhledem k tomu, že Excel ve skutečnosti porovnává kódy znaků při vyhledávání, jakýkoli text v naší tabulce bude technicky „menší“ než takový dlouhý řádek „yyyyyyyyy“:
Vygenerujte odkaz pomocí INDEXU
Nyní, když známe pozici posledního neprázdného prvku v tabulce, zbývá vytvořit odkaz na celý náš sortiment. K tomu použijeme funkci:
INDEX(rozsah; číslo_řádku; číslo_sloupce)
Udává obsah buňky z rozsahu podle čísla řádku a sloupce, tj. např. funkce =INDEX(A1:D5;3;4) v naší tabulce s městy a měsíci z předchozí metody dá 1240 – obsah ze 3. řádku a 4. sloupce, tedy buňky D3. Pokud existuje pouze jeden sloupec, jeho číslo lze vynechat, tj. vzorec INDEX(A2:A6;3) dá na posledním snímku „Samara“.
A je tu jedna ne zcela zřejmá nuance: pokud INDEX není zadán pouze do buňky za znaménkem =, jako obvykle, ale je použit jako poslední část odkazu na rozsah za dvojtečkou, pak již nevydává obsah buňky, ale její adresa! Vzorec jako $A$2:INDEX($A$2:$A$100;3) tedy poskytne na výstupu odkaz na rozsah A2:A4.
A zde přichází na řadu funkce MATCH, kterou vložíme dovnitř INDEXU, abychom dynamicky určili konec seznamu:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“Já”;255);A2:A100))
Vytvořte pojmenovaný rozsah
Zbývá to vše sbalit do jediného celku. Otevřete kartu vzorec (vzorce) A klikněte na tlačítko Správce jmen (správce jmen). V okně, které se otevře, klikněte na tlačítko Vytvořit (Nový), do pole zadejte název našeho rozsahu a vzorec Rozsah (Odkaz):
Zbývá kliknout OK a rozsah připravenosti lze použít v libovolných vzorcích, rozevíracích seznamech nebo grafech.
- Použití funkce VLOOKUP k propojení tabulek a vyhledávání hodnot
- Jak vytvořit automaticky vyplňující rozevírací seznam
- Jak vytvořit kontingenční tabulku pro analýzu velkého množství dat