Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 68602 Skopiuj do schowka Liczenie nadgodzin
Autor Wiadomość
Gre1234
Starszy Forumowicz


Posty: 41
Wysłany: 27-07-2020, 11:47   Liczenie nadgodzin

Witam, do tej pory godziny były liczone za cały miesiąc, czyli znalem z góry liczbę godzin do przepracowania. Chciałem jednak znaleźć jakiś sposób żeby posiadać aktualna liczbę nadgodzin i tu pojawiają się dwa problemy:

1. Nie wiem jak automatycznie zliczyć liczbę przepracowanych godzin w momencie, gdy ktoś miał wolne i jest to zaznaczone tylko kolorem.
2. Nie wiem, jak zliczać komórki w których są tylko liczby, ponieważ czasami są robione dopiski bezpośrednio w komórkach, w które wpisuje się godziny.

Klos ma jakiś pomysł??

W załączniku, w wierszu 38 są zliczane nadgodziny z całego miesiąca, a w wierszu 59 z aktualnie przepracowanych godzin i zaznaczone na czerwono te które się nie zgadzają z powyżej podanych powodow.

PS. W skrócie, potrzebuje sposobu, aby zliczać ilość komórek z wierszy 2-32 (w odpowiednich kolumnach -E do K) w których są tylko liczby (godzin) lub jakiś kolor...

Mappe1.xlsm
Pobierz Plik ściągnięto 6 raz(y) 20.63 KB

ID posta: 390204 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1667 razy
Posty: 5448
Wysłany: 27-07-2020, 12:48   

Zastanów się co dokładnie mają liczyć Twoje formuły, a potem dopasujemy formuły (jeśli się da).
W tej chwili używasz w formułach wyrażenia:
Kod:
LICZ.WARUNKI(zakres;"<>")
które zlicza komórki, które mają jakąkolwiek zawartość (również teksty puste), nie zlicza tylko pustych komórek. Formatowanie (w tym kolor tła) nie jest traktowane jako zawartość.
Excel potrafi rozróżnić liczbę od tekstu zapisanych w komórce, natomiast kolor tła można odczytać przez VBA.
ID posta: 390212 Skopiuj do schowka
 
 
Gre1234
Starszy Forumowicz


Posty: 41
Wysłany: 27-07-2020, 13:07   

Maciej Gonet napisał/a:

W tej chwili używasz w formułach wyrażenia:
Kod:
LICZ.WARUNKI(zakres;"<>")
które zlicza komórki, które mają jakąkolwiek zawartość (również teksty puste), nie zlicza tylko pustych komórek.

Pierwszy raz używam tego licz.warunki i nie wiedziałem jak sprawdzać czy w komórce jest liczba czy nie. Używając CZY.LICZBA zamiast "<>" zwracało mi zero...
Poszukam jeszcze makro sprawdzające czy komórka ma kolor, ale nie wiem za bardzo jak by to później poskładać w całość.

Tak jak pisałem, potrzebuje sprawdzać czy w komórce jest liczba lub kolor (oprócz szarego, bo on jest od oznaczania weekendów) i zliczyć to, żeby wiedzieć z ilu dni będę liczyć nadgodziny.
ID posta: 390215 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1667 razy
Posty: 5448
Wysłany: 27-07-2020, 13:32   

Jeśli masz do sprawdzenia tylko jeden warunek możesz zamiast LICZ.WARUNKI używać też LICZ.JEŻELI. Jeśli chcesz używać tych funkcji, musisz sobie poczytać gdzieś w internecie czy w pomocy Excela, jak formułować kryteria dla tych funkcji. To nie jest oczywiste. Jeśli chcesz zliczać teksty, jako kryterium należy użyć gwiazdki (symbol zastępczy dla dowolnego tekstu):
Kod:
=LICZ.JEŻELI(zakres; "*")
Jeśli chcesz zliczać liczby, to pomyśl sobie jaka największa liczba może wystąpić w Twoich danych, na przykład milion, 1E6 i wtedy kryterium może być:
Kod:
=LICZ.JEŻELI(zakres; "<1E6")

Analogicznie można użyć bardzo małej liczby i operatora większości. Kryterium musi mieć ostatecznie postać tekstu, Excel poradzi sobie z zamianą liczby na tekst, ale nie wykona obliczeń wewnątrz tekstu, więc jeśli ten milion chcielibyśmy zapisać jako 10^6, to tak:
Kod:
"<"&10^6
a nie tak:
Kod:
"<10^6"

Jeśli chodzi o zliczanie komórek kolorowych, to masz już w arkuszu funkcję, która to robi. Trzeba ją tylko zaadaptować do zmodyfikowanego zadania.
ID posta: 390218 Skopiuj do schowka
 
 
Gre1234
Starszy Forumowicz


