Excel - wyszukiwanie pionowe i funkcja jeżeli

Question

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ł cza...

jsw123 · Accepted Answer

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.

jsw123 · Accepted Answer

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.

jsw123 · Accepted Answer

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

marek003 · Accepted Answer

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.

marek003 · Accepted Answer

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)

Log in, to see the code

marek003 · Accepted Answer

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).

nunak · Answer

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.

jobdziak · Answer

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

JaroFon · Answer

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

jobdziak · Answer

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

marek003 · Answer

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ł?

jobdziak · Answer

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

jobdziak · Answer

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

jobdziak · Answer

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?

jsw123 · Answer

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ć.

jobdziak · Answer

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

jobdziak · Answer

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

marek003 · Answer

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.

jobdziak · Answer

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!!!!

jobdziak · Answer

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

marek003 · Answer

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:

Log in, to see the code

Mimo że "czasPrc"/"czasSpo" był prawidłowy to Format() "obcinał" wszystko ponad 24 godziny.
powinno być:

Log in, to see the code

Bo właściwie póżniej formatuje te komórki:

Log in, to see the code

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

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

jobdziak · Answer

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