Prośba o formułę liczącą czas

[autoregio]

Witam,

Proszę o formułę sumującą czas w zakresie komórek wg załącznika, gdzie literom D i N odpowiadać będzie 12 godzin,+ 1 dyżur dodatkowy wpisywany ręcznie w formacie czasowym, np 3:15

[Smaigas]

Jakoś tak
Pozdrawiam

[Jermor]

Jak się domyślam będzie to jakiś miesięczny grafik dyżurów.
W sytuacji pracy z wartościami dotyczącymi czasu warto zapoznać się ze sposobem jego reprezentowania w arkuszu i zasadami prezentacji na ekranie i wydrukach.
Co należy zmienić (moim zdaniem)?
Wszystkie komórki grafiku odpowiadające dniom w miesiącu muszą mieć przypisany format HH:MM. Teksty wpisane do komórek z takim formatem będą i tak prezentowane jako teksty, natomiast liczby jako godzina i minuta przy czym należy zawsze pamiętać o dwukropku (np. wpisanie liczby 3: wyświetli 3:00).
Litery N i D odpowiadają prawdopodobnie symbolom Dyżur Nocny, Dyżur Dzienny, a 12 godzin to w rozumieniu arkusza kalkulacyjnego wartość 0,5.
Dyżury dodatkowe należy wpisywać dokładnie w postaci "liczba godzin:liczba minut". Ponieważ komórka ma format HH:MM wprowadzenie np 4:20 wyświetli taką właśnie postać tej wartości.
Tylko ostatnia komórka grafiku, sumująca czas dyżurów, musi mieć format ustawiony tak: [HH]:MM
W efekcie można w niej zbudować następującą formułę:

Kod: Zaznacz cały

=(LICZ.JEŻELI(B4:AF4;"=N")+LICZ.JEŻELI(B4:AF5;"=D"))*0,5+SUMA(B4:AF4)

Formuła oblicza ile razy w miesiącu wystąpiła litera N oraz litera D i sumaryczna wartość mnoży przez 0,5 (co w dziedzinie czasu odpowiada 12 godzinom) a następnie dodaje do tego sumę wartości liczbowych znajdujących się w wierszu grafiku. Tymi wartościami będą tylko dodatkowe dyżury (bo litery mają wartość 0).
W końcu suma zostanie wyświetlona z formatowaniem o jakim napisałem powyżej, [HH]:MM, co oznacza, że wyświetlany wynik ma odpowiadać łącznej liczbie godzin.
W załączeniu podsyłam przykład. W odróżnieniu od propozycji Smaigasa nie zawiera on innych arkuszy roboczych.
Ponadto zmodyfikowałem go następująco:
W komórce A1 należy wpisać rok.
w komórce A2 numer miesiąca.
Wówczas tabela dni wypełni się samodzielnie, ponadto pod numerem dnia pojawi się nazwa dnia tygodnia.
Dodałem także formatowanie warunkowe, które powoduje, że soboty są kolorowane na zielono a niedziele na czerwono..
Arkusz można potraktować jako szablon i tworzyć grafiki na dowolny miesiąc.

[Jermor]

Dodaję ponownie przykładowy plik. Znalazłem w nim niestety błąd w formule sumującej. Usuwam wersję z poprzedniego postu.
Dodatkowo uzupełniłem arkusz o kontrolę wprowadzanego roku, dopuszczając tylko rok bieżący lub następny; kontrolę numeru miesiąca, dopuszczając tylko liczby całkowite od 1 do 12.
Ponadto dodałem formułę w komórce A3 wyświetlającą nazwę miesiąca.
Komunikat z ostatniego wiersza nie zostanie wydrukowany. Orientacja arkusza - pozioma.
Arkusz jest chroniony hasłem, ale hasło nie jest ustawione.

[autoregio]

Dziękuję kolego Jermor - jak zwykle niezawodnie. Tym razem przyczyniasz się do sprawniejszego funkcjonowania systemu ratownictwa medycznego i chwała Ci za to )))
Czy mógłbym Cię prosić o zmodyfikowanie formuły w ten sposób, aby wartości 12 odpowiadały oprócz D i N, także symbole: DS, DU, DCH, NS, NU, NCH ?

[Jermor]

Z prośby o modyfikację wynika, że każdy z dodanych symboli odpowiada 12-stu godzinom i że innych symboli znakowych nie będzie.
Dlatego formułę obliczająca zmodyfikowałem do postaci:

Kod: Zaznacz cały

=LICZ.JEŻELI(B4:AF4;">=a")*0,5+SUMA(B4:AF4)

co oznacza, że jeśli w komórce jest jakikolwiek tekst, to odpowiada on wartości 0,5 (12-stu godzinom).
Poprawiony grafik pod zmieniona nazwą załączam.

[autoregio]

Wspaniała robota. Dziękuję

[autoregio]

Kolejna prośba z mojej strony dotycząca modyfikacji grafika pracy. Dotyczy ona sumowania komórek, które nie będą wypełnione kolorem. W załączniku mam rozpisane przykładowe dyżury, oznaczone D i N, na samym dole sumuję obsady Noc i Dzień. Chodzi mi o to, że kiedy wypełnię kolorem czerwonym lub niebieskim komórkę z D lub N, nie była ona sumowana w obsadach na dole (pracownik nie liczy się do obsady, ponieważ jest na urlopie (wypełnienie niebieskie lub zwolnieniu chorobowym - wypełnienie czerwone) Faktycznie musi być wpisany alby liczyć mu czas pracy, ale fizycznie nie będzie go w zespole

[autoregio]

Wiem, że funkcja LICZ.Jeżeli nie zadziała, czy potrzeba coś jak VBA? Znalazłem temat o sumowaniu kolorów, ale tutaj potrzebuję, żeby sumowało niekolorowe komórki (a niesumowało D i N w kolorze), a dodatkowo aby została funkcja licz.jeżeli sumująca D i N

[belstar]

Znalazłem temat o sumowaniu kolorów, ale tutaj potrzebuję, żeby sumowało niekolorowe komórki

Kod: Zaznacz cały

=SUMA(Wszystkie komórki) - SUMA(kolorowe komórki)

[autoregio]

Znalazłem temat o sumowaniu kolorów, ale tutaj potrzebuję, żeby sumowało niekolorowe komórki

Kod: Zaznacz cały

=SUMA(Wszystkie komórki) - SUMA(kolorowe komórki)

Ok, dziękuję, tylko jak połączyć tą formułę z wprowadzoną przeze mnie funkcją LICZ.JEŻELI:D,N=12 ??

[Jermor]

Zastanawiam się czy nie lepiej wprowadzić dodatkowe oznaczenia: Du (dzień urlop) Dc (dzień choroba) i analogicznie z literą N.
Jak liczyć takie wartości już wiesz. Natomiast w arkuszu wstawić formatowanie warunkowe uzależnione od symboli.
Formalnie formuła licząca mogłaby wyglądać tak:

Kod: Zaznacz cały

=LICZ.JEŻELI(C3:C22;"D.*")-LICZ.JEŻELI(C3:C22;"D.")

Zwróć uwagę na specyficzny zapis "D.*" (litera D, kropka, gwiazdka) oraz "D." (litera D, kropka).
Są to użyte wyrażenia regularne. Pierwsze oznacza, że zliczone mają być wszystkie wyrażenia zaczynające się literą D. Kropka sygnalizuje,że może po niej wystąpić jakiś znak a gwiazdka, że tych znaków może być zero albo dowolnie dużo. Dzięki temu zsumowane zostaną zarówno wyrażenia jednoliterowe jak i wieloliterowe. Drugie oznacza, że zliczone maja być te wyrażenia, które są dwu znakowe a pierwszym znakiem jest litera D. Ten zapis mówi bowiem, że musi to być litera D po której wystąpi jakiś znak (w tym przypadku literka c albo u).

[belstar]

Zrób tak jak ci Jermor radzi, tym sposobem wykorzystasz narzędzia dostępne w calcu standardowo. Przy zliczaniu komórek pokolorowanych, musisz napisać odpowiednią funkcję UDF używając StarBasica. Ponadto obliczenia oparte na kolorze komórek nie są bezpiecznym rozwiązaniem i pewno dlatego twórcy calca nie oferują narzędzi łatwo dostępnych dla użytkownika.
Kiedyś jeszcze w świecie Excela sam stosowałem takie rozwiązania (inni dalej to stosują), lecz dziś już bym tak nie zrobił.

[Jermor]

Jeszcze raz przeczytałem swój post. Rozwiązanie jest nadmiarowe, niektóre obliczenia są niepotrzebne. Przecież chcesz policzyć tylko tych którzy mają wpisane D. Więc wystarczy Twoja stara formuła:
LICZ.JEŻELI(C3:C22;"D")
Natomiast do wyróżnienia komórek wykorzystaj formatowanie warunkowe.
Zaproponowane wyrażenia regularne możesz wykorzystać do zliczania czasu pracy, skoro muszą być w nim uwzględnieni także chorujący i urlopowicze.

[autoregio]

Witam,

Dziękuję wszystkim za rady, być może przedobrzyłem z tym kolorowaniem. Chodzi o to, że grafik występuje również w wersji papierowej, gdzie wszystko to trochę inaczej wygląda. Chciałem go podwiesić również w intranecie i pomyślałem, że będzie estetycznie i przejrzyście...ale zostanie jednak DU, DCH, niech się pracownicy przyzwyczają )

[Jermor]

Nic nie stoi na przeszkodzie byś komórkom dodatkowo przypisał formatowanie warunkowe, uzależnione od zawartości "DU" lub "DCH"

[autoregio]

Nic nie stoi na przeszkodzie byś komórkom dodatkowo przypisał formatowanie warunkowe, uzależnione od zawartości "DU" lub "DCH"

Dziękuję, tak też mam zrobione.

[Husar]

Temat już chyba wyczerpany, ale o coś takiego chodziło autorowi?

[autoregio]

w przybliżeniu tak, chociaż nie w sposób aż tak rozbudowany...w naszej firmie mamy dedykowany specjalny moduł kadrowy, mój szablon koledzy tworzyli na węższe potrzeby