Dziś znów coś dla HR-owców. Z problemem, który opiszę przyszła do mnie moja własna siostra. Potrzebowała ona bowiem stworzyć wykres zatrudnienia pracowników w konkretnych miesiącach wybranego roku. Problem jednak polegał na dostępnych danych – znała jedynie datę zatrudnienia i, ewentualnie, zwolnienia. I dopiero z tych danych mogła cokolwiek dalej kombinować.
Ten wpis jest szczególny, ponieważ nagrałam do niego szczególny film. Postanowiłam, że w tym filmie po raz pierwszy w historii Malinowego Excela – pokażę siebie. Uznałam, że przyjemniej będzie Wam się oglądało film, jak zobaczycie KTO do Was mówi, a nie tylko usłyszycie mój głos. Jestem ciekawa Waszych wrażeń 🙂
A wracając do wykresu zatrudnienia, to dostępne dane wyglądają tak:
Dane wejściowe
A potrzebujemy tego:
Wynik: dane + wykres
Zadanie do najprostszych nie należy, natomiast spokojnie sobie z nim poradzimy. Zrobimy to w 2 etapach:
1. Przygotujemy dane do wykresu (tutaj będzie formuła)
2. Stworzymy wykres
Natomiast wszystko zacznijmy od omówienia logiki całego problemu.
Logika problemu
Do dyspozycji mamy tylko datę zatrudnienia i, ewentualnie, datę zwolnienia. Jeśli nie ma daty zwolnienia, to znaczy, że pracownik jeszcze pracuje (i na to trzeba będzie się zabezpieczyć w formule, ech!). Do wykresu potrzebujemy tabelkę taką, jak na poprzednim rysunku, czyli z podziałem na miesiące. Formuła, którą umieścimy w tej tabelce, musi więc umieć określić, czy dany pracownik był zatrudniony w konkretnym miesiącu roku, czy nie.
A kiedy pracownik jest zatrudniony? Kiedy nie został jeszcze zwolniony lub kiedy został, ale po dacie, która nas interesuje. Weźmy przykład:
Załóżmy, że rozpatrujemy marzec 2017. Jeśli został zatrudniony przed tą datą (czyli data zatrudnienia jest mniejsza lub równa 1 marca 2017) i został zwolniony po tej dacie (data zatrudnienia jest większa niż 1 marca 2017), to znaczy, że pracuje. To w sytuacji, gdy został zwolniony. Gdy nie został – ma zastosowanie tylko pierwszy z tych warunków.
Idealnie pokazuje to poniższy rysunek:
Logika formuły
Czyli naszym zadaniem jest policzenie ile pracowników spełnia te warunki, a konkretnie ile dat zatrudnienia i zwolnienia je spełnia. Ponieważ mamy więcej niż jeden warunek, użyjemy funkcji LICZ.WARUNKI. Użyjemy jej dwukrotnie, ponieważ musimy rozpatrzeć sytuację, w której pracownik nie ma daty zwolnienia, jak i taką, w której tę datę ma.
Przygotowanie danych do wykresu – formuła
Dla ułatwienia sobie życia proponuję wpisać do komórki F1 rok, który ma być prezentowany na wykresie (będzie można go tutaj zmieniać), a do komórki F2 – dzisiejszą datę (możesz wpisać dowolną inną – jest to data do której dokonujesz analizy zatrudnienia). Ja obie te komórki nazwałam odpowiednio: Rok i Czas, natomiast nie jest to konieczne, możesz zostawić ich adres, natomiast pamiętaj wtedy o ich blokowaniu.
Teraz już samo uzupełnienie tabelki formułą. Tak, jak wspomniałam wyżej – będą to 2 funkcje LICZ.WARUNKI. Zakres z danymi wejściowymi u mnie jest w zakresie A2:C12.
Oto formuła, którą należy wpisać do komórki F4
=JEŻELI(Czas>DATA(Rok;E4;1);LICZ.WARUNKI($B$3:$B$12;"<="&DATA(Rok;E4+1;0);$C$3:$C$12;">"&DATA(Rok;E4;1))+LICZ.WARUNKI($B$3:$B$12;"<="&DATA(Rok;E4+1;0);$C$3:$C$12;"");0)
Wyświetli ona zero dla przyszłych miesięcy. Uznałam, że nie wiemy jaki będzie wtedy stan zatrudnienia. Możemy dokonywać prognoz, ale to inna bajka, na inne formuły 🙂
Stworzenie wykresu
Na koniec samo stworzenie wykresu. To jest najprostsze. Wystarczy ustawić się w danych i użyć skrótu klawiszowego Alt + F1. Utworzymy w ten sposób domyślny wykres osadzony w arkuszu, w którym jesteśmy. Potem to już tylko (albo aż!) kosmetyka i dostosowywanie wykresu do naszych (albo raczej szefa!) potrzeb. Ja stworzyłam najprostszy wykres, widoczny na rysunku poniżej:
Wykres liczby zatrudnienia w miesiącu
I wszystko! Można dorzucić kilka bajerków, np. listę rozwijaną w komórce F1, żeby wybierać rok. W najprostszej wersji można jako źródło listy wpisać ręcznie wartości lat. W innej – można pisać formułę tablicową, która te wartości pobierze sama, ale z kolei będzie ograniczona liczba lat do wyboru (formułę tę trzeba rozpisać na konkretną wysokość). Kolejną opcją jest też Power Query, które pomoże zasilić listę, natomiast ma tę wadę, że trzeba będzie to odświeżać. Każde rozwiązanie jest na swój sposób ciekawe i każde ma swoje wady i zalety. Ale to już dodatek 🙂
Mam nadzieję, że zagadnienie opisane w tym poście Ci się przyda. A jak nie dokładnie to, to może chociaż schemat działania. Mogą to być np. wykres cyklu życia produktów. Też się nadaje 🙂
Mam prośbę: jeśli znasz kogoś, komu może przydać się opisane zagadnienie – przekaż jej/mu proszę wiadomość o tym wpisie. Możesz jej wysłać mailem np. linka do tego wpisu lub linka do filmu na YB. Pomożesz tym zarówno jej, jak i mnie 🙂
A co do filmu – oto on:
I plik z gotowym rozwiązaniem:
Malinowy Excel Wykres zatrudnienia dw.xlsx
Dzień dobry, bardzo lubię pracować z excelem a dzięki Pani trikom i podpowiedziom nasza współpraca układa się znakomicie ☺️
Jednak mam problem dot. przedstawiania danych na wykresie. Tzn. mam dość dużą liczbę danych do pokazania z czego wiele z nich ma wartość 0. I tu zaczynają się schody ponieważ wykres ze wszystkimi danymi jest trochę nieczytelny i bardzo dlugi, nie mam potrzeby żeby wyświetlać zerowe pozycję na wykresie ale nie chcę tego robić przez sortowanie danych i ich ustawianie ręcznie. Czy istnieje jakies sprytne rozwiązanie sprawiające że Excel sam będzie wiedzial, że ma nie wyświetlać na wykresie zerowych rekordów? Póki co niestety ustawiam to ręcznie a wykres zmienia się w trakcie kwartałów wiec zastanawiam się czy nie dałoby się tego jakoś zautomatyzować?
Magda, cieszę się, że dogadujecie się z Excelem i mam w tym swój skromny udział :).
Odnośnie pytania o wykres – czy chodzi o to, żeby zerwowe wartości w ogóle nie były wyświetlane na wykresie, czy żeby np. wykres liniowy nie schodził gwałtownie do osi x, gdy wskazuje wartość zerową? I czy jeśli pozbędziesz się tych zerowych wartości to już ilośc danych będzie akceptowalna? Bo przychodzi mi kilka pomysłów do głowy i nie wiem co zaproponować 🙂
Dziękuję za odpowiedź Chodzi mi o wykres słupkowy, w którym nie będą widoczne kolumny z wartością zero. Mam dosyć dużo takich danych, których zakres na wykresie jest co kwartał praktycznie taki sam jednak istotnie jest to, żeby pokazać tylko te pozycje które mają wartość większa od zera i co kwartał są to inne pozycje z zakresu. Nie chciałabym ustawiać tego ręcznie jak do tej pory bo formatka excela będzie używana przez kolejne lata do porównywania danych rok do roku i fajnie by było gdyby taka weryfikacja danych robiła się automatycznie
Hej, to może spróbuj umieścić dane w obiekcie tabela, oprzyj na tym wykres i po prostu filtruj tabelę (odfiltruj zera, nie potrzebne daty itp). Myślę, że powinno załatwić sprawę 🙂
Dzień dobry,
Pani artykuł bardzo mi pomógł i liczenie działa. Ale musze dodatkowo policzyć ilośc umów w poszczególnych spółkach w danym miesiącu, czyli dodać kolejny warunek do tej formuły i szczerze mówiąć jestem w kropce bo cały czas mi liczy tę samą wartośc na wszystkei miesiąca .
ColumnA = nazwa Spółki
Czy dobrze rozumiem, że docelowo chcemy mieć: ile osób zostało zatrudnionych w konkretnym miesiącu w konkretnej spółce?
Jeśli tak, to ja już bym nie kombinowała z formułą, tylko stworzyła tabelę przestawną i zrobiła wykres przestawny na tej podstawie. Oczywiście uwzględniając pole z nazwą spółki 🙂
Tak, ma byc info ile osób w danej spolce w danym miesiącu
ale nie moge tabeli przestawnej użyć ponieważ mam podane :
Colmn: Start Umowy Colmn: koniec umowy
Tzn: niemam skąd pobrac danych do przestawnej o tym ile bylo umów w danym miesiącu zeby z tego wyciągnąć poszczegolne spolki
Ok, a gdzie masz informację o spółce? Bo do takiego zadania potrzebujemy: data-od, pracownik (możemy go liczyć), spółka. O tak:
O to chodziło?
Wysłałam do Pani @ z załącznikiem – żeby mogła Pani zobaczyć jak wygląda źródło danych
Bo ja naprawdę już nie wiem jak to rozwiązać :/
Odpisałam 🙂