Yleinen kaava
=SUMPRODUCT(expression,range)
Yhteenveto
Voit suodattaa SUMPRODUCT-tuloksia tietyin ehdoin käyttämällä yksinkertaisia loogisia lausekkeita suoraan funktion matriiseihin IF-funktion käyttämisen sijaan. Esitetyssä esimerkissä kaavat kohdissa H5: H7 ovat:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
jossa määritellään seuraavat nimetyt alueet:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Jos haluat välttää nimettyjä alueita, käytä yllä annettuja alueita absoluuttisina viitteinä. H6: n ja H7: n loogiset lausekkeet voidaan yhdistää, kuten alla selitetään.
Selitys
Tämä esimerkki havainnollistaa yhtä SUMPRODUCT-funktion keskeisistä vahvuuksista - kykyä suodattaa tietoja loogisilla peruslausekkeilla IF-funktion sijaan. SUMPRODUCTin sisällä ensimmäinen taulukko on looginen lauseke, joka suodatetaan punaisen värin mukaan:
--(color="red")
Tämä johtaa matriisi- tai TOSI-EPÄTOSI-arvoihin, jotka pakotetaan ykkösiksi ja nolliksi kaksoisnegatiivisella (-) toiminnalla. Tuloksena on tämä taulukko:
(1;0;1;0;0;0;1;0;0;0)
Huomaa, että taulukossa on 10 arvoa, yksi kullekin riville. Yksi osoittaa rivin, jossa väri on "punainen", ja nolla osoittaa rivin minkä tahansa muun värin kanssa.
Seuraavaksi meillä on vielä kaksi taulukkoa: yksi määrälle ja toinen hinnalle. Yhdessä tämän ensimmäisen taulukon tulosten kanssa meillä on:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Laajentamalla taulukoita meillä on:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
SUMPRODUCTin ydinkäyttäytyminen on kertoa ja sitten laskea matriisit. Koska teemme kolme matriisia, voimme nähdä toimintaa, kuten on esitetty alla olevassa taulukossa, jossa tulos sarake on saatu kertomalla matriisi1 * matriisi2 * array3 :
| taulukko 1 | taulukko 2 | taulukko 3 | tulos |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Notice array1 toimii suodattimena - nolla-arvot tässä "nollaavat" -arvot riveillä, joissa väri ei ole "punainen". Palauttamalla tulokset takaisin SUMPRODUCT-palveluun meillä on:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Mikä palauttaa lopputuloksen 480.
Lisäehtojen lisääminen
Voit laajentaa ehtoja lisäämällä toisen loogisen lausekkeen. Esimerkiksi, jos haluat löytää kokonaismyynnin, jossa väri on "punainen" ja tila on "TX", H6 sisältää:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Huomaa: SUMPRODUCT ei eroa isoja ja pieniä kirjaimia.
Yksinkertaistaminen yhdellä taulukolla
Excel ammattilaiset usein yksinkertaistaa syntaksin sisällä SUMPRODUCT hiukan kertomalla taulukot suoraan sisälle matriisi1 näin:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Tämä toimii, koska matematiikkaoperaatio (kertolasku) pakottaa TOSI- ja EPÄTOSI-arvot automaattisesti kahdesta ensimmäisestä lausekkeesta yksiköiksi ja nolliksi.
