Bendroji formulė
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Santrauka
Norėdami suskaičiuoti savaitės dienas (pirmadienius, penktadienius, sekmadienius ir kt.) Tarp dviejų datų, galite naudoti masyvo formulę, kurioje naudojamos kelios funkcijos: SUMPRODUCT, WEEKDAY, ROW ir NETIESIOGINIS. Parodytame pavyzdyje formulė langelyje E6 yra
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
Bendrojoje formulės versijoje pradžia = pradžios data, pabaiga = pabaigos data ir dow = savaitės diena.
Paaiškinimas
Iš esmės ši formulė naudoja WEEKDAY funkciją, kad patikrintų datų skaičių ir nustatytų, ar jos nusileidžia tam tikrą savaitės dieną (dow), ir funkciją SUMPRODUCT, kad būtų galima susumuoti bendrą sumą.
Nurodžius datą, WEEKDAY tiesiog pateikia skaičių nuo 1 iki 7, atitinkantį konkrečią savaitės dieną. Naudojant numatytuosius nustatymus, 1 = sekmadienis ir 7 = šeštadienis. Taigi, 2 = pirmadienis, 6 = penktadienis ir pan.
Šios formulės triukas yra supratimas, kad „Excel“ datos yra tik serijos numeriai, prasidedantys 1900 m. Sausio 1 d. Pavyzdžiui, 2016 m. Sausio 1 d. Yra serijos numeris 42370, o sausio 8 d. - 42377. „Excel“ datos atrodo tik tada, kai taikomas datos numerio formatas.
Taigi kyla klausimas - kaip galite sukurti datų masyvą, kurį galite įtraukti į SAVAITĖS DIENOS funkciją, kad sužinotumėte atitinkamas savaitės dienas?
Atsakymas yra naudoti ROW su netiesioginėmis funkcijomis, tokiomis:
ROW(INDIRECT(date1&":"&date2))
Netiesioginis leidžia susietas datas „42370: 42377“ interpretuoti kaip eilučių numerius. Tada funkcija ROW pateikia tokį masyvą:
(42370;42371;42372;42373;42374;42375;42376;42377)
Funkcija WEEKDAY šiuos skaičius vertina kaip datas ir grąžina šį masyvą:
(6;7;1;2;3;4;5;6)
kuris tikrinamas atsižvelgiant į nurodytą savaitės dieną (šiuo atveju 6 iš D6). Kai bandymo rezultatai bus konvertuojami į 1 ir 0 su dviguba brūkšneliu, šią masyvą apdoroja SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Kuris grąžina 2.
Su SEQ
Naudojant naują SEQUENCE funkciją, ši formulė gali būti supaprastinta maždaug taip:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
Šioje versijoje mes naudojame SEQUENCE, kad generuotume datų masyvą tiesiogiai, nereikalaujant NETIESIOGINĖS ar EILUTINĖS.
