Bendroji formulė
=SUMPRODUCT(--(range1=criteria),range2)
Santrauka
Norėdami sumuoti stulpelius arba sudaryti tarpines sumas pagal gretimų stulpelių kriterijus, galite naudoti formulę, pagrįstą funkcija SUMPRODUCT. Parodytame pavyzdyje formulė J5 yra:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Paaiškinimas
Iš esmės ši formulė naudoja SUMPRODUCT, kad padaugintų dviejų masyvų: masyvo1 ir masyvo2 sandaugas . Pirmasis masyvas1 masyvas yra nustatytas veikti kaip „filtras“, leidžiantis leisti tik reikšmes, atitinkančias kriterijus.
Masyvas1 naudoja diapazoną, prasidedantį nuo pirmojo stulpelio, kuriame yra reikšmės, kurios turi atitikti kriterijus. Šios „kriterijų vertės“ yra stulpelyje, esančiame kairėje „duomenų reikšmių“ kairėje ir šalia jų.
Kriterijai taikomi kaip paprastas testas, sukuriantis TRUE ir FALSE reikšmių masyvą:
--($B5:$H5=J$4)
Šis formulės bitas „patikrina“ kiekvieną pirmojo masyvo vertę naudodamas pateiktus kriterijus, tada naudoja dvigubą neigiamą (-), kad gautos TRUE ir FALSE reikšmės būtų priverstos 1 ir 0. Rezultatas atrodo taip:
(1,0,0,0,1,0,1)
Atkreipkite dėmesį, kad 1 atitinka 1,5 ir 7 stulpelius, kurie atitinka „A“ kriterijus.
Dėl 2 masyvas vidų SUMPRODUCT, mes naudojame įvairias kad yra "perkeliamas" vienos skilties į dešinę. Šis diapazonas prasideda pirmajame stulpelyje, kuriame pateikiamos sumos, ir baigiasi paskutiniu stulpeliu, kuriame yra sumos vertės.
Taigi, pavyzdyje J5 pateiktoje formulėje, po masyvų užpildymo, mes turime:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Kadangi „SUMPRODUCT“ yra užprogramuotas specialiai ignoruoti klaidas, atsirandančias padauginus teksto reikšmes, galutinis masyvas atrodo taip:
(1,0,0,0,1,0,1)
Vienintelės vertės, kurios „išgyvena“ dauginimąsi, yra tos, kurios atitinka 1 masyvo viduje1 . Jūs galite galvoti apie logikos 1 masyvas "filtravimo" reikšmes 2 masyvas .
