MS Excel

Dobrý den, chtěl bych se zeptat, zda lze nějak zásadně ovlivnit velikost souboru?
Posílám vzor a popíši ho.
Ve vzoru jsem nechal jen pár řádků vstupních dat na listu „data“ takže je tam jen pár řádků.
Na listu „data“ ve sloupcích „A“ až „AI“ jsou vstupní data a ve sloupcích „AJ“ až „AO“ mám vzorce.
Ve sloupcích, např. „C“, „D“, „H“, „AF“ a ještě i v některých dalších jsou vždy data v každém řádku.
Nyní mám můj soubor 174 441 řádků a jeho velikost je 42 962 kB.
Když spustím makro „Module2“, které jinak na menších souborech funguje dobře, neboť zaktualizuje všechny konti tabulky a vymaže stará data, tak se to „kousne“ respektive to nějak dlouho trvá.
1) Chtěl bych se tedy zeptat, zda velikost toho mého konkrétního souboru je ovlivněna počtem řádků a zároveň těmi vzorci, neboť zdrojová data vkládám jako "hodnoty"?
2) Byl by někdo za úplatu ochoten napsat mi makro, které by nahradilo všechny dosud napsané vzorce s tím, že by makro vždy doplnilo hodnoty ve všech řádcích s daty (dynamická oblast) a nakonec rutiny by vložilo všude jen „hodnoty“, aby byl soubor co nejmenší? A asi, aby se při přepočtech přepnulo do „maunálního“ režimu, aby to nebylo pomalé…
Případné, cenové nabídky, prosím, posílejte na můj email. Jsem občan, zaměstnanec, takže si to zaplatím rád ze svého a nepotřebuji řádný doklad.
Děkuji
P.S. Moc by mi to makro pomohlo...

Zaslat odpověď >

lubo(14.5.2015 17:52)#024949

1) Chtěl bych se tedy zeptat, zda velikost toho mého konkrétního souboru je ovlivněna počtem řádků

ANO

a zároveň těmi vzorci,

ANO

Je velikost souboru oblivněna nastavením KT?

ANO

=>

1) Importovat jen potřebná data
2) Optimalizovat vzorce (rychlost i velikost):

AO2:
=KDYŽ(U2="Doprava";IFERROR(SVYHLEDAT(AL2;KonvTab;2;0);AM2);AM2)

Kde KonvTab je tabulka se sloupci: SYNT a Kód

Kratší, přehlednější, neopakuje zbytečně dříve provedené výpočty.

3) Zamyslete se nad potřebou a definicí pojmenovaných oblastí:

Např. ORG=ucty!A1:INDEX(ucty!A:A;POZVYHLEDAT(9999;ucty!A:A;0))

Není volatilní a je i rychlejší. Obecně zkuste nahradit posun indexem, pro uvedený počet řádků to význam má.

4) Zamyslete se nad změnou nastavení KT:

(záložka data)
a) neukládat data s KT
b) aktualizovat při otevření.

5) Mazání polí KT:

Vlastnosti KT/data/Počet zachovaných položek na pole/žádné

6) Zdroj dat pro KT - definujte nako tabulku

7) makro pro refresh KT - mazání polí není třeba, nastavebo ve vlastnostech KT. Místo KT aktualizujte pivotcache (je pro obě tabulky společná)

Pokud to nepomůže, lze uvažovat o náhradě vzorců za hodnoty.citovat

Hav-Ran(14.5.2015 19:56)#024950

Výborná analýza aj doporučenia (najúčinnejšie KT bez dát). Ešte si dovolím poradiť zníženie veľkosti skoro o 25% jednoduchým spôsobom - uložiť súbor ako binárny (vyskúšal som bez akýchkoľvek iných zásahov a znížila sa veľkosť z cca 86 na 61, po aplikovaní KT bez dát na 53)citovat

Alfan(15.5.2015 8:39)#024952

Děkuji. Aplikoval jsem některé věci, které jsem uměl a snížil jsem velikost ze 42 MB na 34 MB.

Add 2) Neumím nadefinovat "KonvTab" takže nevyužiji. A navíc nikdy nevím, kolik budu mít řádků.

Add 3) Ten POSUN mi někdo tady na fóru "předepsal" ve smyslu, že mi to vytvořil a já to použil. Jen jsem ještě musel trochu upravit s ohledem na to, že data v tabulce nejsou od buňky "A1". S INDEX si nedokážu poradit...

Add 4) Neukládat data jsem dal, ale potom mi při každém pokusu upravit možnosti Konti tabulky vyskakuje okno, že nemám aktualizovaná data. Jde to nějak zrušit?
Automatickou aktualizaci při otevření jsem neměl a snažím se ji nezadávat.

Add 5) Použil jsem, neznal jsem.

Add 6) Nerozumím... Já potřebuji mít ten zdroj dat dynamický, protože nikdy nevím, kolik budu mít po exportu řádků (více či méně).

Add 7) Rozumím, že není potřeba mazat stará data, ale nedokážu upravit makro podle vaší rady...
Obecně bych měl rád makro, které mi vždycky zaktualizuje všechny konti tabulky v daném sešitu.

Každopádně ještě jednou děkuji

citovat

lubo(15.5.2015 13:47)#024962

Add 2) Neumím nadefinovat "KonvTab" takže nevyužiji.

Někde v sešitu vložíte:
SYNT Kód
502 OPN
503 OPN
521 MZDY
... ...

Vyberete buňku v tabulce, vyberete záložku vložení a vyberete Tabulka.

Dále v možnostech tabulky si tabulku pojmenujete.

A navíc nikdy nevím, kolik budu mít řádků.

Proto je tam ta tabulka. Ve vzorcích se rozsah neuvádí a po přidání dalších řádků vzorce fungují bez opravy.

Add 3) Ten POSUN mi někdo tady na fóru "předepsal" ve smyslu, že mi to vytvořil a já to použil. Jen jsem ještě musel trochu upravit s ohledem na to, že data v tabulce nejsou od buňky "A1". S INDEX si nedokážu poradit...

Možná je načase se na to podívat. POSUN, INDEX, NEPŘÍMÝ.ODKAZ - všechy tyto fukce vrací odresu buňky nebo pole.

Add 4) Neukládat data jsem dal, ale potom mi při každém pokusu upravit možnosti Konti tabulky vyskakuje okno, že nemám aktualizovaná data. Jde to nějak zrušit?
Automatickou aktualizaci při otevření jsem neměl a snažím se ji nezadávat.

Pro práci tabulka potřebuje nahrát data, buď automaticky nebo ručně. Data se ukládájí pokud nahráváte externí data nebo pokud je dat málo. Pokud je dat hodně v sešitu a ještě v kont. tabulce, jsou tam data vícekrát.

Add 6) Nerozumím... Já potřebuji mít ten zdroj dat dynamický, protože nikdy nevím, kolik budu mít po exportu řádků (více či méně).

Právě proto ta tabulka.

Add 7) Rozumím, že není potřeba mazat stará data, ale nedokážu upravit makro podle vaší rady...
Obecně bych měl rád makro, které mi vždycky zaktualizuje všechny konti tabulky v daném sešitu.

Nepotřebujete makro: Záložka data, Aktualizovat vše (aktualizuje i externí data)

Totéž makrem:

Sub Akt()
ThisWorkbook.RefreshAll
End Sub

Aktualizace cache

Sub AktCache()
For Each PivotCache In ActiveWorkbook.PivotCaches
PivotCache.Refresh
Next
End Sub

Aktualizace KT (měl jte to v souboru, akorát doplněné o spoustu zbytečností)

Sub AktKT()
Dim PT As PivotTable
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
For Each PT In WS.PivotTables
PT.RefreshTable
Next PT
Next WS
End Sub
citovat

Hav-Ran(15.5.2015 14:32)#024966

Doplním : ide to aj ručnou aktulizáciou klávesová skratka Ctrl+Alt+F5citovat

Alfan(15.5.2015 15:07)#024968

