Grafik pionowy - problem podliczania godzin. [SOLVED]

[jozo2009]

Witam Szanownych forumowiczów!

Bardzo liczę na Waszą pomoc.

Tworzę sobie grafik i jako totalny głąb w temacie, do tej pory jestem zadowolony z miernych efektów jakie osiągnąłem.
Mam listę miesięcy zmieniające układ dni w grafiku - wielkim trudem ogarnąłem formatowanie warunkowe oznaczające soboty i niedziele.

Przede mną jeszcze lista świąt, niedziel pracujących itp. które też zamierzam ogarnąć formatowaniem warunkowym (np. święto na czerwono, pracująca na zielono a zwykła też na czerwono). Jednak rzecz nie tego się tyczy.

Nie mogę za Chiny ludowe rozwiązać następującego problemu.

Każda zmiana miesiąca ustawia układ dni. Ja potrzebuję aby pod każdą niedzielą znalazł się dodatkowy wiersz zliczający godziny w danym tygodniu. Wychodzi na to, że taki wierszyk powinien podklejony być zawsze do pola o nazwie niedziela, zawierać formułę SUMA i przemieszczać się po każdej zmianie miesiąca. Mózg mi paruje i nie wiem jak to rozwiązać. Szukam w necie bez skutku. Wiem że to dosyć nieoczekiwane zagadnienie i niebywale trudne do rozkminy takiemu laikowi jak ja.

Załączam jeden grafik "surowy" drugi z kolei jako przykład jaki chciałbym osiągnąć (no i w tym przykładzie zmiana miesiąca powoduje że dodatkowe wiersze i tak pozostają w miejscu). Czy ktoś ma może jakieś pomysły na rozwiązanie zagadki?

Z góry dziękuję za jakieś rady.

[Rafkus]

Można to osiągnąć za pomocą sum częściowych.
Korzystanie z narzędzia sum częściowych
Tylko że sumy częściowe :
*nie działają na obszarze ze scalonymi komórkami,
**niestety w przypadku zmiany daty nie zaktualizują obszaru grupowania automtycznie - trzeba tego dokonać ręcznie.

Aby użyć tego narzędzia, trzeba przede wszystkim określić według jakich kryteriów ma grupować dane, dlatego proponuję dodać do tabeli jeszcze jedną kolumnę w której będzie wyznaczony numer tygodnia (przy pomocy funkcji NUM.TYG(B7;2)). Pod scalonymi komórkami z nazwami osób wstawiłem dodatkowo jeden wiersz (*)

[Jermor]

Także myślałem o sumach częściowych.
Wymyśliłem inne rozwiązanie, ale jego wykorzystanie zależy od tego czy możesz zmienić ogląd tego tematu.
Potrzebne ci sumy zorganizowałem jako odrębną część tabeli. Wszystko zobaczysz w załączniku.
Trochę zmieniłem twoje tworzenie dat do bieżącego miesiąca. Datę tę w komórce A6 wyznaczam funkcją DATA(). Zawiera ona wpudowaną funkcję WYSZUKAJ.PIONOWO(), która na podstawie wybranej nazwy dnia miesiąca, wyznacza jego numer, potrzebny w funkcji DATA(). Komórki w kolumnie B zawierają to samo co A tylko stosują odpowiednie formatowania.
Wyznaczanie daty w kolumnie A zmienia się od wiersza 29. Od tego wiersza sprawdza, czy nie jest to ostatni dzień miesiąca i jeśli wiersz byłby już poza miesiącem wstawia pusty ciąg znakowy.
Dla tych trzech ostatnich wierszy utworzyłem formatowanie warunkowe, które nakłada białe tło i białe ramki na wiersze niewchodzące w skład miesiąca. Dzięki temu widzisz tylko dni danego miesiąca.
Trochę teorii. W miesiącu możesz mieć do 6 tygodni rozliczeniowych. Pierwszy tydzień zaczyna się pierwszego dnia miesiąca i trwa do najbliższej niedzieli, ostatni tydzień trwa od poniedziałku po ostatniej niedzieli do końca miesiąca. Taki układ będziesz miał akurat w lipcu tego roku.
Dlatego zestawienie rozliczenia tygodniowego osób ma 6 pozycji. Do obliczeń wykorzystałem funkcję SUMA.WARUNKÓW(), która sumuje godziny osoby wg numeru tygodnia w miesiącu. Aby to osiągnąć, utworzyłem dwie pomocnicze kolumny (które w końcowym rozwiązaniu możesz oczywiście ukryć)
Pierwsza kolumna (AB) wyznacza, w którym tygodniu roku wypada dzień miesiąca. Druga kolumna (AC) na tej podstawie wylicza, który to jest tydzień w tym miesiącu, i te właśnie liczby stanowią zakres kryterium sumowania.
Kolumna AB zawiera formułę macierzową, ale można wpisać zwykłą formułę =NUM.TYG(A6;2) i powielić ją do wiersza odpowiadającego 28 dniu w miesiącu a tylko w trzech ostatnich dniach miesiąca zmienić ją na =JEŻELI.BŁĄD(NUM.TYG(34;2);0). Ta modyfikacja jest potrzebna, aby formuła nie wyznaczała numeru tygodnia dla dni nienależących do miesiąca. Analogicznie w kolumnie AC na trzech ostatnich pozycjach formuła wymusza wstawienie numeru tygodnia 0, a taki numer nie występuje jako kryterium wyszukiwania. Trzeba było to zastosować, bo twój arkusz po zmianie miesiąca na krótszy niż 31 dni nie kasuje nienałażących wpisów a są one wówczas niewidoczne i mogłyby wprowadzać błędne wyniki.
W przesłanym arkuszu wszystkim komórkom, do których można wprowadzać dane nadałem atrybut komórek niechronionych. Dzięki temu po włączeniu ochrony arkusza nie skasujesz niechcący formuł.
Tu pojawia się problem usuwania danych wpisanych do tabeli przy zmianie miesiąca. Aby to robić w miarę sprawnie,nadałem komórkom przeznaczonym na wpisywanie godzin nazwy, odpowiednio: osoba1, osoba2 itd. do osoba8. Można więc wybrać zakres przez podanie nazwy i nacisnąć klawisz DELETE. Jeśli jak piszesz słabo się orientujesz w arkuszu, to obszar możnawybrać w tym miejscu;

obraz_2023-04-07_201639165.png (14.04 KiB) Przejrzano 2631 razy

Na forum swego czasu zamieściłem post o grafiku. Jest on tu: viewtopic.php?t=5771 są w nim informacje o własnych dniach.

A tu załączam plik z moją propozycją.

[Rafkus]

Myślałem jeszcze troszkę nad twoim problemem i mogę zaproponować jeszcze dwa rozwiązania:

Pierwsze (prostsze) to pod twoim grafikiem dodać w kolejnych wierszach formuły obliczającą sumę godzin z danego tygodnia, można to osiągnąć przy pomocy funkcji SUMA.JEŻELI() (zobacz Arkusz2)

Drugie rozwiązanie jest trudniejsze (formuły bardziej rozbudowane), ale robi to co co chciałeś a więc pod każdą niedzielą będzie pojawiał się "dzień o nazwie Razem". Pomysł jest taki, że przy pomocy funkcji DZIEŃ.TYG jest sprawdzany jaki dzień tygodnia był w wierszu wcześniejszym. Jeżeli to była niedziela to zostanie wpisane słowo "Razem", w przeciwnym razie zostanie wpisany dzień kolejny. Ale uwaga, jeżeli w wierszu powyżej będzie wyświetlany tekst "Razem" to funkcja DZIEŃ.TYG zgłosi błąd. Dlatego formułę trzeba rozwinąć np. do postaci: JEŻELI w wierszu powyżej jest słowo "Razem" to ma pobrać datę znad wiersza "Razem" i zwiększyć ją o jeden, w przeciwnym razie dopiero teraz ma sprawdzić jaki był dzień tygodnia i zwrócić odpowiedni wynik:

Kod: Zaznacz cały

=JEŻELI(C7="Razem";C6+1;JEŻELI(DZIEŃ.TYG(C7)=1;"Razem";C7+1))

Następna komplikacja odbywa się w kolumnie obliczającej ilość przepracowanych godzin w danym dniu. Trzeba będzie w niej sprawdzać czy dany dzień (wiersz) ma nazwę "RAZEM" jeśli tak to będzie miało wtedy obliczyć sumę tygodniową, jeśli nie to różnicę godzin w danym dniu:

Kod: Zaznacz cały

=JEŻELI(C8="Razem";SUMA.JEŻELI($B$7:B7;B7;$F$7:F7);E8-D8)

W załączniku w kolejnych arkuszach przedstawiam oba powyższe rozwiązania, przy czym ten w arkuszu 3 trzeba jeszcze dopracować...

[jozo2009]

Dzięki Rafkus i Jermor! Chociaż dopiero zaczynam rozkminiać to coś mi faktycznie zaświtało dzięki Waszym podpowiedziom.

Rafkus - ostatni plik arkusz 3 to dokładnie o co mi chodzi. działa to pięknie. Walczę nad poprawnym wprowadzeniem
=JEŻELI(C8="Razem";SUMA.JEŻELI($B$7:B7;B7;$F$7:F7);E8-D8)
może dojdę do tego jak dla każdej komórki to określę bo zwykłe przeciąganie formuł nie daje oczekiwanego efektu.
Wpadłem na pomysł, że może połączę dwa systemy w jeden. Zastosuję tabele zliczenia z numerami tygodni pod spodem. Sklecę formułę dla każdej komórki liczącej liczbę godzin na zasadzie sprawdzenia czy wiersz zawiera "Razem" jesli nie to E8-D8, jesli jest "Razem" to komórka równa się komórce z tabeli takiej jak z arkusza 2. Z tym, że muszę się kapnąć jak to uzależnić od numeru tygodnia bo początek mc może być niedzielą i zaraz pod nim podsumowanie i to jeszcze ok bo wiem, że to będzie pierwszy wynik zliczania godzin w num erach tyg. ale dalej może sie to pokomplikować bo zacznie błędnie wybierać mi komórki.

Muszę nad tym przysiąść, i może mi się uda. Dzięki za wsparcie!

[Rafkus]

A, to musisz wstawić odpowiednio znak $ do adresów. Blokuje on odpowiednio zmianę adresów w formule tj: kolumny (jeśli będzie wstawiony przed pierwszą literą) lub wierszy (jeśli będzie wstawiony przed pierwszą cyfrą) - jest to adresowanie względne i bezwzględne.
Do komórki F8 wklej poniższą, poprawioną formułę a będziesz mógł ją przeciągać do kolejnych komórek:

Kod: Zaznacz cały

=JEŻELI($B8="";SUMA.JEŻELI($B$7:$B7;$B7;F$7:F7);E8-D8)

[jozo2009]

Dzięki zadziałało! Pracuję nad grafikiem nadal i pojawiły się następujące rozkminy których od dobrych kilku godzin nie mogę rozwiązać.

Pierwsza jest taka że w arkuszu niedziele i święta mam normy godzin, które chcę sobie importować do komórki C43 i działa to super ale za wyjątkiem czerwca i chyba października gdzie nie wiadomo skąd pojawiają się błędy i złe wartości które wcale nie są tam przypisane do nazwy mc.

Druga - za nic w świecie nie mogę pokolorować tych przeskakujących pod niedziele wierszy, które Rafkus nie wiem jakim cudem stworzył. Formatowania warunkowego już próbowałem na puste komórki na wpis "SUMA" na brak numeru tygodnia i nic.

I gdyby ktoś mógł mi podpowiedzieć jak zmienić formułę w od A38 do A41, żeby tak jak Jermor zrobił, dni nie z danego miesiąca znikały ale pozostał wiersz 41 bo zapomiałem że i tam ma być SUMA na koniec miesiąca.

Dzięki Wam już bliżej ro realizacji projektu grafiku w takim wydaniu. Dzękuję.

[Jermor]

Szanowni @jozo2009 i @Rafkus
Trochę pogrzebałem przy propozycji kolegi @Rafkus, i efekt tych pomysłów przesyłam w wynikowym pliku.
Oto co zmieniłem:

