Dni robocze (przy pomocy SUMA.ILOCZYNÓW)

by Marcin

Jednym ze sposobów obliczenia ilości dni roboczych (poniedziałek – piątek) pomiędzy dwiema datami jest wykorzystanie funkcji NETWORKDAYS, wchodzącej w skład dodatku Analysis ToolPak (w Excelu 2007 wspomniany dodatek został wbudowany w zestaw macierzystych funkcji programu).

Osobiście preferuję jednak użycie innej funkcji – SUMA.ILOCZYNÓW. Pomimo, że dłuższa w zapisie, nie ogranicza Cię tylko do „tradycyjnych” dni roboczych (poniedziałek – piątek) czy też, w najlepszym razie, do tygodnia z 5 dniami roboczymi.

Korzystając z SUMA.ILOCZYNÓW z łatwością sprawdzisz na przykład:

  • tylko ilość wtorków w podanym przedziale czasowym, lub
  • ilość dni roboczych, ale biorąc pod uwagę fakt, że dniami wolnymi od pracy są np. środa, piątek i sobota (lub dowolna kombinacja dni).

Załóżmy, że w komórce C3 masz datę początkową, w komórce C4 końcową (daty koniecznie w excelowskim formacie daty).

Dni robocze

Formułę

=NETWORKDAYS(C3;C4)

zastąpić możesz formułą:

=SUMA.ILOCZYNÓW(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4))<>7);
--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>1))

gdzie w miejsce „7” i „1” możesz wpisać cyfry odpowiadające dowolnym dniom tygodnia, które chcesz wykluczyć z kalkulacji.

Niedziela = 1, Poniedziałek =2, Wtorek =3, Środa = 4. Czwartek = 5, Piątek = 6, Sobota = 7.

Kilka słów o tym jak wszystko działa.

Formuła wykorzystuje fakt, że w Excelu daty są przechowywane w postaci liczb. Dlatego też WIERSZ(ADR.POŚR(C3&”:”&C4) przekształcony zostanie do postaci WIERSZ(39337: 39355) i zwróci tablicę z numerami 39337 do 39355 (numery seryjne odpowiadające datom z wybranego zakresu. Innymi słowy jest to tablica ze wszystkimi datami zakresu).

Oczywiście dodając (lub odejmując) fragment:

(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>numer_dnia)

możesz wybrać dowolne dni, które chcesz wykluczyć lub uwzględnić w swojej kalkulacji.

Dla przykładu, chcąc policzyć tylko liczbę wtorków w podanym przedziale dat możesz użyć:

(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))=3)

a chcąc policzyć liczbę dni roboczych, przy założeniu, że dniami wolnymi od pracy są środa, piątek i sobota możesz użyć formuły:

=SUMA.ILOCZYNÓW(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>4);
--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>5);
--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>6))

Dla funkcji NETWORKDAYS możesz także jako parametr podać zakres z dodatkowymi dniami świątecznymi (innymi niż soboty i niedziele), które zostaną odjęte od całkowitej liczby dni. To samo możesz oczywiście zrobić korzystając z SUMA.ILOCZYNÓW. W przykładzie poniżej dodatkowe dni świąteczne znajdują się w komórkach C6:C7.

Funkcja

=SUMA.ILOCZYNÓW(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>7);
--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>1);
--(LICZ.JEŻELI(C6:C7;WIERSZ(ADR.POŚR(C3&":"&C4)))=0))

jest więc dokładnym, dłuższym w zapisie, ale o wiele bardziej elastycznym, odpowiednikiem

=NETWORKDAYS(C3;C4;C6:C7)

Marcin

{ 10 comments… read them below or add one }

Ya

ale czemu to nie dziala ?
cos trzeba zmienic w funkcji ? jest info ze formula zawiera blad….

Marcin

Konkretnie, która formuła pokazuje błąd ?

Być może błąd bierze się w faktu, że (przynajmniej póki co) Wordpress nie bardzo chce mnie słuchać i czasami zmienia nieco treść tego co napisałem. Wiem na pewno, że coś dziwnego dzieje się od czasu do czasu z cudzysłowami. Pamiętać także musisz, że formuła powinna zostać wprowadzona w jednej linii. Ja, dla czytelności, łamię długie formuły enterem.

Ze swojej strony na pewno sprawdzam wszystkie formuły, zanim opublikuję tekst. Powoli staram sie także dodawać pliki źródłowe do wpisów na moim blogu. Mam nadzieję, że w przypadku wątpliwości ułatwi Wam to analizę formuł. Postaram się teraz w pierwszej kolejności załączyć plik źródłowy do tego wpisu. Będziesz więc mógł go ściągnąć i sprawdzić formuły bezpośrednio w Excelu.

Pozdrawiam,

Marcin

Ya

nie dziala to:

=SUMA.ILOCZYNÓW(–(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&”:”&C4)))7);–(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&”:”&C4)))1);–(LICZ.JEŻELI(C6:C7;WIERSZ(ADR.POŚR(C3&”:”&C4)))=0))

=SUMA.ILOCZYNÓW(–(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&”:”&C4))7);–(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&”:”&C4)))1))

nie wiem jak inne

co poprawic ?

Ya

no tak, – i ‚” bylo do poprawy
ale czemu nie formula dziala na format rrrr-mm-dd gg:mm ?

Marcin

>ale czemu nie formula dziala na format rrrr-mm-dd gg:mm ?

Za cały problem odpowiedzialna jest wykorzystana w formule funkcja WIERSZ() oraz sposób w jaki Excel zapisuje daty i czas, a konkretnie zapis godziny jako ułamka (po przecinku) daty.

Fragment:

WIERSZ(ADR.POŚR(C3&”:”&C4))

to jeden ze sposobów na wygenerowanie w tablicy (w pamięci komputera) następujących po sobie kolejno liczb. Korzystamy tu z funkcji WIERSZ(), która zwraca numer wiersza. Jak łatwo się domyśleć numer wiersza nie może być ułamkiem, musi być liczbą całkowitą. Możesz coś mieć przecież w 1 wierszu, w drugim etc, trudno jednak mówić o 1,5 – tym wierszu.

Jeżeli teraz weźmiesz pod uwagę fakt, że godzina przechowywana jest przez Excela jako ułamek, powinno stać się zrozumiałe dlaczego program reaguje w logiczny w sumie sposób wyświetlając błąd.

Można to łatwo naprawić, wystarczy użyć funkcji LICZBA.CAŁK() dla wartości znajdujących się w komórkach C3 i C4. Dla przykładu poprawiony fragment kodu powinien wyglądać następująco:

WIERSZ(ADR.POŚR(LICZBA.CAŁK(C3&)”:”&LICZBA.CAŁK(C4)))

Oczywiście trzeba to poprawić we wszystkich miejscach, w jakich ten fragment występuje w formule.

Marcin

Ya

ale formula wpisana tak:
=SUMA.ILOCZYNÓW(-(DZIEŃ.TYG(WIERSZ(ADR.POŚR(LICZBA.CAŁK(C3)&”:”&LICZBA.CAŁK(C4))))7);-(DZIEŃ.TYG(WIERSZ(ADR.POŚR(LICZBA.CAŁK(C3)&”:”&LICZBA.CAŁK(C4))))1);-(LICZ.JEŻELI($C$6:$C$7;WIERSZ(ADR.POŚR(LICZBA.CAŁK(C3)&”:”&LICZBA.CAŁK(C4)))=0)))
daje wynik 0

telias

Witam
potrzebuje dodać liczbę dni roboczych Np. 03-07-2008 + 7 dni roboczych = 14-07-2008
Gdzie 7 jest zmienna a wynik był obliczany na podstawie 1 daty i zmiennej dni roboczych
pozdrawiam

Pietrek

Witam wszystkich
poprawiłem formułę, w przykładzie był zamiast minusa był myślnik i zamiast cudzysłowów rozumianych przez Excela jakieś inne ciapki. A i jeszcze żeby wyszła liczba dodatnia trzeba było dodać jeszcze obliczenie modułu liczby.
Pozdr :-)

=MODUŁ.LICZBY(SUMA.ILOCZYNÓW(-(DZIEŃ.TYG(WIERSZ(ADR.POŚR($C$3&”:”&$C$4)))7);-(DZIEŃ.TYG(WIERSZ(ADR.POŚR($C$3&”:”&$C$4)))1);-(LICZ.JEŻELI(C6:C7;WIERSZ(ADR.POŚR($C$3&”:”&$C$4)))=0)))

josif

Witam. Czy jest sposób na policzenie w roku kto robił co danego dnia tygodnia?. Dotyczy to konkretnie grafiku jazd. Np. ile razy osoba A jechała w pn, wt, itd.
Data automatyczna (dziś()+1) natomiast symbol osoby wpisywany ręcznie.
Ewentualną odpowiedź poproszę na maila gdyż nie zawsze mam czas na przeglądanie stron a pocztę „noszę non stop przy sobie”
Pzdr

Carlos

po c4 wpisac jeszcze jeden )
czyli
SUMA.ILOCZYNÓW(–(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&”:”&C4)))7);–(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&”:”&C4)))1))

KM

Leave a Comment

Next post: