- Проблема 1. Використовуваний діапазон листа більше, ніж потрібно
- Проблема 2. Використовується старий формат XLS замість нових XLSX, XLSM і XLSB
- Проблема 3. Надмірне форматування
- Проблема 4. Непотрібні макроси і форми на VBA
- Проблема 5. Іменовані діапазони
- Проблема 6. Фотографії з високою роздільною здатністю і невидимі автофігури
- Проблема 7. Вихідні дані зведених таблиць
- Проблема 8. Журнал змін (логи) в файлі із загальним доступом
- Проблема 9. Багато сміттєвих стилів
- Проблема 10. Багато приміток
Якщо в один прекрасний момент ви усвідомлюєте, що ваш основний робочий файл в Excel набряк до декількох десятків мегабайт і під час відкриття файлу можна сміливо встигнути налити собі каву, то спробуйте пробігтися по описаним нижче пунктам - можливо один або декілька з них укоротять вашого "переростка "до осудних розмірів і розженуть його" гальма ":)
Проблема 1. Використовуваний діапазон листа більше, ніж потрібно
Якщо ваша таблиця займає 5 на 5 осередків, то це аж ніяк не означає, що Excel запам'ятовує при збереженні цього файлу тільки 25 осередків з даними. Якщо ви в минулому використовували будь-які осередки на цьому аркуші, то вони автоматично включаються в використовуваний діапазон (так званий Used Range), який і запам'ятовується при збереженні книги. Проблема в тому, що при очищенні використовуваних осередків Excel далеко не завжди автоматично виключає їх з використовуваного діапазону, тобто починає запам'ятовувати в файлі більше даних, ніж реально є.
Перевірити це просто - натисніть на клавіатурі клавіші Ctrl + End і подивіться куди переміститься активна осередок. Якщо вона стрибне на фактичну останній осередок з даними на аркуші - відмінно. А якщо раптом ускачет сильно правіше і / або нижче "в порожнечу" - справи кепські: всі ці непотрібні порожні клітинки Excel теж запам'ятовує всередині файлу.
Лікується це, тим не менш, досить легко:
- Виділіть першу порожній рядок під вашою таблицею
- Натисніть клавіші Ctrl + Shift + стрілка вниз - виділяться всі порожні рядки до кінця листа.
- Видаліть їх, натиснувши на клавіатурі Ctrl + знак мінус або вибравши на вкладці Основне - Видалити - Видалити рядки з листа (Home - Delete - Delete rows).
- Повторіть те ж саме за допомогою стовпців.
- Повторіть всі вищеописані процедури на кожному аркуші, де при натисненні на Ctrl + End активна осередок переміщається нема на фактичну останній осередок з даними а "в порожнечу" нижче і / або правіше.
- Збережіть файл (обов'язково, інакше зміни не вступлять в силу!)
Якщо у вашій книзі дуже багато таких листів, то простіше, напевно, використовувати короткий макрос.
Проблема 2. Використовується старий формат XLS замість нових XLSX, XLSM і XLSB
Багато років і версій поспіль ще з початку дев'яностих в Excel був один формат файлів - XLS. Це, звичайно, прибирало проблеми сумісності, але, сам по собі, цей формат давно застарів і мав багато неприємних недоліків (великий розмір, непрозорість внутрішньої структури даних, легку пошкоджуваність і т.д.)
Починаючи з Берії Excel 2007 Microsoft ввела нові формати збереження файлів, використання яких помітно полегшує життя і - ваші файли:
- XLSX - по суті є зазіпованний XML. Розмір файлів в такому форматі в порівнянні з Excel 2003 менше, в середньому, в 5-7 разів.
- XLSM - те ж саме, але з підтримкою макросів.
- XLSB - двійковий формат, тобто по суті - щось на зразок скомпільованої XML. Зазвичай в 1.5-2 рази менше, ніж XLSX. Єдиний мінус: немає сумісності з іншими додатками крім Excel, але зате розмір - мінімальний.
Висновок: завжди і всюди, де можна, переходите від старого формату XLS (можливо, що дістався вам "у спадок" від попередніх співробітників) до нових форматів.
Проблема 3. Надмірне форматування
Складне багатобарвне форматування, само-собою, негативно відбивається на розмірі вашого файлу. А умовне форматування ще й відчутно уповільнює його роботу, тому що змушує Excel перераховувати умови і оновлювати форматування при кожному чханні.
Залиште тільки найнеобхідніше, не ухитрялися. Особливо в тих таблицях, які крім вас ніхто не бачить. Для видалення тільки форматів (без втрати вмісту!) Виділіть комірки і виберіть у спадному списку Очистити - Очистити формати (Clear - Clear Formats) на вкладці Основне (Home):
Особливо "завантажують" файл відформатовані цілком рядки і стовпці. Оскільки розмір листа в останніх версіях Excel сильно збільшений (> 1 млн. рядків і> 16 тис. стовпців), то для запам'ятовування і обрабокі подібного форматування потрібно багато ресурсів. В Excel 2013-2016, до речі, з'явилася надбудова Inquire, яка містить інструмент для швидкого позбавлення від подібних надмірностей - кнопку Видалити надмірне форматування (Clean Excess Cell Formatting):
Вона миттєво видаляє всі зайве форматування до кінця листа, залишаючи його тільки всередині ваших таблиць і ніяк не пошкоджуючи ваші дані. Причому може це зробити навіть для всіх листів книги відразу.
Якщо ви не бачите у себе в інтерфейсі вкладку Inquire, то її необхідно підключити на вкладці Розробник - Надбудови COM (Developer - COM Addins).
Проблема 4. Непотрібні макроси і форми на VBA
Великі макроси на Visual Basic і особливо призначені для користувача форми із запровадженою графікою можуть досить помітно обтяжувати вашу книгу. Для видалення:
- натисніть Alt + F11, щоб увійти в редактор Visual Basic
- знайдіть вікно Project Explorer'а (якщо його не видно, то виберіть в меню View - Project Explorer)
- видаліть всі модулі і все форми (правою кнопкою миші - Remove - далі у вікні з питанням про експорті перед видаленням - No):
Також код може міститися в модулях листів - перевірте їх теж. Також можна просто зберегти файл у форматі XLSX без підтримки макросів - все макроси і форми помруть автоматично. Також можна скористатися інструментом Очистити книгу від макросів з надбудови PLEX .
Проблема 5. Іменовані діапазони
Якщо у вашому файлі використовуються іменовані діапазони (особливо з формулами, динамічні або одержувані при фільтрації), то має сенс від них відмовитися на користь економії розміру книги. Подивитися список наявних діапазонів можна натиснувши Ctrl + F3 або відкривши вікно Диспетчера імен (Name Manager) на вкладці Формули (Formulas):
Також вичищати іменовані діапазони з помилками (їх можна швидко відібрати за допомогою кнопки Фільтр в правому верхньому куті цього вікна) - вони вам точно не знадобляться.
Проблема 6. Фотографії з високою роздільною здатністю і невидимі автофігури
Якщо мова йде про фотографії, доданих до книги (особливо коли їх багато, наприклад в каталозі продукції), то вони, само-собою, збільшують розмір файлу. Раджу стискати їх, зменшуючи дозвіл до 96-150 точок на дюйм. На екрані за якістю це абсолютно не відчувається, а розмір файлу зменшує в рази. Для стиснення скористайтеся кнопкою Стиснути малюнки (Compress pictures) на вкладці Формат (Format):
Крім видимих картинок на аркуші можуть міститися і невидимі зображення (малюнки, фотографії, автофігури). Щоб побачити їх, виділіть будь-яку картинку і на вкладці Формат (Format) натисніть кнопку Область виділення (Selection Pane).
Для видалення взагалі всіх графічних об'єктів на поточному аркуші можна використовувати простий макрос:
Sub Delete_All_Pictures () 'макрос для видалення всіх картинок на поточному аркуші Dim objPic As Shape For Each objPic In ActiveSheet.Shapes objPic.Delete Next objPic End Sub
Проблема 7. Вихідні дані зведених таблиць
За замовчуванням Excel зберігає дані для розрахунку зведеної таблиці (pivot cache) всередині файлу. Можна відмовитися від цієї можливості, помітно скоротивши розмір файлу, але збільшивши час на оновлення зведеної при наступному відкритті книги. Клацніть правою кнопкою миші по зведеної таблиці і виберіть команду Властивості таблиці (Pivot Table Properties) - вкладка Дані (Data) - зняти прапорець Зберігати вихідні дані разом з файлом (Save source data with file):
Якщо у вас кілька зведених таблиць на основі одного діапазону даних, то скоротити розмір файлу здорово допомагає метод, коли всі зведені таблиці після першої будуються на основі вже створеного для першої таблиці кеша. В Excel 2000-2003 це робиться вибором перемикача на першому кроці Майстра зведених таблиць при побудові:
В Excel 2007-2016 кнопку Майстра зведених таблиць потрібно додавати на панель вручну - на стрічці такої команди немає. Для цього клацніть по панелі швидкого доступу правою кнопкою миші і виберіть Налаштування панелі швидкого доступу (Customize Quick Access Toolbar) і потім знайдіть в повному списку команд кнопку Майстер зведених таблиць (PivotTable and PivotChart Wizard):
Проблема 8. Журнал змін (логи) в файлі із загальним доступом
Якщо у вашому файлі включений загальний доступ на вкладці Рецензування - Доступ до книги (Review - Share Workbook), то всередині вашого файлу Excel на спеціальному прихованому аркуші починає зберігатися вся історія змін документа: хто, коли і як міняв осередки всіх листів. За замовчуванням, такий журнал зберігає дані змін за останні 30 днів, тобто при активній роботі з файлом, може запросто займати кілька мегабайт.
Мораль: не використовуйте загальний доступ без необхідності або скоротіть кількість днів зберігання даних журналу, використовуючи другу вкладку Детальніше (Advanced) у вікні Доступ до книги. Там можна знайти параметр Зберігати журнал змін протягом N днів (Keep change history for N days) або зовсім відключити його:
Проблема 9. Багато сміттєвих стилів
Про цю капость я вже докладно писав раніше в статті про те, як перемогти помилку "Занадто багато форматів осередків" . Суть, якщо коротко, в тому, що якщо ви розвертаєте на вкладці Основне список Стилі комірок (Home - Cell Styles) і бачите там дуже багато незрозумілих і непотрібних стилів, то це погано - і для розміру вашого файлу Excel і для його швидкодії.
Видалити непотрібні стилі можна за допомогою макросу або готової команди з надбудови PLEX.
Проблема 10. Багато приміток
Примітки до осередків, звичайно, не самий шкідливий момент з усіх перерахованих. Але деякі файли можуть містити велику кількість тексту або навіть картинок в примітках до осередків . Якщо примітки не містять корисної для вас інформації, то їх можна легко видалити за допомогою команди на вкладці Основне - Очистити - Очистити примітки (Home - Clear - Clear Comments).