Fórum pro uživatele kancelářského balíku OpenOffice | LibreOffice
 

#1 13. 3. 2018 09:42:12

Pbnka
Člen
Registrace: 13. 3. 2018
Příspěvků: 1

Kombinace funkcí Subtotal a Sumif (popř. Sumifs)

Ahoj, dobrý den všem,
marně se snažím najít odpověď/návod jak zkombinovat 2 fce - Subtotal a Sumif (popř. Sumifs). Potřebovala bych provést z určeného sloupce součet všech buněk, které splňují podmínku ">0". Potřebuji ale, aby se mi tento součet aktualizoval i po použití filtrů.
Mohl byste mi prosím někdo poradit jakou fci v tomto případě zvolit pokud nejde tyto dvě kombinovat?


Předem moc děkuji za každé info

Hezký den, Petra

Offline

#2 13. 3. 2018 11:02:01

neutr
Člen
Registrace: 8. 3. 2007
Příspěvků: 2,678

Re: Kombinace funkcí Subtotal a Sumif (popř. Sumifs)

Na tohle by měla stačit funkce SUNIFS. Subtotal vytvoří mezisoučty. Takže první podmínka by měla být jak uvádíte '>0'. Tou druhou by mělo být testování zda se jedná o matici součtu (subtotal).
     Ten druhý případ lze řešit různě, ale je to dané spíš strukturou kontingenční tabulky. Někdy bývají položky signované například datumem, nebo pořadovým číslem dokladu a podobně. Subtotal je mezisoučet který buď má odlišné označení v řádku, nebo často vůbec žádné. Na takovou odlišnost se postaví druhá podmínka.
     Jiným případem je klasický soubor čísel (má dáti - dal). Subtotal je vzorec, respektive matice a tu lze detekovat - tím ji vyloučit. Například funkce TYPE :

nápověda napsal(a)

TYPE
Vrátí typ hodnoty (1 = číslo, 2 = text, 4 = booleovská hodnota, 8 = vzorec, 16 = chybová hodnota, 64 = matice).

.
     Stačí testovat Type = 1, respektive zda není vzorcem (8) či maticí (64). Respektive například =ISFORMULA() která vrátí true pokud jde o vzorec. Asi by šlo sestrojit více podobných řešení.


     V takovém případě už nevadí jak se to přetřídí. Celkem důležité je absolutní adresování pomocí dolarů - označení testovaného úseku. Také by šlo řešit zadání pomocným sloupcem který může být skrytý. Zde pro každý řádek dotaz zda je to vzorec, nebo číslo menší nežli nula. Pokud není tak číslo zobrazit k součtu.


     Takže řešení je více druhů a mohlo by to být i součástí filtru. Pokud si s tím neporadíte dejte sem odkaz na vzorový soubor který uložíte nejlépe na portálu uložto.cz, nebo podobný. Důrazně upozorňuji nedávejte k dispozici ostrá data. Nahraďte je tak aby byl správně popsán problém.

Editoval neutr (13. 3. 2018 11:08:40)


Pokud je Váš problém vyřešen, označte prosím svůj příspěvek za "VYŘEŠENÝ"
Zlepšíte tak orientaci na fóru při vyhledávání řešení problémů
JAK OZNAČIT TÉMA ZA VYŘEŠENÉ

Offline

#3 14. 3. 2018 11:23:26

lp.
Člen
Registrace: 24. 9. 2009
Příspěvků: 790

Re: Kombinace funkcí Subtotal a Sumif (popř. Sumifs)

Doporučuji pomocný sloupec + SUMIFS

Pokud hodnoty jsou ve sloupci B
do c2 : =SUBTOTAL(3;B2) a nakopírovatdo dalších buněk
a vzorec pak vypadá takto:

=SUMIFS(B2:B6;B2:B6;">0";C2:C6;">0")


Pokud Vás to bude bavit, maticový vzorec bez pomocného vzorce vypadá takto:

=SUM(IF(SUBTOTAL(9;OFFSET($B$2;ROW($B$2:$B$6)-2;0;1))>0;SUBTOTAL(9;OFFSET($B$2;ROW($B$2:$B$6)-2;0;1));0))
Uložit ctrl-shift-enter

(Jen pro rozvoj osobnosti, použití nedoporučuji.)

Editoval lp. (14. 3. 2018 11:24:26)

Offline

#4 14. 3. 2018 14:46:18

kabi
Člen
Registrace: 1. 6. 2017
Příspěvků: 107

Re: Kombinace funkcí Subtotal a Sumif (popř. Sumifs)

doporučil bych spíše pomocný sloupec + subtotal na tento sloupec (lze tak řešit i průměr, počet hodnot aj. pro filtrované buňky). Ve vzorci do pomocného sloupce zadat  =if(B2>0;B2;0 nebo "") a rozkopírovat.
Druhá varianta je samozřejmě použít filtr i na sloupec B a vyfiltrovat hodnoty větší než nula, pak stačí subtotal na sloupec B (pokud B obsahuje součtovaná data).

Editoval kabi (14. 3. 2018 14:59:45)

Offline

Zápatí