Vispārēja formula
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Kopsavilkums
Lai summētu vērtības atbilstošās kolonnās un rindās, varat izmantot funkciju SUMPRODUCT. Parādītajā piemērā formula J6 ir:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
kur dati (C5: G14), dienas (B5: B14) un kodi (C4: G4) ir nosaukti diapazoni.
Paskaidrojums
Funkcija SUMPRODUCT var automātiski apstrādāt masīvus, neprasot vadības nobīdes ievadi.
Šajā gadījumā mēs reizinām visas nosaukto diapazona datu vērtības ar divām izteiksmēm, kas filtrē neinteresējošās vērtības. Pirmajā izteiksmē tiek lietots filtrs, kura pamatā ir kodi:
(codes=J4)
Tā kā J4 satur "A002", izteiksme izveido šādu TRUE FALSE masīvu:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Otra izteiksme tiek filtrēta dienā:
(days=J5)
Tā kā J4 satur "Wed", izteiksme izveido šādu TRUE FALSE masīvu:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Programmā Excel jebkuras matemātiskās darbības rezultātā TRUE FALSE vērtības tiek automātiski piespiestas 1 un 0 vērtībām, tāpēc reizināšanas operācija augstāk minētos masīvus piespiež vieniem un nullēm un izveido 2D masīvu ar tādām pašām dimensijām kā sākotnējie dati. Procesu var vizualizēt, kā parādīts zemāk:
Visbeidzot, SUMPRODUCT atgriež visu masīva elementu summu 9.
Skaitīt nevis summēt
Ja vēlaties saskaitīt atbilstošās vērtības, nevis summēt, varat saīsināt formulu līdz:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Ņemiet vērā, ka šajā skaitā tiks iekļautas tukšas šūnas.
Piezīmes
- Lai gan piemērā ir redzama tikai viena atbilstoša kolonna, šī formula pareizi summēs vairākas atbilstošās kolonnas.
- Ja jums jāatbilst tikai kolonnām (nevis rindām), varat izmantot šādu formulu.
- Lai saskaņotu tikai rindas, varat izmantot funkciju COUNTIFS.
