W tym wpisie pokażę Ci, jak w Excelu ustalić, którym pracownikom został już wysłany PIT roczny. Moje rozwiązanie będzie miało kilka etapów, chociaż do uzyskania czystej informacji z tematu tego posta wystarczyłby tylko pierwszy krok. Jednak ja dodam coś od siebie, aby łatwiej (i przyjemniej) pracowało się z tymi danymi. Stworzę mianowicie:
- Formułę, która odpowie na główne pytanie: komy wysłaliśmy już PIT roczny?
- Stworzę podsumowanie: ilu osobom wysłaliśmy, a ilu jeszcze nie wysłaliśmy PIT-u, z ładną wizualizacją postępu w wysyłce.
- W tabeli z pracownikami wyróżnię kolorem tych pracowników, którzy jeszcze nie otrzymali PIT-u.
Do dyspozycji mam tabelę z listą pracowników (w niej są również szczegóły dotyczące tych pracowników jak imię i nazwisko, dział czy data zatrudnienia i urodzenia) w arkuszu BAZA, oraz listę pracowników, którzy otrzymali już PIT roczny (arkusz Wysyłki).
Obie formatki z tabelami przedstawia poniższy obraz:
wysyłka PITów Formatka
Jak widzisz, dane pracowników znajdują się w tabeli o nazwie tbPracownicy, a dane wysyłek w tbWysylki. Przechowywanie danych w obiekcie tabela (a nie w zwykłym zakresie) jest o tyle wygodne, że po dopisaniu danych do tabeli, jej zakres się automatycznie powiększa. Jeśli zatem oprę formułę na tabeli, co mam zamiar zrobić, to formuła ta automatycznie uwzględni dopisane dane. Uwielbiam tę jej funkcjonalność, choć to nie jedyna fajna 😉 (więcej o obiekcie tabela możesz dowiedzieć się z mojego webinaru O obiekcie tabela słów kilka, dostępnego w Malinowym Sklepie TUTAJ).
OK, zatem do dzieła!
1. Kto już otrzymał PIT – formuła
Do ustalenia kto już otrzymał PIT, użyjemy formuły, a w niej prostej funkcji LICZ.JEŻELI. Wykorzystamy fakt, że w tabeli z wysyłkami mamy ID pracownika oraz datę wysyłki. Jeśli policzymy, ile razy w tej tabeli występuje ID konkretnego pracownika, otrzymamy de facto informację o tym, czy dostał on PIT, czy nie. Jeśli dostał – powinien wystąpić w tabeli raz (nie zakładamy sytuacji, że ktoś dostał PIT więcej razy 😉). Jeśli nie otrzymał PIT-u – w ogóle nie powinien się znaleźć w tej tabeli (czyli występuje tam zero razy).
Czyli wystarczy, że policzymy ile razy w tabeli tbWysylki występuje konkretne ID pracownika. Zadanie wprost dla LICZ.JEŻELI.
Formuła, którą należy umieścić w arkuszu BAZA, najlepiej w komórce I14, wygląda tak:
=LICZ.JEŻELI(tbWysylki[ID pracownika];[@[ID pracownika]])
Zauważ tutaj dwie rzeczy:
- Obiekt tabela tbPracownicy automatycznie się powiększył o dodatkową kolumnę, kiedy obok niej dopisaliśmy dane (warto od razu zmienić nazwę tej kolumny z automatycznej Kolumna1, np. na Czy dostał PIT?)
- Ponieważ obie tabelki to obiekty tabela (odpowiednio tbPracownicy i tbWysylki), w powyższej formule, zamiast tradycyjnych odwołań do komórek są tzw. odwołania strukturalne – charakterystyczne właśnie dla obiektu tabela. Oznaczają one odpowiednio:
- tbWysylki[ID pracownika] → tabela o nazwie tbWysylki, kolumna ID pracownika
- [@[ID pracownika]] → wartość z wiersza, w którym jest formuła (@), z kolumny ID pracownika. Nazwa tabeli została tutaj pominięta, ponieważ formuła znajduje się w tabeli, do której ten zapis się odwołuje, czyli w naszym wypadku tbPracownicy
- Wpisana formuła automatycznie się skopiowała do końca kolumny (i będzie się kopiować, w przypadku, gdy do tej tabeli dopiszemy nowe dane!)
Efekt jest następujący:
Wysyłka PITów – kto dostał PIT?
Kolumnę tę należy odczytywać następująco:
- 1 → otrzymał już PIT
- 0 → nie otrzymał jeszcze PIT-u
Jeśli teraz chcemy wybrać osoby, które PIT-u nie otrzymały, możemy użyć zwykłego autofiltru (Dane/Filtruj) i wybrać wszystkie 0, tak:
Wysyłka PITów -Autofiltrowanie pracowników
To jest wersja minimum tego, co bym zrobiła, aby dowiedzieć się, kto otrzymał/nie potrzymał PIT-u rocznego. Natomiast ja bym na tym nie zakończyła, zrobiłabym tutaj trochę więcej, aby ułatwić sobie pracę z kontrolą wysyłek PIT-ów. Zrobiłabym jeszcze:
- Podsumowanie: ilu pracowników już otrzymało i nie otrzymało PIT-ów + ładna wizualizacja.
- W tabeli głównej zaznaczyłabym kolorem tych pracowników (ich imiona i nazwiska), którzy PIT-u jeszcze nie dostali.
Te usprawnienia spowodują, że będzie mi się przyjemniej pracowało z danymi 😊 Do dzieła zatem!
2. Tabelka z podsumowaniem kto (nie)otrzymał PIT-u
Tabelkę tę umieszczę nad główną tabelą z pracownikami, czyli w arkuszu BAZA, nad tabelą tbPracownicy (począwszy od komórki F7). Zamieszczę w niej proste podsumowanie: ilu pracowników dostało PIT, ilu nie dostało, ilu jest łącznie, te informacje w procentach oraz prostą wizualizację postępu wysyłki. Tabela ta wygląda tak:
Wysyłka PITów – tabelka z podsumowaniem
Teraz pozostaje kwestia wpisania odpowiednich formuł podsumowujących. A zatem:
- Liczba wysłanych będzie to zwykła suma kolumny, którą przed chwilą uzupełniliśmy formułą:
G8: =SUMA(tbPracownicy[Czy dostał PIT?])
- Liczba pozostałych do wysłania będzie taka, jak liczba zer w tejże kolumnie. Znowu możemy to obliczyć funkcją LICZ.JEŻELI, o tak:
G9: =LICZ.JEŻELI(tbPracownicy[Czy dostał PIT?];0)
- Łączna liczba pracowników, to suma obu powyższych wartości:
G7: =SUMA(G8:G9)
- Na koniec już tylko procenty, czyli:
H8: =G8/$G$7 H9: =G9/$G$7
- A dla komórki H7 wpiszę taką formułę:
H7: =H8
Czyli przyrównanie do wartości procentowej wysłanych PIT-ów. Jest to bowiem informacja o tym, jaki procent osób już dostało PIT. Na podstawie tej wartości zrobię też wizualizację w postaci paska postępu wysyłek, a technicznie będzie to po prostu pasek danych formatowania warunkowego.
Aby go stworzyć, zaznacz komórkę H7, gdyż ją właśnie będziemy formatować na podstawie wartości, i wejdź do Narzędzia główne/Formatowanie warunkowe/Paski danych. Wybierz kolor paska, który Ci najbardziej odpowiada. Efekt, który zobaczysz, to jeszcze nie jest to, co chcemy osiągnąć, więc się nie przerażaj 😉
Wysyłka PITów – pierwszy efekt paska danych
Teraz będziemy edytować tę regułę, a zatem kliknij na Narzędzia główne/Formatowanie warunkowe/Zarządzaj regułami i wybierz przed chwilą utworzoną regułę, a następnie przycisk Edytuj.
Teraz dokonamy zmiany w sposobie wyświetlania paska. Chcemy, aby jego wartością maksymalną było 100%, czyli liczba 1, a minimalną 0% czyli 0. Dzięki temu, jeśli do 58% osób zostanie wysłany PIT, to nasz pasek danych będzie prezentował właśnie tę wartość. Ustaw zatem te opcje w okienku edycji reguły. Pokazuje to poniższy obrazek:
Wysyłka PITów – edycja reguły formatowania warunkowego – pasek danych
Jeśli dodatkowo nie chcesz, aby wartość procentowa (u mnie 58%) była widoczna obok paska, możesz dodatkowo zaznaczyć checkbox: Pokaż tylko pasek (krok 3 na rysunku).
Po zatwierdzeniu wszystkich okienek otrzymasz taki efekt:
Wysyłka PITów – gotowa tabelka z podsumowaniem
OK, pora zatem na ostatni bajerek, czyli zaznaczanie pracowników kolorem, jeśli jeszcze nie dostali PIT-u.
3. Zaznaczanie kolorem pracowników, którzy nie otrzymali PIT-u
Aby tego dokonać, będziemy działać w tabeli tbPracownicy, a konkretnie na jej kolumnie Pracownik. Zaznacz zatem tę kolumnę, gdyż ona będzie podlegała formatowaniu warunkowemu.
Następnie przejdź do Narzędzia główne/Formatowanie warunkowe/Nowa reguła. W okienku, które się pojawi, zaznacz ostatnią opcję Użyj reguły do określenia komórek, które należy sformatować i wpisz taką regułę:
=$I14=0
Ta formuła mówi tyle, że jeśli komórka w kolumnie I (kolumna jest zablokowana dolarem), w tym samym wierszu, co nazwisko pracownika (wiersz nie jest zablokowany), jest równa zero, to owa komórka ma być wyróżniona kolorem (wybierz taki, który Ci pasuje, klikając przycisk Formatuj).
Powyższe kroki prezentuje obrazek:
Wysyłka PITów – wyróznianie pracowników kolorem – reguła
Jeśli chcesz się dowiedzieć więcej o adresowaniu komórek, czyli o użytych w tym rozwiązaniu dolarach – omawiam je bardzo szczegółowo podczas mojego webinaru Adresowanie komórek, dostępnym w moim sklepie pod TYM LINKIEM.
A o formatowaniu warunkowym więcej mówię podczas webinaru Formatowanie warunkowe, dostępnym TUTAJ.
To by było na tyle jeśli chodzi o kontrolę wysyłek PIT-ów. Mam nadzieję, że artykuł był dla Ciebie przydatny 😊.
Na koniec mam do Ciebie prośbę: pomożesz mi dotrzeć z tym artykułem do szerszego grona? Jeśli zechcesz udostępnić ten artykuł na Facebooku – będę Ci bardzo wdzięczna! Bardzo się starałam, aby ten artykuł był dla Ciebie na tyle wartościowy, by wart był wzmianki ;). Oczywiście śmiało podeślij go innym osobom mailem lub Messengerem. Będzie mi bardzo miło:)
Plik do pobrania:
I film na Youtubie, gdzie pokazuję powyższe rozwiązanie:
0 Comments