7 úžasných triků formátu vlastního čísla v Excelu (musíte vědět)

Formátování vlastního čísla v Excelu je oblečením pro data v buňkách Excelu. Můžete se oblékat tak, jak chcete. Vše, co potřebujete, je trochu know-how, jak funguje formát vlastního čísla aplikace Excel.

Pomocí vlastního formátování čísel v aplikaci Excel můžete změnit způsob zobrazení hodnot v buňkách, ale zároveň zachovat původní hodnotu neporušenou.

Například mohu nastavit, aby se hodnota data zobrazovala v různých formátech data, zatímco základní hodnota (číslo, které představuje datum) by zůstalo stejné.

V tomto tutoriálu se budu zabývat vším o vlastním formátu čísel v aplikaci Excel a také vám ukážu několik opravdu užitečných příkladů, které můžete použít při každodenní práci.

Začněme tedy!

Konstrukce vlastního formátu čísla Excel

Než vám ukážu jeho úžasnost, dovolte mi stručně vysvětlit, jak vlastní formátování čísel funguje v Excelu.

Podle návrhu může Excel v buňce přijímat následující čtyři typy dat:

• Kladné číslo
• záporné číslo
• 0
• Textové řetězce (to může zahrnovat čisté textové řetězce i alfanumerické řetězce)

Dalším datovým typem, který může Excel přijímat, jsou data, ale protože všechny hodnoty data a času jsou v Excelu uloženy jako čísla, budou zahrnuty jako součást kladného čísla.

Pro libovolnou buňku v listu v aplikaci Excel můžete určit, jak se mají v buňce zobrazovat všechny tyto datové typy.

A níže je syntaxe, ve které můžete tento formát určit.

;;;

Všechny tyto položky jsou odděleny středníkem (;).

Cokoli, co zadáte do buňky v aplikaci Excel, by spadalo do jedné z těchto čtyř kategorií, a proto lze určit vlastní formát.

Pokud uvedete pouze:

• Jeden formát: Aplikuje se na všechny čtyři sekce. Pokud například napíšete Obecné, bude použito pro všechny čtyři oddíly.
• Dva formáty: První se použije na kladná čísla a nuly a druhý na záporná čísla. Formát textu se ve výchozím nastavení stává Obecným.
• Tři formáty: První se použije na kladná čísla, druhý na záporná čísla, třetí se použije na nulu a text zmizí, protože pro text není zadáno nic.

Pokud se chcete dozvědět více o vlastním formátování čísel, vřele doporučuji sekci Nápověda Office.

Jak to funguje a kde tyto formáty zadat, si povíme dále v tomto kurzu. Prozatím mějte na paměti, že toto je syntaxe formátu pro libovolnou buňku

Jak funguje vlastní formát čísel v aplikaci Excel

Poté otevřete nový sešit aplikace Excel, ve výchozím nastavení mají všechny buňky ve všech listech v tomto sešitu obecný formát, kde:

• Pozitivní čísla jsou zobrazeny tak, jak jsou (zarovnány doleva)
• Záporná čísla jsou zobrazeny se znaménkem minus (a zarovnány doleva)
• Nula je zobrazen jako 0 (a zarovnán doleva)
• Text je zobrazen tak, jak je (zarovnáno doprava)

A pokud zadáte datum, zobrazí se na základě vašeho regionálního nastavení (ve formátu DD-MM-RRRR nebo MM-DD-RRRR).

Ve výchozím nastavení je formátování čísla buňky:

Obecné; Obecné; Obecné; Obecné

Kde je každý datový typ formátován jako Obecný (a dodržujte pravidla, která jsem zmínil výše, podle toho, zda jde o kladné číslo, záporné číslo, 0 nebo text)

Nyní je výchozí nastavení v pořádku, ale jak změníte formát. Pokud například chci, aby se moje záporná čísla zobrazovala červeně, nebo chci, aby se moje data zobrazovala v konkrétním formátu, jak to udělám?

Pomocí rozevíracího seznamu Formát na pásu karet

Některé z běžně používaných formátů čísel najdete na kartě Domů ve skupině čísel.

Je to rozevírací seznam, který ukazuje mnoho běžně používaných formátů, které můžete na buňku použít jednoduchým výběrem buňky a následným výběrem formátu z tohoto seznamu.

