Formatowanie warunkowe excel

Question

Witam mam grafik z ewidencją czasu pracy, dni ustawowo wolne (niedziele, święta itp.) są oznaczone kolorem czerwonym i x w komórce, formuła która to f...

walek33 · Accepted Answer

A co mi tam. Jak szaleć to szaleć z formatowaniem. Myślę, że przykład w pliku Cię zadowoli. Nie mam tak poważnego narzędzia jak Ty ale do zabawy z xlsx-ami używam OO. Mam nadzieję, że nic nie namotał (a czasem potrafi) :cry:

Ostatnio złapałem go na oszustwie z datami. Na tej samej dacie pomiędzy OO a MsO jest dwa dni. :cry:

Ale do tematu. Formatowanie warunkowe polega (w tym przypadku) na zmontowaniu daty z wiersza nagłówkowego tabelki i pierwszej kolumny i wyliczenie, który to dzień tygodnia. Rok dodałem ręcznie, ale możesz go pobrać z którejkolwiek komórki arkusza. Masz dwa kolory na dni żółty sobota, czerwony niedziela i jeden na dni nienależące do miesiąca (to siwy). Jak się uprzesz to możesz dodać jeszcze kolorek na święta państwowe, kościelne, partyjne i jakie tam sobie jeszcze wymyślisz. Dopracowanie pozostawiam...
potomnym.

marek003 · Accepted Answer

Przyznam że trochę mnie drażni że za takie "badziewie" (piszę o głównym pliku) ktoś żąda pieniędzy i to 30 zł. Jeszcze żeby to miało właśnie wbudowany (automatyczny) kalendarz ze świętami w tym wielkanocą, albo by liczyło nadgodziny (choć w tym układzie ktoś w ogóle tego nie przewidział by tu był jakikolwiek automat)
To jest tylko zwykłe liczydło - taka kartka papieru z sumatorem.

Z drugiej strony jeżeli ktoś chce takie rzeczy kupować to czemu nie, ale ja wstydziłbym się proponować coś takiego za pieniądze.

Szkoda że autor topiku nie odwiedził forum wcześniej. Było tysiące wątków liczących czas pracy itp.

Powracając do głównego zagadnienia wątku.
Rok jest w komórce AE2 pozwoliłem sobie zmodyfikować nieznacznie formatowanie wprowadzonego przez kolegę walka33 (o czym właściwie pisał)

Druga rzecz trzeba poza kolorami pamiętać o tym żeby wstawiać w nich "x" bo inaczej nie policzą się "Dni ustawowo wolne od pracy". (Dziwaczne podejście głównego twórcy arkusza ... ale... )

Wprowadziłem "automat" liczący nadgodziny. Trzeba tylko wpisać dla danego pracownika jaką ma dniówkę 8 czy 12 godzinną a może inną (dodałem oznaczoną komórkę przy nazwisku za nr ewidencyjnym).
Obliczenia te nie są tak na wprost bo jeżeli raz pracowałeś 14 godzin a w kolejnym dniu tylko 10 to nadgodziny ci się nie należą. (Co prawda nie wiem jak jest u ciebie ale standardowo tak z jest).
A co z nocnymi ???
Ręce opadają.

---------
Cały czas nie mogę się nadziwić... Jak bym z czymś takim (tak prostym arkuszem) wyskoczył do naszej kadrowej to by mnie wyśmiała bo nie po to ma kombajn w postaci excela by cos jeszcze samemu liczyć.

marek003 · Accepted Answer

Nie cytuj siebie tylko zmieniaj - nie będzie powtarzać.

steb0 wrote:
...Zresztą to nie mój kalendarz ...

Jeżeli byś umiał sam "wpaść" na obliczenie daty wielkanocy zapewne nie pytał byś się jak co robic w excelu

A kalendarz jak kalendarz do najbliższych lat może być. Jednak tak prosta funkcja wyliczająca wielkanoc ma pare błedów np rok 2079 pokazuje 16.04 a powinna 23.04; w 2204 pokazuje 15.04 a powinna 22.04; w 2207 pokazuje 12.04 a powinna 19.04 No ale to chyba dla tego pliku za daleka przyszłość

