MS Excel

Dobrý den,
píši Vám ohledně pomoci s excelem, protože si již nevím rady a zase v těch makrech nejsem tak zběhlý. Dopředu říkám, že to nechci zadarmo, stačí mi napsat (checkboy@gmail.com), jestli byste to byli schopni a ochotni udělat a za kolik, já bych se případně ještě poradil a když bych Vám to odsouhlasil, tak není problém.

Potřeboval bych udělat něco jako rozšířený filtr, kde se jedná především o to, že potřebuji vyfiltrovat dle všech zadaných klasifikací a kritérií všechny firmy, které tyto klasifikace a kritéria obsahují -
(klasifikací můžou být i stovky.. tisíce, rozdělují se na dvoumístné např. 65, pětimístné např. 65236 a sedmimístné například 6523601) + jestli se jedná např. o výrobce, distributora, servis, importéra a exportéra (ale může být i jen samostatně klasifikace, což by byly všechny firmy, které mají uvedenou klasifikaci popř. její „podklasifikaci“ a nebo naopak např. můžu chtít jen a pouze výrobce bez zadání další klasifikace). Tyto údaje mohou být i případně propojené, že budu chtít dle zadané klasifikace vyfiltrovat výrobce + distributory, kteří jsou zároveň exportéry.

Klasifikace jsou pojaty tak, že dvoumístná klasifikace má pod sebou všechny pětimístné klasifikace a zároveň sedmimístné klasifikace, které začínají stejně – viz např. 65, 65236, 6523601. Pětimístná
klasifikace má pod sebou všechny sedmimístné klasifikace. Takže pokud zadám dvoumístnou klasifikaci, tak zároveň mi musí vypadnout všechny firmy, kterým začíná jakákoliv klasifikace tímto dvoumístným číslem. Stejné to je i v případě pětimístné klasifikace, kde když se zadá pětimístná, tak vypadnou všechny firmy, které mají zadanou buď již tu pětimístnou klasifikaci a nebo všechny sedmimístné klasifikace, které začínají na prvních pěti místech stejnými čísly.

Dvoumístné (všeobecné) klasifikace, tak těch je nejméně (cca do 100). Pětimístné klasifikace, tak těch je cca 10.000 a sedmimístných klasifikací jsou statisíce až miliony. Každá firma má několik klasifikací (firma, resp. Její ID je ve sloupečkách „CompanyId“). Dohromady to je tolik klasifikací, že by to jeden list excelu prostě nepojmul (je jich necelé dva miliony) a list pobere něco přes jeden milion, takže abych nemusel používat access, tak jsem klasifikace rozdělil na dva listy. V tom může být malý zádrhel, protože já bych potřeboval, aby to makro zvládalo vybrat všechny firmy (ID firem) dle zadané klasifikace + bych potřeboval, aby se tam firmy (ID firem) neduplikovali, ale aby tam dle klasifikace bylo to ID firem jen jednou, takže pokud už bylo při vybrané klasifikace dříve vybráno, tak aby se tam znovu nepřidávalo do výsledku vyhledávání.

V příloze zasílám ukázkový soubor s několika vybranými klasifikacemi na listech (Klasifikace1 a Klasifikace2). Pokud se Vám to bude hodit, rozdělil jsem je ještě na dvoumístné, pětimístné a sedmimístné. Potřeboval bych, aby se s těmi listy moc již nehýbalo, protože se v budoucnu budou také obnovovat. Na listu „Zadání“ najdete dané sloupečky, dle kterých jsme byli zvyklí takto vyhledávat –
klasifikační kód (dvou, pěti nebo sedmimístný), výrobce (označení „P“ jako Producer), Distributor (D), Servis (S), Exportér (E), Importér (I)… tady mi nezáleží vyloženě na tom, jestli tam budu dávat tyto
písmenka a nebo jen např. „A“ ..jako Ano, že je tato klasifikace brána v úvahu. Ve sloupečku G by měl být k nalezení výsledek – ID firem, který se tam promítne ze sloupečků „D“ (CompanyId) na listech
s klasifikacemi.

Pokud budete mít jakékoli otázky tak mne prosím kontaktujte buď tady a nebo na e-mailu: checkboy@gmail.com .

Nezapomeňte, že se tu pak bude pracovat s hrozně moc daty a na každém listě bude až milion klasifikací. Děkuji moc za případnou budoucí pomoc.

PS: obyčejné filtry v hlavní liště nepojmou tolik sedmimístných klasifikací k profiltrování.

Zaslat odpověď >

Strana: « předchozí 1 2 3 4 další »

AL(31.8.2016 17:46)#032547

@xlnc
Petře, čo konkrétne Ti vadí na aktualizácii dát v databáze (Access) z Excelu? Robím to pomerne bežne cez ADO a zatiaľ som na problém nenarazil. Tak ma zaujíma, čoho by som sa mal prípadne vyvarovať..citovat

xlnc(31.8.2016 18:44)#032548

@AL
Vlastně dobrá otázka, musel jsem hlouběji do své hlavy :-) Pravděpodobně je to trošku paranoia a nic konkrétního, asi proto, že nevěřím Excelu v importu a exportu dat, když svým způsobem neřídím datové typy. Je to prostě můj osobní hist(o/e)rický pocit. Nevěřím Excelu, když má vytvořit tabulku v databázi ani updatu, prostě od UPDATE po INSERT INTO. Možná je na čase to překonat. Je pravda, že i při využití nahrávání dat do databází nějakým importním nástrojem v "manažeru" jsem si taky užil svoje se zdroji v Excelu, CSV, kódováním atp. Ale nejsem v tomhle databázový mistr, abych Vám něco radil.citovat

eLCHa(31.8.2016 20:43)#032550

Mno, teď jen teoreticky. Pokud se bavíme o balíčku office, tak když chci obsluhovat tabulku access z excelu pomocí VBA, prostě použiju referenci na access (createobject)

Pokud se bavíme o tomto konkrétním případě, tak k tomu nevidím důvod
jen bych potřeboval případně do té databáze jednou za čas přijít a celou jí zaktualizovat.
Jednou za čas chápu tak, že to udělá klidně ručně v accessu a pak to bude nějakou dobu platit.
Tzn v excelu jen definovat dotaz na tabulkucitovat

AL(31.8.2016 22:05)#032551

@xlnc
Ďakujem za odpoveď. Ja to riešim tak, že importujem do pomocnej tabuľky, nad ňou následne spustím nejakú sériu dotazov na príslušné kontroly. Všetko z Excelu cez VBA. Očakávaným výsledkom dotazu na výskyt napr. hodnôt NULL má byť prázdny recordset. Pokiaľ recordset nie je prázdny (count >0), tak viem, že nejaká hodnota sa nenaimportovala. V takom prípade obsah pomocnej tabuľky zmažem cez DELETE * FROM tblTemp. Makro zapíše niekam do logu, že vstupná tabuľka z Excelu nebola spracovaná z toho a toho dôvodu. Pokiaľ kontroly na konzistenciu a prípustnosť dát prebehnú korektne, VBA spustí aktualizačný, pridávací atd. dotaz nad hlavnou tabuľkou (tabuľkami) z pomocnej tabuľky a následne obsah pomocnej tabuľky vymaže.

@eLCHa
používam radšej early binding, nie som úplne kovaný v syntaxi jednotlivých príkazov a intellisense mi potom uľahčuje život

ad: Tzn v excelu jen definovat dotaz na tabulku
Alebo vytvoriť parametrický dotaz priamo v tom Accesse a len parametre načítať priamo z Excelu.
V prípade jednoduchých dotazov by mohlo stačiť i MS Query, to parametrické dotazy zvláda bez problémov tiež.citovat

xlnc(31.8.2016 22:29)#032552

@AL
Mno, mám živě před očima, jak uživatelé Accessu, kterým jsem ukazoval MS Query, kroutili hlavami nad tou sr... s tím, že to si radši pohledy sestaví v Accessu. MS Query/MS Jet je strašný historický šrot, navíc nějakým p.tomcem v "návrháři" přeložený do češtiny i s jazykem SQL... Do toho IMEX, v Excelu 2010 nefungující import hodnot typu čas a pořádně neexistující nápověda, prapodivně se do jazyka promítající funkce jakoby z VBA, prakticky nemožné řízení datových typů sloupců (ještěže je možná primitivní konverze v dotazu), praštěné používání uvozovek, Excel kašle na Tabulky... ADO apod. ve VBA taky neřeší všechno, překlápění Null do listu, ořezaná SQL syntaxe, ... trošku je situace lepší s příchodem "Power" nástrojů, ale...citovat

AL(31.8.2016 23:50)#032553

Petře, MS Query nie je určite bez chýb, ale je zadarmo a prakticky bez nutnosti nejakej zložitej inštalácie. Obávam sa, že bez Tvojej pomoci by som MS SQL server express korektne nainštalovať nedokázal. Na MS Query sa mi páči hlavne tá možnosť parametrických dotazov, kedy dotaz načíta parameter z bunky v Exceli a pri zmene jej hodnoty prebehne aktuaizácia dotazu - bez nutnosti čokoľvek programovať a pod. Pár vecí som s MS Query spáchal a ten nástroj nie je úplne špatný. Samozrejme, na Access to nemá, ale ani Access nie je bez múch. Mne na 2016 napr. strašne vadí, že objekty v návrhovom zobrazení majú tak tenký okraj (hranu), že je skutočne problematické hranu uchopiť myškou a zmeniť veľkosť objektu.
Ešte technická: Mám za to, že pohľady v Access nie sú nazývané pohľadmi (views), ale dotazmi (queries). Pohľady má napr. MS SQL server. V tej terminológii je trochu guláš.citovat

eLCHa(1.9.2016 6:29)#032555

@AL
používam radšej early binding, nie som úplne kovaný v syntaxi jednotlivých príkazov a intellisense mi potom uľahčuje život

