Vlastní formát buněk v Excelu se vám bude hodit ve chvíli, kdy potřebujete, aby vám v buňce zůstaly původní hodnoty, ale na oko se zobrazilo něco jiného. Potřebujete skrýt hodnoty? Potřebujete k číselné hodnotě dodat text, ale zároveň potřebujete s buňkou i nadále počítat? Vlastní formát buněk vám pomůže se vším. V dnešním videu Vlastní formát buněk si projdeme základní zástupné znaky a jak s nimi pracovat.
Excelový soubor ke stažení
Vlastní formát buněk v Excelu
Do nastavení vlastního formátu buňky se dostanete tak, že na vybranou hodnotu kliknete pravým tlačítkem myši, vyberete formát buněk a zde vyberete formát vlastní. Nebo můžete použít klávesovou zkratku CTRL+SHIFT+1. Nebo na horní excelové liště v pásu karet najdete sekci číslo a zde rozkliknete symbol vpravo dole a dostanete se do stejného zobrazení jako po kliknutí pravým tlačítkem myši.
Vlastní formát buněk má přednastavených několik formátů, ze kterých si můžete vybrat. Tyto přednastavené formáty můžete rovněž použít jako pomocný tahák, když nastavujete vlastní formát a potřebujete se držet nějakého vodítka. Po bližším prozkoumání přednastavených formátů zjistíte, že se ve vlastním formátu vyskytuje několik znaků. Jedná se o tzv. zástupné znaky, pomocí kterých určujete vlastní požadovaný vzhled neboli formát buňky.
| Zástupný znak | Význam zástupného znaku |
|---|---|
|
|
Zástupný znak 0 - číslice VČETNĚ nevýznamných nul |
|
# |
Zástupný znak # - číslice BEZ nevýznamných nul |
Vlastní formát buněk – zástupný znak 0
Prvním zástupným znakem je 0. Jedná se o zástupný symbol číslic, který zobrazuje číslice VČETNĚ nevýznamných nul. Pomocí znaku 0 se nejčastěji zaokrouhluje nebo se přidávají nuly před číslice.
Jako vždy si to ale ukážeme na příkladu.
Zástupný znak 0 a přidávání fixních pozic
V našem příkladu máme ve sloupci zdroj několik čísel, přičemž každé číslo je jinak dlouhé. Může se jednat například o čísla faktur. Ve sloupci požadovaný formát vidíme, jak bychom si přáli tato čísla zformátovat. Rádi bychom, aby všechna čísla měla 4 pozice, to znamená, že číslo, které je kratší než 4 místa, by tak mělo mít před číslem nuly. Do sloupce formát si zkopírujeme čísla ze sloupce zdroj a zkusíme za pomoci vlastního formátu buněk přetvořit požadovaný formát. Označíme si celý sloupec s hodnotami, klikneme na ně pravým tlačítkem myši a vybereme formát buněk. Zde vybereme možnost Vlastní. Jelikož chceme přidávat nevýznamné nuly, použijeme k formátu zástupného znaku nul. V řádku nahoře vidíme nápovědu, která nám ukazuje, jak se změní formát čísla. Pokud nejdříve napíšeme pouze jednu nulu, tak se nestane nic. Pokud přidáme druhou nulu, tak už se ale formát čísla změní. Pokud napíšeme dvě nuly, tak u čísla, které má v současné době jen jednu pozici, přibude 0 před číslem. Formát 0 tedy přinutí Excel přidat druhou pozici, a to v podobě nuly. Pokud bychom změnili formát na tři nuly, tak by se dvoumístné číslo změnilo na trojmístné. My ale potřebujeme 4 místa, takže přidáme ještě jenu nulu. A potvrdíme. Úkol jedna je splněný.
Zástupný znak 0 a zaokrouhlování
Stejně tak se symbol 0 používá pro zaokrouhlování. Z čísel ve sloupci zdroj chceme vytvořit formát čísla s dvěma desetinnými místy. Čísla si zkopírujeme, vybereme vlastní formát a do příkazového řádku napíšeme nulu, čárku a počet desetinných míst, které chceme zobrazit. Chceme zobrazit dvě desetinná místa, takže za čárkou napíšeme dvě nuly. Kdyby naopak jsme vedle měli čísla s desetinnými místy o různém počtu, tak pomocí symbolu 0 můžeme počet desetinných míst sjednotit. Máme hotovo, potvrdíme a úkol číslo dva je hotový.
Vlastní formát buněk – zástupný znak #
Další zástupný znak je křížek neboli hashtag #. Jedná se o zástupný symbol číslic, který zobrazuje číslice BEZ nevýznamných nul. Na rozdíl od znaku 0 zobrazuje variabilní pozice, nikoliv fixní. Nejčastěji tak slouží k optickému zkracování číslic.
Zástupný znak # a oddělování tisíců
V tomto příkladu máme několik číslic, pro jejichž lepší přehlednost by bylo lepší, kdybychom mezerou oddělili tisíce. Stačí použít vlastní formát. Čísla si zkopírujeme, vybereme vlastní formát a použijeme zápis křížek, mezera a tři křížky. Tento zápis nám oddělení mezerou tisíce. Jedná se o jeden z nejzákladnějších formátů čísel v Excelu, takže ho vidíte i přednastavený ve vlastním formátu. Nemusíte si to tedy pamatovat a stačí vám vybrat tento přednastavený formát.
Stejně tak lze ale jednotlivé zástupné znaky kombinovat. Ve sloupci vedle bychom rádi oddělili tisíce a ještě přidali jedno desetinné místo. Čísla si opět zkopírujeme, vybereme vlastní formát a napíšeme křížek, mezera, dva křížky, to je formát pro oddělení tisíců a 0,0. V ukázce vidíme, že máme formát správně, tak můžeme potvrdit.
V dalším příkladu máme čísla s různým počtem desetinných míst, rádi bychom číselný formát sjednotili na dvě desetinná místa. Čísla zkopírujeme, vlastní formát, a 0,00 pro dvě desetinná místa. Hotovo. Pokud se ale na buňky podíváme zblízka, tak zjistíme, že když klikneme do buňky, tak se v příkazovém řádku číslo pořád objevuje se všemi desetinnými místy, tak jak bylo. Vlastní formát je tak jen optická iluze, která nezmění zdrojové číslo, což znamená, že s tímto číslem stále můžete počítat se stejným výsledkem, jako bez vlastního formátu.
Tyto základní příklady šly samozřejmě naformátovat i bez vlastního formátu, jen za pomocí klasického předdefinovaného formátu v liště Excelu. Největší výhoda vlastního formátu přichází tehdy, když potřebujete v buňce zobrazit určité speciality.
Zástupný znak # a textové hodnoty
V dalším příkladu nechceme zobrazit deset tisíc jako číslo, ale v kombinaci číslovky a slova tisíc. Použijeme k tomu další výhodu zástupného znaku křížek #. Nejen, že za pomoci křížku můžeme oddělovat tisíce, ale můžete s jeho pomocí i zkracovat čísla. Přepneme se do vlastního formátu a nejprve začneme tak, že napíšeme 4 křížky. Nic se nestane, číslo deset tisíc zůstane stejně. Pokud první křížek oddělíme mezerou, tak se oddělí tisíce. Co se ale stane pokud tyto křížky za mezerou smažu? Zůstane nám jen číslo před mezerou, oddělily se nám tedy tisíce. Teď můžeme spojit tento zápis s textovou hodnotou, která musí být jako vždy v excelu napsaná v uvozovkách. Napíšeme tedy uvozovky“ tisíce“. Ještě mezi uvozovku a tisíce vložíme mezeru ať máme číslovku a slovo oddělené. A máme hotovo.
Velmi podobně bychom to udělali i u dalšího příkladu, kde místo slova tisíce použijeme písmeno K, což se hojně používá zejména ve slangu. Postup je stejný, křížkem a mezerou oddělíme tisíce a písmeno k dáme do uvozovek.
Zástupný znak # a zkracování čísel
Toto nahrazování slovy nebo písmeny se vám může hodit při práci s velkými čísly v milionech nebo miliardách. A to zejména v grafech, kdy se vám na ose nezobrazí dlouhé číslo, ale pouze zkratka čísla. Z milionu bychom tedy 1 M udělali tak, že ve vlastním formátu napíšeme křížek a pomocí dvou mezer odstraníme šest nul. Stačí napsat uvozovky „M“ a máme hotovo. Kdybychom teď milion změnili na 2 miliony, tak vlastní formát se samozřejmě přizpůsobí. Pokud bychom to změnili na 1,5 milionu, tak bychom ale viděli 2 miliony. A to proto, že ve formátu nemáme nastavená desetinná místa. Vrátíme se tedy k formátu a upravíme ho. Za křížek bychom napsali 0,0 a zachovali dvě mezery.
U miliard si klidně můžeme vybrat, jak chceme formát zobrazit. Jako 1 miliardu, 1 mld nebo třeba 1 000 milionů. Zkusíme tisíc milionů. Nejdříve víme, že v čísle tisíc máme mít oddělené tisíce mezerou, takže začneme # ### a teď potřebujeme zkrátit 6 nul, tedy dvě mezery. Napíšeme dvě mezery a písmeno M v uvozovkách. Hotovo.
I přesto, že to na první pohled vypadá, že máme v buňce textovou hodnotu, tak je to jen zdání. S buňkou lze i nadále počítat. Když si najedete na buňku, tak v příkazovém řádku vidíte stále nezměněné číslo.
Teď ovšem chceme, aby se na řádcích, kde máme nulu nezobrazilo nic. Pokud nechceme manuálně mazat nuly, což by nebyl správný způsob, tak musíme použít vlastní formát.
Vlastní formát buněk a pozice ve formátu
Abychom mohli zformátovat hodnoty nula, tak si musíme nejdříve ukázat, jak vlastní formát buněk vlastně hodnoty formátuje. Ve vlastním formátu můžeme mít celkem 4 pozice. To nejdůležitější, co si musíte pamatovat je to, že první pozice je pro kladné hodnoty, druhá pozice je pro záporné hodnoty, třetí pozice je pro nulové hodnoty a poslední, čtvrtá je pro textové neboli nečíselné hodnoty.
Pokud si tedy přejeme nezobrazit nulu, tak vyplníme kladnou a zápornou hodnotu a nulu necháme prázdnou. Pokud jako vlastní formát použijeme pouze jednu pozici, tak se tento formát uplatní na všechny hodnoty. Pokud vyplníme dvě pozice, tak se první část použije na kladné hodnoty a nulu a druhá se použije na hodnoty záporné. Pokud vyplníte tři pozice, tak se první použije na kladné hodnoty, druhá na záporné a třetí na nulové hodnoty. Pro každou ze čtyř kategorií můžeme zvlášť definovat vlastní formát. Ne vždy musíme vyplňovat všechny 4 pozice. Pokud některou nevyplníme, Excel pro tuto kategorii uplatní obecný formát buňky. Pro přeskočení kategorie se kategorie nevyplní, ale použije se středník. Pokud vyplníme pouze ;;; tak hodnoty zmizí.
V tomto příkladě bychom tedy zkopírovali hodnoty a otevřeli vlastní formát. A napsali # ### tedy formát pro kladné hodnoty; # ### formát pro záporné hodnoty, středník a další středník, čímž formát pro nulu přeskočíme a nula se tak nezobrazí.
Pomocí vlastního formátu si můžeme i zvolit, co se má zobrazit místo hodnoty nula, můžeme chtít třeba zobrazit NA, nebo sousloví Není hodnota. Použijeme tedy před chvíli vytvořený formát a místo přeskočení nulové hodnoty ji nahradíme NA v uvozovkách.
Výhodou použití vlastního formátu je to, že i přesto, že v buňce vidíme textovou hodnotu, tak obsahem buňky je stále číslo nula. S buňkou tak nadále můžeme normálně počítat, bez toho, aby se vrátila chybová hláška.
Vlastní formát buněk vám pomůže i se zobrazením záporných hodnot. V dalším příkladu máme klasický účetní formát, kdy se záporné hodnoty zobrazují v závorce. Zkopírujeme čísla, otevřeme vlastní formát a nejprve nastavíme číselný formát, kdy chceme tisíce oddělit mezerou. Napíšeme tedy standardní zápis # ###; takto chceme zobrazit kladná čísla, záporné hodnoty chceme zobrazit stejně, tedy -# ### akorát je chceme uvést do závorek.
Pokud bychom nechtěli mít v závorce mínus, tak ho z vlastního formátu smažeme a negativní hodnoty zůstanou pouze v závorce.
Vlastní formát buněk a barvy
Stejně jako můžeme odlišit formát, tak můžeme odlišit i formát barvy. Řekněme, že chceme, aby se kladné hodnoty zobrazily zeleně a negativní červeně. Zkopírujeme čísla a otevřeme vlastní formát. Barvy se píšou do hranatých závorek. Základní barvy jako je červená, modrá, zelená, můžete napsat slovy. Začneme tedy formátem čísla. Zvolíme naši klasiku # ### a negativní hodnoty jako (# ###). Teď ještě přidat barvy. Před kladnou hodnotu v hranatých závorkách napíšeme Zelená a před formát negativních hodnot napíšeme v hranatých závorkách červená.
Pokud by se vám odstín některých přednastavených barev nelíbil, třeba předdefinovaná zelená není moc k přečtení, tak můžete použít kód barvy, které jsou dostupné ZDE.
Vlastní formát buněk a intervaly/podmínky
Kromě výše zmíněných formátů lze za pomoci vlastního formátu definovat i intervaly, a tím podmínky. Intervaly se stejně jako barvy zapisují do hranatých závorek. Vlastním formátem lze definovat pouze dva intervaly, a to za pomoci znamének menší, větší a rovná se. Pokud nějaká hodnota nevyhovuje ani jednomu intervalu, pak pro ně platí zbytek číselné osy. Pokud potřebujeme použít více než dva, respektive tři intervaly, tak musíme použít podmíněné formátování místo vlastního formátu.
Zde například nechceme prezentovat přesná čísla, ale pouze to, zda je hodnota nadprůměrem, podprůměrem nebo se jedná o průměrnou hodnotu. Ve vlastním formátu tedy provedeme tento zápis. Podmínka se píše do hranatých závorek, takže začneme [<50] „Podprůměr“;[=50] „Průměr“; „Nadprůměr“. Potvrdíme a zkontrolujeme. A máme to dobře.
3 komentáře
Super 🙂
Moc děkuji 🙂
Děkuji, v plánu je ještě navazující video o dalších zástupných znacích 🙂