Általános képlet
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Összegzés
Az egymáshoz illő oszlopokban és sorokban lévő értékek összegzéséhez használhatja a SUMPRODUCT függvényt. A bemutatott példában a J6 képlete a következő:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
ahol az adatok (C5: G14), a napok (B5: B14) és a kódok (C4: G4) tartományokat neveznek meg.
Magyarázat
A SUMPRODUCT funkció natívan képes kezelni a tömböket, anélkül, hogy a vezérlő shift váltóját meg kellene adni.
Ebben az esetben a megnevezett tartományadatok összes értékét megszorozzuk két olyan kifejezéssel, amelyek kiszűrik a nem érdekes értékeket. Az első kifejezés a kódokon alapuló szűrőt alkalmaz:
(codes=J4)
Mivel a J4 "A002" -et tartalmaz, a kifejezés az IGAZ HAMIS értékek tömbjét hozza létre:
(FALSE,TRUE,FALSE,FALSE,FALSE)
A második kifejezés a napon szűr:
(days=J5)
Mivel a J4 "Wed" -et tartalmaz, a kifejezés a TRUE FALSE értékek tömbjét hozza létre, mint ez:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Az Excelben az IGAZ HAMIS értékeket minden matematikai művelet automatikusan 1 és 0 értékekre kényszeríti, így a szorzási művelet a fenti tömböket eggyel és nullával kényszeríti, és létrehoz egy 2D tömböt, amelynek méretei megegyeznek az eredeti adatokkal. A folyamat az alábbiak szerint vizualizálható:
Végül a SUMPRODUCT visszaadja az utolsó tömb összes elemének összegét, 9.
Számoljon összeg helyett
Ha összegzés helyett az egyező értékeket szeretné megszámolni, akkor a képletet lerövidítheti:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Vegye figyelembe, hogy ez a számlálás üres cellákat fog tartalmazni.
Megjegyzések
- Noha a példa csak egy illesztett oszlopot mutat, ez a képlet helyesen összegzi több egyező oszlopot.
- Ha csak az oszlopoknak (nem a soroknak) kell egyeznie, használhat egy ilyen képletet.
- Csak a sorok egyezéséhez használhatja a COUNTIFS függvényt.
