Как уменьшить размер файла Excel и заставить его работать быстрее
Каждый, кто часто имеет дело с Excel и созданными в нем документами, рано или поздно сталкивается с проблемой медленной работы файла. «Файл тормозит, что делать?» Такой вопрос часто можно увидеть на форумах, тематических сайтах или просто в списке запросов поисковика. Чаще всего подобная проблема сопровождается гипертрофированными размерами книги Excel. Из этой статьи вы узнаете несколько самых действенных способов уменьшения размера файла и ускорения его работы.
1. Уменьшаем размер используемого диапазона листа
Наиболее часто проблема увеличения размера книги и ее торможения бывает связана с разросшимся используемым диапазоном листа. Это тот диапазон, который Excel запоминает и с которым каждый раз работает. Если вы используете на листе всего несколько ячеек, но когда то у вас там была таблица в 1000 строк, то даже после ее удаления Excel будет обрабатывать все эти строки, тем самым замедляя работу файла.
Чтобы проверить, есть ли на листе лишние пустые столбцы и строки нужно нажать сочетание клавиш «Ctrl+End». Вы попадете в последнюю ячейку, которую использует программа. Если она явно за пределами ваших данных, то лишние строки и столбцы стоит удалить. Для этого в столбце А встаем в ячейку ниже последней нужной нам строки и нажимаем «Ctrl+Shift+End».
Выделятся все лишние строки. Удаляем их. То же самое повторяем для столбцов. Если их много – файл может зависнуть. В таком случае проще использовать небольшой макрос. Нажмите Alt+F11 или кнопку Visual Basic на вкладке Разработчик (как ее активировать - показывали здесь), вставьте новый модуль через меню Insert - Module и скопируйте туда код макроса:
Sub УдалениеЛишнихЯчеек()
For Each Sht In ActiveWorkbook.Worksheets
Sht.UsedRange
Next Sht
End Sub
Чтобы запустить его - на той же вкладке (1) нажмите кнопку "Макросы" (2), выберите в списке "УдалениеЛишнихЯчеек" (3) и нажмите кнопку "Выполнить" (4).
После проделывания всех операций обязательно сохраняем книгу.
2. Пересохраняем файл в другом формате
Если кто-то еще пользуется файлами в старом формате XLS, но уже сидит на более новом ПО (Excel 2007 и новее), то есть смысл пересохранить файл в один из новых форматов: XLSX, XLSM, XLSB. Они более современные, лучше оптимизированы, весят меньше и работают быстрее. Самый компактный из них – XSLB. При сохранении в этом формате размер файла существенно уменьшится (даже если пересохранить в него "новый" XLSX).
3. Удаляем лишние объекты из книги
Часто при копировании в файл данных из сторонних программ (например, 1С) вместе с ними копируются лишние объекты (фигуры, картинки и прочее), которые не всегда можно сразу заметить на листе. Чтобы проверить, есть ли в файле лишние объекты, нужно найти на ленте команду "Найти и выделить" (1) и выбрать "Область выделения" (2). Откроется список объектов листа (3).
Удалить все объекты можно выделив их и нажав клавишу Delete. Чтобы выделить все объекты снова используем команду "Найти и выделить" (1), выбираем пункт "Выделить группу ячеек" (2) и в открывшемся окне выбираем "Объекты" (3).
Если объектов много, то имеет смысл удалять их простым макросом. Код - ниже. Его нужно запустить поочередно на каждом листе.
Sub УдаляемОбъекты()
For each shp in ActiveSheet.Shapes
shp.delete
Next shp
End Sub
4. Уменьшаем размер сводных таблиц
Если в вашем файле есть сводные таблицы, то имеет смысл отключить хранение данных для них вместе с файлом. Это потребует обновления таблицы каждый раз при открытии книги, но уменьшит размер файла. Для отключения этой опции выберите нужную сводную таблицу, перейдите в ее Параметры (правая кнопка мыши - Параметры сводной таблицы) и на вкладке Данные (1) снимите галочку с пункта "Сохранять исходные данные вместе с файлом" (2).
5. Заменяем формулы на значения
Если в файле содержится очень много формул, то нужно постараться везде, где это возможно, заменить их на значения. Постоянный пересчет сильно замедляет работу файла. И хоть его можно отключить (как - смотри здесь), рекомендуем избавляться ото всех лишних формул на листах.
6. Удаляем лишнее форматирование
Красивые документы нравятся всем, но чем более пёстрый лист у Вас получился, тем медленнее будет работать файл. А условное форматирование сказывается на быстродействии еще больше, так как основано на формулах и постоянно пересчитывается. Рекомендуем удалять все лишние форматы, оставляя только то, что действительно нужно (как очистить форматы - смотри здесь). Минимализм сейчас в моде.
Чтобы удалить лишние правила условного форматирования выбираем на вкладке "Главная" инструмент "Условное форматирование", кнопка "Управление правилами". В открывшемся диспетчере выбираем весь лист (1), выделяем лишнее правило (2) и удаляем его (3). Повторяем, пока не удалим всё лишнее.
И помните - никогда не используйте формат целиком для всего столбца или строки!
7. Удаляем лишние имена
Именованные диапазоны используют далеко не все, но обнаружить их в своем файле может каждый (например, они могли попасть туда при импорте или копировании данных). Весь список можно увидеть в диспетчере имен ("Формулы" - "Диспетчер имен"). Там же можно и удалить все ненужные имена.
Иногда в книге могут оказаться скрытые имена. Для их удаления придется воспользоваться небольшим макросом.
Sub УдалениеСкрытыхИмен()
For Each Nm In ActiveWorkbook.Names
If Nm.Visible = 0 Then
Nm.Delete
End If
Next Nm
End Sub
Заключение
Предложенные выше способы помогают решить подавляющее большинство "решаемых" проблем с быстродействием и размером книги. Однако, помните - если ваш рабочий файл содержит множество сводных таблиц, сложных формул, связи с другими документами, диаграммы и условные форматы - то вполне может быть, что и после всех проделанных операций он будет работать медленно и весить много. Это естественно, так как вычислительные способности компьютеров и самого Excel не безграничны. В таких случаях стоит задуматься о том, чтобы оптимизировать саму логику работы файла, подумать над перестраиванием всей модели вычислений. Если такая необходимость возникла, но самостоятельно решение придумать не удалось - можете обращаться к нам.
Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
С уважением, команда tDots.ru