Descărcați exemplul de registru de lucru
Acest tutorial va arăta cum să însumați datele corespunzătoare anumitor numere de săptămână în Excel și Foi de calcul Google.
Suma dacă după numărul săptămânii
Pentru a „însuma dacă” după numărul săptămânii, vom folosi funcția SUMIFS. Dar mai întâi trebuie să adăugăm o coloană de ajutor care conține funcția WEEKNUM.
The Numărul săptămânii coloana de ajutor este calculată utilizând funcția WEEKNUM:
| 1 | = SĂPTĂMÂNĂ (B3,1) |
Apoi, vom folosi funcția SUMIFS pentru a rezuma toate Vânzări care au loc într-un anumit Numărul săptămânii.
| 1 | = SUME (D3: D9, C3: C9, F3) |
Suma dacă după numărul săptămânii - fără coloana de ajutor
Metoda coloanei de ajutor este ușor de urmat, dar puteți, de asemenea, să reproduceți calculul într-o singură formulă utilizând funcția SUMPRODUCT în combinație cu funcția WEEKNUM pentru a însuma Număr total de vânzări de Numărul săptămânii.
| 1 | = SUMPRODUCT (- (WEEKNUM (B3: B9 + 0,1) = E3), C3: C9) |
În acest exemplu, putem folosi funcția SUMPRODUCT pentru a efectua calcule „sum dacă” complicate. Să parcurgem exemplul de mai sus.
Aceasta este formula noastră finală:
| 1 | = SUMPRODUCT (- (WEEKNUM (B3: B9 + 0,1) = E3), C3: C9) |
În primul rând, funcția SUMPRODUCT listează matricea de valori din intervalele de celule:
| 1 | =(--(({"1/3/2020"; "1/6/2020"; "1/9/2020"; "1/12/2020"; "1/15/2020"; "1/18/2020"; "1/21/2020"}+0,1)=1), {4; 9; 1; 7; 6; 2; 5}) |
Apoi, funcția WEEKNUM calculează Numărul săptămânii din fiecare dintre Date de vânzare.
Funcția WEEKNUM nu este concepută pentru a funcționa cu valorile matricei, deci trebuie să adăugăm zero („+0”) pentru ca WEEKNUM să proceseze corect valorile.
| 1 | = SUMPRODUCT (- ({1; 2; 2; 3; 3; 3; 4} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Numărul săptămânii valorile egale cu 1 sunt schimbate în valori ADEVĂRATE.
| 1 | = SUMPRODUCT (- ({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}), {4; 9; 1; 7; 6; 2; 5}) |
Apoi liniuțele duble (-) convertesc valorile TRUE și FALSE în 1s și 0s:
| 1 | = SUMPRODUCT ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
Funcția SUMPRODUCT multiplică apoi fiecare pereche de intrări în tablouri pentru a produce o serie de Număr de vânzări care au o Numărul săptămânii din 1:
| 1 | = SUMPRODUCT ({4; 0; 0; 0; 0; 0; 0}) |
În cele din urmă, numerele din matrice sunt însumate împreună:
| 1 | =4 |
Această formulă este apoi repetată pentru celelalte valori posibile ale Numărul săptămânii.
Mai multe detalii despre utilizarea instrucțiunilor booleene și comanda „-” într-o funcție SUMPRODUCT pot fi găsite aici.
Blocarea referințelor celulei
Pentru a simplifica citirea formulelor, am arătat formulele fără referințe de celule blocate:
| 1 | = SUMPRODUCT (- (WEEKNUM (B3: B9 + 0,1) = E3), C3: C9) |
Dar aceste formule nu vor funcționa corect atunci când sunt copiate și lipite în altă parte a fișierului. În schimb, ar trebui să utilizați referințe de celule blocate ca aceasta:
| 1 | = SUMPRODUCT (- (SĂPTĂMÂNĂ ($ B $ 3: $ B $ 9 + 0,1) = E3), $ C $ 3: $ C $ 9) |
Citiți articolul nostru despre blocarea referințelor celulei pentru a afla mai multe.
Suma dacă după numărul săptămânii din Foi de calcul Google
Aceste formule funcționează exact la fel în Foi de calcul Google ca în Excel.
Cu toate acestea, funcția WEEKNUM este mai flexibilă în Foi de calcul Google decât în Excel și acceptă intrări și ieșiri de matrice. Prin urmare, operația {Array} +0 din formula WEEKNUM (B3: B9 + 0,1) nu este necesară.
Formula completă SUMPRODUCT poate fi scrisă în Foi de calcul Google ca:
| 1 | =SUMPRODUCT(--(SĂPTĂMÂNĂ($ B $ 3: $ B $ 9+0,1)=E3),$ C $ 3: $ C $ 9) |
