Yleinen kaava
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Yhteenveto
Jos haluat laskea näkyvät rivit vain ehtojen perusteella, voit käyttää melko monimutkaista kaavaa, joka perustuu SUMPRODUCT, SUBTOTAL ja OFFSET. Esitetyssä esimerkissä C12: n kaava on:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Esipuhe
SUBTOTAL-toiminto voi helposti luoda summat ja laskelmat piilotetuille ja piilottamattomille riveille. Se ei kuitenkaan pysty käsittelemään ehtoja, kuten COUNTIF tai SUMIF, ilman apua. Yksi ratkaisu on käyttää SUMPRODUCT-toimintoa sekä SUBTOTAL-funktion (OFFSETin kautta) että kriteerien soveltamiseen. Tämän lähestymistavan yksityiskohdat kuvataan alla.
Selitys
Ytimessä tämä kaava toimii asettamalla kaksi taulukkoa SUMPRODUCTin sisälle. Ensimmäinen ryhmä soveltaa ehtoja, ja toinen taulukko käsittelee näkyvyyttä:
=SUMPRODUCT(criteria*visibility)
Kriteerejä sovelletaan osan kaavasta:
=(C5:C8=C10)
Mikä luo tällaisen taulukon:
(FALSE;TRUE;FALSE;TRUE)
Jos TOSI tarkoittaa "täyttää kriteerit". Huomaa, koska käytämme kertomista (*) tässä taulukossa, TOSI EPÄTOSI -arvot muunnetaan automaattisesti arvoksi 1 ja 0 matemaattisen operaation avulla, joten päädymme:
(0;1;0;1)
Näkyvyyssuodatin käytetään käyttämällä SUBTOTAL, funktionumero 103.
SUBTOTAL pystyy sulkemaan pois piilotetut rivit laskutoimituksia suoritettaessa, joten voimme käyttää sitä tässä tapauksessa "suodattimen" luomiseen piilotettujen rivien poissulkemiseksi SUMPRODUCT: n sisällä. Ongelmana on kuitenkin se, että SUBTOTAL palauttaa yhden numeron, kun tarvitsemme joukko tuloksia, jotta voimme käyttää sitä onnistuneesti SUMPRODUCT-sovelluksessa. Temppu on käyttää OFFSET-syötettä SUBTOTAL yksi viite riviä kohti, jotta OFFSET palauttaa yhden tuloksen riviä kohden.
Tietysti se vaatii toisen temppun, joka on antaa OFFSET-ryhmälle taulukko, joka sisältää yhden numeron riviä kohti, alkaen nollasta. Teemme sen lausekkeella, joka on rakennettu ROW-funktiolle:
=ROW(C5:C8)-MIN(ROW(C5:C8)
joka luo tällaisen taulukon:
(0;1;2;3)
Yhteenvetona voidaan todeta, että toinen taulukko (joka käsittelee näkyvyyttä käyttämällä SUBTOTAL) luodaan näin:
=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)
Ja lopuksi meillä on:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Joka palauttaa arvon 1.
Useita kriteerejä
Voit laajentaa kaavaa käsittelemään useita tällaisia ehtoja:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Tulosten yhteenveto
Jos haluat palauttaa arvojen summan laskennan sijaan, voit mukauttaa kaavaa sisällyttämään summa-alueen:
=SUMPRODUCT(criteria*visibility*sumrange)
Kriteerit ja näkyvyysryhmät toimivat samalla tavalla kuin edellä on selitetty, lukuun ottamatta soluja, jotka eivät ole näkyvissä. Jos tarvitset osittaista hakua, voit luoda lausekkeen käyttämällä ISNUMBER + SEARCH, kuten täällä on selitetty.
