Как уменьшить размер файла и ускорить его

Если в один прекрасный момент вы осознаете, что ваш основной рабочий файл в Excel разбух до нескольких десятков мегабайт и во время открытия файла можно смело успеть налить себе кофе, то попробуйте пробежаться по описанным ниже пунктам - возможно один или несколько из них укоротят вашего "переростка" до вменяемых размеров и разгонят его "тормоза" :)

Проблема 1. Используемый диапазон листа больше, чем нужно

Если ваша таблица занимает 5 на 5 ячеек, то это отнюдь не означает, что Excel запоминает при сохранении этого файла только 25 ячеек с данными. Если вы в прошлом использовали какие-либо ячейки на этом листе, то они автоматически включаются в используемый диапазон (так называемый Used Range), который и запоминается при сохранении книги. Проблема в том, что при очистке используемых ячеек Excel далеко не всегда автоматически исключает их из используемого диапазона, т.е. начинает запоминать в файле больше данных, чем реально имеется. 

Проверить это просто – нажмите на клавиатуре сочетание клавиш Ctrl+End и посмотрите куда переместится активная ячейка. Если она прыгнет на фактическую последнюю ячейку с данными на листе – отлично. А если вдруг ускачет сильно правее и/или ниже "в пустоту" – дело плохо: все эти ненужные пустые ячейки Excel тоже запоминает внутри файла.

Лечится это, тем не менее, достаточно легко:

  1. Выделите первую пустую строку под вашей таблицей
  2. Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
  3. Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows).
  4. Повторите то же самое со столбцами.
  5. Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а "в пустоту" ниже и/или правее.
  6. Сохраните файл (обязательно, иначе изменения не вступят в силу!)

Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.

Проблема 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):

reduce_size5.gif

Особенно "загружают" файл отформатированные целиком строки и столбцы. Т.к. размер листа в последних версиях Excel сильно увеличен (>1 млн. строк и >16 тыс. столбцов), то для запоминания и обрабоки подобного форматирования нужно много ресурсов. В Excel 2013-2016, кстати, появилась надстройка Inquire, которая содержит инструмент для быстрого избавления от подобных излишеств - кнопку Удалить избыточное форматирование (Clean Excess Cell Formatting):

reduce_size9.png

Она мгновенно удаляет все излишнее форматирование до конца листа, оставляя его только внутри ваших таблиц и никак не повреждая ваши данные. Причем может это сделать даже для всех листов книги сразу.

Если вы не видите у себя в интерфейсе вкладку Inquire, то ее необходимо подключить на вкладке Разработчик - Надстройки COM (Developer - COM Addins).

Проблема 4. Ненужные макросы и формы на VBA

Большие макросы на Visual Basic и особенно пользовательские формы с внедренной графикой могут весьма заметно утяжелять вашу книгу. Для удаления:

  1. нажмите Alt+F11, чтобы войти в редактор Visual Basic
  2. найдите окно Project Explorer’а (если его не видно, то выберите в меню View - Project Explorer)
  3. удалите все модули и все формы (правой кнопкой мыши - Remove - дальше в окне с вопросом о экспорте перед удалением - No):

reduce_size4.gif

Также код может содержаться в модулях листов - проверьте их тоже. Также можно просто сохранить файл в формате XLSX без поддержки макросов - все макросы и формы умрут автоматически. Также можно воспользоваться инструментом Очистить книгу от макросов из надстройки PLEX.

Проблема 5. Именованные диапазоны

Если в вашем файле используются именованные диапазоны (особенно с формулами, динамические или получаемые при фильтрации), то имеет смысл от них отказаться в пользу экономии размера книги. Посмотреть список имеющихся диапазонов можно нажав Ctrl+F3 или открыв окно Диспетчера имен (Name Manager) на вкладке Формулы (Formulas):

reduce_size3.gif

Также вычищайте именованные диапазоны с ошибками (их можно быстро отобрать с помощью кнопки Фильтр в правом верхнем углу этого окна) - они вам точно не пригодятся.

Проблема 6. Фотографии высокого разрешения и невидимые автофигуры

Если речь идет о фотографиях, добавленных в книгу (особенно когда их много, например в каталоге продукции), то они, само-собой, увеличивают размер файла. Советую сжимать их, уменьшая разрешение до 96-150 точек на дюйм. На экране по качеству это совершенно не чувствуется, а размер файла уменьшает в разы. Для сжатия воспользуйтесь кнопкой Сжать рисунки (Compress pictures) на вкладке Формат (Format):

reduce_size1.gif

Кроме видимых картинок на листе могут содержаться и невидимые изображения (рисунки, фотографии, автофигуры). Чтобы увидеть их, выделите любую картинку и на вкладке Формат (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):

reduce_size2.gif

Если у вас несколько сводных таблиц на основе одного диапазона данных, то сократить размер файла здорово помогает метод, когда все сводные таблицы после первой строятся на основе уже созданного для первой таблицы кэша. В Excel 2000-2003 это делается выбором переключателя на первом шаге Мастера сводных таблиц при построении:

reduce_size6.gif

В Excel 2007-2016 кнопку Мастера сводных таблиц нужно добавлять на панель вручную - на ленте такой команды нет. Для этого щелкните по панели быстрого доступа правой кнопкой мыши и выберите Настройка панели быстрого доступа (Customize Quick Access Toolbar) и затем найдите в полном списке команд кнопку Мастер сводных таблиц (PivotTable and PivotChart Wizard):

reduce_size7.gif

Проблема 8. Журнал изменений (логи) в файле с общим доступом

Если в вашем файле включен общий доступ на вкладке Рецензирование - Доступ к книге (Review - Share Workbook), то внутри вашего файла Excel на специальном скрытом листе начинает сохраняться вся история изменений документа: кто, когда и как менял ячейки всех листов. По умолчанию, такой журнал сохраняет данные изменений за последние 30 дней, т.е. при активной работе с файлом, может запросто занимать несколько мегабайт.

Мораль: не используйте общий доступ без необходимости или сократите количество дней хранения данных журнала, используя вторую вкладку Подробнее (Advanced) в окне Доступ к книге. Там можно найти параметр Хранить журнал изменений в течение N дней (Keep change history for N days) или совсем отключить его:

reduce_size8.png

Проблема 9. Много мусорных стилей

Про эту пакость я уже подробно писал ранее в статье о том, как победить ошибку "Слишком много форматов ячеек". Суть, если кратко, в том, что если вы разворачиваете на вкладке Главная список Стили ячеек (Home - Cell Styles) и видите там очень много непонятных и ненужных стилей, то это плохо - и для размера вашего файла Excel и для его быстродействия.

too-many-formats2.png

Удалить ненужные стили можно с помощью макроса или готовой команды из надстройки PLEX.

Проблема 10. Много примечаний

Примечания к ячейкам, конечно, не самый вредный момент из всех перечисленных. Но некоторые файлы могут содержать большое количество текста или даже картинок в примечаниях к ячейкам. Если  примечания не содержат полезной для вас информации, то их можно легко удалить с помощью команды на вкладке Главная - Очистить - Очистить примечания (Home - Clear - Clear Comments).



31.01.2013 17:24:59
Отличная статья! Расширил свои знания! Сохранение в двоичный формат постояно использую.
06.03.2013 01:31:16
а я рекомендую старый формат XLS, т.к. постоянно и везде использую связи с Word  и AutoCAD  и они очень не любят сжатые файлы  XLSX. Именно связи с разными приложениями позволяют делать сложные рабочие проекты. Жаль, что и MicroSoft и Autodesk полностью игнорируют тему OLE-link и занимаются ерундой (типа облачных технологий) и другими украшательствами.
08.03.2013 15:34:43
Не соглашусь. Новый формат XLSX в разы лучше старого, который Microsoft как чемодан без ручки тащило через все версии ради совместимости больше десяти лет. Новый формат - это открытый OpenXML, с которым внешним программам работать на порядок легче, чем с закрытым XLS. Новый формат устойчивее к повреждениям и компактнее.
04.05.2013 07:40:35
Спасибо , Николай! Буду использовать формат XLSB при работе с кубами!!!
18.07.2016 23:30:55
Кстати, замечено, что XLSB гораздо менее надёжен при сохранении файлов (особенно частом). Я как-то потерял рабочий день из-за этого формата. До закрытия Excel файл XLSB многократно сохранялся без каких-либо проблем, а после переоткрытия Excel (на следующее утро) сказал, что файл повреждён и восстановлению не подлежит.
17.03.2014 02:30:34
14.05.2014 15:58:30
Здравствуйте!подскажите,пожалуйста,как быть, если в надстройках для модели компонентных объектов (COM) нет надстройки Inquire?(((:( заранее спасибо;)
14.05.2014 17:55:30
А версия Excel какая?
15.05.2014 08:05:49
15.05.2014 08:11:46
в надстройках вообще нет никаких надстроек,в том числе и Inquire((
15.05.2014 10:51:41
Возможно, это просто не полная, а частичная установка. Попробуйте в Панели управления - Установка и удаление программ найти Office и нажать кнопку Изменить. Может быть не хватает галочки на каких-то компонентах Excel?
15.05.2014 10:58:47
немаловероятно)но я так никогда и не узнаю,ибо нет у меня прав администратора(((....но все равно спасибо за подсказку:oops::)
10.07.2014 10:45:26
Друзья, а строки не удаляются. Точней они удаляются, но все равно после этого Ctrl+End перемещает курсор на ту же ячейку, что показал и до удаления строк...
Оксана, как правило надо еще и закрыть файл и открыть заново. Тогда все вступит в силу.
27.11.2014 17:56:17
У меня аналогичная ситуация. Удаляю строки, сохраняю, закрываю, открываю снова - та же картина. Ну и размер файла, конечно, не меняется. В чем может быть дело? Excel 2010
26.02.2015 17:14:00
:o Первый способ помог уменьшить таблицу с 9 Мб до 14 Кб.....
06.04.2015 10:31:12
Хотелось бы поделиться небольшим лайфхаком.

Можно сконвертировать как было сказано в статье - в бинарный XLSB, сохранить, и сразу же сохранить обратно в XLSX или XLSM. Финальный размер книги уменьшится значительно, в моем лучае XLSM (4,4мб) -> XLSB (1,9мб) -> XLSM (2,4мб)
16.12.2015 09:34:04
Интересно, попробуем - спасибо!
30.12.2015 12:00:34
Почему-то не получается.
Есть исходный файл Тест.xlsx (11 772 кб). Сохранить как в xlsb --> становится 542 кб. Пересохраняюсь обратно в xlsx, но размер остаётся тем же - 11 772 кб.
15.12.2015 14:53:13
Николай, добрый день!
Давно посещаю Ваш сайт но только решила задать вопросы. )))

Возможно ли прописать макрос на уменьшение файла?
16.12.2015 09:38:01
Татьяна, смелее! ;)

А что этот макрос должен делать?
Макрос, который удаляет лишние пустые строки (см. первый пункт этой статьи), например, давно есть в надстройке PLEX в Менеджере Листов.
19.02.2016 13:07:03
Здравствуйте, Николай.
Скажите пожалуйста, как насчет связи с внешними источниками, гиперссылки например, стоит ли от них избавляться или они незначительно утяжеляют книгу?
28.04.2016 13:14:01
Николай, спасибо за видео.
Углядел у вас на видео плагин для VBA - Rubberduck, - ранее про такой не слышал (сейчас вот установил, - пытаюсь найти в нём что-нибудь полезное)
10.05.2016 09:21:59
Николай, здравствуйте!
Вопрос по проблеме 2: Вы пишите- " Единственный минус: нет совместимости с другими приложениями кроме Excel ", можно ли обновлять таблицу из Access в Excel в формате XLSB, загрузится ли она корректно (обновляю таблицу в Excel через сохраненный запрос,Excel 2016) ?
С уважением, Зураб.
20.06.2016 15:09:35
Николай подскажите, а можно ли изменить источник для всех уже созданных сводных таблиц ?
у меня есть 2 странички с данными и 8-9 страниц презентационных материалов которые созданы с помощью кучи сводных таблиц.
Я так понимаю что можно сильно сократить объем файла если воспользоваться пунктом 7 ваших рекомендаций. Только заново их все пересоздавать не хочется есть ли возможность построить их все на основе данных кэша первой без перестройки ? ( Excel 2007)  
11.07.2016 12:54:32
Добрый день! Хорошая статья. У меня как раз проблема с "жирным файлом". Но все же не могу решить проблему. Суть в след: на листе проверяю кол-во ячеек используемых. нажал Ctrl+End и... о боже 1 039 971 строка. Далее если нажать Shift+Ctrl+вниз и потом нажать "Удалить" то комп благополучно просто зависает. ( видимо для него это нереальный труд). Решил попробовать удалить строки частично штук по 100 по 200 например. Выделяю, удаляю 200 строк. опять нажимаю Ctrl+End и он опять показывает мне 1 039 971 строк. Как так? что делать? Помогите сбросить вес.:o
12.07.2016 17:26:38
Алексей! Предлагаю скопировать и перенести с этого листа все нужные данные (формулы, данные, форматы) на новый лист,  и старый  лист удалить совсем, предварительно сделайте копию файла.
22.09.2016 21:16:02
Оставлю это тут
Ссылка на скачать файл не работает
к Проблеме №9

Sub УдалениеСтилей()
'Макрос для удаление лишних стилей
Dim Стиль As Style
For Each Стиль In ActiveWorkbook.Styles
    On Error Resume Next
    If Not Стиль.BuiltIn Then Стиль.Delete
    On Error GoTo 0
Next

End Sub
23.03.2017 13:31:45
Подскажите, пожалуйста, в сравнительном отношении файл весом 22 737 Кб имеющий основную вкладку с данными в 62 507 строк и 35 столбцов, плюс справочник, откуда подтягиваются ВПРми данные в основную многострочную вкладку и пару сводных таблиц имеет разумное соотношение объема информации и веса, или слишком тяжелый?
и еще, втростепенный вопрос, насколько тяжелее и медленнее делают файл гиперссылки? Например, если в базе данных на 2 000 строк и 15 столбцов, тоже с ВПР  и справочником сделать гиперссылки на сканы самих документов, которые занесены в базу, файл умрет?
26.03.2017 20:41:42
Алеся,
По ощущениям, тяжеловато, но надо смотреть на файл, конечно. И вообще, если компьютер мощный, то и 50 Мб неоптимизированный файл может "летать", а если старенький, то и с 5 Мб книгой могут быть тормоза жуткие. Главное - комфортно ли вам с файлом работать.

По поводу гиперссылок - не особо влияют, ибо хранятся как обычный текст, практически.
27.03.2017 09:39:45
Большое спасибо за ответ!
24.10.2017 14:10:17
Подскажите пожалуйста.
Есть файл в котором обрабатывается база данных, в нём прописано много массивных гипер формул (впр, поиск поз, если и тд)

Если эти мегаформулы перенсти в макрос а в ячейках вбить формулу макроса, файл будет обрабатываться быстрее?
24.11.2017 07:34:24
А почему могут не удаляться столбцы? У меня через PQ грузятся на лист данные. Выделяю лишние столбцы справа и удаляю, но потом все равно ставится последняя ячейка в этом столбце.
12.02.2018 13:04:40
Специально зарегистрировалась, чтобы сказать большое спасибо! Не все для меня понятно, но цели своей добилась благодаря первому пункту - с 64 МБ до 280 КБ!!! Да, еще на одном листе никак не удалялись строки и столбцы, скопировала таблицу, опять же как Вы советовали, на новый лист и все нормально! Проверю остальные файлы. Спасибо!
25.07.2018 12:29:13
Николай, благодарю за прекрасную статью! Столько разных инсайтов толкьо в одной теме
13.01.2019 19:35:34
Николай, подскажите пожалуйста, есть ли шанс на Мас работать с файлом 1Гб. Вроде сейчас возможны такие большие таблицы, но на РС, есть какая-то надстройка. А у меня Мас, Офис 365, таблица весом 380 Мб (много картинок, я их конечно же уменьшила), но мой ноутбук её уже не тянет, а она мне нужна еще больших размеров...
Наверх