Raport rozliczeniowy serwisu w Excel Power Query - czas pracy, dojazdy, pojazdy i urlopy w jednym zestawieniu

Problem: rozliczenie serwisu wymaga ręcznego łączenia kilku arkuszy

Dane potrzebne do rozliczenia są w systemie, ale w kilku osobnych raportach. Złożenie ich w jedno zestawienie za każdym razem wymaga tej samej, żmudnej pracy.

Rozwiązanie: skrypt Power Query, który składa raport z eksportów Locatick

Power Query to wbudowany w Excel moduł do pobierania i przekształcania danych. Raz zapisane kroki - łączenie tabel, filtrowanie, przeliczenia - wykonują się automatycznie przy każdym odświeżeniu. Skrypt wczytuje cztery eksporty z Locatick, porządkuje je i łączy w jedną tabelę rozliczeniową.

W logice raportu jest przeliczanie czasu pracy na podstawie godzin start/stop, osobne sumowanie czasu naprawy, dojazdu i powrotu, dopasowanie pojazdu do wykonawcy oraz normalizacja danych urlopowych do wspólnego formatu. Skrypt trafia do firmy razem z instrukcją wdrożenia krok po kroku.

Raport rozwija się razem z klientem. Poprawki - na przykład w sumowaniu kilometrów przejazdu - trafiają do firmy jako zaktualizowana wersja skryptu, znów z instrukcją wdrożenia.

Jak działa raport rozliczeniowy w Power Query - krok po kroku

Od eksportów z systemu do gotowego zestawienia do rozliczeń.

Rezultaty: rozliczenie serwisu bez ręcznego łączenia arkuszy

Raport zamienia powtarzalną pracę na danych w jedno odświeżenie zapytania.

Rozliczanie czasu pracy serwisantów - jak zautomatyzować raport w Excelu

Rozliczenie pracy w terenie to zwykle kilka źródeł danych naraz. Poniżej najważniejsze pytania o to, jak uporządkować je w jednym raporcie i kiedy wystarczy Excel z Power Query.

Czym jest Power Query i dlaczego sprawdza się w rozliczeniach serwisu?

Power Query to wbudowane w Excel narzędzie do pobierania, łączenia i przekształcania danych, które zapamiętuje każdy krok i powtarza go przy odświeżeniu. Zamiast co miesiąc ręcznie kopiować kolumny, usuwać zbędne wiersze i liczyć różnice godzin, zapisuje się te operacje raz, w postaci zapytania. Przy kolejnym rozliczeniu wystarczy podmienić pliki źródłowe i odświeżyć raport. W rozliczeniach serwisu to szczególnie przydatne, bo dane pochodzą zwykle z kilku miejsc: z zadań, z rejestracji czasu pracy, z kart pojazdów i z ewidencji urlopów. Power Query potrafi połączyć takie tabele po wspólnych polach, na przykład po wykonawcy i dacie, i policzyć z nich wartości potrzebne do rozliczenia. Dla działu rozliczeń ważne jest też to, że cała praca odbywa się w znanym środowisku Excela, bez nowego narzędzia do nauki.

Jak rozliczać czas pracy serwisanta: naprawa, dojazd i powrót osobno?

Czas pracy serwisanta warto dzielić co najmniej na trzy składowe: czas naprawy na obiekcie, czas dojazdu i czas powrotu. Taki podział daje znacznie więcej niż jedna suma godzin. Pozwala rozliczać klienta tylko za te elementy, które przewiduje umowa, pokazuje, ile czasu zespół spędza w drodze, i ułatwia ocenę, czy trasy i przydział zleceń są dobrze zaplanowane. W raporcie Power Query składowe są wyliczane automatycznie z godzin start/stop zarejestrowanych w systemie, a następnie sumowane osobno. Dzięki temu rozliczenie nie zależy od ręcznych notatek serwisanta ani od tego, czy ktoś w biurze dobrze przepisał godziny. Sposób traktowania czasu dojazdu w wynagrodzeniach i fakturach zależy od ustaleń w firmie i umów z klientami, dlatego raport dostarcza dane w rozbiciu, a decyzja o ich wykorzystaniu zostaje po stronie firmy.

Jak połączyć eksporty z systemu serwisowego w jeden raport?

Eksporty łączy się po wspólnych kluczach - najczęściej po wykonawcy, dacie i numerze zadania - po wcześniejszym ujednoliceniu ich formatu. W praktyce każdy eksport ma trochę inną strukturę: inne nazwy kolumn, inny zapis dat czy godzin, inne jednostki. Pierwszym krokiem w Power Query jest więc oczyszczenie każdej tabeli osobno: wybór potrzebnych kolumn, ustawienie typów danych, ujednolicenie nazw. Dopiero potem tabele są scalane w jedno zestawienie. W raporcie rozliczeniowym łączone są cztery źródła z Locatick: raport zadań z protokołami, raport aktywności z Timetrackera, dane karty pojazdu i dane urlopowe. Ważne, żeby eksporty były pobierane zawsze w tym samym układzie - wtedy zapisane kroki zapytania działają przy każdym odświeżeniu bez poprawek. Dobrze jest też od początku zdecydować, który eksport jest tabelą główną, do której dołączane są pozostałe dane - zwykle jest to lista zadań albo aktywności serwisantów.

