Sešity Excelu s uvedenými příklady (komprese ZIP) Pojmenované oblasti, vyhledávací funkce
| V Excelu můžeme pojmenovat libovolnou buňku či oblast buněk a tento název pak použít ve vzorcích a funkcích, ať už v samotném listu, či v procedurách VBA. A nejen to. Smíme si pojmenovat i konstantu nebo vzorec. Výhody pochopíte na jednotlivých příkladech. Předesílám, že používání názvů je velmi vhodné právě u vyhledávacích funkcí z důvodu přehlednosti. |
| Pamatujte, že u názvů oblastí se nehledí na velká a malá písmena, název nesmí začínat číslicí a mezi nepřípustné znaky patří mezera a některé znaky interpunkce. Stejné názvy se přitom smí vyskytovat na dvou listech téhož sešitu. Názvy zadáváme buď přímo do tzv. Pole názvů (políčko se seznamem vlevo od řádku vzorců), nebo v dialogu Vložit / Název / Definovat... (zkratka Ctrl+F3). Standardně se v tomto dialogu zobrazí absolutní odkaz na právě vybranou oblast listu. | |
  |
| Pojmenovávání oblastí patří mezi nejpoužívanější v kategorii přidělování názvů. Výhody jsou zřejmé. Odkaz na oblast pojmenovanou "Jaro" je názornější než například "A1:A3" a navíc je tento zápis méně náchylný k chybám. Navíc, pokud se ve VBA odvoláváte na oblast stylem Range("A1:A3") a tuto oblast v listu přesunete, odkaz bude stále směřovat na A1:A3, zatímco Range("Jaro") bude "stopovat" kolekci buněk do jejího nového umístění. | |
 Vzorec =POČET2(sladkosti) v buňce A10 na obrázku vpravo vrací počet neprázdných buněk v oblasti pojmenované sladkosti (na obrázku vybrána). Narozdíl od VBA se odkazy na názvy oblastí nepíší do uvozovek. Co se smí... |
| Občas se můžete dostat do situace, kdy budete potřebovat nadefinovat oblast přesahující rozsah jednoho listu. V tom případě postupujte jako v následujícím příkladu. | |
 |
|
V příkladu je definována oblast "casy60", která slouží ke statistickým výpočtům běhu na šedesát metrů pátých tříd ZŠ (viz příloha). Oblast zahrnuje tři suboblasti buněk A2:A11, a to z listů 5A, 5B a 5C. Postup tvorby vzorce je následující:
|
| Je to tak, je velká škoda, jak si někteří uživatelé komplikují vzorce (a život) neznalostí skutečnosti, že stejně jako oblastem můžeme přiřazovat jméno našim vlastním konstantám a vytvořeným vzorcům. Princip je jednoduchý. Stačí místo odkazu na oblast listu vložit vzorec v dialogu Definovat název. | |
 |
|
| Fakt, že lze použít vzorce, vám teď asi leží v hlavě a říkáte si, co kdyby... Ukážeme si dva příklady. V prvním si nadefinujeme oblast dynamicky, ve druhém si uvědomíte sílu vzorců ve spojení s relativní adresací buněk. Pojďme na to. | |
 |
|
| K vzorci, který jsem použil pro "auta", řeknu jen to nejpodstatnější. Mezi apostrofy se vyskytuje název listu (oblast I), buňka $C$1 je buňka s hlavičkou a výraz $C:$C značí sloupec, ve kterém se oblast vyskytuje. Ostatní parametry ve vzorci zůstávají neměnné. Stále jsem však neřekl, proč jsme použili tuto krkolomnou konstrukci. Oproti staticky pojmenované oblasti má jedno velké plus. Zde totiž položky přidávané před první položku (BMW) i na konec seznamu (od buňky A8 níže) automaticky rozšiřují pojmenovanou oblast. A nyní pokračujme druhým slíbeným příkladem. | |
 |
|
| Ve vzorci pojmenovaném "vlevo" se vyskytuje odkaz na buňku E2. Když jsem název zadával (název listu v apostrofech se doplní sám), byla vybrána buňka F2, což je nutná podmínka pro správnou funkci relativního odkazu. Následně jsem přímo do buňky F2 zapsal vzorec =vlevo a zkopíroval jej do buněk F3 až F7. Jak vidíte, pojmenovaný vzorec se chová skutečně relativně. |
| Jednou z nejčastějších potřeb uživatelů je používat tentýž název na vícero listech sešitu, každý s platností pro jeden list. Pokud si tedy přejete používat například název Zisk na listech List1 i List2, pak dané oblasti pojmenujte List1!Zisk a List2!Zisk. |
| Pokud pojmenujete například buňku B5, neznamená to, že adresu B5 Excel automaticky přepíše ve všech vzorcích na listu. Můžete to ale učinit ručně tak, že vyberete oblast se vzorci, ve kterých má být záměna provedena, kliknete na Vložit / Název / Použít... a vyberete názvy, které chcete aplikovat. Excel jako takový bohužel nedisponuje nástrojem pro opačný postup - nahrazení názvů oblastí jejich skutečnými adresami. Jestliže název použitý ve vzorcích ze sešitu odstraníte, obdržíte chybová hlášení #NAME?. |
| V Excelu je možné pracovat s popisky a používat je ve vzorcích. Těmito popisky se myslí názvy užité přímo v buňkách tvořících hlavičky řádků a sloupců tabulky. Podmínkou funkčnosti je zatržení volby Nástroje / Možnosti / karta Výpočty, Povolit popisky ve vzorcích. | |
 |
|
 Vzorec v buňce I4 na obrázku vpravo vrací hodnotu v průsečíku řádku čtvrtek a sloupce Petr (totéž vrací vzorec =Petr čtvrtek). Mezera zde slouží jako tzv. "operátor průniku". Dále platí, že pokud popisek obsahuje znak mezery nebo číslici, musíte jej uzavřít mezi znaky apostrofu (totéž platí pro odkazy obsahující názvy listů). Ačkoliv je tato možnost odkazování mnohem jednodušší než užití vyhledávacích funkcí, osobně v ní nemám důvěru a nedoporučuji ji. Používat mezeru jako operátor a nechat na Excelu, aby pochopil, co tvoří hlavičky tabulek, mi nepřipadá vhodné. |
| Těžko říci, kdy vznikla první tabulka, ale určitě již od počátku si od ní její tvůrci slibovali snadné vyhledávání údajů. Pokud je tabulka v papírové podobě, pak pro vyhledávání používáme nejčastěji prst, pravítko či list papíru. S nástupem tabulkových procesorů vznikali i vyhledávací nástroje a funkce a právě jim se v Excelu podíváme na zoubek. Objem tabulkových a databázových funkcí ovšem přesahuje prostor tohoto článku a proto veškerá pozornost tomuto tématu je směrována do sešitu v příloze. Sešit je přitom koncipován jako karta první pomoci, po tisku v papírové podobě. Veškeré příklady jsou samozřejmě v elektronické podobě funkční a čekají na otestování. Speciálně pro tento sešit byl vytvořen panel nástrojů se seznamem pojmenovaných buněk. Výběrem položky ze seznamu aktivujete pojmenovanou oblast. Kód makra (Alt+F11) pro vytvoření a funkci tohoto panelu berte jako bonus. |
pro časopis 