Jak nastavit rozsah hodnot v Excelu

Při použití aplikace Microsoft Excel velmi často uživatel předem neví, kolik informací bude ve výsledku v tabulce. Proto nerozumíme ve všech situacích, jaký rozsah by měl být pokryt. Koneckonců, sada buněk je variabilní koncept. Abychom se tohoto problému zbavili, je nutné provést automatické generování rozsahu tak, aby bylo založeno pouze na množství dat zadaných uživatelem.

Automatická změna rozsahů buněk v Excelu

Výhodou automatických rozsahů v Excelu je, že výrazně usnadňují používání vzorců. Navíc umožňují výrazně zjednodušit analýzu složitých dat, která obsahují velké množství vzorců, které obsahují mnoho funkcí. Tento rozsah můžete pojmenovat a poté bude automaticky aktualizován podle toho, jaká data obsahuje.

Jak provést automatickou změnu rozsahu v Excelu

Předpokládejme, že jste investor, který potřebuje investovat do nějakého objektu. Ve výsledku chceme získat informace o tom, kolik můžete celkem vydělat za celou dobu, kdy budou peníze na tento projekt fungovat. Abychom však tyto informace získali, musíme pravidelně sledovat, jaký celkový zisk nám tento objekt přináší. Vytvořte stejný přehled jako na tomto snímku obrazovky.

Řešení je na první pohled zřejmé: stačí sečíst celý sloupec. Pokud se v něm objeví položky, částka se aktualizuje nezávisle. Ale tato metoda má mnoho nevýhod:

1. Pokud se problém vyřeší tímto způsobem, nebude možné použít buňky obsažené ve sloupci B pro jiné účely.
2. Taková tabulka spotřebuje hodně paměti RAM, což znemožní použití dokumentu na slabých počítačích.

Proto je nutné tento problém řešit pomocí dynamických jmen. Chcete-li je vytvořit, musíte provést následující posloupnost akcí:

1. Přejděte na kartu „Vzorce“, která se nachází v hlavní nabídce. Bude zde sekce „Definovaná jména“, kde je tlačítko „Přiřadit jméno“, na které musíme kliknout.
2. Dále se objeví dialogové okno, ve kterém musíte vyplnit pole, jak je znázorněno na snímku obrazovky. Je důležité si uvědomit, že musíme funkci použít = POSUN spolu s funkcí KONTROLApro vytvoření rozsahu automatické aktualizace. 
3. Poté musíme funkci použít SOUČET, který používá náš dynamický rozsah jako argument. 

Po dokončení těchto kroků můžeme vidět, jak se aktualizuje pokrytí buněk patřících do „příjmového“ rozsahu, když tam přidáváme nové prvky.

Funkce OFFSET v Excelu

Podívejme se na funkce, které jsme zaznamenali v poli „rozsah“ dříve. Pomocí funkce LIKVIDACE můžeme určit velikost rozsahu podle toho, kolik buněk ve sloupci B je vyplněno. Argumenty funkce jsou následující:

1. Počáteční buňka. Pomocí tohoto argumentu může uživatel určit, která buňka v rozsahu bude považována za levou horní. Bude se hlásit dolů a doprava.
2. Rozsah posunutý o řádky. Pomocí tohoto rozsahu nastavíme počet buněk, o které má dojít k posunu od levé horní buňky rozsahu. Můžete použít nejen kladné hodnoty, ale také nulu a mínus. V tomto případě nemusí k posunu vůbec dojít, nebo bude provedeno v opačném směru.
3. Rozsah posunutý o sloupce. Tento parametr je podobný předchozímu, pouze umožňuje nastavit míru horizontálního posunu rozsahu. Zde můžete také použít nulové i záporné hodnoty.
4. Rozsah výšky. Ve skutečnosti nám název tohoto argumentu objasňuje, co znamená. Toto je počet buněk, o který by se měl rozsah zvýšit.
5. Hodnota rozsahu na šířku. Argument je podobný předchozímu, pouze se týká sloupců.

