Všeobecný vzorec
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Zhrnutie
Ak chcete počítať pracovné dni (pondelok, piatok, nedeľa atď.) Medzi dvoma dátumami, môžete použiť maticový vzorec, ktorý používa niekoľko funkcií: SUMPRODUCT, WEEKDAY, ROW a INDIRECT. V zobrazenom príklade je vzorec v bunke E6
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
V generickej verzii vzorca start = začiatočný dátum, end = konečný dátum a dow = deň v týždni.
Vysvetlenie
Tento vzorec v jadre využíva funkciu WEEKDAY na testovanie niekoľkých dátumov, aby sa zistilo, či pristanú v daný deň v týždni (dow), a funkcie SUMPRODUCT na zladenie celkového počtu.
Po zadaní dátumu WEEKDAY jednoducho vráti číslo medzi 1 a 7, ktoré zodpovedá konkrétnemu dňu v týždni. Pri predvolenom nastavení je 1 = nedeľa a 7 = sobota. Takže 2 = pondelok, 6 = piatok atď.
Trik v tomto vzorci spočíva v pochopení toho, že dátumy v programe Excel sú iba poradové čísla, ktoré sa začínajú 1. januára 1900. Napríklad 1. január 2016 je poradové číslo 42370 a 8. január je 42377. Dátumy v programe Excel vyzerajú iba ako dátumy, keď použije sa formát čísla dátumu.
Otázkou teda zostáva - ako môžete zostaviť rad dátumov, ktoré môžete vložiť do funkcie WEEKDAY, aby ste zistili príslušné dni v týždni?
Odpoveď je použiť ROW s funkciami INDIRECT takto:
ROW(INDIRECT(date1&":"&date2))
INDIRECT umožňuje, aby sa zreťazené dátumy „42370: 42377“ interpretovali ako čísla riadkov. Potom funkcia ROW vráti pole podobné tomuto:
(42370;42371;42372;42373;42374;42375;42376;42377)
Funkcia WEEKDAY vyhodnotí tieto čísla ako dátumy a vráti toto pole:
(6;7;1;2;3;4;5;6)
ktorý sa testuje proti danému dňu v týždni (6 v tomto prípade od D6). Po prevedení výsledkov testu na 1 s a 0 s dvojitou pomlčkou sa toto pole spracuje pomocou SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Čo vráti 2.
S POSTUPNOSŤOU
S novou funkciou SEQUENCE je možné tento vzorec zjednodušiť asi takto:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
V tejto verzii používame SEQUENCE na generovanie poľa dátumov priamo, bez potreby INDIRECT alebo ROW.
