Általános képlet
=SUMPRODUCT(data*(headers=A1))
Összegzés
Az oszlopokban szereplő értékek összegzéséhez egyező oszlopfejlécek összeegyeztetésével használhatja a SUMPRODUCT függvényen alapuló képletet. A bemutatott példában a J5 képlete a következő:
=SUMPRODUCT(data*(LEFT(headers)=J4))
ahol az "adatok" a B5: G14 elnevezett tartomány, a "fejlécek" pedig a B4: G4 elnevezett tartományok.
A képlet összegzi azokat az oszlopokat, ahol a fejlécek "a" -val kezdődnek, és 201-et adnak vissza.
Magyarázat
Lényegében ez a képlet a SUMPRODUCT függvényre támaszkodik, hogy összeadja az értékeket a megnevezett "adatok" C5: G14 tartomány oszlopaiban. Ha az összes adatot egyetlen tartományban adnák meg a SUMPRODUCT-nak, akkor az eredmény a tartomány összes értékének összege lesz:
=SUMPRODUCT(data) // all data, returns 387
Szűrő oszlopfejlécek - „A” betűvel kezdődő fejlécek - illesztésével történő alkalmazásához a BAL funkciót így használjuk:
LEFT(headers)=J4) // must begin with "a"
Ez a kifejezés az IGAZ értéket adja vissza, ha az oszlopfejléc "a" -val kezdődik, és ha nem, akkor a FALSE értéket. Az eredmény egy tömb:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Láthatja, hogy az 1,2 és 5 értékek megfelelnek az "a" betűvel kezdődő oszlopoknak.
A SUMPRODUCT oldalon ezt a tömböt megszorozzuk "adatokkal". A műsorszórás miatt az eredmény egy kétdimenziós tömb:
(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)
Ha ezt a tömböt egy táblázatban jelenítjük meg, akkor könnyen belátható, hogy csak az "a" betűvel kezdődő oszlopok értékei élték túl a műveletet, az összes többi oszlop nulla. Más szavakkal, a szűrő megőrzi az érdeklődésre számot tartó értékeket, és a többit "törli":
| 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 |
Csak egyetlen tömb feldolgozásával a SUMPRODUCT visszaadja az összes érték összegét, 201-et.
Összegzés pontos egyezés alapján
A fenti példa bemutatja, hogyan lehet összegezni az egy vagy több konkrét karakterrel kezdődő oszlopokat. A pontos egyezés alapján oszlop összegzéséhez használhat egy egyszerűbb képletet, mint ez:
=SUMPRODUCT(data*(headers=J4))
