Vispārēja formula
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Kopsavilkums
Lai skaitītu darba dienas (pirmdienas, piektdienas, svētdienas utt.) Starp diviem datumiem, varat izmantot masīva formulu, kurā tiek izmantotas vairākas funkcijas: SUMPRODUCT, WEEKDAY, ROW un INDIRECT. Parādītajā piemērā formula šūnā E6 ir
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
Formulas vispārīgajā versijā sākuma = sākuma datums, beigu = beigu datums un dow = nedēļas diena.
Paskaidrojums
Būtībā šī formula izmanto funkciju WEEKDAY, lai pārbaudītu vairākus datumus, lai noskaidrotu, vai tie nokļūst noteiktā nedēļas dienā (dow), un funkciju SUMPRODUCT, lai summētu kopējo summu.
Piešķirot datumu, WEEKDAY vienkārši atgriež skaitli no 1 līdz 7, kas atbilst noteiktai nedēļas dienai. Ar noklusējuma iestatījumiem 1 = svētdiena un 7 = sestdiena. Tātad, 2 = pirmdiena, 6 = piektdiena utt.
Šīs formulas triks ir saprast, ka datumi programmā Excel ir tikai sērijas numuri, kas sākas 1900. gada 1. janvārī. Piemēram, 2016. gada 1. janvāris ir sērijas numurs 42370 un 8. janvāris ir 42377. Excel datumi izskatās tikai pēc datumiem, kad tiek izmantots datuma numura formāts.
Tātad rodas jautājums - kā jūs varat izveidot datumu masīvu, ko varat ievadīt WEEKDAY funkcijā, lai uzzinātu atbilstošās nedēļas dienas?
Atbilde ir izmantot ROW ar netiešām funkcijām, piemēram:
ROW(INDIRECT(date1&":"&date2))
NETIEŠI ļauj saīsinātos datumus "42370: 42377" interpretēt kā rindu numurus. Tad funkcija ROW atgriež šādu masīvu:
(42370;42371;42372;42373;42374;42375;42376;42377)
Funkcija WEEKDAY šos skaitļus novērtē kā datumus un atgriež šo masīvu:
(6;7;1;2;3;4;5;6)
kas tiek pārbaudīts pret konkrēto nedēļas dienu (šajā gadījumā 6 no D6). Kad testa rezultāti ir pārveidoti par 1 un 0 ar dubulto defisi, šo masīvu apstrādā SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Kas atgriež 2.
Ar SEQUENCE
Izmantojot jauno SEQUENCE funkciju, šo formulu var vienkāršot šādi:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
Šajā versijā mēs izmantojam SEQUENCE, lai tieši ģenerētu datumu masīvu, neprasot INDIRECT vai ROW.
