Laadige alla töövihiku näide
See õpetus näitab, kuidas arvutada „summaprodukt, kui”, tagastades kriteeriumide alusel massiivide või vahemike korrutiste summa.
Funktsioon SUMPRODUCT
Funktsiooni SUMPRODUCT kasutatakse numbrimassiivide korrutamiseks, tulemuseks oleva massiivi summeerimiseks.
„Sumproduct If” loomiseks kasutame massiivivalemis funktsiooni SUMPRODUCT koos funktsiooniga IF.
SUMPRODUCT IF
Kombineerides massiivivalemis SUMPRODUCT ja IF, saame sisuliselt luua funktsiooni „SUMPRODUCT IF”, mis töötab sarnaselt sisseehitatud SUMIF-funktsiooni tööga. Lähme läbi näite.
Meil on nimekiri müükidest, mida sõdurid on saavutanud erinevates piirkondades koos vastavate vahendustasudega:
Oletame, et meil palutakse arvutada iga halduri vahendustasu järgmiselt:
Selle saavutamiseks saame IF -funktsiooni sisestada juhataja kui meie kriteeriumid funktsiooni SUMPRODUCT sees nii:
= SUMPRODUCT (IF (=,*))
= SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Kui kasutate Excel 2022 ja varasemat versiooni, peate valemi sisestama, vajutades CTRL + SHIFT + ENTER et saada lokkisulud valemi ümber (vt ülemist pilti).
Kuidas valem töötab?
Valem töötab, hinnates meie kriteeriumivahemiku iga lahtrit tõeks või valeks.
Olivia kogukulude arvutamine:
= SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
= SUMPRODUCT (IF ({TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}), {928.62; 668.22; 919,695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
Seejärel asendab funktsioon IF iga väärtuse väärtusega VÄÄR, kui selle tingimus ei ole täidetud.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Nüüd jätab funktsioon SUMPRODUCT vahele valed väärtused ja summeerib ülejäänud väärtused (2077,40).
SUMPRODUCT IF mitme kriteeriumiga
Kui soovite kasutada SUMPRODUCT IF-d mitme kriteeriumiga (sarnaselt sisseehitatud SUMIFS-funktsiooni tööga), pesake lihtsalt funktsioonis SUMPRODUCT rohkem IF-funktsioone järgmiselt.
= SUMPRODUCT (IF (=, IF (=, *))
(CTRL + SHIFT + ENTER)
= SUMPRODUCT (IF ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)))
(CTRL + SHIFT + ENTER)
Teine lähenemisviis SUMPRODUCT IF -le
Sageli on Excelis soovitud tulemuste saamiseks mitu võimalust. „Sumproduct if” arvutamiseks on teistsugune viis kriteeriumide lisamine sees funktsioon SUMPRODUCT massiivina, kasutades kahekordset ühist, nii:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
See meetod kasutab topelt unaarset (-), et teisendada TÕELINE VÄÄR massiiv nullideks ja üksikuteks. Seejärel korrutab SUMPRODUCT teisendatud kriteeriumide massiivid kokku:
= 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})
Nõuanded ja nipid:
- Võimaluse korral lukustage alati oma vahemikud ja valemisisendid, et lubada automaatne täitmine.
- Kui kasutate Excel 2022 või uuemat, võite valemi sisestada ilma Ctrl + Tõstuklahv + Enter.
SUMPRODUCT IF Google'i arvutustabelites
Funktsioon SUMPRODUCT IF töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
