Splošna formula
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Povzetek
Za štetje delavnikov (ponedeljek, petek, nedelja itd.) Med dvema datumoma lahko uporabite matrično formulo, ki uporablja več funkcij: SUMPRODUCT, WEEKDAY, ROW in INDIRECT. V prikazanem primeru je formula v celici E6
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
V splošni različici formule je začetni = začetni datum, končni = končni datum in dow = dan v tednu.
Pojasnilo
V osnovi ta formula uporablja funkcijo WEEKDAY za preizkušanje številnih datumov, da se ugotovi, ali pristanejo v določenem dnevu v tednu (dow), in funkcijo SUMPRODUCT, da sešteje skupni znesek.
Ko dobite datum, WEEKDAY preprosto vrne številko med 1 in 7, ki ustreza določenemu dnevu v tednu. Pri privzetih nastavitvah je 1 = nedelja in 7 = sobota. Torej, 2 = ponedeljek, 6 = petek itd.
Trik te formule je razumevanje, da so datumi v Excelu samo serijske številke, ki se začnejo 1. januarja 1900. Na primer, 1. januar 2016 je serijska številka 42370, 8. januar pa 42377. Datumi v Excelu so videti samo kot datumi, ko uporabljena je oblika številke datuma.
Tako se postavlja vprašanje - kako lahko sestavite vrsto datumov, ki jih lahko vnesete v funkcijo WEEKDAY, da ugotovite ustrezne dni v tednu?
Odgovor je, da uporabite ROW s posrednimi funkcijami, tako:
ROW(INDIRECT(date1&":"&date2))
INDIRECT omogoča, da se združeni datumi "42370: 42377" razlagajo kot številke vrstic. Nato funkcija ROW vrne matriko, kot je ta:
(42370;42371;42372;42373;42374;42375;42376;42377)
Funkcija WEEKDAY izračuna te številke kot datume in vrne to matriko:
(6;7;1;2;3;4;5;6)
ki se testira glede na določen dan v tednu (v tem primeru 6 iz D6). Ko se rezultati preizkusa pretvorijo v 1s in 0s z dvojno vezajem, to polje obdela SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Kar vrne 2.
S SEQUENCE
Z novo funkcijo SEQUENCE lahko to formulo nekoliko poenostavimo takole:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
V tej različici uporabljamo SEQUENCE za neposredno generiranje nabora datumov, ne da bi potrebovali INDIRECT ali ROW.
