Ako nastaviť rozsah hodnôt v Exceli

Pri používaní programu Microsoft Excel veľmi často používateľ vopred nevie, koľko informácií bude v dôsledku toho v tabuľke. Preto nerozumieme vo všetkých situáciách, aký rozsah by mal byť pokrytý. Sada buniek je totiž variabilný pojem. Aby sme sa zbavili tohto problému, je potrebné, aby bolo generovanie rozsahu automatické, aby bolo založené výlučne na množstve údajov, ktoré zadal používateľ.

Automatická zmena rozsahov buniek v Exceli

Výhodou automatických rozsahov v Exceli je, že výrazne uľahčujú používanie vzorcov. Okrem toho umožňujú výrazne zjednodušiť analýzu zložitých údajov, ktoré obsahujú veľké množstvo vzorcov, ktoré obsahujú mnoho funkcií. Tento rozsah môžete pomenovať a potom sa automaticky aktualizuje v závislosti od toho, aké údaje obsahuje.

Ako vykonať automatickú zmenu rozsahu v Exceli

Predpokladajme, že ste investor, ktorý potrebuje investovať do nejakého objektu. Výsledkom je, že chceme získať informácie o tom, koľko môžete celkovo zarobiť za celú dobu, počas ktorej budú peniaze na tento projekt fungovať. Napriek tomu na získanie týchto informácií musíme pravidelne sledovať, aký celkový zisk nám tento objekt prináša. Urobte rovnaký prehľad ako na tejto snímke obrazovky.

Na prvý pohľad je riešenie zrejmé: stačí sčítať celý stĺpec. Ak sa v ňom objavia záznamy, suma sa aktualizuje nezávisle. Ale táto metóda má veľa nevýhod:

1. Ak sa problém vyrieši týmto spôsobom, nebude možné použiť bunky v stĺpci B na iné účely.
2. Takáto tabuľka spotrebuje veľa pamäte RAM, čo znemožní použitie dokumentu na slabých počítačoch.

Preto je potrebné tento problém riešiť prostredníctvom dynamických mien. Ak ich chcete vytvoriť, musíte vykonať nasledujúcu postupnosť akcií:

1. Prejdite na kartu „Vzorce“, ktorá sa nachádza v hlavnej ponuke. K dispozícii bude sekcia „Definované názvy“, kde je tlačidlo „Priradiť názov“, na ktoré musíme kliknúť.
2. Ďalej sa zobrazí dialógové okno, v ktorom musíte vyplniť polia, ako je znázornené na snímke obrazovky. Je dôležité poznamenať, že musíme použiť funkciu = POSUN spolu s funkciou KONTROLAna vytvorenie rozsahu automatickej aktualizácie. 
3. Potom musíme použiť funkciu SUM, ktorý používa náš dynamický rozsah ako argument. 

Po dokončení týchto krokov môžeme vidieť, ako sa aktualizuje pokrytie buniek patriacich do „príjmového“ rozsahu, keď tam pridávame nové prvky.

Funkcia OFFSET v Exceli

Pozrime sa na funkcie, ktoré sme predtým zaznamenali v poli „rozsah“. Pomocou funkcie LIKVIDÁCIA môžeme určiť rozsah rozsahu podľa toho, koľko buniek v stĺpci B je vyplnených. Argumenty funkcie sú nasledovné:

1. Začiatočná bunka. Pomocou tohto argumentu môže používateľ určiť, ktorá bunka v rozsahu sa bude považovať za ľavú hornú časť. Bude sa hlásiť dole a vpravo.
2. Rozsah posunutý o riadky. Pomocou tohto rozsahu nastavíme počet buniek, o ktoré má dôjsť k posunu od ľavej hornej bunky rozsahu. Môžete použiť nielen kladné hodnoty, ale aj nulu a mínus. V tomto prípade nemusí k posunu vôbec dôjsť, alebo sa uskutoční v opačnom smere.
3. Rozsah posunutý o stĺpce. Tento parameter je podobný predchádzajúcemu, len vám umožňuje nastaviť stupeň horizontálneho posunu rozsahu. Tu môžete použiť aj nulové aj záporné hodnoty.
4. Rozsah výšky. V skutočnosti nám názov tohto argumentu objasňuje, čo znamená. Toto je počet buniek, o ktoré by sa mal rozsah zvýšiť.
5. Hodnota rozsahu v šírke. Argument je podobný predchádzajúcemu, len sa týka stĺpcov.

Ak nepotrebujete, nemusíte uvádzať posledné dva argumenty. V tomto prípade bude hodnota rozsahu iba jedna bunka. Napríklad, ak zadáte vzorec =POSUN(A1;0;0), bude tento vzorec odkazovať na rovnakú bunku ako tá v prvom argumente. Ak je vertikálny posun nastavený na 2 jednotky, potom v tomto prípade bude bunka odkazovať na bunku A3. Teraz si podrobne popíšeme, čo funkcia znamená KONTROLA.

Funkcia COUNT v Exceli

Pomocou funkcie KONTROLA určíme, koľko buniek v stĺpci B sme celkovo vyplnili. To znamená, že pomocou dvoch funkcií určíme, koľko buniek v rozsahu je vyplnených, a na základe prijatých informácií určíme veľkosť rozsahu. Preto bude konečný vzorec nasledujúci: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Poďme sa pozrieť na to, ako správne pochopiť princíp tohto vzorca. Prvý argument ukazuje, kde začína náš dynamický rozsah. V našom prípade je to bunka B2. Ďalšie parametre majú nulové súradnice. To naznačuje, že nepotrebujeme posuny vzhľadom na ľavú hornú bunku. Všetko, čo vypĺňame, je vertikálna veľkosť rozsahu, ako sme funkciu použili KONTROLA, ktorý určuje počet buniek, ktoré obsahujú nejaké údaje. Štvrtý parameter, ktorý sme vyplnili, je jednotka. Ukážeme teda, že celková šírka rozsahu by mala byť jeden stĺpec.

Teda pomocou funkcie KONTROLA užívateľ môže využívať pamäť čo najefektívnejšie načítaním len tých buniek, ktoré obsahujú nejaké hodnoty. V dôsledku toho sa v práci nevyskytnú žiadne ďalšie chyby spojené so slabým výkonom počítača, na ktorom bude tabuľka fungovať.

Preto, aby ste určili veľkosť rozsahu v závislosti od počtu stĺpcov, musíte vykonať podobnú postupnosť akcií, iba v tomto prípade musíte zadať jednotku v treťom parametri a vzorec vo štvrtom KONTROLA.

Vidíme, že pomocou vzorcov v Exceli môžete nielen automatizovať matematické výpočty. Toto je len kvapka v mori, no v skutočnosti vám umožňujú automatizovať takmer akúkoľvek operáciu, ktorá vás napadne.

Dynamické grafy v Exceli

V poslednom kroku sme teda mohli vytvoriť dynamický rozsah, ktorého veľkosť úplne závisí od toho, koľko vyplnených buniek obsahuje. Teraz môžete na základe týchto údajov vytvárať dynamické grafy, ktoré sa automaticky zmenia, akonáhle používateľ vykoná nejaké zmeny alebo pridá ďalší stĺpec alebo riadok. Postupnosť akcií je v tomto prípade nasledovná:

1. Vyberieme náš rozsah, po ktorom vložíme graf typu „Histogram so zoskupením“. Túto položku nájdete v časti „Vložiť“ v časti „Grafy – Histogram“.
2. Klikneme ľavým tlačidlom myši na náhodný stĺpec histogramu, po ktorom sa vo funkčnom riadku zobrazí funkcia =SERIES(). Na snímke obrazovky môžete vidieť podrobný vzorec. 
3. Potom je potrebné vykonať nejaké zmeny vo vzorci. Musíte nahradiť rozsah za „Hárok1!“ k názvu rozsahu. Výsledkom bude nasledujúca funkcia: =ROW(Hárok1!$B$1;;Hárok1!príjem;1)
4. Teraz zostáva pridať nový záznam do zostavy, aby ste skontrolovali, či sa graf aktualizuje automaticky alebo nie.

Poďme sa pozrieť na náš diagram.

Poďme si zhrnúť, ako sa nám to podarilo. V predchádzajúcom kroku sme vytvorili dynamický rozsah, ktorého veľkosť závisí od toho, koľko prvkov obsahuje. Na tento účel sme použili kombináciu funkcií KONTROLA и LIKVIDÁCIA. Tento rozsah sme pomenovali a potom sme odkaz na tento názov použili ako rozsah nášho histogramu. Ktorý konkrétny rozsah zvoliť ako zdroj údajov v prvej fáze nie je až taký dôležitý. Hlavná vec je nahradiť ho názvom rozsahu neskôr. Týmto spôsobom môžete ušetriť veľa pamäte RAM.

