Excel. Борьба со вздутием и тяжестью.⁠⁠

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

Едем дальше.

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

1. Диапазоны

Вот вы видите перед собой таблицу, 10 на 10 ячеек, и вам кажется, что Excel запоминает при сохранении этого файла только 100 ячеек с данными. Спешу вас расстроить. Это как с сурком. Ты его не видишь, а он есть.

Дело в том, что вы когда-то могли использовать какие-то ячейки на этом листе, а теперь они автоматически попадают  используемый диапазон (он же Used Range), который excel и запоминает при сохранении.

Нажатие Ctrl+End перенесет вас на последнюю используемую ячейку, и если это будет фактическая последняя ячейка с данными в вашей таблице, то ура-ура, а если сильно далеко вправо/вниз - то вот вся эта пустота паразитирует и жрет мегабайты

Как избавиться от паразитов:

1. Встаем на первую пустую строку под таблицей

2. Нажмите Ctrl+Shift+стрелка вниз – (таким шагом выделим все пустые строки до конца листа)

3. Удаляем нещадно любым любимым способом (Ctrl+знак минус /Главная – Удалить – Удалить строки с листа./ через контекстное меню)

4. Та же операция для столбцов

5. Помните, что это стоит проверить на каждом листе.

6. Сохраните (обязательно, иначе волшебство не произойдет!)

2. Форматы

Очень часто вижу, что клиенты по привычке используют формат  - XLS. Да- классика, да -меньше проблем с совместимостью, да - привычка. Но устарел он крепко.

Что есть вкусненького, облегчающего жизнь и ваши файлы:

• XLSX - по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.

• XLSM - то же самое, но с поддержкой макросов.

• XLSB - двоичный формат, т.е. по сути - что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер - минимален.

Вывод: всегда и везде, где можно, переходите от старого формата XLS (возможно, доставшегося вам "по наследству" от коллег) к новым форматам.

Мой любимый формат – XLSB, советую.

Пример:

Имеем небольшой файл такого вида

Сохраняем тремя разными способами

Даже на таком небольшом файле разница очевидна.

3. Автоматический пересчет формул

Если у вас в файле много формул, которые пытаются себя пересчитать при каждом минимальном изменении, то вам может быть полезно зайти в настройки (Файл – Параметры - Формулы) и поставить галочку на ручном пересчете формул:

Да, вариант не самый изящный, но действенный.

Дальше важно помнить, что теперь файл будет пересчитываться только при сохранении файла и нажатии F9 на клавиатуре. (Так что при отправке файла кому-то, лучше вернуть настройку, чтобы не было мучительно больно.)

4. Избыточное форматирование

Раскрашенный файл становится красивым, но менее умным. А если еще и сильно побаловаться условным форматированием, то вынудим Excel пересчитывать условия и обновлять форматирование при каждом чихе.

Как говорила моя незабвенная преподавательница русского языка и литературы: "прощЕе всегда лучшЕе", поэтому оставьте только самое необходимое, не изощряйтесь. Особенно в тех таблицах, которые кроме вас никто не видит. Для удаления только форматов (без потери содержимого!) выделите ячейки и выберите в выпадающем списке Очистить - Очистить форматы на вкладке Главная.

Сильно  "загружают" файл залитые целиком строки и столбцы, т.к. размер листа в последних версиях Excel сильно увеличен (>1 млн. строк и >16 тыс. столбцов)

Часто наблюдала у клиентов, когда для красоты, сотрудники выделяли весь лист и заливали белым цветом, мол полосочки раздражали. Для этих целей лучше Лучше на вкладке Вид отщелкнуть Сетку.

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

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

Отсюда вывод: без необходимости не пользуемся общим доступом или сокращаем/отключаем количество дней хранения данных журнала (вкладка Подробнее в окне Доступ к книге.)

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

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

• Именованные диапазоны

• Фотографии и невидимые автофигуры

• Много примечаний

Всем добра и уютного Excel.