Upał za oknami, środek okresu urlopowego. Dzisiejszy artykuł będzie więc “na czasie”, ponieważ pokażę Wam, w jaki sposób sprawdzić ile pracowników pracuje w danym dniu i czy jest to wystarczająca ich liczba. Odpowiedź na to pytanie chciał poznać jeden z czytelników mojego bloga.
Z pewnością prezentowany tutaj przeze mnie sposób przyda się tym z was, którzy pracują w dziale HR lub przykładowo w dziale sprzedaży czy dziale obsługi klienta.
Załóżmy, że w dziale sprzedaży pracuje 15 osób. Chcę, aby codziennie pracowało minimum 60% zespołu, czyli 9 osób. Jeśli ten warunek nie jest spełniony – Excel ma mnie o tym poinformować (napisze mi, że są braki i obramuje komórkę na czerwono).
Efekt wygląda tak:
Teraz pokażę Wam jak to zrobić!
Oto formatka:
Najpierw zajmiemy się obliczeniem ile osób liczy nasz dział. Wpisz do komórki D1 następującą formułę:
=ILE.NIEPUSTYCH(B9:B23)
Teraz policzmy ile osób stanowi 60% działu. Wpisz następującą formułę do komórki D3:
=ZAOKR.GÓRA(D1*D2;0)
Ok. Kolejny krok to formuła właściwa: ustalenie czy dany pracownik pracuje danego dnia, czy jest na urlopie. Aby szybko wstawić tę formułę dla wszystkich pracowników, dla wszystkich dni wykonaj następujące kroki:
1. Zaznacz zakres F9:R23.
2. Do komórki F9 wpisz taką formułę:
=JEŻELI(ORAZ(F$8>=$C9;F$8<=$C9+$D9-1);"Urlop";"P")
(jeszcze nie zatwierdzaj!).
3. Zatwierdź ją używając kombinacji klawiszy Ctrl + Enter. Wpiszesz dzięki niej formułę do zaznaczonego zakresu, zachowując formatowanie (więcej o tym przeczytasz tutaj: Szybkie wprowadzanie formuł z zachowaniem formatowania).
Efekt jest taki:
Następnie policzymy ile osób pracuje danego dnia. W tym celu, do komórki F6 wpisz:
=LICZ.JEŻELI(F9:F23;"P")
W efekcie otrzymamy:
Ustalmy teraz jaki to procent zespołu. Posłuży do tego formuła, którą należy wpisać do komórki F7:
=F6/$D$1
I na koniec: informacja o tym, czy brakuje pracowników, czy jest ok. I formuła do komórki F5:
=JEŻELI(F7<$D$2;"braki";"ok")
Teraz już tylko upiększanie, czyli formatowanie warunkowe: jak zrobić te czerwone ramki, które są widoczne na rysunkach powyżej 🙂 Za każdym razem, kiedy danego dnia jest zbyt mało pracowników (mniej niż 9 w naszym przykładzie), Excel ma zaznaczyć czerwoną ramkę wokół liczby pracowników danego dnia. Aby ją zrobić należy:
1. Zaznaczyć zakres F6:R6.
2. Z menu Narzędzia główne/ Formatowanie warunkowe wybrać Nowa reguła, zanzaczyć opcję Formatuj tylko komórki zawierające.
3. A jako regułę wpisać: Wartość komórki/ mniejsza niż/ =$D$3 (to, co na obrazku poniżej):
4. Aby wybrać formatowanie, w okienku pokazanym na rysunku wyżej, naciśnij guzik Formatuj… i wybierz czerwoną ramkę (obrazek poniżej):
Na koniec mamy taki efekt:
Na obrazkach pokazuję tylko część danych. W załączonym do tego artykułu pliku jest miejsce na więcej dni. Choć i tak jestem przekonana, że tobie będzie potrzebne jeszcze więcej 🙂
A plik możesz pobrać tutaj:
MalinowyExcel_Ile-osob-na-urlopie-danego-dnia new.xlsx
Znasz kogoś, komu przyda się zaprezentowana powyżej wiedza? Wyślij tej osobie maila z linkiem do tego artykułu. Na pewno chętnie z niego skorzysta 🙂
A co w przypadku gdy jeden pracownik ma urlop np. od 6 sierpnia 2 dni oraz od 20 sierpnia 5 dni
Trzeba byłoby przebudować formuły, myślę, że najprościej dorzucić kolejny wiersz z pracownikiem. Ale zdecydowanie formuły do modyfikacji.
bardzo proszę o podanie modyfikacji formuły w takim właśnieprzypadku
Jak na to spojrzałam teraz, to można to zrobić tą samą formułą, natomiast ręcznie zaznaczyć urlop na formatce. Wtedy można wpisywać nawet kilka krótszych urlopów u jednego pracownika. Dodajemy sobie w ten sposób pracy z zaznaczaniem, natomiast zyskujemy elastyczność i nie trzeba modyfikować formuł 🙂