Bez přílohy?
P.citovat
P.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: Optimalizace excelového souboru
MS Excel
Téma: Optimalizace excelového souboru 
Zaslal/a Jirka32 9.3.2017 15:46
Zdravím,
prosím je možné např nějakou procedurou VBA eliminovat již vypočtené vazby v excelovém sešitu, nebo jak jinak zrychlit celkově výpočet?
Mám soubor o cca 100 vzájemně propojených listech (velikost cca 20M, použité fce defacto sumif, indexy, pozvyhledat atd. neřekl bych že nijak výrazně neefektivní) a přepočet je k pláči pomalý, nevím si s tím rady.
Existuje prosím něco na zrychlení?
Předem děkuji za radu.
(9.3.2017 22:20)#035431
Bez přílohy:
..Ne - nějak efektivně. Mlžete se kouknout na závislosti vzorců. Jestli tam nemáte nějaké zbytečné špagety.
Řešením by mohla být změna skladby dat (normalizace, denormalizace atp..), prostě zbavit se redundancí, počtu závislostí, počtu tabulek. Excel >=2013 umí definovat relační vztahy, to by mohlo v některých případech pomoci.
Podívat se po možnosti vytvořit další úroveň agregace bez použití vzorců (KT + funkce GetPivotData, Power nástroje, ...)
Pokud tak máte, nebo po úpravě bude přepočet stále výkonnostně nevyhovující, přejděte na databázi.
Podmíněná agregace v excelu je - za použití vzorců - pomalá, zejména u většího množství dat.citovat
..Ne - nějak efektivně. Mlžete se kouknout na závislosti vzorců. Jestli tam nemáte nějaké zbytečné špagety.
Řešením by mohla být změna skladby dat (normalizace, denormalizace atp..), prostě zbavit se redundancí, počtu závislostí, počtu tabulek. Excel >=2013 umí definovat relační vztahy, to by mohlo v některých případech pomoci.
Podívat se po možnosti vytvořit další úroveň agregace bez použití vzorců (KT + funkce GetPivotData, Power nástroje, ...)
Pokud tak máte, nebo po úpravě bude přepočet stále výkonnostně nevyhovující, přejděte na databázi.
Podmíněná agregace v excelu je - za použití vzorců - pomalá, zejména u většího množství dat.citovat
Možno off-topic, možno nie:
Rok v práci pracujem so súborom, ktorý postupne ladím. Od užívateľov prichádzajú postupne nejaké zmenové požiadavky, takže sa zdá, že žiadna verzia nie je postupom času definitívna. Priebežne v súbore robím nejaké optimalizácie. Pred poslednou optimalizáciou mal súbor cca 13 MB. Pri poslednej optimalizácii som to stiahol na cca 10,5 Mb. Uvedený súbor má rádovo cca 30 sheetov. A teraz k jadru pudla: Jeden z listov obsahoval tabuľku s cca 3 tisícmi riadkov v excel table. V uvedenej table boli v dvoch stĺpcoch vzorce, ktoré som trochu modifikoval (samozrejme, modifikovaný vzorec dával tie samé hodnoty ako pôvodný, modifikácia spočívala v redesigne vzorca nemajúcom vplyv na jeho návratovú hodnotu), po ich modifikácii sa veľkosť súboru zmenšila z 10,5Mb na 6 Mb... Inými slovami, stačí jeden vzorec, nie moc šikovne napísaný, pokiaľ sa vyskytuje v rozsiahej oblasti, tak veľkosť súboru je okamžite rádovo inde. Trúfam si tvrdiť, že nie som žiadna excel lama, a i napriek tomu sa mi toto stáva..citovat
Rok v práci pracujem so súborom, ktorý postupne ladím. Od užívateľov prichádzajú postupne nejaké zmenové požiadavky, takže sa zdá, že žiadna verzia nie je postupom času definitívna. Priebežne v súbore robím nejaké optimalizácie. Pred poslednou optimalizáciou mal súbor cca 13 MB. Pri poslednej optimalizácii som to stiahol na cca 10,5 Mb. Uvedený súbor má rádovo cca 30 sheetov. A teraz k jadru pudla: Jeden z listov obsahoval tabuľku s cca 3 tisícmi riadkov v excel table. V uvedenej table boli v dvoch stĺpcoch vzorce, ktoré som trochu modifikoval (samozrejme, modifikovaný vzorec dával tie samé hodnoty ako pôvodný, modifikácia spočívala v redesigne vzorca nemajúcom vplyv na jeho návratovú hodnotu), po ich modifikácii sa veľkosť súboru zmenšila z 10,5Mb na 6 Mb... Inými slovami, stačí jeden vzorec, nie moc šikovne napísaný, pokiaľ sa vyskytuje v rozsiahej oblasti, tak veľkosť súboru je okamžite rádovo inde. Trúfam si tvrdiť, že nie som žiadna excel lama, a i napriek tomu sa mi toto stáva..citovat
Díky za podněty, nicméně v tom asi nebude jádro pudla.
Příloha...no těch 20M asi nene...nicméně možná je to jednodušší, než si myslím...ten soubor je složen z cca 10 listů, které jsou nakopírované 12ti (pro každý z 12ti produktů), protože potřebuji, aby mezi sebou byly on-line propojeny...figurují tam určité interní přeúčtování mezi výnosy a náklady, které se musejí rovnat. Měl jsem to před tím na 12 jednotlivých souborech, nicméně jsem nebyl schopný toto přeúčtování držet stále v pořádku, proto jsem to byl nucen dát do jednoho souboru a ejhle...kdo by to byl čekal :-)...je to pomalý
Takže výpočet běží jen na oněch 10 listech stále stejný, jen dohromady všech cca 120 listech...tak jsem myslel, jak vnutit excelu myšlenku, at už nepočítá co už je vypočtené...nevím no...asi se budu muset smířit s pomalým modelem...přesto všem díkycitovat
Příloha...no těch 20M asi nene...nicméně možná je to jednodušší, než si myslím...ten soubor je složen z cca 10 listů, které jsou nakopírované 12ti (pro každý z 12ti produktů), protože potřebuji, aby mezi sebou byly on-line propojeny...figurují tam určité interní přeúčtování mezi výnosy a náklady, které se musejí rovnat. Měl jsem to před tím na 12 jednotlivých souborech, nicméně jsem nebyl schopný toto přeúčtování držet stále v pořádku, proto jsem to byl nucen dát do jednoho souboru a ejhle...kdo by to byl čekal :-)...je to pomalý
Takže výpočet běží jen na oněch 10 listech stále stejný, jen dohromady všech cca 120 listech...tak jsem myslel, jak vnutit excelu myšlenku, at už nepočítá co už je vypočtené...nevím no...asi se budu muset smířit s pomalým modelem...přesto všem díkycitovat
(21.3.2017 14:09)#035653
Nejčastější chybou je, když nutíte Excel tisíckrát počítat totéž, například když formule
=KDYŽ(A($A$1=$B$1*$C$1;$A$2=$B$2*$C$2;$A$3=$B$3*$C$3); D10; E10)
je umístěna do buňky A10 a poté zkopírována do buněk A11:A109; kopírováním odkazy na buňky z A1:C3 zůstanou stejné, odkazy na D10 a E10 se adjustují na D11 až D109 a E11 až E109. Pak Excel 100x počítá tutéž hodnotu
A($A$1=$B$1*$C$1;$A$2=$B$2*$C$2;$A$3=$B$3*$C$3)
Řešení je samozřejmě triviální: Do nějaké nepoužívané buňky (třeba do Z1) dát vzorec
=A(A1=B1*C1; A2=B2*C2; A3=B3*C3)
a do A10 dát formuli
=KDYŽ($Z$1; D10; E10)
a tu zkopírovat do A11:A109.
Najděte všechna místa v sešitu, kde se opakovaně počítá totéž a zbavte se toho naznačeným způsobem.citovat
=KDYŽ(A($A$1=$B$1*$C$1;$A$2=$B$2*$C$2;$A$3=$B$3*$C$3); D10; E10)
je umístěna do buňky A10 a poté zkopírována do buněk A11:A109; kopírováním odkazy na buňky z A1:C3 zůstanou stejné, odkazy na D10 a E10 se adjustují na D11 až D109 a E11 až E109. Pak Excel 100x počítá tutéž hodnotu
A($A$1=$B$1*$C$1;$A$2=$B$2*$C$2;$A$3=$B$3*$C$3)
Řešení je samozřejmě triviální: Do nějaké nepoužívané buňky (třeba do Z1) dát vzorec
=A(A1=B1*C1; A2=B2*C2; A3=B3*C3)
a do A10 dát formuli
=KDYŽ($Z$1; D10; E10)
a tu zkopírovat do A11:A109.
Najděte všechna místa v sešitu, kde se opakovaně počítá totéž a zbavte se toho naznačeným způsobem.citovat
Najskôr je problém v mnoho krát vyhľadávacími funkciami vlookup. Skúste používať iba jeden stĺpec na hľadanie riadku pomocou POZVYHLEDAT a hodnoty z rôznych stĺpcov pomocou ZVOLIT ang. Index. Problém nastane pri aktualizácii hodnoty zošita, kedy sa automaticky spustí prepočet všetkých funkcií. Tiež by sa dalo výpočet zmeniť na ručne; alebo vyhľadanie nechať na prvom riadku a ostatné vzorce nahradiť hodnotami (po zmene údajov vrátiť vzorce z 1. riadku).
Od verzie 2010, ak sú údaje v tabuľkách, dajú sa medzi nimi definovať relácie a potom zobrazovať v kontingenčnej tabuľke s ľubovolnými hodnotami z rôznych tabuliek a tiež by sa dalo vytvoriť spojenie (a načítanie) údajov pomocou Power Query, resp. ak máte pomocou modelu Power Pivot.citovat
Od verzie 2010, ak sú údaje v tabuľkách, dajú sa medzi nimi definovať relácie a potom zobrazovať v kontingenčnej tabuľke s ľubovolnými hodnotami z rôznych tabuliek a tiež by sa dalo vytvoriť spojenie (a načítanie) údajov pomocou Power Query, resp. ak máte pomocou modelu Power Pivot.citovat
(22.3.2017 10:18)#035676
Nejčastější chybou je, když nutíte Excel tisíckrát počítat totéž ...
Tímto způsobem dosáhnem menší velikosti souboru a lepší(někdy) přehlednost ve vzorcích.
Zisk rychlosti přepočtu je jen teoretický, v závislosti na úspoře systémových prostředků z důvodu menšího souboru. Buňka si nepamatuje jednou získanou hodnotu ze Vzorce, vyhodnocuje se znovu a znovu při každém zavolání.citovat
Tímto způsobem dosáhnem menší velikosti souboru a lepší(někdy) přehlednost ve vzorcích.
Zisk rychlosti přepočtu je jen teoretický, v závislosti na úspoře systémových prostředků z důvodu menšího souboru. Buňka si nepamatuje jednou získanou hodnotu ze Vzorce, vyhodnocuje se znovu a znovu při každém zavolání.citovat
Ukázka vzorců by dost napověděla.
Stovka vzájemně propojených sešitů je docela dost na uhlídání efektivity.
Obecně:
pozvyhledat - na setříděném souboru je na delší tabulce i o několik řádů rychlejší
sumif - pokud je toho více, tak se hodně vyplatí kontingenční tabulka. pokud jsou listy jen vstupy pro výpočet, lze načítat přímo do ní, šetří to místo i čas.
Tabulku lze vytvořit jako zdroj předvypočtených dat.
získatkontdata je pak o dost rychlejší než jiné hledání.
Podívejte se na zbytečné závislosti (např. index, svyhledat, ...) - vedou ke zbytečným přepočtům
Zvažte propojování bloků jen na omezeném prostoru, za každý produkt jen jeden list, který se propojuje s ostatními. Případně rozdělte to zpět a ke konsolidaci použijte např. powerquery.citovat
Stovka vzájemně propojených sešitů je docela dost na uhlídání efektivity.
Obecně:
pozvyhledat - na setříděném souboru je na delší tabulce i o několik řádů rychlejší
sumif - pokud je toho více, tak se hodně vyplatí kontingenční tabulka. pokud jsou listy jen vstupy pro výpočet, lze načítat přímo do ní, šetří to místo i čas.
Tabulku lze vytvořit jako zdroj předvypočtených dat.
získatkontdata je pak o dost rychlejší než jiné hledání.
Podívejte se na zbytečné závislosti (např. index, svyhledat, ...) - vedou ke zbytečným přepočtům
Zvažte propojování bloků jen na omezeném prostoru, za každý produkt jen jeden list, který se propojuje s ostatními. Případně rozdělte to zpět a ke konsolidaci použijte např. powerquery.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