Power Query czy integracja API - kiedy wybrać automatyzację po stronie danych?

Automatyzacja po stronie danych sprawdza się, gdy wynik jest potrzebny cyklicznie, na przykład raz w miesiącu, a firma nie ma zasobów programistycznych. Integracja API daje przepływ danych w czasie rzeczywistym, ale wymaga zaprojektowania, wdrożenia i utrzymania połączenia między systemami. Przy rozliczeniu miesięcznym czas rzeczywisty nie jest potrzebny - liczy się to, żeby zestawienie było poprawne i szybkie do przygotowania. Wtedy skrypt Power Query działający na standardowych eksportach jest prostszy i tańszy w utrzymaniu. Integracje i skrypty mają z kolei przewagę tam, gdzie dane muszą płynąć na bieżąco albo uruchamiać kolejne akcje, jak w automatyzacji, w której oferta PDF powstaje od razu po zatwierdzeniu formularza. Dobór podejścia warto zacząć od pytania, jak często i przez kogo wynik będzie używany. Często najlepiej sprawdza się połączenie obu: integracje dla procesów operacyjnych, raporty po stronie danych dla rozliczeń.

Jak rozliczać przejazdy i kilometry serwisantów?

Przejazdy najłatwiej rozliczać, gdy dane z karty pojazdu są powiązane z konkretnym wykonawcą i dniem pracy. Sam rejestr kilometrów pojazdu nie mówi jeszcze, kto nim jechał i do jakich zadań. Dopiero połączenie karty pojazdu z wykonawcą pozwala zestawić przejazdy z czasem dojazdu i powrotu oraz z realizowanymi zadaniami. W raporcie to dopasowanie odbywa się automatycznie w Power Query. Warto też testować raport na prawdziwych danych - błędy, na przykład w sumowaniu kilometrów przejazdu, najłatwiej wychwycić właśnie na teście i poprawić w kolejnej wersji skryptu. Przy danych rozliczeniowych taka iteracja jest normalnym etapem - lepiej wychwycić rozbieżność na teście niż w gotowym rozliczeniu. Prostą kontrolą jest porównanie sumy kilometrów w raporcie z odczytami licznika z karty pojazdu za ten sam okres - szybko pokazuje, czy dopasowanie działa poprawnie.

Jak uwzględnić urlopy i nieobecności w raporcie rozliczeniowym?

Urlopy i nieobecności trzeba sprowadzić do tego samego formatu co dane o czasie pracy - zwykle do wiersza na osobę i dzień. Ewidencja urlopów często ma inną strukturę niż raport aktywności: zamiast pojedynczych dni zawiera zakresy dat albo inne oznaczenia typów nieobecności. Bez normalizacji takie dane nie dadzą się połączyć z resztą zestawienia. Power Query może rozwinąć zakres dat na pojedyncze dni i ujednolicić oznaczenia, dzięki czemu w raporcie widać pełny obraz miesiąca: kiedy serwisant pracował, ile czasu spędził na naprawach i w drodze, a kiedy był nieobecny. To ułatwia kontrolę kompletności danych i pozwala szybko wychwycić dni, w których brakuje rejestracji czasu pracy. Warto ustalić jedną listę typów nieobecności i trzymać się jej w ewidencji, bo każde nowe oznaczenie trzeba uwzględnić w logice zapytania.

Jak utrzymać i rozwijać raport Power Query w firmie?

Raport Power Query wymaga przede wszystkim stałego układu danych wejściowych i prostej instrukcji odświeżania. Jeśli eksporty są pobierane zawsze w ten sam sposób, osoba z działu rozliczeń odświeża raport bez znajomości logiki zapytań. Zmiany - nowe kolumny, inne zasady przeliczeń, dodatkowe źródło - warto wprowadzać jako kolejne wersje skryptu, z opisem, co się zmieniło i jak wdrożyć nową wersję. Tak działa to w Locatick: zgłoszone uwagi trafiają do poprawionej wersji skryptu, przekazywanej z instrukcją wdrożenia krok po kroku. Dobrą praktyką jest też trzymanie przykładowego zestawu danych testowych, na którym po każdej zmianie można sprawdzić, czy wyniki zgadzają się z oczekiwaniami. Przy większych zmianach dobrze jest zachować poprzednią wersję raportu, żeby w razie wątpliwości porównać wyniki obu wersji na tych samych danych.

Najczęstsze pytania o raport rozliczeniowy w Power Query