Pokud nepotřebujete, nemusíte uvádět poslední dva argumenty. V tomto případě bude hodnota rozsahu pouze jedna buňka. Pokud například zadáte vzorec =POSUN(A1;0;0), bude tento vzorec odkazovat na stejnou buňku jako ta v prvním argumentu. Pokud je svislé posunutí nastaveno na 2 jednotky, pak v tomto případě bude buňka odkazovat na buňku A3. Nyní si podrobně popíšeme, co funkce znamená KONTROLA.

Funkce COUNT v Excelu

Pomocí funkce KONTROLA určíme, kolik buněk ve sloupci B jsme celkem vyplnili. To znamená, že pomocí dvou funkcí určíme, kolik buněk v rozsahu je vyplněno, a na základě přijatých informací určíme velikost rozsahu. Konečný vzorec tedy bude následující: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Pojďme se podívat na to, jak správně pochopit princip tohoto vzorce. První argument ukazuje, kde začíná náš dynamický rozsah. V našem případě je to buňka B2. Další parametry mají nulové souřadnice. To naznačuje, že nepotřebujeme offsety vzhledem k levé horní buňce. Vyplňujeme pouze vertikální velikost rozsahu, jakou jsme funkci použili KONTROLA, který určuje počet buněk, které obsahují nějaká data. Čtvrtým parametrem, který jsme vyplnili, je jednotka. Ukážeme tedy, že celková šířka rozsahu by měla být jeden sloupec.

Tedy pomocí funkce KONTROLA uživatel může využívat paměť co nejefektivněji načtením pouze těch buněk, které obsahují nějaké hodnoty. V souladu s tím nebudou v práci žádné další chyby spojené se špatným výkonem počítače, na kterém bude tabulka fungovat.

Chcete-li tedy určit velikost rozsahu v závislosti na počtu sloupců, musíte provést podobnou sekvenci akcí, pouze v tomto případě musíte zadat jednotku ve třetím parametru a vzorec ve čtvrtém KONTROLA.

Vidíme, že pomocí vzorců Excelu můžete nejen automatizovat matematické výpočty. To je jen kapka v moři, ale ve skutečnosti vám umožňují automatizovat téměř jakoukoli operaci, která vás napadne.

Dynamické grafy v Excelu

V posledním kroku jsme tedy mohli vytvořit dynamický rozsah, jehož velikost zcela závisí na tom, kolik vyplněných buněk obsahuje. Nyní můžete na základě těchto dat vytvářet dynamické grafy, které se automaticky změní, jakmile uživatel provede nějaké změny nebo přidá další sloupec nebo řádek. Pořadí akcí je v tomto případě následující:

1. Vybereme náš rozsah, za který vložíme graf typu „Histogram se seskupením“. Tuto položku naleznete v sekci „Vložit“ v sekci „Grafy – Histogram“.
2. Klikneme levým tlačítkem myši na náhodný sloupec histogramu, po kterém se ve funkčním řádku zobrazí funkce =SERIES(). Na snímku obrazovky můžete vidět podrobný vzorec. 
3. Poté je třeba provést nějaké změny ve vzorci. Musíte nahradit rozsah za „List1!“ na název rozsahu. Výsledkem bude následující funkce: =ROW(List1!$B$1;;List1!příjem;1)
4. Nyní zbývá přidat nový záznam do sestavy a zkontrolovat, zda se graf aktualizuje automaticky nebo ne.

Pojďme se podívat na náš diagram.

Pojďme si shrnout, jak se nám to povedlo. V předchozím kroku jsme vytvořili dynamický rozsah, jehož velikost závisí na tom, kolik prvků obsahuje. K tomu jsme použili kombinaci funkcí KONTROLA и LIKVIDACE. Tento rozsah jsme pojmenovali a pak jsme odkaz na tento název použili jako rozsah našeho histogramu. Jaký konkrétní rozsah zvolit jako zdroj dat v první fázi není tak důležité. Hlavní věc je nahradit jej později názvem rozsahu. Tímto způsobem můžete ušetřit spoustu paměti RAM.

