Vispārēja formula
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Kopsavilkums
Lai uzskaitītu redzamās rindas tikai ar kritērijiem, varat izmantot diezgan sarežģītu formulu, kuras pamatā ir SUMPRODUCT, SUBTOTAL un OFFSET. Parādītajā piemērā formula C12 ir:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Priekšvārds
Funkcija SUBTOTAL var viegli ģenerēt summas un skaitījumus slēptām un neslēptām rindām. Tomēr bez palīdzības nav iespējams apstrādāt tādus kritērijus kā COUNTIF vai SUMIF. Viens risinājums ir izmantot SUMPRODUCT, lai piemērotu gan funkciju SUBTOTAL (izmantojot OFFSET), gan kritērijus. Šīs pieejas detaļas ir aprakstītas turpmāk.
Paskaidrojums
Būtībā šī formula darbojas, SUMPRODUCT iekšpusē izveidojot divus masīvus. Pirmajā masīvā tiek izmantoti kritēriji, un otrajā masīvā tiek rīkota redzamība:
=SUMPRODUCT(criteria*visibility)
Kritēriji tiek izmantoti, izmantojot daļu no formulas:
=(C5:C8=C10)
Kas ģenerē šādu masīvu:
(FALSE;TRUE;FALSE;TRUE)
Kur TRUE nozīmē "atbilst kritērijiem". Ņemiet vērā, ka šajā masīvā mēs izmantojam reizināšanu (*), TRUE FALSE vērtības matemātikas operācijas rezultātā automātiski tiks pārvērstas par 1 un 0, tāpēc mēs galu galā iegūsim:
(0;1;0;1)
Redzamības filtrs tiek lietots, izmantojot SUBTOTAL ar funkcijas numuru 103.
SUBTOTAL var izslēgt slēptās rindas, veicot aprēķinus, tāpēc mēs to varam izmantot šajā gadījumā, lai ģenerētu "filtru", lai izslēgtu slēptās rindas SUMPRODUCT iekšpusē. Tomēr problēma ir tā, ka SUBTOTAL atgriež vienu skaitli, savukārt mums ir nepieciešams rezultātu masīvs, lai to veiksmīgi izmantotu SUMPRODUCT. Viltība ir izmantot OFFSET, lai barotu SUBTOTAL vienu atsauci katrā rindā, lai OFFSET atgrieztu vienu rezultātu katrā rindā.
Protams, tas prasa vēl vienu triku, proti, piešķirt OFFSET masīvu, kurā katrā rindā ir viens skaitlis, sākot ar nulli. Mēs to darām ar izteiksmi, kas balstīta uz funkciju ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
kas ģenerēs šādu masīvu:
(0;1;2;3)
Rezumējot, otrais masīvs (kas apskata redzamību, izmantojot SUBTOTAL) tiek ģenerēts šādi:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
Un, visbeidzot, mums ir:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Kas atgriež 1.
Vairāki kritēriji
Jūs varat paplašināt formulu, lai apstrādātu vairākus šādus kritērijus:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Rezultātu apkopošana
Lai atgrieztu vērtību summu, nevis skaitīšanu, varat pielāgot formulu, lai iekļautu summu diapazonu:
=SUMPRODUCT(criteria*visibility*sumrange)
Kritēriji un redzamības masīvi darbojas tāpat kā iepriekš paskaidrots, izslēdzot šūnas, kuras nav redzamas. Ja jums nepieciešama daļēja atbilstība, varat izveidot izteiksmi, izmantojot ISNUMBER + SEARCH, kā paskaidrots šeit.
