Jak obliczyć liczbę piątków?

Z lekcji dowiesz się:

• Jak policzyć ilość dni z tygodnia stosując różne kombinacje?

• Do czego służy formuła DNI.ROBOCZE.NIESTAND.?

• Jak oddzielić piątki wolne od pracujących?

Zadanie to wykonamy na podstawie przykładowych danych z rys. 1.

Rys. 1 Przykładowe dane

Zaczniemy od obliczenia ilości wszystkich dni pomiędzy dwoma datami. W Excelu jest to bardzo proste zadanie, ponieważ wystarczy od daty końcowej odjąć datę początkową. Zapis dla komórki C2 powinien wyglądać następująco:

=B2-A2

Otrzymamy w ten sposób różnicę pomiędzy datami (rys. 2).

Rys. 2 Różnica pomiędzy dwoma datami

W zależności od tego, czy chcemy uwzględniać datę początkową i końcową lub którąś z nich pominąć, możemy zmodyfikować obliczenie. Mianowicie pomiędzy datami 2020-10-23 a 2020-10-24 jest 1 dzień różnicy, ale jeśli chcemy uwzględnić zarówno dzień początkowy, jak i końcowy do formuły musimy dodać wartość 1. Zapis powinien wyglądać następująco:

=B2-A2+1

Po skopiowaniu formuły na komórki poniżej otrzymamy ilość dni pomiędzy wszystkimi datami z uwzględnieniem daty początkowej i końcowej (rys. 3).

Rys. 3 Ilość dni pomiędzy datami z uwzględnieniem daty początkowej i końcowej

Tak naprawdę chcemy policzyć ilość piątków pomiędzy tymi datami, dlatego nałożyliśmy na dane takie formatowanie liczbowe, które pokazuje jakim dniem tygodnia są daty początkowe i końcowe.

W Excelu jest funkcja o nazwie DNI.ROBOCZE.NIESTAND. Dlatego wybieramy funkcję niestandardową, ponieważ chcemy w niestandardowy sposób określić weekendy, mianowicie chcemy uwzględnić święta (wypadające również w piątki). Funkcja ta liczy dni robocze pomiędzy datami. Jeśli podamy pierwszy argument funkcji (data_pocz) – datę początkową oraz drugi argument funkcji (data_końc) – datę końcową, to funkcja uwzględni standardowe weekendy, czyli soboty i niedziele. Zapis formuły powinien wtedy wyglądać następująco:

=DNI.ROBOCZE.NIESTAND(A2;B2)

Po skopiowaniu tej formuły na wiersze poniżej otrzymamy wyniki przedstawione na rys. 4.

Rys. 4 Ilość dni roboczych pomiędzy datami

Możemy zauważyć, że w pierwszym wierszu funkcja policzyła tylko pierwszą datę, ponieważ data końcowa wypada w sobotę. Nas interesują tylko piątki, dlatego możemy użyć trzeciego opcjonalnego argumentu funkcji (weekend). Standardowo w funkcji DNI.ROBOCZE.NIESTAND za weekend są uznawane sobota i niedziela. Funkcja ta daje możliwość wybrania, jaki dni mają być traktowane jako weekend (rys. 5).

Rys. 5 Możliwość wyboru dni, jakie funkcja ma traktować jak weekend

Przykładowo możemy przyjąć, że każdy piątek będzie dniem wolnym od pracy (weekendem). Wtedy zapis formuły powinien wyglądać następująco:

=DNI.ROBOCZE.NIESTAND(A2;B2;16)

Po zatwierdzeniu formuły i skopiowaniu jej na wiersze poniżej, otrzymamy wyniki nie uwzględniające piątku przedstawione na rys. 6.

Rys. 6 Ilość dni roboczych bez piątków (piątek traktowany jako weekend)

Podsumowując otrzymaliśmy liczbę wszystkich dni pomiędzy datami nie licząc piątków. Teraz jeśli tą formułę odejmiemy od formuły w komórce C2 otrzymamy ilość wszystkich piątków. Zapis formuły w komórce C2 powinien wyglądać następująco:

=B2-A2+1-DNI.ROBOCZE.NIESTAND(A2;B2;16)

Przed zatwierdzeniem formuły musimy pamiętać o nałożeniu na dane formatowania liczbowego ogólnego, co możemy zrobić za pomocą skrótu klawiszowego Ctrl+Shift+~ (tylda). Po zatwierdzeniu i skopiowaniu formuły na wiersze poniżej otrzymamy ilość wszystkich piątków pomiędzy datami (rys. 7).

Rys. 7 Ilość wszystkich piątków pomiędzy datami

Można zauważyć, że mamy sporo elementów składni naszej formuły. Jeśli wczytamy się dokładniej w pomoc na temat funkcji DNI.ROBOCZE.NIESTAND, dowiemy się że możemy określić dni robocze w systemie 0 i 1. Wartość 1 jest wtedy interpretowana jako weekend, a wartość 0 jako dzień roboczy, ponadto te wartości musimy zapisać w cudzysłowie. Przy takim zapisie, funkcja DNI.TYGODNIA.NIESTAND policzy tylko liczbę piątków. Zapis funkcji powinien wyglądać następująco:

=DNI.ROBOCZE.NIESTAND(A2;B2;"1111011)

Po zatwierdzeniu formuły i skopiowaniu jej na wiersze poniżej otrzymamy ilość piątków pomiędzy datami (rys. 8).

Rys. 8 Liczba piątków zwrócona przez funkcję DNI.ROBOCZE.NIESTAND (zapis weekendu w formie 0 i 1)

Podsumowując możemy obliczyć liczbę piątków wykorzystując tylko funkcję DNI.ROBOCZE.NIESTAND. Analogicznie możemy policzyć ilość dowolnych dni tygodnia zapisując wartość 0 w odpowiednim miejscu w formule, pamiętając że zapis zaczynamy od poniedziałku, a kończymy na niedzieli. Możemy również policzyć ilość kilku dni z tygodnia stosując różne kombinacje, czyli wpisując wartość 0 w miejsca dni w ciągu, które chcemy uwzględnić.

Teraz chcemy do naszych obliczeń dołożyć dni świąteczne (wolne od pracy), czyli chcemy uwzględnić tylko piątki pracujące. Sprowadza się to, do określenia czwartego argumentu funkcji DNI.ROBOCZE.NIESTAND, czyli święta. W tym argumencie zaznaczamy zakres dat z tabelki Święta 2020, następnie blokujemy go bezwzględnie za pomocą klawisza F4. Zapis formuły powinien wyglądać następująco:

=DNI.ROBOCZE.NIESTAND(A2;B2;"1111011;$F$2:$F$14)

Powyższą formułę zatwierdzamy i kopiujemy na wiersze poniżej. Otrzymamy ilość piątków z uwzględnieniem świąt (rys. 9).

Rys. 9 Ilość piątków pracujących pomiędzy datami (po odliczeniu świąt)

Jak widać na rysunku powyżej przy niektórych zakresach dat nie było żadnych zmian, a przy innych zostały odjęte piątki wypadające w tabeli Święta 2020. Na rys. 10 zostały zaznaczone kolorem zielonym zakresy, w których wystąpiły piątki wolne od pracy.

Rys. 10 Zaznaczone zakresy z uwzględnionymi piątkami wolnymi od pracy

Podsumowując, możemy w różny sposób określać argumenty funkcji DNI.ROBOCZE.NIESTAND, dzięki czemu uzyskamy wyniki, jakie nas interesują. Możemy obliczyć ilość dowolnych dni tygodnia (jeden konkretny dzień lub zbiór kilku), możemy uwzględniać dni świąteczne (dni wolne od pracy), a przede wszystkim sami możemy określić dni traktowane jako weekend.

Polecamy także:

• Co to jest dynamiczna lista rozwijana?

Adam Kopeć