backtotop

Categories: Мануалы

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

Большой-размер

Тормоза программа пакета ms office дело распространненое. Excel может тормозить из-за целого ряда причин — это может быть и неправильное использование функций программы (условное форматирование, сводные таблицы, лишние строки), так и не совершенство программной части (появляющиеся обьекты, папка printerSettings в структуре). Поэтому рекомендуем пройтись по всем 10 шагам, представленным ниже.

Итак, подробнее о каждом варианте:

1. Тормозит Excel? Нужно убрать форматирование в виде таблиц

Иногда диапазон отформатирован в виде специальной таблицы (Верхняя панель — вкладка Вставка — Таблица), это может привести к тому, что тормозит Excel файл очень сильно.

Чтобы определить такой формат, выделите любую ячейку таблицы. Появляется панель «Работа с таблицами» в верхнем меню, самая правая. Выберите всю таблицу затем вкладку Работа с Таблицами, Конструктор — Раздел сервис — Преобразовать в диапазон.

Работа с таблицами

2. Убрать излишнее условное форматирование (если Excel тормозит при прокрутке)

Что такое условное форматирование, читайте здесь. Очень полезная штука, если правильно применять, если нет, то это заставит Ваш файл работать медленно.

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

Убираем так. Выбираете лист. Выберите нужный диапазон или весь лист. В верхней панели Главная — Условное форматирование — Удалить правила — Удалить правила из выделенных ячеек/со всего листа. Жмем!

3. Удалить лишние строки/столбцы (если ползунок прокрутки очень маленький)

тормозит Excel

Это самая частая проблема для excel с которой я встречаюсь, если кто-то случайно переместился на конец листа (на строку номер 1 млн и более) и так сохранил книгу. Размер файла сразу увеличился. Дойти до конца таблицы можно и случайно — если нажать сочетание Ctrl + стрелка вниз или вправо. Бывает, что в конце книги находится какой-нибудь случайный символ или заливка.

Главный признак, если размер ползунка очень маленький, как на картинке.

Чтобы от этого избавиться необходимо удалить все лишние строки или столбцы. Найдите последнюю полезную ячейку для вас, выделите первую пустую ячейку после нее (а лучше первую пустую строку/столбец после нее), нажмите Ctrl+Shift+End. Это сочетание выделяет все ячейки ниже выделенной строки или правее выделенного столбца. Правая кнопка мыши – Удалить –  Удалить строку или столбец (может проходить долго). После удаления выберите ячейку А1 и сохраните файл. Ползунок должен увеличиться.

4. Удалить лишние объекты

Очень часто, особенно при копировании из других файлов или сайтов в таблицах прячутся скрытые объекты — рисунки, фигуры и т.п.

Чтобы удалить такие объекты выполните макрос (нажмите Alt + F11 и копируйте текст ниже)

sub УдалитьОбъекты()
For Each i In ActiveSheet.Shapes
 i.Delete
 Next
end sub

Или выделить и  удалить все объекты вручную. Перейдите в меню Главная — Редактирование — Найти и выделить — пункт Выделение группы ячеек — Объекты. Теперь удаляйте.

5. Удалить лишние данные в структуре файла Excel

Даже очень опытные пользователи не знают, что файл Excel, как говорит Википедия, это файл-архив (начиная с 2007 версии).

Т.е. файл Excel можно открыть, к примеру, архиватором 7-zip или WinRar и внутри него могут храниться всякие ненужные файлы, это тормозит Excel порой на десятки минут!

Хотите удалить это неудобство? Сперва сделайте резервную копию файла :) Затем запустите 7-zip или другой архиватор, меню «Файл» — «Открыть внутри». Возможно открыть так же, кликнув правой кнопкой мыши — Открыть с помощью и выбрав .exe файл WinRar или 7-zip.

Откроется архив (да, это файл Excel) c папками и файлам.

Находим папки «drawings» и/или «printerSettings» (скорее всего они будут в папке xl) и удаляем их.

Для WinRar делаем все тоже самое.

Тормозит 2

После чего открываем книгу как файл Excel, он немного поругается (выведет несколько системных сообщений), что не находит данных и т.п. — на это всегда нажимаем ОК, данные восстановятся.

Будьте внимательны, если в вашем файле есть нарисованные кнопки или другие фигуры, то удалять всю папку drawings — значит и удалять полезные фигуры.

Поэтому, в этой папке, удалите только файлы vmlDrawing.vml, они могут накапливать информацию и весить до 100 мб!

Тормозит

6. Тормозит Excel — правильно настройте сводные таблицы

Тормозит Excel 3

Если сводная таблица ссылается на большой диапазон данных (от 10 тыс. строк), она хранит результаты расчетов, которые могут быть очень большими. От этого вся книга excel тормозит, конечно же. Чтобы устранить эту причину, нажмите на сводную таблицу правой кнопкой мыши — Параметры сводной таблицы — вкладка Данные — уберите галочку Сохранять исходные данные вместе с файлом.

Что позволит уменьшить файл почти в два раза!

7. Измените формат файла на .xlsb

Если вы работаете с огромными таблицами и ваши файлы больше 5 мб весом, то лучше сохранять такие книги в формате .xlsb. Это двоичный формат книги Excel, т.е. специальный формат для создания в базы данных на основе электронных таблиц. Если сохранить большой файл в таком формате, вес книги уменьшится более чем в два раза. Расчеты в файл тоже будут проходить быстрее.

8. Установлен неопознанный принтер

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

Бывает что даже при удалении настроек принтера из пункта 5 настройки принтера тормозят файл.

9. Удалите файл PERSONAL

Зайдите в …Application Data\Microsoft\Excel\XLSTART или …Microsoft Office\Office12 найдите в этих папках файл под название PERSONAL и удалите его. Подробнее можете прочитать здесь.

10. Замените много созданных формул макросами

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

Теперь попробуйте сами!

Если эти 10 шагов не помогли, то лучше перенесите (скопируйте) данные в новый файл! Возможно поврежден сам файл Excel.

Если ничего не помогает пишите комментарии или на ветке форума

 

.

.

.


Comments

( 12 Comments )

Максим:

Когда файл большой, все верно и понятно. Но у меня вот отчего-то жутко тормозит любой новый, только что созданный файл. Тормозит он только на операциях «скопировать ячейку» и «объединить ячейки». Если копировать содержимое внутри ячейки, проблем не наблюдается. Что бы это могло быть?

Izotoz:

Максим, добрый день! Это довольно редкий случай;) Но уверен мы с этим разберемся. Скорее всего дело в так называемом файле с настройками Personal, когда открываете excel название файла Personal или Лист 1?
Чтобы исключить прочие варианты, пришлите мне пожалуйста пример файла.

Максим:

Добрый день.
Не очень понял, где посмотреть файл с настройками Personal…
Что касается примера файла, то вот он: http://rusfolder.com/44062633
Я его прямо только что создал и попытался скопировать / вставить произвольные значения — тормозит

Izotoz:

Максим, файл проверю!
Если мои подозрения оправдаются — напишу подробную статью про файл Personal

Izotoz:

У меня файл не тормозит, проблема в принципе ясна. Попробуйте скачать файл http://excelworks.ru/wp-content/uploads/2015/03/Test.xlsx и отписать мне, стало ли лучше, если нет — напишу дополнение к статье в ближайшие дни — тут может быть сразу несколько причин.
Плюс проверьте, добавил 8 и 9 пункт в статью, возможно в этом дело.
Какая у Вас операционная система?

Максим:

Ваш файл не тормозит! Фантастика

По пунктам 7 и 8:
7: принтер у меня установлен и работает, но я для чистоты эксперимента даже его удалял — ничего не меняется.
8: по указанным адресам файл PERSONAL отсутствует и Поиском файл с таким именем не ищется.

ОС у меня Windows7 домашняя расширенная.

Максим:

*поправка: по п. 8 и 9, конечно

Максим, приветствую! Если файла нет, это хорошо, значит проблема не в нем. Проблема в итоге осталась?
Интересный случай, могу предложить подключиться по Тимвьюверу к вам,
Сделал ветку форума http://excelworks.ru/forums/topic/pochemu-tormozit-excel/
Давайте продолжим беседу там

Лола:

Очень сильно тормозит фильтр в excel.
Таблица большая со множеством формул, файл сетевой, принтер тоже сетевой. Без этого никак.
Начал тормозить после того, как установили больше памяти. До этого комп в целом тормозил, а теперь всё работает гораздо быстрее,
но когда включаю фильтр, зависает на несколько минут.
С чем это связано и можно ли что-то предпринять?
Сил больше нет. Полдня уходит на ожидание.

Лола, предложенные методы в статье не помогли?

farsh1k:

Испробовал все варианты, но самым действенным оказался пятый : Удалить лишние данные в структуре файла Excel
Размер файла уменьшился с 500 кб до 60 кб
Спасибо Вам!

Для этого и писалась статья :)
Пожалуйста!

Leave a Comment:

Ваш e-mail не будет опубликован. Обязательные поля помечены *

4 × 4 =