Vlastné formáty buniek v Exceli: 7 praktických príkladov nielen pre čísla

Základná syntax vlastného formátu

Na definovanie vlastného formátu sa používajú rôzne preddefinované znaky – kódy z prehľadu vyššie. Znaky môžete navzájom kombinovať. Práve ich vzájomnou kombináciou vznikajú vlastné formáty, ktoré dokážu zobrazovať údaje podľa vašich potrieb.

Zápis:

0,0″ km“

zobrazí číslo s jedným desatinným miestom a textovou jednotkou km.

Napríklad zápis:

# ##0,00 €

zobrazí číslo s pozične oddelenými tisícami, dvoma desatinnými miestami a symbolom eura na konci.

7 praktických príkladov vlastných formátov buniek v Exceli

V tejto časti si ukážeme praktické príklady, ktoré využijete v reportoch, evidenciách, cenníkoch, zoznamoch alebo nástenkách (dashboardoch). Pri každom príklade je dôležité myslieť na to, že vlastný formát nemení skutočnú hodnotu v bunke, mení len jej zobrazenie.

Vlastný formát dokáže obsahovať aj podmienené pravidlá bez použitia nástrojov podmieneného formátovania.

[>=1000][Modrá]# ##0;[<0][Červená]-# ##0;# ##0

Kladné číslo v bunke ≥ 1000 sa zobrazí modrou farbou, bez desatinných miest, s pozične oddelenými tisícami a miliónmi.
Záporné číslo < 0 bude zobrazené červenou, so znamienkom mínus, bez desatinných miest, s pozične oddelenými tisícami a miliónmi.
Ostatné → výsledok bude zobrazený čiernou farbou, bez desatinných miest, s pozične oddelenými tisícami a miliónmi.

V kombinácii s textovým výstupom môže vlastný formát v jednoduchých prípadoch nahradiť funkciu podmieneného formátovania:

[Modrá][>=75]“Test úspešný“;[Červená][<75]“Test neúspešný“

Poznámka:
Pri podmienkach vo vlastnom formáte platí dôležité obmedzenie: v jednom formáte môžete použiť maximálne dve podmienky. Na zložitejšie logické členenia je lepšie použiť podmienené formátovanie alebo pomocný vzorec.

Vo finančných reportoch sa neraz používa pre sprehľadnenie výsledkov zobrazenie v tisícoch alebo miliónoch. Dosiahnete ho pomocou medzery za číselnou hodnotou (v US lokalizácii čiarkou). Automatický prepočet obsahu bunky môže vyzerať napríklad takto:

0,0 “ kW“
0,0 “ MW“

Jedna medzera zníži číslo v bunke o 3 rády, dve medzery o 6 rádov – zobrazovaný údaj bude v tisícoch, resp. miliónoch. Medzera uvedená za prvými úvodzovkami slúži na odsadenie textu od zobrazovanej číselnej hodnoty.

Pri skombinovaní s podmienkou môžete priamo vo formáte vytvoriť aj prevod pri zobrazení:

[Červená][>=1000000]0,0 “ MW“;[<1000000]0,0 “ kW“
[>=1000]0,00 “ km“;[>=1]# ##0″ m“

V prvom prípade formát zvýrazní červenou údaje o spotrebe odberateľa väčšej ako 1 MW (zobrazí v MW), ostatné údaje zobrazí v kW.
V druhom prípade, ak je v bunke údaj 1500, zobrazí sa ako 1,50 km, ale ak je 985, zobrazí sa ako 985 m – bez akéhokoľvek vzorca. Dôležité je nezabudnúť vložiť medzeru pred a do úvodzoviek s km – jedna medzera pred úvodzovkami dáva pokyn Excelu deliť obsah tisícom. Medzera v úvodzovkách slúži už len na odsadenie jednotky od čísla.

Je to praktické pri metroch/kilometroch, gramoch/kilogramoch/tonách, wattoch/kilowattoch/megawattoch, litroch/m³ a pod. v reportoch, kde nechcete pomocný stĺpec. Treba však myslieť na to, že vlastný formát nemení reálnu hodnotu v bunke – iba ju pri zobrazení zmenší tisíc- alebo miliónkrát.

Platí obmedzenie uvedené v predchádzajúcej časti s výsledkami testov – max. dve podmienky v jednom formáte – [>=x] / [<x] alebo [>=x] / [>y] pre x>y. Na zložitejšie logické členenia je potrebné použiť podmienené formátovanie.

Vlastný typ formátu vám umožní aj „neformátovať“.
Tri bodkočiarky bez akéhokoľvek obsahu vyzerajú takto netradične:

;;;

Bunka s obsahom kladného, záporného čísla, nuly i textu bude v mriežke vyzerať prázdna – nič nezobrazí. Obsah v nej však stále zostáva. Uvidíte ho na riadku vzorcov. Vzorce s ním normálne počítajú, dá sa i kopírovať, filtrovať, zoraďovať, zobraziť v grafe atď.

Toto je praktická finta na skrytie pomocných hodnôt (napr. medzivýpočtov alebo kódov), ktoré nechcete zobrazovať, ale potrebujete ich pre funkčnosť tabuľky či grafu. Bez nutnosti skrývať stĺpce tabuľky alebo skrývať či zamykať hárok.

Takto môžete skryť hoci len konkrétnu časť údajov.

0,00;-0,00;

Tento typ formátovania skryje všetky nuly. Údaje za druhou bodkočiarkou nemajú definovaný formát zobrazenia, teda nuly v bunkách síce budú, ale v tabuľke budú viditeľné len ako prázdne bunky.

