Filter a SUMIF (+VIDEO)

EXCEL – Filtrovanie patrí k najpoužívanejším nástrojom v Exceli. Skombinovať s ním výpočty dáva občas zabrať. Jednoduchú sumu nahradí funkcia SUBTOTAL alebo AGGREGATE. Ale čo v prípade, keď je potrebné filter skombinovať ešte aj s funkciami SUMIF alebo COUNTIF?

Aj to sa samozrejme dá. Samotný zápis vzorca však na prvý pohľad môže vyzerať trošku desivo… Veď posúďte sami:

=SUMPRODUCT(SUBTOTAL(109;OFFSET(D6;ROW(D6:D20)-ROW(D6);0));–(B6:B20=C1))

Pekné, však? 🙂 Paradoxom je, že tam funkcia SUMIF vôbec nie je.

No nie je to až také hrozné, ako sa to zdá. Krok za krokom vysvetlím, čo ktorá funkcia robí.

Ale najskôr priblížim celú pointu článku. Máme nejakú databázu – napr. na obrázku sú obchodníčky, suma a mesto, v ktorom obchodovali.

Výpočty bez filtra (t.j. z celej tabuľky):

• ak chcete vypočítať celkovú sumu predaja, použijete funkciu SUM,
• ak chcete vypočítať celkovú sumu predaja iba pre Lucku, použijete funkciu SUMIF,
• ak chcete vypočítať počet predajov, použijete COUNT,
• ak chcete vypočítať počet Luckiných, použijete COUNTIF.

Dali by sa použiť aj iné funkcie, ale tieto sú v daných prípadoch najšikovnejšie.

Výpočty s filtrom:

• ak vyfiltrujete iba mesto Bratislava (BA) a chcete sumu predaja vyfiltrovaných, tak použijete funkciu SUBTOTAL alebo AGGREGATE,
• ak vyfiltrujete iba mesto Bratislava (BA) a chcete počet vyfiltrovaných, tiež použijete funkciu SUBTOTAL alebo AGGREGATE,
• ak vyfiltrujete iba mesto Bratislava (BA) a chcete sumu predaja vyfiltrovaných, ale iba pre Lucku, tak použijete funkciu SUMPRODUCT v kombinácii s ďalšími, ako je uvedené v úvode článku,
• ak vyfiltrujete iba mesto Bratislava (BA) a chcete počet vyfiltrovaných, ale iba pre Lucku, tak použijete funkciu SUMPRODUCT v kombinácii s ďalšími, ako je uvedené v úvode článku.

Príklady k samotným funkciám SUM, COUNT, SUMIF, COUNTIF, SUBTOTAL a AGGREGATE uvádzať teraz nebudem, pretože článok je zameraný práve na posledné dva prípady.

Poznámka: Na konci článku nájdete aj názvy uvedených funkcií v nemeckej a českej verzii Excelu.

A teraz sa vrátime ku vzorcu pre kombináciu filtra a výpočtu v zmysle funkcie SUMIF alebo COUNTIF:

=SUMPRODUCT(SUBTOTAL(109;OFFSET(D6;ROW(D6:D20)-ROW(D6);0));–(B6:B20=C1))

Ak vám JE jedno, ako to funguje

Ak vám je jedno, ako to funguje, a potrebujete to „iba“ použiť vo svojich údajoch, tak systém je takýto:

• číslo 9 alebo 109 vo funkcii SUBTOTAL – je to číslo pre výpočet sumy. Ak chcete vypočítať sumu, tak tam dajte 9, ak počet, tak 3. Ak potrebujete ignorovať aj skryté riadky, tak suma = 109, počet = 103,
• adresa D6 vo funkcii OFFSET a vo funkcii ROW – prvá bunky v stĺpci, z ktorého chcete sumu,
• rozsah D6:D20 vo funkcii ROW – rozsah buniek v stĺpci, z ktorého chcete sumu,
• rozsah B6:B20 na konci vzorca – rozsah buniek, ktoré majú splniť nejaké kritérium. V našom prípade sú to všetky mená (pretože máme kritérium, že chceme iba Lucku),
• bunka C1 na konci vzorca – bunka s kritériom. V našom prípade bunka, kde je meno Lucka. Ale môže to tam byť aj napísané v úvodzovkách, t.j. „Lucka“.

Ak vám NIE JE jedno, ako to funguje

Vo videu uvidíte postup krok za krokom, ako celý výpočet nastavíte a ako jednotlivé funkcie fungujú.

Poznámka: Ak nejde video prehrať, môžete si ho pozrieť priamo na YouTube >>

Môžete si stiahnuť aj súbor s príkladom: tu >>:

Preklad ENG/SK – DE – CZ:

• AGGREGATE – AGGREGAT – AGGREGATE
• COUNT – ANZAHL – POČET
• COUNTIF – ZÄHLENWENN – COUNTIF
• OFFSET – BEREICH.VERSCHIEBEN – POSUN
• ROW – ZEILE – ŘÁDEK
• SUBTOTAL – TEILERGEBNIS – SUBTOTAL
• SUM – SUMME – SUMA
• SUMIF – SUMMEWENN – SUMIF
• SUMPRODUCT – SUMMENPRODUKT – SOUČIN.SKALÁRNÍ

Ďakujem za prečítanie článku. Verím, že návody budú pre vás prínosné a uľahčia vám prácu v Exceli.