Bendroji formulė
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Santrauka
Norėdami apskaičiuoti darbo valandas tarp dviejų datų pagal pasirinktinį tvarkaraštį, galite naudoti formulę, pagrįstą WEEKDAY ir SUMPRODUCT funkcijomis, naudodamiesi ROW, INDIRECT ir MID. Parodytame pavyzdyje formulė F8 yra:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Kuris grąžina 36 valandas pagal įprastą tvarkaraštį, kai pirmadieniais – penktadieniais dirba 8 valandos, šeštadienį - 4 valandos, o rugsėjo 3 d., Pirmadienis, yra atostogos. Atostogos pateikiamos nurodytu intervalu G6: G8. Darbo grafikas įrašomas kaip teksto eilutė D stulpelyje ir gali būti pakeistas pagal pageidavimą.
Pastaba: Tai masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“. Jei turite standartinę 8 valandų darbo dieną, ši formulė yra paprastesnė.
Paaiškinimas
Iš esmės ši formulė naudoja WEEKDAY funkciją, kad nustatytų savaitės dieną (ty pirmadienį, antradienį ir kt.) Kiekvienai dienai tarp dviejų nurodytų datų. WEEKDAY pateikia skaičių nuo 1 iki 7. Su numatytaisiais nustatymais sekmadienis = 1 ir šeštadienis = 7.
Šios formulės gudrybė yra surinkti datų masyvą, kurį galite įtraukti į WEEKDAY funkciją. Tai daroma naudojant ROW su netiesioginiu:
ROW(INDIRECT(B6&":"&C6))
ROW interpretuoja sujungtas datas kaip eilutės numerius ir pateikia tokį masyvą:
(43346;43347;43348;43349;43350;43351;43352)
Kiekvienas masyvo skaičius reiškia datą. Tada WEEKDAY funkcija įvertina masyvą ir grąžina savaitės dienų reikšmių masyvą:
(2;3;4;5;6;7;1)
Šie skaičiai atitinka kiekvienos datos savaitės dieną. Jie pateikiami funkcijai MID kaip pradinio numerio argumentas, kartu su D6 reikšme „0888884“, skirtam tekstui:
MID("0888884",(2;3;4;5;6;7;1),1)
Kadangi mes suteikiame MID pradinių skaičių masyvą, jis pateikia tokių rezultatų masyvą:
("8";"8";"8";"8";"8";"4";"0")
Šios vertės atitinka kiekvieną dieną dirbtas valandas nuo pradžios datos iki pabaigos datos. Atkreipkite dėmesį, kad šio masyvo reikšmės yra tekstas, o ne skaičiai. Norėdami konvertuoti į faktinius skaičius, padauginame iš antrojo masyvo, sukurto atostogoms tvarkyti, kaip paaiškinta toliau. Matematikos operacija priverčia tekstą pateikti skaitines reikšmes.
Atostogos
Norėdami tvarkyti atostogas, mes naudojame ISNA, MATCH ir pavadintą diapazoną „atostogos“:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Ši išraiška naudoja MATCH, kad surastų datas, kurios yra įvardytose atostogų dienose, naudodami tą patį datų masyvą, sugeneruotą aukščiau su INDIRECT ir ROW. MATCH pateikia skaičių, kai randamos atostogos, o # N / A klaidą, kai ne. ISNA funkcija „apverčia“ rezultatus taip, kad TRUE reiškia šventes, o FALSE - ne atostogas. ISNA pateikia tokį masyvą ar rezultatus:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Galiausiai SUMPRODUCT viduje abu masyvai yra padauginami vienas iš kito. Matematikos operacija „TRUE“ ir „FALSE“ verčia 1 ir nuliu, o pirmojo masyvo teksto reikšmes - skaitinėmis reikšmėmis (kaip paaiškinta aukščiau), taigi galiausiai turime:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Padauginę, mes turime vieną masyvą SUMPRODUCT viduje, kuriame nurodytos visos darbo valandos dienų sekoje:
=SUMPRODUCT((0;8;8;8;8;4;0))
Tada SUMPRODUCT susumuoja visus masyvo elementus ir pateikia 36 rezultatą.