Já taky. Proto si ten kód napíšu přímo v aplikaci, kterou volám, odladím a teprve pak přenesu do aplikace, kde bude spuštěn. Pokud to není možné (nemá VBA), tak vytvořím referenci a po odladění odstraním. Proč? Protože, když to pak přenesu do jiného PC a ta aplikace tam není, nefunguje ve VBA ani to, co na ní není závislé. Prostě se v maximální míře vyhýbám referencím.

Ono ani vytvoření dotazů v Accessu s Excelem moc nespolupracuje. Jednoduché dotazy si excel najde, ale jakmile použiju vnořený dotaz, už ho v seznamu nemám a musím z něj vytvořit tabulku.

Osobně taky zatím dávám přednost MSQuery před PowerQuery. Za prvé jsem ještě neměl čas se v PQ hlouběji hrabat (kdyby někdo měl odkaz na nějaké dobré stánky, tak sem s nima) a za druhé mi to při každém zavření (Excel 2010) hodí hlášku "Excel provedl neplatnou operaci..." a taky jsem to ještě neměl čas řešit (nepoužívám, tak to nespěchá).citovat

adaal(1.9.2016 12:26)#032557

Dobrý den,

takové množství dat rozhodně do excelu nepatří, přeci jenom je to kalkulátor a nikoli databáze. Čím dříve opustíte tuto myšlenku, tím lépe. Každopádně si myslím, že je to práce pro IT. V kostce:

Zpracování většího (>300 000ř) množství dat v MS Excel

1. Rozšířený filtr - omezené možnosti a špatný výkon
2. Dotazování nad pracovním listem pomocí nástrojů (MS Query, PowerQuery) - vyhnout se kvůli určování datových typů
3. VBA a dotazování pomocí ADO nad pracovním listem - vyhnout se kvůli určování datových typů
4. VBA (Array, Collection, Dictionary) - slušně použitelné zhruba do 300t řádků a 20 sloupců

Zpracování většího (>300 000ř) množství dat v MS Access

1. Včetně rozhraní (forms) a použití bez excelu
Odpadne aktualizace z excelu,
nutné řešit distribuci - instalace drahého accessu nebo runtime mód,
možnost to je, ale je nutné zvážit počet uživatelů a zátěž na souborový server,
pohrát si s lock managementem,
omezené možnosti základníhoSQL ANSI92,
rozdíly v jednotlivých verzích,
DAO i ADO je zastaralá omezující, neoptimalizovaná technologie

2. MS Access pouze jako uložiště dat
a aktualizace dat z pracovního listu excelu pomocí DAO nebo ADO - znovu problém s určováním datového typu, v praxi se vytvoří z listu flat soubor, který se pomocí průvodce připojením, připojí do accessu a určí se datové typy, ale je to znovu slušně použitelní tak do 500 000 řádků, a opět:
nutné zvážit počet uživatelů a zátěž na souborový server,
pohrát si s lock managementem,
omezené možnosti základního SQL ANSI92,
rozdíly v jednotlivých verzích,
DAO i ADO je zastaralá omezující, neoptimalizovaná technologie

3. MS Excel jako klient + plnohodnotná databáze jako backend
nejlépe mít databázi na serveru nikoli lokálně,
potřebné znalosti IT,
lze snadno projektovat data do excelu,
CRUD operace třeba v rámci zkompilovaných uložených procedur,
ve větší míře odpadá práce s uživatelský rozhraním,
určit potřeby a podle toho vybrat OLTP nebo OLAP řešení

4. Desktopová nebo webová aplikace jako klient + plnohodnotná databáze jako backend

5. OPENXML - můžete použít různé knihovny pro různé jazyky, ale ta databáze bude stejně asi potřeba.

Předejte práci IT, pokud máte možnost nebo začněte rychle studovat :) Na tento úkol se MS Office dost dobře nehodí.citovat

adaal(1.9.2016 12:30)#032558

Ještě oprava ať to dává smysl: V mém příspěvku si, prosím, odmyslete číslice 3. 4. 5., (text nikoli)citovat

xlnc(1.9.2016 14:09)#032559

@AL
Jo dobře, z pohledu pojmů máte asi pravdu, já nejsem žádný databázový expert. Co potřebuju, si udělám, asi tak. Tak holt zjistím, že existuje manažer pro daný typ databáze, že syntaxe je taková a maková pro dotazy, že Visual Studio pracuje s datasety a nějak si to (ne vždy kulturně) zplácám. V tomhle rozhodně mistr světa nejsem a věřím, že v tuto chvíli toho víte i víc.

Jinak je to na hodiny diskusí.

České CSV není taky CSV v pravém slova smyslu, ono vlastně CSV ani definici pořádně nemá a pak každý myslí, že je to sranda vyplivnout vícestránkový texťák z mašiny s hlavičkami, stránkováním, grafickými serepetičkami, a chtít import do Excelu... A když pak vidíte na jedné straně Power (a všechno je Power, jako všichni jsme Gogo), a na straně druhé místo opravených chyb ptákoviny typu Import dat z Facebooku, tak co si myslet...citovat

Strana: « předchozí 1 2 3 4 další »

Téma: Makro vzorec - Finanční odměna za vytvoření

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje