Formula generică
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
rezumat
Pentru a calcula orele de lucru între două date cu un program personalizat, puteți utiliza o formulă bazată pe funcțiile WEEKDAY și SUMPRODUCT, cu ajutorul ROW, INDIRECT și MID. În exemplul prezentat, formula din F8 este:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Care returnează 36 de ore, pe baza unui program personalizat în care se lucrează 8 ore de luni până vineri, 4 ore se lucrează sâmbătă, iar luni, 3 septembrie este sărbătoare. Sărbătorile sunt furnizate ca gama numită G6: G8. Programul de lucru este introdus ca un șir de text în coloana D și poate fi modificat după cum doriți.
Notă: Aceasta este o formulă matrice care trebuie introdusă cu Control + Shift + Enter. Dacă aveți o zi de lucru standard de 8 ore, această formulă este mai simplă.
Explicaţie
În esență, această formulă utilizează funcția WEEKDAY pentru a afla ziua săptămânii (adică luni, marți etc.) pentru fiecare zi între cele două date date. WEEKDAY returnează un număr între 1 și 7. Cu setările implicite, duminică = 1 și sâmbătă = 7.
Trucul pentru această formulă este asamblarea unei serii de date pe care le puteți introduce în funcția WEEKDAY. Acest lucru se face cu ROW cu INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW interpretează datele concatenate ca numere de rând și returnează o matrice ca aceasta:
(43346;43347;43348;43349;43350;43351;43352)
Fiecare număr din matrice reprezintă o dată. Funcția WEEKDAY evaluează apoi matricea și returnează o matrice de valori zilnice:
(2;3;4;5;6;7;1)
Aceste numere corespund cu ziua săptămânii fiecărei date. Acestea sunt furnizate funcției MID ca argument al numărului de început, împreună cu valoarea din D6, „0888884” pentru text:
MID("0888884",(2;3;4;5;6;7;1),1)
Deoarece oferim MID o serie de numere de început, aceasta returnează o serie de rezultate ca aceasta:
("8";"8";"8";"8";"8";"4";"0")
Aceste valori corespund orelor lucrate în fiecare zi de la data de începere până la data de sfârșit. Rețineți că valorile din această matrice sunt text, nu numere. Pentru a converti în numere reale, înmulțim cu o a doua matrice creată pentru a gestiona sărbătorile, așa cum se explică mai jos. Operația matematică constrânge textul la valori numerice.
Sărbători
Pentru a gestiona sărbătorile, folosim ISNA, MATCH și gama denumită „sărbători” astfel:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Această expresie folosește MATCH pentru a localiza datele care se află în intervalul de vacanță numit folosind aceeași matrice de date generate mai sus cu INDIRECT și ROW. MATCH returnează un număr când sunt găsite sărbătorile și eroarea # N / A când nu. Funcția ISNA „întoarce” rezultatele astfel încât ADEVĂRUL să reprezinte sărbătorile și FALSUL să reprezinte non-sărbătorile. ISNA returnează o matrice sau rezultate de acest gen:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
În cele din urmă, ambele matrice sunt înmulțite una cu cealaltă în interiorul SUMPRODUCT. Operația matematică constrânge TRUE și FALSE la 1 și zero, iar valorile textului din prima matrice la valori numerice (așa cum s-a explicat mai sus), deci la final avem:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
După multiplicare, avem o singură matrice în interiorul SUMPRODUCT care conține toate orele de lucru din intervalul de date:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT însumează apoi toate articolele din matrice și returnează un rezultat de 36.
