EXCEL - poradit s výpočtem podmíněného součtu
Zdravím chytré hlavy a prosím o pomoc.
V excelovské tabulce (zjednodušené) je sloupec C (název zboží), ve sloupci E jeho hmotnost a ve sloupci O datum potvrzení.
Někde mimo bude uvedena celková hmotnost zboží, které je k dispozici. Potřeboval bych vysčítat hmotnosti jednotlivého zboží (ve sloupci E), ale jen těch, u kterého je potvrzené datum (sloupec O<>"") a tento součet odečíst od celkového množství - tedy výsledkem by měla být zbývající hmotnost (označené zeleně).
Jednotlivé kroky výpočtu bych tak nějak zvládnul, ale nedaří se mi to spojit do jednoho vzorce - jde-li to vůbec? Zkoušel jsem funkce SUMIF nebo SKALÁRNÍ.SOUČIN....
Ukázková tabulka (s vymyšlenými čísly) je v příloze. Děkuju za návrh řešení.
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ěď.
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:
Tento vzorec sa dá zapísať ako štandardný vzorec pomocou SUMPRODUCT takto:
Výsledný vzorec, ktorý funguje, vyzerá takto:
Tento vzorec sa dá zapísať ako poľový vzorec takto:
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:
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ý.