EXCEL - poradit s výpočtem podmíněného součtu

Netuším jestli sem správně pochopil Tvůj záměr, tak se na to podívej. Jsou použity maticové vzorce.

Děkuju za odpověď. Na maticové vzorce jsem při hledání řešení také narazil, ale nikdy jsem s nimi nepracoval, tak jsem se jim snažil vyhnout....

Koukal jsem na tvoje řešení, ale není to úplně ono. V části CELKEM mají zůstat ty ručně zadané hodnoty - celkové množství. A od tohoto čísla se má odečíst součet hodnot, které mají potvrzený termín a výsledek zobrazit v části ZBÝVÁ.

Ok co tohle?

To vypadá líp! Ale nefunguje výpočet pro zboží B, D a E. Asi bude chyba v kopírovaní vzorce, ne? Např. by tam mělo stále zůstávat O2:O31.

Sakra dělám víc věcí najednou a vytvářím kraviny. Takže nový soubor se správnými výsledky (zapoměl sem udělat absolutní odkazy pro sloupec O). Omluva

Edit: Koukám už sis toho všimnul, hold sem pomalej

Otestoval jsem to a vše funguje! Ještě že jsem ti radil, co? Moc děkuju, ještě zkusím počkat, jestli se někdo vytasí s návrhem jiného řešení nebo objeví případně chybu a pak označím jako vyřešené.

Ještě jednou moc děkuju!

Ještě že jsem ti radil, co?

Jo jo přesně tak !!

Chyba tam nie je a lepšie riešenie neexistuje.

To jsem potřeboval slyšet! Značím vyřešeno a oběma děkuji!

Nedalo mi to a zkusil jsem pro zajímavost vytvořit jiné řešení. Je tam navíc pomocný sloupec "P". Jaký je váš názor?

A ještě jedna podotázka - lze ve vzorci nějak ošetřit, aby se nezapočítávaly skryté řádky?

Předem děkuju za případnou odpověď.

lze ve vzorci nějak ošetřit, aby se nezapočítávaly skryté řádky?

Koukni na funkci SUBTOTAL

To je ono! Jenom se mi nedaří to naroubovat na výše uvedený maticový vzorec. Můžu ještě poprosit o radu, jak udělat, aby se skryté řádky nepočítaly?

Tak sem se o něco pokusil a vypadá to, že by to mohlo být funkční. Hodnoty, které se "mění" při skrytí řádku sem označil červeně.
Jen se mě prosím neptej proč je ten vzorec napsaný tak, jak je napsaný a jak to "funguje", protože NEMÁM PÁRU. Tohle řešení sem našel na netu a kupodivu se mi to povedlo napasovat do Tvé tabulky. Řádně vyzkoušej funkčnost.

Rád bych slyšel vyjádření zdejšího Excel Guru "LOSA" k použitým vzorcům, popřípadě nějaký jiný (lepší) návrh na řešení tvé situace.

Díky.

Ten vzoreček, to je teda síla! Ještě složitější než ty maticové vzorce. Také by mě zajímalo, zda to lze udělat nějak elegantněji, jednodušeji. Nemám rád složité věci. Uvidíme, jestli se los ozve...
Každopádně ti děkuju za tvůj čas a funkční řešení, i když jsem nečekal, že to bude tak komplikované.

Možno by elegantnejšie riešenie bola kontingenčná tabuľka (pivot table), ktorá ti umožňuje aj filtrovanie.

Len opíšem, prečo je napísaný ten vzorec tak, ako je napísaný:

Pôvodný poľový vzorec (array formula) vyzeral takto:

=E35-SUM(($C$2:$C$31=H35)*($E$2:$E$31)*($O$2:$O$31<>""))

Tento vzorec sa dá zapísať ako štandardný vzorec pomocou SUMPRODUCT takto:

=E35-SUMPRODUCT(($C$2:$C$31=H35)*($E$2:$E$31)*($O$2:$O$31<>""))

Výsledný vzorec, ktorý funguje, vyzerá takto:

=E35-SUMPRODUCT(SUBTOTAL(103;OFFSET($C$2;ROW($C$2:$E$31)-ROW($C$2);0))*($C$2:$C$31=H35)*($E$2:$E$31)*($O$2:$O$31<>""))

Tento vzorec sa dá zapísať ako poľový vzorec takto:

=E35-SUM(SUBTOTAL(103;OFFSET($C$2;ROW($C$2:$E$31)-ROW($C$2);0))*($C$2:$C$31=H35)*($E$2:$E$31)*($O$2:$O$31<>""))

A teraz vysvetlenie k tomu, čím sa líši pôvodný vzorec od nového, ktorý zohľadňuje skryté riadky:

Obsah funkcie SUMPRODUCT (resp. SUM) je potrebné prenásobiť poľom s indikátorom, či je riadok skrytý alebo nie (indikátor bude nadobúdať len hodnoty 0 a 1, prenásobenie je logické AND). Jediný možný spôsob, ako vo vzorci odlíšiť skrytý riadok, je pomocou funkcie SUBTOTAL (t.j. lepšie riešenie, ak to má byť len pomocou vzorcov, neexistuje).

Pole s indikátorom obsahuje v sebe takýto vzorec:

OFFSET($C$2;ROW($C$2:$E$31)-ROW($C$2);0)

Na prvý pohľad to síce vyzerá, že sa tento zápis dá zjednodušiť na $C$2 (pretože ROW($C$2:$E$31)-ROW($C$2) je nula a OFFSET($C$2;0;0) je $C$2). Treba si ale uvedomiť, že výsledkom je pole, kde prvým prvkom je OFFSET($C$2;0;0), druhým prvkom je OFFSET($C$2;1;0), tretím OFFSET($C$2;2;0), atď. Vo výsledku je to teda pole s bunkami $C$2, $C$3, $C$4, atď. Toto pole sa vďaka funkcii SUBTOTAL stane poľom, ktoré nadobúda pre každý riadok hodnotu 0 alebo 1 podľa toho, či je riadok skrytý alebo zobrazený.