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

#1 5. 2. 2023 07:46:27

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 38

Makro - Načtení externích dat - "VYŘEŠENO"

Dobrý den,
zkouším dohledávat data pomocí funkce VLOOKUP v kombinaci s MATCH, SUBSTITUTE, VALUE ze 2 externích souborů.
Trvá to dlouho a při přepnutí do daného listu opět čekám, než se data zobrazí.
Vstupní data mají až 10 tisíc řádků a až 50 sloupců. Ne všechny sloupce potřebuji.
Makrem by to mělo být rychlejší. Bohužel s ním nemám velké zkušenosti.
   
Zadání:
1. po spuštění makra dotaz na externí soubor (formátu XLS) a případně výběr listu (může jich být více).
2. do aktuálního listu import sloupců dle jejich názvu "PN","Text AAA", "hmotnost", "Popis AAA", "Popis BBB", "Unit Quantity","Minimum Order Quantity", "UOM", "Box Quantity",
"Quantity 1", "Price 1" až "Quantity 10" a "Price 10". Ne všechny buňky musí obsahovat data.
3. Oprava formátu buněk ve sloupcích "*Quantity*" na formát "číslo", záměna "," za ".", např. "Box Quantity" = 2,500 (správně 2500), formát buňky je text
4. Oprava formátu čísel ve sloupcích "*Price*" - záměna tečky za čárku, např. cena = 0.08536 (správně 0,08536)
5. setřídění dat dle "PN" (sloupec A)
Výsledkem by měl být seznam bez prázdných sloupců.
   
Upravený zdrojový sešit je zde.
https://docs.google.com/spreadsheets/d/ … ue&sd=true
Děkuji.

Editoval Eddie60 (1. 4. 2023 12:14:12)

Offline

#2 5. 2. 2023 09:05:45

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

Re: Makro - Načtení externích dat - "VYŘEŠENO"

Eddie60 napsal(a)

... Trvá to dlouho a při přepnutí do daného listu opět čekám, než se data zobrazí.
Vstupní data mají až 10 tisíc řádků a až 50 sloupců. Ne všechny sloupce potřebuji.
Makrem by to mělo být rychlejší. Bohužel s ním nemám velké zkušenosti.   
.....

     Celkem snad chápu co potřebujete opravit, vytáhnout a porovnat s hodnotami jiného zdroje. Přes to je také znát, že vzorce asi nepoužíváte tak efektivně, jak byste mohl. Pro těžbu vzorci nemusíte externí sešity otevírat. Makro je sice otevírá, ale "hidden", tedy skrytě.
     Je otázkou, zda by nebylo lepší importovat celý list a ten snadno promazat - nejlépe přetříděním pomocí pomocného sloupce.


     Nevím ale jak si představujete výslednou tabulku. Co znamená "promazat prázdné sloupce". Ve sloupci A máte nabídky "Item 1 - 10". Ve zbytku tabulky jsou jednotlivé "Item" v předpisu sloupce.
     Postup výběru a uspořádání může být alternativní v tom, že se například do stejných sloupců nakopírují všechny odpovídající hodnoty které mají ještě pomocný sloupec s označením zdroje pro odlišení. Nakonec se to seřadí a všechny "itemy" jsou pod sebou. To se dá podle potřeby rozebrat na jednotlivé nadídky "item" ve stejném sloupci pro stejnou nabídku. Jiná nabídka v jiném sloupci.


     Jste se jist, že konkrétní "item" je v sešitech unikátní? Nemůže se tam vyskytovat stejná položka s jinými parametry - zejména jednotkovou cenou, nebo se mají stejné položky sečíst a nějak zprůměrovat?


     Makro Vám po takovémto zadání nikdo nepostaví - musí být představa vzorem zdrojů, nejen výstupu. Nejspíš musíte postnout ukázky zdroje. Například pokud dostanete makro podle požadavku viz 1. po spuštění makra dotaz na externí soubor (formátu XLS) a případně výběr listu (může jich být více). Není to problém, Ale budete mít sešit i list ke kterému se musí zadat úseky a hledané hodnoty. Pokud nemáte zkušenosti tak to nedáte a nevím zda bych chtěl někoho korespondenčně učit programovat.
     Mám s takovým postupem bohaté zkušenosti. Když se k něčemu dopracujeme, autor přijde na to, že ještě to a tohle a nakonec něco ještě úplně jiného.


     Nechci mluvit za druhé, ale bez validní ukázky zdroje do toho nejdu. Nevím například jestli je vhodné nejprve opravit celý zdrojový sešit a pak z něj vycucat jenom něco, nebo opačně - vycucat a pak opravit. Například špek je čárka jako oddělovač tisící která se má odstranit a naproti tomu desetinnou tečku nahradit čárkou. To vypadá spíš na nesprávný import z nějakého CSV - byť do Excelu. Možná by stačilo jen zkopírovat a vložit do Calcu se správným filtrem a půl je hotovo .....


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 5. 2. 2023 11:00:32

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 38

Re: Makro - Načtení externích dat - "VYŘEŠENO"

Díky za Vaši reakci. Nechci Vás zbytečně zdržovat, ale potřebuji pomoc.
1) Nepotřebuji "opravit, vytáhnout a porovnat s hodnotami jiného zdroje."
Potřebuji do prázdného listu zkopírovat potřebné sloupce ze souboru "Dodavatel.xls".
Prázdné sloupce v souboru jsou data, která nepotřebuji, proto jsem je vymazal. Celkem je tam 176 sloupců.
Nejedná se o "nesprávný import z nějakého CSV". Data stahujeme z webu dodavatele.
Položky (=druh zboží) ve sloupci A "Item 1 - 10" jsou jedinečné.
Buňky "Quantity 1" až "Quantity 10" jsou množstevní limity s odpovídající cenou "Price 1" až "Price 10".
Ostatní sloupce mají víceméně informativní charakter.
Výsledkem by mělo být pouze 29 sloupců - viz soubor Test.ods
https://drive.google.com/file/d/127A5WW … sp=sharing
V listu "Kalkulace" jsou chyby, které se snažím odstranit úpravou vstupních dat.
   
2) K "Přes to je také znát, že vzorce asi nepoužíváte tak efektivně, jak byste mohl."
Možná máte pravdu. Třeba mi poradíte, jak to zrychlit. Používám tento vzorec (příklad) pro načtení dat do listu Dodavatel1:
=VLOOKUP($A2;'file:///C:/Users/Eddie/Downloads/SynologyDrive/Calc/Dodavatel1.ods'#$'Sheet 1'.$E$2:$EA$5000;MATCH("Quantity 1";'file:///C:/Users/Eddie/Downloads/SynologyDrive/Calc/Dodavatel1.ods'#$'Sheet 1'.$E$1:$EA$1;0);0) - soubor *.xls ukládám do *.ods (soubor je výrazně menší, formát *.xls mi dělal problémy)
a pak v dalším listu upravuji zobrazení čísla (Quantity):
=VALUE(SUBSTITUTE(VLOOKUP($A2;$Dodavatel1.$E$2:$Dodavatel1.$EA$5000;MATCH("Quantity 1";$Dodavatel1.$E$1:$Dodavatel1.$EA$1;0);0);",";""))
   
Předpokládál jsem, že jednorázové načtení dat makrem výrazně ušetří čas. Mění se sortiment zboží, množství a ceny.
Nejsem si jist, že cca 150 tisíc vzorců (29 sloupců x 5 000 řádků) na jednom listu bude rychlejší než makro.
 
3) Napadlo mě zjednodušení bodu 1. a 2. zadání.
Do aktuálního ceníku zkopíruji všechny data, vložím prázdný řádek pod Hlavičku tabulky a z pomocného listu do tohoto prázdného řádku zkopíruji řádek, kde v bude parametr např. "AAA" u sloupců, které se smažou makrem (nebo naopak, které zůstanou a ostatní se smazali). Nevím, co je jednodužší.

Editoval Eddie60 (5. 2. 2023 15:09:54)

Offline

