V předešlém videu jsme si ukázali 15 triků v Excelu, které vám usnadní nebo urychlí práci. V dnešním videu se podíváme na dalších zajímavých 10 triků, které z vás udělají excelového profíka. Od jednoduchých triků s kopírovat a vložit, po triky s automatikou funkcí SUBTOTAL až po triky s automatickým filtrem.
Excelový soubor ke stažení
Triky v Excelu, které z vás udělají profíka
1. Kopírovat a vložit
Každý uživatel Excelu ví, že lze zkopírovat jakéhokoliv hodnoty, které jsou výsledkem vzorce a vložit je jen jako hodnoty. V příkladu máme v tabulce tři sloupce, v jednom sloupci máme množství a ve druhém cenu. Ve třetím sloupci máme jednoduchý výpočet na celkovou cenu, což je množství vynásobené cenou. Pokud bychom tyto hodnoty, které jsou výsledkem vzorce, chtěli vložit pouze jako hodnoty, tak máme několik způsobů, jak to udělat. Můžeme hodnoty ve sloupci C3:C22 označit, zkopírovat pomocí klávesové kombinace CTRL+C a vložit znovu pomocí záložky v horní liště v Excelu, kde vybereme záložku Vložit, rozkliknete šipku a vyberete Vložit jako hodnoty.
Nebo můžeme hodnoty zkopírovat a kliknout pravým tlačítkem myši a vybrat Vložit jako hodnoty. A nebo můžeme použít tento trik.
Označíme hodnoty, které chceme vložit jako hodnoty (C3:C22), najedeme na okraj označeného sloupce až se z kurzoru myši vytvoří černý kříž, zmáčkneme pravé tlačítko myši, tlačítko držíme a přetáhneme označený sloupec doprava, zatímco držíme stále pravé tlačítko myši. Když hodnoty přesuneme na požadované místo (E3:E22), tak pustíme pravé tlačítko myši a objeví se menu (Obrázek č.1), kde máme i možnost Zkopírovat sem jen hodnoty.
2. Filtr dle vybrané položky – automatický filtr
Pokud často používáte jednoduchý filtr nad tabulkou, tak se vám bude hodit následující trik. Nejprve si ale do panelu Rychlého přístupu budeme muset přidat novou ikonu Automatického filtru. Pravým tlačítkem myši klikneme na panel Rychlého přístupu. Pokud nevíte, kde panel Rychlého přístupu máte, tak klikněte pravým tlačítkem myši na horní lištu v Excelu a vyberte Přizpůsobit pás karet. V okně vlevo máte možnost kliknout na Panel nástrojů Rychlý přístup. V rozbalovacím seznamu Zvoli příkazy z vybereme Všechny příkazy a najdeme položku Automatický filtr. Přidáme Automatický filtr do panelu a potvrdíme.
V liště Excelu by se měl objevit nový panel Rychlého přístupu. Pokud ne, tak opět klikněte na horní lištu a vyberte Zobrazit panel nástrojů Rychlý přístup. Někomu se lišta Rychlého přístupu zobrazí úplně nahoře a někomu nad příkazovým řádkem. V liště Rychlého přístupu vidíme novou ikonu Automatického filtru. Řekněme, že chceme vyfiltrovat tabulku pro produkt A, klikneme v tabulce na produkt A a klikneme na Automatický filtr, tabulka se okamžitě vyfiltrovala. Tento trik ovšem nefunguje na vícenásobný výběr a nefunguje ani u oficiální excelové tabulky.
3. Filtr tabulky – automatický filtr
A automatického filtru ještě zůstaneme. Řekněme, že z tabulky chceme vyfiltrovat položky, které mají celkové tržby vyšší než 5 000 Kč. Pokud bychom nepoužili Automatický filtr, tak bychom museli nad tabulkou uplatnit jednoduchý filtr pomocí klávesové zkratky CTRL+SHIFT+L nebo kliknou do tabulky a na liště v Excelu vybrat Seřadit a filtrovat > Filtr. Ve filtru bychom vybrali položky, které jsou vyšší než 5 000 Kč, nebo bychom použili možnost Filtry čísel a Větší než 5 000 Kč. Když máte ale aktivovaný Automatický filtr, tak si ušetříte spoustu klikání. Jednoduše pod sloupec Celkem napíšeme >5000, označíme tuto buňku (C23) a klikneme na Automatický filtr. Tabulka se vyfiltruje na jedno kliknutí myši.
4. Automatický SUBTOTAL
V dalším příkladu máme tabulku, ve které máme aktivovaný jednoduchý filtr. Tabulku vyfiltrujeme pro několik produktů. Teď bychom chtěli spočítat celkové množství pro vyfiltrované produkty. Pokud pod tabulku do buňky B30 napíšeme SUMA, tak se nám sečtou všechny hodnoty, i ty které jsou odfiltrované. Museli bychom tedy v buňce B30 použít funkci SUBTOTAL. Věděli jste ale, že stačí kliknout do buňky, kde chcete mít celkový součet B30 a použít Automatickou SUMU? Automatická suma, kterou najdete na liště Domů > Úpravy > Automatická SUMA, totiž rozpozná, že se jedná o filtrovanou tabulku a místo funkce SUMA uplatní funkci SUBTOTAL. To samé platí i pro klávesovou zkratku pro automatickou SUMU, ALT+=. Jediná podmínka je, že musíte tabulku nejprve vyfiltrovat. Pokud tabulka vyfiltrovaná nebude, automatická suma vloží prostou funkci SUMA.
5. Řada písmen
Potřebovali jste někdy vyplnit řadu písmen abecedy, napsali jste první dvě písmena a,b, stáhli jste buňky dolů, a doufali jste, že se vám doplní postupně ostatní písmena? Jak většina z nás zjistila, u písmen to v Excelu takto nefunguje. Alespoň ne v základním nastavení Excelu. Můžeme ovšem Excel přimět k tomu, aby to napříště udělal. Stačí vložit písmena jako vlastní nový list v Excelu. V horní liště vybereme Soubor a ve výběru Možnosti a zde vybereme kartu Upřesnit. Sjedeme až dolů a najdeme Upravit vlastní seznamy.
Ve vyskakovacím okně Vlastní seznamy uvidíme několik základních seznamů. Pokud chceme seznam přidat, stačí kliknout do pole Položky seznamu a jednoduše písmena abecedy vypsat, oddělená čárkou. Když je máme vypsaná klikneme na Přidat. Potvrdíme a vrátíme se do Excelu. Teď stačí napsat písmeno a, buňku označit, stáhnout buňku dolů a abeceda se bude postupně doplňovat. Jelikož jste tento seznam uložili jako nový seznam, tak vám to bude fungovat i po otevření nového Excelu.
6. Najít a nahradit
V dalším příkladu máme seznam, ve kterém máme barevně vyznačené některé produkty. Naším úkolem je změnit oranžové buňky na zeleno. Samozřejmě můžeme jít a manuálně označit každou oranžovou buňku a když máme všechny buňky označené, tak u nich naráz změnit barevnou výplň. A nebo použijeme trik s Najít a nahradit. Použijeme klávesovou kombinaci CTRL+F, čímž se otevře okno Najít. V okně Najít rozklikneme Možnosti. Zde můžeme nastavit, co hledáme, a to nejen slovo a číslo, ale i barvu nebo formát. Hledáme oranžové buňky, takže klikneme na Formát a ve Výplni vybereme barvu, kterou hledáme. Potvrdíme a vybereme možnost Najít vše. V dolním okně se objeví všechny oranžové buňky, stačí teď zmáčknout kombinaci CTRL+A, pro celkový výběr, čímž se označí všechny buňky a toto okno zavřít. Tím zůstanou všechny buňky označené a teď můžeme jednoduše u všech najednou změnit barvu. Stejně tak bychom barvu mohli nahradit tak, že místo zavření okna Najít bychom vybrali možnost Nahradit. Jako najít máme nastavenou oranžovou barvu a teď jen vybereme barvu, kterou chceme oranžovou nahradit. Potvrdíme, že chceme nahradit vše a úkol je hotový.
Tento trik můžeme použít i pro nahrazení formátu. Ve vedlejší tabulce máme některé produkty napsané kurzívou, podtržené a tučně, chceme tento formát nahradit normálním klasickým formátem. Zmáčkneme tedy klávesovou kombinaci CTRL+H pro otevření okna Nahradit. Jelikož v buňce máme několik formátů najednou, tak si můžeme pomoci tak, že u formátu najít rozklikneme šipku a vybereme Zvolit formát buňky, klikneme do jedné buňky, která je zformátovaná (D7). Tím se přenese hledaný formát. V buňce formát Nahradit vybereme opět formát z buňky a označíme libovolnou nezformátovanou buňku (třeba D8), potvrdíme Nahradit vše.
7. CONVERT funkce
Funkce CONVERT se vám bude hodit, pokud často přepočítáváte jednotky. Řekněme, že máme následující hodnotu 100 kg (B2) a musíme tuto hodnotu vyjádřit v gramech, librách a uncích. Žádný problém, stačí použít funkci CONVERT. Do buňky, kde chceme mít gramy (C2) napíšeme funkci CONVERT, kde nejprve označíme buňku s množstvím ($B$2), kterou zafixujeme klávesou F4, následně uvedeme z jaké jednotky převádíme, tedy z kil, v uvozovkách “kg”, a následně na jakou jednotku převádíme. Převádíme na gramy, tedy písmeno “g” v uvozovkách. Potvrdíme a 100 kilo se převedlo na gramy. To samé uděláme i pro libry. Následně to samé uděláme i pro unce.
Převádět jednotky můžete i v délce. V další tabulce máme metry, palce, stopy a míle. Opět nejprve napíšeme funkci CONVERT, následně označíme 100 metrů ($B$6), buňku zafixujeme, a nejprve vybereme palce. Potvrdíme a vzorec přetáhneme doprava, změníme palce na stopy, potvrdíme a přetáhneme doprava a změníme stopy na míle. Hotovo.
8. Vytvořit z výběru
Na rychlé pojmenování oblastí buněk existuje následující trik. Místo toho, abyste každou oblast buněk pojmenovávali zvlášť za pomocí definování názvu, tak na kartě Vzorce vyberte možnost Vytvořit z výběru. Nejprve si tabulku označíme, včetně záhlaví, a na kartě Vzorce vybereme Vytvořit z výběru. Jednotlivé oblasti chceme pojmenovat podle názvů v záhlaví, takže vybereme možnost Horní řádek a potvrdíme. Teď když označíme hodnoty ve sloupci produkt, tak vidíme, že je oblast pojmenovaná jako Produkt. To samé platí i pro ostatní sloupce.
9. Rozdělení obrazovky
Při práci s rozsáhlými tabulkami se vám může hodit, když si rozdělíte obrazovku na dvě poloviny. Dovolí vám to jednodušeji si srovnat data. V příkladu máme rozsáhlou tabulku a potřebujeme mezi sebou srovnat květen 2020 a květen 2022. Jak to udělat jednoduše? První věc, kterou bychom měli udělat je ukotvit příčky, aby nám neutíkalo záhlaví a boční panel. Klikneme do buňky B2, což je první buňka s daty pod záhlavím a vedle bočního panelu. Na kartě Zobrazení vybereme Ukotvit příčky a znovu Ukotvit příčky. Teď máme jistotu, že ať se budeme pohybovat kamkoliv, že uvidíme jak záhlaví, tak boční panel.
Zbývá rozdělit obrazovku, tak aby se nám lépe srovnávala data. Začneme tím, že klikneme do tabulky a na kartě Zobrazení vybereme Rozdělit. Okamžitě se nám v sešitě objeví šedé čáry. Teď stačí přetáhnout boční čáru za měsíc, který chceme srovnávat, tedy květen 2020. Obrazovka se tím rozdělí na dvě samostatná okna. Když se teď posuneme v Excelu za šedou čárou doprava, tak nám vlevo na obrazovce květen 2020 zůstane zamrzlý. V obrazovce vpravo dojedeme tedy až ke květnu 2022 a máme rázem tyto měsíce vedle sebe. Vrchní čáru nepotřebujeme, tak ji chytíme a umístíme ji mimo tabulku, čímž zmizí.
10. Buňky se vzorci a bez vzorců
Pokud musíte někdy po někomu kontrolovat excelové soubory, tak se vám může hodit i následující trik. Řekněme, že máme následující tabulku a chceme rychle zkontrolovat, kde se vyskytují v tabulce vzorce. Stačí na kartě Domů vybrat Najít a vybrat a zde rozkliknout Přejít na jinak. Zde zaškrtněte Vzorce a potvrďte. Okamžitě se vám označí buňky, které na listu obsahují vzorce. Pokud byste naopak potřebovali zvýraznit buňky, které vzorce neobsahují, tak vyberte Najít a Nahradit a Přejít na jinak a místo Vzorce vyberte Konstanty. Označí se vám pouze konstanty ve vybraném listu. Pokud by vás zajímaly konstanty pouze ve sloupci, tak nejprve sloupec označte, vyberte Přejít na jinak a Konstanty. Označí se vám pouze konstanty ve vybraném sloupci.