Děkuji.
Add 2) Tu tabulku obecně už zvládnu.
Ale tu konkrétní KonvTab ne, protože pro každou ORG TEXT (to je organizační jednotka) je u stejné SYN (syntetika účtu) jiný "Kód". Přikládám soubor, kde jsem vyfiltroval tuhle věc (jen na jedné syntetice). Já totiž nejdřív potřebuji přiřadit, když náklad určitého účtu (SYN) spadne na konkrétní ORG TEXT, druh "nákladu" (MAT, MZDY atd.). A navíc je v tom, ještě doprava, u které je to vše na "VR" a pak podle toho, jestli má konkrétní zakázku, tak ještě podle SYN rozděluji jen tu Dopravu na druh nákladu (MAT, MZDY atd.).

Add 3) Rozumím a nastuduji.

Add 4) Tomu rozumí, respektive jsem to pochopil

ale i přesto se mi nepodaří snížit velikost souboru pod 38 MB

Add 6) Už umím

Add 7)
To první makro aktualizuje vše, tedy propojení i KT tabulky?
To druhé makro dělá konkrétně co? Respektive aktualizace jen cashe má jaký význam? Rychlost?
To třetí makro mohu používat na aktualizaci všech KT najednou nebo, které z těchto tří maker bych na tu aktualizaci obecně měl používat s ohledem na rychlost a případně velikost souboru (ale to nevím, jestli makro na to má vliv?).

Děkuji

Příloha: rar

24968_syn-kod_wall.rar (7kB, staženo 18x)

citovat

lubo(18.5.2015 21:58)#025011

Trochu jsem zkoušel. Doplnil jsem původní soubor na 200000 řádků. Pak jsem to
postupně upravoval a sledoval velikost souborů a doby přepočtu:

a) V souboru zakázáno ukládání dat v KT a neukládají se položky v poli.
b) Ve sloupci AO nahrazen vzorec za alternativní
=KDYŽ(U2="Doprava";IFERROR(SVYHLEDAT(AL2;KonvTab;2;0);AM2);AM2)
Tabulka KonvTab je na listu data:
SYNT Kód
501 MAT
502 OPN
503 OPN
504 OPN
511 OPN
512 OPN
513 OPN
518 OPN
521 MZDY
524 MZDY
c) Vzorec ve sloupci AM je nahrazen vzorcem:
=INDEX(ucty!$1:$1048576;POZVYHLEDAT(AJ2;ucty!$A:$A;0);POZVYHLEDAT(AF2;ucty!$2:$2;0))
Odstraněny názvy ORG, SYNT a ZDROJ.
d) Data na listu data transformována na tabulku. Název zdrojkonti
odstraněn. Zdrojem dat v KT na listech "detail střediska" a "konti" je
tabulka.
e) Vzorce v tabulce na listu data nahrazeny strukturovanými.

Výsledky:

MiB Sest. a přep. Úplný přep. Přepočet
Původní soubor 39,85 1,982 0,701 0,582
Var a - bez dat v KT 33,67 2,004 0,715 0,590
Var b - or -> svyhledat 35,62 1,450 0,467 0,348
Var c - změna hledání v "účty" 37,15 1,419 0,360 0,037
Var d - data - > tabulka 37,14 1,397 0,360 0,036
Var e - Strukt. Vzorce 34,72 2,735 0,470 0,041

Doby přepočtu jsou v sekundách. Velikosti souborů jsou orientační. Velikosti
souborů kolísaly při různém ukládání o desítky MB. Jak se zdá pro velikost
souboru je také důležitý počet unikátních hodnot ve sloupcích.

Překvapila varianta e). Z hlediska velikosti
dobré, doba přepočtu zklamala a doba sestavení byla nejhorší. Byly i problémy
s čerpáním paměti (excel zabral cca 800 MB, paměti mám 8GB).

Varianty c) i d) lze považovat za rovnocené. Variantu d) (s tabulkou) považuji za bezpečnější.citovat

Téma: Ovlivnění velikosti souboru

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje