17 skyrius · 17.18
Kaip sudėti tik filtruotas eilutes, taip pat su papildoma sąlyga
Jūs filtruojate pardavimų sąrašą pagal vieną kategoriją, tačiau po juo esanti suma vis tiek rodo visų eilučių sumą. Taip ir turi būti: SUM sudeda ir tas eilutes, kurias filtras paslepia. Jei norite sudėti tik tai, ką matote, naudokite SUBTOTAL. O jei filtruotose eilutėse norite taikyti dar vieną sąlygą arba praleisti klaidų reikšmes, yra dar dvi naudingos formulės.
Pavyzdžiui imame gėlių parduotuvės Bloemisterij Het Zonnetje pardavimų sąrašą: stulpelyje A yra data, B produktas, C kategorija, o D suma, nuo 2 iki 300 eilutės. Antraštės eilutėje įjungtas filtras (žr. Rūšiavimas ir filtravimas).
Kur dėti formules?
Ne po sąrašu: formulė, esanti tiesiai po sąrašu, filtruojant gali pati pasislėpti, o rikiuojant pasislinkti kartu su duomenimis. Tačiau ir ne bet kur šalia sąrašo: filtras paslepia ištisas eilutes, todėl formulė langelyje F3 išnyks kartu, jei 3 eilutė bus išfiltruota. Todėl dėkite jas antraštės eilutėje (1 eilutėje, šalia antraščių) arba atskirame darbalapyje. Pavyzdyje jos yra 1 eilutėje, pradedant stulpeliu F.
Matomų eilučių suma
=SUBTOTAL(109;D2:D300)
Pirmasis skaičius nurodo skaičiavimo būdą. 9 reiškia sumą, 109 taip pat, tik su vienu skirtumu: 109 praleidžia ir tas eilutes, kurias paslėpėte rankiniu būdu (dešiniuoju pelės mygtuku, Hide). 9 praleidžia tik filtro paslėptas eilutes. Todėl naudokite 109, jei norite sudėti tai, ką matote.
| Skaičiavimas | Filtruotos | Taip pat rankiniu būdu paslėptos |
|---|---|---|
| Sum | 9 | 109 |
| Užpildytų langelių skaičius | 3 | 103 |
| Vidurkis | 1 | 101 |
| Maximum | 4 | 104 |
Matomų eilučių skaičių suskaičiuosite su =SUBTOTAL(103;B2:B300).

Matomos eilutės ir papildoma sąlyga
Jūs filtravote pagal kategoriją Boeketten ir norite joje sudėti tik gedulo kompozicijas. SUMIFS neatsižvelgia į filtrą, o SUBTOTAL neturi sąlygos. Internete šiam tikslui rasite formulę su SUMPRODUCT, SUBTOTAL ir OFFSET. Euro-Office ji grąžina klaidos reikšmę #NUM!. Su pagalbiniu stulpeliu tai pavyksta, be to, paprasčiau:
- Šalia sąrašo sukurkite stulpelį Gali matyti, čia tai stulpelis E. Pažymėkite E2:E300, įveskite =SUBTOTAL(103;D2) ir paspauskite Ctrl+Enter. Kiekviena eilutė gauna 1, kol yra matoma, ir 0, kai filtras ją paslepia.
- Sudėkite su SUMIFS ir įtraukite pagalbinį stulpelį kaip papildomą sąlygą: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Pakeitus filtrą, pagalbinis stulpelis ir suma iškart perskaičiuojami.
Klaidų reikšmių praleidimas
Jei kur nors stulpelyje yra #N/A arba #DIV/0!, SUM ir SUBTOTAL taip pat grąžina klaidos reikšmę. AGGREGATE gali praleisti klaidas:
=AGGREGATE(9;7;D2:D300)
9 vėl reiškia sumą, o 7 reiškia: praleisti paslėptas eilutes ir klaidų reikšmes.
Spąstai
- Paslėpti stulpeliai vis tiek įskaičiuojami: SUBTOTAL atsižvelgia tik į paslėptas eilutes.
