17. nodaļa · 17.18
Tikai filtrēto rindu summēšana, arī ar papildu nosacījumu
Jūs filtrējat pārdošanas sarakstu pēc vienas kategorijas, bet kopsumma zem tā joprojām rāda visu rindu summu. Tā tam jābūt: SUM summē arī rindas, kuras filtrs paslēpj. Ja vēlaties summēt tikai to, ko redzat, izmantojiet SUBTOTAL. Ja filtrētajās rindās Jums vajadzīgs vēl papildu nosacījums vai vēlaties izlaist kļūdu vērtības, tam ir vēl divas noderīgas formulas.
Kā piemēru izmantosim ziedu veikala Bloemisterij Het Zonnetje pārdošanas sarakstu: A kolonnā ir datums, B kolonnā produkts, C kolonnā kategorija un D kolonnā summa, no 2. līdz 300. rindai. Virsrakstu rindai ir pievienots filtrs (skatiet Šķirošana un filtrēšana).
Kur novietot formulas?
Ne zem saraksta: formula tieši zem saraksta filtrējot var tikt paslēpta pati, bet kārtojot var pārvietoties kopā ar datiem. Taču arī ne vienkārši blakus sarakstam: filtrs paslēpj veselas rindas, tāpēc formula šūnā F3 pazūd, ja 3. rinda tiek izfiltrēta. Tāpēc novietojiet formulas virsrakstu rindā (1. rindā, blakus virsrakstiem) vai atsevišķā darblapā. Piemērā tās atrodas 1. rindā, sākot no F kolonnas.
Redzamo rindu summa
=SUBTOTAL(109;D2:D300)
Pirmais skaitlis nosaka aprēķinu. 9 ir summa, 109 arī, ar vienu atšķirību: 109 izlaiž arī rindas, kuras esat paslēpis manuāli (peles labā poga, Paslēpt). 9 izlaiž tikai tās rindas, kuras paslēpj filtrs. Tāpēc izmantojiet 109, ja vēlaties summēt to, ko redzat.
| Aprēķins | Filtrētas | Arī manuāli paslēptas |
|---|---|---|
| Summa | 9 | 109 |
| Aizpildīto šūnu skaits | 3 | 103 |
| Viduspunkts | 1 | 101 |
| Maksimums | 4 | 104 |
Redzamo rindu skaitu varat saskaitīt ar =SUBTOTAL(103;B2:B300).

Redzamās rindas un papildu nosacījums
Jūs esat filtrējis pēc kategorijas Boeketten un tajā vēlaties summēt tikai sēru kompozīcijas. SUMIFS neņem vērā filtru, savukārt SUBTOTAL neatbalsta nosacījumus. Internetā šim nolūkam atradīsiet formulu ar SUMPRODUCT, SUBTOTAL un OFFSET. Euro-Office tā atgriež kļūdas vērtību #NUM!. Ar palīgkolonnu tas izdodas, turklāt vienkāršāk:
- Izveidojiet blakus sarakstam kolonnu Zichtbaar (redzams), šeit tā ir E kolonna. Atlasiet E2:E300, ierakstiet =SUBTOTAL(103;D2) un nospiediet Ctrl+Enter. Katrā rindā būs 1, kamēr tā ir redzama, un 0, ja filtrs to paslēpj.
- Summējiet ar SUMIFS un izmantojiet palīgkolonnu kā papildu nosacījumu: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Ja maināt filtru, palīgkolonna un summa uzreiz tiek pārrēķinātas.
Kļūdu vērtību izlaišana
Ja kaut kur kolonnā ir #N/A vai #DIV/0!, arī SUM un SUBTOTAL atgriež kļūdas vērtību. AGGREGATE var kļūdas izlaist:
=AGGREGATE(9;7;D2:D300)
Skaitlis 9 atkal nozīmē summu, bet 7 nozīmē: izlaist paslēptās rindas un kļūdu vērtības.
Biežākās kļūdas
- Paslēptās kolonnas tiek ņemtas vērā: SUBTOTAL ņem vērā tikai paslēptās rindas.
