Финансово моделиране в Excel: Какво да направите, ако размерът на файла е извън контрол

Когато започнете да получавате достатъчно количество данни във вашия финансов модел, е доста лесно да се окажете с огромен файл на Excel, който отнема много време за изчисляване, което затруднява прегледа или споделянето с други.

Ако големият размер на файла се дължи на голям брой редове (да речем, повече от 100 000), помислете за използването на Power Pivot за съхраняване на данните вместо това.

Ако установите, че моделът ви излиза извън контрол (и използвате стандартен Excel, без помощта на Power Pivot или други добавки), ето няколко изпитани съвета, които можете да използвате, за да запазите този размер на файла надолу:

• Премахнете ненужното форматиране. Цветовете и форматирането трябва да се прилагат само към необходимия диапазон, а не към цял ред или колона. Избягвайте ръчното форматиране и вместо това използвайте стилове.

  Когато изчиствате клетки, които вече не използвате, вероятно ще направите това, като изберете клетката и натиснете клавиша Delete. Това действие изчиства съдържанието и формулите, но не изчиства форматирането. Ако подозирате, че това е проблем, можете да натиснете Ctrl+A, за да изберете всички клетки; след това в раздела Начало на лентата, в секцията Редактиране, щракнете върху падащото меню Изчистване и изберете Изчистване на форматите.

• Уверете се, че формулите се позовават само на необходимия диапазон (не избират целия ред или колона). Ако вашите формули се позовават на повече клетки, отколкото е необходимо, това ще използва повече памет. Например, използвайте формулата =SUM(A1:A1000), за да разрешите допълнителни редове вместо =SUM(A:A). Като алтернатива можете да форматирате данните като таблица и вместо това да се обърнете към автоматично разширяващите се диапазони на таблицата във вашата формула.
• Премахнете (или поне проверете размера на) всички лога или изображения, които използвате. Един JPG файл, вмъкнат в модел, може лесно да добави 10MB към размера на файла в Excel.
• Избягвайте обобщените таблици. Обобщените таблици наистина дъвчат паметта. Ако имате няколко обобщени таблици, уверете се, че те използват един и същ източник на данни, а не създават изцяло нов.
• Премахнете външни връзки към други файлове. За да проверите дали във вашия модел има външни връзки, отидете на раздела Данни на лентата и щракнете върху Редактиране на връзки в секцията Връзки. Ако бутонът Редактиране на връзки е неактивен, това означава, че няма външни връзки. Ако съществуват, щракнете върху бутона и когато се появи диалоговият прозорец Редактиране на връзка, щракнете върху Прекъсване на връзки, за да поставите данните от всички външни връзки като стойности и външните връзки ще бъдат премахнати. Ако абсолютно трябва да използвате външни връзки, отворете изходния файл по едно и също време; това ще ускори нещата.
• Проверете за излишъци във входните данни и изчисленията. Понякога моделът се развива и може да има части, които вече не се използват, или части, при които информацията се е променила.
• Избягвайте нестабилни функции. Някои от най-често използваните променливи функции са OFFSET, INDIRECT, RAND, СЕГА, ДНЕС, ROW и COLUMN. Прекалената употреба на тези конкретни функции във вашия модел може наистина да забави вашите изчисления. Ако трябва да ги използвате, опитайте се да ограничите появата им в модела. Например, =TODAY() е много полезен за даване на днешна дата, но вместо да го използвате няколко пъти във формула, имайте го в една единствена клетка и непрекъснато препращайте обратно към тази клетка за днешната дата.
• Уверете се, че не използвате файловия тип XLS. XLSX е много по-компактен тип файл и ще видите огромна разлика в скоростта и размера на файла, ако го използвате. XLSB е файл с двоична работна книга на Excel, който е дори по-компактен от XLSX.

Ако сте изпробвали всички тези съвети и все още имате проблеми, помислете за превключване на изчислението към ръчно (можете да направите това, като щракнете върху раздела Формули на лентата, отидете в секцията Изчисление и изберете опцията за ръчно изчисление). След това натиснете F9 само когато трябва да преизчислите. Ще разберете дали нещо трябва да бъде изчислено, защото ще видите Calculate в лентата на състоянието.

И накрая, в краен случай, малък трик е да оставите една клетка в горната част на колоната с връзката на живо и да поставите всички останали клетки като стойности. Копирайте клетката надолу и преизчислите, когато трябва да опресните. Това със сигурност не е предпочитан вариант, защото отнема време и е склонен към грешки, но затова е краен вариант.