Články v kategórii Príklady:
Príprava kalendára | Počet absencií | Plánovač dovolenky | Sledovanie PN-niek | Kalendár sviatkov
V tomto článku si vytvoríme univerzálny kalendár, ktorý sa dá využiť na plánovanie dovolenky, sledovanie dochádzky alebo počtu hodín strávených u doktora, atď. Použijeme naň kalendár, ktorý sme si pred-pripravili v článku Dochádzka Excel.
Hotový kalendár, ktorý si spolu budeme vytvárať v tomto článku, si môžete stiahnuť tu.
Do môjho kalendára si v prvom rade pridáme hárky s prehľadom. Pomenujeme ich Prehľad 2019, Prehľad 2020 atď.
Potom do týchto hárkov pridáme tabuľku, v ktorej budeme zaznamenávať počet absencií, počet dní dovolenky a počet hodín na PN. Napríklad v hárku "Prehľad 2019" budeme mať:
Najprv si pridáme vzorec, ktorý nám bude sčítavať všetky absencie za celý rok. Využijeme na to funkciu SUMIF. Viac o tejto funkcií a iných užitočných funkciách pre súčet v Exceli nájdete v článku Funkcia SUMIF.
Vpravo vedľa Meno 1 do bunky C6 pridáme nasledovný vzorec:
=SUMIF('Rok 2019'!$B$7:$B$170,'Prehľad 2019'!B6,'Rok 2019'!$AH$7:$AH$170)
Funkcia SUMIF tohto vzorca má nasledovné argumenty:
Tento vzorec pretiahneme aj do ostatných buniek pre kalendár v Exceli.
Takto sme vytvorili prehľad súčtu dní absencií pre každé meno za celý rok 2019.
Podobne si vytvoríme aj kalendár v Exceli pre prehľad dní dovolenky za celý rok a aj mesačne.
Najprv si pridáme mesačný prehľad do hárku Rok 2019. Do stĺpca AI si pridáme mesačný prehľad pomocou funkcie COUNTIF. Táto funkcia nám vráti počet buniek, ktoré spĺňajú nejakú podmienku. Naša podmienka bude, že bunka sa bude počítať ak obsahuje písmeno D. Týmto písmenom budeme v kalendári značiť dni dovolenky. Do bunky AI7 vložíme vzorec:
=COUNTIF(C7:AG7,"=D")
Tento vzorec pretiahneme aj to ostatných buniek v stĺpci AI. Ako skúšku dáme do bunky AA7 písmeno D.
Všimli sme si, že sa aj hodnota pre počet absencií zmenila na 1. Je to pre to, lebo tam máme vzorec COUNTA, ktorý počíta všetky bunky, ktoré nie sú prázdne. Túto funkciu zmeníme tiež na COUNTIF, a to nasledovne:
=COUNTIF(C7:AG7,"=A")
Teraz vráti iba počet buniek, ktoré obsahujú A.
Na to, aby používateľ kalendára v Exceli vedel, že je treba rozlišovať absencie a dovolenky podľa písmen A a D, pridáme si o tom žlto-zvýraznené informácie.
Ešte si do hárku "Prehľad 2019" pridáme prehľad za celý rok. Podobne ako pri prehľade absencií, využijeme funkciu SUMIF. Do bunky D6 v hárku "Prehľad 2019" vpíšeme:
=SUMIF('Rok 2019'!$B$7:$B$170,'Prehľad 2019'!B6,'Rok 2019'!$AI$7:$AI$170)
Je to skoro ten istý vzorec ako pri počte absencií, pričom jediné, čo sme zmenili, je rozsah poľa v treťom argumente na AI7 až AI170, pretože teraz chceme spočítavať bunky v stĺpci AI. Takže teraz tretí argument 'Rok 2019'!$AI$7:$AI$170 označuje pole buniek, ktoré chceme sčítavať. V našom prípade bunky AI7 až AI170 z hárka Rok 2019.
Vzorec pretiahneme do ostatných buniek pod "Počet dní dovolenky" a je to.
Vytvorili sme si plánovač dovolenky ako kalendár v Exceli behom chvíľky. Pomocou tohto plánovača môžme mať prehľad o dovolenkách všetkých členov nášho tímu.
Ak by sme chceli sledovať miesto počtu dní počet hodín (napríklad PN hodín), budeme musieť využiť mierne inú kombináciu funkcií. V hárku "Rok 2019" pridáme do stĺpca AJ nadpis “Počet hodín na PN”. Pre spočítanie počtu hodín za každý mesiac použijeme funkciu SUM. V bunke AJ7 bude vzorec vyzerať takto:
=SUM(C7:AG7)
Keď skopírujeme vzorec aj do ostatných buniek v stĺpci AJ, vidíme, že nám vzorec v riadkoch medzi mesiacmi spočítal aj hodnoty dátumov. Do týchto buniek
skopírujeme nadpisy z buniek AH6:AJ6.
Teraz ešte do hárku "Prehľad 2019" pridáme funkciu SUMIF aj pre Počet hodín na PN, aby sme si skompletizovali náš kalendár v Exceli. Vzorec bude vyzerať takto:
=SUMIF('Rok 2019'!$B$7:$B$170,'Prehľad 2019'!B6,'Rok 2019'!$AI$7:$AI$170)
Jediná zmena od vzorcov v predošlých odstavcoch je v treťom argumente pole buniek AJ7:AJ170 z hárka "Rok 2019".
Do kalendára v Exceli môžeme tiež pridať dni sviatkov. V článku Excel Sviatky som pre vás pripravil kompletný zoznam sviatkov a ich Excel hodnôt. My tento zoznam teraz využijeme na to, aby sme v našom kalendári vyznačili, ktoré dni sú sviatky.
Ukážeme si to na roku 2019. Pre ostatné roky by bol postup rovnaký. Pre rok 2019 máme tieto sviatky a ich Excel hodnoty, ktoré si skopírujeme do hárku "Prehľad 2019".
Vyznačiť sviatky v našom kalendári je najjednoduchšie pomocou podmieneného formátovania. V hárku "Rok 2019" si pridáme nové pravidlo formátovania pre bunky C6 až AG16. Ako podmienku podmieneného formátovania použijeme nasledovný vzorec:
=OR(C$6='Prehľad 2019'!$J$6,C$6='Prehľad 2019'!$J$7,C$6='Prehľad 2019'!$J$8,C$6='Prehľad 2019'!$J$9, ,C$6='Prehľad 2019'!$J$10,C$6='Prehľad 2019'!$J$11,C$6='Prehľad 2019'!$J$12,C$6='Prehľad 2019'!$J$13,C$6='Prehľad 2019'!$J$14,C$6='Prehľad 2019'!$J$15,C$6='Prehľad 2019'!$J$16,C$6='Prehľad 2019'!$J$17,C$6='Prehľad 2019'!$J$18,C$6='Prehľad 2019'!$J$19,C$6='Prehľad 2019'!$J$20)
Tento vzorec je jednoduchší, ako sa zdá. Ak ste si všimli, hrubo-vytlačená časť vo vzorci sa opakuje. Jediné, čo sa mení je posledné číslo, teda riadok zo stĺpca J.
Ako formát zvolím oranžovú výplň.
Tieto kroky zopakujeme aj pre ostatné mesiace. Pre február by sme takéto podmienené formátovanie v hárku "Rok 2019" uplatnili na bunky C20 až AG30.
Náš video kurz Excelu