Splošna formula
=SUMPRODUCT(data*(headers=A1))
Povzetek
Če želite sešteti vrednosti v stolpcih z ujemanjem ujemajočih se glav stolpcev, lahko uporabite formulo, ki temelji na funkciji SUMPRODUCT. V prikazanem primeru je formula v J5:
=SUMPRODUCT(data*(LEFT(headers)=J4))
kjer je "data" imenovani obseg B5: G14, "headers" pa imenovani obseg B4: G4.
Formula povzema stolpce, kjer se glave začnejo z "a" in vrnejo 201.
Pojasnilo
V osnovi se ta formula opira na funkcijo SUMPRODUCT za seštevanje vrednosti v ujemajočih se stolpcih v imenovanem obsegu "podatki" C5: G14. Če bi bili vsi podatki posredovani družbi SUMPRODUCT v enem obsegu, bi bil rezultat vsota vseh vrednosti v obsegu:
=SUMPRODUCT(data) // all data, returns 387
Za uporabo filtra z ujemanjem glav stolpcev - stolpcev z glavo, ki se začnejo z "A" - uporabimo funkcijo LEFT, kot je ta:
LEFT(headers)=J4) // must begin with "a"
Ta izraz vrne TRUE, če se glava stolpca začne z "a", in FALSE, če se ne. Rezultat je matrika:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Vidite lahko, da vrednosti 1,2 in 5 ustrezata stolpcem, ki se začnejo z "a".
V SUMPRODUCT se ta matrika pomnoži s "data". Zaradi oddajanja je rezultat dvodimenzionalna matrika, kot je ta:
(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)
Če to matriko vizualiziramo v tabeli, je lahko videti, da so samo vrednosti v stolpcih, ki se začnejo z "a", preživele operacijo, vsi drugi stolpci pa so nič. Z drugimi besedami, filter obdrži zanimive vrednosti, ostalo pa "prekliče":
| 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 |
SUMPRODUCT vrne vsoto vseh vrednosti 201 samo z eno matriko za obdelavo.
Vsota po natančnem ujemanju
Zgornji primer prikazuje, kako sešteti stolpce, ki se začnejo z enim ali več določenimi znaki. Če želite stolpec povzeti na podlagi natančnega ujemanja, lahko uporabite preprostejšo formulo, kot je ta:
=SUMPRODUCT(data*(headers=J4))
