V dnešním videu si ukážeme další triky v Excelu jako pokračování předešlých oblíbených videí na Akademii Excelu. V předchozích několika videích jsme si ukázali spoustu triků, jak pracovat v Excelu efektivněji, ukázali jsme si 10 triků s kontingenčními tabulkami, triky s klávesovými zkratkami CTRL+ENTER, 5 triků pro práci s více listy, triky s nástrojem Najít a Nahradit, 20 triků s kopírovat a vložit, ukázali jsme si 15 triků k odstranění chyb v Excelu. Na populární videa o tricích v Excelu jsme následně navázali ve videu Dalších 10 triků v Excelu, které z vás udělají profíka a Další excelové tipy a triky. V neposlední řadě jsme si ukázali i užitečné triky s funkcí SVYHLEDAT nebo v nástroji Power Query. Dnes na tato videa navážeme a ukážeme si dalších několik triků v Excelu, které se vám mohou hodit.
Excelový soubor ke stažení
Přehlednější datumová osa v grafu
Někdy když máte v tabulce hodně dat, ze kterých tvoříte graf, tak může datumová osa v grafu působit nepřehledně. Datumovou osu můžeme ale zobrazit i přehledněji a to pomocí malé úpravy. V tabulce máme datumy a tržby. Pomůžeme si dvěma pomocnými sloupci.
V prvním sloupci určíme rok. A rok nechceme na každém řádku, ale jen na řádku, kde je leden. Takže použijeme podmínkovou funkci KDYŽ, anglicky funkci IF. Ve funkci KDYŽ bude podmínka, že pokud je pořadové číslo měsíce rovno jedné, to znamená, že se jedná o leden, takže funkce MĚSÍC, anglicky funkce MONTH, kde označíme datum a to se musí rovnat jedničce. A pokud je podmínka splněná, tak chceme vrátit rok z datumu, takže funkce ROK, anglicky funkce YEAR, kde označíme datum. A pokud podmínka splněná není, tak chceme vrátit prázdnou buňku. Funkci potvrdíme a stáhneme ji pro ostatní řádky dolů.
A vedle ještě definujeme měsíc a to slovně. Funkce HODNOTA.NA.TEXT, anglicky funkce TEXT, kde označíme datum a jako formát „mmmm“. Funkci potvrdíme a stáhneme dolů. Teď tyto pomocné sloupce vložíme do grafu.
Označíme graf a vybereme Vybrat data. Vybereme Upravit osu x, kde máme datumy a nahradíme původní data těmito dvěma novými sloupci bez záhlaví tabulky.
Potvrdíme nahrazení dat a datumová osa v grafu se nahradila.
Součet kvartálních dat
V tabulce máme datumy a tržby. Vedle v tabulce potřebujeme sečíst kvartální hodnoty. Nicméně nechce se nám sčítat tržby postupně tím, že budeme sčítat ručně vždy tři měsíce. Začneme s funkcí ROUNDUP, pomocí které určíme, které měsíce patří do kterého kvartálu. Ve funkci ROUNDUP napíšeme funkci MĚSÍC, anglicky funkci MONTH, kde označíme datumy. A to vydělíme trojkou a zaokrouhlujeme na celá čísla. Funkce vrací sérii jedniček, dvojek, trojek atd., a to podle toho, do kterého kvartálu daný měsíc patří.
Celá tato funkce se rovná funkci řádky, kde vytvoříme dynamické rozpětí buněk od buňky, ve které se právě nacházíme. Takže buňka, ve které tvoříme funkci, dvojtečka a znovu ta stejná buňka, přičemž první buňku zafixujeme pevně klávesou F4. Výsledkem této funkce budou pravdy a nepravdy. Abychom tuto funkci mohli použít pro sečtení hodnot, tak musíme pravdy a nepravdy převést na jedničky a nuly a to uděláme pomocí dvojitého negativu před funkcí, takže celou tuto funkci zabalíme do závorek a před ně napíšeme dvojitý negativ. Takže před to musíme dát dvojitý negativ.
Tuto celou funkci vynásobíme hodnotami v tabulce. Teď to stačí sečíst. Buď můžeme použít funkci SUMA nebo SOUČIN.SKALÁRNÍ. Použijeme funkci SOUČIN.SKALÁRNÍ, anglicky funkci SUMPRODUCT, jelikož tato funkce nepotřebuje pro potvrzení klávesy CSE. Potvrdíme funkci a stáhneme ji dolů.
A máme sečtená kvartální data.
Mini grafy v buňce
Věděli jste, že můžete vytvořit malé grafy v buňce pomocí funkce? V jednom z videí na Akademii Excelu jsme si ukázali, jak pracovat s mini grafy v Excelu. Mini graf je velmi malý graf, který se vejde do jedné buňky. Malý graf v buňce ale můžete vytvořit i díky funkci OPAKOVAT. Ve sloupci máme několik čísel, nad sloupci bychom chtěli vyjádřit součet jedniček nikoliv klasickým součtem ale sloupcovým grafem v buňce.
Použijeme k tomu funkci OPAKOVAT, anglicky funkci REPT. Ve funkci OPAKOVAT se nejprve vyplňuje text, který chceme opakovat. Sloupcový graf vytvoříme ze svislé čáry. Takže napíšeme znak svislé čáry a jelikož se jedná o text, tak bude svislá čára v uvozovkách. Druhým parametrem je počet a o počtu opakování rozhoduje součet hodnot ve sloupci, takže funkce SUMA, kde sečteme hodnoty ve sloupci. Funkci OPAKOVAT ukončíme a potvrdíme a funkce OPAKOVAT vrátila pět svislých čar.
Teď to ještě jako sloupcový graf nevypadá. Označíme buňku s čárkami a změníme u nich styl písma na Stencil. Po potvrzení se čáry jakoby slejí dohromady. Necháme označenou buňku a změníme velikost písma na 36. Tím se sloupec zvětší. Stále necháme buňku označenou a ještě změníme směr otočení textu. Vybereme Otočit text nahoru a teď už máme sloupcový graf v buňce. Teď přetáhneme funkci doprava a máme nad hodnotami malé sloupcové grafy v buňce.
Samozřejmě u nich můžeme změnit barevnou výplň, takže stačí vybrat barva textu a vybrat si požadovanou barvu. Malý graf v buňce pomocí funkce je hotový.
Převod textu na číslo
Občas se vám stane, že máte čísla naformátovaná jako text, což většinou poznáte buď podle zarovnání čísla v buňce nebo podle zeleného trojúhelníku v buňce. Tak jako tak, když chcete s těmito čísly počítat, tak je nejprve musíte převést na číslo. Což vidíme i na funkci SUMA, která místo toho, aby hodnoty sečetla, tak nepočítá, jelikož jsme ji místo čísel dodali text. Nejčastějším způsobem převodu je to, že se buňky označí a v nabídce vykřičníku se vybere Převést na číslo. Když ale máte takových sloupců na listů hodně, tak to může zabrat dost času. Zároveň si všimněte, že funkce SUMA se sama neopravila, když jsme čísla převedli na čísla. Museli bychom do funkce kliknout a znovu potvrdit ENTEREM. To je dost manuální práce, pokud máte takových buněk a funkcí na listu hodně.
Existují dva rychlejší způsoby, jak si s převodem a opravou funkce poradit. První možností převodu textu na číslo je pomocí nástroje Text do sloupce. V takovém případě označíme sloupec s textem a vybereme na kartě Data Text do sloupce a nic neměníme a jen dokončíme rozdělení. Po potvrzení se převede text na čísla.
Druhou možností, která je ještě rychlejší je, že někde do buňky napíšeme jedničku. Tuto jedničku zkopírujeme a následně označíme všechny sloupce, kde máme text. Sloupce postupně označujeme a držíme klávesu CTRL. Teď na kartě Domů vybereme Vložit a Vložit jinak.
V nabídce vybereme Násobit. Tím se všechny označené hodnoty vynásobí jedničkou, což text převede na číslo.
Ještě musíme opravit funkce SUMA. Označíme celý řádek, kde máme funkce a zmáčkneme CTRL+H pro vyvolání nástroje Nahradit. A co budeme nahrazovat? Rovná se nahradíme znaménkem rovná se a vybereme Nahradit vše. Toto nahrazení resetuje všechny funkce a tím se funkce opraví a začnou počítat. Opraveno.
Nahrazení nechtěných znaků
V dalším příkladu máme jméno a příjmení zaměstnanců, ale mezi jménem a příjmením se vyexportoval i nechtěný znak svislá čára a u některých jmen ještě otazník. Těchto nechtěných znaků se můžeme zbavit pomocí funkce DOSADIT, neboli anglicky funkce SUBSTITUTE. Napíšeme funkci DOSADIT, kde prvním parametrem je text, což je buňka s nechtěnými znaky. Jako druhý parametr se vyplňuje starý znak, tedy ten, který chceme nahrazovat. Nejprve potřebujeme nahradit svislou čáru, takže v uvozovkách napíšeme svislou čáru a čím ji chceme nahradit? Svislá čára je vždy mezi jménem a příjmením, takže ji chceme nahradit mezerou a jelikož se opět jedná o text, tak mezera musí být v uvozovkách. Funkci ukončíme a potvrdíme a pošleme ji dolů a svislá čára z buněk zmizela. Ještě se musíme vypořádat s otazníkem. To vyřešíme druhou funkcí DOSADIT. První funkci DOSADIT zabalíme do druhé funkce DOSADIT, kde textem je teď první funkce DOSADIT a starým znakem je otazník v uvozovkách a čím ho chceme nahradit? Otazník máme většinou ve slově, takže ho potřebujeme nahradit ničím, takže dvěma uvozovkami. Ukončíme funkci, potvrdíme a stáhneme ji dolů a máme odstraněné nechtěné znaky z buněk a vyčištěný text.
O nahrazování nechtěných hodnot a čárek a háčků máme na Akademii Excelu celé video.
Jedna odpověď
😊