Finanční modelování v Excelu: Co dělat, když je velikost souboru mimo kontrolu

Když ve svém finančním modelu začnete získávat značné množství dat, je docela snadné skončit s obrovským souborem Excel, jehož výpočet trvá dlouho, takže je obtížné jej zkontrolovat nebo sdílet s ostatními.

Pokud je velká velikost souboru způsobena velkým počtem řádků (řekněme více než 100 000), zvažte použití Power Pivot k uložení dat.

Pokud zjistíte, že se váš model vymyká kontrole (a používáte standardní Excel, bez pomoci Power Pivot nebo jakýchkoli jiných doplňků), zde je několik osvědčených tipů, které můžete použít k udržení této velikosti souboru dolů:

• Odstraňte veškeré zbytečné formátování. Barvy a formátování by se měly vztahovat pouze na nezbytný rozsah, nikoli na celý řádek nebo sloupec. Vyhněte se ručnímu formátování a místo toho použijte styly.

  Když mažete buňky, které již nepoužíváte, pravděpodobně to uděláte tak, že vyberete buňku a stisknete klávesu Delete. Tato akce vymaže obsah a vzorce, ale nevymaže formátování. Pokud máte podezření, že se jedná o problém, můžete stisknutím Ctrl+A vybrat všechny buňky; potom na kartě Domů na pásu karet v části Úpravy klikněte na rozevírací seznam Vymazat a vyberte Vymazat formáty.

• Ujistěte se, že vzorce odkazují pouze na rozsah, který potřebují (nevybírají celý řádek nebo sloupec). Pokud vaše vzorce odkazují na více buněk, než potřebují, zabere to více paměti. Například použijte vzorec =SUM(A1:A1000), abyste umožnili další řádky namísto =SUM(A:A). Případně můžete naformátovat data jako tabulku a místo toho odkazovat na automaticky se rozšiřující rozsahy tabulek ve vzorci.
• Odstraňte (nebo alespoň zkontrolujte jejich velikost) všechna loga nebo obrázky, které používáte. Jediný soubor JPG vložený do modelu může snadno přidat 10 MB k velikosti souboru Excel.
• Vyhněte se kontingenčním tabulkám. Kontingenční tabulky opravdu žvýkají paměť. Pokud máte více kontingenčních tabulek, ujistěte se, že používají stejný zdroj dat a nevytvářejí zcela nový.
• Odstraňte externí odkazy na jiné soubory. Chcete-li zkontrolovat, zda váš model obsahuje nějaké externí odkazy, přejděte na kartu Data na pásu karet a v části Připojení klikněte na Upravit odkazy. Pokud je tlačítko Upravit odkazy zašedlé, znamená to, že neexistují žádné externí odkazy. Pokud existují, klikněte na tlačítko a když se zobrazí dialogové okno Upravit odkaz, klikněte na Přerušit odkazy, abyste vložili data ze všech externích odkazů jako hodnoty a externí odkazy budou odstraněny. Pokud bezpodmínečně musíte použít externí odkazy, mějte současně otevřený zdrojový soubor; to vše urychlí.
• Zkontrolujte nadbytečnost ve vstupech a výpočtech. Někdy se model vyvíjí a mohou existovat části, které se již nepoužívají, nebo části, kde se informace změnily.
• Vyhněte se nestálým funkcím. Některé z nejčastěji používaných nestálých funkcí jsou OFFSET, NEPŘÍMÝ, RAND, NOW, TODAY, ROW a COLUMN. Nadměrné používání těchto konkrétních funkcí ve vašem modelu může skutečně zpomalit vaše výpočty. Pokud je musíte použít, pokuste se omezit jejich výskyt v modelu. Například =TODAY() je velmi užitečné pro zadání dnešního data, ale místo jeho vícenásobného použití ve vzorci ho umístěte do jedné buňky a neustále se vraťte zpět na tuto jednu buňku pro dnešní datum.
• Ujistěte se, že nepoužíváte typ souboru XLS. XLSX je mnohem kompaktnější typ souboru a při jeho použití uvidíte obrovský rozdíl v rychlosti a velikosti souboru. XLSB je typ souboru binárního sešitu aplikace Excel, který je ještě kompaktnější než XLSX.

Pokud jste vyzkoušeli všechny tyto tipy a stále máte problémy, zvažte přepnutí výpočtu na ruční (můžete to provést kliknutím na kartu Vzorce na pásu karet, přechodem do části Výpočet a výběrem možnosti ručního výpočtu). F9 pak stiskněte jen tehdy, když potřebujete přepočítat. Budete vědět, zda je třeba něco vypočítat, protože ve stavovém řádku uvidíte Vypočítat .

Nakonec jako poslední možnost je malým trikem ponechat jednu buňku nahoře ve sloupci s živým odkazem a vložit všechny ostatní buňky jako hodnoty. Zkopírujte buňku dolů a přepočítejte, kdy potřebujete obnovit. Toto rozhodně není preferovaná možnost, protože je časově náročná a náchylná k chybám, ale proto je to poslední možnost.