Posty: 41
Wysłany: 27-07-2020, 13:57   

Maciej Gonet napisał/a:

Jeśli chcesz zliczać liczby, to pomyśl sobie jaka największa liczba może wystąpić w Twoich danych, na przykład milion, 1E6 i wtedy kryterium może być:
Kod:
=LICZ.JEŻELI(zakres; "<1E6")

Super pomysł. Całkowicie inaczej niż do tego podchodziłem :) Dzięki!
Maciej Gonet napisał/a:

Jeśli chodzi o zliczanie komórek kolorowych, to masz już w arkuszu funkcję, która to robi. Trzeba ją tylko zaadaptować do zmodyfikowanego zadania.

Myślę, że aby sprawdzać jaki komórka ma kolor, to potrzebuje czegoś takiego:

Kod:
Function PodajKolor(Komorka As Range)
   
    Application.Volatile
   
    cell.Value = Komorka.Interior.Color
   
End Function

ale nie zadziałało :/
ID posta: 390220 Skopiuj do schowka
 
 
umiejead 
Excel Expert


Wersja: Win Office 2013
Pomógł: 661 razy
Posty: 3475
Wysłany: 27-07-2020, 14:48   

Kod:
Option Explicit
Sub a()
Dim kol, kol1, kol2$
Dim r&, g&, b&

kol = ActiveCell.Interior.ColorIndex
kol1 = ActiveCell.Interior.Color
r = kol1 Mod 256
g = kol1 \ 256 Mod 256
b = kol1 \ 65536 Mod 256
kol2 = "R=" & r & ", G=" & g & ", B=" & b
MsgBox "KolorIndex: " & kol & vbCrLf & vbCrLf & "Kolor: " & kol1 & _
        vbCrLf & vbCrLf & "KolorRGB: " & r & "," & g & "," & b

End Sub
_________________
.
Jak poprawnie opisać problem: http://www.excelforum.pl/...ika-vt59262.htm
Chcesz precyzyjną odpowiedź - zadaj precyzyjne pytanie.
ID posta: 390224 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1667 razy
Posty: 5448
Wysłany: 27-07-2020, 15:19   

Gre1234, tak jak chciałeś też można, tylko funkcja musi zwracać wynik przez swoją nazwę, a więc tak:
Kod:
Function PodajKolor(Komorka As Range)
    Application.Volatile
    PodajKolor = Komorka.Interior.Color
End Function
Dopiero gdy chcesz odczytać wynik w komórce, możesz tam wpisać:
Kod:
=PodajKolor(adres)
ID posta: 390227 Skopiuj do schowka
 
 
Gre1234
Starszy Forumowicz


Posty: 41
Wysłany: 27-07-2020, 16:02   

Działa! Teraz tylko jak to połączyć spójnikiem "lub"? Czyli żeby liczyło gdy jest w komórce liczba lub gdy kolor jest inny od szarego i białego? JEŻELI ?
Bo chyba licz.warunki się tutaj nie sprawdzi ?? No i jak podawać zmienna do PodajKolor jeśli chciałbym jej używać w formułach ?? Normalnie jako zakres?
ID posta: 390231 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1667 razy
Posty: 5448
Wysłany: 27-07-2020, 17:04   

Adam Mickiewicz napisał/a:
Twardowski na koncept wpada
I zadaje trudność nową.
A co to są te szare komórki? To pewnie te dni weekendowe zaznaczone formatowaniem warunkowym. A koloru formatowania warunkowego nie da się odczytać tak samo jak formatowania stałego. To komplikuje rozwiązanie. Po pierwsze - która wersja Excela?
A generalnie już kilka razy pisałem, że Excel nie został zaprojektowany z myślą, żeby używać kolorów jako informacji wejściowej. To miała być tylko informacja wyjściowa.
Ale analizując sens tego co napisałeś, to czy ten szary kolor formatowania warunkowego trzeba w ogóle brać pod uwagę? Te komórki w formatowaniu stałym są bez koloru. Chyba, że ktoś zmieniłby im kolor na inny, a później zamaskował to formatowaniem warunkowym. Wtedy taki kolor mógłby zostać zliczony. Czy zachodzi takie ryzyko?
W ogóle zliczanie pustych komórek kolorowych mocno skomplikuje te obliczenia. Trzeba by to robić głównie w VBA. Wydaje mi się, że rozwiązaniem najprostszym jest wpisywanie 0 do komórki, która ma mieć kolor, a nie ma innej zawartości. Następnie za pomocą LICZ.JEŻELI lub LICZ.WARUNKI zliczać wartości liczbowe (w tym te zera dopisane do kolorowych komórek). Równocześnie należy zwracać uwagę, czy jakieś liczby nie są przypadkowo wpisane w dniach wolnych.
Jeśli chodzi o użycie PodajKolor z argumentem, to używamy normalnie adresu komórki:
Kod:
=PodajKolor(B6)

Należy uważać na używanie tej funkcji z zakresem komórek, na przykład:
Kod:
=PodajKolor(B6:B8)
Funkcja nie zwraca w takim przypadku tablicy wyników jak funkcje zwykłe, lecz zawsze jedną liczbę - jeśli komórki zakresu mają taki sam kolor, to funkcja zwraca ten kolor, jeśli mają różne kolory - funkcja zwraca 0.
Oczywiście można ją tez napisać inaczej - tak żeby zwracała tablicę wyników, ale to już inna sprawa.

Mappe1.xlsm
Pobierz Plik ściągnięto 3 raz(y) 19.72 KB

ID posta: 390233 Skopiuj do schowka
 
 
Gre1234
Starszy Forumowicz


Posty: 41
Wysłany: 27-07-2020, 17:37   

Maciej Gonet napisał/a:

Po pierwsze - która wersja Excela?

16.16.24 dla Mac
Maciej Gonet napisał/a:

Ale analizując sens tego co napisałeś, to czy ten szary kolor formatowania warunkowego trzeba w ogóle brać pod uwagę?

Nie, to wynika z moich braków w znajomości tematu :/ Sorry za zamieszanie.
Maciej Gonet napisał/a:

Wydaje mi się, że rozwiązaniem najprostszym jest wpisywanie 0 do komórki, która ma mieć kolor, a nie ma innej zawartości.

To psuje trochę to co już jest. Zobacz proszę pojawiający się czerwony kolor ;)
Maciej Gonet napisał/a:

Następnie za pomocą LICZ.JEŻELI lub LICZ.WARUNKI zliczać wartości liczbowe (w tym te zera dopisane do kolorowych komórek). Równocześnie należy zwracać uwagę, czy jakieś liczby nie są przypadkowo wpisane w dniach wolnych.

A może inne podejście do tematu. W danym zakresie (E2:E32) chce znaleźć ostatnia nie pusta i niekolorową komórkę. Wtedy w D2:D(wiersz tej komórki) policzę sobie ile było dni roboczych.
Maciej Gonet napisał/a:

Jeśli chodzi o użycie PodajKolor z argumentem, to używamy normalnie adresu komórki:
Kod:
=PodajKolor(B6)



Kod:
=PodajKolor(B6:B8)
Funkcja nie zwraca w takim przypadku tablicy wyników jak funkcje zwykłe, lecz zawsze jedną liczbę - jeśli komórki zakresu mają taki sam kolor, to funkcja zwraca ten kolor, jeśli mają różne kolory - funkcja zwraca 0.
Oczywiście można ją tez napisać inaczej - tak żeby zwracała tablicę wyników, ale to już inna sprawa.

A gdyby zwracała tablice to wtedy funkcja Licz.warunki byłaby w stanie porównywać po kolei czy komórka jest "<1E6" i wynik funkcji PodajKolor dla niej ??
ID posta: 390234 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1667 razy
Posty: 5448
Wysłany: 27-07-2020, 18:35   

Tak to jest jak nie ma całościowej koncepcji od początku tylko łata się dziury.
W wierszu 37. są zliczane wszystkie liczby. Można zamiast tego zliczać liczby >0.
Jeśli chodzi o obliczenie ile było dni roboczych w miesiącu, to jest do tego funkcja DNI.ROBOCZE, gdzie podaje się datę początkową i końcową i ewentualnie daty świąt.
Nawet gdyby funkcja PodajKolor zwracała tablicę, to nie będzie współpracować z LICZ.JEŻELI, bo ta funkcja porównuje wszystkie wartości komórek z wszystkimi wartościami odniesienia. Tu można by było zastosować SUMĘ.ILOCZYNÓW, ale generalnie wydaje mi się to mniej efektywne.

Mappe1-2.xlsm
Pobierz Plik ściągnięto 3 raz(y) 19.84 KB

ID posta: 390236 Skopiuj do schowka
 
 
Gre1234
Starszy Forumowicz


Posty: 41
Wysłany: 28-07-2020, 07:23   

Maciej Gonet napisał/a:

W wierszu 37. są zliczane wszystkie liczby. Można zamiast tego zliczać liczby >0.

Wychodzi na to ze nie mam innej opcji...
Maciej Gonet napisał/a:

Jeśli chodzi o obliczenie ile było dni roboczych w miesiącu, to jest do tego funkcja DNI.ROBOCZE, gdzie podaje się datę początkową i końcową i ewentualnie daty świąt.

Mi bardziej chodzi żeby jakoś wyłuskać automatycznie, do jakiego momentu liczyć nadgodziny dla konkretnej osoby. Z podliczaniem miesięczny nie mam problemu bo wiem z góry ile ma być godzin przepracowanych w miesiącu. Tu problemem jest to żeby funkcja wiedziała dokąd są wpisane godziny i za ten okres podliczyła nadgodziny.

A nie dało by się zrobić w VBA prostego przejścia z wiersza 2 do 32 które podawałoby adres ostatniego wystąpienie komórki z liczba bądź kolorem?? Wtedy prosto byłoby policzyć wymagane godziny robiąc licz.jezeli w kolumnie D z zakresu D2:DOstatniaKomorka.
ID posta: 390244 Skopiuj do schowka
 
 
Gre1234
Starszy Forumowicz


Posty: 41
Wysłany: 28-07-2020, 08:21   

Maciej Gonet napisał/a:
Tak to jest jak nie ma całościowej koncepcji od początku tylko łata się dziury.

Ps. Mogę zacząć od początku ale jak wpłynie to na rozwiązanie mojego problemu ?

Maciej Gonet napisał/a:

W wierszu 37. są zliczane wszystkie liczby. Można zamiast tego zliczać liczby >0.

Ps2. Albo zamiast 0 wpisywać 0,5 co równa się przerwie i zostanie automatycznie odliczone, a więc nie wpłynie na liczbę godzin. Pasuje i matematycznie i logicznie bo, jeśli przepracowałeś 0,5h z czego 0,5h było przerwą to nic nie przepracowałeś :-P
  
ID posta: 390247 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1667 razy
Posty: 5448
Wysłany: 28-07-2020, 08:57   

gre1234 napisał/a:
A nie dało by się zrobić w VBA prostego przejścia z wiersza 2 do 32 które podawałoby adres ostatniego wystąpienie komórki z liczbą bądź kolorem??
Propozycja funkcji poniżej:
Kod:
Function Zapisane(Zakres As Range) As Range
   Application.Volatile
   Dim i As Long
   For i = Zakres.Rows.Count To 1 Step -1
      If Zakres(i).Interior.Color <> vbWhite Or Application.IsNumber(Zakres(i)) Then Exit For
   Next i
   If i = 0 Then i = 1
   Set Zapisane = Zakres.Resize(i)
End Function

Argumentem funkcji jest zakres do sprawdzenia. Funkcja zwraca w wyniku ten zakres skrócony o komórki bez koloru lub zawartości liczbowej na końcu zakresu (sprawdza zakres od końca). Ta funkcja bierze pod uwagę formatowanie stałe, a nie warunkowe. Jeśli zakres badany jest pusty funkcja zwraca pierwszą komórkę (mogłaby zwracać błąd, gdyby tak było wygodniej). Funkcja zwraca zakres, więc może być użyta jako argument LICZ.JEŻELI i podobnych funkcji. Przykłady użycia w wierszu 41. zaznaczone na żółto.
Funkcja nie przelicza się po wyborze nowego koloru z palety. Przelicza się po edycji w arkuszu, po użyciu malarza formatów, po naciśnięciu klawisza F9.

Mappe1-2.xlsm
Pobierz Plik ściągnięto 4 raz(y) 20.94 KB

ID posta: 390248 Skopiuj do schowka
 
 
Gre1234
Starszy Forumowicz


Posty: 41
Wysłany: 28-07-2020, 10:18   

Dzięki!
Moja koncepcja była taka, żeby liczyć godziny robocze w kolumnie D (zwykła suma) ale do momentu który zwróci ta funkcja.
Czyli jeśli znajdzie ostatni element w wierszu E25 to policzę suma($D$2:$D(wynik funkcji dla kolumny E) i później tylko przeciągnąć w prawo.
PS. Jeśli nie będzie nic wypełnione to nie powinno zwrócić 0 ??

PS2. Już chcę to uprościć maksymalnie i znajdować tylko ostatni wiersz i zawsze zwracać adres D(ostatni wiersz).

edycja Zbiniek:
Nie cytuj całej wypowiedzi znajdującej się bezpośrednio powyżej – w takim przypadku wiadomo, że się do niej odnosisz. Cytowanie stosuj tylko wtedy, gdy nawiązujesz do wypowiedzi o kilka postów wcześniej lub gdy komentujesz tylko jakiś fragment ostatniej wypowiedzi. W obu przypadkach zacytuj tylko fragment, do którego się odnosisz.
  
ID posta: 390250 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe