• Úvod
• Excel návod
• Excel šablony
• Nástěnka
• Mobility
• Tipy na výlet
• Galerie
• Fórum

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)

Zaslat odpověď >

---

Strana:  1 2   další »

xlnc(15.3.2017 17:35)#035550

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

elninoslov(15.3.2017 18:34)#035555

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

MePExG(15.3.2017 20:36)#035559

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

pavelo(16.3.2017 8:51)#035567

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

pavelo(28.3.2017 22:54)#035816

Rozvinuji 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

elninoslov(28.3.2017 23:31)#035818

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)

citovat

pavelo(29.3.2017 13:57)#035832

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 :-)

Příloha: 35832_35818_35549_p3-seznam-predpisu-nastroj_2.xlsx (237kB, staženo 34x)

citovat

pavelo(29.3.2017 15:34)#035838

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

elninoslov(29.3.2017 17:40)#035842

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

pavelo(30.3.2017 8:13)#035860

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

Strana:  1 2   další »

Uživatelské menu

Nejste přihlášen(a)

• přihlásit
• registrace

\n

Menu

On-line nástroje

Formulář Faktura

Aktivní diskuse