Bendroji formulė
=SUMPRODUCT(expression,range)
Santrauka
Norėdami filtruoti SUMPRODUCT rezultatus pagal konkrečius kriterijus, galite naudoti paprastas logines išraiškas tiesiai funkcijos masyvams, užuot naudoję funkciją IF. Parodytame pavyzdyje formulės H5: H7 yra:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
kur apibrėžti šie įvardyti diapazonai:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Jei nenorite įvardyti diapazonų, aukščiau nurodytus diapazonus naudokite kaip absoliučias nuorodas. H6 ir H7 logines išraiškas galima sujungti, kaip paaiškinta toliau.
Paaiškinimas
Šis pavyzdys iliustruoja vieną iš pagrindinių SUMPRODUCT funkcijos stipriųjų pusių - galimybę filtruoti duomenis pagrindinėmis loginėmis išraiškomis, o ne IF funkcija. „SUMPRODUCT“ viduje pirmasis masyvas yra loginė išraiška, skirta filtruoti pagal spalvą „raudona“:
--(color="red")
Dėl to gaunamos masyvo arba TRUE FALSE reikšmės, kurios yra dvigubai neigiamos (-) operacijos verčiamos į vienetus ir nulius. Rezultatas yra šis masyvas:
(1;0;1;0;0;0;1;0;0;0)
Atkreipkite dėmesį, kad masyve yra 10 reikšmių, po vieną kiekvienoje eilutėje. Vienas rodo eilutę, kurioje spalva yra „raudona“, o nulis - eilutę su bet kokia kita spalva.
Toliau mes turime dar dvi masyvas: vieną kiekiui ir kitą kainai. Kartu su šiais pirmojo masyvo rezultatais turime:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Išplėsdami masyvus, turime:
=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))
Pagrindinis „SUMPRODUCT“ elgesys yra dauginti, tada sumuoti masyvus. Kadangi mes dirbame su trimis matricomis, mes galime vizualizuoti operaciją, kaip parodyta toliau pateiktoje lentelėje, kur rezultatas stulpelis yra dauginant rezultatas 1 masyvas * 2 masyvas * array3 :
| masyvas1 | masyvas2 | masyvas3 | rezultatas |
|---|---|---|---|
| 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“ veikia kaip filtras - nulinės vertės čia „nulinės“ vertės eilutėse, kuriose spalva nėra „raudona“. Pateikdami rezultatus atgal į „SUMPRODUCT“, turime:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Kuris grąžina galutinį rezultatą 480.
Pridedami papildomi kriterijai
Kriterijus galite išplėsti pridėdami kitą loginę išraišką. Pvz., Norint rasti bendrą pardavimą, kai spalva yra „Raudona“, o būsena - „TX“, H6 yra:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Pastaba: „SUMPRODUCT“ neskiria didžiųjų ir mažųjų raidžių.
Supaprastinimas vienu masyvu
„Excel“ profesionalai dažnai šiek tiek supaprastins SUMPRODUCT sintaksę daugindami masyvus tiesiai 1 masyvo viduje taip:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Tai veikia todėl, kad matematikos operacija (daugyba) automatiškai priverčia tikrąsias ir netikrąsias reikšmes iš pirmųjų dviejų išraiškų į vienetus ir nulius.
