Všeobecný vzorec
=SUMPRODUCT(data*(headers=A1))
Zhrnutie
Ak chcete sčítať hodnoty v stĺpcoch porovnaním zodpovedajúcich hlavičiek stĺpcov, môžete použiť vzorec založený na funkcii SUMPRODUCT. V zobrazenom príklade je vzorec v J5:
=SUMPRODUCT(data*(LEFT(headers)=J4))
kde „údaj“ je pomenovaný rozsah B5: G14 a „hlavičky“ je pomenovaný rozsah B4: G4.
Vzorec sumarizuje stĺpce, kde hlavičky začínajú na „a“ a vráti hodnotu 201.
Vysvetlenie
Tento vzorec sa v jadre spolieha na funkciu SUMPRODUCT, ktorá umožňuje sčítať hodnoty v zodpovedajúcich stĺpcoch v pomenovanom rozsahu „data“ C5: G14. Ak by boli všetky údaje poskytnuté spoločnosti SUMPRODUCT v jednom rozsahu, výsledkom by bol súčet všetkých hodnôt v rozsahu:
=SUMPRODUCT(data) // all data, returns 387
Ak chcete použiť filter zladením hlavičiek stĺpcov - stĺpcov s hlavičkami, ktoré začínajú na „A“ - použijeme funkciu LEFT takto:
LEFT(headers)=J4) // must begin with "a"
Tento výraz vráti hodnotu TRUE, ak hlavička stĺpca začína na „a“, a FALSE, ak nie. Výsledkom je pole:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Uvidíte, že hodnoty 1, 2 a 5 zodpovedajú stĺpcom, ktoré začínajú na „a“.
Vo vnútri SUMPRODUCT je toto pole vynásobené údajmi. Výsledkom vysielania je dvojrozmerné pole, ako je toto:
(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)
Ak si toto pole predstavíme v tabuľke, je ľahké zistiť, že operáciu prežili iba hodnoty v stĺpcoch začínajúcich sa na „a“, všetky ostatné stĺpce sú nulové. Inými slovami, filter zachováva zaujímavé hodnoty a zvyšok „ruší“:
| 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, ktorý má na spracovanie iba jediné pole, vráti súčet všetkých hodnôt 201.
Súčet podľa presnej zhody
Vyššie uvedený príklad ukazuje, ako sčítať stĺpce, ktoré začínajú jedným alebo viacerými konkrétnymi znakmi. Ak chcete stĺpec sčítať na základe presnej zhody, môžete použiť jednoduchší vzorec, ako je tento:
=SUMPRODUCT(data*(headers=J4))
