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

#1 2. 3. 2010 18:41:09

dech
Člen
Registrace: 1. 10. 2008
Příspěvků: 150

vzorec na součet čísel v sloupci podle kritéria

Ahojte,
potřeboval bych poradit s vytvořením vzorce. Nejlepší je se podívat na tento obrázek a snad by mělo být jasné co ten vzorec má počítat.
20100302180852.png

asi mě navedete na DSUM...což ovšem představuje nutnost vytvořit si záhlaví k sloupcům tabulky. To by nebyl problém, ale je třeba i vytvořit záhlaví pro bunku ve které bude uvedeno vyhledávací kritérium a to je problém - vyhledávací kritérium je totiž zároven bunka v databázovém poli(C1:D7)...napr pro vzorec "A" by to byla bunka C1,pro vzorec "B" by to byla bunka C5. A vkládat záhlaví do databázového pole je katastrofa - jak pro přehlednost,tak i způsob vkládání. Takových položek jako tu mám "auto" a "kolo" mám stovky. Tedy: vyhledávací kritérium potřebuju mít jako součást databázového pole, ne mimo něj. Tvar a pořadí řádků chci mít zachováno(jde o řádky v časovém sledu)

Takže tak. Diky za napady.

Editoval dech (2. 3. 2010 18:47:12)


LO 6.1.2.1. (x64)  WIn 8 64bit  16GB RAM

Offline

#2 2. 3. 2010 19:57:26

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

Re: vzorec na součet čísel v sloupci podle kritéria

sumif

Offline

#3 3. 3. 2010 06:49:10

j-pastierik
Člen
Registrace: 15. 11. 2004
Příspěvků: 761

Re: vzorec na součet čísel v sloupci podle kritéria

sumif(c1:c7;"kolo";d1:d7)

Offline

#4 3. 3. 2010 09:41:23

dech
Člen
Registrace: 1. 10. 2008
Příspěvků: 150

Re: vzorec na součet čísel v sloupci podle kritéria

supr! diky!
...ted jeste premyslim,jestli by do toho šlo zadat jeste jedno vyhledavaci kriterium: kdyby byly tři sloupečky- dopravní prostředek,barva dopr. pr., počet.  A ten vzorec SUMIF by počítal např. jenom auta bílá. Vím že se to řešení blíží stále víc vzorci DSUM, ale pořád je zde výhoda toho, že nejsou záhlaví pro vyhledávací kritérium. Zkoušel jsem to(druhé kritérium) do toho vzorce nějak napasovat s čárkama a prostředníkama,ale vyhodilo to vždy nějakou chybu.


LO 6.1.2.1. (x64)  WIn 8 64bit  16GB RAM

Offline

#5 3. 3. 2010 10:46:38

Ondra.kl
Člen
Registrace: 30. 9. 2008
Příspěvků: 933

Re: vzorec na součet čísel v sloupci podle kritéria

... a nechcete na to radši použít kontingenční tabulku?

Offline

#6 3. 3. 2010 11:19:48

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

Re: vzorec na součet čísel v sloupci podle kritéria

dech napsal(a)

...ted jeste premyslim,jestli by do toho šlo zadat jeste jedno vyhledavaci kriterium: kdyby byly tři sloupečky- dopravní prostředek,barva dopr. pr., počet.

Viz SUMPRODUCT

(http://forum.openoffice.cz/viewtopic.php?id=919)

Offline

#7 3. 3. 2010 13:19:06

dech
Člen
Registrace: 1. 10. 2008
Příspěvků: 150

Re: vzorec na součet čísel v sloupci podle kritéria

diky moc!


LO 6.1.2.1. (x64)  WIn 8 64bit  16GB RAM

Offline

#8 3. 3. 2010 17:32:33

dech
Člen
Registrace: 1. 10. 2008
Příspěvků: 150

Re: vzorec na součet čísel v sloupci podle kritéria

A...tak sem se radoval a děkoval předčasně (s tím SUMPRODUCT). Už nad tím sedím fakt půl hodiny a nemůžu přijít na to jak ten vzorec zadat. :-(
proto sem dávám obrázek jak jsem to myslel s těma dvěma podmínkama.

20100303172813.png

diky


LO 6.1.2.1. (x64)  WIn 8 64bit  16GB RAM

Offline

#9 3. 3. 2010 22:41:00

hanus
Člen
Místo Olomouc
Registrace: 29. 11. 2006
Příspěvků: 573
Web

Re: vzorec na součet čísel v sloupci podle kritéria

lp. uvedl odkaz na topic, kde je ukázkový vzorec. Pro vás tedy analogicky:

=SUMPRODUCT(B6:B12="kolo";C6:C12="modrá";D6:D12)
=SUMPRODUCT(B6:B12="auto";C6:C12="bílá";D6:D12)

Offline

#10 4. 3. 2010 08:09:55

dech
Člen
Registrace: 1. 10. 2008
Příspěvků: 150

Re: vzorec na součet čísel v sloupci podle kritéria

jj,ja jsem na ten odkaz koukal, ale ani tak mi to nedocvaklo,sem na matiku lempl...
diky diky!!

mohu ještě poslední?   roll
pokud zadám jako vyhledávací kritérium místo mé "modrá" datum:
=SUMPRODUCT(B6:B12="kolo";C6:C12="22.2.2002";D6:D12)   tak ten vzorec nefunguje - neobjeví se žádná chyba,ale výsledek je 0. Zkoušel jsem to i bez uvozovek. V oblasti, kde se vyhledává to datum (C6:C12)jsem vymazal formátování abych měl jistotu,že ty bunky jsou brány jako datum. Pokud ovšem do vzorce zadám jako vyhl. kr. odkaz na bunku ve které je to kritérium, tak to funguje:
=SUMPRODUCT(B6:B12="kolo";C6:C12=A1;D6:D12)    a v A1 je: 22.2.2002

Editoval dech (4. 3. 2010 10:19:16)


LO 6.1.2.1. (x64)  WIn 8 64bit  16GB RAM

Offline

#11 4. 3. 2010 11:26:13

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

Re: vzorec na součet čísel v sloupci podle kritéria

dech napsal(a)

pokud zadám jako vyhledávací kritérium místo mé "modrá" datum:
=SUMPRODUCT(B6:B12="kolo";C6:C12="22.2.2002";D6:D12)   tak ten vzorec nefunguje - neobjeví se žádná chyba,ale výsledek je 0. Zkoušel jsem to i bez uvozovek. V oblasti, kde se vyhledává to datum (C6:C12)jsem vymazal formátování abych měl jistotu,že ty bunky jsou brány jako datum. Pokud ovšem do vzorce zadám jako vyhl. kr. odkaz na bunku ve které je to kritérium, tak to funguje:
=SUMPRODUCT(B6:B12="kolo";C6:C12=A1;D6:D12)    a v A1 je: 22.2.2002

To je dotaz? Pokud chcete datum použít ve vzorci, tak použijte VALUE:

=SUMPRODUCT(B6:B12="kolo";C6:C12=value("22.2.2002");D6:D12)

Jinak žádná magie v tom není. sumproduct nejprve pronásobí všechny prvky na stejné pozici v zadaných maticích a výsledky potom sečte.

Výraz TRUE se konvertuje na hodnotu 1 a FALSE na hodnotu 0.
Logickou operaci AND lze nahradit násobením. JAk je známo, libovolnou logickou operaci lze vyjádřit pomocí NOT a AND. Operaci NOT obvykle ani moc nepotřebujeme, lze ji obejít vhodnou volbou podmínky (=|<>; ...)

Pokud jsou splněny všechny podmínky je výsledná hodnota součinu podmínek 1 a ta se násobí s hledanou hodnotou, pokud ne, je hledaná hodnota vynásobena 0. Výsledný součet je pak roven součtu hledaných hodnot.

Pokud se mi nechce moc přemýšlet tak operaci OR nahradím součtem:

=SUMPRODUCT((B6:B12="kolo") + (B6:B12="auto"); C6:C12<=VALUE("22.2.2002"));D6:D12)

Podobně lze lze seskupit i násobení:

=SUMPRODUCT((B6:B12="kolo") * (C6:C12=value("22.2.2002"));D6:D12)

Offline

#12 4. 3. 2010 12:28:21

dech
Člen
Registrace: 1. 10. 2008
Příspěvků: 150

Re: vzorec na součet čísel v sloupci podle kritéria

diky! už to funguje i s datumem - díky VALUE...
No jak říkám, sem na matiku lempl,...jako já bych tomu chtěl rozuměnt,akorát to jaksi hned nevidím když se na TO kouknu.

Jo už vím přoč to s tím datumem nešlo...protože např. datum 22.2.2002 Calc vidí jako číslo 37309 ...takže kdybych tam toto číslo zadal do =SUMPRODUCT(B6:B12="kolo";C6:C12=37309;D6:D12) a přesto v oblasti C6:C12 byly datumy ve formátu např. 22/2/10  tak by to fungovalo...


LO 6.1.2.1. (x64)  WIn 8 64bit  16GB RAM

Offline

Zápatí