Efektivní práce s listy v Excelu: Ukotvení, seskupení a pokročilé funkce

Při práci s excelovým sešitem, který obsahuje více listů, se může stát, že se velmi rychle ztratíte. Obvykle na spodní liště vidíte jen několik prvních listů a na další se musíte posouvat šipkou doprava, což může být po nějaké době otravné. Naštěstí existuje několik užitečných triků a funkcí, které vám pomohou s listy pracovat efektivněji a usnadní orientaci v rozsáhlých souborech.

Zobrazení a uspořádání více listů najednou

Jedním z jednoduchých triků je zobrazení několika různých listů najednou. Když pracujeme s více listy najednou, je pro nás daleko složitější si neustále pamatovat, na jakém listu jsme, zvlášť když máme například něco kontrolovat. Můžeme si pomoci tak, že si zobrazíme více listů vedle sebe.

V tomto případě chceme vidět naráz listy Leden až Duben. Postupujte takto:

  1. Klikněte na list "Únor" a na kartě Zobrazení vyberte Nové okno.
  2. To samé udělejte pro "Březen": klikněte na "Březen" a vyberte Nové okno.
  3. A ještě to samé udělejte pro "Duben": klikněte na "Duben" a vyberte Nové okno.
  4. Teď v jednom z těchto nových oken na kartě Zobrazení vyberte Uspořádat vše, kde zvolte Vedle sebe a dole zaškrtněte Okna aktivního sešitu.

V tu chvíli se vám tato čtyři okna uspořádají vedle sebe a vy můžete daleko lépe zkontrolovat výpočty.

Zorné pole spodní lišty si můžete také zvětšit posunutím spodní lišty tak, abyste viděli co nejvíce listů. Stačí myší chytit tyto tři tečky u spodního posuvníku a posunout je co nejvíce doprava.

Čtěte také: Kompletní průvodce Excel Mix nátěrem

Seskupení listů pro hromadné úpravy

Dalším trikem je seskupení více listů tak, aby se vám s nimi lépe a rychleji pracovalo. Řekněme, že máte v excelovém souboru několik listů, které mají stejnou strukturu. V našem případě reprezentuje každý list jeden měsíc a na každém listu máme tabulku s prodejními daty za jednotlivé produkty. Vedle tabulky máme na každém listu základní výpočty.

Příklad:

  • První výpočet: Tržby za prvních 14 dnů v každém měsíci pomocí funkce SOUČIN.SKALÁRNÍ (anglicky SUMPRODUCT).
  • Druhý výpočet: Tržby za prvních 14 dnů v měsíci, kdyby se tržby zvýšily o 5 %.

Tyto stejné výpočty máme na každém listu. Abychom nemuseli měnit procento na každém listu zvlášť, můžeme použít následující trik, který spočívá v seskupení listů a hromadné úpravě:

  1. Označte všechny listy v sešitu. To můžete udělat tak, že buď držíte klávesu CTRL a klikáte na listy, nebo kliknete na první list, držíte klávesu SHIFT a označíte poslední list, čímž se automaticky označí všechny listy mezi nimi.
  2. Když máte označené všechny listy, stačí na jednom aktivním listu změnit procento a potvrdit klávesou ENTER. Po potvrzení se změní procenta na všech označených listech.

To samé by fungovalo i na vzorec. Teď vás nezajímají tržby za prvních 14 dnů, ale za prvních 20 dnů. Bohužel zde nemáme buňku pro změnu dnů, kterou bychom mohli měnit, ale číslo 14 máme přímo ve vzorci.

  1. Můžete opět označit všechny listy, kliknout do vzorce a změnit číslici 14 na 20.
  2. Změnu potvrdíte ENTEREM a jelikož máte stále označené všechny listy, tak tu samou změnu provedete i ve druhém vzorci: kliknete do vzorce, změníte číslici a potvrdíte.

To samé bychom mohli udělat i v textu, změnit 14 na 20.

Čtěte také: Recenze tekutých hydroizolací

Propojení výsledků z více listů pomocí funkce NEPŘÍMÝ.ODKAZ (INDIRECT)

V dalším příkladu máme několik listů, na kterých máme namodelovaný vývoj ukazatele IRR při různých peněžních tocích. Na listu "Přehled scénářů" chceme jednotlivé výsledky scénářů porovnat v tabulce.

