Pénzügyi modellezés Excelben: Mi a teendő, ha a fájl mérete nem szabályozható

Amikor elkezd tisztességes mennyiségű adatot begyűjteni a pénzügyi modellbe, nagyon könnyű egy hatalmas Excel-fájlhoz jutni, amelynek kiszámítása hosszú időt vesz igénybe, és megnehezíti az áttekintést vagy a másokkal való megosztást.

Ha a nagy fájlméret a sok sor (mondjuk több mint 100 000) miatt van, akkor fontolja meg a Power Pivot használatát az adatok tárolására.

Ha úgy találja, hogy modellje kicsúszik az irányítás alól (és szabványos Excelt használ, a Power Pivot vagy bármely más bővítmény nélkül), íme néhány jól bevált tipp, amelyek segítségével megtarthatja a fájlméretet. le:

• Távolítson el minden szükségtelen formázást. A színeknek és a formázásnak csak a szükséges tartományra kell vonatkoznia, nem pedig egy teljes sorra vagy oszlopra. Kerülje a kézi formázást, és használja helyette a Stílusokat.

  Ha olyan cellákat töröl, amelyeket már nem használ, ezt valószínűleg úgy teheti meg, hogy kijelöli a cellát, és megnyomja a Delete billentyűt. Ez a művelet törli a tartalmat és a képleteket, de nem törli a formázást. Ha azt gyanítja, hogy ez probléma, a Ctrl+A billentyűkombináció lenyomásával kijelölheti az összes cellát; majd a Szalag Kezdőlap lapjának Szerkesztés részében kattintson a Törlés legördülő menüre, és válassza a Formátumok törlése lehetőséget.

• Győződjön meg arról, hogy a képletek csak arra a tartományra hivatkoznak, amelyre szükségük van (nem a teljes sort vagy oszlopot jelölik ki). Ha a képletek a kelleténél több cellára hivatkoznak, ez több memóriát fog igénybe venni. Használja például a =SZUM(A1:A1000) képletet további sorok engedélyezéséhez az =SZUM(A:A) helyett. Alternatív megoldásként az adatokat táblázatként is formázhatja, és helyette hivatkozhat a képletben automatikusan bővülő táblázattartományokra.
• Távolítson el minden használt logót vagy képet (vagy legalább ellenőrizze a méretét). Egy modellbe beszúrt egyetlen JPG-fájl könnyen hozzáadhat 10 MB-ot az Excel-fájl méretéhez.
• Kerülje a kimutatásokat. A PivotTable-ok nagyon megrágják a memóriát. Ha több kimutatástáblával rendelkezik, győződjön meg arról, hogy ugyanazt az adatforrást használják, és nem hoznak létre egy teljesen újat.
• Távolítsa el a más fájlokra mutató külső hivatkozásokat. Annak ellenőrzéséhez, hogy vannak-e külső hivatkozások a modellben, lépjen a szalag Adatok lapjára, és a Kapcsolatok részben kattintson a Hivatkozások szerkesztése lehetőségre. Ha a Hivatkozások szerkesztése gomb szürkén jelenik meg, az azt jelenti, hogy nincsenek külső hivatkozások. Ha léteznek, kattintson a gombra, és amikor megjelenik a Hivatkozás szerkesztése párbeszédpanel, kattintson a Hivatkozások megszakítása gombra az összes külső hivatkozás adatainak értékként való beillesztéséhez, és a külső hivatkozások eltávolításra kerülnek. Ha feltétlenül külső hivatkozásokat kell használnia, akkor a forrásfájlt egyidejűleg nyissa meg; ez felgyorsítja a dolgokat.
• Ellenőrizze a redundanciákat a bemenetekben és a számításokban. Néha egy modell fejlődik, és előfordulhatnak olyan alkatrészek, amelyeket már nem használnak, vagy olyan alkatrészek, amelyeknél az információ megváltozott.
• Kerülje az illékony funkciókat. A leggyakrabban használt illékony függvények közül néhány az OFFSET, INDIRECT, RAND, NOW, TODAY, ROW és COLUMN. Ezen funkciók túlzott használata a modellben valóban lelassíthatja a számításokat. Ha használnia kell őket, próbálja korlátozni megjelenésüket a modellben. Például a =TODAY() nagyon hasznos a mai dátum megadásához, de ahelyett, hogy többször használná a képletben, egyetlen cellában helyezze el, és folyamatosan hivatkozzon erre az egy cellára a mai dátumhoz.
• Győződjön meg arról, hogy nem XLS fájltípust használ. Az XLSX egy sokkal kompaktabb fájltípus, és óriási különbséget fog látni sebességben és fájlméretben, ha használja. Az XLSB egy Excel bináris munkafüzet-fájltípus, amely még az XLSX-nél is kompaktabb.

Ha már kipróbálta ezeket a tippeket, és továbbra is problémái vannak, fontolja meg a számítás kézire váltását (ezt úgy teheti meg, hogy a Szalagon a Képletek fülre kattint, a Számítás szakaszra lép, és kiválasztja a kézi számítás opciót). Ezután csak akkor nyomja meg az F9 billentyűt, ha újra kell számolnia. Tudni fogja, ha valamit ki kell számítani, mert az állapotsorban megjelenik a Számítás felirat.

Végül, végső megoldásként, egy kis trükk az, hogy az élő hivatkozást tartalmazó oszlop tetején hagyunk egy cellát, és az összes többi cellát értékként illesszük be. Másolja le a cellát, és számolja újra, amikor frissítenie kell. Ez természetesen nem egy előnyben részesített lehetőség, mert időigényes és hajlamos a hibákra, de ezért ez a végső megoldás.