Štýly - Podmienené formatovanie

V tomto seriály si rozoberieme formátovanie tabuľky a podmienené formatovanie na jednoduchom príklade. Vytvoríme si jednoduchý plánovač dňa pre mesiac január. Plne funkčný plánovač vyzerá nasledovne:

Krok po kroku:

Označenú časť zlúčime do jednej bunky a text zarovnáme na stred. Písmo zväčšíme na požadovanú veľkosť. Obrázok pridáme na karte Vložiť --> Obrázok a zvolíme cieľovú destináciu obrázka. Pomocou myši vložíme obrázok na požadovanú pozíciu.

Vloženie aktuálneho dátumu: Aktuálny dátum vložíme pomocou funkcie TODAY 

syntax : =TODAY() 

Aby sme dosiahli dátum vo formáte názov dňa dd.mm.yyyy zvolíme nasledujúce formatovanie zobrazené na obrázku.

Vytvorenie formátovanej tabuľky:

Táto funkcia nám poskytuje možnosť vytvorenie estetických a hlavne funkčných tabuliek. Na výber je mnoho predvolených štýlov tabuliek. Pre tých náročnejších je tu ponuka vytvorenia vlastného motívu.

Po kliknutí na možnosť "Nový štyl tabuľky dostaneme nasledujúcu ponuku:

Verím, že táto ponuka je zreteľná a intuitívne budete vedieť postupovať aj pri vytváraní vlastných šablón. 

Výsledkom nášho snaženia je tabuľka, ktorá vyzerá po pár úpravach nasledovne.

Šírku stĺpcov upravíme nasledovne: pravým kliknutím na políčko napr. stĺpca B zvolíme ponuku šírka stĺpca. Viď obrázok.

Vytvorenie zoznamu údajov:

Pre rýchle vkladanie často opakovaného textu je výhodne tieto údaje vyberať z predvolenej ponuky. Na tento účel použijeme na karte Údaje --> Nástroje pre údaje --> Overenie údajov

Zo spomínanej ponuky zvolíme ponuku Zoznam. Zdroj údajov zvolíme podľa umiestnenia údajov. Pre prípad na obrázku je zdroj údajov A1:A3

PODMIENENÉ FORMÁTOVANIE:

Podmienené formatovanie je jednou z najužitočnejších funkcií, ktoré Excel ponúka. Podľa vami zvolených kritérií sa text alebo cele pole textov formatuje podľa vami zvolenom formate buniek.

V našom prípade chceme, aby sa náš zvýraznil riadok, podľa kontroly aktuálneho dátumu. Aby sme vizuálne videli, v ktorom dni sme. Ďalej by sme radi vedeli, ktoré z našich plánovaných úloh sme dokončili, ktoré sme pozastavili a ktoré sme v požadovanom termíne nestihli.

Samotný Excel ponúka obrovské množstvo predvolených formatov a zjednodušených zápisov podmienok. V tomto článku detailnejšie opíšem iba tie druhy formátovania, ktoré som v tomto prípade použil.

Zvýraznenie riadka na základe aktuálneho dátumu:

Klikneme na ponuku podmienené formatovanie a zvolíme možnosť Nové pravidlo. Označíme si cely rozsah buniek, ktorý chceme aby sa formatoval, ak je podmienka splnená (čiže, oblasť pod hlavičkou). Zvolíme možnosť formatovania " Použiť vzorec na určenie buniek, ktoré sa majú formátovať" a do riadku vzorcov zapíšeme =$A11=$J$8. Pri tomto zápise na chvíľku zastavíme.

Excel tento zápis chápe nasledovne : Ak sa bunka A11 rovná J8 tak označím vybrane riadok a sformatujem ho podľa tvojich kritérií. Ja som si nastavil modré pozadie a biely text. Pretože čierny sa na tmavšom podklade stráca. 

Ďalším krokom je vytvorenie podmieneného formatovania, ktoré po vybratí z ponuky stavu splnenia našej ulohy zvýrazní Ok zeleným pozadím, No červeným a On hold oranžovým.

Toto formatovanie zabezpečíme kliknutím na položku podmienené formatovanie --> Nove pravidlo --> Formatovať len bunky, ktoré obsahujú a vyberieme ponuku "špecifický text. Bunka ktorá vytvára pravidlo formatovania je =$Y$2. Čiže, ak bunka vo zvolenom rozsahu bude obsahovať rovnaký text ako Y2 bunka sa sformatuje na oranžovo. Takto to vytvoríme pre Ok a No.

Verím, že tento príklad bol pre vás prínosný. V prípade nejasnosti (pre mňa je veľa veci samozrejmosťou) alebo otázok sa na mňa môžte obrátiť pomocou komentára.

Výsledný plánovač si môžte stiahnuť tu.

Formátovanie čísla - Zaokrúhľovanie

V tejto časti sa pozrieme, aké možnosti nám Excel ponúka pri formatovaní čísel.

Kedže Excel patrí k tabuľkovým procesorom, jeho základnou funkciou je práca s čislami. Zavisí práve od Vás a Vašej špecializácie, aký format je pre Vás najdôležitejší.

Pomocou rozkliknutej položky dokážeme formatovať text podľa predvolených formátov. Jednotlivé položky sú zobrazené na nasledujúcom obrázku.

Je neskonale množstvo možnosti, akým môžte vaše čísla upravovať. Nechám to na vašej kreativite. Ak by sa vám zdalo, že predvolené formáty čísel sú pre vás obmedzujúce tak kliknutím na položku "Ďalšie formáty čísel" získate nasledovná ponuku. V tejto ponuke si môžte vybrať z väčšieho množstva formátov alebo si vytvoriť formát vlastný.

Zaokrúhľovanie: 

Zaokrúhľovanie môžme vykonať viacerými spôsobmi, ale dalo by sa rozdeliť do dvoch kategórií.

• Zmena počtu zobrazených desatinných miest bez toho, aby sa zmenilo číslo
• Zaokrúhlenie, kedy sa zmení hodnota čísla - tu sa nezaobídeme bez funkcií

1. Ak chcem zmeniť počet desatinných miest bez toho aby sme zmenili hodnotu čísla (Aj napriek tomu, že sa niektoré desatine čísla nezobrazujú, Excel počíta so všetkými.). Túto akciu podnikneme pomocou tlačidiel

FUNKCIA ROUND:

ROUNDUP: zaokrúhli čísla nahor: =ROUNDUP(Bunka;počet číslic), ak zadáme počet číslic 0 --> zaokrúhli na cele číslo.

ROUNDDOWN: zaokrúhli čísla nadol:  =ROUNDDOWN(Bunka;počet číslic) 
EVEN: Zaokrúhli na najbližšie párne číslo: =EVEN(Bunka)
ODD: Zaokrúhli na najbližšie nepárne číslo: =ODD(A2)

Súbor k tejto lekcii stiahnete tu.

Zarovnanie textu - Príprava faktúry

Síce Excel nie je stavaný pre úpravu textu, jeho možnosti v úprave textu a modifikáciach sú v mnohých ohľadoch aj lepšie ako v programe Word.

Predstavme si, že neexistuje program na úrovni ako Word a máme pripraviť formulár, ktorý budeme každodenne používať. Budeme sa tváriť, že máme dobre fungujúcu spoločnosť a potrebujeme si pripraviť fakturačný list, ktorý budeme zasielať spolu s našimi výrobkami. Táto faktúra môže vyzerať nasledovne:

Samozrejme k dokonalosti chýbajú viaceré údaje. Na ukážku zarovnania textu a formátovať buniek bude tento príklad postačovať.

V tejto časti využijeme vedomosti a zručnosti, ktoré sú rozpísané v predchádzajúcich častiach tohto blogu hlavne čo sa týka využitia funkcie SUM.

Na začiatok si vytvoríme prázdny list, ktorý predom ohraničíme, aby naša faktúra mala predpisanú veľkosť a aby sa tlačila iba na 1 A4-ku.

Tento postup môžme rozpísať do 3 krokov.

1. Ohraničíme si čiernym, hrubým orámovaním oblasť ktorú chceme upraviť na našu faktúru.
2. Pomocou farby výplne v kroku 2, vytvoríme biele pozadie. Pre mňa osobne je táto štruktúra dokumentu lepšia pre editáciu.
3. Stlačíme tlačidlo "ukážka zlomov strán"

Výsledkom týchto krokov je nasledovný dokument. Tento dokument si samozrejme ešte prispôsobím. Určite budem pridávať niekoľko stĺpcov aby sa zachovala aj estetická stránka dokumentu.

Na začiatok potrebujeme vyplniť informácie potrebné pre zadanie faktúry. A tými sú: Kontaktné údaje, IČO,.. , Názov produktov a podobne.
Neformatovaný súbor vyzerá nasledovne:

1. NADPIS: Označíme si formatovanú oblasť, čo predstavuje na obrázku bod 1, v bode 2 zlúčime a vycentrujeme bunky, bod 3 a 4 je na každom. Ja preferujem font Cambria veľkosť 24 pt.
   
   
2. Popis "našej firmy" nie je nijako z formatovania textu zaujímavý a je opäť na každom z nás ako by si to predstavoval.
3. FAKTURAČNÉ ÚDAJE: Kedže dátum, číslo účtu by zabralo väčšie miesto ako jednu bunku vyberieme si v rozsahu viac buniek. V bode 1 bunky zlúčime, v bode 2 zarovnáme nadol (toto formatovanie je zaujímave vtedy, ak je iná výška bunky). V bode 3 zarovnáme text vľavo.
   
   
4. SEKCIA ODBERATEĽ: Táto sekcia sa v podstate ničím nelíši od tej predošlej. Jedine s tým rozdielom, že sa pridalo orámovanie. 
   
   
   
   
   
5. FAKTUROVANÉ POLOŽKY: Opäť sa jedná o zlučovanie buniek. Kvôli automatizácii sa tieto bunky prepoja vzorcami v každom riadku, kde sa vypočíta cena za danú položku. V tomto prípade použijeme pri násobení funkciu IFERROR, ktorá vráti hodnotu ak je výsledok číslo, ak je výsledok chyba vloží požadovaný znak, číslo alebo inú hodnotu.
   pre nás prípad platí: =IFERROR(K19*H19;""), kde platí podmienka, ak je výsledok chyba zapíše "prázdnu bunku".
   
   
   
   V poslednej bunke tabuľky sa nachádza funkcia =SUM(N19:O34), ktorá vypočíta výslednú cenu, ktorá bude fakturovaná.

Ďalšie kroky zvládnete podľa vyššie uvedeného aj sami.

V tomto článku som ešte nespomenul niektoré súčasti tejto ponuky. Na nasledujúcom obrázku vidieť, ako sa využíva zarovnanie textu.

Sklon textu: 

Zalomenie textu: 

Týmto by som ukončil ďalšiu časť môjho tutorialu. Verím, že pridanie viacerých obrázkov a výsledného súboru Vám pomôže sa jednoduchšie zorientovať v danej problematike.

Súbor faktúra nájdite tu.

SUM, AVERAGE, COUNT, MAX, MIN

V tejto časti môjho seriálu sa pozrieme na základné funkcie, ktoré ponúka Excel.

Nazdávam sa, že ide o najviac používane funkcie v Exceli. V postupnosti krokov si na jednoduchej tabuľke s údajmi vysvetlíme syntax a možnosti použitia týchto funkcii.

Príklad: Máme pre nášho šéfa určiť, ktorý predajca dosiahol najvyšší počet predaných výrobkov za roky 2009,2010 a 2011. Taktiež chce vedieť, aký je priemer medzi jednotlivými predajcami. Ako zaneprázdnený šéf už ani nevie koľko ľudí vlastne zamestnáva. Preto bude dobre uviesť aj tento údaj.

SUM: Funkcia sum vráti sumu všetkých údajov vo výbere. Nás v tomto kroku bude zaujímať, koľko sa predalo kusov výrobkov za jednotlivé roky 2009-2011. Pre využitie funkcie SUM máme viacero možnosti.

1. Priamo: označíme bunku v ktorej chceme výsledok  a zapíšeme =SUM(Bunka1:BunkaX). V našom prípade pre rok 2009 vzorec vyzerá nasledovne: =SUM(B2:B49)
   
   
   
2. Na karte domov --> Úpravy nájdeme ikonu . Postup je nasledovný. Zvolíme si oblasť pre ktorú sa ma vykonať súčet prvkov výberu, klikneme na túto ikonu a zvolíme funkciu SUM. Výsledok musí byť vo všetkých prípadoch totožný.

Excel ponúka viacero možnosti akým vytvárať sučty. Ďalším rozšírením funkcie SUM je funkcia SUMIF, ktorá vracia súčet na základe zadanej podmienky.

SUMIF:
=SUMIF(rozsah buniek; kritérium, rozsah súčtu)

Pre názornosť rozšírime náš príklad, kde pridáme k jednotlivým predajcom ich regionálnu príslušnosť.

Vzorec vyzerá nasledovne: =SUMIF($B$2:$C$49;G2;$C$2:$C$49) pre rok 2009.

O veľa názornejšie použitie tejto funkcie je pre napr rok 2011, kde sa rozsah buniek $B$2:$E$49, čo predstavuje takmer cely rozsah tabuľky. Rozhodujúci je zápis rozsahu súčtu. To je rozsah údajov, z ktorých sa vypočíta súčet  $E$2:$E$49. 

=SUMIF($B$2:$E$49;G2;$E$2:$E$49)

MIN, MAX, AVERAGE: Tieto funkcie by som zbytočne nerozpisoval.

Funkcia MIN vráti hodnotu najnižšieho čisla z výberu.

=MIN(Rozsah buniek)
=MIN(C$2:C$49)


MAX: vráti hodnotu najvyššieho čisla z výberu:
=MAX(Rozsah buniek)
=MAX(C$2:C$49)



AVERAGE/PRIEMER: Vypočíta priemer z vybraných údajov.

=AVERAGE(Rozsah buniek)
=AVERAGE(C$2:C$49)


COUNT: o tejto funkcii som už písal v tomto článku.


Súbor pre názornú ukážku stiahnete tu