Vispārēja formula
=SUMPRODUCT(expression,range)
Kopsavilkums
Lai filtrētu SUMPRODUCT rezultātus ar noteiktiem kritērijiem, varat izmantot vienkāršas loģiskās izteiksmes tieši funkcijas masīviem, nevis izmantot IF funkciju. Parādītajā piemērā formulas H5: H7 ir:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
kur ir definēti šādi nosauktie diapazoni:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Ja vēlaties izvairīties no nosauktajiem diapazoniem, izmantojiet iepriekš ievadītos diapazonus kā absolūtas atsauces. H6 un H7 loģiskās izteiksmes var apvienot, kā paskaidrots turpmāk.
Paskaidrojums
Šis piemērs ilustrē vienu no SUMPRODUCT funkcijas galvenajām priekšrocībām - spēju filtrēt datus ar pamata loģiskām izteiksmēm IF funkcijas vietā. SUMPRODUCT iekšpusē pirmais masīvs ir loģiska izteiksme, kas jāfiltrē pēc krāsas “sarkanā”:
--(color="red")
Tā rezultātā tiek izveidotas masīva vai TRUE FALSE vērtības, kuras ar dubultnegatīvas (-) darbības palīdzību tiek piespiestas vienībās un nullēs. Rezultāts ir šāds masīvs:
(1;0;1;0;0;0;1;0;0;0)
Ievērojiet, ka masīvā ir 10 vērtības, viena katrai rindai. Viens norāda rindu, kur krāsa ir "sarkana", un nulle norāda rindu ar jebkuru citu krāsu.
Tālāk mums ir vēl divi masīvi: viens daudzumam un otrs cenai. Kopā ar šiem pirmā masīva rezultātiem mums ir:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Paplašinot masīvus, mums ir:
=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))
SUMPRODUCT galvenā uzvedība ir reizināt, pēc tam summēt masīvus. Tā kā mēs strādājam ar trim masīviem, mēs varam iztēloties darbību, kā norādīts tabulā, kur rezultāts kolonna ir rezultāts, reizinot masīvs1 * masīvs2 * array3 :
| masīvs1 | masīvs2 | masīvs3 | rezultāts |
|---|---|---|---|
| 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 |
Paziņojuma masīvs1 darbojas kā filtrs - nulles vērtības šeit "nulle" vērtības rindās, kurās krāsa nav "sarkana". Ievietojot rezultātus atpakaļ SUMPRODUCT, mums ir:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Kas atgriež gala rezultātu 480.
Pievienojot papildu kritērijus
Kritērijus varat paplašināt, pievienojot citu loģisku izteiksmi. Piemēram, lai atrastu kopējo pārdošanas apjomu, kur krāsa ir "Sarkana" un valsts ir "TX", H6 satur:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Piezīme: SUMPRODUCT nav reģistrjutīgs.
Vienkāršošana ar vienu masīvu
Excel profesionāļi bieži nedaudz vienkāršos sintaksi SUMPRODUCT iekšpusē, reizinot masīvus tieši masīva1 iekšpusē šādi:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Tas darbojas tāpēc, ka matemātikas operācija (reizināšana) automātiski piesaista TRUE un FALSE vērtības no pirmajām divām izteiksmēm vienībās un nullēs.
