Трюк №14. Предотвращение распухания рабочей книги Excel
Вы когда-нибудь замечали, что рабочая книга увеличивается в размере с пугающей скоростью без какой-либо очевидной причины? Это происходит по нескольким причинам, и мы предлагаем решения, помогающие вашим книгам похудеть.
Бывало ли с вами такое, что из-за переедания случались проблемы пищеварения? Распухание книги в Excel очень похоже на такую ситуацию. Распухание рабочей книги — это термин, обозначающий ситуацию, когда в книге сделано так много и она увеличилась до такого размера, что не может правильно функционировать.
Мы проверили размер типичной книги, содержащей достаточно много данных. Только с данными размер файла книги был равен 1,37 Мбайт. Затем мы добавили сводную таблицу, которая ссылалась на четыре полных столбца исходных данных, и заметили, что размер файла существенно увеличился — до 2,4 Мбайт. Добавьте еще немного форматирования, и размер типичной книги возрастет практически вдвое после выполнения всего лишь нескольких действий.
Одна из наиболее часто встречающихся причин распухания файла, особенно в ранних версиях Excel, — это наложение форматирования на столбцы или строки целиком, а не только на используемый диапазон данных. Другая причина — это ссылки на полные столбцы как на источники данных для диаграмм и сводных таблиц, а не только на ячейки с фактическими данными. Чтобы исправить эти ошибки, понадобится исключить излишнее форматирование и ограничить источники данных только нужными диапазонами ячеек.
[stextbox id=»warning»]Перед тем как выполнять такие изменения, всегда создавайте резервную копию рабочей книги.[/stextbox]
Устранение излишнего форматирования
Первый шаг при устранении излишнего форматирования — это вычисление ячейки, где заканчиваются данные на рабочем листе, то есть правого нижнего угла таблицы данных. Не полагайтесь на команду Правка → Перейти → Выделить → Последнюю ячейку (Edit → Go To → Special → Last Cell), так как она приведет вас к последней ячейке, содержащей форматирование, а не фактические данные. Вручную найдите последнюю ячейку с фактическими данными, выделите строку сразу же за ней. Удерживая клавиши Ctrl и Shift, нажимайте на клавиатуре стрелку вниз, чтобы выделить все строки ниже, и выберите команду Правка → Очистить → Все (Edit → Clear → All), чтобы очистить их.
Теперь примените ту же логику к нежелательному форматированию в столбцах. Найдите ячейку в последнем столбце, содержащем данные, и щелкните кнопку столбца справа. Удерживайте сочетание клавиш Ctlr+Shift и нажимайте на клавиатуре стрелку вправо, чтобы выделить все остальные столбцы справа, а затем очистите их командой Правка → Очистить → Все (Edit → Clear → All).
[stextbox id=»warning»]Не нужно фактически удалять эти строки или столбцы вместо их очистки, так как при этом в формулах любых ячеек, которые могут ссылаться на них, часто возникает ошибка #REF!.[/stextbox]
Сохраните книгу и радостно взгляните на изменение в размере файла, выбрав команду Файл → Свойства → Общие (File → Properties → General).
Если у вас есть макросы, то теперь необходимо обратиться к модулям, в которые помещен код макросов. Это достаточно быстрый, безболезненный и очевидный процесс, который включает экспорт всех модулей и пользовательских форм UserForm на жесткий диск, удаление существующих модулей и форм, сохранение и импортирование только что экспортированных модулей.
Чтобы сделать это, перейдите в редактор Visual Basic и в окне Project Explorer правой кнопкой мыши щелкните каждый модуль и выберите команду Remove Module1 (считайте, что вместо Module1 стоит имя любого вашего модуля). Когда появится сообщение с вопросом, нужно ли экспортировать модуль перед удалением, ответьте Yes и запомните путь, куда будут экспортированы модули.
Выполните это для всех модулей по очереди, а также для любых форм UserForm, которые у вас могут быть. Не забывайте частные модули вашей книги и листов, если в них также содержится код. Закончив, сохраните рабочую книгу. Затем выберите команду Файл → Импорт (File → Import File) и импортируйте все модули и формы UserForm обратно в рабочую книгу. При этом будет создан текстовый файл для каждого модуля и удалено все лишнее, что может содержаться в модулях.
В сети можно найти несколько бесплатных утилит, которые до некоторой степени автоматизируют эту задачу, но мы встречались со случаями, когда такие утилиты только усложняли код, увеличивая размер файлов. Если вы все же применяете подобные утилиты, всегда сначала сохраняйте резервную копию книги, так как разработчики утилит не несут ответственности за потерю данных.
Настройка источников данных
Если после выполнения предыдущих действий вы все же уверены, что размер файла слишком большой, можно проверить ссылки на неиспользуемые ячейки в сводных таблицах (PivotTable) и сводных диаграммах (PivotChart). Это особенно касается сводных таблиц, так как пользователи часто создают ссылки на все 65 536 строк, чтобы избежать обновления диапазонов вручную после добавления данных. Если это ваш случай, используйте динамические именованные диапазоны [Трюк № 42] для источников данных.
Очистка поврежденных книг
Если вы все так же считаете, что ваша книга слишком большая, возможно, эта книга или составляющие ее листы повреждены. К сожалению, определение точки повреждения требует выполнить процесс исключения вручную.
[stextbox id=»warning»]И снова мы настоятельно рекомендуем сохранить копию рабочей книги перед переходом к следующей процедуре.[/stextbox]
Чтобы гарантировать, что вы ничего не пропустили, отобразите все скрытые листы командой Формат → Лист → Отобразить (Format → Sheet → Unhide). Если эта команда недоступна, вам незачем беспокоиться о скрытых листах — у вас их нет. Теперь, когда все листы видимы, начните с самого левого и по очереди обрабатывайте все листы.
Для каждого листа: удалите лист, сохраните книгу и проверьте размер файла, выбрав команду Файл → Свойства → Общие (File → Properties → General). Если размер файла существенно уменьшился, учитывая количество данных на удаленном листе, то вы, вероятно, нашли место повреждения.
Чтобы заменить в книге поврежденный лист, создайте новый лист, вручную выделите данные на поврежденном листе, вырежьте (не копируйте!) и вставьте их на новый лист. Удалите поврежденный лист из книги, сохраните ее и повторите процесс.
[stextbox id=»warning»]Благодаря вырезанию, а не копированию данных Excel автоматически будет переносить на новый лист данные с соответствующими ссылками.[/stextbox]