Další excelové triky a tipy v Excelu | Excelové triky

V dnešním videu navážeme na předešlá populární videa o tricích a tipech v Excelu. V předchozích videích jsme si ukázali 15 triků pro práci v Excelu a pak dalších deset triků v Excelu. Stejně tak máme na Akademii Excelu videa 15 triků k odstranění chyb v Excelu, 20 triků s Kopírovat a Vložit a Triky s funkcí Najít a Nahradit. Dnes jsem pro vás posbírala dalších 10 tipů, jak v Excelu pracovat rychleji nebo efektivněji.   

Excelový soubor ke stažení

Další excelové triky a tipy v Excelu

A vrhneme se na první triky.

1. Fixace sloupců v oficiální excelové tabulce

Jednou z věcí, se kterou se uživatelé excelových tabulek perou, je to, že při přetahování a kopírování funkcí, které se odkazují na pole v excelové tabulce, se posouvají i sloupce, jelikož v excelových tabulkách neexistuje fixace. V excelové tabulce máme tržby produktů v jednotlivých měsících. Vedle do tabulky chceme u pro každý produkt sečíst tržbu (E3:H3). Tržby sečteme pomocí funkce SUMIF

K prvnímu produktu napíšeme funkci SUMIF (E3), kde nejprve označíme sloupec s produkty (B3:B14), následuje hledaný produkt (E2), a jako poslední parametr sloupec s tržbami (C3:C14). Jediné pole, které v tomto vzorci můžeme zafixovat je pole produktu (E2), které se odkazuje na buňku mimo excelovou tabulku. Pole produktu zafixujeme pro řádky klávesou F4. Funkci potvrdíme.

Zkusíme přetáhnout funkci doprava pro ostatní produkty (F3:H3) a na první pohled je patrné, že funkce nefunguje. Klikneme do buňky u druhého produktu a hned vidíme proč. Přetažením funkce se změnila poloha nezafixovaných sloupců. Tento problém jde vyřešit dvěma způsoby. Prvním je, že funkci nepřetáhneme, ale zkopírujeme. Označíme funkci (E3), zkopírujeme ji (CTRL+C) a označíme buňky, kam chceme funkci vložit (F3:H3) a jednoduše ji do buňky vložíme pomocí kláves CTRL+V.

Druhou možností je označit první pole, kde máme funkci (E3), označit i další buňky, kam chceme funkci vložit (E3:H3) a zmáčknout CTRL+R. Tato klávesová kombinace totiž zkopíruje obsah první buňky do ostatních označených buněk, což v případě excelových tabulek funguje i na funkce.

Další triky v Excelu 1
Obrázek č.1 Fixace sloupců v excelové tabulce

2. Kopírování buněk bez porušení formátu

V dalším příkladu máme tabulku, kde na prvním řádku máme vyplněný produkt. Řekněme, že potřebujeme tyto hodnoty stáhnout dolů pro ostatní buňky. Chceme tedy nakopírovat produkt A na všechny řádky. Jak vidíme, tak máme v tabulce ale na určitých řádcích formát buněk. Pokud bychom hodnoty chytili a protáhly buňky dolů, tak se hodnoty sice nakopírují, ale zruší se tím jakýkoliv formát, který jsme v tabulce měli. Řešením je označit buňky, najet myší na pravý spodní roh, tak jako kdybychom chtěli hodnoty klasicky stahovat dolů. A místo levého tlačítka myši stisknout pravé tlačítko a stáhnout hodnoty dolů. Když pravé tlačítko pustíme, tak se objeví výběr, mezi kterým máme i možnost Vyplnit bez formátování. Potvrdíme a hodnoty se nakopírovali, a to bez porušení formátu. Více triků s kopírovat a vložit najdete ve videu 20 triků s Kopírovat a Vložit

Další triky v Excelu 2
Obrázek č.2 Kopírování bez porušení formátu

3. Jak přidat nebo odebrat symbol % bez změny čísla