#4 6. 2. 2023 08:06:57

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

Re: Makro - Načtení externích dat - "VYŘEŠENO"

ad 1) Problém importu mne napadl když vidím, že tabulka má anglické i české výrazy. Velcí dodavatelé to dělají v angličtině a pochybuji, že by to takto česko-anglicky dělala také česká firma pro domácí odběratele.
     Z Vašeho prvního příspěvku :

Eddie60 napsal(a)

"Dobrý den, zkouším dohledávat data pomocí funkce VLOOKUP v kombinaci s MATCH, SUBSTITUTE, VALUE ze 2 externích souborů.
Trvá to dlouho a při přepnutí do daného listu opět čekám, než se data zobrazí.
Vstupní data mají až 10 tisíc řádků a až 50 sloupců."


     Takže když taháte data ze dvou externích souborů dá se to vysvětlit například tak, že máte dva poslední ceníky. Odtud ta myšlenka, že něco porovnáváte. Je sice možné, že originální zdroj je rozdělen do dvou souborů, a proto se musí prohledat oba.
     Uvádíte : "Trvá to dlouho a při přepnutí do daného listu opět čekám, než se data zobrazí." Proč musíte přepínat nechápu - mají snad nabídkové seznamy vždy jiné uspořádání?
     Také uvádíte, že tabulka má 50 sloupců a v dalším příspěvku jich je už 176. Není to sice podstatné, ale vzbuzuje to pochybnosti. Má celý soubor 176 sloupců a dva sešity po 10 tisíc řádcích, tedy 20 000 řádků? - asi jde o záměnu externích souborů za externí listy (stejného sešitu)? Není to podstatné nekomentujte.


ad 2) Tím efektivním používáním vzorců jsem měl na mysli něco jiného. Konstrukce jsou složité. To už samo o sobě zpomaluje. Práci je možné rozdělit na jednoduché hledání v externím sešitě, zejména když jsou položky 1 - 10 unikátní.
a)     Když zadáte hledání jen v prvém sloupci například IFS(Url.sešit.list.A2 = "item 1";"item 1";Url.sešit.list.A2 = "item 2";"item 2";;;;;Url.sešit.list.A2 = "item 10";"item 10";0=0;"") a zkopírujete ho do celého sloupce A (musíte vědět, že začínat od řádku 2 a končit na posledním řádku zdroje). Dostanete Celkem prázdný sloupec A. Do tohoto poloprázdného sloupce (je zatím celý vybraný stisknete Ctrl+F4 (relativní dresy se změní na absolutní). Následně stále vybraný úsek ve sloupci A seřadíte. Nyní máte v buňkách A2 až A11 vzorce. Zbytek smažete (A12:A10000). Nyní změníte IFS() na =$Url.$sešit.$list.A$2 - tedy jen odkaz bez vzorce. A$2 musí mít relativní adresu (nesmí mít "dolary" před značkou sloupečku. Může být také jen A2), aby šlo kopírovat sloupce do dalších sloupců. Následně upravené vzorce z A2:A11 vykopírujeme do úseku B2:AC11, celý úsek načteme do paměti a vložíme zpět jen jako text a čísla - nakonec promažeme sloupce a opravíme čárky a tečky.
b)     Nejsnadnější způsob je v kopii přidat první sloupec do kterého napíšeme podobný vzorec, ale složený z místních adres asi takto : IFS(B2 = "item 1";"item 1";B2 = "item 2";"item 2";;;;;B2 = "item 10";"item 10";0=0;""). Vykopírujeme dokonce na řádku 10000. Ještě vybraný úsek A2:A1000 načteme do paměti a vložíme zpět jen jako text. Následně celý soubor A2:AD10000 seřadíme podle sloupce A. Item 1 až 11 máme na začátku ostatní řádky počínaje řádkem 12 smažeme. Nakonec smažeme nepotřebné sloupce včetně pomocného sloupce A, opravíme čárky a tečky - je hotovo.
c)     Pokud je soubor pro stroj opravdu těžký vložíme vzorce do přidaného sloupce A, vynecháme seřazení a začneme odspoda buňkou A10000 + Ctrl+Shift+šipka nahoru. Dostaneme se na první správný řádek - nejspíš "item 10". Takže následně krok výběru o jeden dolu aby hledaný řádek zůstal a zbytek smažeme jako řádky. Následně se přesuneme na další řádek nad "Item 10" a opět  Ctrl+Shift+šipka nahoru + 1 řádek dolů a smazat - získáme "item 9" ... To je postup který uvolní paměť stroje pokud by měl tendenci padat. Po každém takovém kroku uložit. Když poznáme, že stroj zvládá, setřídíme tak jak popisuji v odstavci výše.


