Odskočil jsem se z hospůdky na vzduch, tak omluvte tu gramatiku :)citovat
excel, návod, tipy, funkce, makra, vba, tabulky, šablony
Váš průvodce programem Microsoft Excel a programováním maker ve VBA
< návrat zpět
Téma: Makro vzorec - Finanční odměna za vytvoření
Jako takove zakladni seznameni muze dobre poslouzit tato stranka:
https://www.excelcampus.com/powerquery/power-query-overview/
Co se konkretnich postupu a ukazek tyce, vyborna je tato video serie:
https://www.youtube.com/watch?v=VeS0fh8xUVA&index=2&list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiKcitovat
MS Excel
Téma: Makro vzorec - Finanční odměna za vytvoření 
Zaslal/a checkboy 31.8.2016 13:36
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í.
Příloha: 
32512_excel.zip (239kB, staženo 38x)
32512_excel.zip (239kB, staženo 38x)
Strana: « předchozí 1
2
3
4
(2.9.2016 21:05)#032611
zdravim,
zde je alespon jeden pokus, ale pomoci userform. Pak kdyz jsem chvili koukal na list Zadani, tak to asi chcete tak, ze makro proje vsechny kriteria a postupne do sloupce G vypise vysledek, ze?
No zkuste, tak jak je, zda to vubec funguje (na tech vasich 1000000 radcich), snad se to podoba tomu, ceho chcete dosahnout.
Uvidime
syd
zde je alespon jeden pokus, ale pomoci userform. Pak kdyz jsem chvili koukal na list Zadani, tak to asi chcete tak, ze makro proje vsechny kriteria a postupne do sloupce G vypise vysledek, ze?
No zkuste, tak jak je, zda to vubec funguje (na tech vasich 1000000 radcich), snad se to podoba tomu, ceho chcete dosahnout.
Uvidime
syd
Příloha: 32614_klasifikace-ukazka-syd.zip (43kB, staženo 32x)
citovat32614_klasifikace-ukazka-syd.zip (43kB, staženo 32x)(3.9.2016 14:37)#032620
No, pokud byste shledal všechny možné postupy v rámci excelu jako nedostačující, je pro Vás - asi jako druhá další schůdná varianta - použít MS Access jako tupé uložiště dat, případně i dotazů a zbytek řídit pomocí VBA kódu z Excelu - a tím si odlehčit.
Teda, za předpokladu, že máte k dispozici MS Access (2010, 2013, možná i 2016) a třeba souborový server. Také základy SQL.
Připravil jsem pro Vás základní ukázku, jakým způsobem by se to dalo využít. Pokud o to nemáte zájem, nemá smysl dále číst :)
Řešení využívá pozdní vazbu v rámci DAO knihovny.
Postup jak to rozchodit:
1. Vytvořte si databázi MS Access v nějaké složce a pojmenujte jí source_data.accdb
2. Vytvořte v databázi jednoduchou tabulku s názvem tableName a naplňte jí trochou nějakých dat. Tabulku uložte
3. Databázi uzavřete a otevřete si soubor z přílohy
4. Do listu s názvem Setting buňky B2 vložte cestu ke složce, kde jste databázi vytvořil
5. Spusťte makro s názvem Reresh
Vámi vytvořená data by se měla všechna natáhnout z databáze do listu source_data.
Soubor, který jsem vytvořil, obsahuje modul s názvem cls_MSAccessFile
Tento modul nabízí mj. metodu RecordsetToActiveWBook pro vložení sady záznamu do mřížky excelu předmětného listu aktivního sešitu. Další metoda RecordsetToNewWBook dělá téměř totéž, ale vytváří sešit nový.
V modulu je i metoda ExecuteActionQueries pomocí které můžete volat z excelu akční dotazy uložené v databázi MS Access, pro CUD operace (vytváření, aktualizaci a mazání).
Způsob použití modulu třídy cls_MSAccessFile vidíte ve funkci RefreshData() v modulu mdl_Refresh.
Pokud byste chtěl použít toto řešení, můžete si zkusit Vaše data naimportovat do nové tabulky v MS Access a vytvořit vlastní dotaz v modulu mdl_SQL a název tohoto dotazu předat ve funkci RefreshData metodě RecordsetToActiveWBook, která se postará o vložení dat do aktivniho sešitu. Dotazy můžete vytvořit a uložit i v accessu a v modulu mdl_SQL vytvořit dotaz ve tvaru SELECT sloupce FROM NazevDotazuUlozenyVAccessu.
Takto si můžete testovat do jaké míry je výkon přijatelný a při jak složitém dotazu. Základy SQL se naučíte za pár dní. Prostě filtrovat a vkládat data pomocí SQL dotazu, procedury se postarají o import dat z accessu do excelu.
Pokud by se řešení rozrostlo, bylo by ještě poměrně udržovatelné, dokumentace k tomu je v kódu.
Parametry můžete sbírat od uživatelů v excelu a předávat je do řetězce sql, který se odešle do accessu.
Řešení dávám zdarma, tak jak je, bez podpory a na vlastní nebezpečí, ikdyž nepředpokládám, že byste ho použil.
Změny v názvu cesty, názvu databáze a další se dají upravit v modulu mdl_Globals v jednotlivých konstantách.
Metody v modulu jsou poměrně dost parametrizované, tak můžete prozkoumat jejich možné využití.
Snad je to takž takž provozu schopné řešení.
Teda, za předpokladu, že máte k dispozici MS Access (2010, 2013, možná i 2016) a třeba souborový server. Také základy SQL.
Připravil jsem pro Vás základní ukázku, jakým způsobem by se to dalo využít. Pokud o to nemáte zájem, nemá smysl dále číst :)
Řešení využívá pozdní vazbu v rámci DAO knihovny.
Postup jak to rozchodit:
1. Vytvořte si databázi MS Access v nějaké složce a pojmenujte jí source_data.accdb
2. Vytvořte v databázi jednoduchou tabulku s názvem tableName a naplňte jí trochou nějakých dat. Tabulku uložte
3. Databázi uzavřete a otevřete si soubor z přílohy
4. Do listu s názvem Setting buňky B2 vložte cestu ke složce, kde jste databázi vytvořil
5. Spusťte makro s názvem Reresh
Vámi vytvořená data by se měla všechna natáhnout z databáze do listu source_data.
Soubor, který jsem vytvořil, obsahuje modul s názvem cls_MSAccessFile
Tento modul nabízí mj. metodu RecordsetToActiveWBook pro vložení sady záznamu do mřížky excelu předmětného listu aktivního sešitu. Další metoda RecordsetToNewWBook dělá téměř totéž, ale vytváří sešit nový.
V modulu je i metoda ExecuteActionQueries pomocí které můžete volat z excelu akční dotazy uložené v databázi MS Access, pro CUD operace (vytváření, aktualizaci a mazání).
Způsob použití modulu třídy cls_MSAccessFile vidíte ve funkci RefreshData() v modulu mdl_Refresh.
Pokud byste chtěl použít toto řešení, můžete si zkusit Vaše data naimportovat do nové tabulky v MS Access a vytvořit vlastní dotaz v modulu mdl_SQL a název tohoto dotazu předat ve funkci RefreshData metodě RecordsetToActiveWBook, která se postará o vložení dat do aktivniho sešitu. Dotazy můžete vytvořit a uložit i v accessu a v modulu mdl_SQL vytvořit dotaz ve tvaru SELECT sloupce FROM NazevDotazuUlozenyVAccessu.
Takto si můžete testovat do jaké míry je výkon přijatelný a při jak složitém dotazu. Základy SQL se naučíte za pár dní. Prostě filtrovat a vkládat data pomocí SQL dotazu, procedury se postarají o import dat z accessu do excelu.
Pokud by se řešení rozrostlo, bylo by ještě poměrně udržovatelné, dokumentace k tomu je v kódu.
Parametry můžete sbírat od uživatelů v excelu a předávat je do řetězce sql, který se odešle do accessu.
Řešení dávám zdarma, tak jak je, bez podpory a na vlastní nebezpečí, ikdyž nepředpokládám, že byste ho použil.
Změny v názvu cesty, názvu databáze a další se dají upravit v modulu mdl_Globals v jednotlivých konstantách.
Metody v modulu jsou poměrně dost parametrizované, tak můžete prozkoumat jejich možné využití.
Snad je to takž takž provozu schopné řešení.
Příloha: 32620_accesszexcelu.zip (58kB, staženo 31x)
citovat32620_accesszexcelu.zip (58kB, staženo 31x)eLCHa napsal/a:
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á).
Jako takove zakladni seznameni muze dobre poslouzit tato stranka:
https://www.excelcampus.com/powerquery/power-query-overview/
Co se konkretnich postupu a ukazek tyce, vyborna je tato video serie:
https://www.youtube.com/watch?v=VeS0fh8xUVA&index=2&list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiKcitovat
(3.9.2016 21:50)#032629
Dobré články. Taky raději upřednostňuji PowerQuery před MS Query. Je to silný a optimalizovaný nástroj s velkou podporou zdrojů.
Varuji ale před zneužíváním excelu jako databankou, kvůli možnostem power nástrojů. Data o kterých ve vlákně jde, bych už v excelu neskladoval.
Uvědomte si, že jejich primární účel je využit excel jako klienta v rámci BI.citovat
Varuji ale před zneužíváním excelu jako databankou, kvůli možnostem power nástrojů. Data o kterých ve vlákně jde, bych už v excelu neskladoval.
Uvědomte si, že jejich primární účel je využit excel jako klienta v rámci BI.citovat
Zdravím Vás,
tak již se mi do rukou dostal reálný funkční návrh toho, co jsem chtěl (kontaktoval mne s ním jeden profesor, který vyučuje excel - jemu taky bude putovat finanční odměna za vytvoření). Vyhledá to v excelu dle zadaných kritérií všechny ID firem, které má. Je fakt, že to chvíli trvá, ale jedná se o minuty až desetiminuty, což není zase tak hrozné. Návrhům od syda a daala děkuji.
Jinak k tomu návrhu syda, tak záměr byl dobrý, jen já bych právě potřeboval zadávat více klasifikací najednou, dost často i desítky, takže zadávat to po jedné a kopírovat vždy ID někam bokem a pak provést např. reduplikaci všech ID by byla hodně zdlouhavá.. popravdě někam sem jsem se tehdy dostal i já. Ale pokud by jsi to třeba chtěl dodělat a bylo by to rychlejší než doposud od profesora, tak bych určitě byl pro tvou verzi a finančně bych jí i ocenil. Plus mi u té tvé verze vyskakovala chybová hláška "Run-time error 438".
Verzi přes access.. ještě jsem jí popravdě nezkusil, ještě se k ní vrátím a zkusím jí vyzkoušet, ale jak jsem říkal, chtěl jsem spíše verzi v excelu, aby to mohly kolegové taky používat.
Každopádně všem zúčastněným moc děkuji za spolupráci a vaše návrhy a řešení. Jsem rád, že existuje takovéto forum. Je možné, že budeme do budoucna potřebovat poradit ještě v dalších otázkách excelu. Tak bych Vás případně kontaktoval.
citovat
tak již se mi do rukou dostal reálný funkční návrh toho, co jsem chtěl (kontaktoval mne s ním jeden profesor, který vyučuje excel - jemu taky bude putovat finanční odměna za vytvoření). Vyhledá to v excelu dle zadaných kritérií všechny ID firem, které má. Je fakt, že to chvíli trvá, ale jedná se o minuty až desetiminuty, což není zase tak hrozné. Návrhům od syda a daala děkuji.
Jinak k tomu návrhu syda, tak záměr byl dobrý, jen já bych právě potřeboval zadávat více klasifikací najednou, dost často i desítky, takže zadávat to po jedné a kopírovat vždy ID někam bokem a pak provést např. reduplikaci všech ID by byla hodně zdlouhavá.. popravdě někam sem jsem se tehdy dostal i já. Ale pokud by jsi to třeba chtěl dodělat a bylo by to rychlejší než doposud od profesora, tak bych určitě byl pro tvou verzi a finančně bych jí i ocenil. Plus mi u té tvé verze vyskakovala chybová hláška "Run-time error 438".
Verzi přes access.. ještě jsem jí popravdě nezkusil, ještě se k ní vrátím a zkusím jí vyzkoušet, ale jak jsem říkal, chtěl jsem spíše verzi v excelu, aby to mohly kolegové taky používat.
Každopádně všem zúčastněným moc děkuji za spolupráci a vaše návrhy a řešení. Jsem rád, že existuje takovéto forum. Je možné, že budeme do budoucna potřebovat poradit ještě v dalších otázkách excelu. Tak bych Vás případně kontaktoval.
citovatZdravim,
zde verze bez formulare. Vse co je v oblasti A2:F az posledni radek klasifikacniho kodu (zde by to urcite chtelo osetrit, aby clovek nepsal do bunek kraviny) na listu Zadani je filtrovano v ostanich dvou listech a vysledek vlozen do sloupce G2 a nize.
Radne ostestovat, zda dava spravny vysledek.
syd
zde verze bez formulare. Vse co je v oblasti A2:F az posledni radek klasifikacniho kodu (zde by to urcite chtelo osetrit, aby clovek nepsal do bunek kraviny) na listu Zadani je filtrovano v ostanich dvou listech a vysledek vlozen do sloupce G2 a nize.
Radne ostestovat, zda dava spravny vysledek.
syd
Příloha: 32658_klasifikace-ukazka-syd2.zip (40kB, staženo 31x)
citovat32658_klasifikace-ukazka-syd2.zip (40kB, staženo 31x)Zdravím syde,
napsal jsem vám e-mail, tak se prosím podívejte do své schránky a dejte mi případně vědět. Děkuji.
citovat
napsal jsem vám e-mail, tak se prosím podívejte do své schránky a dejte mi případně vědět. Děkuji.
syd napsal/a:
Zdravim,
zde verze bez formulare. Vse co je v oblasti A2:F az posledni radek klasifikacniho kodu (zde by to urcite chtelo osetrit, aby clovek nepsal do bunek kraviny) na listu Zadani je filtrovano v ostanich dvou listech a vysledek vlozen do sloupce G2 a nize.
Radne ostestovat, zda dava spravny vysledek.
sydPříloha: 32658_klasifikace-ukazka-syd2.zip
Strana: « předchozí 1
2
3
4
Uživatelské menu
Nejste přihlášen(a)
\n
\nMenu
On-line nástroje
Formulář Faktura
Formulář Faktura IV
Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.
Aktivní diskuse
Čas od do
lubo • 19.4. 16:30
Makro smyčka
MilanKop • 19.4. 10:46
Makro smyčka
elninoslov • 19.4. 9:02
Čas od do
elninoslov • 19.4. 8:46
Čas od do
jarek1111 • 18.4. 13:46
Čas od do
lubo • 18.4. 11:13
Čas od do
jarek1111 • 18.4. 8:32
RSS nejnovější články