Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

jak w excelu z tabli z logami wejść i wyjść pracowników zliczyć godziny

tok13 01 Kwi 2017 10:00 1881 35
  • #1 01 Kwi 2017 10:00
    tok13
    Poziom 12  

    zaimportowałem z pliku csv logi wejśc i wyjśc pracowników do tabeli gdzie poszczególne wiersze sa pojedynczymi zdarzeniami albo wejsciem albo wyjsciem danego pracownika tylko jak teraz zrobić aby zliczył mi ile dany pracownik pracował (czas wyjscia minus czas wejscia) tymbardziej że są pracownicy którzy wchodzili i wychodzili po kilka razy

    0 29
  • #2 01 Kwi 2017 10:18
    adamas_nt
    Moderator Programowanie

    Najprościej: odejmowanie w każdym wierszu i SUMA.JEŻELI dla kolumny wynikowej.

    0
  • #4 01 Kwi 2017 10:45
    tok13
    Poziom 12  

    ale w pojedynczym wierszu jest imie nazwisko data i czas wejścia albo wyjscia i nie mogę zrobić odejmowania w wierszu tylko w kolumnie dwóch kolejnych zdarzeń dla danej osoby, na szczęście są one ułożone chronologicznie czyli jest wiersz z wejściem a poniżej z wyjsciem, ale po drodze są jeszcze wejścia i wyjścia innych osób

    0
  • #6 01 Kwi 2017 14:12
    tok13
    Poziom 12  

    a troszkę jaśniej, obie te funkcje są mi znane ale nie bardzo mogę złapać jak je w tym przypadku zastosowac

    0
  • #8 02 Kwi 2017 10:45
    tok13
    Poziom 12  

    super właśnie o to chodziło ale niestety liczy poprawnie tylko do 7- go wiersza nie mogę namierzyć co jest nie tak

    Dodano po 14 [godziny] 24 [minuty]:

    coś z przeszukiwaniem tablicy jest nie tak bo jak sie robi wieksza tablica to głupieje co moze być nie tak

    0
  • #10 02 Kwi 2017 11:41
    tok13
    Poziom 12  

    próbowałem i to samo bo problem jest w tym że funkcja wyszukaj pionowo zawsze zaczyna przeszukiwanie tablicy od góry i jak znajdzie pirwsze wejście to ten czas odejmje, wszystko by działało gdyby każdy z pracowników tylko raz wchodził i raz wychodził ale niestety tak nie jest i nie mam pomysłu jak to obejść

    0
  • #11 02 Kwi 2017 11:52
    Dżyszla
    Poziom 42  

    Dlatego odwróć porządek w tabeli - niech na górze będą najnowsze informacje. Wtedy pierwsze wyszukiwanie z tabeli poniżej będzie ostatnią akcją.

    Nie ma niestety szukania liniowego ze zwracaniem ostatniego rekordu :( Jak już, to tylko pierwsze wystąpienie, faktycznie.

    0
  • #12 02 Kwi 2017 14:45
    Prot
    Poziom 27  

    tok13 napisał:
    jak teraz zrobić aby zliczył mi ile dany pracownik pracował (


    Spróbuj kolego wykorzystać tutaj najwygodniejszą funkcję bazy danych Excela tj. BD.SUMA() :idea:
    jak w excelu z tabli z logami wejść i wyjść pracowników zliczyć godziny2017-04...png Download (146.59 kB)

    Jeżeli zachowasz odpowiednie formatowanie czasu to bardzo łatwo uzyskasz sumowanie w różnych przekrojach określonych ustawionymi kryteriami.

    Przykładowe możliwości tej funkcji ilustruję w załączonym pliku

    Czas.xlsx Download (11.93 kB)Punkty: 2 dla użytkownika

    0
  • #14 02 Kwi 2017 16:50
    tok13
    Poziom 12  

    Niestety odwróciłem tabele i tez działa tylko przy jednokrotnym wejściu i wyjściu kurcze jak to można obejść

    0
  • #16 02 Kwi 2017 18:58
    tok13
    Poziom 12  

    Chodzi o parametr False czyli 0 w funkcji wyszukaj pionowo ostatni przed zamknieciem nawiasu?
    =JEŻELI(B3="wyj";C3-WYSZUKAJ.PIONOWO(A3;A$2:C2;3;0);"")
    tak to wygląda ale nadal nie działa i nie wiem dla czwego

    0
  • #19 02 Kwi 2017 19:34
    tok13
    Poziom 12  

    Brawo teraz działa super ..jestem pod wrażeniem bardzo bardzo dziękuę jeszcze tylko jak automatycznie odwrócić kolumny
    ja zastosowałem funkcję przesunięcie =PRZESUNIĘCIE($B$19;-WIERSZ(G1)+1;0;1;1) ale odwraca mi tylko jedną kolumnę a jak zrobic żeby odwrócił wszystkie trzy lub cztery

    0
  • #21 03 Kwi 2017 12:03
    tok13
    Poziom 12  

    proste ale skuteczne jeszcze raz dziękuję

    Dodano po 15 [godziny] 48 [minuty]:

    Niestety mój optymizm prysł bo na realnym pliku to nie chce działac i nie wiem dla czwego wypisuje N/D

    0
  • #22 03 Kwi 2017 15:58
    Prot
    Poziom 27  

    tok13 napisał:

    Niestety mój optymizm prysł bo na realnym pliku to nie chce działac i nie wiem dla czwego wypisuje N/D


    A nie zastanawiałeś się jak Excel ma traktować:

    - występujące 204 razy w tej tabeli "nazwiska" typu Harmonogram, Użytkownik nieznany czy po prostu pustą komórkę ?

    - dziwną asymetrię zdarzeń: 534 wejścia oraz 659 wyjść (w tym 114 tego Użytkownika nieznanego :D ) ?

    - wyjście Admina tego samego dnia o godz 00:52:00 i 05:03:50 - które wejście przypisać do jednego i drugiego ???

    W mojej ocenie tu nie pomogą żadne triki excelowskie - konieczne jest uważny import danych , wyeliminowanie danych błędnych lub sprzecznych z logiką np godz 08:42:00 - 6 jednoczesnych wyjść (w tym 2 "harmonogram" i 4 puste :?: ).

    Jak wszystkie dane będą już "wyczyszczone" (usunięte wszystkie oczywiście błędne i te zbędne do analizy czasu pracy) to można wtedy przekształcić Twoją tabelę w "tabele obliczeniową" - jak widać na nowym moim zrzucie.

    jak w excelu z tabli z logami wejść i wyjść pracowników zliczyć godzinyczas.JPG Download (165.9 kB)

    Szczegóły moich przekształceń (opartych o niestandardowe sortowanie i wyszukiwanie najbliższego wyjścia w dynamicznym offsecie) widać w arkuszu 2 załączonego pliku.

    Czas(2..lsx Download (19.25 kB)

    0
  • #23 03 Kwi 2017 17:42
    Dżyszla
    Poziom 42  

    Chodzi o ułożenie danych. Funkcja WYSZUKAJ.PIONOWO szuka w pierwszej kolumnie tabeli! Musisz mieć czas za kolumną z nazwą osoby.

    A poza tym - faktycznie dużo niepoprawnych danych jest w tym zestawieniu. I masz zły zakres przeszukiwany - nie może się na tym samym wierszu zaczynać.

    jak w excelu z tabli z logami wejść i wyjść pracowników zliczyć godziny

    0
  • #24 04 Kwi 2017 08:31
    ramzesix
    Poziom 7  

    Panie magistrze a co w sytuacji kiedy jest:
    LP-Agent-Akcja-Czas-Data
    1-AK-Wejscie-8:00:00-1.03-2017
    2-JN-Wejście-10:00:00-1.03.2017
    3-JK-Wejście-11:00:00-1.03.2017
    4-AK-Wyjście-12:00:00-1.03.2017
    5-AK-Wejście-12:15:00-1.03.2017
    6-AK-Wyjście-17:00:00-1.03.2017
    7-JK-Wyjście-17:05:00-1.03.2017
    8-JN-Wyjście-18:00:00-1.03.2017
    8-JK-Wejście-7:00:00-2.03.2017
    9-JN-Wejście-9:00:00-2.03.2017
    10-AK-Wejście-11:00:00-2.03.2017

    Zrobiłem dwa filtrowania: A-Z i Z-A dwupoziomowe, pierwsze jest wyjęte z raportu, drugie wyfiltrowane datą i czasem malejąco. Podobno jest jakiś skrypt żeby wyszukał osobe i jej czas wejścia i wyjścia jednego dnia, trzeba tylko zrobić dwie bazy jedną rosnącą drugą malejącą co też zrobiłem, jaki jest zatem następny krok, jakiej uzyć formuły?

    0
  • #25 04 Kwi 2017 12:10
    Prot
    Poziom 27  

    ramzesix napisał:
    ...a co w sytuacji kiedy jest:
    ... Podobno jest jakiś skrypt żeby wyszukał osobe i jej czas wejścia i wyjścia jednego dnia, trzeba tylko zrobić dwie bazy jedną rosnącą drugą malejącą co też zrobiłem, jaki jest zatem następny krok, jakiej uzyć formuły?


    Pytanie wprawdzie nie do mnie :D , ale mogę podpowiedzieć, że bez VBA przy użyciu standardowych funkcji można uzyskać prostą analizę czasu pracy

    jak w excelu z tabli z logami wejść i wyjść pracowników zliczyć godziny2017-04...png Download (62.78 kB)

    Wystarczy dane odpowiednio wyczyścić :!: (z wszelkich błędów), posortować i zastosować np funkcję
    Kod: vbscript
    Zaloguj się, aby zobaczyć kod

    do wyszukiwania odpowiednich czasów wyjść.

    0
  • #26 04 Kwi 2017 13:24
    ramzesix
    Poziom 7  

    Kolego Prot,

    Dziękuję za szybką odpowiedź i pomoc. Niestety problem jest trudniejszy.

    Zrób takie coś bez tej tabeli gdzie wypisałeś wejście i wyjście w jednym. Mam bazę .csv i w 65tys. rekordach nie chce mi się naprawdę grzebać. Wczoraj metodą tradycyjną ponad 10h siedziałem żeby wpisać dane wyjścia wejścia dla ok. 100 pracowników.
    Chodzi o myk żeby wypisywał wartość najwyższą i najniższą jednego dnia danego użytkownika, może to robić nawet na wszystkie a nie konkretny dzień, byle by były z danego dnia: wyszukana wartość minimalna - kiedy ten użytkownik wchodzi i wyszukana maksymalna - kiedy wychodzi. Na podstawie tej bazy powinien właśnie wyszukiwać, podałem ci w jaki sposób to sobie posortowałem i jak to wygląda. Patrzyłem w accesie jak to zrobić ale jak zakładałem minimalną to wyszukiwał wystąpień minimalnej w całej bazie - a nie o to tu chodzi. Jakby mi wypluwała wyjście i wejście w jednym rzędzie to bym się nie bawił, tylko zrobił tabelę przestawną. To co tu dałeś bazuje na wpisanym przez ciebie ograniczeniu do określonej godziny - bo ją widać a co by było jakby była na miejscu 2347? też byś wpisał? Bez sensu! Skrypcik o którym mówię ma sam wyszukać największą wartość z danego dnia danego użytkownika.

    O coś takiego mi chodzi, i tak do 31. A potem następny.

    LP-Agent-Akcja-Czas-Data________________________________________________AGENT-DZIEN-AKCJA-CZAS
    1-AK-Wejscie-8:00:00-1.03-2017________________________________________AK-1.03.2017-wejscie-8:00:00
    2-JN-Wejście-10:00:00-1.03.2017_______________________________________AK-1.03.2017-wyjscie-17:00:00
    3-JK-Wejście-11:00:00-1.03.2017________________________________________AK-2.03.2017-wejscie-11:00:00
    4-AK-Wyjście-12:00:00-1.03.2017
    5-AK-Wejście-12:15:00-1.03.2017
    6-AK-Wyjście-17:00:00-1.03.2017
    7-JK-Wyjście-17:05:00-1.03.2017
    8-JN-Wyjście-18:00:00-1.03.2017
    8-JK-Wejście-7:00:00-2.03.2017
    9-JN-Wejście-9:00:00-2.03.2017
    10-AK-Wejście-11:00:00-2.03.2017

    0
  • #27 04 Kwi 2017 14:49
    Prot
    Poziom 27  

    ramzesix napisał:
    .

    O coś takiego mi chodzi, i tak do 31. A potem następny.

    LP-Agent-Akcja-Czas-Data________________________________________________AGENT-DZIEN-AKCJA-CZAS
    1-AK-Wejscie-8:00:00-1.03-2017________________________________________AK-1.03.2017-wejscie-8:00:00
    2-JN-Wejście-10:00:00-1.03.2017_______________________________________AK-1.03.2017-wyjscie-17:00:00
    3-JK-Wejście-11:00:00-1.03.2017________________________________________AK-2.03.2017-wejscie-11:00:00




    Jak wskazuje doświadczenie (choćby na przykładzie autora tego wątku) pliki csv uzyskiwane z urządzeń rejestrujących obarczone są całą gamą różnorodnych błędów :!: , które uniemożliwiają ich automatyczną analizę. Obojętnie czy w Accessie, czy w Excelu przy użyciu formuł, czy w VBA - zawsze musisz wstępnie rozstrzygnąć co program ma robić w przypadku błędu (każdego możliwego :D ) albo ... przyjąć (czysto teoretycznie, bo praktycznie to niemożliwe - vide powyższy cytat w którym już widać 4 błędy literowe i chyba parę logicznych :cry: ), że zestawienie danych jest bezbłędne :?:

    Jeśli masz problemy z importem danych i ich analizą to najlepiej pokaż oryginalny plik źródłowy (ewentualnie tylko zanonimizowany jeśli są tam dane osobowe).
    Tu na forum jest wiele osób, które gotowe są pomóc w rozwiązywaniu konkretnych zadań lecz omijają pokraczne przykłady.

    0
  • #28 09 Kwi 2017 17:22
    tok13
    Poziom 12  

    Prot napisał:
    tok13 napisał:

    Niestety mój optymizm prysł bo na realnym pliku to nie chce działac i nie wiem dla czwego wypisuje N/D


    A nie zastanawiałeś się jak Excel ma traktować:

    - występujące 204 razy w tej tabeli "nazwiska" typu Harmonogram, Użytkownik nieznany czy po prostu pustą komórkę ?

    - dziwną asymetrię zdarzeń: 534 wejścia oraz 659 wyjść (w tym 114 tego Użytkownika nieznanego :D ) ?

    - wyjście Admina tego samego dnia o godz 00:52:00 i 05:03:50 - które wejście przypisać do jednego i drugiego ???

    W mojej ocenie tu nie pomogą żadne triki excelowskie - konieczne jest uważny import danych , wyeliminowanie danych błędnych lub sprzecznych z logiką np godz 08:42:00 - 6 jednoczesnych wyjść (w tym 2 "harmonogram" i 4 puste :?: ).

    Jak wszystkie dane będą już "wyczyszczone" (usunięte wszystkie oczywiście błędne i te zbędne do analizy czasu pracy) to można wtedy przekształcić Twoją tabelę w "tabele obliczeniową" - jak widać na nowym moim zrzucie.



    Szczegóły moich przekształceń (opartych o niestandardowe sortowanie i wyszukiwanie najbliższego wyjścia w dynamicznym offsecie) widać w arkuszu 2 załączonego pliku.



    Poidoba mi sie to co zrobiłeś ale u mnie jakoś nie chce działać w kolumnie H w formule wyszukaj pionowo jest jakiś błąd i nie mogę go znaleść

    0
  • #29 09 Kwi 2017 20:47
    Prot
    Poziom 27  

    tok13 napisał:
    w formule wyszukaj pionowo jest jakiś błąd i nie mogę go znaleść


    Nie tyle (no może nie najważniejszy :D ) w formule jest błąd lecz Twoje dane są nie wyczyszczone, źle sformatowane i źle posortowane żeby formuła tego typu dała sobie radę.

    W moim przykładzie uszło Twej uwagi, że:
    - przykładowe dane w całym zakresie nazwanym "Dane" - wszystkie dane czasowe są sformatowane niestandardowo:"dd.mm.rrrr gg:mm:ss" - tak więc jedna wartość liczbowa zawiera zarówno datę jak i czas (szczególnie ważne przy nocnych zmianach :D );
    - żadna funkcja Excela nie wyeliminuje błędnych danych RCP - musisz je odpowiednio wyfiltrować tj bez "harmonogramu", bez nieznanych i bez pustych;
    - wszystkie istotne Dane muszą dla tej formuły być odpowiednio posortowane tj najpierw wg czasu (dzień i godzina), a potem wg typu akcji (sortowanie od A do Z zapewni pożądany układ najpierw same wejścia, a potem wyjścia).

    Dopiero po takim przygotowaniu Danych można zastosować tą funkcję wyszukiwania :!:

    Masz rację, że proponowa funkcja generuje jeszcze błędy - ale to wynika z jej specyficznej cechy wyszukiwania pierwszego wystąpienia poszukiwanej wartości.
    W sytuacji zakłócenia "dynamiki" przeszukiwanego zakresu np poprzez zestawienie tylko godzin akcji (bez dni) lub wielokrotnego wychodzenia i wchodzenia tego samego użytkownika jednego dnia - funkcja przestaje działać.

    Proponuję pokaż jak wygląda oryginalny plik csv z systemu RPC (ewentualnie na pw) to będę mógł tą funkcje udoskonalić.

    0
  • #30 09 Kwi 2017 21:55
    tok13
    Poziom 12  

    ok rozumiem
    juro podeślę oryginalny plik csv i może uda ci się coś z niego wycisnąć, będę bardzo wdzięczny....
    pozdrawiam

    0