ad 3) Ano udělat si šablonu, podle které vymažete nechtěné sloupce je dobrý nápad. Můžete však snadněji vložit řádek nad hlavičku kde budou označené sloupce přehlednější. Navíc můžete místo označení "AAA" zadat nejlépe čísla sloupců, nebo názvy sloupců, protože potom vyberete celý soubor Ctrl+End a Ctrl+Home, dáte seřadit dialogem z nabídky DATA > ŘADIT, vyberete možnost řadit sloupce (výběr možností dole zaškrtávacím tlačítkem výběru) podle řádku 1 a je to - stačí nechtěné smazat.


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

#5 7. 2. 2023 10:56:28

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 38

Re: Makro - Načtení externích dat - "VYŘEŠENO"

Nerozumíme si. Uváděná data jsou pouze ukázková. "Item" je druh materiálu. V našem systému máme cca 5 000 druhů materiálu, 2 hlavní dodavatele a různé zákazníky. Neřešte, prosím, "anglické i české výrazy". Dnes komunikujeme s některými zahraničnímu firmami v češtině a naopak v angličtině se zahraničními.
1-2 týdně stahuji aktuální informace z webu na základě poptávek. Je nás více a v celkovém součtu ztrácíme zbytečně čas. Proto jsem uvažoval o makru.
Ve zdrojovém souboru se nemění hlavička, ale obsah dat podle požadavku. Položka "Item 1" je sice jedinečná, ale to neznamená, že ve zdojových datech je vždy. Použití funkce IFS je v tomto případě nepoužitelné.
Napadlo mě toto řešení na základě ad 3):
Udělám si šablonu, kde budou 2 řádky s čísly pro seřazení sloupců (v obou bude stejný počet čísel, jen pořadí jiné, aby sloupce, kde potřebuji upravit formát dat byly v matici) a do listu "Dodavatel" zkopíruji vstupní data a vložím 2 řádky s čísly. Zbytek by snad šlo řešit makrem v listu "Dodavatel":
1) seřadit sloupce podle řádku č. 1
2) zjistit počet neprázdných buděk v A4:A5000 (= např. "pocet_PN")
3) opravit formát buněk v matici B4:K"pocet_PN" na formát "číslo", záměna "," za ".", např. nyní 2,500 (výsledek 2500), formát buňky je text
4) opravit čísla v matici M4:V"pocet_PN" - záměna tečky za čárku, např. cena = 0.08536 (výsledek 0,08536)
5) vymazat řádek č. 1
6) seřadit sloupce podle řádku č. 1 (původně č. 2)
Dle tohoto postupu si můžu nahrát jednotlivá makra a pak je spojit. To ale nebude ideální.
Pomůžete mi, prosím?  Opravdu nám to ušetří to čas.
Pokud ano, upravím testovací data.
Děkuji.

Editoval Eddie60 (7. 2. 2023 17:27:26)

Offline

#6 7. 2. 2023 18:15:56

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

Re: Makro - Načtení externích dat - "VYŘEŠENO"

Pane Kristek já můžu napsat makra pro přizpůsobení, ale komplex maker s požadovaným výsledkem musím ověřovat, respektive ladit. Jinak si budeme hodně dopisovat tady přes fórum. To co uvádím pro vzorce jsou postupy, které platí více - či méně také pro makra. Například řazení sloupců je lepší upravovaným nahraným makrem, nežli klasicky psaným.
     Pokud tedy nemám úplně jasno nepouštím se do toho. Je mi jasné, že "item" je obecný pojem položka a předpokládám, že pro Vaše účely má tato konkrétní jiné označení. Nevím zda jste testoval funkci IFS. Ta totiž značně zjednodušuje zanořované funkce IF, nebo funkce OR a vzorec může pojmout mnoho různých dotazů - mnohem více nežli skutečně vnořené funkce IF, nebo OR. Objem vzorce, tedy počet argumentů je limitován 1kB, i když i to je už dnes asi více. IFS hledá od prvního dotazu po poslední, takže když konkrétně "item 1" nenajde, tak ho přeskočí a testuje další. Když v buňce nenajde žádný z hledaných výrazů zanechá dík poslední podmínce 0=0;"" prázdnou buňku a skončí tak "bez chyby" - funguje jako filtr. Proč o tom píšu - myslím, že Váš popis možného postupu akceptuje právě takovéhle "odfiltrování" :     

Eddie60 napsal(a)

1) seřadit sloupce podle řádku č. 1
2) zjistit počet neprázdných buněk v A3:A5000 (= např. "pocet_PN")

V jiném případě není důvod zjišťovat počet neprázdných buněk - stačí zjistit poslední buňku ve sloupci A, který by neměl obsahovat řídké záznamy tak jako jiné sloupce.


Také není jasné co znamená  formát buněk v matici B3:K. To by mělo být asi o matici B3:K5000. To znamená 11 sloupců v celkové matice A3:K5000.


Pokud by to mělo být makrem, tak zřejmě načteme pro odlehčení jen chtěné sloupce (které bychom řadili podle 1. řádku) a nad nimi jen pořadí podle 2. řádku. Zkopírování sloupců zpřeházeně se zadá do cyklu, který je nasází za sebe. Nad nimi pro závěrečně řazení číslo finálního výstupu.
     Také je možné postupovat jinak - smazat makrem všechny nechtěné, zanechat původní pořadí, ale opravit čárky a tečky v konkrétních sloupcích (po smazání nechtěných řádků). Je jen otázkou co by bylo rychlejší a to znamená zkoušet - tedy ladit makro a postupy.
     Následné promazání řádků může být pomocným sloupcem a řazením, nebo mazáním řádků, které bývá pomalejší, ale není potřeba pomocný sloupec, jen se vyberou "selectem" nechtěné řádky, které se pak naráz smažou. Což by se mělo také otestovat.
     Do toho ladění patří také test zda je rychlejší importovat ze zdroje jen chtěné sloupce a nebo naopak nakopírovat všechny sloupce a ty promazat.


     Chápu nechuť postoupit ostrá data - osobně doporučuji postnout simulovaný problém, ale ten musí vydat všechny informace o rozsahu, formátech, "špecích" a požadavcích výstupu. Pokud byste měl obavy postnout data veřejně, pošlete to na můj email (je dole pod tímto textem).
     V některých případech umím rychle vytvořit soubory s velkými rozsahy, ale já mám víc práce a když by něco autor doplnil dodatečně (zapomenul zmínit, nevšiml si ap.) asi bych se na to vykašlal. Zažil jsem to už mockrát.


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 8. 2. 2023 17:26:20

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 38

Re: Makro - Načtení externích dat - "VYŘEŠENO"

Poslal jsem soubor na email.

Offline

#8 1. 4. 2023 12:13:04

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 38

Re: Makro - Načtení externích dat - "VYŘEŠENO"

Dobrý den,

rád bych Vám poděkoval za řešení tohoto problému, a zejména za ochotu a trpělivost při procesu ladění méně zkušenému uživateli LibreOffice, který se snaží zefektivnit nejen svoji práci, ale i svých kolegů/kolegyň.
     
Stále se mi potvrzuje, že člověk se učí celý život, pokud si nechce nechat ujet vlak.
     
Ještě jednou vřelé díky za profesionální přístup. :) =) :)

Editoval Eddie60 (1. 4. 2023 12:15:00)

Offline

Zápatí