Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Formatowanie warunkowe excel

20 Lut 2012 11:24 10150 53
  • Poziom 9  
    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 formatuje wygląda tak:
    Code:
    =LICZ.JEŻELI(C6:AG6;$AV$5)
    komórka AV5 jest sformatowana właśnie na kolor czerwony i wstawiony x. Od pewnego czasu muszę pracować w niektóre niedziele, i po wpisaniu godzin w komórkę oznaczoną jako wolny dzień tracę w niej formatowanie :/, Jak zrobić żeby po wpisaniu liczby a nie x komórka taka zachowywała czerwony kolor i była nadal zliczana jako dzień wolny? Makra VBA raczej nie wchodzą w grę ponieważ arkusz jest używany na 32 i 64 bitowym systemie i miałem z nimi czasem problem. Excel 2007

    Druga sprawa, mój dzień pracy to 12 godzin, czy jest możliwość zliczenia nadgodzin? To znaczy jeśli w komórkach będą wartości większe niż 12 to excel obliczy mi sumę nadgodzin? Na w miesiącu mam dwa dni po 14 godzin czyli 4 godziny nadgodzin jest jakiś sposób to policzyć?
  • Poziom 40  
    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)
  • Poziom 9  
    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:
    =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:
    Function 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.
  • Użytkownik usunął konto  
  • Pomocny post
    Poziom 28  
    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.
  • Pomocny post
    Poziom 40  
    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ć.
  • Poziom 9  
    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 :)
  • Poziom 40  
    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:

    Code:
    =(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ę.
  • Poziom 28  
    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:
    Code:
    =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. :D
    To było tylko jako ciekawostka. Nie ma przymusu stosowania. :D
    Cytat:
    Przyznam że trochę mnie drażni że...

    Ja rzekłbym, że nie tylko trochę. Ale podaż ustanawia popyt (czy może odwrotnie?) :D
  • Poziom 40  
    walek33 napisał:
    ...funkcja Suma.Iloczynów() którą także można zaprząc do obliczenia dni roboczych...


    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Ł
    :arrow: 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?
  • Użytkownik usunął konto  
  • Poziom 40  
    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ą]
  • Poziom 9  
    Wszystkim dziękuje za pomoc już wszystko działa jak powinno.
  • Użytkownik usunął konto  
  • Użytkownik usunął konto  
  • Poziom 28  
    A coś takiego Ci pomoże?
    Code:
    =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)))

    :D :D :D
    Z góry uprzedzam, że formuła nie jest moim osobistym wymysłem.
  • Użytkownik usunął konto  
  • Poziom 28  
    Ale już pozbyłeś się podwójnej negacji. :D
  • Użytkownik usunął konto  
  • Poziom 28  
    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.
  • Poziom 40  
    walek33 napisał:
    tylko do 2079-06-05

    Może M$ wie więcej na temat naszego końca :)

    A poważniej też zaraz spróbuję poeksperymentować.
  • Poziom 28  
    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.

    Formatowanie warunkowe excel
  • Użytkownik usunął konto  
  • Poziom 40  
    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ż
    Formatowanie warunkowe excel

    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).
  • Poziom 9  
    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?
  • Poziom 28  
    Formułka musi być bez rok. Sama wartość D3.
  • Poziom 9  
    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:
    =(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 napisał:
    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:
    =(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?
    Code:
    =(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
  • Pomocny post
    Poziom 40  
    Nie cytuj siebie tylko zmieniaj - nie będzie powtarzać.

    steb0 napisał:
    ...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.).
  • Poziom 9  
    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.