17. peatükk · 17.18
Liida ainult filtreeritud read, ka lisatingimusega
Filtreerid müüginimekirja ühe kategooria järgi, kuid all olev kogusumma näitab endiselt kõigi ridade summat. See on õige: SUM liidab ka read, mille filter peidab. Kui soovid liita ainult seda, mida näed, kasuta SUBTOTAL-it. Kui soovid filtreeritud ridade seas veel lisatingimust või vigade vahelejätmist, on selleks kaks kasulikku valemit.
Näitena võtame lillepoe Bloemisterij Het Zonnetje müüginimekirja: veerus A on kuupäev, B-s toode, C-s kategooria ja D-s summa, ridadel 2 kuni 300. Päiseribal on filter (vt Sortimine ja filtreerimine).
Kuhu valemid panna?
Mitte nimekirja alla: otse nimekirja all olev valem võib filtreerimisel ise peitu jääda ja sortimisel kaasa liikuda. Aga ka mitte lihtsalt nimekirja kõrvale: filter peidab terveid ridu, nii et valem lahtris F3 kaob koos reaga 3, kui see välja filtreeritakse. Pane valemid seepärast päiseribale (rida 1, pealkirjade kõrvale) või eraldi töölehele. Näites on need real 1, alates veerust F.
Nähtavate ridade summa
=SUBTOTAL(109;D2:D300)
Esimene number valib arvutuse. 9 on summa, 109 samuti, ühe erinevusega: 109 jätab vahele ka read, mille oled käsitsi peitnud (paremklõps, Hide). 9 jätab vahele ainult filtri peidetud read. Kasuta seega 109, kui soovid liita seda, mida näed.
| Arvutus | Filtreeritud | Ka käsitsi peidetud |
|---|---|---|
| Sum | 9 | 109 |
| Täidetud lahtrite arv | 3 | 103 |
| Keskmine (Average) | 1 | 101 |
| Maximum | 4 | 104 |
Nähtavate ridade arvu loendad valemiga =SUBTOTAL(103;B2:B300).

Nähtav ja lisatingimus
Oled filtreerinud kategooria Boeketten järgi ja soovid selle sees liita ainult leinaseaded (Rouwstuk). SUMIFS ei arvesta filtrit, SUBTOTAL ei tunne tingimusi. Internetist leiad selleks valemi, mis kasutab SUMPRODUCT-i, SUBTOTAL-it ja OFFSET-it. Euro-Office'is annab see veaväärtuse #NUM!. Abiveeruga see aga õnnestub, ja lihtsamalt:
- Lisa nimekirja kõrvale veerg Võib vaadata (nähtav), siin veerg E. Vali E2:E300, kirjuta =SUBTOTAL(103;D2) ja vajuta Ctrl+Enter. Iga rida saab väärtuse 1, kui see on nähtav, ja 0, kui filter selle peidab.
- Liida SUMIFS-iga ja lisa abiveerg lisatingimuseks: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Kui muudad filtrit, arvutavad abiveerg ja summa kohe uuesti.
Veaväärtuste vahelejätmine
Kui veerus on kuskil #N/A või #DIV/0!, annavad ka SUM ja SUBTOTAL veaväärtuse. AGGREGATE oskab vigu vahele jätta:
=AGGREGATE(9;7;D2:D300)
9 on jälle summa, 7 tähendab: jäta vahele peidetud read ja veaväärtused.
Lõksud
- Peidetud veerud lähevad arvesse: SUBTOTAL arvestab ainult peidetud ridu.
