Excel i dni robocze jeszcze inaczej

by Marcin

Kilka dni temu pisałem o możliwości zastąpienia NETWORKDAYS funkcją SUMA.ILOCZYNÓW oraz o kilku korzyściach z tego płynących. Dzisiaj chciałbym pokazać Ci jeszcze inną, całkiem elastyczną, formułę. Nie będę ukrywał, że to rozwiązanie bardzo mi się podoba, chociaż także nie jest pozbawione wad.

Dni robocze

={SUMA(ZAOKR.DO.CAŁK((DZIEŃ.TYG(C3-{2\3\4\5\6})+C4-C3)/7))}

W przedstawionej postaci formuła policzy liczbę dni roboczych (od poniedzialku do piątku) pomiędzy 12 września 2007, a 12 października 2007 roku. W moim przykładzie jest to formuła tablicowa, ale działa doskonale także jako zwykła formuła nietablicowa (o tym dlaczego napiszę innym razem).

Póki co nie jest to nic więcej niż to co możemy zrobić przy pomocy NETWORKDAYS. Zmieniając jednak zawartość stałej tablicowej {2\3\4\5\6} możesz zliczać dowolne dni tygodnia. Dla przykładu, zmieniając {2\3\4\5\6} na {3\5\7} policzysz tylko wtorki, czwartki i soboty, co wykracza już nieco poza możliwości NETWORKDAYS.

Jeżeli chcesz możesz także uwzględnić dodatkowe dni „wolne”, których listę przechowujesz w komórkach skoroszytu (w moim przykładzie C6 i C7). W tym jednak momencie przyznam, że funkcja traci nieco na swojej prostocie.

Pozostając przy moim testowym przedziale dat, policzmy tym razem wtorki, środy i piątki. Łatwo to zrobić funkcją:

=SUMA(ZAOKR.DO.CAŁK((DZIEŃ.TYG(C3-{3\4\6})+C4-C3)/7))

Jako wynik otrzymasz 14. Gdyby więc tylko wtorek, środa i piątek były dniami pracującymi to (o szczęściarzu !) w podanym przedziale czasowym musiałbyś iść do pracy 14 razy.
Co jednak jeżeli po drodze trafią się dodatkowe święta ?

Załóżmy, że na liście świąt mam jedną sobotę i jeden wtorek (29 września i 9 października 2007). Wykorzystując formułę:

=SUMA(ZAOKR.DO.CAŁK((DZIEŃ.TYG(C3-{3\4\6})+C4-C3)/7))-SUMA.ILOCZYNÓW(–(C6:C7>=C3);–(C6:C7<=C4);
–(DZIEŃ.TYG(C6:C7)=3)+(DZIEŃ.TYG(C6:C7)=4)+(DZIEŃ.TYG(C6:C7)=6))

otrzymasz właściwy wynik – 13 (ponieważ sobota jest i tak niewliczana do dni „roboczych” należy odjąc od ogólnej liczby dni roboczych tylko jeden „extra”wolny wtorek.)

Przyznam, że to jedyne rozwiązanie, na jakie udało mi się wpaść. Formuła jest długa, chociaż widziałem o wiele dłuższe. Wydaje się, że działa, jeżeli jednak odkryłes błąd lub znasz jakieś lepsze rozwiązanie – bardzo fajnie by było gdybyś zechciał podzielić się tym ze mną i czytelnikami tego bloga.

Oczywiście, jeżeli potrafisz się zdyscyplinować i będziesz miał pewność, że dodatkowe dni wolne nie będą się pokrywały w dniami wolnymi pomijanymi przez formułę zasadniczą (jeżeli nie liczysz niedziel to w zakresie z dodatkowymi świętami nie masz żadnych niedziel etc.) śmiało możesz skorzystać z prostszej wersji powyższej formuły, sprawdzającej tylko czy dodatkowe dni wolne znajdują się w interesującym Cie zakresie dat.

=SUMA(ZAOKR.DO.CAŁK((DZIEŃ.TYG(C3-{2\3\4\5\6})+C4-C3)/7))-SUMA.ILOCZYNÓW(–(C6:C7>=C3);–(C6:C7<=C4))

Ze swojej strony zalecam jednak wcześniejsze, bardziej uniwersalne rozwiązanie.

Miałem o tym pisać teraz, ale i tak post ten zrobił się strasznie długi. Następnym więc razem napiszę dlaczego omawiana dzisiaj formuła w ogóle działa, czyli o tym co dzieje się niejako w „tle” (sam sie nad tym przez moment zastanawiałem, więc mam nadzieję oszczędzę tego przynajmniej Tobie.).

{ 5 comments… read them below or add one }

Tybek

Witam!
A jak z podanej daty, np. 27-04-2007 zaokrąglić (lub innym sposobem) napisać formułę, aby kolejna komórka pokazywała pierwszy dzień miesiąca, czyli dla tego przypadku byłaby to data: 01-04-2007 ???

Tybek

Odnośnie mojego pytania to sprawa jest już rozwiązana tutaj: http://www.excelforum.pl/viewtopic.php?t=5739

Niemniej jednak wielkie gratulacje za fajnego bloga!! Pozdrawiam,
Tyberiusz

Darek

Witam,

na wstępie chciałem podziękować za dobrą formułę. Nie jestem do końca pewien, bo w pewnym momencie zakręciłem się, jeżeli chodzi o znaki +/-.
Wydaje mi się, że w tej formule jest błąd:
=SUMA(ZAOKR.DO.CAŁK((DZIEŃ.TYG(C3-{3\4\6})+C4-C3)/7))-SUMA.ILOCZYNÓW(–(C6:C7>=C3);–(C6:C7<=C4);
–(DZIEŃ.TYG(C6:C7)=3)+(DZIEŃ.TYG(C6:C7)=4)+(DZIEŃ.TYG(C6:C7)=6))

albo przed SUMA.ILOCZUNÓW powinien być plus, albo przed ostatnim wierszem z "dniami tygodnia"

Dodam że sprawdzałem to na przykładzie ze strony oraz na swoich datach.

Pozdrawiam

Pietro

Odnosząc się do tych samych pól, jakie zostały użyte w przykładzie (obrazkowym) wytworzyłem coś takiego:
{=JEŻELI(ORAZ(C3″”;C4″”);(SUMA(ZAOKR.DO.CAŁK((DZIEŃ.TYG(C3-{1\2\3\4\5};2)+C4-C3)/7))-SUMA(JEŻELI($C$6:$C$7>=C3;JEŻELI($C$6:$C$7<=C4;JEŻELI(DZIEŃ.TYG($C$6:$C$7;2)<6;JEŻELI(DZIEŃ.TYG($C$6:$C$7;2)<6;1;0);0);0);0)));"")}

Formuła po pierwsze nie wyświetla błędu jeśli nie ma podanej którejś z dat – tylko się nie liczy dalej, po drugie wybiera robocze dni tygodnia (pn-pt), po trzecie odejmuje dni wolne wymienione na liście (jeśli nie są sobotą lub niedzielą) – oczywiście można poszerzać zakres listy… Pozdrawiam!

Pietro

W powyższej formule na początki w ORAZ między c3 a „” oraz C4 a „” powinien być znak „różne” ale ponieważ są to znaki robiące znaczniki w htmlu – to mi je usunęło 🙂

Pozdrawiam

Leave a Comment

Previous post:

Next post: