Как очистить книгу Excel, чтобы использовать меньше памяти

Симптомы

После обновления до Office 2013/2016/Microsoft 365 вы испытываете одну или несколько следующих проблем:

• Компьютер использует больше памяти при открытии нескольких книг Microsoft Excel 2013, сохранении книг Excel или выполнении вычислений в книгах Excel.

• Вы больше не можете открывать столько рабочих книг Excel в том же сеансе, как могли раньше до обновления до Excel 2013/2016.

• При вставке столбцов в книгу Excel появляется ошибка, связанная с доступной памятью.

• При работе с электронной таблицей Excel вы получите следующее сообщение об ошибке:

  There isn't enough memory to complete this action.
  Try using less data or closing other applications.
  To increase memory availability, consider:
     - Using a 64-bit version of Microsoft Excel.
     - Adding memory to your device.
  

Причина

Начиная с Excel 2013, были внесены улучшения, требующие больше системных ресурсов, чем требовали более ранние версии. В этой статье определяются области книг Excel, использующие много памяти, и описывается, как можно оптимизировать работу файлов книг Excel.

Дополнительные сведения об изменениях, внесённых в Excel 2013, см. в разделе «Использование памяти в 32-разрядном выпуске Excel 2013».

Резолюция

Чтобы устранить эту проблему, используйте следующие методы в том порядке, в котором они представлены. Если один из этих методов не помогает, перейдите к следующему методу.

Замечание

Многие книги Excel имеют несколько проблем, вызывающих трудности. После устранения этих проблем книга будет работать более эффективно.

Рекомендации по форматированию

Форматирование может привести к тому, что книги Excel становятся настолько большими, что перестают правильно функционировать. Часто Excel зависает или завершает работу из-за проблем форматирования.

Метод 1. Устранение чрезмерного форматирования

Чрезмерное форматирование в книге Excel может привести к росту файла и может привести к снижению производительности. Форматирование считается чрезмерным, если вы отформатировали целые столбцы или строки с цветом или границами. Эта проблема также возникает, когда форматирование требует копирования или импорта данных из веб-страниц или баз данных. Чтобы исключить избыточное форматирование, используйте надстройку для очистки формата, которая доступна в Очистка избыточного форматирования ячеек в листе.

Если вы продолжаете сталкиваться с проблемами после устранения избыточного форматирования, перейдите к методу 2.

Метод 2. Удаление неиспользуемых стилей

Стили можно использовать для стандартизации форматов, используемых во всех рабочих книгах. При копировании ячеек из одной книги в другую их стили также копируются. Эти стили продолжают расти и в конечном итоге могут вызвать сообщение об ошибке "Слишком много различных форматов ячеек" в Excel при сохранении обратно в старые версии файлов.

Многие служебные программы доступны, которые удаляют неиспользуемые стили. Если вы используете книгу Excel на основе XML (то есть файл .xlsx или xlsm-файл), можно использовать средство очистки стиля. Это средство можно найти здесь.

Если вы продолжаете сталкиваться с проблемами после удаления неиспользуемых стилей, перейдите к методу 3.

Метод 3. Удаление фигур

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

• Диаграммы
• Рисование фигур
• Комментарии
• Картинки
• SmartArt
• Живопись
• WordArt

Часто эти объекты копируются из веб-страниц или других листов и скрыты или сидят друг на другом. Часто пользователь не знает, что они присутствуют.

Чтобы проверить наличие фигур, выполните следующие действия.

1. На домашней ленте нажмите кнопку "Найти" и "Выбрать", а затем выберите область выбора.
2. Щелкните на фигуры этого листа. Фигуры отображаются в списке.
3. Удалите ненужные фигуры. (Значок в виде глаза указывает, видна ли фигура.)
4. Повторите шаги 1–3 для каждого листа.

Если после удаления фигур по-прежнему возникают проблемы, следует изучить вопросы, которые не связаны с форматированием.

Метод 4. Удаление условного форматирования

Условное форматирование может привести к росту файла. Это происходит при повреждении условного форматирования в файле. Условное форматирование можно удалить в качестве теста, чтобы узнать, связана ли проблема с повреждением в форматировании. Чтобы удалить условное форматирование, выполните следующие действия.

1. Сохраните резервную копию файла.
2. На домашней ленте щелкните условное форматирование.
3. Очистить правила из всего листа.
4. Выполните шаги 2 и 3 для каждого листа в рабочей книге.
5. Сохраните книгу под другим именем.
6. Узнайте, устранена ли проблема.

Если удаление условного форматирования устраняет проблему, можно открыть исходную книгу, удалить условное форматирование и повторно применить ее.

Проблема остается?

Если ни один из этих методов не работает, вы можете рассмотреть возможность перехода на 64-разрядную версию Excel, разделить проблемную книгу на несколько книг или обратиться в службу поддержки для получения дополнительной помощи по устранению неполадок.

Рекомендации по вычислению

Помимо форматирования вычисления также могут вызвать сбой и зависание в Excel.

Метод 1. Открытие книги в последней версии Excel

Открытие книги Excel впервые в новой версии Excel может занять много времени, если книга содержит много вычислений. Чтобы открыть книгу в первый раз, Excel должен пересчитывать книгу и проверять значения в книге. Для получения дополнительной информации см. статью о медленной загрузке электронной таблицы при первом открытии в Excel.

Если файл продолжает открываться медленно после того, как Excel пересчитывает файл полностью, и сохраните файл, перейдите к методу 2.

Метод 2. Формулы

Просмотрите книгу и изучите типы формул, которые вы используете. Некоторые формулы занимают много памяти. К ним относятся следующие формулы массива:

• ПОИСК
• НЕПРЯМОЙ
• СМЕЩЕНИЯ
• ИНДЕКС
• СОВПАДЕНИЕ

Можно их использовать. Однако имейте в виду диапазоны, на которые вы ссылаетесь.

Формулы, ссылающиеся на целые столбцы, могут привести к снижению производительности в .xlsx файлах. Размер сетки вырос с 65 536 строк до 1048 576 строк и с 256 (IV) столбцов до 16 384 (XFD). Популярным способом создания формул, хотя и не рекомендуется, было ссылаться на целые столбцы. Если в старой версии вы ссылались только на один столбец, то у вас было включено всего 65 536 ячеек. В новой версии вы ссылаетесь на более чем 1 миллиона столбцов.

Предположим, что у вас есть следующая ВПР:

=VLOOKUP(A1,$D:$M,2,FALSE) 

В Excel 2003 и более ранних версиях этот VLOOKUP ссылается на всю строку, которая включала только 655 560 ячеек (10 столбцов x 65 536 строк). Однако с новой, более крупной сеткой, та же формула ссылается почти на 10,5 миллиона ячеек (10 столбцов x 1 048 576 строк = 10 485 760).

Это исправлено в Office 2016/365 версии 1708 16.0.8431.2079 и более поздних версий. Сведения об обновлении Office см. в статье "Установка обновлений Office".

Для более ранних версий Office может потребоваться восстановить формулы, чтобы ссылаться только на те ячейки, которые необходимы для формул.

Замечание

Проверьте определенные имена, чтобы убедиться, что у вас нет других формул, ссылающихся на целые столбцы или строки.

Замечание

Этот сценарий также возникает, если вы используете целые строки.

Если проблемы продолжаются после того, как вы изменили формулы, чтобы ссылаться только на используемые ячейки, перейдите к методу 3.

Метод 3. Вычисление между книгами

Ограничьте формулы, выполняющие вычисления в нескольких книгах. Это важно по двум причинам:

• Вы пытаетесь открыть файл по сети.
• Excel пытается вычислить большие объемы данных.

Вместо выполнения вычислений через сети, содержите формулу в одной книге, а затем создайте простую ссылку из одной книги на другую.

Если проблема сохраняется после того, как вы изменили формулы так, чтобы они ссылались только на ячейки, а не выполняли вычисления в разных книгах, перейдите к способу 4.

Метод 4. Переменные функции

Ограничьте использование изменчивых функций в книге. Вам не нужно иметь сотни ячеек, использующих функцию TODAY или NOW. Если вам нужно вставить текущую дату и время в электронную таблицу, используйте функцию один раз, а затем ссылайтесь на функцию через определенное имя.

Если вы продолжаете сталкиваться с проблемой после ограничения переменных формул, перейдите к методу 5.

Метод 5. Формулы массива

Формулы массива являются мощными. Но они должны использоваться правильно. Важно не добавлять в массив больше ячеек, чем необходимо. Если ячейка в массиве содержит формулу, требующую вычисления, вычисление выполняется для всех ячеек, на которые ссылается эта формула.

Дополнительные сведения о работе массивов см. в excel 2010 Performance: Советы по оптимизации препятствий для производительности.

Если после обновления формул массива по-прежнему возникает проблема, перейдите к методу 6.

Метод 6. Определенные имена

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

Вы можете использовать средство диспетчера имен для просмотра скрытых имен, которые не отображаются в интерфейсе Excel. Это средство позволяет просматривать и удалять определенные имена, которые вам не нужны.

Если Excel продолжает падать и зависать после удаления ненужных имен, определенных в Excel, перейдите к методу 7.

Метод 7. Ссылки и гиперссылки

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

Идем дальше

Это наиболее распространенные проблемы, которые вызывают зависание и сбой в Excel. Если у вас по-прежнему происходят сбои и зависания в Excel, стоит рассмотреть возможность открытия запроса в службу поддержки Microsoft.

Дополнительная информация

Если ни один из этих методов не оказали эффекта, следует либо перейти на 64-разрядную версию Excel, либо разделить проблемную книгу на разные книги Excel.

Как устранить ошибки "доступных ресурсов" в Excel

Excel: Как устранять сбои и проблемы с "не отвечает" в книге Excel