Samozřejmě bychom mohli jít a kliknout do buňky ke scénáři jedna a pomocí rovná se provázat buňku s výsledkem na listu scénář 1. Toto bychom ale museli opakovat pro všechny scénáře. Pokud máme scénářů málo, tak to nemusí být problém, ale co kdybychom takových listů měli desítky?

Můžeme použít funkci NEPŘÍMÝ.ODKAZ neboli funkci INDIRECT. Výsledná funkce se může zdát trochu komplikovaná, ale když pochopíte její strukturu, tak vám v podobných situacích může ušetřit spoustu času.

U tvorby reference na list je nejjednodušší začít tak, že se podíváme, jak je reference na list strukturovaná. Napíšeme rovná se do první buňky a proklikneme se na "Scénář 1" a provážeme buňku s výpočtem a potvrdíme. Jelikož máme list pojmenovaný dvěma slovy s mezerou mezi slovem a číslicí, je název listu uvedený jednoduchými uvozovkami. Následuje vykřičník, který je u odkazu na list vždy, a pak následuje buňka, na kterou se odkazujeme. Tuto referenci vytvoříme teď ve funkci NEPŘÍMÝ.ODKAZ.

Největší chybou ve funkci NEPŘÍMÝ.ODKAZ je to, že se zapomíná, že všechno v této funkci je textem, tudíž musí být vše uvedené v uvozovkách. Jelikož vše ve funkci musí být bráno jako text, musíme do vlastních uvozovek nejprve dát tyto jednoduché uvozovky. Před tyto apostrofy tedy napíšeme uvozovky.

Čtěte také: Materiály pro upevnění dřevovláknitých desek

Dále musí být v samostatných uvozovkách i vykřičník. Nicméně text musí být spojený ampersandem, takže za apostrof napíšeme ampersand a vykřičník dáme do uvozovek. Následuje odkaz na buňku, který rovněž musí být v uvozovkách a spojený s ostatním textem ampersandem.

Poslední co zbývá je nahradit text list odkazem na buňku v tabulce, kde máme pojmenované jednotlivé listy. Takže text listu nahradíme odkazem na buňku, tedy A4. Nicméně to opět musíme s ostatním textem spojit ampersandy, jak za textem, tak před textem.

Pokud bychom se chtěli podívat, zda máme vše správně, označíme tu část funkce, která označuje název listu, tedy tu část včetně vykřičníku, a zmáčkneme klávesu F9. A jak vidíme, objeví se název "Scénář 1", funkce NEPŘÍMÝ.ODKAZ tedy teď ví, že se má na listu "Scénář 1" odkázat na buňku E3 a vrátit její obsah. Jak funkci potáhneme dolů, tak na druhém řádku bude místo "Scénáře 1" "Scénář 2" atd. Nezapomeneme se vrátit do funkce zmáčknutím CTRL+Z. Funkci potvrdíme a stáhneme ji dolů.

Používání 3D vzorců

Dalším trikem je používání tzv. 3D vzorců. Nejoblíbenější funkcí, kterou můžeme použít ve formátu 3D, je určitě funkce SUMA.

V dalším příkladu máme tři listy A, B a C, na kterých máme uvedené tržby. Na listu "Přehled" bychom chtěli všechny tržby ze tří listů sečíst.

První možnost: Součet mezisoučtů

Nejprve sečteme tržby na jednotlivých listech. Abychom to nemuseli dělat postupně, využijeme předešlého triku:

  1. Označíme všechny tři listy (A, B, C) a na jednom listu napíšeme rovná se, funkci SUMA a označíme tržby v tabulce.
  2. Funkci potvrdíme ENTEREM. Teď by funkce SUMA měla být ve všech buňkách na všech listech. Jediné, na co si musíte dát pozor, je, že v lednu jsme označili více buněk, jelikož máme 31 dnů, takže v únoru bude označeno i několik řádků pod tabulkou, jelikož v únoru máme méně dnů. V tomto případě to nevadí, ale pokud byste hned pod tabulkami měli jiná data, tak byste si na to měli dát pozor.

Součty máme všude. Nyní na listu "Přehled" provedeme 3D sumu:

  1. Napíšeme funkci SUMA, kde nejprve klikneme na první list A a označíme součet.
  2. Držíme klávesu SHIFT a klikneme na poslední třetí list, který chceme do součtu zahrnout, na tomto listu C ani nemusíme klikat na žádnou buňku. Tím, že jsme klikli na list, zatímco jsme drželi SHIFT, se vzorec ve funkci SUMA změnil. Teď je ve formátu A:C a odkaz na buňku součtu.

Tím jsme funkci SUMA řekli, že má sečíst všechny buňky E2 na listech od A do C.

3D suma je zajímavá i v tom, že bude reagovat na přidané listy. Zkopírujeme list C, vytvoříme duplikát a přejmenujeme ho na list D. A vložíme ho před list C. Přepneme se na list "Přehled" a jak vidíme, tím, že jsme nový list vložili mezi list A a C, se součet automaticky zahrnul do sumy.

Samozřejmě nemusíte 3D funkci tvořit označováním listů, ale když znáte syntax 3D sumy, tak celou funkci můžete napsat. Napsali bychom =SUMA('A:C'!E2), kde bychom v jednoduchých uvozovkách napsali A:C, vykřičník a odkaz na buňku se součty. Funkci ukončíme a potvrdíme.

Druhá možnost: Součet bez mezisoučtů

Stejně tak bychom buňky mohli sečíst bez mezisoučtů. Napsali bychom funkci SUMA, kde bychom klikli na list A, označili buňky, které chceme sečíst, drželi klávesu SHIFT a překlikli se na poslední list C.

3D funkce můžete použít i ve spojení s pojmenovanými oblastmi. Klikneme na list A, kde pojmenujeme sloupec tržeb. Klikneme na kartu Vzorce a vybereme Definovat název. Oblast pojmenujeme jako "Tržba". A jako oblast označíme buňky tržeb. Teď za odkaz na list A napíšeme dvojtečku a list C a potvrdíme název.

Zamykání buněk pro ochranu dat

Další velice užitečné je využít zamykání buněk. Jako autor budete moci určit jen ty buňky, které půjdou přepisovat (měnit). Pokud si vytvoříte nějaký populární sešit (fakturu, docházkový list, ...), bude se vám hodit uzamčení buněk. To znamená, že buňky nepůjdou přepsat (až na těch pár, které necháte odemčeny).

V následně zobrazeném dialogovém okně Uzamknout list můžete nastavit heslo (pokud chcete uživateli zamezit odemknutí). Pozor: Heslo není nepřekonatelné, na internetu jsou k dispozici prográmky, které ho dokáží zjistit (odstranit).

Označování buněk, řádků a sloupců

Když už jste na správném listu, naučte se po tomto listu pohybovat. Nejčastěji používaná je kombinace myši a klávesových zkratek. Pokud zvládáte pohyb po listech, je vhodné umět označit část buněk (například pro kopírování, přesun).

Většinou je aktivní jen jedna buňka. Má kolem sebe silnější ohraničení. Pomocí kurzorových kláves se můžete přesunout na požadovanou buňku.

  • Řádek označíte kliknutím na číslo řádku.
  • Sloupec označíte kliknutím na písmenu sloupce.
  • Pro označení celé tabulky stačí mít aktivní buňku v této tabulce a využít klávesovou zkratku Ctrl + *. Poznámka: tabulka musí být souvislá oblast.

Skrytí řádků a sloupců

Pro BFU (Běžný Franta Uživatel) stačí skrýt sloupce/řádky s výpočty a tento uživatel se je již nezobrazí. Skrýt řádky (sloupce) umíte tak, že označíte řádky/sloupce za/pod tabulkou a poté je skryjete.

Kopírování, vyjímání a vkládání

  • Jak zkopírovat řádek, sloupce, buňku: Použijte Ctrl+C. Máte hotovo (ve schránce máte požadované údaje).
  • Vyjmout: Ctrl+X je pro vyjmout (tj. ikona Vyjmout) a data máte ve schránce.
  • Vložit: Ctrl+V.

Pokud kopírujete buňku, ve které je výpočet, nemusí fungovat tak, jak potřebujete. Na vině je vlastní zadání výpočtu (funkce, vzorce), zda je zadán relativně nebo absolutně. Kopírovat lze i trochu jinak, například jen hodnoty, jen formát, tabulku při kopírování transponovat - použijte Vložit jinak...

Změna šířky řádků a sloupců

Pro změnu šířky řádku nebo sloupce postupujte takto:

  1. Požadované řádky (sloupce) označíte.
  2. Jakmile máte označeny sloupce (řádky), přesunete se kurzorem myši mezi sloupce.
  3. Ikona kurzoru se změní. Klikněte a roztažením (stažením) upravte velikost.

Všechny označené sloupce (řádky) budou mít stejnou šířku (výšku).

tags: #excel #upevneni #listy #jak #na #to

Oblíbené příspěvky: