Elektroda.pl
Elektroda.pl
X

Search our partners

Find the latest content on electronic components. Datasheets.com
Please add exception to AdBlock for elektroda.pl.
If you watch the ads, you support portal and users.

Excel - wyszukiwanie pionowe i funkcja jeżeli

jobdziak 17 Jan 2013 20:55 5085 22
  • #1
    jobdziak
    Level 10  
    Witam,

    mimo długich poszukiwań odpowiedzi, prób i błędów poddaje się prosząc jednocześnie o pomoc.

    Chcę stworzyć arkusz który będzie kontrolował czas pracy Pracowników - ich zalogowanie i wylogowanie z systemu.
    Dane które pozyskuje są z mało doskonałego narzędzia które należy obrabiać ręcznie lub np za pośrednictwem tabeli przestawnej. Ponieważ w przyszłości narzędzie będzie rozbudowywane o nowe funkcje muszę odejść od tabeli przestawnej i...
    Do zakładki "dane" będą przeklejane dane z zewnątrz w przykładowym formacie:
    - imię i nazwisko
    - czas zalogowania
    - czas wylogowania
    - data



    Pierwszy problem to jeden Pracownik w ciągu jednego dnia wylogowuje się kilka razy co rejestruje system i rozdziela każde wylogowanie i zalogowanie na osobne wiersze.

    Co ja potrzebuje...
    W zakładce czas chcę zliczać dla danego Pracownika, pierwszy czas zalogowania do systemu i ostatni czyli wylogowanie inaczej przyjście i wyjście z pracy.
    Poza tym ponieważ dane będę generował za miesiąc potrzebuje w rozdzielić wynik wyszukiwania na datę (przykład formatki w zakładce czas)

    Próbowałem funkcji "wyszukaj pionowo", "min" i "max", jeżeli itd ale jakoś nie mogę znaleźć rozwiązania by w formule uwzględnić wyszukiwanie, warunek jeżeli dla daty i wskazać wartość min lub max dla danego pracownika.

    Proszę Was o pomoc - będę zobowiązany

    Pozdrawiam
    GJ
  • #2
    nunak
    Level 14  
    A to chcesz robić codziennie ? czy raz na jakiś czas ?
    Znalezienie max i min w okresie dnia nie stanowi problemu dla danego pracownika, za chwilę ewentualnie przysiądę i zobaczę jak to jest z dodatkowym warunkiem daty.
  • #3
    jobdziak
    Level 10  
    chcę to robić raz na tydzień, czyli konieczne jest wprowadzenie warunku dla daty.
    Bez warunku daty udało mi się wykonać formułę z min i max, ale jak zacząłem kombinować z datą arkusz się rozjeżdżał :(

    Dopiero uczę się excela i mam wrażenie że jest to proste, ale brak doświadczenia mnie jednak przerasta
  • #4
    JaroFon
    Level 23  
    Kolego jobdziak skoro prosisz o pomoc i dałeś link do pliku to nie żądaj zapłaty w postaci punktów na Twoje konto za pobranie tego pliku :wink:
  • #5
    jobdziak
    Level 10  
    Wielkie sory - nie było to specjalnie
    Myślałem że ktoś zyska punkty a nie ja - już poprawiam...

    Mam nadzieję iż to nie wpłynie na dalszą pomoc
  • #6
    marek003
    Level 40  
    Powiem ci że nie jest to takie proste zagadnienie.

    Czy dane będą zwasze pogrupowane tak jak w przykładzie tzn w danym dniu wg nazwisk i logowania od pierwszego do ostatniego?
    Czy to może być "sieczka"?

    A czasu przerw nie będziesz sprawdzał?
  • #7
    jobdziak
    Level 10  
    nie wiem czy dobrze zrozumiałem pytanie ale postaram się odpowiedzieć.
    Dane dotyczące nazwisk będą pogrupowane w ten sposób ale powtarzalność danego użytkownika nie będzie stała, podobnie jak liczba nie powtarzających się nazwisk. Raz to samo nazwisko może powtórzyć się 3 razy a innym razem 15. Raz różnych nazwisk może być 10 a raz 20.

    Czas przerw będę kontrolował ale za pomocą innego narzędzia które już mam przygotowane - tam jednak nie wykorzystuje podziału na dni bo ów statystykę muszę wykonywać codziennie.

    Wiem że temat nie jest prosty bo już siedziałem nad tym kilka godzin :(
  • Helpful post
    #8
    jsw123
    Level 11  
    Jeśli nie tabela przestawna to myślę, że najlepszym rozwiązaniem jest zastosowanie makra. Takie makro może pobierać datę na którą ma być obliczona godzina wejścia wyjścia i dla wszystkich pracowników wklejać w odpowiednie pola odpowiednie czasy.
    Jeśli będziesz zainteresowany mogę przedstawić więcej szczegółów.
  • #9
    jobdziak
    Level 10  
    Tabeli przestawnej chciałbym uniknąć.
    Odnośnie makra niestety nie potrafię ich pisać, więc pozostaje mi tylko prosić i być na łasce innych by stworzyć taki formularz z napisanym makrem :(

    Będę wdzięczny za pomoc

    pozdr
    GJ
  • Helpful post
    #10
    jsw123
    Level 11  
    Na podstawie pliku czas_pracy.xls przygotowałem przykładowe makro.

    W skoroszycie czas dodałem w komórce A2 ilość pracowników wyszukaną funkcją =ILE.NIEPUSTYCH(A3:A5000).
    Dodałem nowy skoroszyt "Warunek" w którym znajdują się komórki z funkcjami wyszukującymi interesujące nas godziny : =BD.MIN(dane!$A:$D;C$1;$A$1:$B$2) i =BD.MAX(dane!$A:$D;D$1;$A$1:$B$2) oraz komórki z zakresem kryteriów.
    Tego skoroszytu nie należy ruszać, a najlepiej go ukryć.

    Makro nazywa się czas_log_wylog i działa w następujący sposób:
    Ustawiamy się na dowolnej komórce poniżej daty w skoroszycie czas.
    Wciskamy kombinację klawiszy Ctrl-Shift-A
    Makro wyszukuje datę powyżej, w arkuszu warunek wpisuje datę do zakresu kryteriów. Następnie ustawia się na pracowniku i w pętli aż do ostatniego pracownika wpisuje do odpowiedniej komórki z zakresem kryteriów nazwisko pracownika. Z komórek z wyszukanymi czasami przepisuje dane do odpowiednich komórek w skoroszycie czas. Na koniec ustawia się na dacie na którą wyszukiwał.

    Ze względu na różnice w wersji excel-a skrypt może wymagać drobnych korekt.
    Należy pamiętać o zezwoleniu na działanie makr w arkuszu kalkulacyjnym.
  • Helpful post
    #11
    jsw123
    Level 11  
    Jeśli zdecydujesz się skorzystać z tabeli przestawnej np. jako skoroszytu pomocniczego wypróbuj funkcję WEŹDANETABELI
  • #12
    jobdziak
    Level 10  
    Kolego jsw123 bardzo Ci dziękuje za pomoc - przejrzałem arkusz i stworzone makra - właśnie o to chodziło :)

    Chyba czeka mnie nauka makr :)

    PS. jak rozumiem jedyna możliwość by uruchomić makro to kombinacja klawiszy Ctr+shift+A? Nie można uruchomić makra dla całego arkusza, czyt. dla wszystkich zakresów dat jednocześnie?
  • Helpful post
    #13
    marek003
    Level 40  
    Jeżeli bez makra to trzeba by było dodać kolumny pomocnicze do danych.

    A jak z makrem to właściwie wszystko można w nim zawrzeć.

    Poniżej bez makr ale dwie kolumny pomocnicze.
  • #14
    jsw123
    Level 11  
    Zachęcam do nauki języka skryptów VB.
    Makra można uruchamiać na kilka sposobów z Makra w menu bądź wstążce,
    z przypisanych skrótów klawiszowych do makra,
    z obiektu graficznego posadzonego na skoroszycie (do obiektu może być przypisane uruchomienie makra). Sądzę, że znalazło by się jeszcze kilka sposobów.

    Odpowiednio modyfikując makro możesz oczywiście uruchomić obliczenia dla całego interesującego cię zakresu. Spróbuj pokombinować.
  • #15
    jobdziak
    Level 10  
    Panowie, bardzo dziękuje za pomoc i rady.
    Makra owszem muszę się nauczyć, niemniej w pierwszej kolejności chcę poćwiczyć formuły w excelu a później nauczyć się języka SQL.

    Marek003 - dzięki za kolejne rozwiązanie - miło uczyć się od innych nowych formuł. Na chwilę obecną to jest dla mnie łatwiejsze rozwiązanie, ale jsw123 zainspirował mnie by "wjeść głębiej"

    raz jeszcze dzięki - już w myślach widzę kolejne formularze które udoskonalę o nowe "przeliczanie" :)

    miłego
  • Helpful post
    #16
    marek003
    Level 40  
    Jak najbardziej należy się uczyć i VBA ale z kolei nie wszystko na raz. Piszesz że słabo znasz excela (funkcje) a chcesz się brać od razu za VBA.
    Można ... tylko boje się że się wcześniej zniechęcisz całkowicie do excela nie tylko do VBA (tym bardziej jak nie miałeś do czynienia z jakimkolwiek programowaniem).


    Poniżej kod i przykład który za przyciśnięciem jednego przycisku tworzy ci cały raport. Sam wie ile wpisać dat i jakie nazwiska powinny się znaleźć na liście więc możesz spokojnie zostawić arkusz "czas" czysty wystarczy tylko wymienić dane w arkuszu dane.
    I jak to zwykle przy pisaniu programów: sprawdź czy czasem nie myli się w obliczeniach (bo program pisał człowiek a to najsłabsze ogniwo programowania)


    Code: vb
    Log in, to see the code
  • #17
    jobdziak
    Level 10  
    Marek003 - słuszna uwaga że wiedzę powinienem dawkować stopniowo jednak, jak wiedzę jakie formularze można tworzyć dzięki makrom to zazdroszczę tej umiejętności innym.

    Własnie odpaliłem przygotowany przez Ciebie formularz i mówię wow...

    Świetne narzędzie które na spokojnie muszę rozgryźć chociaż w jakiejś części w zaciszu domowym.

    Zacząłem już przerabiać Twój wcześniejszy formularz w excelu. Pewnie są inne szybsze sposoby by dokonać obliczeń które robię, ale czy to samo można wykonać korzystając z makr (pewnie tak ale jak :)) ????

    Każdy dzień tygodnia rozszerzyłem o dodatkową kolumnę "spóźnienie" gdzie odniesieniem dla spóźnienia jest zalogowanie w przedziale 20min po pełnej godzinie czyli 08:01-08:20; 09:01-9:20 itd...W kolumnie tej koduje czas spóźnienia i zliczam w kolumnie podsumowanie. W kolumnie podsumowanie zliczam również łączny czas zalogowanie jak i liczbę spóźnień

    Gdyby wprowadzić tu makra było by idealnie

    Jeśli jest taka szansa byś zerknął na temat będzie super
  • #18
    marek003
    Level 40  
    Za reklama smakowity:
    "No z formułami to będziesz musiał chyba jeszcze trochę poćwiczyć" :)

    Excel ma pomoc na temat funkcji. Naciśnij przy polu komend przycisk Fx i np zobacz jakie są funkcje daty i czasu. Dużo się wyjaśni.

    Co prawda mam mieszane uczucia co do zasad spóźnień ale ... to nie moja firma. (nie opłaca się spóźniać krócej niz 20 minut. Jak i tak trzeba będzie odpracować nie będzie straszących zapisów o spóźnieniach)


    Trzeba tez zwrócić uwagę na formatowanie łącznego czasu pracy bo jak przekroczy 24 godziny to pokaże ci czas ponad te 24 godziny. (zgubi wielokrotność 24)
    W tym przypadku format niestandardowy powinien być "[gg]:mm" Wtedy będzie widoczne np 60:21


    Co do makra to jak znajdę chwilę.

    edit:
    Dodałem plik z makro.
  • #19
    jobdziak
    Level 10  
    aż mi głupio - jak na to patrzę to takie proste a ja wszystko na około robiłem.
    Tym większą mam mobilizację by umieć więcej :)

    dzięki Marek - jestem winien DUŻEGO browara!!!!
  • Helpful post
    #20
    marek003
    Level 40  
    Dodam tylko że makro w poprzednim poście co do zasady właściwie robi to samo co formuły (no zliczanie spóźnien ma trochę inaczej).
  • #21
    jobdziak
    Level 10  
    czuję się jak dziecko które otrzymało prezent na gwiazdkę !!!
    Te makra są świetne!!!
    Pewnie dla Ciebie to chleb powszedni ale dla mnie to magia którą władają profesjonaliści.

    Jeszcze raz bardzo dziękuje - arkusz wyprzedził moje oczekiwania.
    Teraz muszę go przestudiować na spokojnie bo już widzę kolejne narzędzie które mogę udoskonalić :)

    Służę pomocą w akwarystycę :)

    Pozdrawiam

    Dodano po 1 [godziny] 45 [minuty]:

    Kolego marek003, przepraszam że niepokoje ale nie wiem dlaczego dla kolumny czas pracy dla większej ilości danych wartość sumaryczna jest błędna

    Szukam odpowiedzi w makrach ale nie potrafię znaleźć formuły zliczającej :(

    będę zobowiązany
  • #22
    marek003
    Level 40  
    Wspominałem o 24 godzinach a sam zrobiłem ten błąd.
    tak jak wspomniałem wszystko trzeba sprawdzać bo to pisał człowiek.

    Niepotrzebnie wcześniej (przy wyliczaniu czasu) określałem że interesują mnie tylko godziny i minuty.

    czasPrc to czas pracy a czasSpo to czas spóźnień, s to ilość spóźnień.

    Błąd był w tych liniach kodu:
    Code: vb
    Log in, to see the code

    Mimo że "czasPrc"/"czasSpo" był prawidłowy to Format() "obcinał" wszystko ponad 24 godziny.
    powinno być:
    Code: vb
    Log in, to see the code

    Bo właściwie póżniej formatuje te komórki:
    Code: vb
    Log in, to see the code

    a tak wcześniej "obcinałem" te 24 godziny.

    Jak już wspominałem mylić się jest rzeczą ludzką.
  • #23
    jobdziak
    Level 10  
    zamykam temat - funkcje w excelu jak i makra działają rewelacyjnie - nauczyłem się czegoś nowego :)