Praktické tipy pro práci s Excelem v HR

Jako pomocí Excelu ušetřit stovky hodin práce. Praktické ukázky z HR praxe a jak vypočítat některé HR ukazatele.

Moje skvělá kolegyně Dáša potřebuje pomoct s Excelem. Jako pro většinu lidí v HR to není úplně její hobby, nicméně správně cítí, že pokud se zlepší v Excelu, zbude jí více času na zajímavější věci. Když už pro ní ten souhrn dělám, řekl jsem si, že ho dám na blog. Třeba pomůže ještě někomu.

HR a Excel

Práce v HR vyžaduje poměrně dost práce s daty. Ať už jsou to různé seznamy zaměstnanců, reportování vývoje headcountu, sledování KPI náborového procesu nebo třeba evidence nákladů na vzdělávání. Excelu se skoro nikdo nevyhne… ani v HR.

Záludností Excelu je, že to člověk ve finále vždycky nějak dá, ale pokud by uměl některé šikovné funkce, práce v Excelu by mu zabrala zlomek času, a mohl by se věnovat lepším věcem.

Rád bych vám ukázal na konkrétních příkladech z HR, jak Excel efektivně používat.

Práce s textem (jména, emaily, názvy oddělení)

Funkce: LEFT, RIGHT, LEN, FIND, CONCATENATE

Často pracujeme se seznamy zaměstnanců: jména, adresy, emaily, organizační zařazení… Texty potřebujeme slučovat, ořezávat nebo nahrazovat.

Př: V jednom sloupci jsou jména a v druhém příjmení. Chceme vytvořit nový sloupec, kde bude jméno a příjmení dohromady. Funkce CONCATENATE spojuje různé texty. Může spojovat obsah, který se načte z buněk, a nebo texty, které do funkce zadáte natvrdo (pak je třeba je dát do uvozovek).

=CONCATENATE(„Jana“; „Fialova“) vytvoří toto: JanaFialova. Pokud tedy spojuji jména, nesmím zapomenout vložit mezeru, jak ukazuje obrázek dole.

Názvy některých funkcí se v anglické a české verzi Excelu liší. Proto jsem vytvořil tahák, kde jsou nejen všechny příklady z tohoto článku, ale i překlady nejběžnějších funkcí. Ke stažení zde.

Př: Máte v buňkách texty a potřebujete vypreparovat pouze jejich část.
Funkce LEFT a RIGHT useknou z textu počet znaků, který zadáte, a to buď z levého konce (LEFT) a nebo pravého konce (RIGHT).

=LEFT(A1;3) říká: „vem text v buňce A1 a usekni tři znaky zleva“

HR Excel Concatenate

Ve sloupci F vidíte, že pokud potřebuji vytvořit e-mailové adresy všech zaměstnanců (za předpokladu, že mají stejný formát e-mailu), tak spojím jméno+tečka+příjmení+doména.

Místo CONCATENATE můžete použít znak „&“. Pak je zápis =A3&“.“&B3&“@firma.cz“

Př: V buňkách mám kód oddělení a jeho název. Chci to očistit, a mít jenom název oddělení. LEFT a RIGHT nestačí, protože názvy jsou různě dlouhé, takže nemůžu říct „usekni 10 znaků zprava“.
Musím zapojit víc funkcí:
LEN(A21) – řekni mi, kolik znaků má text v buňce A21
FIND(„-„; A21) – řekni mi, na kolikáté pozici je znak „-„, tj. pomlčka.
Když je odečtu, vím kolik znaků je za pomlčkou a tolik useknu zprava RIGHT(A21; LEN(A21)-FIND(„-„; A21))

HR Excel LEFT RIGHT LEN FIND

Práce s daty: délka náboru, demografie zaměstnanců…

Ice Cube : Today, Is A Good Day - by Anonymous

funkce: DATEDIF, TODAY, DATE, EMONTH

Potřebujeme vypočítat senioritu zaměstnanců, věk zaměstnanců, čas na obsazení pozice (time to fill), nebo udělat graf vývoje počtu zaměstnanců po měsících nebo rocích. Tam všude potřebujete datumové funkce.

Práce s daty v Excelu je celkem jednoduchá, protože data se chovají jako čísla, která lze od sebe odečítat nebo přičítat.