Pomenované rozsahy a ich použitie

Povedzme si teraz podrobnejšie o tom, ako správne vytvárať pomenované rozsahy a používať ich na vykonávanie úloh, ktoré sú nastavené pre používateľa Excelu.

V predvolenom nastavení používame bežné adresy buniek, aby sme ušetrili čas. Je to užitočné, keď potrebujete raz alebo viackrát zapísať rozsah. Ak sa musí používať stále alebo musí byť adaptívny, mali by sa použiť pomenované rozsahy. Uľahčujú vytváranie vzorcov a pre používateľa nebude také ťažké analyzovať zložité vzorce, ktoré obsahujú veľké množstvo funkcií. Opíšme si niektoré kroky pri vytváraní dynamických rozsahov.

Všetko to začína pomenovaním bunky. Ak to chcete urobiť, jednoducho ho vyberte a potom do poľa jeho názvu napíšte názov, ktorý potrebujeme. Je dôležité, aby bol ľahko zapamätateľný. Pri pomenovaní je potrebné zvážiť niekoľko obmedzení:

1. Maximálna dĺžka je 255 znakov. To je celkom dosť na to, aby ste priradili meno, po ktorom vaše srdce túži.
2. Názov nesmie obsahovať medzery. Ak teda obsahuje viacero slov, potom je možné ich oddeliť pomocou znaku podčiarknutia.

Ak neskôr na ďalších listoch tohto súboru potrebujeme zobraziť túto hodnotu alebo ju použiť na vykonanie ďalších výpočtov, potom nie je potrebné prepínať na úplne prvý list. Stačí si zapísať názov bunky tohto rozsahu.

Ďalším krokom je vytvorenie pomenovaného rozsahu. Postup je v podstate rovnaký. Najprv musíte vybrať rozsah a potom zadať jeho názov. Potom je možné tento názov použiť vo všetkých ostatných operáciách s údajmi v Exceli. Napríklad pomenované rozsahy sa často používajú na definovanie súčtu hodnôt.

Okrem toho je možné vytvoriť pomenovaný rozsah pomocou karty Vzorce pomocou nástroja Nastaviť názov. Po jeho výbere sa nám zobrazí okno, v ktorom musíme vybrať názov pre náš rozsah, ako aj manuálne určiť oblasť, do ktorej sa rozšíri. Môžete tiež určiť, kde bude tento rozsah fungovať: v rámci jedného hárku alebo v celej knihe.

Ak už bol vytvorený rozsah názvov, potom na jeho použitie existuje špeciálna služba nazývaná správca mien. Umožňuje nielen úpravu alebo pridávanie nových mien, ale aj ich vymazanie, ak už nie sú potrebné.

Treba mať na pamäti, že pri použití pomenovaných rozsahov vo vzorcoch sa potom po jeho odstránení vzorce automaticky neprepíšu správnymi hodnotami. Preto sa môžu vyskytnúť chyby. Preto sa pred odstránením pomenovaného rozsahu musíte uistiť, že sa nepoužíva v žiadnom zo vzorcov.

Ďalším spôsobom, ako vytvoriť pomenovaný rozsah, je získať ho z tabuľky. Na tento účel existuje špeciálny nástroj s názvom „Vytvoriť z výberu“. Ako vieme, aby ste ho mohli použiť, musíte najprv vybrať rozsah, ktorý budeme upravovať, a potom nastaviť miesto, kde máme nadpisy. Výsledkom je, že na základe týchto údajov Excel automaticky spracuje všetky údaje a automaticky sa priradia názvy.

Ak názov obsahuje niekoľko slov, Excel ich automaticky oddelí podčiarkovníkom.

Preto sme prišli na to, ako vytvoriť dynamické pomenované rozsahy a ako vám umožnia automatizovať prácu s veľkým množstvom údajov. Ako vidíte, stačí použiť niekoľko funkcií a programové nástroje zabudované do funkcionality. Nie je v tom vôbec nič zložité, hoci sa to začiatočníkovi na prvý pohľad môže zdať.