Koukal jsme na zdroj, není ideální ani z pohledu importu dat (Data / Z webu), ani pro parsování přes VBA. Čili zkraje by bylo dobré zkusit sehnat jiný zdroj.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: Extrakce a úprava dat
MS Excel
Téma: Extrakce a úprava dat 
Zaslal/a pavelo 15.3.2017 16:51
Zdravím komunitu.
V excelu vytvářím čtvrtletně seznam předpisů, které vyšly ve sbírce zákonů. Protože jsem zatím nepřišel na efektivnější způsob, mám pro ten účel udělané funkční řešení - excelovský sešit se 3 listy. Do prvního s názvem „data“ ručně kopíruju surová data z webové stránky. Jelikož tyto data nejsou zcela v použitelném tvaru, provádí se prostřednictvím dalších dvou listů jejich transformace do konečné podoby, která se pak tiskne. Jak jsem napsal, je to funkční a v podstatě to, s ohledem na poměrně malou četnost potřeby práce s tím, vyhovuje jak to je. Data v prvním listu však každý čtvrtrok přepíšu, takže se k nim později už nemůžu vrátit. Chci provést úpravu, že bych do prvního listu data vkládal a doplňoval postupně celý rok a pro zpracování a generování seznamu bych si odsud vytahoval jen zadaný rozsah předpisů, odpovídající požadovanému časovému období.
V listu „transformace“ jsou v buňkách F1 a G1, pro příklad, zapsána mezní čísla rozsahu předpisů od / do, na základě kterých by se měl vygenerovat na listu „tisková sestava“ seznam. V buňkách H1, I1 jsou vzorce pro určení řádků na listu „data“, kde se mezní předpisy nacházejí.
Prosím, pomůže mi s tím někdo?
Příloha: 
35549_p3-seznam-predpisu-nastroj_1.xlsx (237kB, staženo 48x)
35549_p3-seznam-predpisu-nastroj_1.xlsx (237kB, staženo 48x)(15.3.2017 17:35)#035550
Keď to máte takto funkčné a zažité, tak nové dáta vkladajte pod staré, natiahnite si vzorze v transformačnom liste, aj v tlačovom, a v tlačovom si dajte v hornom riadku filter, kde si zvolíte obdobie aké chcete. Takt by sa to dalo poriešiť podľa mňa ľahko...citovat
Možno by nebolo od veci skúsiť tabuľky [na prenos vzorcov do ďalších riadkov] a kontingenčné tabuľky [na počítanie vzhľad a jednotné ovládanie] nasledovne:
- načítať obsah stránky pomocou prepojenia na stránku noviniek (údaje načítať zo stránky [tým by ste mali dostať prenos údajov na list] na tomto liste stačí byť na začiatku umiestnenia údajov a dať ich refresh, alebo je možné nastaviť ich aktualizáciu pri otvorení súboru;
- preniesť (vložiť ako hodnoty) vybraté údaje do listu data, ktorom je vhodné zadefinovať si tabuľku [dodatočne je možné preniesť aj staré údaje].. na koniec je možné doplniť vlastné vypočítavené stĺpce, ktoré môžu mať význam (byť použité) v kt;
{na načítanie a doplnenie do tabuľky sa dá použiť od verzie 2010 PowerQuery (ako doplnok) a v 2016 je už obsiahnutý}
- z vyskladanej tabuľky data vytvoriť kontingenčnú tabuľku (alebo viac kont. tabuliek rozmiestnených po listoch) s možnosťou nadefinovať rýchle filtre (môžu byť spoločné na dátumy a iné podmienky pre všetky kt)citovat
- načítať obsah stránky pomocou prepojenia na stránku noviniek (údaje načítať zo stránky [tým by ste mali dostať prenos údajov na list] na tomto liste stačí byť na začiatku umiestnenia údajov a dať ich refresh, alebo je možné nastaviť ich aktualizáciu pri otvorení súboru;
- preniesť (vložiť ako hodnoty) vybraté údaje do listu data, ktorom je vhodné zadefinovať si tabuľku [dodatočne je možné preniesť aj staré údaje].. na koniec je možné doplniť vlastné vypočítavené stĺpce, ktoré môžu mať význam (byť použité) v kt;
{na načítanie a doplnenie do tabuľky sa dá použiť od verzie 2010 PowerQuery (ako doplnok) a v 2016 je už obsiahnutý}
- z vyskladanej tabuľky data vytvoriť kontingenčnú tabuľku (alebo viac kont. tabuliek rozmiestnených po listoch) s možnosťou nadefinovať rýchle filtre (môžu byť spoločné na dátumy a iné podmienky pre všetky kt)citovat
Děkuji všem za reakce. Člověk je někdy zaslepený. Protože nejjednodušší a přitom dostatečně efektivní je řešení, které předhodil elninoslov. Proč vymýšlet zbytečné složitosti. 
citovat
citovatRozvinuji k tématu navazující myšlenku.
Prosím o pomoc, jak nadefinovat dynamicky tiskovou oblast, která by měla být na výšku omezena proměnným počtem řádků s údaji. Tento počet řádků plyne za prvé z rozsahu dostupných hodnot, které jsou vraceny vzorci v listu.
Tabulka v listu je tvořena 4 sloupci a ty jsou vyplněny v 500 řádcích vzorci, na základě kterých se vrací od shora buď reálné údaje nebo je vyplněna prázdná hodnota "" - ve zbývajících řádcích do toho počtu 500.
Stanovit tiskovou oblast je pak třeba za druhé ještě s ohledem na omezení počtu zobrazených řádků pomocí ručně nastavovaného filtru ve sloupci s datumy (vybírá se časové rozmezí).
Nějaká rada?citovat
Prosím o pomoc, jak nadefinovat dynamicky tiskovou oblast, která by měla být na výšku omezena proměnným počtem řádků s údaji. Tento počet řádků plyne za prvé z rozsahu dostupných hodnot, které jsou vraceny vzorci v listu.
Tabulka v listu je tvořena 4 sloupci a ty jsou vyplněny v 500 řádcích vzorci, na základě kterých se vrací od shora buď reálné údaje nebo je vyplněna prázdná hodnota "" - ve zbývajících řádcích do toho počtu 500.
Stanovit tiskovou oblast je pak třeba za druhé ještě s ohledem na omezení počtu zobrazených řádků pomocí ručně nastavovaného filtru ve sloupci s datumy (vybírá se časové rozmezí).
Nějaká rada?citovat
Takto ? Vyfiltrujete si dátumy, a rozsah bude vždy začínať 1. riadkom (resp. 2. keď 1. je hlavička), lebo ide o filter, no dynamicky sa počíta počet čísel v stĺpci s dátumom (lebo dátum je číslo), plus nechajte nezaškrtnuté "Prázdne". A ak to myslíte tak, že sa vytlačí buď zadaných/vypočítaných počet riadkov, alebo počet vyplnených riadkov, tak sa berie v úvahu Min hodnota z týchto dvoch veličín. Na to je vo vzorci MINR, ktorú si teda odkazujte na tú Vašu vypočítavanú hodnotu, a ak chcete všetky riadky, tak jej dajte hodnotu 500 (alebo 1 000 000) a menšia bude tá zaplnená. Snáď ma chápete.
Příloha: 35818_35549_p3-seznam-predpisu-nastroj_1.xlsx (237kB, staženo 31x)
citovat35818_35549_p3-seznam-predpisu-nastroj_1.xlsx (237kB, staženo 31x)Asi jsem se nevyjádřil docela srozumitelně, ale takto mi to podle mých představ nefunguje. Ale už jsem to snad vyšpekuloval.
Do definice pro oblast tisku jsem zadal tento vzorec:
=POSUN('tisková sestava'!$A$1:$D$1;POZVYHLEDAT(SUBTOTAL(5;'tisková sestava'!XFD:XFD);'tisková sestava'!XFD:XFD;0);0;SUBTOTAL(2;'tisková sestava'!$B:$B)+1)
Takto se to chová přesně tak, jak potřebuji. Problém byl v tom, že výběrem filtru pro období nikoli od začátku sestavy, ale třeba měsíce 7 až 9, tak se pro oblast tisku pořád napočítaly řádky od začátku, a ne od prvního zobrazeného.
Ale navedl jste mě správným směrem, děkuji :-)
Do definice pro oblast tisku jsem zadal tento vzorec:
=POSUN('tisková sestava'!$A$1:$D$1;POZVYHLEDAT(SUBTOTAL(5;'tisková sestava'!XFD:XFD);'tisková sestava'!XFD:XFD;0);0;SUBTOTAL(2;'tisková sestava'!$B:$B)+1)
Takto se to chová přesně tak, jak potřebuji. Problém byl v tom, že výběrem filtru pro období nikoli od začátku sestavy, ale třeba měsíce 7 až 9, tak se pro oblast tisku pořád napočítaly řádky od začátku, a ne od prvního zobrazeného.
Ale navedl jste mě správným směrem, děkuji :-)
Příloha: 35832_35818_35549_p3-seznam-predpisu-nastroj_2.xlsx (237kB, staženo 34x)
citovat35832_35818_35549_p3-seznam-predpisu-nastroj_2.xlsx (237kB, staženo 34x)V předchozím příspěvku i v příloze byla chyba ve vzorci. Správně má být:
=POSUN('tisková sestava'!$A$1:$D$1;POZVYHLEDAT(SUBTOTAL(5;'tisková sestava'!$B:$B);'tisková sestava'!$B:$B;0)-1;0;SUBTOTAL(2;'tisková sestava'!$B:$B)+1), navíc ještě chybně relativní odkazy na oblasti.citovat
=POSUN('tisková sestava'!$A$1:$D$1;POZVYHLEDAT(SUBTOTAL(5;'tisková sestava'!$B:$B);'tisková sestava'!$B:$B;0)-1;0;SUBTOTAL(2;'tisková sestava'!$B:$B)+1), navíc ještě chybně relativní odkazy na oblasti.citovat
Príklad čisto počítaného rozsahu bez filtra, na základe dátumov od-do v bunkách J1-K1:
=OFFSET('tisková sestava'!$A$1:$D$1;MATCH('tisková sestava'!$K$1;'tisková sestava'!$B:$B;1)-COUNTIFS('tisková sestava'!$B:$B;">="&'tisková sestava'!$J$1;'tisková sestava'!$B:$B;"<="&'tisková sestava'!$K$1);;COUNTIFS('tisková sestava'!$B:$B;">="&'tisková sestava'!$J$1;'tisková sestava'!$B:$B;"<="&'tisková sestava'!$K$1))
=POSUN('tisková sestava'!$A$1:$D$1;POZVYHLEDAT('tisková sestava'!$K$1;'tisková sestava'!$B:$B;1)-COUNTIFS('tisková sestava'!$B:$B;">="&'tisková sestava'!$J$1;'tisková sestava'!$B:$B;"<="&'tisková sestava'!$K$1);;COUNTIFS('tisková sestava'!$B:$B;">="&'tisková sestava'!$J$1;'tisková sestava'!$B:$B;"<="&'tisková sestava'!$K$1))
máte ich zoradené, dá sa teda aj takto.citovat
=OFFSET('tisková sestava'!$A$1:$D$1;MATCH('tisková sestava'!$K$1;'tisková sestava'!$B:$B;1)-COUNTIFS('tisková sestava'!$B:$B;">="&'tisková sestava'!$J$1;'tisková sestava'!$B:$B;"<="&'tisková sestava'!$K$1);;COUNTIFS('tisková sestava'!$B:$B;">="&'tisková sestava'!$J$1;'tisková sestava'!$B:$B;"<="&'tisková sestava'!$K$1))
=POSUN('tisková sestava'!$A$1:$D$1;POZVYHLEDAT('tisková sestava'!$K$1;'tisková sestava'!$B:$B;1)-COUNTIFS('tisková sestava'!$B:$B;">="&'tisková sestava'!$J$1;'tisková sestava'!$B:$B;"<="&'tisková sestava'!$K$1);;COUNTIFS('tisková sestava'!$B:$B;">="&'tisková sestava'!$J$1;'tisková sestava'!$B:$B;"<="&'tisková sestava'!$K$1))
máte ich zoradené, dá sa teda aj takto.citovat
Přišel jsem na problém. Moje řešení
http://wall.cz/index.php?m=topic&id=35549&page=1#post-35838přeci jen nefunguje jak by mělo. Když vzorec nastavím pro název "Oblast tisku", tak to funguje do doby, než filtrem vyberu období, předcházející tomu, které bylo vybráno před tím. Potom se stane to, že původní vzorec v názvu je nahrazen pevnou oblastí.citovat
http://wall.cz/index.php?m=topic&id=35549&page=1#post-35838přeci jen nefunguje jak by mělo. Když vzorec nastavím pro název "Oblast tisku", tak to funguje do doby, než filtrem vyberu období, předcházející tomu, které bylo vybráno před tím. Potom se stane to, že původní vzorec v názvu je nahrazen pevnou oblastí.citovat
Uživatelské menu
Nejste přihlášen(a)
\n
\nMenu
Formulář Faktura
Formulář Faktura IV
Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.
Helios iNuvio
Používáte podnikový systém Helios iNuvio? Potřebujete pomoci se správou nebo vyvinout SQL proceduru? Více informací naleznete na stránce Helios iNuvio.
RSS nejnovější články