Üldine valem
=SUMPRODUCT(expression,range)
Kokkuvõte
SUMPRODUCTi tulemuste filtreerimiseks konkreetsete kriteeriumidega saate funktsiooni IF asemel kasutada lihtsaid loogilisi väljendeid otse funktsiooni massiividele. Näidatud näites on valemid H5: H7:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
kus on määratletud järgmised nimevahemikud:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Kui soovite pigem nimetada vahemikke, kasutage ülaltoodud vahemikke absoluutsete viidetena. H6 ja H7 loogilisi väljendeid saab kombineerida, nagu allpool selgitatud.
Selgitus
See näide illustreerib funktsiooni SUMPRODUCT ühte võtmetugevust - võimalust funktsiooni IF asemel andmeid filtreerida põhiliste loogiliste avaldistega. SUMPRODUCTi sees on esimene massiiv loogiline avaldis, mis filtreerib värvi "punane":
--(color="red")
Selle tulemuseks on massiivi või TRUE FALSE väärtused, mis sunnitakse kahekordse negatiivse (-) toiminguga üksusteks ja nullideks. Tulemuseks on see massiiv:
(1;0;1;0;0;0;1;0;0;0)
Pange tähele, et massiiv sisaldab 10 väärtust, ühe iga rea jaoks. Üks tähistab rida, kus värv on "punane", ja null tähistab rida mis tahes muu värviga.
Järgmisena on meil veel kaks massiivi: üks koguse ja teine hinna jaoks. Koos selle esimese massiivi tulemustega on meil:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Massiivide laiendamisel on meil:
=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))
SUMPRODUCTi põhikäitumine on massiivide korrutamine, seejärel summeerimine. Kuna me töötame koos kolm massiivid, saame visualiseerida operatsiooni, nagu on näidatud allpool tabelis, kus tulemus veerus on tulemus korrutatakse massiiv1 * massiiv2 * array3 :
| massiiv1 | massiiv2 | massiiv3 | tulemus |
|---|---|---|---|
| 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 töötab filtrina - siin on nullväärtused "nullida" väärtused ridades, kus värv ei ole "punane". Tulemused SUMPRODUCTi tagasi pannes on meil:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Mis annab tulemuseks 480.
Lisakriteeriumide lisamine
Kriteeriume saate laiendada, lisades veel ühe loogilise avaldise. Näiteks kogu müügi leidmiseks, kus värv on "punane" ja olek on "TX", sisaldab H6 järgmist:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Märkus. SUMPRODUCT ei ole tõstutundlik.
Lihtsustamine ühe massiiviga
Excel plusse sageli lihtsustada süntaks sees SUMPRODUCT natuke korrutades massiivid otseselt sees massiiv1 niimoodi:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
See töötab, sest matemaatikaoperatsioon (korrutamine) sunnib TÕENE ja VÄÄR väärtused kahest esimesest avaldisest automaatselt üheks ja nulliks.
