Projektová evidence v Excelu

Připravila Lenka Pyrasová

Projektová terminologie

Stručný výčet pojmů a zkratek použitých v tomto příkladu.

MD(s) – manday(s) = „člověkoden(dny)“ (1 MD = 8 hodin)
INT = internista (zaměstnanec banky)
EXT = externista (pracuje na základě objednávky, dodávající firma fakturuje za jeho MDs)
Artefakt = kód, na který lidé vykazují odpracované MDs
PD = fáze Project Definition
SD = fáze Solution Design
IMP = fáze Implementation
HR – Human Resources = lidské zdroje
Budget = rozpočet (nákladů a MDs)
Alokace = zarezervování kapacity lidských zdrojů pro daný projekt
IPC – Internal Personnel Costs = interní personální náklady
OPEX – Operating Expense = operační náklady na zajištění běžné činnosti
CAPEX – Capital Expenditure = kapitálové (investiční) náklady
ITSA – IT Status Assessment = měsíční dashboard projektu pro vedení

Použité funkce

CONCATENATE

Sloučí několik textových řetězců do jednoho.

=CONCATENATE(text1; text2; …)

Text1, Text2, … = 1 až 255 textových řetězců, které chceme sloučit do jediného textového řetězce.

Více

DATUM

Vrátí číslo, které představuje datum v kódu aplikace MS Office Excel pro datum a čas.

=DATUM(rok; měsíc; den)

Rok = číslo od 1900 do 9999 představující rok.

Měsíc = číslo od 1 do 12 představující měsíc v roce.

Den = číslo od 1 do 31 představující den v měsíci.

TEXT

Převede hodnotu na text v určitém formátu.

=TEXT(hodnota; formát)

Hodnota = číslo, vzorec, jehož výsledek je číselná hodnota, nebo odkaz na buňku obsahující číselnou hodnotu.

Formát = číselný formát ve formě textu vybraný v seznamu Druh na kartě Číslo dialogového okna Formát buněk (kromě obecného formátu).

CHYBHODN

Funkce CHYBHODN slouží k nahrazení chybové hodnoty jiným textem nebo hodnotou.

=CHYBHODN(hodnota;hodnota_v_případě_chyby)

hodnota je vyhodnocovaná buňka nebo výpočet.

hodnota_v_případě_chyby je výstup funkce za předpokladu, že výsledkem vyhodnocení prvního argumentu je chybová hodnota. Vyhodnocovány jsou chyby #NENÍ_K_DISPOZICI, #HODNOTA!, #ODKAZ!, #DĚLENÍ_NULOU!, #ČÍSLO!, #NÁZEV? a #NULL!.

V Excelu 2010 je funkce CHYBHODN přeloženou funkcÍ IFERROR z Excelu 2007. Jde v podstatě spojením dvou funkcí - KDYŽ a JE.CHYBHODN. Tato funkce má v podstatě jen dva možné typy výstupů.

Výsledek odkazu nebo výpočtu není chybový - za tohoto předpokladu se zobrazí normální výsledek a funkce CHYBHODN tedy nemá žádný vliv.
Výsledek vzorce je chybový. V tomto případě funkce CHYBHODN vrátí to, co zadáme do argumentu hodnota_v_případě_chyby.

JE.ČISLO

Ověří, zda je argument Hodnota číslo, a vrátí hodnotu PRAVDA nebo NEPRAVDA.

=JE.ČISLO(hodnota)

Hodnota = hodnota, kterou chceme testovat.

JE.PRÁZDNÉ

Ověří, zda odkaz v argumentu Hodnota odkazuje na prázdnou buňku a vrátí hodnotu PRAVDA nebo NEPRAVDA.

=JE.PRÁZDNÉ(hodnota)

Hodnota = testovaná buňka nebo název, který se vztahuje k testované buňce.

JE.TEXT

Ověří, zda je argument Hodnota text a vrátí hodnotu PRAVDA nebo NEPRAVDA.

=JE.TEXT(hodnota)