Pojmenované rozsahy a jejich použití

Promluvme si nyní podrobněji o tom, jak správně vytvářet pojmenované rozsahy a používat je k provádění úloh, které jsou nastaveny pro uživatele Excelu.

Ve výchozím nastavení používáme běžné adresy buněk, abychom ušetřili čas. To je užitečné, když potřebujete jednou nebo vícekrát zapsat rozsah. Pokud je třeba jej používat neustále nebo je třeba, aby byl adaptivní, měly by být použity pojmenované rozsahy. Díky nim je vytváření vzorců mnohem jednodušší a pro uživatele nebude tak obtížné analyzovat složité vzorce, které obsahují velké množství funkcí. Pojďme si popsat některé kroky spojené s vytvářením dynamických rozsahů.

Vše začíná pojmenováním buňky. Chcete-li to provést, jednoduše jej vyberte a do pole jeho názvu napište název, který potřebujeme. Je důležité, aby byl snadno zapamatovatelný. Při pojmenovávání je třeba vzít v úvahu některá omezení:

1. Maximální délka je 255 znaků. To je docela dost k přiřazení jména, po kterém vaše srdce touží.
2. Název nesmí obsahovat mezery. Pokud tedy obsahuje více slov, je možné je oddělit pomocí znaku podtržítka.

Pokud později na dalších listech tohoto souboru potřebujeme tuto hodnotu zobrazit nebo ji použít pro další výpočty, není třeba přepínat na úplně první list. Stačí si zapsat název této buňky rozsahu.

Dalším krokem je vytvoření pojmenovaného rozsahu. Postup je v podstatě stejný. Nejprve musíte vybrat rozsah a poté zadat jeho název. Poté lze tento název použít ve všech ostatních operacích s daty v Excelu. Například pojmenované rozsahy se často používají k definování součtu hodnot.

Kromě toho je možné vytvořit pojmenovaný rozsah pomocí karty Vzorce pomocí nástroje Set Name. Poté, co jej vybereme, se objeví okno, kde musíme zvolit název našeho rozsahu a také ručně určit oblast, do které se bude rozšiřovat. Můžete také určit, kde bude tento rozsah fungovat: v rámci jednoho listu nebo v celé knize.

Pokud již byl vytvořen rozsah jmen, pak pro jeho použití existuje speciální služba nazývaná správce jmen. Umožňuje nejen upravovat nebo přidávat nová jména, ale také je mazat, pokud již nejsou potřeba.

Je třeba mít na paměti, že při použití pojmenovaných rozsahů ve vzorcích pak po jeho smazání nedojde k automatickému přepsání vzorců správnými hodnotami. Proto může dojít k chybám. Před odstraněním pojmenovaného rozsahu se proto musíte ujistit, že není použit v žádném ze vzorců.

Dalším způsobem, jak vytvořit pojmenovaný rozsah, je získat jej z tabulky. K tomu existuje speciální nástroj s názvem „Vytvořit z výběru“. Abychom ji mohli použít, musíte nejprve vybrat rozsah, který budeme upravovat, a poté nastavit místo, kde máme nadpisy. Výsledkem je, že na základě těchto dat Excel automaticky zpracuje všechna data a automaticky se přiřadí názvy.

Pokud název obsahuje několik slov, Excel je automaticky oddělí podtržítkem.

Tak jsme přišli na to, jak vytvořit dynamické pojmenované rozsahy a jak vám umožní automatizovat práci s velkým množstvím dat. Jak vidíte, stačí použít několik funkcí a programových nástrojů zabudovaných do funkčnosti. Není to vůbec nic složitého, ač se to začátečníkovi na první pohled může zdát.