Formula generică
=SUMPRODUCT(data*(headers=A1))
rezumat
Pentru a însuma valorile în coloane prin potrivirea antetelor de coloane potrivite, puteți utiliza o formulă bazată pe funcția SUMPRODUCT. În exemplul prezentat, formula din J5 este:
=SUMPRODUCT(data*(LEFT(headers)=J4))
unde „date” este intervalul numit B5: G14, iar „headers” este intervalul numit B4: G4.
Formula rezumă coloanele în care antetele încep cu „a” și returnează 201.
Explicaţie
La bază, această formulă se bazează pe funcția SUMPRODUCT pentru a suma valorilor în coloane potrivite din intervalul numit „date” C5: G14. Dacă toate datele ar fi furnizate către SUMPRODUCT într-un singur interval, rezultatul ar fi suma tuturor valorilor din interval:
=SUMPRODUCT(data) // all data, returns 387
Pentru a aplica un filtru prin potrivirea antetelor de coloane - coloane cu anteturi care încep cu „A” - folosim funcția STÂNGA astfel:
LEFT(headers)=J4) // must begin with "a"
Această expresie returnează TRUE dacă un antet de coloană începe cu „a” și FALSE dacă nu. Rezultatul este o matrice:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Puteți vedea că valorile 1,2 și 5 corespund coloanelor care încep cu „a”.
În interiorul SUMPRODUCT, această matrice este înmulțită cu „date”. Datorită difuzării, rezultatul este o matrice bidimensională ca aceasta:
(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)
Dacă vizualizăm această matrice într-un tabel, este ușor de văzut că numai valorile din coloanele care încep cu „a” au supraviețuit operației, toate celelalte coloane sunt zero. Cu alte cuvinte, filtrul păstrează valorile de interes și „anulează” restul:
| A001 | A002 | B001 | B002 | A003 | B003 |
|---|---|---|---|---|---|
| 8 | 10 | 0 | 0 | 7 | 0 |
| 9 | 10 | 0 | 0 | 10 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 7 | 6 | 0 | 0 | 6 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 10 | 11 | 0 | 0 | 7 | 0 |
| 7 | 8 | 0 | 0 | 8 | 0 |
| 2 | 3 | 0 | 0 | 3 | 0 |
| 3 | 4 | 0 | 0 | 4 | 0 |
| 7 | 7 | 0 | 0 | 4 | 0 |
Cu o singură matrice de procesat, SUMPRODUCT returnează suma tuturor valorilor, 201.
Suma după potrivirea exactă
Exemplul de mai sus arată cum să însumați coloanele care încep cu unul sau mai multe caractere specifice. Pentru a rezuma coloana pe baza unei potriviri exacte, puteți utiliza o formulă mai simplă ca aceasta:
=SUMPRODUCT(data*(headers=J4))
