Po co kupować drogie narzędzie do zarządzania frekwencją dla swojego startupu, jeśli możesz śledzić frekwencję zespołu w programie Excel? Tak! Możesz łatwo utworzyć śledzenie obecności w programie Excel. W tym artykule dowiemy się, jak to zrobić.
Krok 1: Utwórz 12 arkuszy na każdy miesiąc w skoroszycie
Jeśli planujesz śledzić frekwencję przez rok, musisz utworzyć arkusz każdego miesiąca w programie Excel.
Krok 2: Dodaj kolumny dla każdej daty w arkuszu każdego miesiąca.
Teraz utwórz tabelę zawierającą nazwiska członków zespołu, kolumnę z sumami i 30 (lub liczbę dni w miesiącu) kolumn z datą i dniem tygodnia jako nagłówkami.
Aby uzyskać nazwę dnia tygodnia, możesz wyszukać kalendarz lub użyć formuły, aby skopiować ją do pozostałych komórek.
| =TEKST(data;"ddd") |
Możesz o tym przeczytać tutaj.
Sformatuj weekendy i święta na ciemne i wypełnij je stałymi wartościami, takimi jak Weekend/Wakacje, jak pokazano na poniższym obrazku.
Zrób to samo dla każdego arkusza.
Krok 3. Napraw możliwe dane wejściowe za pomocą sprawdzania poprawności danych dla każdej otwartej komórki.
Teraz każdy może wstawić swoją obecność w arkuszu, ale może wpisać dowolny tekst. Niektórzy mogą pisać P jako obecny, obecny lub per itp. Jednolitość danych jest obowiązkowa w każdym systemie zarządzania frekwencją.
Aby umożliwić użytkownikom pisanie tylko P lub A odpowiednio jako obecny i nieobecny, możemy użyć walidacji danych.
Wybierz dowolną komórkę, przejdź do danych na wstążce i kliknij sprawdzanie poprawności danych. Wybierz listę z opcji i wpisz A,P w polu tekstowym.
Naciśnij OK.
Skopiuj tę walidację dla całego otwartego zakresu danych (otwarty zakres oznacza komórkę, w której użytkownik może wstawić wartości).
Krok 3: Zablokuj wszystkie komórki z wyjątkiem miejsc, w których należy wprowadzić obecność.
Wybierz datę w kolumnie daty. Na przykład wybierz 1 stycznia. Teraz kliknij wybrany zakres i przejdź do formatowania komórek. Idź do ochrony. Usuń zaznaczenie zablokowanego pola wyboru. Naciśnij OK. Teraz skopiuj ten zakres do wszystkich otwartych zakresów dat.
Umożliwi to wejście do tych komórek tylko wtedy, gdy chronimy arkusze robocze za pomocą menu ochrony arkuszy roboczych. Dzięki temu Twoje formuły, formatowanie pozostaną nienaruszone, a użytkownicy będą mogli jedynie modyfikować swoją obecność.
Krok 4: Oblicz obecne dni członków drużyny
Jak więc obliczyć współczesność? Cóż, każdy ma swoje własne formuły obliczania frekwencji. Tutaj omówię moje. Możesz dokonać zmian zgodnie z wymaganiami arkusza obecności.
Całkowitą liczbę dni teraźniejszych liczę jako liczbę dni w miesiącu pomniejszoną o liczbę dni nieobecnych. Dzięki temu święta i weekendy będą w ryzach. Będą one automatycznie liczone jako dni robocze.
Zatem formuła Excela do liczenia dni obecnych będzie wyglądać tak:
| =LICZBA(daty)-LICZ.JEŻELI(zakres_obecności; "A") |
To domyślnie spowoduje, że wszyscy będą obecni przez cały miesiąc, dopóki nie oznaczysz ich nieobecnych na arkuszu.
W tym przykładzie formuła to:
| =LICZBA(C$2:$AG$2)-LICZ.JEŻELI(C3:AG3;"A") |
Napisałem tę formułę w komórce B3, a następnie skopiowałem ją. Widać, że 27 dni jest pokazanych jako prezent. Mimo że nie wypełniłem wszystkich komórek obecności. Możesz zachować to w ten sposób, jeśli chcesz, aby domyślnie były obecne. Lub jeśli chcesz, aby domyślnie były nieobecne, zaznacz wszystkie komórki jako nieobecne. Spowoduje to, że w bieżących obliczeniach będą liczyły się tylko obecne dni.
Krok 5: Chroń arkusz
Teraz, kiedy zrobiliśmy wszystko na tym arkuszu. Chrońmy go, aby nikt nie mógł zmienić formuły ani formatowania w arkuszu.
Przejdź do karty recenzji na wstążce. Znajdź menu Chroń arkusz. Kliknij na to. Otworzy się okno dialogowe, które poprosi o uprawnienia, które chcesz nadać użytkownikom. Sprawdź wszystkie uprawnienia, na które chcesz zezwolić. Chcę tylko, aby użytkownik mógł wypełnić swoją obecność niczym innym. Więc zachowam to tak, jak jest.
Powinieneś użyć hasła, które łatwo zapamiętasz. W przeciwnym razie każdy może go odblokować i zmienić skoroszyt obecności.
Teraz, jeśli spróbujesz zmienić komórki bez obecności, program Excel nie pozwoli ci na to. Możesz jednak zmienić cele obecności, ponieważ nie zostały one zabezpieczone.
Krok 6: Wykonaj powyższą procedurę dla wszystkich arkuszy miesięcznych
Zrób to samo dla każdego arkusza miesiąca. Najlepiej skopiować ten sam arkusz i zrobić z niego 12 arkuszy. Usuń ich ochronę i wprowadź niezbędne zmiany, a następnie chroń je ponownie.
Przygotuj arkusz obecności mistrza
Chociaż mamy wszystkie arkusze gotowe do wykorzystania do wypełnienia frekwencji, nie mamy jednego miejsca, w którym można by je wszystkie monitorować.
Administracja chciałaby widzieć całą frekwencję w jednym miejscu, a nie na różnych arkuszach. Musimy stworzyć główny arkusz obecności.
Krok 7: Przygotuj tabelę główną do monitorowania obecności w jednym miejscu w programie Excel
W tym celu przygotuj tabelę zawierającą nazwiska członków zespołu jako nagłówki wierszy i nazwę miesiąca jako nagłówki kolumn. Zobacz obrazek poniżej.
Krok 7: Sprawdź obecność zespołu z arkusza każdego miesiąca
Aby sprawdzić frekwencję z arkusza, możemy mieć prostą formułę WYSZUKAJ.PIONOWO, ale wtedy będziemy musieli to zrobić 12 razy dla każdego arkusza. Ale wiesz, że możemy mieć jedną formułę do wyszukiwania z wielu arkuszy.
Użyj tej formuły w komórce C3 i skopiuj w pozostałych arkuszach.
| =WYSZUKAJ.PIONOWO($A3;ADR.POŚR(C$2&"!$A$3:$B$12");2,0) |
Ponieważ wiemy, że wszystkie arkusze mają łączną frekwencję w zakresie B3:B12, używamy funkcji ADR.POŚR, aby pobrać wartości z wielu arkuszy. Gdy skopiujesz tę formułę w prawo, wyszukuje ona wartości w arkuszach z lutego.
Uwaga: upewnij się, że nazwy arkuszy i nagłówki kolumn we wzorcu są takie same, w przeciwnym razie ta formuła nie zadziała.
Krok 8: Użyj funkcji Sum, aby uzyskać wszystkie obecne dni roku członka zespołu.
Jest to opcjonalne. Jeśli chcesz, możesz obliczyć łączną liczbę obecnych dni swoich pracowników w ciągu roku za pomocą formuły sumy.
I to wszystko. Mamy gotowy system zarządzania obecnością Excel. Możesz to zmodyfikować zgodnie ze swoimi wymaganiami. Użyj go do obliczania wynagrodzenia, obliczania zachęt lub czegokolwiek innego. To narzędzie Cię nie zawiedzie.
W każdym arkuszu możesz wprowadzić zmiany, aby osobno obliczać święta i weekendy. Następnie odejmij je od sumy dni bieżących, aby obliczyć sumę dni roboczych. Możesz również dodać L dla urlopu w rozwijanym menu, aby oznaczyć urlop pracowników.
Więc tak, w ten sposób możesz stworzyć system zarządzania frekwencją Excel dla swojego startupu. Jest tani i bardzo elastyczny. Mam nadzieję, że ten samouczek pomoże ci w stworzeniu własnego skoroszytu obecności w programie Excel. Jeśli masz jakieś pytania, daj mi znać w sekcji komentarzy poniżej.
Wyszukiwanie z tabel zmiennych przy użyciu funkcji POŚREDNIE: Aby wyszukać ze zmiennej tabeli w Excelu, możemy użyć funkcji ADR.POŚR. Funkcja ADR.POŚR pobierze zakres tekstu i przekształci go w rzeczywisty zakres obecności.
Użyj INDEKSU i DOPASUJ, aby wyszukać wartość: Formuła INDEKS-POZYCJA służy do dynamicznego i precyzyjnego wyszukiwania wartości w danej tabeli. Jest to alternatywa dla funkcji WYSZUKAJ.PIONOWO i eliminuje wady funkcji WYSZUKAJ.PIONOWO.
Użyj funkcji WYSZUKAJ.PIONOWO z co najmniej dwóch tabel przeglądowych | Aby wyszukać z wielu tabel, możemy zastosować podejście IFERROR. Wyszukiwanie z wielu tabel przyjmuje błąd jako przełącznik do następnej tabeli. Inną metodą może być podejście Jeśli.
Jak wykonać wyszukiwanie z uwzględnieniem wielkości liter w programie Excel? | funkcja WYSZUKAJ.PIONOWO programu Excel nie rozróżnia wielkości liter i zwróci pierwszą dopasowaną wartość z listy. INDEX-MATCH nie jest wyjątkiem, ale można go zmodyfikować, aby rozróżniać wielkość liter. Zobaczmy, jak…
Wyszukaj często pojawiający się tekst z kryteriami w programie Excel | Wyszukiwanie najczęściej pojawia się w tekście w zakresie, w którym używamy funkcji INDEX-MATCH z funkcją MODE. Oto metoda.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO programu Excel| Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z Excela LICZ.JEŻELI Funkcja| Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.