V dalším příkladu máme tabulku produktů a u nich máme uvedené jejich marže. Nicméně nám někdo marže vyplnil jako celé číslo a nikoliv jako procento. Pro další výpočty ovšem potřebujeme, aby hodnota marže byla uvedená v procentech a ne jako celé číslo. Můžeme se pokusit změnit formát čísla na procenta. Označíme hodnoty a na horní liště v Excelu vybereme procenta. Touto změnou formátu se ovšem z hodnoty 10 stane 1000%, což ovšem není změna, kterou potřebujeme. 

Existují dva jednoduché triky jak to vyřešit. Označíme hodnoty a klikneme na ně pravým tlačítkem myši a vybereme Formát buněk. Zde místo procent vybereme Vlastní formát. Do příkazového řádku napíšeme místo všeobecného formátu 0,obrácené lomítko a znak pro procenta. V náhledu vidíme, že se celé číslo změnilo na procenta a zůstalo tak jak ho potřebujeme. Formát potvrdíme a všechna čísla jsou převedená na správná procenta.

Obrázek č.3 Změna obecného čísla na procenta

Druhou možnost, jak hodnoty převést na procenta si ukážeme vedle ve sloupci. Někam do pomocné buňky napíšeme 1 % (E3). Zkopírujeme hodnotu jednoho procenta a označíme hodnoty, které potřebujeme převést (C3:C14). Klikneme pravým tlačítkem a vybereme Vložit jinak. Zde v okně vybereme násobit. Potvrdíme a tím, že jsme hodnoty vynásobili 1 %, tak se formát převedl na procenta bez změny samotného čísla. 

Tento trik by fungoval i na odebrání znaku procenta. Někam vedle buněk napíšeme hodnotu 1, zkopírujeme jedničku, označíme hodnoty procent v tabulce, pravé tlačítko myši a Vložit jinak a zde vybereme Násobit. A hodnoty se tím samým trikem převedou zpátky na celá čísla.

Další triky v Excelu 4
Obrázek č.4 Změna obecného čísla na procento

4. Měnící se formát čísla v buňce

Tento trik využijete zejména na excelových přehledech neboli dashboardech. Z následující tabulky chceme na základě výběru produktu spočítat buď součet tržeb a nebo počet prodaných produktů. V buňce mám předpřipravenou funkci, která reaguje na výběr v rozbalovacím seznamu. Pokud v rozbalovacím seznamu vybereme Součet, tak funkce vrátí hodnotu součtu tržeb. Pokud vybereme Počet, tak funkce vrátí počet prodaných kusů vybraného produktu. Problém je ovšem s formátováním. U součtu tržeb chceme hodnotu zobrazit v české měně. Pokud na buňku klikneme a změníme formát na měnu, tak po změně na počet se formát měny uplatní i na hodnotu prodaných kusů, což je špatně. Správný formát můžeme nastavit pomocí podmíněného formátu. Označíme buňku a na kartě Domů vybereme Podmíněné formátování, kde vybereme Nové pravidlo a určit pravidlo pomocí vzorce. Pravidlo je, že pokud se buňka rozbalovacího seznamu rovná „Součet“, tak chceme změnit formát. Vybereme formát, kde vybereme číslo a měnu bez desetinných míst. Potvrdíme. Teď jelikož máme ve výběru součet, tak buňka ukazuje měnu, když ale výpočet změníme na počet, tak u čísla zůstane obecný formát.

Obrázek č.5 Měnící se formát v buňce

5. Generátor náhodných čísel

Pokud někdy v Excelu potřebujete vytvořit sestavu náhodných čísel, tak se vám bude hodit následující trik. Řekněme, že do následující tabulky máme doplnit náhodné hodnoty tržeb. Místo toho, abychom čísla vymýšleli, tak můžeme použít funkci RANDBETWEEN. Označíme prázdný sloupec tržeb, a do první buňky napíšeme funkci RANDBETWEEN, kde si zvolíme limitní čísla mezi kterými se mají náhodná čísla zobrazovat. Zkusíme třeba 100 až 500. Funkci potvrdíme stisknutím kláves CTRL+ENTER, tím se funkce doplní do všech označených buněk. Funkce RANDBETWEEN reaguje změnou sestavy při každém stisknutí klávesy ENTER. Pokud nechceme, aby se čísla měnila s každým stisknutím klávesy ENTER, tak čísla označíme, stiskneme pravé tlačítko myši a použijeme trik s přesunem hodnot doprava, posuneme hodnoty o jeden sloupec doprava a zase ho hned vrátíme zpět. Tím, že jsme celou dobu drželi pravé tlačítko myši, tak se po puštění tlačítka objeví nabídka, kde vybereme Zkopírovat sem jen hodnoty. Funkce RANDBETWEEN zmizela a zůstala pouze náhodná čísla.

Obrázek č.6 Funkce RANDBETWEEN pro náhodná čísla

6. Přidání datové sady do grafu

V dalším příkladu máme malou tabulku dat, ze kterých jsme vytvořili sloupcový graf pro produkt A. Po vytvoření grafu nám do tabulky ale přibyla nová data pro nový produkt B. Tato nová data chceme rovněž zobrazit v grafu. Klasicky by se data do grafu přidala tak, že bychom klikli pravým tlačítkem myši na graf a vybrali Vybrat data, kde bychom přidali datovou sadu. Nejrychlejším způsobem, jak tato nová data dostat do grafu je ovšem hodnoty včetně záhlaví označit, zkopírovat pomocí klávesové kombinace CTRL+C, kliknout na sloupcový graf a jednoduše data vložit pomocí CTRL+V. Nová datová sada se tím přidá do grafu.

7. Prázdný řádek za řádkem v tabulce

V dalším příkladu se po nás chce, abychom z nějakého důvodu za každý produkt vložili prázdný řádek. Museli bychom tedy na každý řádek kliknout a přes pravé tlačítko myši vybrat Vložit řádek a nebo si pomoci klávesovou zkratkou CTRL+. To bychom museli udělat pro každý řádek. Nebo použijeme následující trik. Vedle tabulky k hodnotám napíšeme pořadová čísla. K prvnímu produktu napíšeme jedničku a abychom čísla nemuseli vypisovat, tak můžeme označit jedničku a stisknout klávesu CTRL a stáhnout buňku dolů, tím se doplní pořadová čísla. Nebo označíme jedničku, stiskneme pravé tlačítko myši a stáhneme buňku dolů. Po puštění pravého tlačítka se objeví nabídka, ze které vybereme Vyplnit řady. Tak jako tak máme pro každý řádek pořadové číslo. 

Další triky v Excelu 7
Obrázek č.7 Pořadová čísla

Teď tato čísla označíme, zkopírujeme a vložíme je pod tabulku. Teď klikneme do tohoto sloupce s pořadovými čísly a na kartě Data vybereme Seřadit. Chceme tabulku seřadit podle sloupce C a to od nejnižších hodnot po nejvyšší. Potvrdíme. 

Obrázek č.8 Seřazení pořadových čísel

Tím se pořadová čísla, která měla prázdný řádek přesunula k produktům a vytvořila tím prázdný řádek za každým řádkem v tabulce. Když jsme hotovi, tak tento pomocný sloupec můžeme smazat.

Další triky v Excelu 10
Obrázek č.9 Prázdný řádek za každým produktem

8. Rychlá oprava dat

V dalším příkladu máme v tabulce chybu v datumech. Někdo nám omylem vyplnil mezi datumy i neexistující datum 31. února. Jak vidíme, tak se ani toto datum nelíbí Excelu, jelikož ho neuznal jako datumovou hodnotu, ale udělal z ní textovou hodnotu, což poznáme podle rozdílného zarovnání v buňce. Správné datum je 28.2.2023. Jak nejrychleji tato data opravit, bez toho, abychom museli po jednom datum opravovat? Klikneme do tabulky a pomocí klávesové kombinace CTRL+SHIFT+L vložíme do záhlaví automatický filtr. Pomocí filtru v záhlaví datum vyfiltrujeme pouze tyto chybné datumy. Označíme sloupec s vyfiltrovanými datumy a na kartě Domů vybereme Najít a vybrat a zde Přejít na jinak. Ve vyskakovacím okně vybereme Pouze viditelné buňky. Tím se označili pouze viditelné vyfiltrované buňky v tabulce. Nikam neklikáme a jen přepíšeme datum na správné datum. Teď, místo prostého potvrzení klávesou ENTER zmáčkneme CTRL+ENTER. Tím se toto nové datum propíše do všech viditelných buněk v tabulce.

