Stanisław Kijak

Przedmiot: przedmioty zawodowe

Rozrachunki z tytułu wynagrodzeń w arkuszu kalkulacyjnym

Artykuł opublikowany w specjalistycznym serwisie edukacyjnym http://awans.szkola.net/

Stanisław Kijak

Zespół Szkół Ekonomicznych w Mielcu

Rozrachunki z tytułu wynagrodzeń w  arkuszu kalkulacyjnym
 

Pomoc do prowadzenia zajęć edukacyjnych z rachunkowości w liceum ekonomicznym, liceum handlowym, a także w ramach programu pracowni ekonomiczno-informatycznej w szkołach ponadpodstawowych, ponadgimnazjalnych i na kierunkach policealnych.

Prowadzenie korespondencji w sprawach osobowych, wprowadzanie i aktualizowanie akt osobowych pracowników, prezentowanie danych o pracownikach wg dowolnie wybranych kryteriów, wystawianie zaświadczeń pracownikom, rejestrowanie stałych i ruchomych składników  płac, sporządzanie listy płac, przygotowanie deklaracji ZUS, obliczanie podatku dochodowego od osób fizycznych, to podstawowe umiejętności, których ukształtowanie przy wykorzystaniu informatycznych systemów kadrowo-płacowych jest jednym z zadań zajęć edukacyjnych w ramach programu pracowni ekonomiczno-informatycznej w szkołach ekonomicznych i handlowych. Profesjonalne programy kadrowo-płacowe, umożliwiające pełną obsługę działu kadry-płace z powodzeniem spełniają swoje zadania w każdej nowoczesnej firmie, jednakże jako części zintegrowanych systemów wymagają poznania specyficznego interfejsu użytkownika, co w edukacyjnej pracy z uczniami  lub słuchaczami bazującymi na umiejętnościach kształtowanych na lekcjach „Elementy informatyki” czy „Informatyka” stwarza pewne utrudnienia.

Celem niniejszej publikacji jest zaprezentowanie, jak do realizacji tych zadań można wykorzystać arkusz kalkulacyjny Excel. Praktyczny przykład rozwiązania zawiera plik Wynagrodzenia.xls, który zawiera propozycje projektów baz danych, koniecznych formuł obliczeniowych i funkcji oraz system makr i łączy integrujących poszczególne arkusze projektu.

Wstęp

              Prowadzenie rozrachunków z tytułu wynagrodzeń w prezentowanym projekcie sprowadza się do:

§         wprowadzenia do arkusza Stawki stawek podatku dochodowego, składek na ubezpieczenia społeczne i zdrowotne oraz zasiłków, które należy aktualizować zgodnie zobowiązującymi przepisami prawa regulującymi sposób obliczania wynagrodzeń pracowników,

§         wprowadzenia danych osobistych, adresowych i płacowych pracowników do arkuszy Dane osobiste i adresowe, Dane płacowe,

§         wpisania do arkusza Lista płac kodów pracowników dla których naliczone ma być wynagrodzenie,

§         wykorzystania formantu Pokrętło do wybierania kodów pracowników, przy sporządzaniu dokumentacji kadrowo-płacowej – arkusz ZUS,

§         ewentualnego skorygowania formuł obliczających dodatek stażowy w celu przystosowania ich do przyjętego systemu wynagradzania – arkusz Dodatek stażowy.

Wykorzystanie arkusza kalkulacyjnego do rozrachunków z tytułu wynagrodzeń

Wprowadzanie i aktualizowanie akt osobowych pracowników

              Dane osobiste, dane adresowe, dane do ubezpieczenia, informacje o zatrudnieniu, czasie pracy, opisy składników wynagrodzenia, opisy potrąceń, stawki podatków i stawek na ubezpieczenia społeczne i zdrowotne  oraz inne dane niezbędne do rozrachunków z tytułu wynagrodzeń z powodzeniem można wprowadzać wykorzystując tabele, które są podstawowym dokumentem arkusza kalkulacyjnego.
Komórki tabeli przechowywać mogą:

§         tekst,

§         liczby,

§         formuły obliczeniowe.

Wprowadzanie danych można dokonywać bezpośrednio do komórek tabeli arkusza,

Tabela Dane osobiste i adresowe

lub przy pomocy formularza, który wywołuje się na ekran poprzez zaznaczenie wiersza poniżej wiersza nagłówkowego oraz uruchomienie polecenia Formularz z menu Dane.

Formularz do wpisywania danych

Każda tabela arkusza staje się tym sposobem swoistą bazą danych,

a informacje zawarte w jej wierszu nagłówkowym - nazwami pól tej bazy.

       Nazwy pól w arkuszu Dane osobiste i adresowe

Ze względu na częste odwołania do danych zamieszczonych w poszczególnych arkuszach, zdefiniowane zostały nazwy danych w poszczególnych bazach (po zaznaczeniu potrzebnych danych należy uruchomić polecenie Nazwa/Definiuj... z menu Wstaw). 

W prezentowanym projekcie zdefiniowano nazwy:

§         kadry – z danymi osobistymi i adresowymi, obejmującą dane z arkusza Dane osobiste i adresowe zawarte w polach: Kod pracownika, Nazwisko, imię , PESEL, NIP, Data zatrudnienia, Dział, Data urodzenia, Kraj, Województwo, Powiat, Gmina ulica, Nr domu, Nr lokalu, Miejscowość, Kod pocztowy, Poczta  - (18 kolumn),

§         płace – z danymi płacowymi, obejmującą dane z arkusza Dane płacowe zawarte w polach: Kod pracownika, Płaca podstawowa, Dodatek motywacyjny, Dodatek stażowy, Dodatek funkcyjny, Wynagrodzenie za czas niezdolności do pracy wypłacane ze środków zakładu pracy, Wynagrodzenie za czas niezdolności do pracy wypłacane ze środków ZUS, Składka ubezpieczenia na życie, Inne potrącenia, Zasiłek rodzinny (12 kolumn, w tym dwie ukryte),

§         lista -  obejmująca dane wyliczane  na liście płac (27 kolumn),

§         dodatek – z danymi do naliczenia dodatku stażowego, obejmującą pola: Kod pracownika, Data zatrudnienia, Staż, Płaca podstawowa, Dodatek stażowy z arkusza Dodatek stażowy (5 kolumn).

Aby mieć możliwość w przyszłości wprowadzania do baz, danych nowozatrudnionych  pracowników (nowych rekordów bazy), zakresy definiowanych obszarów danych zaznaczone zostały z odpowiednią rezerwą (do 50 pracowników).

Jak można wywnioskować ze wstępnych uwag, dane osobiste, dane adresowe, dane do ubezpieczenia, informacje o zatrudnieniu, czasie pracy, opisy składników wynagrodzenia i potrąceń związane są z tym samym pracownikiem i zawierać mogą wiele identycznych informacji. W związku z tym umieszczono w każdej bazie pole z danymi sterującymi o nazwie  Kod pracownika, które stanowić będzie odniesienie - podstawę wyszukiwania do Listy płac, Deklaracji ZUS, PIT, zaświadczeń i innych dokumentów kadrowo-płacowych.

W przedstawionym projekcie wprowadzanie i aktualizowanie danych osobowych pracowników dokonuje się w arkuszach Dane osobiste i adresowe oraz Dane płacowe. Dzięki stworzonemu systemowi łącz Kod pracownika wprowadzany jest równocześnie do arkuszy: Dane osobiste i adresowe, Dane płacowe, Dodatek stażowy. Wprowadzenie symbolu N w polu Dział uaktywnia procedurę automatycznego naliczania dodatku stażowego (wg systemu przejętego dla nauczycieli), niezależnie od danych wprowadzanych do pola Dodatek stażowy w arkuszu Dane płacowe. Przy podawaniu informacji o kosztach wystarczy podać cyfrowo jeden z czterech schematów ich naliczania.

Sporządzanie listy płac

Wprowadzenie kodu pracownika (liczba w formacie 0000) do pola Kod pracownika w arkuszu Lista płac, który zawiera tradycyjny wzór listy płac z układem 26 kolumn, uruchamia automat obliczeń miesięcznego wynagrodzenia pracownika  zatrudnionego na podstawie umowy o pracę.  Automat w oparciu o zaprojektowane formuły obliczeniowe i funkcje realizuje:

§         wyszukiwanie na podstawie Kodu pracownika stosownych informacji w arkuszach  Dane osobiste i adresowe oraz Dane płacowe i umieszczenie ich w kolumnach: 2, 3, 4, 5, 6, 7, 8, 15, 22, 23, 24 arkusza Lista płac,
 

§         obliczanie wynagrodzenia brutto, potrąceń, składek ubezpieczenia społecznego i zdrowotnego oraz płacy netto i zachowanie wyników obliczeń w kolumnach:
9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 25.

Wyszukiwanie

Możliwość wyszukania informacji w arkuszach  Dane osobiste i adresowe i Dane płacowe daje funkcja arkusza kalkulacyjnego WYSZUKAJ.PIONOWO, którą wykorzystuje się wg schematu:

=WYSZUKAJ.PIONOWO(odniesienie;tablica;nr_kolumny;kolumna)

Odniesienie   jest wartością, którą należy znaleźć w pierwszej kolumnie tablicy. Odniesienie może być wartością, adresem lub łańcuchem tekstowym; w naszym przypadku odniesieniem będzie wprowadzona w każdej bazie zmienna sterująca Kod pracownika.

Tablica   jest tablicą z informacją, którą należy przeszukać. Należy użyć adresu zakresu lub nazwy zakresu.

Nr_kolumny   jest to numer kolumny w tablicy, z którego pochodzić powinna pasująca wartość. Nr_kolumny o wartości 1 daje w wyniku wartość w pierwszej kolumnie tablicy tablica; nr_kolumny o wartości 2 daje w wyniku wartość w drugiej kolumnie tablicy tablica, i tak dalej. Jeśli indeks nr_kolumny jest mniejszy niż 1, WYSZUKAJ.PIONOWO podaje w wyniku wartość błędu #ARG!; jeśli indeks nr_kolumny jest większy niż liczba kolumn w tablicy tablica, WYSZUKAJ.PIONOWO daje w wyniku wartość błędu #ADR!.

Kolumna   jest wartością logiczną wskazującą, czy WYSZUKAJ.PIONOWO ma znaleźć dokładne czy też przybliżone dopasowanie. Jeżeli parametr ten ma wartość PRAWDA lub został pominięty, wartością wynikową będzie dopasowanie przybliżone; innymi słowy, w razie gdyby nie uzyskano dokładnego dopasowania, wynikiem będzie następna największa wartość mniejsza niż wartość odniesienie. Jeśli parametr ma wartość FAŁSZ, WYSZUKAJ.PIONOWO znajdzie dokładne dopasowanie. Jeśli nie znajdzie żadnego, wynikiem będzie wartość błędu #N/D.

W niniejszym projekcie jako odniesienie przyjęto Kod pracownika, a jako tablice – zdefiniowane obszary o nazwach kadry i płace. Przyjęto również dokładne dopasowanie wartości wynikowej (argument kolumna =0).

Przykład:

wyszukanie płacy podstawowej pracownika o kodzie Kod_pracownika znajdującej się w drugiej kolumnie obszaru Płace i umieszczenie jej w kolumnie 3 Listy płac uzyskuje się po wpisaniu:

=WYSZUKAJ.PIONOWO(Kod_pracownika;Płace;2;0)

Zastosowanie zagnieżdżenia wykorzystującego funkcję logiczną

=JEŻELI(Kod_pracownika=””;””;WYSZUKAJ.PIONOWO(Kod_pracownika;Płace;2;0))

pozwala wyeliminować komunikaty o błędach i zbyteczne zapisy, jakie pojawić się mogą gdy  nie podany zostanie Kod pracownika.

Funkcja logiczna JEŻELI...