7 úžasných trikov vlastného formátu čísla Excel (musíte vedieť)

Formátovanie vlastného čísla v Exceli je oblečením pre údaje v bunkách Excelu. Môžete sa obliecť tak, ako chcete. Všetko, čo potrebujete, je trochu know-how o tom, ako funguje formát vlastného čísla v Exceli.

Pomocou vlastného formátovania čísel v programe Excel môžete zmeniť spôsob, akým sa zobrazujú hodnoty v bunkách, ale zároveň ponechať pôvodnú hodnotu nedotknutú.

Napríklad môžem nastaviť, aby sa hodnota dátumu zobrazovala v rôznych formátoch dátumu, pričom podkladová hodnota (číslo, ktoré predstavuje dátum) by zostalo rovnaké.

V tomto návode sa budem zaoberať všetkým o vlastnom formáte čísel v programe Excel a ukážem vám tiež niekoľko skutočne užitočných príkladov, ktoré môžete použiť pri každodennej práci.

Začnime teda!

Konštrukcia vlastného formátu čísla Excel

Predtým, ako vám ukážem jeho úžasnosť, dovoľte mi stručne vysvetliť, ako funguje vlastné formátovanie čísel v programe Excel.

Podľa plánu môže Excel v bunke prijímať nasledujúce štyri typy údajov:

• Kladné číslo
• záporné číslo
• 0
• Textové reťazce (môžu to byť čisté textové reťazce aj alfanumerické reťazce)

Ďalším typom údajov, ktoré môže Excel akceptovať, sú dátumy, ale keďže všetky hodnoty dátumu a času sú v programe Excel uložené ako čísla, tieto údaje by boli zahrnuté ako súčasť kladného čísla.

Pre každú bunku v hárku v programe Excel môžete určiť, ako sa majú v bunke zobrazovať všetky tieto typy údajov.

A nižšie je syntax, v ktorej môžete zadať tento formát.

;;;

Všimnite si toho, že všetky sú oddelené bodkočiarkami (;).

Čokoľvek, čo zadáte do bunky v programe Excel, bude spadať do jednej z týchto štyroch kategórií, a preto je možné určiť pre to vlastný formát.

Ak spomeniete iba:

• Jeden formát: Aplikuje sa na všetky štyri sekcie. Ak napríklad napíšete Všeobecné, použije sa pre všetky štyri sekcie.
• Dva formáty: Prvý sa použije na kladné čísla a nuly a druhý na záporné čísla. Formát textu sa v predvolenom nastavení zmení na všeobecný.
• Tri formáty: Prvý sa použije na kladné čísla, druhý na záporné čísla, tretí sa použije na nulu a text zmizne, pretože pre text nie je uvedené nič.

Ak sa chcete dozvedieť viac o vlastnom formátovaní čísel, vrelo odporúčam sekciu Pomocník balíka Office.

Ako to funguje a kde zadať tieto formáty, sa budeme zaoberať neskôr v tomto návode. Nateraz majte na pamäti, že toto je syntax formátu pre ľubovoľnú bunku

Ako funguje formát vlastného čísla v programe Excel

Potom otvoríte nový zošit programu Excel, v predvolenom nastavení majú všetky bunky vo všetkých hárkoch v tomto zošite všeobecný formát, kde:

• Pozitívne čísla sú zobrazené tak, ako sú (zarovnané vľavo)
• Záporné čísla sú zobrazené so znamienkom mínus (a zarovnané doľava)
• Nula je zobrazený ako 0 (a zarovnaný doľava)
• Text je zobrazený tak, ako je (zarovnaný vpravo)

A ak zadáte dátum, zobrazí sa na základe vášho regionálneho nastavenia (vo formáte DD-MM-RRRR alebo MM-DD-RRRR).

Štandardne je teda formátovanie čísla bunky:

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

Kde je každý typ údajov formátovaný ako všeobecný (a postupujte podľa vyššie uvedených pravidiel podľa toho, či ide o kladné číslo, záporné číslo, 0 alebo text)

Teraz je predvolené nastavenie v poriadku, ale ako zmeníte formát. Ak napríklad chcem, aby sa moje záporné čísla zobrazovali červenou farbou, alebo chcem, aby sa moje dátumy zobrazovali v konkrétnom formáte, ako to urobím.

Použitie rozbaľovacej ponuky Formát na páse s nástrojmi

Niektoré z bežne používaných formátov čísel nájdete na karte Domov v skupine Čísla.

Je to rozbaľovací zoznam, ktorý ukazuje mnoho bežne používaných formátov, ktoré môžete na bunku použiť jednoduchým výberom bunky a následným výberom formátu z tohto zoznamu.

Ak napríklad chcete previesť čísla na percentá alebo previesť dátumy na krátky formát dátumu alebo dlhý formát dátumu, potom to môžete ľahko vykonať pomocou možností v rozbaľovacom zozname.

Dialógové okno Formátovať bunky

Dialógové okná formátovania buniek poskytujú maximálnu flexibilitu pri použití formátov na bunku v programe Excel.

Už má veľa vopred pripravených formátov, ktoré môžete použiť, alebo ak si chcete vytvoriť svoj vlastný formát čísel, môžete to urobiť aj vy.

Tu je postup, ako otvoriť dialógové okno Formát buniek;

1. Kliknite na kartu Domov
2. V skupine Číslo kliknite na ikonu spúšťača dialógového okna (malá naklonená šípka v pravom dolnom rohu skupiny).

Otvorí sa dialógové okno Formát buniek. V rámci nej nájdete všetky formáty na karte Číslo (spolu s možnosťou vytvárať vlastné formáty buniek)

Dialógové okno buniek formátu môžete tiež použiť nasledujúcu klávesovú skratku:

CONTROL + 1 (všetky ovládacie klávesy a potom stlačte kláves 1).

V prípade, že používate Mac, môžete použiť CMD + 1

Teraz si dáme pár praktických príkladov, kde si vytvoríme vlastné formáty čísel.

Príklady použitia vlastného formátovania čísel v programe Excel

Teraz si dáme pár praktických príkladov, kde si vytvoríme vlastné formáty čísel.

Skryť hodnotu bunky (alebo Skryť všetky hodnoty v rozsahu buniek)

Niekedy môže byť potrebné skryť obsah bunky (bez skrytia riadka alebo stĺpca, v ktorom je bunka).

Praktickým prípadom je to, keď vytvárate informačný panel programu Excel a na hlavnom paneli používate niekoľko hodnôt, ale nechcete, aby ho používatelia videli. takže tieto hodnoty môžete jednoducho skryť

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete skryť všetky číselné hodnoty:

Tu je postup, ako to urobiť pomocou vlastného formátovania v programe Excel:

1. Vyberte bunku alebo rozsah buniek, pre ktoré chcete skryť obsah bunky
2. Otvorte dialógové okno Formát buniek (klávesová skratka Ctrl + 1)
3. Na karte Číslo kliknite na položku Vlastné
4. Do poľa Typ zadajte nasledujúci formát:

   ;;;

Ako to funguje?

Formát pre štyri rôzne typy dátových typov je rozdelený bodkočiarkou v nasledujúcom formáte:

;;;

Pri tejto technike sme odstránili formát a špecifikovali sme iba bodkočiarku, čo naznačuje, že pre bunky nechceme žiadne formátovanie. Toto je indikácia pre Excel, aby skryla všetok obsah, ktorý sa nachádza v bunke.

Všimnite si toho, že aj keď sa tým skryje obsah bunky, stále je tam. takže ak máte čísla, ktoré ste skryli, stále ich môžete použiť vo výpočtoch (čo z neho robí skvelý trik pre panely a zostavy Excelu).

Zobrazujte záporné čísla červenou farbou

V predvolenom nastavení sa záporné čísla zobrazujú so znamienkom mínus (alebo v niektorých regionálnych nastaveniach v zátvorke).

Môžete použiť vlastné formátovanie čísel na zmenu farby záporných čísel a ich zobrazenie v červenej farbe.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete zvýrazniť všetky záporné hodnoty červenou farbou.

Nasledujú kroky, ako to urobiť:

1. Vyberte rozsah buniek, v ktorých chcete zobrazovať záporné čísla červenou farbou
2. Otvorte dialógové okno Formát buniek (klávesová skratka Ctrl + 1)
3. Na karte Číslo kliknite na položku Vlastné
4. Do poľa Typ zadajte nasledujúci formát:

Všeobecné; [červená]-Všeobecné

Pri vyššie uvedenom formáte sa vaše bunky so zápornými hodnotami zobrazia červenou farbou.

Ako to funguje?

Keď ako vlastný formát čísel zadáte iba dva formáty, prvý sa bude brať do úvahy pre kladné čísla a 0 a druhý pre záporné čísla (nasledujúca hodnota sa predvolene stane všeobecným formátom).

Vo vyššie uvedenom formáte, ktorý sme použili, sme určili farbu, ktorú má tento formát zaberať, v hranatých zátvorkách.

Aj keď majú záporné čísla všeobecný formát, sú zobrazené červenou farbou so znamienkom mínus.

V prípade, že chcete, aby sa záporné čísla v zátvorkách zastavovali červenou farbou (namiesto znamienka mínus), môžete použiť nasledujúci formát:

Všeobecné; [červená] (Všeobecné)

Pridajte text k číslam (napríklad milióny/miliardy)

Jedna úžasná vec na vlastnom formátovaní čísel je, že k číslu môžete pridať text predpony alebo prípony, pričom v bunke ponecháte pôvodné číslo.

Môžem napríklad nechať „100“ zobraziť ako „100 miliónov dolárov“, pričom v bunke budem mať stále pôvodnú hodnotu, aby som ju mohol použiť pri výpočtoch.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete tieto čísla zobrazovať so znakom dolára a slovom milión pred ním.

Nasledujú kroky, ako to urobiť:

1. Vyberte množinu údajov, do ktorej chcete pridať text k číslam
2. Otvorte dialógové okno Formát buniek (klávesová skratka Ctrl + 1)
3. Na karte Číslo kliknite na položku Vlastné
4. Do poľa Typ zadajte nasledujúci formát:

Všeobecné „milióny“ dolárov

Tento vlastný formát čísel ovplyvní iba čísla. Ak zadáte text do tej istej bunky, bude vyzerať tak, ako je.

Ako to funguje?

V tomto prípade som do všeobecného formátu pridal slovo milión v dvojitých úvodzovkách. všetko, čo pridám do úvodzoviek do formátu, sa zobrazí tak, ako je v bunke.

Tiež som pridal znak dolára pred všeobecný formát, aby všetky moje čísla tiež zobrazovali znak dolára pred číslom.

Poznámka: Ak vás zaujíma, prečo nie je znak dolára v úvodzovkách, keď je slovo Milión v dvojitých úvodzovkách, je to preto, že Excel rozpoznáva dolár ako symbol, ktorý sa často používa vo vlastnom formátovaní čísel, a nevyžaduje od vás pridať k tomu dvojité úvodzovky. Môžete pokračovať a do znaku dolára pridať dvojité úvodzovky, ale keď zatvoríte dialógové okno buniek formátu a znova ho otvoríte, všimnete si, že ho Excel odstránil.

Maskovať čísla a text

Pri vlastnom formátovaní čísel môžete posúdiť maximálne dve podmienky a na základe týchto podmienok môžete na bunku použiť formát.

Predpokladajme napríklad, že máte zoznam skóre pre niektorých študentov, chcete zobraziť text Úspešne alebo Neúspešne na základe skóre (kde akékoľvek skóre menšie ako 35 by malo ukazovať „Neúspešné“, v opačnom prípade by malo zobrazovať „Úspešné“.

Nasledujú kroky, ako namiesto hodnoty pomocou vlastného formátu čísla zobraziť skóre ako úspešné alebo nesplnené:

1. Vyberte množinu údajov, v ktorej máte skóre
2. Otvorte dialógové okno Formát buniek (klávesová skratka Ctrl + 1)
3. Na karte Číslo kliknite na položku Vlastné
4. Do poľa Typ zadajte nasledujúci formát:

   [<35] "Zlyhanie"; "Úspech"

Nasleduje postup, ako by vaše údaje vyzerali po použití vyššie uvedeného formátu.

Všimnite si toho, že to nemení hodnotu v bunkách. Bunka má naďalej skóre v číselných hodnotách, pričom v závislosti od hodnoty bunky zobrazuje „Prešiel“ alebo „Nepodarilo sa“.

Ako to funguje?

V tomto prípade je potrebné podmienku zadať do hranatých zátvoriek. vlastné formátovanie čísel potom vyhodnotí hodnotu v bunke a podľa toho použije formát.

Ak je teda hodnota v bunke menšia ako 35, číslo je skryté a namiesto toho sa zobrazí text „Zlyhanie“ a pri všetkých ostatných prípadoch sa zobrazí „Pass“.

Podobne, ak chcete previesť rozsah buniek, ktoré majú 0 a 1 na TRUE a FALSE, môžete použiť nasledujúci formát:

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

Pripomíname, že pri formátovaní vlastného čísla môžete použiť iba dve podmienky. Ak sa pokúsite použiť viac ako dve podmienky, bude to tak, že vás výzva upozorní, že tento formát nemôže prijať.

Skryť text, ale zobraziť čísla

Niekedy, keď sťahujete svoje údaje z databáz, pretože sú prázdne, sú naplnené hodnotou, ako je Nedostupné alebo NA alebo -

Vďaka vlastnému formátovaniu čísel môžete skryť všetky textové hodnoty, pričom ponecháte čísla tak, ako sú. to tiež zaisťuje, že vaše pôvodné údaje sú neporušené (pretože textovú hodnotu neodstraňujete, iba ju skryjete)

Predpokladajme, že máte množinu údajov uvedenú nižšie, do ktorej chcete skryť všetky textové hodnoty:

Nasledujú kroky, ako to urobiť:

1. Vyberte množinu údajov, v ktorej máte údaje
2. Otvorte dialógové okno Formát buniek (klávesová skratka Ctrl + 1)
3. Na karte Číslo kliknite na položku Vlastné
4. Do poľa Typ zadajte nasledujúci formát:

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

Všimnite si toho, že som použil všeobecný formát pre čísla. Môžete mať ľubovoľný formát (napríklad 0, 0. #, #0,0%). Všimnite si tiež, že pred druhým všeobecným formátom je záporné znamienko (-), pretože predstavuje formát pre záporné čísla.

Ako to funguje?

V tomto prípade, pretože sme chceli skryť všetky textové hodnoty, jednoducho sme preň neurčili formát.

A keďže sme určili formát pre ďalšie tri typy údajov (kladné číslo, záporné číslo a nula), Excel pochopil, že formát pre textovú hodnotu sme účelovo ponechali prázdny a nemalo by sa nič zobrazovať, ak má bunka text reťazec.

Zobraziť čísla v percentách (%)

Pomocou vlastného formátovania čísel môžete tiež zmeniť čísla a nastaviť ich tak, aby sa zobrazovali v percentách.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete tieto čísla previesť na ekvivalentnú percentuálnu hodnotu:

Nasledujú kroky, ako to urobiť:

1. Vyberte bunky, v ktorých máte čísla (ktoré chcete previesť na percentá)
2. Otvorte dialógové okno Formát buniek (klávesová skratka Ctrl + 1)
3. Na karte Číslo kliknite na položku Vlastné
4. Do poľa Typ zadajte nasledujúci formát:

0%

Tým sa čísla zmenia na ich percentá. Mení sa napríklad z 0,11 na 11%.

Ak všetko, čo chcete, je previesť číslo na jeho ekvivalentnú percentuálnu hodnotu, lepší spôsob, ako to urobiť, je použiť percentuálnu hodnotu v dialógovom okne bunky formátu.

Vlastné formátovanie môže byť užitočné vtedy, keď chcete väčšiu kontrolu nad tým, ako chcete zobrazovať percentuálnu hodnotu.

Ak napríklad chcete zobraziť nejaký dodatočný text s percentom, všetko, čo chcete, aby sa záporné percentá zobrazovali červenou farbou, je lepšie použiť vlastné formátovanie.

Zobraziť čísla v inej jednotke (milióny ako miliardy alebo gramy na kilogramy)

Ak pracujete s veľkými číslami, možno ich budete chcieť ukázať v inej jednotke. napríklad, ak máte údaje o predajoch niektorých spoločností, možno budete chcieť zobraziť tržby v miliónoch alebo miliardách (pričom v bunkách stále ponecháte pôvodnú hodnotu predaja)

Našťastie to môžete ľahko urobiť pomocou vlastného formátovania čísel.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete tieto hodnoty predaja zobrazovať v miliónoch.

Nasledujú kroky, ako to urobiť:

1. Vyberte bunky, v ktorých máte čísla
2. Otvorte dialógové okno Formát buniek (klávesová skratka Ctrl + 1)
3. Na karte Číslo kliknite na položku Vlastné
4. Do poľa Typ zadajte nasledujúci formát:

0.0,,

Vyššie uvedené by vám poskytlo výsledok uvedený nižšie (hodnoty v miliónoch v jednej číslici za desatinnou čiarkou):

Ako to funguje?

Vo vyššie uvedenom formáte nula pred desatinnou čiarkou znamená, že Excel by mal zobrazovať celé číslo (presne tak, ako je v bunke), a 0 za desatinnou čiarkou znamená, že by mala za desatinnou čiarkou zobrazovať iba jednu platnú číslicu.

Takže 0,0 by bolo vždy číslo s jednou číslicou za desatinnou čiarkou.

Keď za 0,0 pridáte čiarku, oholí sa posledná trojmiestna číslica. Napríklad 123456 bude 123,4 (posledné tri číslice budú odstránené a keďže musí za desatinnou čiarkou ukazovať 1 číslicu, zobrazuje 4)

Ak sa nad tým zamyslíte, je to podobné ako delenie čísla číslom 1 000. Ak teda chcete číslo zobraziť v jednotke „milióny“, musíte pridať 2 čiarky, aby sa oholil posledných 6 číslic.

A ak chcete za číslom ukázať aj slovo milión alebo abecedu M, použite nižšie uvedený formát

0,0 ,, "M"