Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, kuinka "sumproduct if" lasketaan ja palautetaan matriisien tai alueiden tuotteiden summa kriteerien perusteella.
SUMPRODUCT -toiminto
SUMPRODUCT -funktiota käytetään lukutaulukkojen kertomiseen, tuloksena olevan taulukon summaamiseksi.
"Sumproduct If": n luomiseksi käytämme SUMPRODUCT -funktiota ja IF -funktiota taulukkokaavassa.
SUMPRODUCT IF
Yhdistämällä SUMPRODUCT ja IF taulukkokaavaan voimme luoda olennaisesti SUMPRODUCT IF -funktion, joka toimii samalla tavalla kuin sisäänrakennettu SUMIF-toiminto. Käydään esimerkki läpi.
Meillä on luettelo myyjien saavutetuista myynnistä eri alueilla vastaavilla provisioilla:
Olettaen, että meitä pyydetään laskemaan palkkioiden määrä jokaiselle esimiehelle seuraavasti:
Tätä varten voimme liittää IF -funktion johtaja kriteereinämme SUMPRODUCT -funktion sisällä, näin:
= SUMPRODUCT (JOS (=,*))
= SUMPRODUCT (JOS ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Kun käytät Excel 2022 ja sitä vanhempia versioita, sinun on annettava kaava painamalla CTRL + VAIHTO + ENTER saadaksesi kaarevat hakasulkeet kaavan ympärille (katso yläkuva).
Miten kaava toimii?
Kaava toimii arvioimalla kriteerialueemme jokainen solu TOSI tai EPÄTOSI.
Olivian palkkion laskeminen:
= SUMPRODUCT (JOS ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
= SUMPRODUCT (JOS ({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}))
Seuraavaksi IF -toiminto korvaa jokaisen arvon arvolla FALSE, jos sen ehto ei täyty.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Nyt SUMPRODUCT -toiminto ohittaa FALSE -arvot ja summaa loput arvot (2 077,40).
SUMPRODUCT IF, jossa on useita ehtoja
Jos haluat käyttää SUMPRODUCT IF -ohjelmaa useiden ehtojen kanssa (samanlainen kuin sisäänrakennettu SUMIFS-toiminto), liitä yksinkertaisesti lisää IF-toimintoja SUMPRODUCT-funktioon seuraavasti:
= SUMPRODUCT (JOS (=, JOS (=, *))
(CTRL + VAIHTO + ENTER)
= SUMPRODUCT (JOS ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)))
(CTRL + VAIHTO + ENTER)
Toinen lähestymistapa SUMPRODUCT IF
Usein Excelissä on useita tapoja saada halutut tulokset. Eri tapa laskea "sumproduct if" on sisällyttää kriteerit sisällä SUMPRODUCT -funktio matriisina, joka käyttää kaksinkertaista unaria, kuten:
= SUMPRODUCT (-(($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Tämä menetelmä käyttää kaksinkertaista unaarista (-) muuntaakseen TOSI EPÄTOSI matriisin nolliksi ja ykköiksi. SUMPRODUCT kertoo sitten muunnetut kriteeritaulukot yhteen:
= 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})
Vinkkejä ja temppuja:
- Jos mahdollista, lukitse aina alueet ja kaavasyötteet (F4) automaattisen täytön sallimiseksi.
- Jos käytät Excel 2022 tai uudempaa, voit kirjoittaa kaavan ilman Ctrl + Vaihto + Enter.
SUMPRODUCT IF Google Sheetsissä
SUMPRODUCT IF -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:
