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 столбцов со сложными формулами ссылающимися на другие листы, а еще хуже книги может жутко затормозить книгу. Попробуйте все расчеты формулами заменить на записанные макрорекордером макрос, переводящий формулы в скопированные как текст данные. Это может помочь, если с файлом с производится много действий — а значит файл часто пересчитывается. Сам недавно попробовал — помогло. Пишите в комментариях, если нужна помощь!

11. Уменьшите размер рисунков

Самый простой способ для версии 2007 и выше — выбрать рисунок, на верхней панели появиться вкладка Работа с рисунками — Формат. Найдите раздел Изменить и выберите действия по сжатию рисунка, подробнее можно прочитать здесь.

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

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

Если ничего не помогает пишите комментарии, постараемся помочь!

 

  .

Comments

( 28 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 кб
Спасибо Вам!

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

Ильхом:

Здравствуйте. Подскажите, пожалуйста: у меня тормозит excel, если выделять ячейки и в контекстном меню выбирать «Преобразовать в число», — почему такое может быть? На других компьютерах нормально. Проблемный компьютер: Windows 7 х64, Office 2007 со всеми апдейтами.

Интересная ситуация — пришлите пожалуйста файл на nizotov@excelworks.ru

Иван:

Здравствуйте.
Применил два из предложенных здесь пункта, уменьшил файл в 1,5 раза. Но осталась другая проблема: в файле 200 тыс. строк и необходимо увеличить до 600 тыс. Это база данных, к которой через ВПР «подключены» различные справочники из внешнего файла. Более того, в самом файле есть справочники, созданные на основе исходных данных: сводная таблица, где вычисляются определенные критерии, — потом данные из этих справочников возвращаются в отдельные столбцы обратно в лист с исходными данными. И так несколько раз.
В общем пересчет формул занимает 10 минут. Файл вешает комп. И после двух-трех действий ОЗУ забивается и пишет, что «Недостаточно ресурсов».
Подскажите, что можно сделать для ускорения работы или даже речь не о скорости, а в работоспособности.
Поможет ли замена формул макросами?

Иван, приветствую. Довольно сложная конструкция у вас создана, если бы прислали файл сказал бы более подробно.
Нужно в первую очередь облегчить сводную таблицу и сделать двоичным листом файл, макросы помогут, если формул довольно много. Так же вполне допускаю что в структуре файла большое количество мусора. Сколько весит файл?

Елена:

Большое спасибо! У меня многолетняя проблема с тормозными excel-ными файлами, я их «лечила» многими способами, но у Вас их на два больше, чем я знала. Вот они и помогли!

Рад помочь! Эту подборку я тоже собирал годами :)

Сергей:

Добрый день!
Подскажите, как можно переписать макрорекордером контсруктцию с ДВССЫЛ и обновление данных из другого файла. Подозреваю, по причине медленной работы в том, что функция ДВССЫЛ работает на основе обновляемых данных из другого файла.
Строчек и столбцов не много…порядка 500 строк и 10 столбцов на 30 листов.
Буду благодарен за дельный совет.

Добрый день!
Тут наверно макрорекордером не получится.
Нужно написать отдельно код или посмотреть сам файл.
Вполне возможно, что из-за количества листов.
Напишите мне на почту решим nizotov@excelworks.ru

Дмитрий:

Добрый день ,может подскажете office 2016 64bit был и 32bit (ОЗУ 4Гб) при копировании или переносе ячеек в Excel возникает ошибка очистки буфера обмена,И очень сильно тормозит excel .Подскажите куда копать?А то решение я так найти и не смог.

Здравствуйте. Недавно встречался с такой ошибкой, помню что такая ошибка бывает возникает из-за программ переключения языка или автопроверки, например arum switcher или punto switcher (помогло). Так же может быть связано с тем, что какая-то программа дополнительно использует буфер обмена — браузера опера или скайпа.

Дмитрий:

Переключатели раскладки не установлены, скайп выключен, из браузерв только хром с 1 вкладкой, запущен тим вьювер хостовый, но он так же работает и на других ПК. Переустанавливал офис 32 на 64 не помогло.

Хмм, надо искать где еще используется буфер обмена и что препятствует копированию, навряд ли это ошибка Excel.

Светлана:

А если в файле Excel несколько вкладок: на первой формулы ссылающиеся на 2 и 3 вкладку.
На 2 и 3 вкладке порядка 1,5млн строк? В этом случае уже ничто не поможет?
Какой подход применять в случае если нужно обработать в excel такой большой объем данных (1,5млн строк*20столбцов)? Access или макрос?

Светлана, добрый день!
Если нужно работать именно в Excel, то можно воспользоваться Power Pivot, надстройка доступна начиная с версии 2010.
В ближайшее время на сайте появится ряд статей про нее.

Галина:

Пожалуйста помогите! Мне нужно сделать меню для работы. Суть в том, что в файле Excel есть лист меню и несколько листов с блюдами по видам (каждый вид блюд- на отдельном листе: салаты, первые, вторые блюда, закуски, напитки, выпечка, и т.д.).
Что нужно:
-при выборе в листе меню из выпадающего списка названия и выхода блюда- должны автоматически заполниться строки с количеством продуктов, входящих в это блюдо исходя из выхода (пример такого расчёта- в меню в столбцах «хлеб»), а также ячейки в номером диеты и номером раскладки блюда
-ширина строки в выпадающем списке по ширине самого длинного названия блюда
-возможность добавления новых блюд в листы с блюдами
-автоматическое добавление названий новых блюд в лист «список блюд» и автоматическая сортировка списков блюд по алфавиту
-файл получается огромный и тормозной (пример упрощён очень сильно), можно его ускорить?

Реально сделать такое в Excel или нет? Заранее спасибо.

Галина, добрый день! Да, конечно можно сделать и ускорить. Ответил на почту.

Leave a Comment:

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

семь − три =