Hodnota = hodnota, kterou chceme testovat.

NEBO

Ověří, zda je nejméně jeden argument roven hodnotě PRAVDA, a vrátí hodnotu PRAVDA nebo NEPRAVDA. Vrátí hodnotu NEPRAVDA pouze v případě, že všechny argumenty jsou rovny hodnotě NEPRAVDA.

=NEBO(logická1; [logická2]; …)

Logická1, Logická2, … = 1 až 255 podmínek, které chceme testovat, a které mohou mít hodnotu PRAVDA nebo NEPRAVDA.

SLOUPEC

Vrátí číslo sloupce odkazu.

=SLOUPEC([odkaz])

Odkaz = buňka nebo souvislá oblast buněk, jejichž číslo sloupce hledáme. Jestliže tento argument nezadáme, bude použita buňka obsahující funkci SLOUPEC.

SUMIF

Sečte buňky vybrané podle zadaných kritérií.

=SUMIF(oblast; kritéria; [součet])

Oblast = oblast buněk, které chceme sečíst.

Kritéria = kritérium ve formě čísla, výrazu nebo textu určující, které buňky budou sečteny.

Součet = skutečné buňky, které budou sečteny.

Více

SVYHLEDAT

Vyhledá hodnotu v krajním levém sloupci tabulky a vrátí hodnotu ze zadaného sloupce ve stejném řádku. Tabulka musí být standardně seřazena vzestupně.

=SVYHLEDAT(hledat; tabulka; sloupec; [typ])

Hledat = hodnota hledaná v prvním sloupci tabulky.

Tabulka = prohledávaná tabulka, může být odkaz na oblast nebo název oblasti.

Sloupec = číslo sloupce v argumentu Tabulka, ve kterém chceme vyhledat odpovídající hodnotu.

Typ = logická hodnota: PRAVDA (1) = nalézt nejbližší odpovídající hodnotu v prvním sloupci seřazeném vzestupně; NEPRAVDA (0) = nalézt přesnou odpovídající hodnotu.

Více

ZÍSKATKONTDATA

Extrahuje data uložená v kontingenční tabulce.

=ZÍSKATKONTDATA(datové_pole; kontingenční_tabulka; [pole1; položka1]; …)

Datové_pole = název datového pole, ze kterého mají být data extrahována.

Kontingenční_tabulka = odkaz na buňku nebo oblast buněk v kontingenční tabulce obsahující data, která chceme načíst.

Pole1, Pole2, … = pole, na které chceme odkazovat.

Položka1, Položka2, … = položka pole, na kterou chceme odkazovat.

Příklad - Filtr HR

Zdrojový soubor
Filtr_HR_zdroj

Úkol

Zjistit počet vykázaných hodin za měsíc leden na projektu CliView2.

Ze seznamu dat nás zajímá Projekt (=NAZEV_JEDNOTKY), Lidé (=JMENO) a Artefakt, na který se vykazovalo (=ARTEFAKT_KOD).

Postup

List import – doplnit vzorce:

Rok-měsíc: funkce TEXT, datum ve formátu 2010-01
Projekt: pomocí funkce KDYŽ a buňky na listu odkaz
Lidé?: pomocí funkce JE.TEXT a SVYHLEDAT zjistíme, zda se jméno vyskytuje v tab. lide
Artefakty?: pomocí funkce JE.TEXT a SVYHLEDAT zjistíme, zda se artefakt vyskytuje v tabulce artefakty
Lidé nebo artefakty: funkce NEBO

List Pivot export

Vytvořit kontingenční tabulku čerpající z listu import
Řádková pole: JMENO, Projekt, OSOBA, ARTEFAKT_KOD
Datové položky: CAS_SUMA (součet)
Stránková pole: Rok-měsíc (2010-01), Lide nebo artefakty (PRAVDA), Artefakty (PRAVDA)

List export

Čerpá z vytvořené kontingenční tabulky
Spotřebovaná kapacita [MH]: pomocí funkce ZÍSKATKONTDATA

Příklad - Management Evidence

Zdrojový soubor
Management_Evidence_zdroj

Úkol

Získat přehled čerpaných MDs a Costs, vytisknout výkazy práce pro externisty.

Postup

List IMPORT

Vložit hodnoty ze souboru Filtr_HR z listu export do souboru Management_Evidence na list IMPORT za současná data.
Pomocí vzorců vyplnit sloupce G, H, K, L.
- Nejprve vyplňte sazbu na listu HR (sloupec G – interní hledat podle role v pojmenované oblasti Internal_position, externí podle jména v objednávkách).
- Sloupec H bude čerpat z tabulky na listu HR.
- Sloupce K a L budou čerpat z již vyplněné sazby na listu HR (u ceny bez DPH ji dělit 1,2), získanou sazbu vynásobit počtem MDs.
U externistů ručně doplňte číslo objednávky podle evidence objednávek na listu Objednávky.
U externistů ručně doplnit číslo faktury podle evidence faktur na listu Faktury.
Nakopírujte ostatní již vyplněné vzorce.

List BudgetHR

Sloupce I, L – P čerpají ze seznamu lidských zdrojů na listu HR.
Cenu celkem vč. DPH vypočteme násobením Ceny/MD vč. DPH počtem MD Budget.
Pokud se jedná o internistu, vyplní se ve sloupci K „IPC“. Pokud jde externistu a PD fázi, vyplní se „OPEX“, v případě SD a IMP fáze se vyplní „CAPEX“.

List Výkaz práce

Vytvořit kontingenční tabulku čerpající z listu IMPORT, umístit do buňky A10.
Řádková pole: Projekt
Datové položky: [MH], [MD], Cena bez DPH [Kč], Cena s DPH [Kč]
- Nastavit součet, přepsat název a změnit formát čísla (2 des. místa, oddělovat tisíce).
Stránková pole: Jméno, Objednávka ID, Měsíc/Rok
Přidat další náležitosti výkazu práce:
- Jméno/Období
- Objednavatel (Banka, a.s.)
- Dodavatel – pomocí funkce SVYHLEDAT v Objednávkách
- Cena / MD bez DPH (Kč) – pomocí funkce SVYHLEDAT v Objednávkách
- Cena / MD s DPH (Kč) – pomocí funkce SVYHLEDAT v Objednávkách
- Přepsat Jméno na Jméno pracovníka, Objednávka ID na Objednávka, Měsíc/Rok na Období.
- Vedle období pomocí vzorců vypočítat interval období (tj. v lednu 1.1.2010 – 31.1.2010).
- Dolů na stránku doplnit místo pro podpisy.
Upravit formát:
- Formát datových položek: 2 des. místa, oddělovat tisíce
- Hodnoty: bílé písmo
- Vložit Textové pole „Přehled odpracovaných hodin“
- Upravit ohraničení a stínování.
Nastavit záhlaví a zápatí:
- Záhlaví: levý oddíl = obrázek, pravý oddíl = „Výkaz práce“, Soubor, Datum Čas
- Zápatí: levý oddíl = Banka, a.s. se sídlem: Praha, Hlavní 1, PSČ 110 00, IČ: 12345678, pravý oddíl = Stránka/Stránek

Úkol navíc:

Potřebujete vytvořit přehled čerpaných MDs za daný měsíc ve struktuře tabulky na listu Přehled MDs. Tento přehled posíláte šéfovi každý měsíc, chcete jej tedy zautomatizovat a využít data z předchozích tabulek.
Připravte nejprve kontingenční tabulku na list MDs, ze které se budou čerpat data (Měsíc/Rok, Projekt, Jméno, [MD]) do tabulky na listu Přehled MDs. Ostatní údaje (Firma, Cena/MD s DPH, INT/EXT) budou převzaty z listu HR. Kontingenční tabulka bude čerpat z listu IMPORT. Zajistěte, aby se Cena/MD zobrazovala pouze u externistů.
Ověřte, že vše funguje, na období 2009-06.

Nahoru