Př: Máte data narození a potřebujete zjistit věk zaměstnanců.
Funkce TODAY() vám vrátí aktuální datum. Pokud odečtete dvě data od sebe, dostanete rozdíl ve dnech. TODAY()-Datum narození = věk zaměstnance. Pokud chcete rozdíl v letech, je třeba výsledek vydělit 365.

HR Excel Today

Místo odečítání dat můžete použít funkce DATEDIF(Datum1; Datum2; d/m/y). Vložíte jedno datum, druhé datum, a pak zvolíte, jestli chcete vidět rozdíl ve dnech, měsících nebo rocích (možností je víc). Nápověda k funkci zde.

Pokud potřebujete zobrazit demografickou strukturu firmy, bude se hodit funkce FLOOR. V té určíte, na jaký násobek se má číslo zaokrouhlit směrem dolů. Klasicky se používají násobky pěti.

FLOOR(D2; 5) nám z čísla 38 udělá 35. Pokud ale chci mít ve sloupci E nejenom zaokrouhlené číslo, ale interval, musím si pomoci takto: 35 & pomlčka & 35+4. Dobře to vidíte na příkladu.

Posledním krokem je udělat pivot graf, který spočítá, kolikrát se jaký interval vyskytuje a zobrazí to jako procento z celku.

Máte seznam s daty nástupu zaměstnanců, a v novém sloupci chcete mít pouze měsíc nástupu, abyste data mohli dále zpracovávat a filtrovat. Funkce MONTH nebo WEEKNUM určí měsíc či číslo týdne určitého data.

Př: Odečítání dat je dobré, když máte seznam nabíraných pozic, u každé datum otevření a datum zavření a chcete sledovat, jak dlouho trval nábor. Pro lepší vizualizaci si můžete udělat Ganttův graf. Ten v základní nabídce Excelových grafů není, ale je celkem lehké si jej vytvořit. Koukněte se do přiloženého taháku.

HR Excel COUNTIFS

Věděli jste, že medián počtu dní na obsazení volné pozice je 30? A že průměr je 34? A víte, jaký je rozdíl mezi průměrem a mediánem? Medián je střední hodnota vzorku a je méně citlivá na extrémní hodnoty než aritmetický průměr.

Př: Máte seznam všech pozic, které se nabíraly za poslední dobu. U každé pozice máte datum otevření a datum zavření. Chcete mít udělátko, které když zadáte libovolné datum, tak vám řekne, kolik k danému datu bylo otevřených pozic.
Použijeme funkci COUNTIFS. Ta počítá, kolik řádků splňuje určitou podmínku. Tedy: spočítej, kolik pozic má datum otevření menší než datum A a zároveň datum uzavření větší než datum A.

Tohle je dobré používat, pokud potřebujete udělat tabulku vývoje počtu zaměstnanců v určitém období. Potřebujete seznam všech zaměstnanců (i těch, co už odešli) a u každého mít datum nástupu a datum odchodu. Pak je to otázka chvilky. Místo tabulky si můžete udělat graf.

Př6: Někdy se může hodit funkce DATE(rok; měsíců den), která složí datum z údajů o roce, měsíci a dni.

HR Excel DATE

Turnover rate: Tento ukazatel se vždy vztahuje k určitému období (měsíc, rok…) a má být počítán jako počet odchodů v daném období děleno průměrný headcount v tomto období. Pokud má firma stabilní headcount, stačí vzít počet odchodů v roce a dělit jej počtem lidí na konci roku. Pokud ale headcount v průběhu roku fluktuuje (masivní nábor nebo propouštění), je třeba opravdu spočítat průměr za celý rok. Opět záleží na situaci, někdy stačí vzít headcount na začátku roku, na konci roku, sečíst a vydělit dvěma.

Věděli jste, že průměrní míra roční fluktuace v ČR je 16,8 % ?

Propojování dvou seznamů

Funkce: VLOOKUP, HLOOKUP

Př7: Propojování dvou seznamů. Kdo tohle v HR někdy nedělal. Máte dvě tabulky: v jedné je seznam zaměstnanců, kteří absolvovali nějaké školení. K nim potřebujete dotáhnout další údaje, které máte v seznamu všech zaměstnanců.

Použiji ID zaměstnance, abych pomocí funkce VLOOKUP obě tabulky propojil.

ExcelVlookup

Můžete to využít i obráceně, když chcete zjistit, kdo ze zaměstnanců na školení nebyl. Když totiž VLOOKUP nic nenajde, vrátí chybový znak. Pokud tam nechcete mít ošklivý chybový znak, použijete funkce IFERROR. Tím řeknete: pokud je v buňce chybová hláška, napiš něco, například „Neproškolen“.

ExcelVlookup2

Pokud propojujete dvě tabulky, rozhodně doporučuji je propojovat přes osobní číslo zaměstnance. Pokud je propojujete přes jméno, tak někdy může být víc zaměstnanců se stejným jménem, nebo může být ve jménu překlep a nebo jednou je to s háčky a podruhé bez.

Př: Funkce VLOOKUP se perfektně hodí pro tvorbu různých číselníků: Mám seznam zaměstnanců, každý zaměstnanec spadá pod nějaké oddělení (sloupec I). Centrála si ale vymyslela nový report, kde chce mít oddělení sloučená na „Support“ a „Direct“. Proto si udělám číselník (sloupce A a B) a přes funkci VLOOKUP si nové kategorie načtu do sloupce J.

Statistické funkce (MIN, MAX, MEDIAN, AVERAGE)

Tyto funkce spočítají nejmenší hodnotu, nejvyšší hodnotu, medián a průměr ve vybraných buňkách. Pokud to potřebujete rychle zjistit, nemusíte hned psát vzoreček, dole na liště v Excelu se vám tyto funkce objevují. Při pravém kliknutí si můžete vybrat, co konkrétně chcete vidět.

Potřebujete nastavit nárůst mzdy na základě kombinace hodnocení a tarifní třídy

Přiřazovací funkce (INDEX, MATCH)

Používám kombinaci těchto funkcí, pokud potřebuji k zaměstnanci přiřadit hodnotu z tabulky. Klasicky mám seznam zaměstnanců, každý má tarifní třídu a udělené hodnocení (tabulka vlevo). V jiné tabulce mám procenta nárůstu mzdy (tabulka vpravo). Potřebuji na základě těchto dvou parametrů přiřadit správné procento nárůstu.

Funkce INDEX funguje tak, že z tabulky vybere buňku podle souřadnic řádku a sloupce. Například INDEX(K5:O9; 1; 2) říká: „mrkni se do tabulky v buňkách K5 až O9 a vyber buňku v řádku 1 a sloupci 2.“ Jedná se o růžově označenou buňku L5.

Jak ale vím, na kterém řádku a ve kterém sloupci se buňka nachází? Proto si musím pomoct funkcí MATCH. Funguje takto: MATCH(C5; K4:O4) – koukni se do buňky C5, tam je hodnocení „B“ a koukni se mi do tabulky K4:O4 a řekni mi, na kolikátém místě se daná hodnota nachází. V tomto případě to je na druhé pozici. Tímto způsobem jsem podle kombinace tarifní třídy a hodnocení schopen určit souřadnice a z tabulky vpravo vzít požadované procento a doplnit ho do sloupce G.

Co se ještě naučit?

To byly pouze základní funkce. Možností, jak si ušetřit práci je daleko více.  Další level jsou reporty. V každé práci, kde jsem byl, se mi podařilo snížit počty dní strávených přípravou reportů na hodiny, či desítky minut. Stáhnout data ze systému, prohnat Excelem, vyplivnout reporty.

Dobré je umět dynamické reporty, kdy můžete měnit různé parametry a zázrak… reporty se sami přepočítávají a sloupcové grafy se hýbou. Na prezentaci perfektní.

Kapitolou sama pro sebe je kontingenční tabulka – ta umí ušetřit opravdu hodiny a týdny práce.

No a level nejvyšší jsou makra a programování ve VBA, ale to už chce opravdu více studia. Tím není třeba se trápit, to využijí ti, kteří dělají HR controlling, Workforce planning a C&B.

Všechny příklady z článku jsem dal do excelového taháku ke stažení.

European Computer Driving Licence (ECDL)

Pokud se chcete Excel doučit, doporučuji certifikát ECDL. Je to standard pro práci s programy MS Office. Každý program má úroveň Core a Advanced. Myslím, že Basic by měl umět každý. Advanced je už pro ty, kteří se pohybují na pomezí financí a HR, jako je například odměňování.

ECDL má dobře zpracované sylaby, kde je přesně popsáno, jaké funkce je třeba pro danou úroveň ovládat. Testování ECDL dělají akreditované počítačové školy.