Elektroda.pl
Elektroda.pl
X

Wyszukiwarki naszych partnerów

Wyszukaj w ofercie 200 tys. produktów TME
Europejski lider sprzedaży techniki i elektroniki.
Proszę, dodaj wyjątek elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Excel - wyszukiwanie pionowe i funkcja jeżeli

jobdziak 17 Sty 2013 20:55 4350 22
  • #1 17 Sty 2013 20:55
    jobdziak
    Poziom 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 17 Sty 2013 21:12
    nunak
    Poziom 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 17 Sty 2013 21:16
    jobdziak
    Poziom 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

  • #5 18 Sty 2013 06:06
    jobdziak
    Poziom 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 19 Sty 2013 14:43
    marek003
    Poziom 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 19 Sty 2013 17:28
    jobdziak
    Poziom 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 :(

  • Pomocny post
    #8 19 Sty 2013 19:24
    jsw123
    Poziom 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 19 Sty 2013 20:20
    jobdziak
    Poziom 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

  • Pomocny post
    #10 19 Sty 2013 22:08
    jsw123
    Poziom 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.

  • Pomocny post
    #11 19 Sty 2013 23:50
    jsw123
    Poziom 11  

    Jeśli zdecydujesz się skorzystać z tabeli przestawnej np. jako skoroszytu pomocniczego wypróbuj funkcję WEŹDANETABELI

  • #12 20 Sty 2013 11:10
    jobdziak
    Poziom 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?

  • Pomocny post
    #13 20 Sty 2013 13:19
    marek003
    Poziom 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 20 Sty 2013 13:28
    jsw123
    Poziom 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 20 Sty 2013 16:37
    jobdziak
    Poziom 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

  • Pomocny post
    #16 21 Sty 2013 10:48
    marek003
    Poziom 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)


    Kod: vb
    Zaloguj się, aby zobaczyć kod

  • #17 21 Sty 2013 11:26
    jobdziak
    Poziom 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 21 Sty 2013 13:51
    marek003
    Poziom 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 21 Sty 2013 14:49
    jobdziak
    Poziom 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!!!!

  • Pomocny post
    #20 21 Sty 2013 14:53
    marek003
    Poziom 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 21 Sty 2013 17:03
    jobdziak
    Poziom 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 22 Sty 2013 00:41
    marek003
    Poziom 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:

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    Mimo że "czasPrc"/"czasSpo" był prawidłowy to Format() "obcinał" wszystko ponad 24 godziny.
    powinno być:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    Bo właściwie póżniej formatuje te komórki:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

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

    Jak już wspominałem mylić się jest rzeczą ludzką.

  • #23 02 Lut 2013 16:51
    jobdziak
    Poziom 10  

    zamykam temat - funkcje w excelu jak i makra działają rewelacyjnie - nauczyłem się czegoś nowego :)

 Szukaj w ofercie
Zamknij 
Wyszukaj w ofercie 200 tys. produktów TME