Pokud například chcete převést čísla na procenta nebo převedená data na krátký formát data nebo dlouhý formát data, můžete to snadno provést pomocí možností v rozevíracím seznamu.

Pomocí dialogového okna Formát buněk

Dialogová okna formátování buněk, kde získáte maximální flexibilitu, pokud jde o použití formátů na buňku v aplikaci Excel.

Už má spoustu předem připravených formátů, které můžete použít, nebo pokud si chcete vytvořit vlastní formát čísel, můžete to udělat také.

Zde je návod, jak otevřít dialogové okno Formát buněk;

1. Klikněte na kartu Domů
2. Ve skupině Číslo klikněte na ikonu spouštěče dialogového okna (malá nakloněná šipka v pravé dolní části skupiny).

Otevře se dialogové okno Formát buněk. V rámci něj najdete všechny formáty na kartě Číslo (spolu s možností vytvářet vlastní formáty buněk)

Dialogové okno Formát buněk můžete také otevřít pomocí následující klávesové zkratky:

CONTROL + 1 (všechny ovládací klávesy a poté stiskněte klávesu 1).

V případě, že používáte Mac, můžete použít CMD + 1

Nyní si dáme pár praktických příkladů, kde si vytvoříme vlastní formáty čísel.

Příklady použití vlastního formátování čísel v aplikaci Excel

Nyní si dáme pár praktických příkladů, kde si vytvoříme vlastní formáty čísel.

Skrýt hodnotu buňky (nebo Skrýt všechny hodnoty v rozsahu buněk)

Někdy může být potřeba skrýt obsah buňky (bez skrytí řádku nebo sloupce, který buňku obsahuje).

Praktickým případem je, když vytváříte řídicí panel aplikace Excel a na řídicím panelu používáte několik hodnot, ale nechcete, aby je uživatelé viděli. takže tyto hodnoty můžete jednoduše skrýt

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete skrýt všechny číselné hodnoty:

Zde je návod, jak to provést pomocí vlastního formátování v aplikaci Excel:

1. Vyberte buňku nebo rozsah buněk, pro které chcete skrýt obsah buňky
2. Otevřete dialogové okno Formát buněk (klávesová zkratka Ctrl + 1)
3. Na kartě Číslo klikněte na Vlastní
4. Do pole Typ zadejte následující formát:

   ;;;

Jak to funguje?

Formát pro čtyři různé typy datových typů je rozdělen středníkem v následujícím formátu:

;;;

Při této technice jsme odstranili formát a zadali pouze středník, což znamená, že pro buňky nechceme žádné formátování. Toto je indikace pro Excel, aby skryl veškerý obsah, který je v buňce.

Všimněte si, že i když se tím skryje obsah buňky, stále tam je. takže pokud máte skrytá čísla, můžete je stále použít ve výpočtech (což je skvělý trik pro řídicí panely a sestavy aplikace Excel).

Zobrazit záporná čísla v červené barvě

Ve výchozím nastavení se záporná čísla zobrazují se znaménkem minus (nebo v některých regionálních nastaveních v závorkách).

Vlastní formátování čísel můžete použít ke změně barvy záporných čísel a jejich zobrazení v červené barvě.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete všechny záporné hodnoty zvýraznit červenou barvou.

Níže jsou uvedeny kroky, jak to provést:

1. Vyberte rozsah buněk, ve kterých chcete zobrazovat záporná čísla červeně
2. Otevřete dialogové okno Formát buněk (klávesová zkratka Ctrl + 1)
3. Na kartě Číslo klikněte na Vlastní
4. Do pole Typ zadejte následující formát:

Obecné; [červená]-Obecné

Díky výše uvedenému formátu budou vaše buňky se zápornými hodnotami zobrazeny červeně.

Jak to funguje?

Když zadáte jako vlastní formát čísel pouze dva formáty, první je považován za kladná čísla a 0 a druhý za záporná čísla (další hodnota je ve výchozím nastavení obecný formát).

Ve výše uvedeném formátu, který jsme použili, jsme určili barvu, kterou chceme, aby tento formát zabíral v hranatých závorkách.

Takže zatímco záporná čísla mají obecný formát, jsou zobrazena červenou barvou se znaménkem minus.

V případě, že chcete, aby se záporná čísla v závorkách zastavila červeně (místo znaménka mínus), můžete použít níže uvedený formát:

