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

#1 22. 7. 2014 09:02:43

Gooorila
Člen
Registrace: 4. 10. 2009
Příspěvků: 48

Porovnání dvou tabulek ve dvou sešitech

Zdravím

Rád bych našel nějakou funkci, která porovná dva sešity v .ods a dle nalezených/nenalezených buněk upraví nový sešit. Pokoušel jsem se to řešit pomocí vlookup, ale není to ideální, tak zjišťuji, zda to nejde udělat elegantněji.


Omlouvám se, ale musím problém vypsat podrobněji, takže to bude delší:
- Mám databázi, do které různí lidé zapisují a odepisují zakázky. Počet položek se většinou pohybuje kolem 100 a v týdnu se cca 50% změní (vyrobené zakázky z databáze po expedici zmizí a při zadání se zapíší nové zakázky) Jednou týdně tuto databázi vyexportuji do excelu (export ihned do OpenOffice není možný). Pk musím anuálně smazat zakázky, které již mají datum ve sloupci s termínem ukončení a přehážu řádky, protože při exportu začínají zakázky dílnou 3 a končí dílnou 1 a já to potřebuji naopak. Pak vložím tabulku do listu vstup v novém sešitu OpenOffice a v listu výstup dostanu data upravené po smazání mezer (Pomocí TRIM).
- V novém listu výstup v .ods, mám přidány vlastní sloupce na konci tabulky, jež označují stav výroby. Tento dokument vytisknu, dám na jednotlivé pracoviště a pracovník, když zakázku vyrobí, ji odškrtne. Já si jednou týdně ty papíry sesbírám a do jednoho dokumentu zaškrtnu všechny křížky.


Právě v tom zaškrtávání je problém. Mám dokument z minulého týdne, kde manuálně dle výpisů doplním zaškrtané zakázky. Pak si vyexportuji nový dokument, upravím jej dle informací výše. Nyní mám tedy stejné dva sešity a to sešit z minulého týdne a aktuální. V sešitu z minulého týdne mám zakázky a u nich označen jejich stav. V novém mám jen zakázky (které se ale o cca 50% změnily oproti minulému týdnu) a nyní musím vzít první buňku v nové tabulce, kde je číslo zakázky a to manuálně hledat napříč celou tabulkou z minulého týdne. Když ji nenajdu, pravděpodobně je to nová zakázka a tudíž se na ní ještě nepracovalo, takže políčka nechávám nezaškrtnuté. Když ale zakázku najdu, kouknu zda a které políčka označující výrobní dílnu, jsou zaškrtnuté a stejné zaškrtnu v novém dokumentu, aby dílna věděla, že na ní už pracovat nemusí.


Je to pracné, stávají se chyby a podobně, takže jsem hledal nástroj, který to zautomatizuje a vyřešil to vlookupem. Dokumenty vždy označuji číslem týdne, takže nejdříve se dostanu do stadia, kdy mám upravený dokument z nového týdne. Pak dokument z minulého týdne přejměnuji na old.ods. V novém dokumentu je v políčku jednotlivé dílny vzorec s vlookupem, který se právě kouká do dokumentu old.ods, hledá číslo zakázky a zaškrtává. Jenže mám problém, že když ten řádek se zakázkou v minulém týdnu nenajde (zakázka nebyla zadána), tak by měl nechat prázdné políčko. On ale vypíše chybovou hlášku. Zkoušel jsem to ošetřit pomocí IF, ale nepřišel jsem na to jak (dělá mi problémy i ten TRIM, který maže mezery, protože pole pak není prázdné, ale vždy je tam nějaký vzorec). Dále je tam ten mezikrok s přejmenováváním na old.ods, tak jsem uvažoval, ať si vyhledává v dokumentu, který je pojmenován podle čísla minulého týdne, ale na to jsem také nepřišel. V neposlední řadě, vlookup, když dám odkazovat na jiný sešit, tak si vypisuje celou cestu, takže když se změní složka, zase to nefunguje.


Dokumenty ve stavu, jak to mám nyní, přikládám, ať se když tak lze kouknout, protože ten popis problému není asi nic moc.


Dokument nový: http://uloz.to/xjnmWNrC/29-ods
Dokument starý: http://uloz.to/xzPZVKwJ/old-ods


Děkuji za pomoc

Editoval Gooorila (22. 7. 2014 09:23:24)

Offline

#2 22. 7. 2014 14:58:25

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

Re: Porovnání dvou tabulek ve dvou sešitech

Mrknu na to. Napadá mne, že místo prohledávání dvou sešitů jste měl do nového nakopírovat původní list, zpracovat a pak smazat. Tím pádem by VLOOKUP nevypisoval adresy.
     Dokonce mne napadá, že jste měl data nakopírovat vedle sebe do jediného listu jako DDE úsek, dde rozpojit a zpracovat. Pak totiž VLOOKUP dostane úsek - příklad A1:AC500 a jeho zadání pak vypadá takto :

=VLOOKUP(D1;B1:AB500;27;0)

     Předpokladem je, že údaj hledaný je v buňce D pro každou buňku A (A1-D1, A2-D2). Do buňky A jde výsledek (je tam ten vzorec). Příznaky porovnání budou ve sloupci B - AB.
     Co s tím byste asi věděl. Já se na to mrknu, ale jsem teď na Linuxu kde dělám instalace, Nenám tady knihovny pro makra a pracuji jen z hlavy. V makrech má vytvořeno podobné porovnávání (jako nákupy a tržby pro nějaký obchod). Takže nevím jestli dnes, ale pokud buděte chtítr - pošlu makra a podívám se na ty Vaše vzory.


Moje e-mailová adresa
Pokud je Váš problém vyřešen, označte prosím svůj příspěvek za "VYŘEŠENÝ"
Zlepšíte orientaci při vyhledávání řešení JAK OZNAČIT TÉMA ZA VYŘEŠENÉ

Offline

#3 23. 7. 2014 06:38:18

Gooorila
Člen
Registrace: 4. 10. 2009
Příspěvků: 48

Re: Porovnání dvou tabulek ve dvou sešitech

Zdravím


Děkuji za reakci.


Kopírování listu do jednoho sešitu jsem také uvažoval, ale nepřišel jsem na to, jak to udělat. Vyexportovaná tabulka z databáze obsahuje buňky, ve kterých je text a to včetně mezer. Aby byla tabulka nějak přehledná, musel jsem se zbavit oněch mezer a to jsem (možná chybně) vyřešil dvěma listy. Jeden vstupní obsahuje surová data (jen jsou přehozeny pořadí řádků, ale to by asi šlo řešit i nějakým řazením) a druhý výstupní list, který pomocí TRIM smaže zbytečné mezery z textu. Takže kdybych dával do jednoho sešitu další týdny, budou se mi prát ty vzorce, které odkazují na jiné listy...nebo nevím, jak jinak to udělat.


Ten předpoklad, že hledaný údaj je v obou tabulkách, je právě problém. Řekněme, že mám z minulého týdne tabulku, kde je nejvyšší číslo zakázky 114 0005 a já jsem včera ráno do databáze dopsal další zakázku s pořadovým číslem 114 0006. Když dnes vyexportuji a porovnám vlookupem, tak zakázku 114 0006 ten vzorec ve vyexportované tabulce jednoduše nenajdě, protože ta tabulka byla vyexportována a uložena/vytištěna minulý týden a v té době jsme o 114 0006 nevěděli. Předpokládal jsem tedy, že tu zakázku vzorec nenajde a tedy do mých sloupců nebude vyplňovat nic. On ale u těchto zakázek napíše chybové hlášení, že ji nenašel.


Možná to tím vlookupem dělám složitě, tak jestli ještě napadá někoho jiný vzorec, budu jen rád. Bohužel, makra vůbec neovládám a už jsem si jejich naučení sliboval dlouho, ale nebyl na to prostor, tak spíš jen nějaký ten vzorec.

Offline

#4 23. 7. 2014 07:54:04

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 697

Re: Porovnání dvou tabulek ve dvou sešitech

