Obecný vzorec
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
souhrn
Chcete-li počítat pracovní dny (pondělí, pátek, neděle atd.) Mezi dvěma daty, můžete použít maticový vzorec, který používá několik funkcí: SUMPRODUCT, WEEKDAY, ROW a INDIRECT. V zobrazeném příkladu je vzorec v buňce E6
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
V obecné verzi vzorce start = počáteční datum, end = konečné datum a dow = den v týdnu.
Vysvětlení
Tento vzorec v jádru používá funkci WEEKDAY k testování řady dat, aby se zjistilo, zda přistávají v daný den v týdnu (dow), a funkce SUMPRODUCT k porovnání celkového počtu.
Když je uvedeno datum, WEEKDAY jednoduše vrátí číslo mezi 1 a 7, které odpovídá konkrétnímu dni v týdnu. Ve výchozím nastavení je 1 = neděle a 7 = sobota. Takže 2 = pondělí, 6 = pátek atd.
Trikem v tomto vzorci je pochopení, že data v aplikaci Excel jsou pouze pořadová čísla, která začínají 1. ledna 1900. Například 1. ledna 2016 je pořadové číslo 42370 a 8. ledna je 42377. Data v aplikaci Excel vypadají pouze jako data, když použije se formát čísla data.
Otázkou tedy zůstává - jak můžete sestavit řadu dat, která můžete poslat do funkce WEEKDAY a zjistit odpovídající dny v týdnu?
Odpověď je použít ROW s NEPŘÍMÝMI funkcemi takto:
ROW(INDIRECT(date1&":"&date2))
INDIRECT umožňuje interpretovat zřetězená data "42370: 42377" jako čísla řádků. Pak funkce ROW vrací takové pole:
(42370;42371;42372;42373;42374;42375;42376;42377)
Funkce WEEKDAY vyhodnotí tato čísla jako data a vrátí toto pole:
(6;7;1;2;3;4;5;6)
který je testován proti danému dni v týdnu (6 v tomto případě od D6). Jakmile jsou výsledky testu převedeny na 1 s a 0 s dvojitou pomlčkou, je toto pole zpracováno programem SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Který vrací 2.
SE SEKVENCÍ
S novou funkcí SEQUENCE lze tento vzorec poněkud zjednodušit takto:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
V této verzi používáme SEQUENCE ke generování pole dat přímo, bez nutnosti INDIRECT nebo ROW.