Obecné; [červená] (obecně)

Přidat text do čísel (například miliony/miliardy)

Jedna úžasná věc na vlastním formátování čísel je, že k číslu můžete přidat text předpony nebo přípony a přitom zachovat původní číslo v buňce.

Například mohu zobrazit „100“ jako „100 milionů dolarů“, přičemž v buňce mám stále původní hodnotu, abych ji mohl použít při výpočtech.

Předpokládejme, že máte datovou sadu, jak je znázorněno níže, a chcete tato čísla zobrazovat se znakem dolaru a slovem milion před ním.

Níže jsou uvedeny kroky, jak to provést:

1. Vyberte datovou sadu, do které chcete přidat text k číslům
2. Otevřete dialogové okno Formát buněk (klávesová zkratka Ctrl + 1)
3. Na kartě Číslo klikněte na Vlastní
4. Do pole Typ zadejte následující formát:

$ General „Million“

Tento vlastní formát čísel ovlivní pouze čísla. Pokud zadáte text do stejné buňky, bude vypadat tak, jak je.

Jak to funguje?

V tomto příkladu jsem do obecného formátu přidal slovo milion v uvozovkách. cokoli, co přidám do uvozovek do formátu, se zobrazí tak, jak je v buňce.

Také jsem přidal znak dolaru před obecný formát, aby všechna moje čísla také zobrazovala znak dolaru před číslem.

Poznámka: Pokud vás zajímá, proč není znak dolaru v uvozovkách, zatímco slovo Milion je v uvozovkách, je to proto, že Excel rozpoznává dolar jako symbol, který se často používá ve vlastním formátování čísel, a nevyžaduje vás přidat do něj uvozovky. Můžete pokračovat a do znaku dolaru přidat uvozovky, ale když zavřete dialogové okno formátovacích buněk a znovu ho otevřete, všimnete si, že ho Excel odstranil

Maskovat čísla a text

Pomocí vlastního formátování čísel můžete posoudit maximálně dvě podmínky a na základě těchto podmínek můžete na buňku použít formát.

