backtotop
Как cчитать НДС в Excel? Что такое НДС?
Как столбцы сделать строками? Транспонирование в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

Главный признак тормозов — это размер ползунка, он очень маленький, когда файл сохранен неверно, как на картинке.

тормозит Excel

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

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

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

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

sub DelOb()
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

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

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

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

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

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

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

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

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

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

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

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

12. Перейдите на поздний Excel — 2013 или 2016

Разработчики нашего любимого редактора действительно не останавливаются в развитии продукта. Проведя ряд экспериментов, я выявил, что большинство сложных операций таких как расчет ячеек, выполнение макроса, сохранение и другие в версии 2016 выполняются быстрее ~на 15% от 2007.

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

Так что еще один хороший способ ускорить работу в Excel — перейти на MS Office 2013 и выше.

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

 

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

Логичное продолжение статьи — оптимизация ресурсов компьютера под использование Excel — читайте в статье.

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

 

Поделитесь нашей статьей в ваших соцсетях:

(Visited 27 530 times, 221 visits today)
Как cчитать НДС в Excel? Что такое НДС?
Как столбцы сделать строками? Транспонирование в Excel

Комментарии:

( 30 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 или нет? Заранее спасибо.

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

Ольга:

Спасибо большое!
Помог перевод в бинарную систему. Размер уменьшился с 8 Мбт до 12 Кбт!

Отлично! Пожалуйста! :)

Оставьте ваш комментарий здесь:

You have to agree to the comment policy.

13 + 15 =