Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

hape · 29. 4. 2024 09:56:47

Zdravím ve spolek, a prosím o pomoc.
Mám list se sloupci A - AB. Ve sloupci B mám na řádcích označení co údaje na tom kterém řádku znamenají, od řádku 37 je jeden řádek vždy označen MAX a další SUM a takhle se střídají až do řádku 162. Sloupce C, E, G, atd. mám označené na řádku 36 jako Hodnota leden, Hodnota únor, Hodnota březen atd. a pod tím od řádku 37 se střídají hodnoty MAX a SUM. Jde nějak nastavit, aby se mi třeba na posledním řádku v každém sloupci C, E, G atd. sečetly hodnoty z řádků SUM?
Doufám, že jsem to o co mi jde vysvětlil srozumitelně.

Editoval hape (8. 5. 2024 08:30:02)

neutr · 29. 4. 2024 10:37:03

Toto lze zvládnout funkcí SUMIF() a složitější zadání také SUMIFS().

lp. · 29. 4. 2024 13:51:36

Popis je perla...

Mrkněte na funkci subtotal.

hape · 30. 4. 2024 16:51:29

Zdravím a děkuji za nasměrování a omlouvám se za ten popis . Holt nejsem žádný spisovatel.
Našel jsem zde na webu článek "Funkce pro inteligentní součty (zejména SUBTOTAL, SUMIF)" ve kterém jsou příklady použití vámi zmiňovaných funkcí. Pro mé použití je ideální příklad funkce SUMIF v kombinaci s funkcí COUNTIF, kdy je výsledkem průměr ze sečtených hodnot. To je pro mně ideální výsledek, který jsem potřeboval.
Bohužel mi ale funkce SUMIF dává špatný součet řádků "SUM" a nejsem si jistý čím to může být. Dává podstatně nižší součet, než když ty řádky sečtu ručně. Nějakou dobu jsem se snažil přijít na to, kde dělám chybu a na nic jsem nepřišel. Až později, když jsem řádky "SUM" sčítal ručně pro porovnání výsledků, jsem si uvědomil, že je požitý jako oddělovač desetinných míst tečka místo u nás obvyklé čárky a blesklo mi hlavou, jestli to nemůže být ten problém.
Může funkci SUMIF vadit tečka jako oddělovač desetinných míst a takové hodnoty vynechat ze součtu? Mně nic jiného nenapadá proč by jinak dávala výrazně nižší součet, oproti ručnímu sečtení.
Jestli tomu tak opravdu je, dá se s tím něco dělat? Já nejsem autorem těch dat, tak jejich podobu neovlivním. Soubor s daty je stažený ze stránek ČHMÚ a ti asi chtějí být světoví, tak používají tečku na oddělení desetinných míst místo čárky.

Editoval hape (30. 4. 2024 16:52:04)

neutr · 30. 4. 2024 17:51:28

Calc čte číslo s tečkou i s čárkou, ale čárka může být také ukázkou textového zadání čísla (formát buňky @). To se musí dobře ošetřit zejména když to taháte z externího zdroje. Raději otestujte pomocný sloupec do kterého načtete stažená data : do pomocného sloupce dejte =Value(odkaz na buňku).

hape · 1. 5. 2024 08:02:04

Dosud jsem Calc nepoužíval aktivně, maximálně jsem ho občas použil pro otevření cizího souboru, takže úplně nevím jestli jsem správně pochopil co mám udělat. Postupoval jsem tedy takto:
V prvním sloupci ve kterém chci provést součet některých řádků jsem provedl označení a zkopírování do schránky všech řádků. Ve stejném listu jsem do prázdného sloupce AD od řádku 1 vložil hodnoty ze schránky a ve vedlejším prázdném sloupci jsem do první buňky napsal =VALUE(AD1) a výsledkem je CHyba:502. To asi nemá být výsledkem, nebo ano?
Jinak, kdyby jste byl někdo ochotný se na ta data podívat, tak ten soubor je ke stažení na webu ČHMÚ. Na stránce Měsíční a roční data dle zákona 123/1998 Sb. je odkaz "Úhrn srážek", na další stránce odkaz "Kraj Vysočina" a tam v seznamu lokalit mi jde konkrétně o Třešť, okres Jihlava.

neutr · 1. 5. 2024 09:36:21

Tak jsem se na to mrknul a funguje to.
Jen pár poznámek:
1. Stažený soubor CSV otevřeme filtrem pro Windows 1250 Východní Evropa latinka 2
2. Do úseku AD37:AD162 jsem vložil vzorec =VALUE(AA37) a zkopíroval do celého úseku AD37:AD162
3. Do buňky AF37 jsem zadal vzorec =SUMIF(B37:B162,"SUM",AD37:AD162)
4. Objevil se mi výsledek 38822
5. Následně jsem kontroloval pomocí dalšího pomocného sloupce AG37:AG162 vzorcem =IF(B37="SUM",AD37,0)
6. Tento sloupec jsem sečetl klasicky se stejným výsledkem
Když zadáváte vzorec v prvním řádku pomocného sloupce tak jej ihned zkopírujte a a stiskněte Shift+Ctrl+End. Vybere se vám celý sloupec a dejte vložit. Je to rychlé a snadné

hape · 1. 5. 2024 13:08:07

Se mnou to asi bude ještě těžké.
Bod 1. mám správně, bez toho jsou některé znaky špatně zobrazené.
Bod 2. nejsem schopný splnit i když se o to vážně snažím.
Trochu začínám uvažovat o tom, že to všechno spočítám ručně.
Ale ještě tomu dám šanci, jestli se mnou budete mít trpělivost a postupně mi budete radit jak na to.
Takže v bodě 2. mi není jasné do které buňky ve sloupci AD jste vložil vzorec =VALUE(AA37) a jak ho potom zkopíroval do celého úseku AD37:AD162. Výběr kl. zkratkou Shift+Ctrl+End u mně funguje až když je v buňce výsledek, do té doby buď dělá něco jiného (fialovou čáru okolo více sloupců), nebo nic. Výsledek vzorce =VALUE(AA37) je u mně Chyba:502. A i když mám vzorec uložený ve schránce a tou zkratkou vybraný sloupec (buňky modře vybarvené), tak klepnutí pravým tl. myši v prostoru těch buněk a volba vložit nic neudělá. Vzorec =SUMIF(B37:B162,"SUM",AD37:AD162) v buňce AF37 mi potom vypisuje Chyba:501.
A pro upřesnění, výsledek z bodu 4. nepotřebuji. Mně jde o sečtení řádků "SUM" po jednotlivých měsících abych si mohl vypočítat průměrnou hodnotu srážek pro každý měsíc zvlášť. Ručně jsem sečetl řádky "SUM" ze sloupce C (Hodnota leden) a vyšlo mi 2 611,0719 mm (nejsem si jistý jestli jsem neudělal chybu), řádků je 63, což dává průměr 41,44 mm srážek pro leden. Takhle bych to potřeboval 12x, pro všechny měsíce v roce a myslel jsem, že bude snadné udělat to nějak automatizovaně, ale už si to tedy nemyslím.

Editoval hape (1. 5. 2024 14:26:00)

neutr · 1. 5. 2024 15:27:24

hape napsal(a)

Se mnou to asi bude ještě těžké.
Bod 1. mám správně, bez toho jsou některé znaky špatně zobrazené.
Bod 2. nejsem schopný splnit i když se o to vážně snažím.
Trochu začínám uvažovat o tom, že to všechno spočítám ručně.
Ale ještě tomu dám šanci, jestli se mnou budete mít trpělivost a postupně mi budete radit jak na to.
Takže v bodě 2. mi není jasné do které buňky ve sloupci AD jste vložil vzorec =VALUE(AA37) a jak ho potom zkopíroval do celého úseku AD37:AD162. Ten vzorec vkládám ve stejném řádku tedy AD37. Já když napíšu vzorec tak se přesvědčím jestli funguje takže uděláte Entr a pak se vrátíte a kopírujete následně do AD37:AD162.

Výběr kl. zkratkou Shift+Ctrl+End u mně funguje až když je v buňce výsledek, do té doby buď dělá něco jiného
Jak jsem uvedl vložím vzorec a vyskočím z něj - zkušenost - nechci kopírovat případnou chybu.

Výsledek vzorce =VALUE(AA37) je u mně Chyba:502. Platí totéž - pomocný sloupec musíte otestovat prvním vzorcem a když funguje kopírujete.

A i když mám vzorec uložený ve schránce a tou zkratkou vybraný sloupec (buňky modře vybarvené), tak klepnutí pravým tl. myši v prostoru těch buněk a volba vložit nic neudělá. Vzorec =SUMIF(B37:B162,"SUM",AD37:AD162) v buňce AF37 mi potom vypisuje Chyba:501.
Vzorec =SUMIF(B37:B162,"SUM",AD37:AD162) se do sloupce nekopíruje - je v jediné buňce AF37. Načítá jen řádky kde je ve slouci B "SUM" ale čte číslo ve sloupci AD37:AD162 kde je ten vzorec =VALUE(AA37). Oba úseky by měly být stejné, ale vím že mi to fungovalo také jen ve verzi =SUMIF(B37:B162,"SUM",AD37)

A pro upřesnění, výsledek z bodu 4. nepotřebuji. Mně jde o sečtení řádků "SUM" po jednotlivých měsících abych si mohl vypočítat průměrnou hodnotu srážek pro každý měsíc zvlášť. Ručně jsem sečetl řádky "SUM" ze sloupce C (Hodnota leden) a vyšlo mi 2 611,0719 mm (nejsem si jistý jestli jsem neudělal chybu), řádků je 63, což dává průměr 41,44 mm srážek pro leden. Takhle bych to potřeboval 12x, pro všechny měsíce v roce a myslel jsem, že bude snadné udělat to nějak automatizovaně, ale už si to tedy nemyslím.
Rozumím - potom si musíte udělat pomocný sloupec pro každý měsíc jednotlivě a na tyto sloupce postavit vzorec SUMIF() - nejlépe nad sloupec pomocných hodnot =Value(buňka).

Není to složité, ale spíš jen málo přehledné. Jde udělat buď kontingenční tabulku a výsledek exportovat do přehledné tabulky, nebo načíst makrem, které je rychlejší, ale muselo by se udělat pro každý sloupec extra. Výsledek se dá například do komentáře ze kterého se dá výsledek zkopírovat a nakonec komentář smazat. Kontingenční tabulka je k tomu ideální, ale to chce tento nástroj prozkoumat a naučit se.

Pokud chcete udělám vzor sešitu se vzorci, nebo kontingeční tabulkou, a nebo i makro.

PS Vidím že uvádíte email - pošlu Vám sešit se vzorci.

Editoval neutr (1. 5. 2024 15:28:48)

neutr · 1. 5. 2024 15:52:50

Raději jsem to uložil na svém Googl disku. S uložto jsou problémy. Stahujte zde Řešení vzorci

Jsou tam skryté první řádky a vlastní řešení je na konci.

Editoval neutr (1. 5. 2024 15:55:26)

LADER · 1. 5. 2024 20:43:02

Zdravím, na uvedené adrese ČHMÚ jsou i denní data. Přestože jich je více, byly by výhodnější a jednodušší pro zpracování protože jsou v jediném sloupci.

hape · 3. 5. 2024 10:04:58

Děkuji neutrovi že si s tím dal tu práci a udělal co jsem potřeboval za mě.
Po povolení přístupu ke sdílenému souboru na Google disku se mi soubor otevřel rovnou v nějakém nástroji od Googlu a jsou v něm vidět ty pomocné sloupce se všemi výsledky. Jestli to dobře chápu, tak celkové součty jsou nad pomocnými sloupci, tedy v buňkách AD1...AO1. Je to tak?
Ještě jednou děkuji za odvedenou práci, mně by se to asi nikdy v Calcu nepodařilo. Zjistil jsem, že ho mám nějaký rozbitý a nedělá to co dělá vám. Po stažení souboru ke mě do PC a otevření v Calcu jsou výsledky v pomocných sloupcích samé Chyby:502 a sem tam v buňkách kde vychází celé číslo bez desetinných míst je správný výsledek. S takovými výsledky bych se mohl snažit až do aleluja a ničeho bych se nedobral.
Udělal jsem snímek obrazovky, jak to v mém Calcu vypadá a uložil ho do jednoho úložiště obrázků. Dávám sem odkaz, tak to snad bude nějakou dobu ke shlédnutí.

neutr · 3. 5. 2024 15:29:51

hape napsal(a)

Děkuji neutrovi že si s tím dal tu práci a udělal co jsem potřeboval za mě.
Po povolení přístupu ke sdílenému souboru na Google disku se mi soubor otevřel rovnou v nějakém nástroji od Googlu a jsou v něm vidět ty pomocné sloupce se všemi výsledky. Jestli to dobře chápu, tak celkové součty jsou nad pomocnými sloupci, tedy v buňkách AD1...AO1. Je to tak? Ano máte pravdu, jenom vzorce začínají pod nadpisem měsíce na řádku 37 a součty jsou nad nadpisem měsíce v řádku 35. Řádky 1 až 34 jsou skryté a sloupce mezi výsledky také - tedy například AE, AG a tak dál

Ještě jednou děkuji za odvedenou práci, mně by se to asi nikdy v Calcu nepodařilo. Zjistil jsem, že ho mám nějaký rozbitý a nedělá to co dělá vám. Po stažení souboru ke mě do PC a otevření v Calcu jsou výsledky v pomocných sloupcích samé Chyby:502 a sem tam v buňkách kde vychází celé číslo bez desetinných míst je správný výsledek. S takovými výsledky bych se mohl snažit až do aleluja a ničeho bych se nedobral.Pokuste se přepsat ty pomocné sloupce. Pod nadpisem je jeden typ vzorce - konkrétně =Value(odkaz na buňku). Po přepsání by měly vzorce nad nadpisem vydat správný výsledek. V jinpm případě přepište :
pro sloupec AD (buňka AD35) na =SUMIF($B$37:$B$162,"SUM",AD37:AD162)
pro sloupec AF (buňka AF35) na =SUMIF($B$37:$B$162,"SUM",AF37:AF162)
a tak dál...
Vzorce v buňkách lze kopírovat. Tam kde se čte ze sloupce B jsou "dolary" ty zůstávají ale ostatní se vztahuje k aktuálnímu sloupci. Nevím co se pokazilo, ale vzorce ztratily relativní adresy. Možná že stačí přepočítat z hlavního menu. Faktem je že zadejte do buňky AD37 vzorec =VALUE(C37). Ten zkopírujte do celého sloupce a potom celý sloupec AD37:AD162 zkopírujte do dalšího sloupce AF a pokračujte až k poslednímu sloupci AZ.

Editoval neutr (3. 5. 2024 15:32:43)

hape · 3. 5. 2024 17:30:34

V pomocných sloupcích vzorce VALUE i SUMIF mám a podle mého ani nejsou nijak změněné, nebo poškozené. Po klepnutí na buňky jsou vzorce v nich obsažené vidět ve vstupní řádce. A když třeba v buňce AD37 odstraním vzorec =VALUE(C37) a znovu ho tam napíšu, vydá opět výsledek Chyba:502. V hlavním menu jsem možnost přepočítat nenašel.
Nepomohl ani soubor, který jste mi poslal e-mailem. Po jeho otevření jsou v pomocných sloupcích stejné chyby 502. Vypadá to, že buď mám v instalaci Calcu vadu, nebo někde něco nastavené, co způsobuje takové chování. Ale netuším co a kde, ani si nejsem vědom, že bych v nastavení Calcu vrtal. Až budu mít čas, tak ho asi přeinstaluji.
V Google drive soubor funguje správně, a součty tam jsou vidět, tak si je opíšu z něho a uložím.

neutr · 3. 5. 2024 17:57:04

Napadlo mne, že asi používáte Apache OpenOffice tento balík asi nepozná číslo s tečkou. Nainstalujte si LibreOffice ve verzi 7.6.x

hape · 4. 5. 2024 09:48:49

Ne, OpenOffice nepoužívám. Měl jsem LibreOffice 7.4.7, aktualizoval jsem je na verzi 7.6.6.3, se kterou došlo ke změně grafiky zástupců ale vzorce stále nefungovaly. Odinstaloval jsem celý balík LO a znovu ho nainstaloval, ale vzorce stále nefungují, pořád mi dávají výsledek Chyba:502. Operační systém mám Windows 10 Pro 22H2, plně aktualizovaný. Už mi došly nápady, čím by to mohlo být.
Máme ještě jeden PC s Windows 10 Pro, až bude volný, tak do něj zkusím balík LO nainstalovat a vyzkouším jak se budou vzorce chovat tam.

EDIT:
Na druhém PC vzorce také nefungují a výsledkem jsou chyby 502 úplně stejně jako na prvním PC.

Editoval hape (4. 5. 2024 14:55:49)

neutr · 4. 5. 2024 17:21:38

Ještě bych doporučil smazat nastavení pro LO. To se udržuje i při nových instalacích. Najeďte si do svého uživatelského profilu (users - uživatelské jméno) > AppData > Roaming > LibreOffice > složka 4 a tu bez milosti smažte. Následně proveďte novou instalaci.

Pozor až budete ve svém profilu budete muset povolit zobrazení skrytých složek. Teprve potom se Vám objeví zašedlá složka AppData.

lp. · 5. 5. 2024 23:55:58

Dost složité. Stačí správně načíst csv soubor. V dialogu při otevření csv, lze kromě hlavičky určit i typ načítaných sloupců.

Ve spodní tabulce s náhledem souboru si posuvníky zobrazte začátek tabulky. Pak klik na hlavičku sloupce (v náhledu) - mám tam typ "Standardní", a v rozbalovacím menu typ sloupce vyberte správný typ. Mám starší verzi LO a v případě čísel s desetinnou tečkou musím vybrat (pro někoho ale ne pro mne zcela pochopitelný) typ "Anglicky USA". Dá se i vybrat více sloupců, předchozí výběr se standardně přidrží pomocí ctrl.

Pak už lze data zpracovat normálně. Tj. sumif, nebo tabulku rozdělit a použím normální sum, ... Na nějaké pomocné slouopce a value můžete zapomenout.

hape · 8. 5. 2024 08:28:56

OK, dík za další tip, jak se dá v Calcu něco nastavit. Ale stejného efektu se podle mě dosáhne nastavením Angličtina (US) v rozbalovací nabídce "Národní prostředí.
Všechny dosavadní rady co a kde nastavit a jaký vzorec použít k výpočtu u mně narážely na nefunkčnost vzorců a nešlo jenom o vzorec VALUE. Správně nefungoval ani ten SUMIF. Zjistil jsem že problém je v nastavení národního prostředí na Češtinu. Je to v globálním nastavení v Nástroje > Možnosti > Jazyková nastavení > Jazyky > Formáty, tam musí být v rozbalovacím seznamu "Národní prostředí" nastavena Angličtina (je jedno jestli US nebo UK) současně musí být při otevírání .csv souboru v okně "Import textu" nastaveno národní prostředí také na Angličtinu. Jakmile je někde nastaveno národní prostředí na Češtinu vzorce nefungují. Toto chování Calcu považuji za chybu.
Nakonec jsem se k funkčním vzorcům a požadovanému výpočtu dobral a považuji toto vlákno za vyřešené, tak ho tak i označuji.
Na závěr ještě jednou děkuji všem zúčastněným za pomoc a užitečné rady.

OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

#1 29. 4. 2024 09:56:47

Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#2 29. 4. 2024 10:37:03

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#3 29. 4. 2024 13:51:36

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#4 30. 4. 2024 16:51:29

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#5 30. 4. 2024 17:51:28

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#6 1. 5. 2024 08:02:04

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#7 1. 5. 2024 09:36:21

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#8 1. 5. 2024 13:08:07

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#9 1. 5. 2024 15:27:24

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#10 1. 5. 2024 15:52:50

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#11 1. 5. 2024 20:43:02

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#12 3. 5. 2024 10:04:58

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#13 3. 5. 2024 15:29:51

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#14 3. 5. 2024 17:30:34

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#15 3. 5. 2024 17:57:04

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#16 4. 5. 2024 09:48:49

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#17 4. 5. 2024 17:21:38

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#18 5. 5. 2024 23:55:58

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

#19 8. 5. 2024 08:28:56

Re: Součet hodnot v určitých sloupcích z určitých řádků - VYŘEŠENO

Zápatí