= SUMPRODUCT (--( WEEKDAY ( ROW ( INDIRECT (start&':'&end)))=dow))rezumat
Pentru a număra zilele săptămânii (luni, vineri, duminică etc.) între două date, puteți utiliza o formulă matrice care utilizează mai multe funcții: SUMPRODUCT, WEEKDAY, ROW și INDIRECT. În exemplul prezentat, formula din celula E6 este
= SUMPRODUCT (--( WEEKDAY ( ROW ( INDIRECT (B6&':'&C6)))=D6))
În versiunea generică a formulei, start = data de începere, Sfârșit = data de încheiere și dow = ziua săptămânii.
cum să clasezi lucrurile în excelExplicaţie
La bază, această formulă folosește Funcția WEEKDAY pentru a testa un număr de date pentru a vedea dacă aterizează într-o anumită zi a săptămânii (dow) și Funcția SUMPRODUCT pentru a contabiliza totalul.
Când vi se dă o dată, WEEKDAY returnează pur și simplu un număr între 1 și 7 care corespunde unei anumite zile a săptămânii. Cu setările implicite, 1 = duminică și 7 = sâmbătă. Deci, 2 = luni, 6 = vineri și așa mai departe.
Trucul pentru această formulă este înțelegerea asta datele din Excel sunt doar numere de serie care încep la 1 ianuarie 1900. De exemplu, 1 ianuarie 2016 este numărul de serie 42370, iar 8 ianuarie este 42377. Datele din Excel arată doar ca date când se aplică un format de număr de dată.
cum se selectează întregul tabel în Excel
Deci, întrebarea devine - cum poți construi un matrice de date pe care le puteți introduce în funcția WEEKDAY pentru a afla zilele corespunzătoare din săptămână?
Răspunsul este de a folosi RÂND cu INDIRECT funcții așa:
ROW ( INDIRECT (date1&':'&date2))
INDIRECT permite ca datele concatenate „42370: 42377” să fie interpretate ca numere de rând. Apoi, funcția ROW returnează o matrice ca aceasta:
{4237042371423724237342374423754237642377}
Funcția WEEKDAY evaluează aceste numere ca date și returnează această matrice:
{67123456}
care este testat în funcție de ziua dată a săptămânii (6 în acest caz, de la D6). Odată ce rezultatele testului sunt convertite în 1s și 0s cu cratima dublă, această matrice este procesată de SUMPRODUCT:
{10000001}
Care returnează 2.
Cu SECVENȚĂ
Cu noul Funcția SECVENȚĂ , această formulă se poate simplifica oarecum astfel:
cum creez o listă derulantă în Excel
= SUMPRODUCT (--( WEEKDAY ( SEQUENCE (end-start+1,1,start,1))=dow))
În această versiune, folosim SEQUENCE pentru a genera direct matricea de date, fără a fi nevoie de INDIRECT sau ROW.
Formule dinamice de matrice sunt disponibile în Office 365 numai. Autor Dave Bruns