pokud je problém jen odstranění chybové hlášky tak to dělám následovně IF(ISNA(VLOOKUP(hledání);"";VLOOKUP(hledání)). Pokud hledané kriterium v databázi není, vypíše se "" čili nic (nebo něco jiného co vám dá tu potřebnou informaci - třeba "nula"). Pokud byste chtěl jen hodnotit zda je či není v databázi moho by to být ještě takto: IF(ISNA(MATCH(hledání);"NE";"ANO")


LibreOffice 5.4.

Offline

#5 23. 7. 2014 09:38:06

Gooorila
Člen
Registrace: 4. 10. 2009
Příspěvků: 48

Re: Porovnání dvou tabulek ve dvou sešitech

Zdravím. Problém je prakticky vyřešen přidáním toho IFu. Moc děkuji, zítra budou probíhat montáže a budu opět exportovat v ostrém provozu, takže ještě ověřím, ale v "laboratorních" podmínkách to skutečně funguje.

Děkuji. Kdyby měl někdo ještě nějaké jiné řešení, kdy bych si odpustil nějaké úpravy vzorcem TRIM a neměl dva sešity provázané, budu jenom rád, ale jak jsem psal, nyní by to mělo být vyřešeno.

Offline

#6 23. 7. 2014 09:57:30

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

Re: Porovnání dvou tabulek ve dvou sešitech

Tak dneska jsem zase na Windows (včera jsem byl na Linuxu - neměl jsem prostor něco hledat). prohlížím ty dva sešity. Uvedu nejdříve své postřehy a pak se zeptám detailně.
- O jaké databázi to hovoříte? Mám rozumět tomu, že existuje databáze, která se načte do sešitu jako 'vstup' - řeknu 1:1, nebo jen část - řekněme rozpracované a objednané z období roku ap.
- Primárně se zadávají zakázky do databáze. Takže sešit vytvořený na aktuální období obsahuje jen data k poslednímu dni předchozího období.
- samozřejmě podle čísla (list Výstup C1 - písmo velikost 44) soudím, že jde o kalendářní týdny. Pak bych měl ale dotaz proč to děláte právě takhle.
1. - Řekněme je pátek. Zaznamenáme do sešitu s názvem '29' všechny provedené procesní úkony spojené s dokončením, zpracujeme přípravu výroby ne pondělí - uložíme.
2. - V pondělí otevřeme znovu sešit 29, ale nyní odstraníme vše co je aktuálně hotové - například záznam do databáze - postoupení materiálové účtárně - k fakturaci. Něčteme nově zadané zakázky z minulého týdne k těm 'nedokončeným - rozpracovaným' a spadla klec - protože :
3. - Původní sešit upravený podle bodu 2. '29' uložíme jako sešit s názvem 30.
-------------------------------------------
A - máte - li ve 29. týdnu 114 zakázek - jste opravdu dobrá firma - je to asi obden nová zakázka. Ale stejně - max 5 nových zakázek týdně se dá upravit velice snadno i manuálně a bez vzorců.
- Pokud se vzorci - tak řekněme stejně jako to děláte, jen zkopírujete ty nové zakázky (v surové podobě) zkopírujte ještě jednou pod vlastní zápis - vynechte 1 (nebo více) řádků. Do té kopie pak vložte vzorce s Tím ořezáním (TRIM(Xy)) a odkazujte do původní kopie (1 kopie = přepis z databáze).
     Následně 2. kopii vystřihnete a vložíte jen jako čísla, text a datumy místo té první kopie. Je hotovo a trvá to tak minutu. Totéž každý pondělek - a žádné HLOOKUP.
------------------------------------------
B. Je - li to jinak, nežli si nyní představuji, tak prosím upřesněte to, co se týká té databáze, nebo proč ukládáte jako 'OLD' a ne jako číslo týdne. Totéž je možné dělat bez většího problému denně, nebo měsíčně.
     Zřejmě bych já osobně čistil makrem při načtení a není to žádný problém. Ani třeba to, aby se odstranil textový formát čísla (to je číslo s tou čárkou '1 - pak musíte načítat ne číslo ale text - př: "1"). Funkce Trim je i v Basicu, ale umí jen mezery na začátku a konci stejně jako ta samá funkce v sešitě - mezera = Char(32).
     Takže když to bude opodstatněné udělám Vám makro na jedno kliknutí (klávesovou zkratku ap). Ale zatím se mi zdá, že jde jen o zbytečně komplikovaný systém práce.

Editoval neutr (23. 7. 2014 10:03:51)


Moje e-mailová adresa
Pokud je Váš problém vyřešen, označte prosím svůj příspěvek za "VYŘEŠENÝ"
Zlepšíte orientaci při vyhledávání řešení JAK OZNAČIT TÉMA ZA VYŘEŠENÉ

Offline

#7 23. 7. 2014 10:51:54

Gooorila
Člen
Registrace: 4. 10. 2009
Příspěvků: 48

Re: Porovnání dvou tabulek ve dvou sešitech

Zdravím

Nejedná se o 114 zakázek, které máme doposud od začátku roku, ale zakázky, které jsou aktuálně ve výrobě.
Takže:
Je podepsána zakázka a dispečer výroby ji zapíše do databáze.
Pak tam ve výrobě dopisujeme termíny, kdy přišla zakázka do výroby a kdy byla vyrobena.
Ve chvíli, kdy je zakázka vyexpedována, z databáze zmizí.

Takže ta zakázka tam je tak 6 týdnů. Rozdíl oproti starému a novému sešitu je nejen to, že nové zakázky přibývají (a vlookup je tedy nenašel v minulém sešitu), ale také ubývají, když jsou vyexpedovány. Ten export dělám tedy jen jednou týdně a mimo jiné i proto, že mi to zabere minimálně hodinu času, řádek po řádku kontrolovat, zda a kde je tato zakázka v minulém sešitu a jestliže ano, v jaké fázi výroby je.

Kolega nahoře mi už ale napsal vzorec, který tento problém ošetří. Sice jsem to doposud dělal na testovacích sešitech, které jsem uložil do složky na ploše a nyní musím zase všechny vzorce přepisovat, protože je to vše v jiné složce na sdíleném disku a tedy cesta u vlookupu se napsala celá a neodkazuje nikam, ale už nyní je to ve stavu, kdy mi to ušetří desítky minut.

Offline

#8 23. 7. 2014 14:06:17

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

Re: Porovnání dvou tabulek ve dvou sešitech

Pomohlo by Vám makro, které samo načte a zkontroluje ten sešit na síti?
     Pokud ano, tak mi napište které sloupce se mají vyhodnotit a jak. Mám předpřipraveno několik podobných záležitostí. například :
Makro otevře sešit jako skrytý (nesmí být ale zrovna otevřený někým jiným) a pak řádek po řádku prohledá ten aktivní - Vámi otevřený. Samozřejmě mám i opačně fungující - řádek po řádku prohledává Váš sešit ten otevřený skrytě.
     Není problém prohledat oblast buňku po buňce, ale dělá se to pomalými makry takže by to mohlo chvíli trvat. Jiná možnost je bleskem ten druhý sešit zkopírovat a vložit do toho Vámi otevřeného (rychlejší, nežli DDE které uříváte nyní - ale musíte zadat makru pokyn) - například do pomocného listu.
     Porovnávat pak makrem v sešitě. Lze samozřejmě mazat, ale spíš označit například podkladem, nebo třídícím parametrem (například za sloupcem G kde ještě zapisujete).


     Aby makro fungovalo, potřebuji úplnou URL - tedy například ServLapetus/Work/29.ods. Nejlépe je udělat na Vašem počítači hypertextový odkaz - nabídka Vložit - HyperTextový odkaz a otestovat, zda Vám tento odkaz sešit otevře.
     Je - li tomu tak, potom ho znovu otevřete tím samým příkazem, ale nyní tam je v prvním řádku vymodřená správná URL a tu zkopírujte.
     Když by to nešlo, tak je problém možná na síti s přístupem. Může tam být někde heslo a to byste měl pak doplnit do makra sám - já ho ani znát nechci. (Udělal bych vstup v místě, kde se musí to heslo dopsat - prostě byste spustil a hláška by Vás zastavila nad správným místem)


     Udělejte znovu sešit kde barevně vyznačíte co se má udělat pro porovnání. Pro různé úkony různou barvou sloupce a pak také koment k té barvě. Napište tam to URL. Nemělo by mi to trvat dlouho - když do toho nic neskočí tak možná do hodky.


Moje e-mailová adresa
Pokud je Váš problém vyřešen, označte prosím svůj příspěvek za "VYŘEŠENÝ"
Zlepšíte orientaci při vyhledávání řešení JAK OZNAČIT TÉMA ZA VYŘEŠENÉ

Offline

Zápatí