Bendroji formulė
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Santrauka
Norėdami suskaičiuoti matomas eilutes tik pagal kriterijus, galite naudoti gana sudėtingą formulę, pagrįstą SUMPRODUCT, SUBTOTAL ir OFFSET. Parodytame pavyzdyje formulė C12 yra:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Įvadas
Funkcija SUBTOTAL gali lengvai sugeneruoti paslėptų ir neslėptų eilučių sumas ir skaičiavimus. Tačiau be jokios pagalbos jis negali apdoroti tokių kriterijų kaip COUNTIF ar SUMIF. Vienas iš sprendimų yra naudoti SUMPRODUCT, kad būtų pritaikyta funkcija SUBTOTAL (per OFFSET) ir kriterijai. Šio metodo detalės aprašytos toliau.
Paaiškinimas
Iš esmės ši formulė veikia nustatant du masyvus SUMPRODUCT viduje. Pirmasis masyvas taiko kriterijus, o antrasis - matomumą:
=SUMPRODUCT(criteria*visibility)
Kriterijai taikomi daliai formulės:
=(C5:C8=C10)
Kuria tokį masyvą:
(FALSE;TRUE;FALSE;TRUE)
Kur TIESA reiškia „atitinka kriterijus“. Atkreipkite dėmesį, kad šiame masyve naudojame dauginimą (*), todėl TRUE FALSE reikšmės bus automatiškai konvertuojamos į 1 ir 0 pagal matematikos operaciją, todėl galiausiai:
(0;1;0;1)
Matomumo filtras taikomas naudojant SUBTOTAL, kurio funkcijos numeris 103.
Vykdydami skaičiavimus, SUBTOTAL gali išskirti paslėptas eilutes, todėl šiuo atveju galime ją naudoti norėdami sukurti „filtrą“, kad pašalintumėte paslėptas eilutes SUMPRODUCT viduje. Vis dėlto problema yra ta, kad SUBTOTAL grąžina vieną skaičių, o mums reikia rezultatų masyvo, kad jį sėkmingai naudotume SUMPRODUCT. Apgaulė yra tai, kad naudodamiesi OFFSET pateikite SUBTOTAL vieną nuorodą vienoje eilutėje, kad OFFSET grąžintų po vieną rezultatą kiekvienoje eilutėje.
Žinoma, tam reikia dar vienos gudrybės, ty suteikti OFFSET masyvą, kuriame yra vienas skaičius eilutėje, pradedant nuliu. Mes darome tai su išraiška, sukurta funkcija ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
kuris sukurs tokį masyvą:
(0;1;2;3)
Apibendrinant galima pasakyti, kad antrasis masyvas (kuris tvarko matomumą naudodamas SUBTOTAL) yra sukurtas taip:
=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)
Galiausiai turime:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Kuris grąžina 1.
Keli kriterijai
Galite išplėsti formulę, kad ji atitiktų kelis tokius kriterijus:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Sumuojant rezultatus
Jei norite grąžinti reikšmių sumą, o ne skaičiavimą, galite pritaikyti formulę ir įtraukti sumų diapazoną:
=SUMPRODUCT(criteria*visibility*sumrange)
Kriterijai ir matomumo masyvai veikia taip pat, kaip paaiškinta aukščiau, išskyrus langelius, kurie nėra matomi. Jei reikia dalinio atitikimo, galite sukonstruoti išraišką naudodami ISNUMBER + SEARCH, kaip paaiškinta čia.
