Formuła ogólna
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Podsumowanie
Aby policzyć dni tygodnia (poniedziałki, piątki, niedziele itp.) Między dwiema datami, możesz użyć formuły tablicowej, która wykorzystuje kilka funkcji: SUMPRODUCT, WEEKDAY, ROW i INTERIRECT. W pokazanym przykładzie formuła w komórce E6 to
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
W ogólnej wersji formuły początek = data rozpoczęcia, koniec = data zakończenia i dow = dzień tygodnia.
Wyjaśnienie
Zasadniczo ta formuła wykorzystuje funkcję WEEKDAY do testowania wielu dat, aby sprawdzić, czy przypadają one w dany dzień tygodnia (dół), oraz funkcję SUMPRODUCT, aby podsumować sumę.
Gdy podana jest data, DZIEŃ.TYG zwraca po prostu liczbę od 1 do 7, która odpowiada określonemu dniu tygodnia. Przy ustawieniach domyślnych 1 = niedziela i 7 = sobota. Tak więc 2 = poniedziałek, 6 = piątek i tak dalej.
Sztuczka związana z tą formułą polega na zrozumieniu, że daty w programie Excel są po prostu liczbami kolejnymi rozpoczynającymi się 1 stycznia 1900 r. Na przykład 1 stycznia 2016 r. To numer seryjny 42370, a 8 stycznia to 42377. Daty w programie Excel wyglądają tylko jak daty, kiedy stosowany jest format daty.
Pojawia się więc pytanie - w jaki sposób można skonstruować tablicę dat, którą można wprowadzić do funkcji WEEKDAY, aby znaleźć odpowiednie dni tygodnia?
Odpowiedzią jest użycie WIERSZA z funkcjami POŚREDNI, takimi jak:
ROW(INDIRECT(date1&":"&date2))
POŚREDNI umożliwia interpretację połączonych dat „42370: 42377” jako numerów wierszy. Następnie funkcja WIERSZ zwraca tablicę taką jak ta:
(42370;42371;42372;42373;42374;42375;42376;42377)
Funkcja WEEKDAY oblicza te liczby jako daty i zwraca tę tablicę:
(6;7;1;2;3;4;5;6)
który jest sprawdzany w odniesieniu do danego dnia tygodnia (w tym przypadku 6 z D6). Gdy wyniki testu zostaną przekonwertowane na 1 i 0 z podwójnym łącznikiem, ta tablica jest przetwarzana przez SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Co zwraca 2.
Z SEQUENCE
Dzięki nowej funkcji SEKWENCJA ta formuła może zostać nieco uproszczona w ten sposób:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
W tej wersji używamy SEQUENCE do bezpośredniego generowania tablicy dat, bez potrzeby użycia INDIRECT lub ROW.
