Фінансове моделювання в Excel: що робити, якщо розмір файлу вийшов з-під контролю

Коли ви починаєте отримувати достатню кількість даних у своїй фінансовій моделі, досить легко отримати величезний файл Excel, обчислення якого займає багато часу, що ускладнює перегляд або обмін з іншими.

Якщо великий розмір файлу пов’язаний із великою кількістю рядків (скажімо, понад 100 000), подумайте про використання Power Pivot для зберігання даних.

Якщо ви помітили, що ваша модель виходить з-під контролю (і ви використовуєте стандартний Excel без допомоги Power Pivot або будь-яких інших надбудов), ось кілька перевірених порад, які ви можете використовувати, щоб зберегти цей розмір файлу. вниз:

• Видаліть непотрібне форматування. Кольори та форматування мають застосовуватися лише до необхідного діапазону, а не до всього рядка чи стовпця. Уникайте ручного форматування та використовуйте стилі.

  Коли ви очищаєте клітинки, які більше не використовуєте, ви, ймовірно, робите це, виділивши клітинку та натиснувши клавішу Delete. Ця дія очищає вміст і формули, але не очищає форматування. Якщо ви підозрюєте, що це проблема, ви можете натиснути Ctrl+A, щоб виділити всі клітинки; потім на вкладці «Головна» стрічки в розділі «Редагування» клацніть спадне меню «Очистити» та виберіть «Очистити формати».

• Переконайтеся, що формули посилаються лише на потрібний діапазон (не вибирають весь рядок чи стовпець). Якщо ваші формули посилаються на більше клітинок, ніж їм потрібно, це буде використовувати більше пам’яті. Наприклад, скористайтеся формулою =SUM(A1:A1000), щоб додати додаткові рядки замість =SUM(A:A). Крім того, ви можете відформатувати дані як таблицю і натомість посилатися на діапазони таблиці, що автоматично розширюються, у вашій формулі.
• Видаліть (або принаймні перевірте розмір) будь-які логотипи чи зображення, які ви використовуєте. Один файл JPG, вставлений в модель, може легко додати 10 МБ до розміру файлу Excel.
• Уникайте зведених таблиць. Зведені таблиці дійсно пережовують пам'ять. Якщо у вас є кілька зведених таблиць, переконайтеся, що вони використовують те саме джерело даних, а не створюють зовсім нове.
• Видаліть зовнішні посилання на інші файли. Щоб перевірити, чи є у вашій моделі зовнішні посилання, перейдіть на вкладку «Дані» на стрічці та натисніть «Редагувати посилання» в розділі «З’єднання». Якщо кнопка Редагувати посилання неактивна, це означає, що зовнішніх посилань немає. Якщо вони існують, натисніть кнопку та, коли з’явиться діалогове вікно «Редагувати посилання», натисніть «Розривати посилання», щоб вставити дані з усіх зовнішніх посилань як значення, і зовнішні посилання будуть видалені. Якщо вам абсолютно необхідно використовувати зовнішні посилання, відкрийте вихідний файл одночасно; це пришвидшить справу.
• Перевірте наявність надмірностей у вводах і розрахунках. Іноді модель розвивається, і можуть бути частини, які більше не використовуються, або частини, де інформація змінилася.
• Уникайте змінних функцій. Деякі з найбільш часто використовуваних змінних функцій: OFFSET, INDIRECT, RAND, NOW, TODAY, ROW і COLUMN. Надмірне використання цих функцій у вашій моделі може справді уповільнити ваші обчислення. Якщо вам потрібно їх використовувати, постарайтеся обмежити їх появу в моделі. Наприклад, =TODAY() дуже корисний для надання сьогоднішньої дати, але замість того, щоб використовувати його кілька разів у формулі, розмістіть його в одній клітинці і постійно посилайтеся на цю клітинку для сьогоднішньої дати.
• Переконайтеся, що ви не використовуєте тип файлу XLS. XLSX є набагато компактнішим типом файлу, і ви побачите величезну різницю в швидкості та розмірі файлу, якщо ви його використовуєте. XLSB — це тип файлу двійкової книги Excel, який навіть компактніший, ніж XLSX.

Якщо ви спробували всі ці поради, але у вас все ще виникають проблеми, подумайте про перехід на ручний розрахунок (це можна зробити, натиснувши вкладку «Формули» на стрічці, перейшовши до розділу «Розрахунок» і вибравши параметр «Ручне обчислення»). Тоді натискайте F9 лише тоді, коли потрібно перерахувати. Ви дізнаєтеся, чи потрібно щось обчислити, оскільки ви побачите Розрахувати в рядку стану.

Нарешті, в крайньому випадку, невелика хитрість полягає в тому, щоб залишити одну клітинку у верхній частині стовпця з живим посиланням і вставити всі інші клітинки як значення. Скопіюйте клітинку вниз і перерахуйте, коли вам потрібно оновити. Це, безумовно, не найкращий варіант, оскільки він займає багато часу і схильний до помилок, але саме тому це крайній засіб.