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

#1 31. 1. 2017 12:30:41

hdplot
Člen
Registrace: 18. 2. 2015
Příspěvků: 79

Seskupení hodnot podle datumu - VYŘEŠENÝ

Tento dotaz souvisí (navazuje) na problém viz. Zobrazení dat z BASE v CALCu . Načtená data z databáze se správně zobrazují v grafech, ale protože je jich hrozně moc (blížím se k 1000) a jsou docela nahodilá, tak z nich nelze v takto zobrazeném grafu vysledovat žádnou informační hodnotu - nějaký trend vývoje. Jedná se o tabulku typu "Datum" "Množství" (prodaných kusů). Grafy by bylo dobré zobrazovat nikoliv ze surových dat (hodnoty den po dni, kdy rozptyl může být od nyly do dvaceti - tedy tisíc různě vysokých sloupečků. které se náhodně opakují), ale z dat, která budou sumarizována např. podle měsíců - tedy výsledný graf by měl dvanáct sloupců, které by už vypovídací hodnotu měly.

Dotaz tedy zní, jak udělat ze zdrojové tabulky "Datum" "Množství" novou tabulku typu "Měsíc" "Celkové množství za měsíc", když počet záznamů pro jednotlivé měsíce není předem známý. Jde to nějak pomocí standardních možností Calcu, nebo se to opět musí řešit makrem.

Editoval hdplot (1. 2. 2017 12:48:26)

Offline

#2 31. 1. 2017 14:19:57

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

Re: Seskupení hodnot podle datumu - VYŘEŠENÝ

Doporučil bych pomocný sloupec který bude obsahovat identifikaci =Month(buňkaSdatumem). Může být kdekoliv a může být i přes tisíce řádků. Tato funkce vrátí číslo měsíce. Na ni uděláte součet vzorcem =SUMIF(Rozsah buněk s číslem měsíce;podmínka = 1; rozsah buněk pro součet). Například :


Datumy máte na rozsahu C1:C10526, částky k součtu v rozsahu D1:D10526 a pomocný sloupec s číslem datumu E1:E20000.
Potom LEDEN =

=SUMIF(E1:E10526;1;D1:D10526)

Potom ÚNOR =

=SUMIF(E1:E10526;2;D1:D10526)

Potom BŘEZEN =

=SUMIF(E1:E10526;3;D1:D10526)

Potom DUBEN =

=SUMIF(E1:E10526;4;D1:D10526)

Potom KVĚTEN =

=SUMIF(E1:E10526;5;D1:D10526)

Potom ČERVEN =

=SUMIF(E1:E10526;6;D1:D10526)

Potom ČERVENEC =

=SUMIF(E1:E10526;7;D1:D10526)

Potom SRPEN =

=SUMIF(E1:E10526;8;D1:D10526)

Potom ZÁŘÍ =

=SUMIF(E1:E10526;9;D1:D10526)

Potom ŘÍJEN =

=SUMIF(E1:E10526;10;D1:D10526)

Potom LISTOPAD =

=SUMIF(E1:E10526;11;D1:D10526)

Potom PROSINEC =

=SUMIF(E1:E10526;12;D1:D10526)

     Takže nyní jste v situaci kdy by DBQ měla být i s pomocným sloupcem skrytá a vidět by měla být jen tabulka s názvy měsíců a souhrny. Tedy já to píšu z hlavy ale snad jsem se neseknul ve vzorci.


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 31. 1. 2017 14:33:54

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

Re: Seskupení hodnot podle datumu - VYŘEŠENÝ

Ještě byste možné potřeboval vylepšit legendu tak aby obsahovala částku v Kč. Na to existuje trik - je to tady někde v historii a je zřejmě více návodů. Ale já užívám součet částky s názvem - pak se objeví v legendě i částka - př Leden 10256 Kč.
     Jestli chcete vzor tak napište sloupec kde je datum a kde částka. Také nějaký volný sloupec vedle tak aby do něj nesahala načítané databáze.


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

#4 31. 1. 2017 15:15:06

hdplot
Člen
Registrace: 18. 2. 2015
Příspěvků: 79

Re: Seskupení hodnot podle datumu - VYŘEŠENÝ

No toto by mohlo být jako třešnička na dortu také (Datum je ve sloupci A, částka ve sloupci B)


SUMIF jsem vyzkoušel a v principu to funguje jak jsem chtěl. Ale spíš ještě je problém, že hodnoty nelze rozlišovat jenom podle měsíce, ale i podle roku (graf tedy nebude mít 12 sloupců, ale 24 nebo podobně, případně budou 2 nebo 3 grafy podle roků) V každém případě rozhodující pro součet není jenom měsíc,ale i rok. Ale =Month(buňkaSdatumem) dává číslo a =Year(buňkaSdatumem) dává taky číslo - nelze je tedy sečíst, (2 + 2016 je totéž jako 1+2017) ale měly by se spojit jako řetězec, aby to bylo jednoznačné (1 2017).


(Druhá varianta by byla některé z čísel vynásobit nebo vydělit (třeba 10000) a už to bude vycházet jednoznačně dobře, ale pokud by v kontrolní buňce byl řetězec "1 2017" bude to přehlednější i na první pohled)

Offline

#5 31. 1. 2017 16:00:16

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

Re: Seskupení hodnot podle datumu - VYŘEŠENÝ

Nene - otestujte funkci SUMIFS. Ta je základem stejná ale umí ještě další podmínky, takže doporučuji další pomocný sloupec kde bude vzorec =YEAR(buňka s datumem). Bylo by to možné udělat i jinak ale tohle je celkem přehledné a stejně se to bude skrývat.
     Vzorec je takto pokud bude ve sloupci C =MONTH a ve sloupci D =YEAR
Leden 2015 =SUMIFS(B1:B2000;C1:C2000;1;D1:D2000;2015)
Leden 2016 =SUMIFS(B1:B2000;C1:C2000;1;D1:D2000;2016)

Editoval neutr (31. 1. 2017 16:01:23)


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

#6 1. 2. 2017 08:17:07

hdplot
Člen
Registrace: 18. 2. 2015
Příspěvků: 79

Re: Seskupení hodnot podle datumu - VYŘEŠENÝ

Ok funguje to jak má, takže bych to považoval za vyřešené. Ale když už jste nakousl možnost

neutr napsal(a)

vylepšit legendu tak aby obsahovala částku v Kč.

, tak Vás poprosím (ze studijních důvodů) ještě o tuto funkci wink

Offline

#7 1. 2. 2017 09:56:38

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

Re: Seskupení hodnot podle datumu - VYŘEŠENÝ

Dejme tomu že v buňkách F1:F12 máte názvy měsícú a v buňkách G1:G12 máte vzorec s výsledkem.
1. - Do buňky H1 vložte vzorec =F1 & " " & G1 & " Kč".
2. - Vzorec vykopírujte do úseku H1:H12
3. - Pokud to uděláte takto tak máte celý úsek H1:H12 vybraný (vymodřený) - vystříhněte ho.
4. - Dejte kurzor do buňky F1 a vložte jinak (dialog) - jen čísla a text - je hotovo :-)
     Podobně by to mohlo být například takto :
=F1 & " tržby " & G1 & " Kč".
=F1 & " výsledek " & G1 & " Kč".
Nebo přiřadit také rok. celkem doporučuji prostudovat nápovědu k legendám. Částky se dají zadávat například přímo do grafu a lze dělat různé vychytávky ale ne vždy je to snadné a optimální řešení. Při změně hodnot to může být neestetické, kontraproduktivní ap. zejéména když se to aktualizuje automaticky.
Přeji úspěch


PS :
     Já zapoměl, že se to musí aktualizovat - tedy pak bych doporučil úpravu vzorcem takto :
Místo názvu měsíce do sloupce F vzorec -
pro leden ="Leden " & G1  & " Kč".
pro únor ="Únor " & G2  & " Kč".
......
     Pro některé případy grafů možná opačně :
pro leden =G1  & " Kč"  & " za " &  "Leden".
pro únor  =G2  & " Kč"  & " za " &  "Únor".

Editoval neutr (1. 2. 2017 10:56:05)


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

#8 1. 2. 2017 12:50:05

hdplot
Člen
Registrace: 18. 2. 2015
Příspěvků: 79

Re: Seskupení hodnot podle datumu - VYŘEŠENÝ

Na cvičném souboru to funguje, zbývá to překlopit do ostrých dat - děkuji za pomoc

Offline

#9 2. 2. 2017 00:57:09

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

Re: Seskupení hodnot podle datumu - VYŘEŠENÝ

Na základě popisu se toho moc poradit nedá.

1 ad. "trend". Zmiňujete, že se jedná o prodeje. Pokud chcete sledovat trend na měsíčních hodnotách, tak jeden rok je málo. U tohoto typu dat lze předpokládat sezóní změny, výsledek ovlivňuje obvykle počet víkendů/pracovních dnů, ... Pokud měsíce, tak aspoň za 3 roky. Samostatné grafy podle roků se hodí spíše ke sledování sezónnosti. Obecně nedoporučují používat vestavěné prokládání trendů v grafech. Je lepší určit trend v listu a trendovou složku do grafu přidat samostatně.

Část vlivů se dá eliminovat buď týdenními hodnotami, nebo rozdělením roku na 13 měsíců po 4 týdnech (obvykle to tak vychází)  Rok začíná  v 1. (iso)týdnu a končí posledním. Většina roků má 52 (iso)týdnů občas, třeba v roce 2016 bylo týdnů 53, pak jsou tyto 4týdení cykly většinou stejně velké a liší je jen počtem svátků. Přebytečný týden pokud se vyskytne, dávám do posledního období.

2) Denní hodnoty - ani to není zásadní problém - stačí vhodně zvolit filtr, jednoduchý je klouzavý průměr, u denních hodnot doporučuji délku rovnu nějakému malému násobku 7 aby se vyrovnaly výkyvy v rámci týdne.

3) Agregace hodnot - standardní variantou je použití kontingenční tabulky. V ní agregaci vytvoříte několika kliknutími myši, může zpracovávat i data z base. Pokud načítáte data z base, mělo by jít také do listu načíst přímo agregované hodnoty.

4) Vyrobit z datumu klíč typu 1 2016 - na to můžete použít funkci TEXT: =TEXT(datum; "m yyyy"), tady se to moc nehodí, jen pro informaci.

Offline

#10 2. 2. 2017 07:43:56

hdplot
Člen
Registrace: 18. 2. 2015
Příspěvků: 79

Re: Seskupení hodnot podle datumu - VYŘEŠENÝ

Samozřejmě že rok je málo, jednalo se mi ale v principu o to, jak vyřešit problém s Calcem a ne "matematický problém trendu". Ve finále jsou data za několik roků a už není věcí tohoto vlákna, jestli sleduji průběh postupně za jednotlivé roky, nebo porovnávám jednotlivé roky (nebo měsíce) mezi sebou a podobně. Prostě mi šlo o to, jak vyfiltrovat potřebná data - proto považuji problém za VYŘEŠENÝ - vlastní reprezentace v grafech už je věc další.

Kromě toho se jedná o možná trochu "laický" pohled pro orientaci, takže bych do toho určitě netahal 13 měsíců a podobné exaktní metody - stačí mi výsledek "v lednu se prodalo X a v únoru Y množství" Je jasné, že když je únor kratší,tak se zřejmě prodá o něco méně.

Offline

Zápatí