• Zmodyfikowałem generowanie kolejnych dni w miesiącu, w ten sposób, że po ostatnim dniu miesiąca także jest tworzona pozycja "Razem".
• Ilość dni wyświetlanych w tabeli wynosi tyle, ile dni ma miesiąc. Miejsce niewykorzystane objęte jest formatowaniem warunkowym, które ukrywa ten obszar.
• Odblokowałem komórki przeznaczone na wpisywanie godzin oraz komórki na podanie roku i wybór miesiąca.
• Dodałem formatowanie warunkowe dla wierszy zawierających podsumowanie tygodnia. Wykorzystałem styl "zły" ale zmieniłem go tak, aby wszystkie komórki w tych wierszach były chronione.
• Z powodu tego, że położenie ostatniego wpisu zależy od liczby dni w miesiącu, przeniosłem sumaryczną liczbę godzin pod nagłówek z osobami. Sumę obliczam formułą macierzową.
• Zablokowałem pierwsze 7 wierszy, dzięki czemu przy przewijaniu w dół stale widoczna jest górna część.
• Włączyłem ochronę arkusza, dzięki czemu nie można nieopatrznie nadpisać formuł. Ochrona nie ma hasła.
• Do wyznaczania kolejnej pozycji wierszowej w miesiącu użyłem funkcji WARUNKI() (nie ma jej w Apache OpenOffice).
• Zmodyfikowałem formułę obliczania liczby przepracowanych godzin. Przez wykorzystanie funkcji PRZESUNIĘCIE() można te formuły kopiować w dowolne dalsze pozycje z osobami. Zachowana musi być jednak zasada, że kolejne sumowanie wypada o trzy kolumny dalej.

[Jermor]

Odpowiadam ci na niewłaściwe liczby norm godzinowych. Funkcja WYSZUKAJ() wymaga, aby wektor przeszukiwany był posortowany.
Zatem albo posortuj tę tabelkę rosnąco wg nazw miesięcy, albo użyj funkcji WYSZUKAJ.PIONOWO() z czwartym argumentem równym 0.

[jozo2009]

Jermor - OMG - rozwaliłeś bank - dokładnie o to chodzi! Czytam sobie teraz Twoje formuły ponieważ zrobiłeś to czego post wyżej nie mogłem, i nie wierzę, że mogą one być niekiedy takie proste. A po czasie postu mniemam, że czytasz w myślach lub rozumiesz jaki jest cel finałowy jaki sobie zadałem. SUPER. Jeszcze raz dzięki.

[Jermor]

Zmodyfikowałem ten mój plik.

• Dołożyłem, na poziomie sumarycznych godzin miesięcznych, normę dla danego miesiąca.
• Dodałem formatowanie warunkowe, wyróżniające dni świąteczne na czerwono.
• Dodałem formatowanie warunkowe, wyróżniające niedziele robocze na niebiesko.

[Jermor]

Poprzełączałem się na wszystkie miesiące w 2023 roku i nigdzie błąd nie wystąpił.
Możesz dokładniej opisać?

[Rafkus]

Ups, usunąłem moją poprzednią wypowiedź do której odniósł się @Jermor, pisałem w niej, że w dostarczonym pliku widzę błędy.

Formuła warunki nagle zwraca błąd, ciekawe że zawsze po pierwszym poniedziałku dla dowolnego miesiąca i roku. Przypuszczam że to coś w moich ustawieniach, próbowałem formułę z mniejszą ilością warunków i również odmawia posłuszeństwa. Reset profilu użytkownika nie pomógł wiec chyba spróbuję jeszcze ściągnąć nowszą wersję.

errorWarunku.png (58.76 KiB) Przejrzano 2527 razy

Edytowano:
Zainstalowałem nową wersję LO (zmiana z 7.1.6 ---> 7.4.6) i błąd znikł.

[jozo2009]

U mnie tabela działa bardzo dobrze. mam pytanie jeszcze, jak do formuły w kolumnie np. F
=JEŻELI($C8="Razem";SUMA.JEŻELI($B$8:$B$43;$B7;PRZESUNIĘCIE($F$8;0;3*ZAOKR((KOLUMNA()-KOLUMNA($F$8))

dopisać dodatkową zależność:

jeżeli w polu D8 tekst "L4" to F8 przyjmie wartość 8, lub "U" wartość 8 a np. inny dowolny tekst komórka ma wartość pustą"" - próbowałem ale wywala błedy i zliczanie godzin w miesiącu,

Inne pytanie to co może zrobić aby np. Open Office wyświetlał tabelę poprawnie.

Dzięki za pomoc.

[Jermor]

• W moim rozwiązaniu zastosowałem funkcję WARUNKI(), której nie ma w Apache OpenOffice. Zastosowana funkcja jest zgodna z analogiczną funkcją w Excelu. Można ją zastąpić złożoną kombinacją wielu funkcji JEŻELI(), wówczas zadziała to w Apache OpenOffice.
• Zmieniłem sposób obliczania sumarycznych godzin w miesiącu. Poprzednio nic nie pisałeś o możliwości wstawiania symboli do komórek określających początek i koniec zmiany. Dlatego skorzystałem z formuły macierzowej, która dodawała różnice czasowe. W wierszu Razem taka różnica nie jest wyliczana. Teraz wstawiłem sumowanie warunkowe tych komórek, które znajdują się w wierszu "Razem".
• Przy okazji zauważyłem, że w przesłanym pliku należy zmienić kolejność formatowania warunkowego, bo nie są ukrywane końcowe dni. Zrobiłem to i załączona wersja już zawiera tę zmianę.
• W apache OpenOffice można zdefiniować jedynie trzy warunki formatowania warunkowego dla tego samego obszaru. Kiedyś to sprawdzałem, że jeśli przeniesie się plik z LibreOffice z wieloma warunkami, to Apache to 'kupuje" ale już poprawianie było problemem.
• W formule, umownie pisząc kolumny F, zmieniłem formułę końcową. Dotychczas była tam formuła "E-D", teraz proponuję użycie funkcji JEŻELI() w połączeniu z funkcją REGEX() (tej także nie ma w Apache OpenOffice i nie ma jej czym zastąpić) ta formuła sprawi, że wpisanie do dowolnej komórki w kolumnie E lub D (i analogicznie tych dla kolejnych osób) wpisów: "u", "U", "l4" lub "L4" sprawi, że w kolumnie wyliczonych godzin wstawione zostanie 8, w pozostałych przypadkach wyliczona zostanie różnica.

[jozo2009]

To już jest wyższa matematyka. Dzięki za pomoc.

A jeśli chciałbym mieć więcej warunków ..JEŻELI(CZY.BŁĄD(REGEX(E8&D8;"u|U|l4|L4"));E8-D8;8)) w nowo dodanej części formuły czyli wprowadzenie określonego np N skutkowałoby pustym polem bądź zerem, bo każda inna wartość słowna powoduje błędy. Powielenie tej formuły z nowymi parametrami też daje błąd. Nie wiem czy to ma być w tej części czy stanowić inną partię warunków.

Nie pisałem o możliwości wstawiania symboli do komórek określających początek i koniec zmiany ponieważ podczas prac nad grafikiem zdałem sobie sprawę, że są sytuacje z góry określające godziny za dzień i można to wyrazić w taki sposób.

[Jermor]

Pytanie jest takie, po co wprowadzać jakieś symbole do grafiku, skoro mają one wygenerować zero. Obecnie gdy pole E i D jest puste, wynikiem jest zero. Chyba że okazuje się, iż grafik ma zawierać takie symbole, które będą służyły do innych celów. Wówczas jednak należy z góry je określić, aby napisać kompletną formułę.
Użyłem funkcji REGEX() bo najłatwiej było mi uwzględnić fakt wpisania symbolu do dowolnej komórki E lub D co zawsze może się przytrafić przy szybkim działaniu, lub nieuwadze. Ponadto funkcja umożliwia uniezależnienie się od tego czy użyto wielkich, czy małych liter.
Zamiast tego możesz użyć funkcji WARUNKI(). Jest łatwa w zastosowaniu.
Jej ogólna postać to; WARUNKI(test 1; działanie 1;test 2;działanie 2;...;test n-ty;działanie n-te)
Funkcja wykonuje testy od lewej do prawej. Pierwszy napotkany test z wynikiem PRAWDA, powoduje wykonanie przylegającego działania i dalsze sprawdzanie nie jest już kontynuowane.
Zatem w twoim przypadku, tam, gdzie przed moją modyfikacją było napisane E-D, możesz wprowadzić sekwencję:
WARUNKI(E8="U";8;E8="L4";8;E8="N";0;E8="COŚ";3234;1;E8-D8)
Ten zapis oznacza: jeśli E8="U" wstaw 8. Jeśli E8 nie równa się "U" sprawdź następny warunek. itd. Ostatnim testem jest wpisana liczba 1, która w tej sytuacji (wyrażenia logicznego) oznacza PRAWDA, a to oznacza, że jeśli żaden z poprzednich warunków nie zostanie spełniony, zostanie wykonane to ostatnie działanie.
O tego typu funkcjach warunkowych poczytać możesz np. tutaj: https://yestok.pl/lbo/y63.php

[jozo2009]

Popracowałem i dopracowałem ten grafik wraz z Twoimi Jermor uwagami i podpowiedziami. Działa dokładnie jak powinien. Mam nadzieje, że już nic mi innego nie przyjdzie do głowy

Wielki szacun i podziękowania za wsparcie!

Pozdrawiam.

[jozo2009]

Witam

Pracuję sobie na grafiku i rozwijam go nadal. Stworzyłem automatyczne różne arkusze m.in stronę która pod pracownikiem wyszukuje wpisu UW i o ile działa mi =INDEKS(DATA;PODAJ.POZYCJĘ("UW";$Grafik.D8:$Grafik.D44;0)), jako pierwszy wpis wyświetlony jako data rozpoczęcia urlopu to za nic nie potrafię skonstruować formuły na ostatni wpis "UW". Resztę sobie poradziłem ale z tym są cyrki. Działa to pięknie w formie =INDEKS(DATA;PODAJ.POZYCJĘ("UW";$Grafik.D8:$Grafik.D44)) - bez zera jako dokładnego wyszukiwania ale jak nie ma w tej kolumnie innych wpisów zawierających dowolne litery.

Pozdrawiam
wszystkich!

[Jermor]

Tak łatwo to nie będzie. Każda funkcja wyszukująca znajduje tylko pierwsze wystąpienie, gdy ma nastąpić dokładne dopasowanie albo najbliższe poniżej/powyżej gdy przeszukiwane dane mają być traktowane jako uporządkowane.
Z urlopami i zwolnieniami jest ten problem, że pracownik może mieć w miesiącu kilka razy po parę dni urlopu lub kilka zwolnień.
No ale jeśli zakładasz, że urlop jest jeden raz w miesiącu, to możesz wyznaczyć datę kiedy się zaczął, a kiedy skończył. Nie znam się na prawach urlopowych i nie wiem, czy sobota i niedziela są wliczane do urlopu, czy nie. Jeśli jednak chcesz mieć datę kiedy urlop się zaczął, to otrzymasz ją z takiej formuły:

Kod: Zaznacz cały

=MIN.WARUNKÓW(DZIEŃ(A8:A44);D8:D44;"uw")

a skończył się

Kod: Zaznacz cały

=MAKS.WARUNKÓW(DZIEŃ(A8:A44);D8:D44;"uw")

Natomiast pozycję w grafiku otrzymasz wpisując odpowiednio:

Kod: Zaznacz cały

=PODAJ.POZYCJĘ(DATA(ROK(C8);MIESIĄC(C8);MIN.WARUNKÓW(DZIEŃ(A8:A44);D8:D44;"uw"));C8:C44;0)
=PODAJ.POZYCJĘ(DATA(ROK(C8);MIESIĄC(C8);MAKS.WARUNKÓW(DZIEŃ(A8:A44);D8:D44;"uw"));C8:C44;0)

Wszystkie formuły są formułami macierzowymi, więc musisz je zatwierdzić SHIFT+CTRL+ENTER

[jozo2009]

Dzięki!

Bardzo dobrze to działa. Zmodyfikowałem jedynie pod INDEKS(DATA;.... i działa wyśmienicie.

Pozdrawiam!