Excel потребляет много оперативной памяти
Доброго времени суток %username%. С ходу к делу.
ОС - Windows7 prof 32bit
MS Office 2010 32bit
Работа ведется в системе, с файлами формата xlsx размером от 50Кб до 20Мб. Файлы располагаются физически на сетевом хранилище (диски серверов). В файлах очень много ссылок, они все между собой связаны в формулах, отчетах и т.п. Т.е. в формулах используются ссылки на другие ячейки в других листах, файлах.
С определенного момента начала проявляться проблема. Периодически файлы "зависают". Excel перестает отвечать на запросы. В результате либо перезапускается автоматически, либо сбрасывается через диспетчер задач. Как результат проявления - сбиваются ссылки, вместо сетевого ресурса, указывается локальный путь типа c:\%userprofail%\. собственно и работа сотрудников теряется.
После наблюдения, проявилась закономерность, и выявлены ошибки:
В течение работы с файлом, объем занятой памяти ~50Мб, Срабатывает автосохранение, и появляется предупреждение "Возникла непридвиденная ошибка. В этом сеансе работы с Excel автовосстановление отключено". После этого, занимаемый ресурс памяти в диспетчере задач резко возрастает до ~750Мб. Приложение "зависает".
В эвентах существуют такие вещи:
Имя журнала : Application
Источник : Application Error
Дата: 01.09.2011 16:22:57
Код события: 1000
Имя сбойного приложения: EXCEL.EXE, версия: 14.0.4756.1000, отметка времени: 0x4b9c08e8
Имя сбойного модуля: EXCEL.EXE, версия: 14.0.4756.1000, отметка времени 0x4b9c08e8
Код исключения: 0xc0000005
Смещение ошибки: 0x00b90ce1
Идентификатор сбойного процесса: 0x1780
Время запуска сбойного приложения: 0x01cc6887058840c6
Путь сбойного приложения : C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
Путь сбойного модуля : C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
Код отчета : fa329272-d47b-11e0-a585-6c626d5a5fc3
Источник : Windows Error Reporting
Дата: 01.09.2011 16:23:00
Код события: 1001
Контейнер ошибки , тип 0
Имя события: APPCRASH
Ответ: Нет данных
Идентификатор CAB: 0
Вложенные файлы : ". используемые файлы".
Как я понимаю, придется вообще отключить автосохранение? Или есть какие либо пути решения проблемы иначе?
Решение
Excel, которые работают со сложными Excel, могут воспользоваться 64-разрядным выпуском Office 2013/2016. Это связано с тем, что 64-разрядный выпуск Office не накладывает жестких ограничений на размер файла. Размер книг ограничен только доступной памятью и системными ресурсами. С другой стороны, 32-разрядный выпуск Office ограничен 2 ГБ виртуального адресного пространства, и это пространство совместно используется Excel, книгой и надстройкими, которые выполняются в том же процессе. Листы размером меньше 2 ГБ на диске могут содержать достаточно данных, чтобы занять 2 ГБ или больше адресуемой памяти.
Следующие параметры помогут повысить производительность Excel 2013/2016:
- Excel, которые регулярно работают с большими сложными листами Excel, могут воспользоваться 64-разрядным выпуском Office 2013, так как 64-разрядные выпуски приложений на основе Windows могут использовать до 8 терабайт (ТБ) памяти. Дополнительные сведения о 64-разрядных выпусках Office 2013.
- Ограничение в 2 ГБ для каждого экземпляра процесса Windows Excel. В одном экземпляре можно запустить несколько файлов. Однако если файлы действительно большие и их нужно открыть, рассмотрите возможность открытия нескольких экземпляров для других файлов. Сведения об ограничениях, которые могут возникнуть, см. в разделе "Невозможно вставить какие-либо атрибуты в книгу в другом экземпляре Excel.".
- Если вы используете Windows 7 или Windows 2008, рекомендуем установить обновление платформы для Windows 7 с пакетом обновления 1 (SP1) и Windows Server 2008 R2 с пакетом обновления 1 (SP1).
- Тестирование производительности без надстроек COM. Надстройки COM могут использовать память за счет ограничения в 2 ГБ. Для тестирования отключите надстройки COM и запустите Excel. Если проблемы с памятью вызывают надстройки COM, обратитесь к стороннему поставщику за обновленной копией или 64-разрядной версией надстроек COM.
- Отключите аппаратное ускорение графики. Это отключает анимацию. Для этого в меню "Файл" выберите пункт "Параметры ", нажмите кнопку "Дополнительно ", выберите пункт "Отобразить ", а затем выберите "Отключить аппаратное ускорение графики".
- Измените книгу, чтобы упростить области, использующие память без необходимости. Рекомендуемые изменения см. в статье "Очистка книги Excel 2013, чтобы она использует меньше памяти".
32-разрядная Excel 2016 будет включена для поддержки больших адресов с обновлением от 4 мая 2016 г. номер сборки 16.0.6868.2060 для подписчиков Канала Office 365 Current Channel. Дополнительные сведения см. в статье об изменении возможностей с поддержкой больших адресов для Excel.
Проблема 9. Много мусорных стилей
Про эту пакость я уже подробно писал ранее в статье о том, как победить ошибку "Слишком много форматов ячеек". Суть, если кратко, в том, что если вы разворачиваете на вкладке Главная список Стили ячеек (Home - Cell Styles) и видите там очень много непонятных и ненужных стилей, то это плохо - и для размера вашего файла Excel и для его быстродействия.
Удалить ненужные стили можно с помощью макроса или готовой команды из надстройки PLEX.
Проблема 10. Много примечаний
Примечания к ячейкам, конечно, не самый вредный момент из всех перечисленных. Но некоторые файлы могут содержать большое количество текста или даже картинок в примечаниях к ячейкам. Если примечания не содержат полезной для вас информации, то их можно легко удалить с помощью команды на вкладке Главная - Очистить - Очистить примечания (Home - Clear - Clear Comments) .
Дополнительная информация
Если ни один из этих методов не помог, вам следует рассмотреть возможность перехода на 64-разрядную версию Excel или разбить проблемную рабочую книгу на разные рабочие книги.
После обновления до Microsoft Office 2013 или 2016 вы можете получить один или несколько из следующих симптомов:
Компьютер использует больше памяти при открытии нескольких Microsoft Excel файлов, Excel файлах или выполнении вычислений в Excel книгах.
Вы больше не можете открывать столько книг Excel в одном экземпляре, сколько можно было до обновления до Excel.
При вставке столбцов в рабочую книгу Excel возникает ошибка касательно доступной памяти.
Рекомендации по форматированию
Форматирование может привести к тому, что рабочие книги Excel станут настолько большими, что будут работать некорректно. Часто Excel зависает или аварийно завершает работу из-за проблем с форматированием.
Способ 1: устранение чрезмерного форматирования
Чрезмерное форматирование в рабочей книге Excel может привести к расширению файла и ухудшению производительности. Форматирование будет считаться чрезмерным если отформатировать целые столбцы или строки с помощью цвета или границ. Эта проблема также возникает, когда форматирование требует копирования или импорта данных с веб-страниц или баз данных. Чтобы устранить избыточное форматирование, используйте надстройку для очистки формата, которая доступна в разделе Очистка избыточного форматирования ячеек в рабочем листе.
Если после устранения избыточного форматирования у вас по-прежнему возникают проблемы, перейдите к способу 2.
Способ 2: удалите неиспользуемые стили
Существует множество утилит, которые удаляют неиспользуемые стили. При использовании рабочей книги Excel на основе XML (то есть файла .xlsx или. xlsm), вы можете использовать инструмент очистки стилей. Вы можете найти этот инструмент здесь.
Если после удаления неиспользуемых стилей у вас по-прежнему возникают проблемы, перейдите к способу 3.
Способ 3: удаление фигур
Добавление большого количества фигур в электронную таблицу также требует много памяти. Фигура определяется как любой объект, который располагается в таблице Excel. Ниже приведены некоторые примеры:
- Диаграммы
- Рисование фигур
- Комментарии
- Клипарт
- SmartArt
- Изображения
- WordArt
Часто эти объекты копируются с веб-страниц или других рабочих листов, скрываются или располагаются друг на друге. Зачастую пользователь не подозревает об их присутствии.
Для проверки наличия фигур выполните указанные ниже действия:
- На домашней ленте нажмите Найти и выделить, а затем нажмите Область выделения.
- Нажмите Фигуры на этом листе. Фигуры отображаются в списке.
- Удалите ненужные фигуры. (Значок в виде глаза указывает, видна ли фигура).
- Повторите шаги от 1 до 3 для каждого листа.
Если проблема продолжает возникать после удаления фигур, следует изучить рекомендации, не связанные с форматированием.
Метод 4. Удаление условного форматирования
Условное форматирование может привести к увеличению размера файла. Это происходит в том случае, если условное форматирование в файле повреждено. Вы можете удалить условное форматирование, чтобы проверить, не связана ли проблема с повреждением в форматировании. Чтобы удалить условное форматирование, выполните следующие действия:
- Сохраните резервную копию файла.
- На домашней ленте щелкните Условное форматирование.
- Удалите правила со всего листа.
- Выполните шаги 2 и 3 для каждого листа в книге.
- Сохраните книгу под другим именем.
- Убедитесь в том, что проблема устранена.
Если удаление условного форматирования позволило устранить проблему, откройте исходную книгу, удалите условное форматирование и повторно примените его.
Проблема не устранена?
Если ни один из этих методов не помогает, вы можете перейти на 64-битную версию Excel, разбить проблемную книгу на различные книги или обратиться в службу поддержки для получения дополнительных сведений по устранению неполадок.
Проблема 5. Именованные диапазоны
Если в вашем файле используются именованные диапазоны (особенно с формулами, динамические или получаемые при фильтрации), то имеет смысл от них отказаться в пользу экономии размера книги. Посмотреть список имеющихся диапазонов можно нажав Ctrl+F3 или открыв окно Диспетчера имен (Name Manager) на вкладке Формулы (Formulas) :
Также вычищайте именованные диапазоны с ошибками (их можно быстро отобрать с помощью кнопки Фильтр в правом верхнем углу этого окна) - они вам точно не пригодятся.
Excel загружает процессор на 100% — Решение:
Открываем Excel — Файл-Параметры-Дополнительно — Находим раздел Экран- Установить галочку в пункте: Отключить аппаратное ускорение обработки изображения
Нажимаем кнопку «ОК» и закрываем Excel.
- Следующим пунктом идет отключение — «Анимированных элементов управления«
- Открываем Компьютер — Свойства — Дополнительные параметры системы
- Вкладка «Дополнительно» — раздел «Быстродействие» — Параметры — Снимаем галочку «Анимированные элементы управления и элементы внутри окна«
В открытых окнах нажимаем кнопки «Применить» и «ОК«, закрываем свойства системы и наслаждаемся адекватной работы Excel. Решение рабочее, проверено на многих компьютерах, после всех манипуляций глюки пропадают и процессор перестает нагружаться.
Видео инструкция по вышеописанному материалу:
На этом все. Надеюсь кому-то эта статья окажется полезной.
Спасибо за внимание.
Пишите комментарии, подписывайтесь на блог, буду регулярно размещать интересные задачи, которые возникают в процессе работы.
Если в один прекрасный момент вы осознаете, что ваш основной рабочий файл в Excel разбух до нескольких десятков мегабайт и во время открытия файла можно смело успеть налить себе кофе, то попробуйте пробежаться по описанным ниже пунктам - возможно один или несколько из них укоротят вашего "переростка" до вменяемых размеров и разгонят его "тормоза" :)
Проблема 3. Избыточное форматирование
Сложное многоцветное форматирование, само-собой, негативно отражается на размере вашего файла. А условное форматирование еще и ощутимо замедляет его работу, т.к. заставляет Excel пересчитывать условия и обновлять форматирование при каждом чихе.
Оставьте только самое необходимое, не изощряйтесь. Особенно в тех таблицах, которые кроме вас никто не видит. Для удаления только форматов (без потери содержимого!) выделите ячейки и выберите в выпадающем списке Очистить - Очистить форматы (Clear - Clear Formats) на вкладке Главная (Home) :
Особенно "загружают" файл отформатированные целиком строки и столбцы. Т.к. размер листа в последних версиях Excel сильно увеличен (>1 млн. строк и >16 тыс. столбцов), то для запоминания и обрабоки подобного форматирования нужно много ресурсов. В Excel 2013-2016, кстати, появилась надстройка Inquire, которая содержит инструмент для быстрого избавления от подобных излишеств - кнопку Удалить избыточное форматирование (Clean Excess Cell Formatting) :
Она мгновенно удаляет все излишнее форматирование до конца листа, оставляя его только внутри ваших таблиц и никак не повреждая ваши данные. Причем может это сделать даже для всех листов книги сразу.
Если вы не видите у себя в интерфейсе вкладку Inquire, то ее необходимо подключить на вкладке Разработчик - Надстройки COM (Developer - COM Addins) .
Причина
Начиная с Excel 2013, были внесены улучшения, которые требуют больше системных ресурсов, чем требовалось в предыдущих версиях. В этой статье определяются области в рабочих книгах Excel, которые используют много памяти, и описывается, как можно повысить эффективность работы с файлами рабочих книг.
Дополнительные сведения об изменениях, которые мы внесли в Excel 2013, см. в разделе Использование памяти в 32-разрядной версии Excel 2013.
Проблема 2. Используется старый формат XLS вместо новых XLSX, XLSM и XLSB
Много лет и версий подряд еще с начала девяностых в Excel был один формат файлов - XLS. Это, конечно, убирало проблемы совместимости, но, сам по себе, этот формат давно устарел и имел много неприятных недостатков (большой размер, непрозрачность внутренней структуры данных, легкую повреждаемость и т.д.)
Начиная с верии Excel 2007 Microsoft ввела новые форматы сохранения файлов, использование которых заметно облегчает жизнь и - ваши файлы:
- XLSX - по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.
- XLSM - то же самое, но с поддержкой макросов.
- XLSB - двоичный формат, т.е. по сути - что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер - минимален.
Решение
Чтобы решить эту проблему, воспользуйтесь приведенными ниже способами в указанном порядке. Если один из этих способов не помогает, переходите к следующему способу.
Многие рабочие книги Excel имеют ряд проблем, которые могут вызвать затруднения. После устранения этих проблем ваша рабочая книга будет работать более эффективно.
Все ответы
Ошибка возникает, скорее всего, из-за потери пути к каталогу автовосстановления. В вашей ситуации для начала действительно придется отключить автосохранение, потом настроить каталог автовосстановления и тестировать потихонечку. Вообще, судя по вашему описанию вам уже нужно заводить базу данных - душевле обойдется, чем содержать неимоверное количество листов и листиков Excel, сдувать с них пыль и регулярно восстанавливать всю эту хрупкую конструкцию после обрушения. Мнения, высказанные здесь, являются отражением моих личных взглядов, а не позиции корпорации Microsoft. Вся информация предоставляется "как есть" без каких-либо гарантий
Думаю что автосохранение придется совсем отключить.
Лично моя мысль следущая: после сбоя, автосохранение вытаскивает из профиля "автосохраненный файл" и в случае глупой комманды заменяющей исходный файл, автосохраненным, естественно бьются все ссылки.
Юрий, что Вы имеете ввиду под фразой "настроить каталог автосохранения"? Он по умолчанию в профиле. Думаю как раз искомая проблема, именно в использовании автосохранения для таких файлов. В общем то и будет как решение вопроса - отключение автосохранения. Но вопрос тогда в другом, должно ли так быть, что при попытке автосохранения приложение занимает столько ресурсов, и почему.
И подскажите, я конечно мог не правильно понять, Excel можно "подсадить" на базу данных? Я такого не знаю, честно говоря. Просто пользователи настолько удовлевторены работой этого приложения, что "подпихнуть" им другое сложно. Что бы Вы посоветовали сделать в этой ситуации?
Илья, для тестовых целей я бы предложил перенацелить каталог восстановления на сетевой диск у одного из пользователей. Касательно того, почему приложение занимает столько ресурсов - у вас сервера не на базе Server 2003? Если да, то на клиентах проделайте netsh interface tcp set global autotuninglevel=disabled и понаблюдайте.
Касательно подключения Excel к базе данных: большинство БД может использоваться в качестве источника данных для Excel (см. Создание и редактирование подключений к внешним данным и управление ими). Однако, я имел в виду то, что при вашей достаточно сложной структуре расположения данных (если я правильно понял вашу ситуацию) целесообразнее уже будет обзаводиться СУБД собственной или сторонней разработки для управления всеми этими таблицами, отчетами и т.д. Excel - штука хорошая, но ваша задача для него смотрится уже излишне масштабной.
Мнения, высказанные здесь, являются отражением моих личных взглядов, а не позиции корпорации Microsoft. Вся информация предоставляется "как есть" без каких-либо гарантий
Илья, у нас пользователи тоже используют файлы Excel со множеством перекрестных связей в разных версиях Office (2003, 2007, 2010), но подобных проблем ни у кого не наблюдалось, хотя автосохранение у всех включено. Есть несколько мыслей по поводу возможной причины этих проблем:
1) возможно проблема все же связана с каталогом автовосстановления, например, с правами или со свободным местом - в общем, я бы попробовал поменять его расположение;
2) слишком длинные имена файлов - при работе с сетевыми файлами имена файлов формируются в виде \\server\share\path1\. \path10\Long file name.xlsx, даже если сетевой диск подключен на какую-либо букву. Кстати, если сетевой путь не очень длинный, то при автосохранении он может значительно увеличится за счет c:\%userprofile%\. Кроме того, есть подозрение, что с русскими именами Excel работает в Юникоде, что дополнительно увеличивает длину имени файлов. А при превышении ограничения на длину имени файла (218 знаков) последствия могут быть самые непредсказуемые, в том числе и описанные в вопросе и не всегда Excel выдаст ошибку, указывающую на длину имени. Соответственно, рекомендацией будет - максимально сократить имена файлов и каталогов, в т.ч. каталога автовосстановления;
3) автономные файлы - теоретически тоже может являться источником проблем, поэтому я бы также отключил эту функцию для сетевого каталога на сервере.
И для полноты эксперимента я бы перенес набор связанных между собой файлов на локальный диск (в папку с коротким именем) и посмотрел как работает автосохранение в данном случае.
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Проблема 8. Журнал изменений (логи) в файле с общим доступом
Если в вашем файле включен общий доступ на вкладке Рецензирование - Доступ к книге (Review - Share Workbook) , то внутри вашего файла Excel на специальном скрытом листе начинает сохраняться вся история изменений документа: кто, когда и как менял ячейки всех листов. По умолчанию, такой журнал сохраняет данные изменений за последние 30 дней, т.е. при активной работе с файлом, может запросто занимать несколько мегабайт.
Мораль: не используйте общий доступ без необходимости или сократите количество дней хранения данных журнала, используя вторую вкладку Подробнее (Advanced) в окне Доступ к книге. Там можно найти параметр Хранить журнал изменений в течение N дней (Keep change history for N days) или совсем отключить его:
Ответы
Ну возможно сетевой путь (типа \\server\share\path1\. \path10\Long file name.xlsx ) все же короче, чем тот, который получается при автосохранении (типа c:\%userprofile%\Long file name.xlsx). А при "ручном" сохранении каталог автовосстановления не используется, файл сохраняется напрямую.
Поэтому я и советовал попробовать изменить каталог автовосстановления по умолчанию на какой-нибудь другой типа c:\bkp.
- Предложено в качестве ответа Sergey Plotnikov 8 сентября 2011 г. 10:49
- Помечено в качестве ответа Vinokurov Yuriy Moderator 3 октября 2011 г. 6:32
Проблема 7. Исходные данные сводных таблиц
По-умолчанию Excel сохраняет данные для расчета сводной таблицы (pivot cache) внутри файла. Можно отказаться от этой возможности, заметно сократив размер файла, но увеличив время на обновление сводной при следующем открытии книги. Щелкните правой кнопкой мыши по сводной таблице и выберите команду Свойства таблицы (Pivot Table Properties) - вкладка Данные (Data) - снять флажок Сохранять исходные данные вместе с файлом (Save source data with file):
Если у вас несколько сводных таблиц на основе одного диапазона данных, то сократить размер файла здорово помогает метод, когда все сводные таблицы после первой строятся на основе уже созданного для первой таблицы кэша. В Excel 2000-2003 это делается выбором переключателя на первом шаге Мастера сводных таблиц при построении:
В Excel 2007-2016 кнопку Мастера сводных таблиц нужно добавлять на панель вручную - на ленте такой команды нет. Для этого щелкните по панели быстрого доступа правой кнопкой мыши и выберите Настройка панели быстрого доступа (Customize Quick Access Toolbar) и затем найдите в полном списке команд кнопку Мастер сводных таблиц (PivotTable and PivotChart Wizard) :
Симптомы
После обновления до Microsoft Office 2013/2016/Office 365 появляется один или несколько следующих симптомов:
Компьютер использует больше памяти, когда вы открываете несколько рабочих книг Microsoft Excel 2013, сохраняете их или выполняете вычисления в них.
Вы больше не можете открыть столько рабочих книг Excel в одном экземпляре, сколько было до обновления до Excel 2013/2016.
При вставке столбцов в рабочую книгу Excel возникает ошибка касательно доступной памяти.
Проблема 4. Ненужные макросы и формы на VBA
Большие макросы на Visual Basic и особенно пользовательские формы с внедренной графикой могут весьма заметно утяжелять вашу книгу. Для удаления:
- нажмите Alt+F11, чтобы войти в редактор Visual Basic
- найдите окно Project Explorer’а (если его не видно, то выберите в меню View - Project Explorer)
- удалите все модули и все формы (правой кнопкой мыши - Remove - дальше в окне с вопросом о экспорте перед удалением - No):
Также код может содержаться в модулях листов - проверьте их тоже. Также можно просто сохранить файл в формате XLSX без поддержки макросов - все макросы и формы умрут автоматически. Также можно воспользоваться инструментом Очистить книгу от макросов из надстройки PLEX.
Дополнительная информация
Обозреватель процессов можно использовать для проверки того, приближается ли ограничение в 2 ГБ в Excel. В качестве максимума для 32-разрядного выпуска Excel учитывайте все, что может превышать 1,75 ГБ. Столбец для фокусировки по умолчанию отсутствует. Его можно добавить, выбрав пункт> "Выбор столбцов> "Обработка памяти" и щелкнув "Виртуальный размер".
Прежде чем перейти на 64-разрядную версию Office, ознакомьтесь с 64-разрядными выпусками Office 2013.
На днях столкнулся я с такой задачей. Обратился пользователь с жалобой: «Открываю два документа в Excel, начинаю работу и через некоторое время все начинает жутко тормозить». Странно…компьютер свежий, жесткий диск заменили на SSD, оперативную память увеличили до 8 ГБ. Для офисного ПК со средними задачи то что нужно. Но факт остается фактом, Excel загружает процессор на 100%…тормоза есть и это явно прослеживается.
К слову, на ПК windows 10, Excel 2016. Подключаюсь к компьютеру и пытаюсь воспроизвести проблему, и да, вот они жуткие глюки в частности после использования комбинации «копировать-вставить«.
Обращаемся к Диспетчеру задач и видим, что при попытке скопировать элемент в буфер обмена, процессор загружает на 100% и не отпускает пока принудительно не завершишь работу документов Excel.
Проблема локализована, причины торможения найдены, но буду честным, увидел я такое впервые. Обратился на форум microsoft и решение было найдено. Дело кроется в аппаратном ускорении обработки изображений.
Ответы
Ну возможно сетевой путь (типа \\server\share\path1\. \path10\Long file name.xlsx ) все же короче, чем тот, который получается при автосохранении (типа c:\%userprofile%\Long file name.xlsx). А при "ручном" сохранении каталог автовосстановления не используется, файл сохраняется напрямую.
Поэтому я и советовал попробовать изменить каталог автовосстановления по умолчанию на какой-нибудь другой типа c:\bkp.
- Предложено в качестве ответа Sergey Plotnikov 8 сентября 2011 г. 10:49
- Помечено в качестве ответа Vinokurov Yuriy Moderator 3 октября 2011 г. 6:32
Рекомендации, связанные с вычислениями
Помимо форматирования сбой и зависание в Excel также могут вызывать вычисления.
Метод 1. Открытие книги в последней версии Excel
Первое открытие книги Excel в новой версии Excel может занять много времени, если книга содержит множество вычислений. При первом открытии книги Excel должен пересчитать книгу и проверить значения в книге. Дополнительные сведения см. в следующих статьях:
Если файл продолжает медленно открываться после того, как Excel полностью пересчитает файл и вы сохраните его, перейдите к методу 2.
Метод 2. Формулы
Просмотрите книгу и изучите типы формул, которые вы используете. Для некоторых формул требуется много памяти. К ним относятся следующие формулы с массивами:
Их можно использовать. Однако следует помнить о диапазонах, на которые вы ссылаетесь.
Формулы, которые ссылаются на целые столбцы, могут привести к низкой производительности файлов .xlsx. Размер сетки увеличился с 65 536 до 1 048 576 строк и с 256 (IV) до 16 384 столбцов (XFD). Популярный (однако не самый лучший) способ создания формул заключался в том, чтобы ссылаться на целые столбцы. Если вы ссылались только на один столбец в старой версии, то были включены только 65 536 ячеек. В новой версии вы ссылаетесь более чем на 1 миллион столбцов.
Предположим, что вы используете следующую функцию ВПР:
В Excel 2003 и более ранних версиях данная функция ВПР ссылалась на целую строку, которая включала только 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: советы по оптимизации препятствий производительности.
Если после обновления формул массива проблема не будет устранена, перейдите к способу 6.
Способ 6: определенные имена
Определенные имена используются для ссылок на ячейки и формулы во всей рабочей книге, чтобы добавить "дружественное имя" к формулам. Необходимо проверить наличие определенных имен, которые ссылаются на другие рабочие книги или временные файлы Интернета. Как правило, эти ссылки излишни и замедляют открытие рабочей книги Excel.
Вы можете использовать инструмент Диспетчер имен для просмотра скрытых определенных имен, которые нельзя увидеть в интерфейсе Excel. Этот инструмент позволяет просматривать и удалять ненужные имена.
Если после удаления ненужных определенных имен Excel продолжает аварийно завершать работу, перейдите к способу 7.
Способ 7: ссылки и гиперссылки
Преимущество Excel заключается в его способности получать данные из других электронных таблиц в реальном времени. Проведите инвентаризацию файла и внешних файлов, на которые он ссылается. В Excel нет ограничений на количество рабочих книг, которые можно связать, хотя существует несколько проблем, с которыми вы можете столкнуться. Протестируйте файл без ссылок, чтобы определить, в чем проблема – в этом файле или в одном из связанных файлов.
Продолжаем
Это наиболее распространенные проблемы, которые вызывают зависание и сбои в Excel. Если вы по-прежнему сталкиваетесь со сбоями и зависаниями в Excel, вам следует рассмотреть возможность открытия запроса в службу поддержки Microsoft.
Причина
Проблема 6. Фотографии высокого разрешения и невидимые автофигуры
Если речь идет о фотографиях, добавленных в книгу (особенно когда их много, например в каталоге продукции), то они, само-собой, увеличивают размер файла. Советую сжимать их, уменьшая разрешение до 96-150 точек на дюйм. На экране по качеству это совершенно не чувствуется, а размер файла уменьшает в разы. Для сжатия воспользуйтесь кнопкой Сжать рисунки (Compress pictures) на вкладке Формат (Format) :
Кроме видимых картинок на листе могут содержаться и невидимые изображения (рисунки, фотографии, автофигуры). Чтобы увидеть их, выделите любую картинку и на вкладке Формат (Format) нажмите кнопку Область выделения (Selection Pane) .
Для удаления вообще всех графических объектов на текущем листе можно использовать простой макрос:
Проблема 1. Используемый диапазон листа больше, чем нужно
Если ваша таблица занимает 5 на 5 ячеек, то это отнюдь не означает, что Excel запоминает при сохранении этого файла только 25 ячеек с данными. Если вы в прошлом использовали какие-либо ячейки на этом листе, то они автоматически включаются в используемый диапазон (так называемый Used Range), который и запоминается при сохранении книги. Проблема в том, что при очистке используемых ячеек Excel далеко не всегда автоматически исключает их из используемого диапазона, т.е. начинает запоминать в файле больше данных, чем реально имеется.
Проверить это просто – нажмите на клавиатуре сочетание клавиш Ctrl+End и посмотрите куда переместится активная ячейка. Если она прыгнет на фактическую последнюю ячейку с данными на листе – отлично. А если вдруг ускачет сильно правее и/или ниже "в пустоту" – дело плохо: все эти ненужные пустые ячейки Excel тоже запоминает внутри файла.
Лечится это, тем не менее, достаточно легко:
- Выделите первую пустую строку под вашей таблицей
- Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
- Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows) .
- Повторите то же самое со столбцами.
- Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а "в пустоту" ниже и/или правее.
- Сохраните файл (обязательно, иначе изменения не вступят в силу!)
Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.
Читайте также: