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

#1 6. 2. 2017 09:07:03

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

Samovolná změna vzorce - VYŘEŠENO

Tento dotaz navazuje na témata viz. Zobrazení dat z BASE v CALCu a následně  Seskupení hodnot podle datumu .


Všechno funguje jak má v případě, že při spuštění souboru došlo v databázi pouze k úpravě stávajících záznamů. V tom případě se data v tabulce CALC aktualizují a vše se řádně zobrazí. Pokud ale v původní databázi dojde ke změně počtu záznamů (vždy se jedná o přidání, takže počet záznamů se zvýší), tak se záznamy aktualizují správně, tabulka v CALCu se rozšíří, ale následný dotaz pro třídění záznamů podle měsíce se "sám od sebe" změní podle počtu záznamů následujícícm způsobem:


Např. původní tabulka záznamy v buňkách A2 až C30, sčítat budeme sloupec B, podmínka je v sloupci E, dotaz zní =SUMIF(E2:E1000;1;B1:B1000). Po přidání tří záznamů a opětovné aktualizaci se tabulka se záznamy upraví (doplní data), takže záznamy jsou v buňkách  A2 až C33 a dotaz se samovolně upraví na =SUMIF(E2:E1003;1;B1:B1000) - tedy rozsah pro test podmínky se o 3 zvýší, rozsah pro součtová data se nemění. V tom okamžiku se výsledek neprovede a je doplněn error.


Dotaz tedy zní, proč dojde ke změně vzorce pro podmínečný součet, a pokud ke změmě dojde, tak proč ne na obou položkách - tedy mělo by se měnit E1000 na E1003 a současně také z B1000 na B1003

Editoval hdplot (9. 2. 2017 15:02:15)

Offline

#2 6. 2. 2017 10:47:26

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

Re: Samovolná změna vzorce - VYŘEŠENO

Těžko odhadnout ale vidím jednu chybičku které možná vznikla při zápisu konkrétně =SUMIF(E2:E1003;1;B1:B1000) - oba úseky musí být totožné rozsahem E2:E1003 není v relaci s B1:B1000. Musí to být shodné E1:E1000 a B1:B1000. Vzorec porovnává jen ve vodorovné úrovni B1~E1 až B1000~E1000.
     Dále jde o to jestli jsou součtová data také vzorce a zda jsou ve stejném úseku. Pokud vím jde jednak o měsíc a jednak o rok v pomocných sloupcích. Pomocné sloupce musí být v mnohem větších úsecích nežli importovaná data a pak mohou vzít každou změnu. Takže pozor na souhrnné vzorce - musí být také součtem od začátku do konce pomocných sloupců.
     Nejspíš máte pomocné sloupce dostatečně dlouhé, ale je možné že souhrnné vzorce nesahají na konec. Těžko odhadnout - musel bych to vidět. Vámi popisovaná chyba že se vzorce v pomocných sloupcích neaktualizují mohou být dána tím že to nejsou vzorce, nebo nemáte zapnutý automatický přepočet. Podívejte se také na formát buněk v místě kde je chyba. Zadejte FORMÁT > BUŇKY > karta čísla a uvidíte jestli tam náhodou není boolean nebo text a podobně. Mělo by tam být "číslo", nebo "vše".


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É

Online

#3 6. 2. 2017 12:51:35

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

Re: Samovolná změna vzorce - VYŘEŠENO

To je právě ta chyba - původně tam byly oba úseky shodné, ale po aktualizaci dat (v případě že v databázi došlo k navýšení počtu záznamů) se meze úseků "samy od sebe" změní a proto to přestane fungovat. Zkuste si to na přiložené ukázce - Ukázkové soubory - bude to asi jasnější. Musíte samostatně otevřít databázi, přidat na konec aspoň jeden záznam, databázi zavřít, otevřít Calc a aktualizovat tlačítkem a měla by vám vyskočit chyba - buňka se sumarizací (červená na záložce MojeData) Když tento chybný vzorec opravíte, bude vše OK. Po zavření Calcu, doplnění databáze a opětovném otevření calcu se chyba opět objeví.


Zkuste si ještě i variantu, že se záznamy v databázi nepřidávají, ale naopak mažou (i když v mém konkrétním případě to tak nikdy nebude) Pak se vzorce pro určení měsíce z datumu změní na =MONTH(A#REF!), vzorce před a za koncem tabulky zůstanou správně (např. =MONTH(A24) a =MONTH(A25) ) a mezi ně se jakoby vloží ten chybný.


Napadá mě jenom to, že makro při aktualizaci maže a vsouvá buňky a Calc na to automaticky reaguje a aktualizuje vzorce, které se těchto buněk týkají. Jako řešení bych viděl to, že podmínku nebudu kontrolovat na buňky mimo rozsah, který makro ovlivňuje, ale pouze na buňky z tohoto rozsahu. Tedy místo vytvoření pomocného sloupce s měsícem (a kontroly na tento sloupec) je nutné provést kontrolu přímo na sloupec s datumem - něco takového jako že buňka obsahuje část textu - tedy např. leden 2017 což jsou datumy "1.1.2017" až "31.1.2017" kontrolovat na to, že obsahují text ".1.2017" tedy kontrolní součet by byl takovýto:  =SUMIFS(B2:B10000;A2:A10000;".1.2017"), přičemž ve sloupci B jsou sečítané hodnoty a ve sloupci a jsou datumy.


Na zkušební databázi to funguje. Zkusím ještě ostrá data a větším množství podmínek a dám vědět

Editoval hdplot (6. 2. 2017 13:38:46)

Offline

#4 6. 2. 2017 17:27:56

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

Re: Samovolná změna vzorce - VYŘEŠENO

Já jsem to původně stavěl se vzorcem Year, respektive Month v SUMIF - ale to se mi nepodařilo i když by to neměl být problém. Jistě by se s tím něco dalo dělat - možná pomocí maticového vzorce, ale podmínka například MONTH(A1)=1 mi to nevzalo. Takže by asi pomohla podmínka nacpaná do IF.
     Ještě by existovalo řešení makrem natáhnout správné rozsahy a souhrny, ale čistá cesta by byla správná - tedy zcela bez pomocných vzorců. To by se mělo lépe otestovat.
     Další věcí by bylo pomocné sloupce aktualizovat také jako odkaz například odjinud (jiný list, nebo dokument) a ještě možná pár triků ale to bych musel testovat.


     Možná že by pomohla jen absolutní adresa v pomocných sloupcích - místo adresy např A1 adresa $A$1. K tomu stačí udělat správně pomocné sloupce, oba je celé vybrat a stisknout Shift+F4 (v Libre Office - stačí F4). Budou tvrdě odkazovat na původní buňky. Otestujete a napište.

Editoval neutr (6. 2. 2017 17:29:31)


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É

Online

#5 7. 2. 2017 17:58:00

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

Re: Samovolná změna vzorce - VYŘEŠENO

neutr napsal(a)

vidím jednu chybičku které možná vznikla při zápisu konkrétně =SUMIF(E2:E1003;1;B1:B1000) - oba úseky musí být totožné rozsahem

Nemusí. Pletete si to s funkcemi typu SUMIFS, kde rozsahy opravdu musí být totožné.

neutr napsal(a)

E2:E1003 není v relaci s B1:B1000. Musí to být shodné E1:E1000 a B1:B1000. Vzorec porovnává jen ve vodorovné úrovni B1~E1 až B1000~E1000.

Ani toto není pravda. Pokud SUMIF vyhodnotá podmínku pravdivou v buňce E2, přičte hodnotu z buňky B1, atd. dokud neprojde celý rozsah E2:E1003. Na rozsah druhého pole nekouká. =SUMIF(E2:F1003;1;B1:C1000) spočte totéž jako vzorec =SUMIF(E2:F1003;1;B1)

Pokud pole nemají stejnou velikost je funkce volatilní, tj. přepočítává se při každém přepočti sešitu. Může to zpomalovat odezvu.

Editoval lp. (7. 2. 2017 21:01:14)

Offline

#6 7. 2. 2017 22:34:32

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

Re: Samovolná změna vzorce - VYŘEŠENO

Pro lp.
     Rozsah =SUMIF(E2:E1003;1;B1:B1000) tedy E2:E1003 je 1001 řádků, ten druhý je tisíc - nejsou stejné. Bohu žel hdplot poslal vzor bez dotazu takže to nejde otestovat i když bych si asi poradil. Není vidět ani ta konkrétní chyba. Jen byly posunuty čísla řádků od 19. asi skokem až na 28 v prvním pomocném sloupci. Druhý byl v pořádku.
     Autor uvádí že mu to hází chybu =MONTH(A#REF!) - v 1. pomocném sloupci která se vloudí. Nastal tam nějaký posun. Je možné se domnívat že druhý pomocný sloupec byl zkrácen o 1 řádek, nebo naopak byl přidán řádek do 1. pomocného sloupce? Měly by být oba sloupce mimo dosah aktualizovaných polí a pole zahrnují mnohem víc řádků nežli aktualizace. Prostě je to divné.


     Jestli tedy souhrn čte chybu - tak jen dík posunům. Když by se posunuly absolutní adresy tak budou ukazovat stále stejně a chyba by neměla vzniknout ani při promíchání.
     Chyba vzniká jistě když se čte v jednom sloupci řádek 2-1003 a druhý na 1:1000. Jiná relace nemůže vzhledem k systému existovat. Máte sice pravdu v tom, že to není spojené pole jako u XY+LOOKUP - vzpomínám si že posunuté pole v LOOKOP chybu vyhodilo i když se zadávají nespojitě tak jako v tomto případě. I když je logické že menší pole dá výsledek jako kdyby mělo menší obsah a bylo stejně rozměrné jako druhé pole.
     Přes to musí být dodržena ta relace stejných řádků když kvůli ničemu jinému tak kvůli orientaci. Podivnost chyby je v tom posunu - jako kdyby asi došlo k posunu (přepisu?) jenom u prvního pomocného sloupce. Není to hotové chce to doladit třeba ty nulové hodnoty.


     Pokud mohu poradit autorovi tak by to chtělo udělat na prvním prázdném řádku žluté pozadí. Po načtení nového stavu bude vidět co se šouplo a kam.

Editoval neutr (7. 2. 2017 22:36:39)


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É

Online

#7 8. 2. 2017 08:42:56

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

Re: Samovolná změna vzorce - VYŘEŠENO

Pro neutr


V zaslané ukázce (znovu přikládám odkaz) - Ukázkové soubory - je soubor CALCu a soubor BASE na kterých to testuji. Předpokládal jsem, že když si jej stáhnete a soubor BASE zaregistrujete, tak můžete provést testy, o kterých jsem psal a ze kterých bude zřejmý problém. Nelze to jenom prohlédnout, musí se stím experimentovat. Stačí jenom do BASE doplnit nebo odmazat nějaký záznam (nebo více záznamů) a následně načíst a aktualizovat CALC a po několika takovýchto pokusech by mělo být zřejmé, v čem je problém. Myslím, že je to určitě tím, že CALC při práci makra přidává nebo maže řádky, ale nějak "podivně" - v některých sloupcích se to projeví a v některých ne.


Pokud se týká toho barevného označení řádku, je to dobrý nápad, který by mohl zvýraznit problém. Zkusím to. Bohužel jsem teď dva dny byl bez PC, takže jsem zatím nikam nepokročil.

Editoval hdplot (8. 2. 2017 08:43:17)

Offline

#8 8. 2. 2017 09:56:26

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

Re: Samovolná změna vzorce - VYŘEŠENO

Úprava
Tak jsem to snad pořešil. Já jsem se vlastní blbostí dopracoval k chybě kterou jsem už udělal vícekrát. Mám na Win XP nainstalované oba balíky AOO i LO. Já jsem zaregistroval databázi v LibreOffice a pokoušel se to aktualizovat.
     Následně jsem se domníval že je chybně nastavené makro v AOO. V LO mi vyskočila hláška že dotaz který je vidět není dostupný. Když jsem nad tím přemýšlel tak mne napadlo že dělám něco špatně a podíval jsem se dnes znovu - a našel tu pitomost s registrací.


     Takže po přeregistrování databáze AOO jsem se dopracoval i k tomu funkčnímu makru a zjistil v čem je problém. Problém je v tom, že když je úsek v Calcu nastaven na jinou hodnotu nežli je velikost databáze (různé počty položek) - což vznikne logicky téměř vždy tak vznikne chyba v tom rozdílu buněk. Je to celkem nelogické ale je to tak.
     Takže jsem udělal úpravu makra které se spouští hned po importu. Přepíše vzorce a je vše v pořádku. (Vzorce jsem vylepšil tak, že tam kde nejsou data není nic vidět).
     

     Je tam jenom jeden problém ta obnova vzorců se musí dělat pro všechny vzorce a já vidím jenom jeden. Nevím kam patří ty ostatní - prostě rozvržení. To potřebuji k tomu abych také tyto vzorce aktualizoval, ale můžete sám v příslušném makru - na konci kde je vidět ten SumiF.


     Omlouvám se - zazmatkoval jsem a čekal až se ozvete.


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É

Online

#9 9. 2. 2017 01:19:31

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

Re: Samovolná změna vzorce - VYŘEŠENO

Ač je to vyřešeno, dovolím si pro "ukázková data" v bodech naznačit standardní postup, třeba se to bude někomu hodit:

1) Záložka  Moje data, vybrat někerou buňku, třeba a4.
2) Data, Kontingenční tabulka, vytvořit, současný výběr. (zde si můžete alternativě přímo načítat data z dBase.)
3)OK.
4) Myší přetáhneme datum do pole řádku, množství do datová pole a pokud chceme komodita do pole sloupce.
5)klik na více,
6)adresu pole Vybrat z  přepsat na DBQ (jméno aktualizované oblasti, při korektní aktualizaci obsahuje načtená data)
7) Výsledky do, vybrat nový list (nebo jinam podle potřeby), OK
8) klik na některé datum, Data, seskupení a souhrn, seskupit (nebo rovnou F12)
9) V intervalech vybrat měsíce a roky (nebo jinak podle potřeby), OK

Hotovo. Bez vzorců.

Ostatně nepovažuji za dobrý nápad umisťovat na list, kam se importují data cokoliv dalšího.

Ps. některé verze calcu mohou mít problém s aktualizací kontingenční tabulku po aktualizaci dat.
Ručne se přepočet spustí - pravý klik na tabulku, vybrat obnovit.

Editoval lp. (9. 2. 2017 01:28:17)

Offline

#10 9. 2. 2017 15:04:39

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

Re: Samovolná změna vzorce - VYŘEŠENO

Problém považuji za vyřešený, ale protože řešení je několik, tak to shrnu.


1) Řešení dle neutr. - Po úpravě maker již vše funguje správně a hodnoty měsíců se aktualizují tak, jak mají. Nevýhodu vidím v tom, že pro každý měsích se musí nastavit trochu jiný vzorec pro sumarizaci (jiná podmínka), takže toto je nutné si připravit v makrech buďto předem s rezervou - výhled do budoucnosti, nebo makra průběžně každý měsíc aktualizovat


2) Řešení dle hdplot. - Nedalo mi to a vyzkoušel jsem variantu, kterou jsem zde v diskusi zmiňoval - tedy nevytvářím žádné pomocné sloupce Rok=Year(buňkaSdatumem) a Měsíc=Month(buňkaSdatumem), ale podmínku směřuji přímo do sloupce s Datumem z databáze, tedy =SUMIF(RozsahBuněkSdatumem;".12.2016";RozsahBuněkShodnotami). Takto lze použít původní neupravovaná makra a RozsahyBuněk se aktualizují v obou sloupcích (podmínka i sčítané hodnoty), protože se jedná o původní buňky vyplňované původním makrem. Nevýhoda je stejně jako u neutr, tedy je nutné si připravit vzorce pro jednotlivé měsíce v buňkách dopředu - výhled do budoucnosti, nebo buňky průběžně každý měsíc doplňovat o další řádek


3) Řešení dle lp. - Sám bych to nedal dohromady, ale podle návodu to šlo dobře a funfuje to také správně. Toto bych řekl, že je v daném případě nejlepší řešení, protože s postupným přidáváním dalších měsíců a roků do databáze se kontingenční tabulka sama automaticky rozšiřuje, takže jednou napsané řešení je bez úprav funkční neustále.


Vyzkoušeno a pro danou databázi dávají všechna 3 řešení rovnocenné výsledky, takže použít může každý co je mu bližší, nebo v návaznosti na ještě nějaké další požadované výstupy a pohledy na zdrojová data. Nicméně i nepoužité řešení bude zdrojem informací pro příště - díky za pomoc

Offline

#11 9. 2. 2017 17:49:43

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

Re: Samovolná změna vzorce - VYŘEŠENO

Problém Nevýhodu vidím v tom, že pro každý měsích se musí nastavit trochu jiný vzorec pro sumarizaci (jiná podmínka), takže toto je nutné si připravit v makrech buďto předem s rezervou - výhled do budoucnosti, nebo makra průběžně každý měsíc aktualizovat je celkem pochopitelný jenomže nejde ani tak o pravidelné připisování vzorců jako rozšiřování počtu sledovaných měsíců a let. To musí podchytit graf - ale obdobný problém bude s kontingenční tabulkou.
     Samozřejmě lze použít trendy, nebo jednoduše sledovat třeba jen poslední 2 roky. A to už lze řešit snadno. Jen se bude aktualizovat sledovaný rok, nebo i měsíc - něco jako fiskální dvouletka (dva nebo 3 různé roky). Potom zůstane počet vstupů stejný.


     Není ale problém vytvořit sloupec sledovacích vzorců který se bude sám aktualizovat podle aktuálního data. Jde spíš o volbu typu grafu. Doporučil bych načítat například posledních 24, nebo 36 měsíců do pevného místa a už se nikdy nemusíte starat - jen kontrolovat jestli to neblbne.
     To makro co tam dává vzorce může samo automaticky vytvořit rozsahy jak pomocných sloupců, nebo vzorců bez pomocných sloupců, tak i nové souhrny pod (nebo vedle) ty staré.


     Jiným řešením je udělat tabulky dotazu na míru a pevný počet měsíců. Takže by se načítaly nikoliv celé tabulky ale odfiltrované na pevný počet. Ono je to asi jedno. Důležité je znát systém který to dělá abyste znal způsob opravy, nebo možné příčiny.
     Faktem je že byste musel SQL posílat z Calcu před přenosem. Měl by jít postavit i zcela nezávislý systém v Base ale vzhledem k omezení na některé funkcionality selectu nevím jak se s tím poprat. Zese asi přes makro uvnitř Base.


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É

Online

Zápatí