Oblicz godziny pracy w Excel: Nocne zmiany, przerwy i sztuczna inteligencja
Zamień 3000 rekordów obecności w raport godzin Excel: obsługuj nocne zmiany, oznaczaj niepełne rejestracje czasu i twórz sumy pracowników, które się przeliczają.
Rzeczywiste zrzuty lokalnego pokazu dewelopera z danymi symulowanymi, a nie danymi klientów. Zachowano oryginalny interfejs. Krótkie filmy zawierają przyspieszone fragmenty przetwarzania.
Odejmowanie rozpoczęcia pracy od zakończenia pracy wydaje się łatwe, dopóki zmiana nie przekroczy północy, przerwa nie zostanie zarejestrowana w minutach, a brakujący cios nie zmieni się w mylącą liczbę.
Dla zmian krótszych niż 24 godziny, zapisanych samymi godzinami, oblicz czas pracy także przy przejściu przez północ i odejmij przerwę. Niepełne rejestracje wejścia lub wyjścia oznacz osobno. Następnie sumuj według identyfikatora pracownika. Demonstracja GetSheetAI wykorzystuje 3000 symulowanych rekordów dla 100 pracowników.
Co zawiera gotowy skoroszyt
Pierwotne pola obecności pozostały, z obliczonymi godzinami i dodaną kolumną statusu. Oddzielne podsumowanie pracowników pokazuje łączną liczbę godzin, ważne rejestry obecności i zapisy niepełnych rejestracji czasu. Przykład zawiera 80 rekordów z niepełną rejestracją czasu, które pozostają widoczne do korekty, a nie są po cichu wypełniane.
W teście czas jednej zmiany zwiększono z 7 do 8 godzin. Suma godzin danego pracownika wzrosła z 210 do 211. Następnie przywrócono wartości początkowe.
Przygotuj dane dotyczące obecności
Użyj identyfikatora pracownika, nazwiska, działu, daty pracy, czasu rozpoczęcia i zakończenia pracy oraz minut przerw. Identyfikuj pracowników na podstawie identyfikatora, a nie nazwiska. Zapisz czasy zegarowe jako wartości czasu Excel i odróżnij pustą komórkę od północy: północ jest poprawną wartością czasu równą zero.
Zdecyduj, czy jeden wiersz oznacza jeden dzień roboczy, czy jeden okres obecności. Jeśli pracownik ma kilka wierszy w jednym dniu, zliczanie wierszy nie uwzględnia odrębnych dni roboczych. Dzienne zapisy przykładu potwierdzają jego podsumowanie; inne układy wymagają innej reguły liczenia.
Prześlij wtyczce zwykłe żądanie
Otwórz arkusz w Excel, otwórz GetSheetAI i rozpocznij nową rozmowę. Możesz dostosować tę prośbę:
Oblicz godziny pracy dla każdego rekordu obecności, włączając nocne zmiany i odejmując minuty przerw. Oznacz brakujące wartości wejścia lub wyjścia, zamiast zgadywać. Zachowaj oryginalne pola. Utwórz podsumowanie pracowników zawierające liczbę przepracowanych godzin, ważne zapisy i brakujące dane, korzystając z formuł, które aktualizują się, gdy poprawiam godzinę.
Nie musisz podawać w żądaniu nazwy IF, MOD ani SUMIFS. Funkcje te są przydatne podczas sprawdzania, w jaki sposób wygenerowany skoroszyt oblicza swoje wyniki.
Omówienie obliczania godzin
Załóżmy, że C2 to wejście, D2 to wyjście, a E2 to minuty przerwy. Przykładowa angielska formuła Excel to:
=IF(OR(C2="",D2=""),"",MOD(D2-C2,1)*24-E2/60)
Najpierw przeprowadzana jest kontrola brakujących wartości. MOD obsługuje czas przekraczający północ, pomnożenie przez 24 zamienia dni na godziny, a podzielenie minut przerwy przez 60 daje tę samą jednostkę. W godzinach 22:00–06:00 z 60-minutową przerwą wynik wynosi 7 godzin. Północy nie należy mylić z brakującą rejestracją czasu.
Użyj tego wzorca tylko dla znanych zmian krótszych niż 24 godziny. Identyczne czasy zegarowe mogą oznaczać zero godzin lub cały dzień; formuła nie może o tym zadecydować za Ciebie. W przypadku zmian wielodniowych należy podać pełną datę rozpoczęcia i zakończenia. Ujemne wyniki, przerwy dłuższe niż zmiana i nieprawdopodobnie długie przerwy należy zgłosić do przeglądu.
Podsumuj według pracownika, bez ukrywania wyjątków
Zsumuj ważne godziny pracy według identyfikatora pracownika, używając osobnej kolumny stanu, aby rozróżnić zapisy ważne i brakujące. SUMIFS może sumować godziny zarówno z kryteriami pracownika, jak i statusu; Funkcja COUNTIFS umożliwia zliczanie rekordów spełniających te same warunki.
Zarejestrowana weryfikacja objęła 3000 wierszy źródłowych, 100 wierszy podsumowań pracowników, zarówno w przypadku nocy, jak i północy, wszystkie wartości źródłowe oraz 80-wierszowy filtr niepełnych rejestracji czasu. Korekty powinny obejmować łącznie dane szczegółowe i sumy godzin pracowników.
Przed ponownym wykorzystaniem własnego raportu sprawdź jedną zmianę dzienną, jedną zmianę nocną, jedną prawidłową godzinę północną i jedną niekompletną parę. Popraw jeden raz i potwierdź, że zmienia się dokładnie zamierzona liczba pracowników.
Często zadawane pytania
Czy to uwzględnia wynagrodzenie lub ustawowe nadgodziny?
W tym przypadku godziny obecności są obliczane zgodnie z określonymi zasadami. Progi nadgodzin, płatne przerwy, dodatki urlopowe, zaokrąglenia i zmiany czasu letniego wymagają odrębnych zasad. Zarejestrowany przykład nie sprawdza zgodności z listą płac.
Czy mogę dodać listę obecności w następnym miesiącu?
Sprawdź, czy formuły i zakresy podsumowań uwzględniają nowe rekordy. Tabela Excel może pomóc w rozszerzaniu danych źródłowych, ale należy zweryfikować rzeczywiste odniesienia, zamiast zakładać, że uwzględniono każdy nowy wiersz.
Co się stanie, jeśli czasy będą przechowywane jako tekst?
Najpierw znormalizuj źródło. instrukcja czyszczenia danych wyjaśnia przygotowanie; Przewodnik po formułach AI pokazuje, jak opisać obliczenia i je zweryfikować.
