Excel jest świetny w obliczaniu czasu pracy i wynagrodzeń pracowników. Niedawno napisałem o formule, która pozwala w prosty sposób obliczyć dniówkę dla różnych stawek godzinowych. Obliczanie dniówki można jednak skomplikować. Wystarczy, że podejdziemy do tematu bardzo skrupulatnie, licząc czas pracy z dokładnością co do minuty.
Pełne godziny robocze
Niedawno napisałem artykuł o tym, jak wyliczyć wynagrodzenie pracownika przy różnych stawkach godzinowych.
Sporym ułatwieniem było założenie, że czas pracy liczymy w pełnych godzinach. Rozpoczęcie pracy i fajrant podawaliśmy więc w zaokrągleniu do pełnych godzin – co bardzo uprościło temat.
Całe zadanie udało się wykonać za pomocą prostej formuły, bez użycia VBA.
Dokładność co do minuty
Co jednak w sytuacji gdy pracownik rozpoczął swoją pracę o 7:40, a zakończył ją o 16:25?
Z punktu widzenia Excela, wprowadzenie takiego założenia mocno utrudnia zbudowanie odpowiedniej formuły.
Postanowiłem więc zmierzyć się z tym wyzwaniem pisząc własną funkcję VBA.
Założenia dla funkcji
Podsumujmy więc, co jest kluczowe z punktu widzenia działania funkcji:
- Czas pracy pracownika. Stosujemy podejście skrupulatne – z dokładnością co do minuty.
- Zmienne stawki. Każda zmiana posiada inną stawkę roboczogodzinową.
- Trzecia zmiana. Rozpoczęcie pracy (np. 22:00) może być wartością większą od końca pracy (06:00).
Budowa funkcji
Funkcja VBA posiada trzy argumenty.
- Start_pracy. Czas rozpoczęcia pracy.
- Koniec_pracy. Czas zakończenia pracy.
- Tabela_stawek. Godzinowa tabela pokazująca wysokość stawki za pracę w określonym przedziale czasu.
Przykłady praktyczne
Poniższy obrazek przedstawia 7 przykładów praktycznych. Wszystkie (poza ostatnią) zostały omówione na filmie.
Kod VBA funkcji
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
' Funkcja oblicza dzienne wynagrodzenie pracownika z dokładnością ' co do minuty przy różnych stawkach godzinowych. ' Funkcja zwraca prawidłowy wynik jeśli koniec pracy < początku pracy. Function DNIOWKA(Start_pracy As Double, Koniec_pracy As Double, Tabela_stawek As Range) As Double Dim bCzyStartWiekszy As Boolean ' Informacja czy start > koniec Dim dblWyplata As Double ' Kwota do wypłaty za całą dniówkę Dim dblMinuty As Double ' Każda minuta w dobie licząc od północy Dim dblStawkaGodzinowa As Double ' Stawka godzinowa dla konkretnej minuty Dim iLicznik As Integer ' Licznik pętli '24h = 1 -> 1min = 0.000694444444444, 1 doba = 1440 min Const dblJEDNA_MINUTA As Double = 0.000694444444444 'Sprawdź czy koniec pracy nie jest wartością większą od początku pracy. 'Może tak być w przypadku np. trzeciej zmiany od 22:00 do 6:00 1 If Start_pracy > Koniec_pracy Then bCzyStartWiekszy = True 'Domyślnie koniec pracy > start 2 If bCzyStartWiekszy = False Then 'Pętla po wszystkich minutach w dobie (1 doba = 1440 minut) 3 For iLicznik = 1 To 1440 'Określ o jaką minutę chodzi 4 dblMinuty = dblJEDNA_MINUTA * iLicznik 'Sprawdź czy minuta mieści się w obszarze roboczogodzinnym 5 If CBool((dblMinuty >= Start_pracy) And (dblMinuty <= Koniec_pracy)) Then 'Odczytaj stawkę godzinową 6 dblStawkaGodzinowa = WorksheetFunction.VLookup(dblMinuty, Tabela_stawek, 3, True) 'Dodaj stawkę minutową do wyniku 7 dblWyplata = dblWyplata + (dblStawkaGodzinowa / 60) 8 End If 9 Next iLicznik 10 Else 'Pętla po wszystkich minutach w dobie (1 doba = 1440 minut) 11 For iLicznik = 1 To 1440 'Określ o jaką minutę chodzi 12 dblMinuty = dblJEDNA_MINUTA * iLicznik 'Sprawdź czy minuta mieści się w obszarze roboczogodzinnym 13 If CBool((dblMinuty >= Start_pracy) Or (dblMinuty <= Koniec_pracy)) Then 'Odczytaj stawkę godzinową 14 dblStawkaGodzinowa = WorksheetFunction.VLookup(dblMinuty, Tabela_stawek, 3, True) 'Dodaj stawkę minutową do wyniku 15 dblWyplata = dblWyplata + (dblStawkaGodzinowa / 60) 16 End If 17 Next iLicznik 18 End If 'Przypisz funkcji wynik 19 DNIOWKA = dblWyplata End Function |
Film na Vlogu
Koniecznie obejrzyj film, aby zobaczyć obliczanie dniówki w praktyce.
Plik XLSM
