Töltse le a példa munkafüzetet
Ez az oktatóanyag bemutatja, hogyan kell kiszámítani a „sumproduct if” értéket, visszatérve a tömbök vagy tartományok szorzatainak összegére kritériumok alapján.
SUMPRODUCT funkció
A SUMPRODUCT függvény a számok tömbjeinek megszorzására szolgál, összeadva a kapott tömböt.
A „Sumproduct If” létrehozásához a SUMPRODUCT függvényt és az IF függvényt használjuk egy tömbképletben.
ÖSSZEFOGLALÓ HA
A SUMPRODUCT és az IF tömbképletben való kombinálásával lényegében létrehozhatunk egy „SUMPRODUCT IF” függvényt, amely hasonlóan működik a beépített SUMIF függvény működéséhez. Nézzünk egy példát.
Van egy listánk a különböző régiókban a jászolok által elért értékesítésekről, megfelelő jutalékkal:
Feltételezzük, hogy minden menedzsernek ki kell számítania a jutalék összegét, így:
Ennek eléréséhez beágyazhatunk egy IF függvényt a menedzser mint kritériumaink a SUMPRODUCT függvényben, így:
= SUMPRODUCT (HA (=,*))
= SUMPRODUCT (HA ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Az Excel 2022 és korábbi verziók használatakor a képletet a gombbal kell megadni CTRL + SHIFT + ENTER hogy a göndör zárójelek köré kerüljenek a képlet (lásd a felső képet).
Hogyan működik a képlet?
A képlet úgy működik, hogy a feltételtartomány minden egyes celláját IGAZ vagy HAMIS értékeli.
Olivia teljes jutalékának kiszámítása:
= SUMPRODUCT (HA ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
= ÖSSZEFOGLALÓ (HA ({IGAZ; IGAZ; HAMIS; HAMIS; HAMIS; IGAZ; HAMIS; HAMIS; FALSE}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
Ezután az IF függvény minden értéket FALSE -ra cserél, ha annak feltétele nem teljesül.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Most a SUMPRODUCT függvény kihagyja a HAMIS értékeket, és összegezi a fennmaradó értékeket (2 077,40).
SUMPRODUCT IF több feltétellel
Ha a SUMPRODUCT IF-t több feltétellel szeretné használni (hasonlóan a beépített SUMIFS függvény működéséhez), egyszerűen helyezzen be több IF függvényt a SUMPRODUCT függvénybe, például:
= SUMPRODUCT (HA (=, HA (=, *))
(CTRL + SHIFT + ENTER)
= SUMPRODUCT (HA ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)))
(CTRL + SHIFT + ENTER)
A SUMPRODUCT IF másik megközelítése
Az Excelben gyakran többféle módon lehet elérni a kívánt eredményeket. A „sumproduct if” kiszámításának más módja a feltételek feltüntetése belül a SUMPRODUCT függvény tömbként, dupla unárist használva, így:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Ez a módszer a kettős unárist (-) használja az IGAZ HAMIS tömb nullákká és eggyé alakítására. A SUMPRODUCT ezután megszorozza az átalakított feltétel tömböket:
= SUMPRODUCT ({1; 1; 0; 0; 0; 1; 0; 0; 0}, {1; 0; 1; 0; 1; 0; 0; 0; 0}, {928.62; 668,22; 919,695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})
Tippek és trükkök:
- Ahol lehetséges, mindig zárolja be (F4) a tartományokat és a képletbevitelt, hogy lehetővé tegye az automatikus kitöltést.
- Ha Excel 2022 vagy újabb verziót használ, a Ctrl + Shift + Enter billentyűkombináció nélkül is megadhatja a képletet.
ÖSSZEFOGLALÓ HA a Google Táblázatokban
A SUMPRODUCT IF függvény pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
