Általános képlet
=SUMPRODUCT(--(range1=criteria),range2)
Összegzés
A szomszédos oszlopok kritériumai alapján oszlopok összegzéséhez vagy részösszegéhez a SUMPRODUCT függvényen alapuló képletet használhatja. A bemutatott példában a J5 képlete a következő:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Magyarázat
A lényege, ez a képlet használ SUMPRODUCT a többszörösen majd összefoglalni termékek két tömb: tömb1 és tömb2 . Az első tömb, az array1 úgy van beállítva, hogy "szűrőként" működjön, és csak a kritériumoknak megfelelő értékeket engedélyezze.
Az 1. tömb olyan tartományt használ, amely az első oszlopban kezdődik, és amely olyan feltételeket tartalmaz, amelyeknek meg kell felelniük a feltételeknek. Ezek a "kritériumértékek" egy oszlopban helyezkednek el az "adatértékek" bal oldalán és közvetlenül mellettük.
A feltételeket egyszerű tesztként alkalmazzák, amely TRUE és FALSE értékek tömbjét hozza létre:
--($B5:$H5=J$4)
A képletnek ez a bitje az első tömb minden értékét a tesztelt kritériumok alapján teszteli, majd kettős negatív (-) értékkel kényszeríti a kapott IGAZ és HAMIS értékeket 1-re és 0-ra. Az eredmény így néz ki:
(1,0,0,0,1,0,1)
Megjegyezzük, hogy az 1-es szám megfelel az 1,5-ös és a 7-es oszlopnak, amelyek megfelelnek az "A" kritériumoknak.
A SUMPRODUCT belsejében lévő 2. tömbhöz egy olyan oszlopot használunk, amelyet egy oszlop "jobbra tol". Ez a tartomány azzal kezdődik, hogy az első oszlop tartalmazza az összesítendő értékeket, és az utolsó oszloppal zárul, amely az összesítendő értékeket tartalmazza.
Tehát a J5 példában szereplő képletben a tömbök feltöltése után:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Mivel a SUMPRODUCT programozása kifejezetten a szövegértékek szorzásából fakadó hibák figyelmen kívül hagyására szolgál, a végső tömb így néz ki:
(1,0,0,0,1,0,1)
Az egyetlen érték, amely "túléli" a szorzást, azok, amelyek 1-nek felelnek meg a tömbben1 . Gondolhat az 1. tömb logikájára, hogy " kiszűri " a tömb2 értékeit .