Další triky v Excelu 11
Obrázek č.10 Rychlá oprava dat v tabulce

9. Klávesové zkraty a navigace bez myši

V Excelu se můžete po horní liště pohybovat i bez použití myši. Stačí vám k tomu klávesu ALT. Pomocí klávesy ALT rovněž odhalíte i klávesové zkratky pro jednotlivé úkony. Řekněme, že chceme z této tabulky vložit kontingenční tabulku. Klikněte kamkoliv do tabulky a zmáčkněte klávesu ALT. Všimněte si, že po zmáčknutí klávesy ALT se u jednotlivých záložek horní lišty objeví písmena a čísla. Ta označují jednotlivé karty v horní liště. Vložení kontingenční tabulky se nachází pod záložkou Vložení, která má písmeno Ř. Zmáčkneme tedy klávesu Ř a přepneme se do karty Vložení. Vložení kontingenční tabulky je pod písmeny V1. Teď tedy stačí zmáčknout V1. Rozbalí se nabídka kontingenční tabulky a vložení v tabulky je pod písmenem B. Potvrdíme ENTER vytvoření kontingenční tabulky a máme vloženou kontingenční tabulku na nový list, a to jen za pomocí kláves. Klávesová zkratka pro vložení kontingenční tabulky v české verzi Excelu je tedy ALT+Ř+V1+B. Takto pomocí klávesy ALT zjistíte jakoukoliv klávesovou zkratku v Excelu. 

Obrázek č.11 Klávesové zkratky v Excelu

10. Navigační tlačítka bez VBA

Posledním trikem je pohyb po excelovém sešitu pomocí navigačních tlačítek, a to bez použití VBA. Na listu Data máme tlačítko, které nás přesměruje na přehled. Když jsme na listu přehled, tak zde máme tlačítko, které nás pošle zpátky na Data. Tato tlačítka se vám tak budou hodit, pokud se potřebujete rychle pohybovat mezi několika listy v Excelu. Začneme tím, že na list Data vložíme tvar. Karta Vložení a zde vybereme Obrazce a vybereme tvar. Tvar nakreslíme na listu. Označíme tvar a začneme psát Přehled, jelikož toto tlačítko nás pošle na list Přehled. Označíme text a trochu ho upravíme. Zvětšíme písmo, vycentrujeme nápis a uděláme ho tučný. Klikneme na tlačítko pravým tlačítkem myši a vybereme Odkaz a zde vybereme Místo v tomto dokumentu. V okně se vám zobrazí všechny listy, které máte v tomto excelovém sešitu. Vybereme list Přehled. Můžeme si dokonce vybrat specifickou buňku, kam chceme při přesunu odkázat. V základu je nastavená buňka A1. Ale můžeme to změnit třeba na C10. Potvrdíme a tlačítko je hotové. Teď když myší najedeme na tlačítko, tak se kurzor myši změní na ručičku, což značí klikatelné tlačítko. Klikneme na tlačítko a přesuneme se na list Přehled do buňky C10. To samé můžeme udělat na listu Přehled a vytvořit tlačítko, které nás zase vrátí na list Data.

Další triky v Excelu 12
Obrázek č.12 Navigační tlačítka přes odkaz

Tak toto bylo dalších deset triků a tipů v Excelu. 

MOHLO BY VÁS ZAJÍMAT

2 komentáře

  1. V bode 10. Navigační tlačítka bez VBA
    pri prípadnej tlači tabuľky je možné nastaviť aby sa navigačné tlačítko nevytlačilo spolu s tabuľkou?

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *