Formula generică
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
rezumat
Pentru a rezuma condiționat intervale identice care există în foi de lucru separate, toate într-o singură formulă, puteți utiliza funcția SUMIF cu INDIRECT, înfășurat în SUMPRODUCT. În exemplul prezentat, formula din C9 este:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Explicaţie
Datele de pe fiecare dintre cele trei foi procesate arată astfel:
În primul rând, rețineți că nu puteți utiliza SUMIF-uri cu o referință 3D „normală” ca aceasta:
Sheet1:Sheet3!D4:D5
Aceasta este „sintaxa 3D” standard, dar dacă încercați să o utilizați cu SUMIF, veți primi o eroare #VALUE. Deci, pentru a rezolva această problemă, puteți utiliza un interval numit „foi” care listează fiecare foaie (fila Foaie de lucru) pe care doriți să o includeți. Cu toate acestea, pentru a construi referințe pe care Excel le va interpreta corect, trebuie să concatenăm numele foilor cu intervalele cu care trebuie să lucrăm și apoi să folosim INDIRECT pentru ca Excel să le recunoască corect.
De asemenea, deoarece intervalul denumit „foi” conține valori multiple (adică este o matrice), rezultatul SUMIF în acest caz este, de asemenea, o matrice (uneori numită „matrice rezultantă). Deci, folosim SUMPRODUCT pentru a o gestiona, deoarece SUMPRODUCT are capacitatea de a gestiona matrici în mod nativ fără a necesita Ctrl-Shift-Enter, ca multe alte formule de matrice.
Altă cale
Exemplul de mai sus este oarecum complicat. O altă modalitate de a rezolva această problemă este de a face o sumă condițională „locală” pe fiecare foaie, apoi utilizați o sumă 3D obișnuită pentru a aduna fiecare valoare din fila rezumat.
Pentru a face acest lucru, adăugați o formulă SUMIF la fiecare foaie de foaie care utilizează o celulă de criterii pe foaia de rezumat. Atunci când modificați criteriile, toate formulele SUMIF legate vor fi actualizate.
