Vispārēja formula
=SUMPRODUCT(--(range1=criteria),range2)
Kopsavilkums
Summu summēšanai vai starpsummu veidošanai, pamatojoties uz blakus esošo sleju kritērijiem, varat izmantot formulu, kuras pamatā ir funkcija SUMPRODUCT. Parādītajā piemērā formula J5 ir:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Paskaidrojums
Būtībā šī formula izmanto SUMPRODUCT, lai reizinātu divu masīvu: masīvs1 un masīvs2 reizinājumus . Pirmais masīvs masīvs1 ir iestatīts darboties kā "filtrs", lai atļautu tikai vērtības, kas atbilst kritērijiem.
1. masīvs izmanto diapazonu, kas sākas pirmajā kolonnā un kurā ir vērtības, kurām jāatbilst kritērijiem. Šīs "kritēriju vērtības" atrodas kolonnā pa kreisi un tieši blakus "datu vērtībām".
Kritēriji tiek izmantoti kā vienkāršs tests, kas rada TRUE un FALSE vērtību masīvu:
--($B5:$H5=J$4)
Šis formulas bits "pārbauda" katru vērtību pirmajā masīvā, izmantojot piedāvātos kritērijus, pēc tam izmanto dubultnegatīvu (-), lai piespiestu iegūtās TRUE un FALSE vērtības uz 1 un 0. Rezultāts izskatās šādi:
(1,0,0,0,1,0,1)
Ņemiet vērā, ka 1s atbilst 1,5 un 7 kolonnām, kas atbilst "A" kritērijiem.
Par masīvs2 iekšpusē sumproduct, mēs izmantojam virkni, kas ir "pārvietoti", ko vienā kolonnā pa labi. Šis diapazons sākas ar pirmo kolonnu, kurā ir summējamas vērtības, un beidzas ar pēdējo kolonnu, kurā ir summējamās vērtības.
Tātad, pēc J5 parauga formulas pēc masīvu aizpildīšanas mums ir:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Tā kā SUMPRODUCT ir ieprogrammēts īpaši, lai ignorētu kļūdas, kas rodas, reizinot teksta vērtības, galīgais masīvs izskatās šādi:
(1,0,0,0,1,0,1)
Vienīgās vērtības, kas "pārdzīvo" reizināšanu, ir tās, kas atbilst 1s masīvā1 . Jūs varat iedomāties masīva1 loģiku, "filtrējot" masīva2 vērtības .