Předpokládejme například, že máte seznam skóre pro některé studenty, chcete zobrazit text Prospěl nebo Neprospěl na základě skóre (kde jakékoli skóre menší než 35 by mělo ukázat „Neprospělo“, jinak by mělo ukázat „Prospělo“.

Níže jsou uvedeny kroky k zobrazení skóre jako úspěšné nebo neúspěšné namísto hodnoty pomocí vlastního formátu čísla:

1. Vyberte datovou sadu, kde máte skóre
2. Otevřete dialogové okno Formát buněk (klávesová zkratka Ctrl + 1)
3. Na kartě Číslo klikněte na Vlastní
4. Do pole Typ zadejte následující formát:

   [<35] "Selhalo"; "Prospělo"

Níže uvádíme, jak by vaše data vypadala poté, co použijete výše uvedený formát.

Všimněte si, že to nemění hodnotu v buňkách. Buňka má i nadále skóre v číselných hodnotách, přičemž zobrazuje „Pass“ nebo „Fail“ v ​​závislosti na hodnotě buňky.

Jak to funguje?

V tomto případě je třeba podmínku zadat do hranatých závorek. vlastní formátování čísel pak vyhodnotí hodnotu v buňce a podle toho použije formát.

Pokud je tedy hodnota v buňce menší než 35, číslo je skryto a místo toho se zobrazí text „Selhání“, přičemž sečtou všechny ostatní případy, zobrazí se „Pass“.

Podobně pokud chcete převést řadu buněk, které mají 0 a 1 na TRUE a FALSE, můžete použít následující formát:

[= 0] "FALSE"; [= 1] "TRUE"

Ve vlastním formátování čísel můžete použít pouze dvě podmínky. Pokud se pokusíte použít více než dvě podmínky, bude to tak, aby vás vaše výzva informovala, že tento formát nemůže přijmout.

Skrýt text, ale zobrazit čísla

Někdy, když stahujete data z databází, protože jsou prázdné, jsou naplněny hodnotou, jako je Nedostupné nebo NA nebo -

Pomocí vlastního formátování čísel můžete skrýt všechny textové hodnoty a přitom zachovat čísla tak, jak jsou. to také zajišťuje, že vaše původní data jsou neporušená (protože textovou hodnotu neodstraňujete, pouze ji skryjete)

Předpokládejme, že máte sadu dat, jak je uvedeno níže, kam chcete skrýt všechny textové hodnoty:

Níže jsou uvedeny kroky, jak to provést:

1. Vyberte datovou sadu, kde máte data
2. Otevřete dialogové okno Formát buněk (klávesová zkratka Ctrl + 1)
3. Na kartě Číslo klikněte na Vlastní
4. Do pole Typ zadejte následující formát:

Všeobecné; -Všeobecné; Všeobecné;

Všimněte si toho, že jsem pro čísla použil obecný formát. Můžete libovolný formát (například 0, 0. #, #0,0%). Všimněte si také, že před druhým obecným formátem je záporné znaménko (-), protože představuje formát pro záporná čísla.

Jak to funguje?

V tomto příkladu, protože jsme chtěli skrýt všechny textové hodnoty, jednoduše jsme pro něj neurčili formát.

A protože jsme určili formát pro další tři datové typy (kladné číslo, záporné číslo a nula), Excel pochopil, že jsme formát textové hodnoty záměrně ponechali prázdný a pokud buňka má text, nemělo by se nic zobrazit tětiva.

Zobrazit čísla jako procenta (%)

Pomocí vlastního formátování čísel můžete čísla také změnit a zobrazit v procentech.

Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete převést tato čísla tak, aby zobrazovala ekvivalentní procentuální hodnotu:

Níže jsou uvedeny kroky, jak to provést:

1. Vyberte buňky, ve kterých máte čísla (která chcete převést na procenta)
2. Otevřete dialogové okno Formát buněk (klávesová zkratka Ctrl + 1)
3. Na kartě Číslo klikněte na Vlastní
4. Do pole Typ zadejte následující formát:

0%

Tím se změní čísla na jejich procenta. Například se změní z 0,11 na 11%.

Pokud vše, co chcete udělat, je převést číslo na jeho ekvivalentní procentuální hodnotu, lepší způsob, jak to udělat, by bylo použít možnost procent v dialogovém okně formátovat buňky.

Kde může být užitečné vlastní formátování, je, když chcete mít větší kontrolu nad tím, jak chcete zobrazit procentuální hodnotu.

Pokud například chcete zobrazit nějaký dodatečný text s procenty, vše, co chcete zobrazit, negativní procenta červenou barvou, pak je lepší použít vlastní formátování

Zobrazit čísla v jiné jednotce (miliony jako miliardy nebo gramy jako kilogramy)

Pokud pracujete s velkými čísly, můžete je ukázat v jiné jednotce. například pokud máte údaje o prodejích některých společností, můžete chtít zobrazit tržby v milionech nebo miliardách (při zachování původní hodnoty prodeje v buňkách)

Naštěstí to můžete snadno udělat pomocí vlastního formátování čísel.

Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete tyto hodnoty prodeje zobrazit v milionech.

Níže jsou uvedeny kroky, jak to provést:

1. Vyberte buňky, kde máte čísla
2. Otevřete dialogové okno Formát buněk (klávesová zkratka Ctrl + 1)
3. Na kartě Číslo klikněte na Vlastní
4. Do pole Typ zadejte následující formát:

0.0,,

Výše uvedené vám poskytne výsledek, jak je uvedeno níže (hodnoty v milionech v jedné číslici za desetinnou čárkou):

Jak to funguje?

Ve výše uvedeném formátu nula před desetinnou čárkou znamená, že by Excel měl zobrazit celé číslo (přesně tak, jak je v buňce), a 0 za desetinnou čárkou znamená, že by za desetinnou čárkou měla zobrazit pouze jednu významnou číslici.

Takže 0,0 by vždy bylo číslo s jednou číslicí za desetinnou čárkou.

Když za 0,0 přidáte čárku, oholí poslední tři číslice čísla. Například 123456 bude 123,4 (poslední tři číslice budou odstraněny, a protože musí za desetinnou čárkou zobrazovat 1 číslici, ukazuje 4)

Když se nad tím zamyslíte, je to podobné jako vydělením čísla 1000. Pokud tedy chcete zobrazit číslo v jednotce „miliony“, musíte přidat 2 čárky, aby oholil posledních 6 číslic.

A pokud chcete za číslem ukázat také slovo milion nebo abecedu M, použijte níže uvedený formát

0,0 ,, "M"