Generička formula
=SUMPRODUCT(data*(headers=A1))
Sažetak
Da biste zbrojili vrijednosti u stupcima podudaranjem odgovarajućih zaglavlja stupaca, možete upotrijebiti formulu koja se temelji na funkciji SUMPRODUCT. U prikazanom primjeru formula u J5 je:
=SUMPRODUCT(data*(LEFT(headers)=J4))
gdje je "podatak" imenovani raspon B5: G14, a "zaglavlja" imenovani raspon B4: G4.
Formula zbraja stupce u kojima zaglavlja započinju s "a" i vraća 201.
Obrazloženje
U osnovi se ova formula oslanja na funkciju SUMPRODUCT za zbrajanje vrijednosti u odgovarajućim stupcima u imenovanom rasponu "podaci" C5: G14. Ako bi se svi podaci pružili SUMPRODUCT-u u jednom rasponu, rezultat bi bio zbroj svih vrijednosti u rasponu:
=SUMPRODUCT(data) // all data, returns 387
Da bismo primijenili filtar podudaranjem zaglavlja stupaca - stupaca sa zaglavljima koja počinju s "A" - koristimo funkciju LIJEVO ovako:
LEFT(headers)=J4) // must begin with "a"
Ovaj izraz vraća TRUE ako zaglavlje stupca započinje s "a", a FALSE ako ne. Rezultat je niz:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Možete vidjeti da vrijednosti 1,2 i 5 odgovaraju stupcima koji počinju s "a".
Unutar SUMPRODUCT-a taj se niz pomnožava s "podacima". Zbog emitiranja, rezultat je dvodimenzionalni niz poput ovog:
(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)
Ako vizualiziramo ovaj niz u tablici, lako je uočiti da su samo vrijednosti u stupcima koji počinju s "a" preživjele operaciju, svi ostali stupci su nula. Drugim riječima, filtar zadržava vrijednosti od interesa i "poništava" ostalo:
| 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 |
Sa samo jednim nizom za obradu, SUMPRODUCT vraća zbroj svih vrijednosti, 201.
Zbroj točnog podudaranja
Gornji primjer pokazuje kako zbrojiti stupce koji počinju s jednim ili više određenih znakova. Da biste zbrojili stupac na temelju točnog podudaranja, možete upotrijebiti jednostavniju formulu poput ove:
=SUMPRODUCT(data*(headers=J4))
