10 triků, jak opravit pomalé tabulky aplikace Excel (zrychlení aplikace Excel)

Jestliže nás všechny něco spojuje, pak je to frustrace z pomalých tabulek aplikace Excel.

Zatímco při menším množství dat může být dopad na výkon zanedbatelný, s přibývajícím množstvím dat/výpočtů v sešitě se prohlubuje.

9 z 10 případů si uživatel aplikace Excel bude stěžovat na pomalé tabulky Excel. A že se s tím nedá téměř nic dělat.

No, to NENÍ tak úplně pravda.

Způsob, jakým byl Excel vytvořen, způsobuje, že je při velkých souborech dat pomalý. Existuje však mnoho triků pro zrychlení, které můžete použít ke zlepšení výkonu pomalé tabulky Excelu.

10 tipů, jak si poradit s pomalými tabulkami Excelu

Představujeme vám 10 tipů, jak trochu zrychlit vaši pomalou tabulku Excelu a ušetřit vám čas a frustraci (kliknutím přejdete na danou část).

1. Vyhněte se těkavým funkcím (musíte).
2. Používejte pomocné sloupce.
3. Vyhněte se vzorcům v poli (pokud můžete).
4. Užívejte opatrně podmíněné formátování.
5. Používejte tabulky Excelu a pojmenované rozsahy.
6. Převádějte nepoužívané vzorce na hodnoty.
7. Udržujte všechna odkazovaná data na jednom listu.
8. Vyhněte se použití celého řádku/sloupce v odkazech.
9. Používejte ruční režim výpočtu.
10. Používejte rychlejší techniky vzorců.

Vyhněte se těkavým vzorcům

Těkavé vzorce se tak nazývají z určitého důvodu. Funkce jako NOW, TODAY, INDIRECT, RAND, OFFSET atd. se přepočítávají při každé změně v sešitě.

Pokud například použijete v buňce funkci NOW, při každé změně v listu se vzorec přepočítá a hodnota buňky se aktualizuje.

To vyžaduje další rychlost zpracování a ve výsledku máte sešit Excelu pomalý.

Pravidlem je vyhýbat se nestálým vzorcům. A pokud to nejde, snažte se jejich použití minimalizovat.

Používejte pomocné sloupce

Pomocné sloupce jsou jednou z nejvíce podceňovaných konstrukcí v Excelu. Viděl jsem, že mnoho lidí se vytváření pomocných sloupců vyhýbá.

NEDĚLEJTE TO.

Největší výhodou používání „pomocných sloupců“ je, že vám to může pomoci vyhnout se vzorcům v poli.

Teď mě nechápejte špatně. Nejsem proti vzorcům v poli. Spíše se domnívám, že by mohly být v některých situacích úžasné. Ale ono když se snažíte udělat vše pomocí jednoho dlouhého vzorce, má to vliv na výkon vašeho sešitu Excelu. Pár vzorců pole tu a tam by nemělo uškodit, ale v případě, že je potřebujete použít na mnoha místech, zvažte použití pomocných sloupců.

Níže uvádíme několik příkladů, kde se pomocné sloupce používají:

• Automatické řazení dat v abecedním pořadí pomocí vzorce.
• Dynamický filtr Excelu – výpis dat podle zadání.
• Vytvoření více rozevíracích seznamů v Excelu bez opakování.

Vyhněte se vzorcům v poli

Vzorce v poli mají své přednosti – ale rychlost mezi ně nepatří.

Jak bylo vysvětleno výše, vzorce v poli mohou pojmout velké množství dat (odkazů na buňky), analyzovat je a poskytnout vám výsledek. To však vyžaduje čas.

Pokud existuje způsob, jak se vzorcům pole vyhnout (například pomocí pomocného sloupce), vždy se vydejte touto cestou.

Používejte podmíněné formátování s rozvahou

Podmíněné formátování naprosto miluji. Díky němu vypadají nevýrazná data tak krásně. Místo toho, abyste porovnávali sami, se teď můžete jednoduše podívat na barvu nebo ikonu buňky a budete vědět, jak je na tom v porovnání s ostatními.

Ale… tady je problém.

Málo uživatelů Excelu ví, že podmíněné formátování Excelu je nestálé. Zatímco u malých souborů dat si rozdílu nemusíte všimnout, při použití na velké soubory dat nebo při vícenásobném použití může vést ke zpomalení excelovské tabulky.

Rada – používejte jej opatrně.

Používejte excelovské tabulky a pojmenované rozsahy

Excelovská tabulka a pojmenované rozsahy jsou dvě úžasné funkce, které uchovávají vaše data a velmi usnadňují odkazování. Může chvíli trvat, než si na ně zvyknete, ale jakmile je začnete používat, život se stane snadným a rychlým.

Při vytváření řídicích panelů založených na datech je téměř vždy dobré převést data do tabulky aplikace Excel.

Má to také další výhodu v tom, že vaše vzorce jsou srozumitelnější.

Co je například srozumitelnější?

=Prodejní cena-Nákladová cena NEBO =SUMA(A1:A10)-SUMA(G1:G10)

Převést nepoužívané vzorce na statické hodnoty

Toto není žádný problém. Když je nepotřebujete, nenechávejte si je.

Spousta vzorců by měla za následek pomalý sešit Excelu. A pokud máte vzorce, které se ani nepoužívají – víte, na koho to svést. Platí pravidlo, že pokud vzorce nepotřebujete, je lepší je převést na statickou hodnotu (vložením jako hodnoty).

Read More: How to quickly convert formulas to values.

Udržujte všechna odkazovaná data na jednom listu

To sice není vždy možné, ale pokud to dokážete, zaručuji vám, že by se váš list Excelu zrychlil.

Logika je jednoduchá – vzorce ve vašem listu nemusí pro data chodit daleko, když jsou hned vedle na stejném listu.

Vyhněte se používání celého řádku/sloupce jako odkazu (A:A)

Důvod, proč mám tento na seznamu, je ten, že vidím mnoho lidí, kteří ve vzorcích používají odkaz na celý řádek/sloupec. To je špatná praxe a měli byste se jí vyvarovat.

Ačkoli si možná myslíte, že řádek/sloupec obsahuje pouze několik buněk s daty, Excel tak neuvažuje. Pokud odkazujete na celý řádek/sloupec, Excel se k němu chová jako k dobrému sluhovi a stejně ho zkontroluje. To zabere více času na výpočty.

9. V případě, že se jedná o řádek nebo sloupec, je potřeba se na něj podívat. Používejte ruční režim výpočtu

Jen opakuji to, co už řeklo milion lidí na různých fórech a blozích. Použití ručního výpočtu vám dává možnost říct Excelu, kdy má počítat, místo toho, aby se Excel rozhodoval sám. Není to něco, co by zrychlilo váš excelovský sešit, ale pokud máte pomalou excelovskou tabulku, rozhodně to šetří čas tím, že Excel nemusí znovu a znovu přepočítávat.

• Chcete-li přepnout do ručního režimu, přejděte na kartu Vzorce -> Možnosti výpočtu -> Ruční (pro přepočet stiskněte klávesu F9)

Používejte techniky rychlejších vzorců

Excel vám nabízí spoustu vzorců a kombinací vzorců, které slouží ke stejnému účelu. Nejlepší je identifikovat a používat ty nejrychlejší.

Tady je pár příkladů:

• Používejte IFERROR místo kombinace IF a ISERROR (pokud nepoužíváte Excel 2003 nebo starší, který IFERROR nemá).
• Používejte MAX(A1,0) místo IF(A1>0,A1,0) – To je super tip, který jsem se naučil od pana Excelu alias Billa Jelena. Jeho výzkumy ukazují, že možnost MAX je o 40 % rychlejší než možnost IF (a já jsem ochoten tuto statistiku brát za bernou minci).
• Používejte kombinaci INDEX/MATCH, místo VLOOKUP – Tohle možná vyvolá spoustu obočí, ale pravda je, že VLOOKUP nemůže být rychlejší, pokud máte 100 sloupců dat. Svět přechází na INDEX/MATCH a vy byste měli tento posun udělat také.
  .
• Používejte – (dvojitý zápor) k převodu hodnot TRUE a FALSE na 1 a 0 (místo abyste je násobili 1 nebo k nim přičítali 0). Zlepšení rychlosti je patrné u velkých souborů dat.

Je to vyčerpávající seznam? Rozhodně NE. Jsou to některé dobré, o kterých si myslím, že stojí za to se o ně podělit jako o výchozí bod. Pokud si chcete osvojit techniky pro zrychlení Excelu, existuje několik dobrých prací od mnoha odborníků na Excel.

Tady jsou některé zdroje, které by se vám mohly hodit:

• 75 tipů pro zrychlení od Chandoo (chytře provedeno crowdsourcingem).
• Webové stránky Decision Models.
• Diskusní fórum pana Excelu (prozkoumejte ho a našli byste spoustu tipů).

Jsem si jistý, že máte také mnoho tipů, které vám mohou pomoci vypořádat se s pomalými tabulkami Excelu. Podělte se o ně s námi zde v sekci komentářů.

Mám také jednu prosbu. Bolest z práce s pomalou excelovou tabulkou zažívá mnoho z nás denně. Pokud vám tyto techniky přijdou užitečné. podělte se o ně s ostatními. Ulehčete jim bolest a získejte nějakou dobrotu 🙂

Musí se vám také líbit následující odkazy na Excel:

• 24 triků Excelu, díky kterým proplujete každodenní prací.
• 10 super elegantních způsobů, jak vyčistit data v tabulkách Excelu.
• 10 tipů pro zadávání dat do Excelu, které si nemůžete nechat ujít.
• Vytvoření a použití rozbalovacího seznamu v Excelu.
• Zmenšení velikosti souborů Excelu.
• Jak obnovit neuložené soubory Excelu.
• Bezplatné online školení Excelu (7dílný videokurz)

.