Vlastní formát buněk - pokročilé nastavení - Excel

Úvodem

Vlastní formát najde uplatnění:

Technické výpočty - zobrazovat požadovaný počet desetinných míst, oddělovat čísla po tisících (tj. 10 000 000 - pro větší přehlednost, atd.)

Účetnictví potřebujete doplnit měnu a třeba změnit barvu čísla, pokud je číslo záporné (červené).

Fyzikální výpočty nejde jen o čísla, ale čísla (hodnoty) mohou mít fyzikální jednotku.

V tomto článku se naučíte používat pokročilejší formátování čísla (hodnot) pomocí vlastního formátu. Pro přehlednost je rozdělen do několika kapitol

Seznam kapitol - rychlý rozcestník

• Proč vlastní formát - možnosti nastavení
• Jak na vlastní formát - zobrazení dialogového okna
  • Excel 2010 (2007)
  • Excel 2003 (97)
  • Klávesová zkratka
• Trocha teorie o vlastním formátu
  • Znaky co znamenají symboly zavináč, procenta (@ / % ...), jak je použít
  • Jak zadat datum co je mm, rrrr, yyyy - (proč vznikají problémy, například při spolupráci na zahraničních sešitech)
  • Názvy barev
  • Zkratky jazyků
• Praktické příklady - Vlastní formát buněk
  • Jazykové hrátky - volba jazyka u názvu dne, měsíce, ..
  • Zaokrouhlování
  • ...
• Ke stažení zdarma - ukázkový sešit
• Související články

Proč vlastní formát - možnosti nastavení

Výhodou nastavení vlastního formátu buněk je, že si způsob zobrazení hodnoty v buňce upravíte k obrazu svému. Sice Microsoft Excel má mnoho předpřipravených formátu, ale není nad to umět si formát zobrazení v buňce nastavit samostatně. V buňce tedy můžete nastavit:

• Doplnění jednotek, symbolů - případně různých textů
• Vlastní datum - měsíc číslem, římským číslem, jménem, zkratkou, atd.
• Nahrazení čísla slovem - místo záporného čísla bude uvedeno "ztráta"
• Barevné označení - záporná červeně, kladná modře, nula černě
• Zaokrouhlování (optické), oddělování čísel po řádech - místo 1000000,0000000 bude zobrazeno 1 000 000,00
• Jazyková verze - možno změnit zobrazovaný jazyk (místo českého pondělí bude anglické Monday, nebo německé Montag)
• kombinace a speciálnosti - kombinace předchozích (barvy, jednotky, texty), opakování čísel, intervaly, skloňování (Excel umí i skloňovat, pokud ho to naučíte :) atd.

Jak zobrazit dialogové okno: Formát buněk

Způsob zobrazení dialogového okna Formát buněk závisí na verzi Excelu, kterou používáte (Excel 2010, Excel 2003). Pokud máte rádi klávesové zkratky, tak i tato možnost existuje.

Excel 2013 (2010, 2007)

Dialogové okno Formát buněk záložka číslo je k dispozici:

• Pravé tlačítko myší - klik na buňce a poté Formát buněk
• Karta Domů sekce Číslo čtvereček se šipkou.

Excel 2003 a 97

Pro verzi MS Excel 2003, MS Excel 97, z menu Formát - Buňky vybereme záložku Číslo a druh Vlastní (Custom).

Klávesová zkratka

Dialogové okno Formát buněk lze zobrazit i pomocí klávesové zkratky Ctrl + 1. Což funguje zatím ve všech verzích Excelu.

Trocha teorie o vlastním formátu

Jelikož se v tomto článku zabývám pokročilejším nastavením vlastního formátu (pro základní bude k dispozici samostatný článek) proberu teoretické základy, které se Vám budou pro experimentování hodit. Na konci článku je k dispozici soubor ke stažení, kde jsou jednotlivé příklady uvedeny. V tomto odstavci proberu:

• Znaky - které se ve vlastním formátu vyskytují (0, @, #, %, ...)
• Podmínky
• Datum čas znaky
• Barvy - jak se definují ve formátu a jaké názvy, nebo číselné označení barvy mají

Poznámka: Pozor na vzájemné kombinace s podmíněným formátováním a vlastních formátů (osobně pro grafiku používám raději podmíněné formátování).

Znaky

Pozor na verzi Excelu (Anglická, česká) - v anglické verzi se používají jiné znaky (čárka, je tečka. atd.), v tomto článku se budu zabývat MS Excelem v české lokalizaci s českým národním nastavením (v poznámce se pokusím upozornit jak je to v anglické verzi).

• @ - zástupný symbol textu v buňce @ "ukázka" @
• 0 - zástupný symbol čísla v buňce - zobrazuje i nuly (lze kombinovat 0,00 nebo 000,0 atd.)
• # - zástupný symbol čísla v buňce - Nezobrazuje nuly (# ### ##0)
• % - přidá procenta a hodnotu vynásobí 100
• - + - zobrazení plus mínus (není nijak vázáno s hodnotou buňky - doporučuji volit z rozmyslem)
• $ - symbol měny (podobně jako Euro, lze zapsat přímo)
• * - musí být doplněna dalším znakem, který vyplní zbytek buňky
• , - desetinný oddělovat (pro anglickou verzi je to tečka)
• "" - pokud obsahuje text tento bude zobrazen (splňuje-li kritéria)
• . - tečka oddělení dnů.měsíců.roků (pokud nejde o datum musí se doplnit uvozovkami ".")
• : - dvojtečka oddělení hodin:minut (pokud nejde o hodiny musí se doplnit uvozovkami ":")
• ; - oddělení sekcí/polí kladné číslo;záporné číslo;nula;text - vid dále barva a příklady
• ? - otazník
• \ - zpětné lomítko - zobrazí následující znak (\@) - aby se daly zobrazit i znaky co mají specifickou vlastnost
• [] - hranaté závorky, lze využít jen tři intervaly (kdy třetí je pro zbývající hodnoty) viz dále
• _ - dolní podtržítko - mezera
• Vęeobecný - Excel píše místo Všeobecný š jako ę

Podmínky

• [podmínka] - viz příklady, kde bude lépe pochopitelné
• [název barva] - viz další text o barvách
• [barva číslo] - viz další text o barvách

Datum čas znaky

Formát data

Pro formát data se používají:

• d,dd,ddd,dddd
• m,mm,mmm,mmmm měsíce jako římské číslice (I až XII) - problém v české verzi se zobrazením Římských číslic
• rr,rrrr - v anglickou (EN) verzi Excelu yy, yyyy
• h,hh
• m,mm - minuty bohužel mají stejný název jako měsíce
• s,ss

Případně využít další možnosti (složené závorky, ...)

• h:mm:ss.00 - zlomky vteřin
• [h] - uplynulý čas v hodinách
• [mm] - uplynulý čas v minutách
• h:mm dop./odp. - zlomky vteřin

Poznámka: Pokud máte údaj v minutách 2570 a potřebujete výstup v celých hodinách + minuty 42:50. Tak stačí údaj v minutách vydělit 60 a 24. =2570/60/24 a nastavit formát [h]:mm

Problém se zobrazováním římských číslic

Excel 2007 - 2013 má problémy zobrazit římské čísla u měsíců. Proč tomu tak je se mi zatím nepodařilo zjistit. Jak tento problém obejít? Navrhuji použití funkce ROMAN:

=ROMAN(MĚSÍC(A1))

Poznámka

Pokud zadáte formát [h] při sečtení 20 hodin + 20 hodin bude výsledek 40 hodin. Při klasické h bude výsledek 16 hodin a přičte se jeden den ke kalendářnímu dnu.

Více informací jak formátovat datum a čas je pospáno v článku: Datum a čas formátování.

Barvy

Pokud chcete pomocí vlastního formátu nastavit barvu buňky je syntaxe:

[název barva 1]Všeobecný;[název barva 2]Všeobecný;[název barva 3]0;[název barva 4]@;

• název barva 1 - pro kladná čísla
• název barva 2 - pro záporná čísla
• název barva 3 - pro nulu
• název barva 4 - pro text

Názvy barev:

• černé
• modré
• zelené
• bílé
• žluté
• červené
• azurové
• purpurové

Čísla barev (1-56):

• 1 černá
• 2 bíla
• 3 červená
• ..
• 5 Modrá
• 6 Žlutá
• ..
• 10 Zelená
• ..
• 53 hnědá
• ..
• 56 - šedá 80%

Praktické příklady - Vlastní formát buněk

Jednotky

Doplnění o jednotku:

0" Hz"

Počet desetinných míst:

[<1]0,000" A";[<1000]0,00" A";0,0" A"

Pozor na zbytečnou mezeru! Za nulou!

0 " Hz"

Zaokrouhlování

Různé možností zaokrouhlování. Více řekne praktická ukázka z Excelovského souboru (sešitu), kterou si můžete stáhnout.

0,0000

Dvě desetinná místa

0,00

Oddělení tisíců, zaokrouhlení na dvě desetinná místa

# ##0,00

Oddělení tisíců, budou doplněny nulami, jinak dvě desetinná místa

0 000,00

Slovní náhrady čísel

Místo čísel slova..

"kladné";"záporné";"nula"

Skloňování

I Excel umí skloňovat, jen ho to musíte naučit ;) ... Jak jsme psal lze využít jen tři intervaly. První omezení, druhé omezení a zbývající hodnoty.

Skloňování slova roky.

[=1]0" rok";[<5]0" roky";0" roků"

Podobně můžete skloňovat další slova (město, tobě sto, řediteli dvě sto :))

Hrátky s jazykem

Anglické datum

[$-409]dddd, mmmm, rrr

Německé datum

[$-407]dddd, mmmm, rrr

Bude-li zájem, doplním seznam kódu pro další jazyky. (Čeština je [$-405].)

Barvy

Záporná čísla červená

# ##0;[Červená]-# ##0

Kladná čísla Modrá, Záporná čísla červená

[Modrá]# ##0;[Červená]-# ##0

Kladná čísla Modrá, Záporná čísla červená, nula žlutá

[Modrá]# ##0;[Červená]-# ##0;[Žlutá]Vęeobecný

Kladná čísla Modrá, Záporná čísla červená, nula žlutá, text zelená

[Modrá]# ##0;[Červená]-# ##0;[Žlutá]Vęeobecný;[Zelená]Vęeobecný

Alternativy

Jednu věc můžete zapsat několika způsoby, třeba mezera jde zapsat:

0\ 00

nebo

0" "00

Pokud jde o mezeru v řádu tisíců lze zjednodušit a nechat pouze samostatnou mezeru.

0 000

m2 m3

Jak zadávat m² a m³ jako horní index pomocí podmíněného formátování?

#" m2"

Při zadávání 2, 3 ve vlastním formátu přidržte klávesu Alt Gr a zadejte dvojku či trojku. Nebo Alt 0178 co zmiňují zahraniční weby (pro 2), člověk musí být přepnut v EN klávesnici (což je jasné :).

Speciálnosti

Skryje nulu

Vęeobecný;-Vęeobecný;;@

Skryje nulu

Vęeobecný;-Vęeobecný;"tady má být nula";@

Ke stažení zdarma

Soubor Ukázky vlastních formátů v MS Excel ke stažení zdarma.

Související články

• Vlastní formát - praktické příklady
• Podmíněné formátování - praktické příklady
• Formátování buněk pomocí VBA

Závěrem

Pokud máte v zásobě nějaký zajímavý formát, můžete svůj postřeh přidat do komentářů, ať poslouží i dalším lidem.

Článek byl aktualizován: 19.09.2020 10:56

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.