Descărcați exemplul de registru de lucru
Acest tutorial va arăta cum să utilizați funcțiile SUMPRODUCT și SUMIFS pentru a însuma date care îndeplinesc anumite criterii pe mai multe foi în Excel și Foi de calcul Google.
Suma regulată pe mai multe foi
Uneori, datele dvs. pot acoperi mai multe foi de lucru într-un fișier Excel. Acest lucru este comun pentru datele colectate periodic. Fiecare foaie dintr-un registru de lucru poate conține date pentru o perioadă de timp stabilită. Vrem o formulă care să rezume datele conținute în două sau mai multe foi.
Funcția SUM vă permite să însumați cu ușurință datele pe mai multe foi folosind un Referință 3D:
| 1 | = SUM (Sheet1: Sheet2! A1) |
Cu toate acestea, acest lucru nu este posibil cu funcția SUMIFS. În schimb, trebuie să folosim o formulă mai complicată.
Suma dacă este pe mai multe foi
Acest exemplu va însuma Numărul de livrări planificate pentru fiecare Client pe mai multe foi de lucru, fiecare conținând date referitoare la o lună diferită, utilizând funcțiile SUMIFS, SUMPRODUCT și INDIRECT:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Să parcurgem această formulă.
Pasul 1: Creați o formulă SUMIFS numai pentru o foaie de intrare:
Folosim funcția SUMIFS pentru a însuma Numărul de livrări planificate de Client pentru o singură foaie de date de intrare:
| 1 | = SUME (D3: D7, C3: C7, H3) |
Pasul 2: Adăugați o referință de foaie la formulă
Păstrăm rezultatul formulei la fel, dar specificăm că datele de intrare se află în foaia numită 'Pasul 2'
| 1 | = SUMIFE („Pasul 2”! D3: D7, „Pasul 2”! C3: C7, H3) |
Pasul 3: Cuibărește într-o funcție SUMPRODUCT
Pentru a pregăti formula pentru a efectua calcule SUMIFS pe mai multe foi și apoi pentru a însuma rezultatele împreună, adăugăm o funcție SUMPRODUCT în jurul formulei
| 1 | = SUMPRODUCT (SUMIFS („Pasul 3”! D3: D7, „Pasul 3”! C3: C7, H3)) |
Utilizarea funcției SUMIFS pe o singură foaie produce o singură valoare. Pe mai multe foi, funcția SUMIFS generează o serie de valori (una pentru fiecare foaie de lucru). Folosim funcția SUMPRODUCT pentru a totaliza valorile din această matrice.
Pasul 4: Înlocuiți referința foii cu o listă de nume de foi
Dorim să înlocuim Numele foii parte a formulei cu o listă de date care conține valorile: Ian, Februarie, Mar, și Aprilie. Această listă este stocată în celulele F3: F6.
Funcția INDIRECTĂ asigură afișarea listei de text Numele foilor este tratat ca parte a unei referințe de celule valide în funcția SUMIFS.
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
În această formulă, referința intervalului scris anterior:
| 1 | „Pasul 3”! D3: D7 |
Se înlocuiește cu:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Ghilimelele fac dificilă citirea formulei, așa că aici este afișată cu spații adăugate:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Utilizarea acestui mod de referențiere a unei liste de celule ne permite, de asemenea, să rezumăm datele din mai multe foi care nu urmează un stil de listă numerică. O referință standard 3D ar necesita ca numele foilor să fie în stil: Intrare1, Intrare2, Intrare3 etc., dar exemplul de mai sus vă permite să utilizați o listă cu orice Numele foilor și pentru a le face referire într-o celulă separată.
Blocarea referințelor celulei
Pentru a simplifica citirea formulelor, am arătat formulele fără referințe de celule blocate:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
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 (SUMIFE (INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & „C3: C7”), H3)) |
Citiți articolul nostru despre blocarea referințelor celulei pentru a afla mai multe.
Suma dacă este peste mai multe foi în Foi de calcul Google
Utilizarea funcției INDIRECT pentru a face referire la o listă de foi într-o funcție SUMPRODUCT și SUMIFS nu este posibilă în prezent în Foi de calcul Google.
În schimb, se pot face calcule SUMIFS separate pentru fiecare foaie de intrare și rezultatele adăugate împreună:
| 1234 | = SUME (ian! D3: D7, ian! C3: C7, H3)+ SUME (Feb! D3: D7, Feb! C3: C7, H3)+ SUME (Mar! D3: D7, Mar! C3: C7, H3)+ SUME (aprilie! D3: D7, aprilie! C3: C7, H3) |