Poznámka:
takto skrytý obsah nie je zabezpečený ani chránený. Používateľ ho stále uvidí na riadku vzorcov alebo po zmene formátu bunky. Ide o vizuálne skrytie, nie o ochranu údajov.

Opakovanie viacerých núl za sebou pri formátovaní čísla v takejto podobe:

00000

prinesie vo výsledku takýto efekt – obsah bunky 123 zobrazí ako 00123.
Veľmi praktické pre čísla faktúr, zákazníkov a pod.

Ak potrebujete na začiatku identifikátor, napr. rok vystavenia faktúry, stačí malá modifikácia:

20260000

Výsledok? Poradové čísla faktúr s prefixom 2026.
Podobne môžete vytvoriť textový identifikátor – textový znak však treba uviesť do úvodzoviek, napr. takto: „Z“20260000. Vo výsledku sa 123 zobrazí ako Z20260123.
Vhodné pre rozlíšenia faktúr, skladových kariet a pod.

Ako definovať vzhľad telefónneho čísla, keďže Excel predčíslie 09xx okamžite zmení na číslo bez úvodnej nuly? Riešení je viacero. Jeden typ formátovania vychádza z princípu použitého pri faktúrach (tu v troch obmenách), druhý – pre medzinárodné formáty – je (v dvoch obmenách) trochu sofistikovanejší.

0000″ „000“ „000
+421 (000)\ 000\ 000
0000\ 000\ 000
+421\ ###\ ###\ ##0
+421“ „# ##0

Pri telefónnych číslach však treba byť opatrný. Vlastný formát funguje dobre vtedy, keď je telefónne číslo uložené ako číslo a má predvídateľnú dĺžku. Ak telefónne čísla importujete z iného systému alebo obsahujú znak +, medzery, zátvorky či majú rôzne dĺžky, často je bezpečnejšie ponechať ich ako text a upraviť ich vzorcom alebo cez Power Query.

V praxi sa používajú tie isté princípy pre zobrazenie PSČ:

000\ 00

V prípade PSČ je obsahom bunky číslo, preto typ formátu funguje jednoducho. Ak však pracujete s databázou adries alebo exportom pre iný systém, je vhodné overiť, či daný systém nevyžaduje PSČ uložené ako text.

Hviezdička * pred znakom ho zopakuje toľkokrát, aby vyplnil celú šírku bunky. Funguje pre ľubovoľný znak:

\_ @ *_

Uvedený príklad zobrazí od okraja bunky podčiarkovník, potom text v bunke a za ním podčiarkovník až do konca bunky.

@ *.

Tento typ formátu bunky zobrazí text v bunke zarovnaný doľava a po ňom bodky až po pravý okraj bunky.

Výsledok je tlačená cenníková tabuľka s názvom produktu a cenou v jednom riadku, pričom oko prirodzene prejde od názvu k cene. Bodky vyplnia priestor medzi názvom a cenou – klasický reštauračný/jedálny lístok efekt.

Excel má 8 základných farieb a 56 indexovaných farieb. Všetky sa dajú použiť vo formáte cez [FarbaN], avšak cez vlastný formát bunky (Formát buniek) môžete nastaviť farbu písma len pre obsah bunky – pomocou [Červená] alebo [Farba3], nie pre výplň bunky.

Pozadie – výplň je možné okrem manuálneho použitia nástroja Výplň nastaviť pomocou podmieneného formátovania alebo makier, nie pomocou vlastného formátu.

Sivé pozadie niektorých farieb je doplnené len kvôli slabému kontrastu na bielom pozadí.

Základné farby v Exceli:
[Čierna] [Biela] [Červená] [Zelená] [Modrá] [Žltá] [Purpurová] [Azúrová]

Príklady použitia vlastného typu formátu s farbou:

[Zelená]# ##0;[Červená]-# ##0
[Farba10]# ##0;[Farba3]-# ##0

zobrazia kladné čísla zelenou, záporné červenou farbou. Použitie štandardnej zelenej býva málo kontrastné na bielom pozadí, preto je v príklade použitý odtieň tmavozelenej (Farba10). Typ formátovania môže vyzerať aj takto:

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

Farby nastavené cez vlastný formát sa týkajú písma v bunke. Nie sú to isté ako výplň bunky ani tematické farby z farebnej palety (Rozloženie strany > Farby).

Pre pokročilých excelákov môžu byť milým prekvapením formátovania pomocou doplnkových symbolov a špeciálnych znakov, napríklad:

▲ 0;▼ 0;“-“
[Farba10]0,00 € ▲;[Červená]-0,00 € ▼;0
[Farba10]0,00 € \✓;[Červená]0,00 € \⮽;

Hoci bodka nie je špeciálny znak, ukážeme si v druhej tabuľke aj jej špeciálne použitie pri tvorbe typu formátu poradového čísla.
Vyzerá to takto (dva varianty):

0″.“
0\.

Poznámka:
šípky a iné symboly či ikony pri vytváraní typu formátovania nájdete na karte Vložiť > Symboly > Symbol > font Arial alebo WingDings. Šípky môžete vložiť aj priamoo z klávesnice skratkami ľavý Alt + 30 (▲) alebo ľavý Alt + 31 (▼). Iné častejšie používané symboly vyčarujete kombináciou ľavý Alt + 7 (•), ľavý Alt + 9 (○). Čísla je potrebné vkladať z numerickej klávesnice.
Viac sa o písaní symbolov a špeciálnych znakov dočítate v článku Tipy a triky v Exceli 7