Przecież masz napisane gdzie wprowadzić zakres ze świętami [jest pogrubione miejsce gdzie].

Tak, dla każdego miesiąca formuła jest trochę inna ale wystarczy zblokować odpowiednio adres komórki i przeciągnąć w dół.

Do wartości roku skorzystałem z daty wpisanej dla pracownika

Docelowo uważam że w D3 arkusza dane powinna być formuła (jak już otworzysz cały skoroszyt z pracownikami):
=LEWY(lista'!E2;4)
wtedy wybierze rok z już gdzieś wpisanego (2012 r.).

marek003 · Answer

Powiem że trochę "nieodpowiedni" warunek w formatowaniu - taka trochę abstrakcja. [nie wiadomo jakie masz dane].

Zgodnie z tym co przedstawiłeś (trochę się domyślam i mogę się mylić) czerwony kolor komórki pojawia się wtedy gdy wpiszesz w niej "x". Więc co się dziwisz że jak wpiszesz godzinę to tracisz format?
A na marginesie, jeżeli jest to prawda z tym x i czerwienią, to formuła w warunku =licz.jeżeli() jest nieodpowiednia. Innymi słowy aż dziw że działa - przecież wystarczyło dać warunek ="x" proste, jasne i na pewno działa.

Ponieważ nie wpisujesz x a wpisujesz godziny tracisz format. Teraz jest pytanie (nie znam twojego arkusz) Po czym ma excel poznać że w danej komórce jest niedziela? Masz przynajmniej daty obok albo słowo Niedziela? Załącz przykład będzie prościej coś doradzić (do czego przyporządkować formatowanie warunkowe).

Co do drugiej sprawy: Da się (np za pomocą licz jeżeli() i suma.jeżeli() sumujesz wszystkie powyżej 12 godzin i odejmujesz wynik mnożenia licz.jeżeli [powyżej 12] razy 12 i masz łącznie nadgodziny)

steb0 · Answer

Dziękuje za zainteresowanie się tematem. W sumie nie jestem pewny czy dobrze się wyraziłem nie jestem biegły w tej materii. Nie wiem również czy mogę udostępnić arkusz ponieważ został zakupiony ze strony: http://www.excel.edu.pl/ark1.html, ale skoro zapłaciłem to chyba stałem się jego właścicielem więc fragment załączam. Chodzi o ostatnią komórkę „dni ustawowo wolne” nie wiem jak zmienić formule Code: text Expand Select all Copy to clipboard=LICZ.JEŻELI(C6:AG6;$AV$5)  aby w niedziele które pracuje zachował formatowanie komórki. Kombinowałem ze zliczaniem kolorowych komórek korzystając z innego wątku:Code: text Expand Select all Copy to clipboardFunction LiczKolory(zakres As Range, kolor As Integer)
For Each kom In zakres
  If kom.Interior.ColorIndex = kolor Then
    LiczKolory = LiczKolory + 1
  End If
Next
End FunctionAle poległem Co do nadgodzin to wiem jak to zrobić z jedną komórką ale wysiadam z zakresem C6:AG6 . Zamówiłem książkę http://helion.pl/ksiazki/excel-2010-pl-biblia-john-walkenbach,ex21bi.htm mam nadzieje że mi rozjaśni w głowie.

Anonymous · Answer

Przy takim sposobie rozmieszczenia proponuję Ci zrezygnować zupełnie z formatowania warunkowego. Ustaw tło i już.

steb0 · Answer

Bardzo wam dziękuje! Oto mi chodziło, to wszystko jest bardziej skomplikowane niż przepuszczałem tym bardziej że niemiałem do tej pory do czynienia z programami biurowymi (prywatnie wystarcza mi LaTeX).
Została tylko kwestia zliczania tych kolorowych komórek do ostatniej kolumny żeby nie trzeba było wykorzystywać tego nieszczęsnego X, przeglądam od paru dni internet i to szacowne forum i wszystkie rady okazały się nieskuteczne w moim przypadku, może mógłby mnie z któryś z kolegów naprowadzić pod jakim kątem mam szukać informacji?
Jeszcze raz bardzo dziękuje zaoszczędziliście mi mnóstwo czasu którego jest taki deficyt

marek003 · Answer

Co żeś się "czepił" kolorowych komórek Trzeba normalnie policzyć dni wolne i tyle.
Nie wiem czy w nowszej wersji excela jest juz formuła "dni wolne" wale w starej jest dni pracujące pod "Networkdays()" i dzięki niej można łatwo policzyć ile jest wolnych
Na początku licze dni danego miesiąca (od daty końca odejmuję date początku) i odejmuje dni pracujące w danym miesiącu i mam:

=(EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)-DATA(LEWY($AE$2;4);B6;1)+1-NETWORKDAYS((DATA(LEWY($AE$2;4);B6;1));EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)

Dodając jeszcze zakres stworzonej gdzieś (może być w innym arkuszu) tabelki z datami świąt w tym miejscu formuły [razem ze średnikiem]:

=(EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)-DATA(LEWY($AE$2;4);B6;1)+1-NETWORKDAYS((DATA(LEWY($AE$2;4);B6;1));EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1;Tu_zakres)

Będziesz miał wyliczone wszystkie dni ustawowo wolne.

Ponizej przykład ale bez świąt bo nie wiem gdzie je docelowo wrzucić tabelkę.

walek33 · Answer

A propos liczenia dni wolnych. Biegając po różnych źródłach i ćwicząc zabawę w arkuszach (podkreślam, tylko zabawę, bo twierdzę zawsze, że laik jestem) przypomniała mi się ulubiona funkcja Suma.Iloczynów() którą także można zaprząc do obliczenia dni roboczych w określonym przedziale czasu. Założenia są następujące:
L2 - data początkowa
L3 - data końcowa
L4:L5 - przykładowy zakres świąt
a formułka wygląda następująco:

=SUMA.ILOCZYNÓW(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(L2&":"&L3)))<>7);--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(L2&":"&L3)))<>1);--(LICZ.JEŻELI(L4:L5;WIERSZ(ADR.POŚR(L2&":"&L3)))=0))

Prawda, że prosta i miła w obsłudze? Ale czasem liczy lepiej niż wbudowana funkcja.

To było tylko jako ciekawostka. Nie ma przymusu stosowania.

Quote:
Przyznam że trochę mnie drażni że...

Ja rzekłbym, że nie tylko trochę. Ale podaż ustanawia popyt (czy może odwrotnie?)

marek003 · Answer

Sprytne i ciekawe zastosowanie i ... nie do końca rozumiem. Kurcze ... gdzie logika ... chm... Niemiej podstawiając na sztywno dane w tym przypadku gdzie trzeba wyliczyć początek i koniec okresu (a koniec na podstawie początku) trochę się zagmatwa. Poza tym od razu trzeba by było liczyć dni wolne [ =7 i =1 i <>0] a nie robocze. Czyli: =SUMA.ILOCZYNÓW(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(DATA(LEWY($AE$2;4);B6;1)&":"&EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)))=7))+SUMA.ILOCZYNÓW(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(DATA(LEWY($AE$2;4);B6;1)&":"&EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)))=1))+SUMA.ILOCZYNÓW(--(LICZ.JEŻELI(L4:L5;WIERSZ(ADR.POŚR(DATA(LEWY($AE$2;4);B6;1)&":"&EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)))<>0)) Ale uważam że bardzo INSPIRUJĄCE zastosowanie ulubionej ostatnio funkcji . Możesz wyjaśnić (nakierować na) te "--" i dlaczego to liczy? Jak adres pośredni z daty, co do tego mają wiersze, kurcze kto na to wpadł, jak to liczy? ŁAŁ walek33 Nakierujesz rządnego wiedzy drugiego laika na jakieś źródła wiedzy i wyjaśnień lub uchylisz tej wiedzy z własnych zasobów?

Anonymous · Answer

licz.warunki() ?

marek003 · Answer

Przepraszam ale dalej nie rozumiem

Co ma funkcja (z 2007 i wyżej) licz.warunki() do dwóch minusów (--)

Dodam że do tej pory rozumiałem funkcję suma.iloczynów() nawet z zastosowaniem jej do właśnie wielowarunkowych obliczeń.

Chodzi o co innego.

Do tej pory wiedziałem że daty są kolejnymi liczbami (1 dla 1900.01.01 i tak dalej) a teraz się okazuje (moja niczym nie uzasadniona dedukcja) że robiąc wirtualny zakres poprzez adres pośredni od daty do daty tworze jakąś "wirtualną" tabelę z wierszami na konkretne daty do których się niby mogę odwoływać (sprawdzać je) i przyjmując że to jest prawda nie rozumiem konieczności zapisu -- przed formułą. Bez tego nie liczy a jak jest potrzebne to co to oznacza?
Gdzie mogę o tym poczytać. Bo nie lubię używać czegoś tylko dlatego że ktoś tak zrobił. Wolał bym to zrozumieć (nie mówię że zrozumiem ale spróbuje).
Może to jakiś zabieg - może właśnie o jakiś wirtualnych tabelach czy o czymś tak jak funkcje tablicowe z {} [co prawda w tym przypadku te znaki się same robią]

steb0 · Answer

Wszystkim dziękuje za pomoc już wszystko działa jak powinno.

Anonymous · Answer

Oj, to ja przepraszam. To było pytanie a nie stwierdzenie.Wpisując "--" w pole funkcji pokazuje się po prostu informacja, ze to licz.warunki, ale nie potrafię powtórzyć wyniku obliczenia stosując "pisane" licz.warunki.Wczoraj przekopałem sieć i kilka ogólnych książek i kursów dot. excela i nie znalazłem śladu o "--". Nie mam pojęcia co to jest i dlaczego liczy.

Aldrin · Answer

zerknijcie tutaj:http://www.mrexcel.com/forum/showthread.php?t=75397http://www.pcreview.co.uk/forums/excel-double-negatives-past-present-and-future-t973029.htmlhttp://www.coderscity.pl/sutra94552.htmlTe minusy:

Anonymous · Answer

Jakoś marnie szukałem najwyraźniej Niestety dalszej części formuły nadal nie mogę załapać

walek33 · Answer

A coś takiego Ci pomoże?

=MODUŁ.LICZBY(SUMA.ILOCZYNÓW(-(DZIEŃ.TYG(WIERSZ(ADR.POŚR(L2&":"&L3)))<>7);-(DZIEŃ.TYG(WIERSZ(ADR.POŚR(L2&":"&L3)))<>1);-(LICZ.JEŻELI(L4:L5;WIERSZ(ADR.POŚR(L2&":"&L3)))=0)))

Z góry uprzedzam, że formuła nie jest moim osobistym wymysłem.

Anonymous · Answer

To wyszło mi prawie samo z siebie, próbuje połapać się w reszcie

walek33 · Answer

Ale już pozbyłeś się podwójnej negacji.

Anonymous · Answer

No pozbyłem się negacji

Najśmieszniejsze, że szukałem i kombinowałem ze wszystkim oprócz "minusów". Pewnie gdybym wpadł, że to są "dwa minusy" byłoby łatwiej.

Całą reszta na razie jest mglista, bo nigdy nie używałem adr.pośr i odwołań typu L2&":"&L3.

Dzisiaj nie mam już na to czasu, ale trzeba się wgłębić

walek33 · Answer

Coby Ci uprościć (lub nie) przemyślenia dodam że tą formułką można liczyć (przynajmniej w Ex97) tylko do 2079-06-05 następnego dnia się wyłoży.

marek003 · Answer

Może M$ wie więcej na temat naszego końca A poważniej też zaraz spróbuję poeksperymentować.

walek33 · Answer

Trochę Cię naprowadzę. Data końca ze względu na WIERSZ() jest ograniczona przez długość arkusza.
To ona tworzy wirtualną tablicę kolejnych liczb całkowitych, które wartością równe są kolejnym datom.
A te wartości powstają dzięki takiemu adresowaniu pośredniemu. Podobno jeden obraz znaczy więcej niż tysiąc słów.

Anonymous · Answer

Taaa, laik

marek003 · Answer

Właśnie przed chwilą na to samo "wpadłem" (poprzez to że "graniczna" data 2079-06-05 to cyfra 65536 czyli maks wierszy dla 2003 i w dół - dla 2007 i w górę może być więcej)

Czyli to "zwykła" zabawa liczbami i sprytne (a nawet bardzo sprytne) przechodzenie z "systemu" dat na liczby i odwrotnie. A nie jakieś ukryte funkcje

.

Bo przecież

Więc później wyciągnięcie z tego liczby reprezentującej datę poprzez wiersz() i podstawienie jej pod jedną z funkcji czasu np dzień tygodnia(reprezentant daty w postaci liczby) to juz pikuś.

Kurcze ktoś nieźle to wymyślił.

A co do "--" czyli zaprzeczenia negacji czy dwóch negacji to nigdy nie stosowałem więc dlatego byłem zdziwiony (zawsze mnożyłem przez 1).

steb0 · Answer

Marku
Zgłupiałem do reszty zrobiłem tabelkę z kalendarzem świąt w arkuszu dane, wpisuje rok 2012, a mi wyświetla 1905

Poza tym, kombinuje jak koń (głupi) pod górę z tym zakresem ale jak bym nie wstawił dodatkowego zakresu ze świętami do twojej formuły to zawsze mam błąd :/
Czy możesz mi jeszcze z tym pomóc?

walek33 · Answer

Formułka musi być bez rok. Sama wartość D3.

steb0 · Answer

Po prostu przedobrzyłem powinno być odwołanie do A1 a nie do D3

Ale jak teraz wstawić ten zakres do tej formuły?

=(EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)-DATA(LEWY($AE$2;4);B6;1)+1-NETWORKDAYS((DATA(LEWY($AE$2;4);B6;1));EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1;[b]Tu_zakres[/b])

Dodano po 4 [minuty]:

steb0 wrote:
Po prostu przedobrzyłem powinno być odwołanie do A1 a nie do D3
Ale jak teraz wstawić ten zakres do tej formuły?
Code: text Expand Select all Copy to clipboard
=(EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)-DATA(LEWY($AE$2;4);B6;1)+1-NETWORKDAYS((DATA(LEWY($AE$2;4);B6;1));EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1;[b]Tu_zakres[/b])

edycja:
Zresztą to nie mój kalendarz pobrałem go stąd http://dobraorganizacja.pl/kalendarz-kompaktowy-2012/

Dodano po 3 [minuty]:

[quote="steb0"]Po prostu przedobrzyłem powinno być odwołanie do A1 a nie do D3

Ale jak teraz wstawić ten zakres do tej formuły?

=(EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)-DATA(LEWY($AE$2;4);B6;1)+1-NETWORKDAYS((DATA(LEWY($AE$2;4);B6;1));EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1;[b]Tu_zakres[/b])

Dodano po 4 [minuty]:

Zresztą to nie mój kalendarz pobrałem go stąd http://dobraorganizacja.pl/kalendarz-kompaktowy-2012/

Czy dla każdego miesiąca trzeba dawać inną formułę czy można zrobić 1 uniwersalną?

Dodano po 1 [minuty]:

Hym.. po każdej edycji posta cytuje sam siebie

coś z moją przeglądarką czy to tak jest na tym forum

steb0 · Answer

Jesteś wielki dziękuje.

Już jutro powinienem dostać książkę więc mam nadzieje że nie będę już was męczył tak banalnymi pytaniami.

Myślałem że jak przeanalizuje kilka gotowych arkuszy to załapie (w mojej naiwności ) o co chodzi, "niestety" żeby coś zrobić trzeba wykuć funkcje, żeby można było to poskładać w jakąś logiczną całość. Jak przeglądam wasze wątki uświadamiam sobie jaka dzieli nas przepaść

i ile czeka mnie pracy.

Dodano po 6 [minuty]:

Jeszcze jedno pytanie

Powiedzmy że mam 2 arkusze:
Jeden z rocznym rozliczeniem czasu, a jeden z miesięcznym, o ile niema problemu zrobić tak żeby roczny pobierał dane z miesięcznego, ale czy można zrobić żeby to działało w obie strony? Jeśli edytuje miesięczny aktualizuje się roczny, a kiedy wstawiam dane do rocznego, wtedy miesięczny korzysta z danych rocznego.