Большие файлы Excel часто добавляют хлопот пользователям из-за увеличения времени их обработки. Иногда проблемы становятся настолько острыми, что с файлом невозможно работать. Несколько лет назад я предложил два варианта, как бороться с мусорными объектами, которые могут появляться при импорте данных из 1С, SAP и других программ: Excel тормозит. Что делать? и Excel «тормозит». Что делать? Дубль 2. Заметки пользуются большой популярностью, при этом в комментариях задают вопросы, напрямую не связанные с паразитными объектами. А около года назад, когда я читал книгу Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel, то обратил внимание на то, как много внимания автор уделяет скорости работы формул, постоянно сравнивая время выполнения обычных формул, формул баз данных и формул массива. Так и родилась идея этой заметки – показать, как различные элементы книги Excel влияют на ее размер. [1]
Рис. 1. Зависимость размера книги от числа листов в ней; 255 пустых листов увеличивают размер книги до 131 КВ
Скачать заметку в формате Word или pdf, примеры в формате Excel
Начнем с того, что Microsoft принципиально переработал версию Excel в 2007 году, так что, если вы используете более раннюю версию, советую вам перейти на Excel2007 или более позднюю версию.
Возможности Excel по работе с большими файлами и скорость работы с ними в значительной степени зависят от объема оперативной памяти. Например, я ощутил существенную прибавку в этом аспекте увеличив память в своем моноблоке с 2 до 4 ГВ. А в одном из комментариев к заметке «Excel тормозит. Что делать?» упоминается, что выделение 14 000 объектов заняло 12 с при 32 ГВ оперативки (я на своем моноблоке с 2 ГВ не смог выделить и за полчаса).
Число листов
Свое исследование я начал с создания книги Excel, не содержащей данных, и включающей всего 1 лист. Ее размер оказался 7,6 КВ. Книга Excel может содержать не более 255 листов (рис. 1).
Объем данных
Вернемся к книге, содержащей 1 лист, и в ячейку А1 введем значение 1. Размер книги не изменится – 7,6 КВ. Если же единицами заполнить 10 000 ячеек в столбце А, то размер увеличится до 613 КВ (рис. 2, синяя прямая).
Кстати, для заполнения ячеек можно воспользоваться следующим методом. Набираем Ctrl+G, и в окне Переход в поле Ссылка вводим адрес А10000, и жмем Ok. Мы перешли к последней ячейке заполняемого диапазона. Вводим в ячейку А10000 значение 1. Возвращаемся к ячейке А1. Удерживая Shift, нажимаем на цифровой клавиатуре кнопку End и затем стрелку вниз. Выбран диапазон А1:А10000. Не снимая выделение, вводим единицу (она появится в ячейке А1) и, по-прежнему не убирая выделение, жмем Ctrl+Enter. 10 000 ячеек заполнились единицами.
Рис. 2. Зависимость размера книги от числа заполненных ячеек
Любопытно, что заполнение единицами квадратной области (100х100, …, 1000х1000 ячеек) сокращает объем файла (рыжая кривая на рис. 2). Для меня оказалось неожиданным, что замена единицы на текстовый массив (я использовал около 1500 символов в каждой ячейке), увеличило размер файла лишь 5–10%. К еще меньшему эффекту приводит замена единицы на большое число с десятичной запятой.
Формулы и функции
Добавление формул почти удваивает размер файла. При этом даже простейшая ссылка на предыдущую ячейку (рис. 3), протянутая на миллион ячеек, увеличивает размер файла с 5,15 МВ до 9 МВ. Дальнейшее усложнение формул увеличивает размер файла лишь на несколько процентов.
Рис. 3. Ссылка вместо константы
Однако, в Excel есть особые функции, пересчитываемые при любом изменении данных, например, =СЛЧИС(). Если протянуть эту функцию на миллион ячеек, размер файла увеличится до 22 МВ!
Сводные таблицы
Исходные данные занимали прямоугольную область: 10 столбцов * 10 000 строк (рис. 4). Файл весил 569 КВ. Добавление сводной таблицы (рис. 5) увеличило размер файла до 750 КВ (на 30%). Форматирование сводной таблицы с использованием стандартных стилей (доступны по меню Работа со сводными таблицами –> Конструктор –> Стили сводной таблицы) практически не изменяет размер файла. Добавление второй сводной таблицы на основе того же кеша увеличило размер файла до 903 КВ (подробнее об использование кеша см. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Добавление второй сводной таблицы на основе нового кеша увеличило файл с 750 до 1 060 КВ. Поэтому, создавая набор сводных таблиц, старайтесь использовать один кеш. И только если вы собираетесь применять к сводным таблицам различные настройки (например, в одной таблице группировать даты по месяцам, а в другой – по неделям), предусмотрите для каждой таблицы свой кеш.
Рис. 4. Исходные данные
Рис. 5. Сводная таблица
Еще одной проблемой, приводящей к росту файла, может быть неверно организованная область данных для сводной таблицы. Чтобы избежать ручного обновления диапазонов сводной таблицы после добавления исходных данных, пользователи часто создают ссылки на целые столбцы. Используйте самообновляющиеся динамические именованные диапазоны вместо ссылок на столбцы.
«Мусор»
Иногда (каким-то непостижимым образом) на листе Excel вводятся ненужные данные в «далекой» ячейке. На одном листе Excel2013 может быть 1 048 576 строк и 16 384 столбцов. Однажды мне встретился файл, в котором было введена единица в предельной ячейке (рис. 6). К счастью, современные версии Excel легко справляются с этой ситуацией, и размер файлов от таких ошибок не распухает, чего не скажешь о более ранних версиях. Увидеть, что на вашем листе есть «мусор» можно с помощью бегунка, который будет иметь неестественно большое поле для пробега (см. нижнюю часть рис. 6).
Рис. 6. «Мусорное» значение в последней ячейке
Форматирование
Еще одна причина распухания файла — наложение форматирования на столбцы или строки целиком, а не только на используемый диапазон данных. Чтобы исправить эту ошибку, нужно исключить излишнее форматирование. Для начала вручную найдите последнюю ячейку с фактическими данными. Если ваш лист не содержит «мусора», то в последнюю ячейку можно попасть, прокрутив бегунок по вертикали и горизонтали в крайние положения (но не более того). Далее выделите горизонтальную область, лежащую ниже данных, и содержащую форматирование, и пройдите по меню Главная –> Редактирование –> Очистить –> Очистить всё. Повторите операцию для вертикальной области, лежащей справа от области с данными.
Обратите внимание, что рекомендуется применить команду Очистить, а не Удалить (строки, столбцы). В противном случае в формулах, которые могут ссылаться на удаленные ячейки возникнет ошибка.
Макросы
Если вы не используете макросы, сохраните книгу с расширением xlsx. Если вы не используете отдельные макросы, можете экспортировать соответствующие модули, а затем удалить их. Иногда (я такого не встречал) какие-то проблемы содержатся в рабочих модулях. Может помочь последовательный экспорт, удаление модуля, импорт.
Очистка поврежденных книг
Возможно, ваша книга или отдельные листы повреждены. К сожалению, определение точки повреждения требует выполнить процесс исключения вручную. Сохраните резервную копию книги. Чтобы гарантировать, что вы ничего не пропустите, отобразите все скрытые листы. Для этого щелкните правой кнопкой мыши на ярлычке любого листа и выберите команду Показать. Если эта команда недоступна, значит у вас нет скрытых листов. Теперь, когда все листы видимы, начните с самого левого и по очереди обрабатывайте все листы. Для каждого листа: удалите лист, сохраните книгу и проверьте размер файла, например, пройдя по меню Файл –> Сведения, область Свойства –> Размер. Если размер файла существенно уменьшился, учитывая количество данных на удаленном листе, то вы, вероятно, нашли место повреждения.
Чтобы заменить в книге поврежденный лист, создайте новый лист, вручную выделите данные на поврежденном листе, вырежьте (не копируйте!) и вставьте их на новый лист. Удалите поврежденный лист из книги, сохраните ее и повторите процесс. Благодаря вырезанию, а не копированию данных Excel автоматически будет переносить на новый лист данные с соответствующими ссылками.
[1] В том числе использованы некоторые идеи из книги Д.Холи, Р.Холи. Excel 2007. Трюки, стр. 58–60