Excel тормозит. Что делать?

Рубрика: 7. Полезняшки Excel

Мой знакомый прислал мне Excel-файл содержащий порядка 200 строк без формул и связей, и весящий около 28МВ! Для перехода курсора из ячейки в ячейку требовалось несколько секунд. Первое, что я сделал, это удалил всё форматирование, очистил все пустые ячейки. Не сильно помогло. Удалил все данные. Ситуация чуть улучшилась, но четко было видно, что файл Excel тормозит. Задал вопрос в Google – о чудо! – нашел ответ.

GPlay пишет:
Наверное это побочный эффект копирования. Похожая вещь возникает при вставке данных целыми листами или столбцами/строками из файлов, созданных 1С и подобных (ERP) систем: они генерируют в файл «пустые» ячейки (забитые пробелами) и объекты типа Надпись. В итоге со временем файл становится очень большим.
В Excel2003: меню Правка → Перейти → Выделить → Объекты.
В Excel2007: вкладка Главная → Найти и выделить → Выделение группы ячеек → Объекты.
Весь процесс сопровождался долгими лагами, но в итоге выделилась какая-то непонятная ерунда, которую я удалил, после чего торможение пропало.

Скачать статью в формате Word Тормоз

К сожалению, в моем файле оказалось почти 14 000! :(. Чтобы увидеть все объекты, пройдите по меню: вкладка Главная → Найти и выделить → Область выделения:

Часть объектов можно увидеть справа на экране; один объект под номером 13 697 выделен в списке и на экране:

При попытке выделить все объекты Excel зависал (через 30 минут мне надоело, и я снял задачу).

Второе обращение к Google «навело» меня на макрос, предложенный The_Prist. Я его немного модернизировал, и получил:

Sub DeleteAllTextBox()

Dim oSh As Shape

For Each oSh In ActiveSheet.Shapes

oSh.Delete

Next oSh

End Sub

То есть, я решил не выделять все объекты сразу, а запустить макрос, который будет находить объекты по одному и удалять. В моем файле макрос справился с уничтожением 14 000 объектов за 5 минут! 🙂

Прилагаю файл Excel2007, содержащий эти злосчастные 14 000 объектов Пример тормоза

P.S. (написано 12 октября 2012 г.) Надо заметить, что пост пользуется популярностью — около 500 просмотров в месяц. С одной стороны это приятно мне. С другой стороны, это минус Microsoft, который не может справится с такой простой проблемой… Посетители благодарят меня за раскрытие темы (более 10 комментариев), а 10 октября 2012 г. Антон предложил оригинальное решение, основанное на архивной природе современного формата Excel-файлов. О чем я решил более подробно написать отдельно — см. Excel  «тормозит». Что делать? Дубль 2

Комментарии: 109 комментариев

спасибо.

Огромное спасибо! После многочисленных копипастов в документе обнаружилось аж 56000 объектов. Excel при работе отъедал 600 метров оперативки. Справился только благодаря макросу.

Вот уж, действительно, спасибо! 🙂

И от меня большой тебе респект за этот макрос, он мне очень помог реально!!!

Макрос просто спас! Спасибо огромное! до этого приходилось восстанавливать файлы из резервных копий.

Уважаемый, да вы просто меня спасли. Спасибо огромное!

Автор, вы с Большой буквы М-А-Л-А-Д-Е-Ц, за то что делитесь с несведущими такими полезными знаниями!!!!! Вы лишили меня «удовольствия» испытывать ежедневно свою нервную систему 🙂

Реально большое СПАСИБО!

Огромное спасибо!!!

Спасибо, очень помогло

Автору СПАСИБО!

Автор просто молодец! Макрос действительно помог избавиться от тормозов в экселевском файле. Спасибо огромное!

P.S. если у кого возникнет вопрос «а как создать и запустить макрос» делайте так: сохраните файл с поддержкой макросов, перейдите на вкладку «Вид», выберите кнопку «Макросы» и создайте макрос с кодом, который приводит автор, далее нажать на кнопку «Выполнить» и будет вам счастье!)

У меня была похожая проблема. Простой переход с одного листа на другой занимал более минуты. Оказалось, что каждом листе было более 4000 скрытых шэйпов!!!!! Я сделал так. Так как формат 2007-2012 — это тупо архив ZIP, то я открыл этот архив ВинРаром и прибил папку drawings.
При загрузке файла Эксель ругнулся на контент, подправил всё, пересохранил и никаких тормозов.

Антон, спасибо. Классное решение проблемы! Обязательно опишу подробнее в одной из заметок.

А я решил проще. На главной выбираем Условное форматирование — Удалить правила — Удалить правила со всего листа.
Несколько секунд и все ОК.

Андрей, наверное, это поможет, если проблемы с условным форматированием. Но… на объекты, размещенные на листе это не повлияет, и файл по-прежнему тормозит… 🙁

Попробуйте отменить отслеживание изменений

Андрей, спасибо огромное!!!! Вы меня спасли!!!! Ваше решение помогло, теперь я не буду тратить нервы и время на то, чтобы просто перейти с ячейки на ячейку))))

Огромное спасибо выручили!

Искренне присоединяюсь к благодарностям — помогло!!!

Спасибо большое Вам! все просто и функциолнально! для меня чайника, Вы просто гений…

[…] работают нормально. Анализ (и Google) дал прекрасную ссылку — правда к тому времени мы уже догадались грохнуть […]

А у меня один конкретный файл проблемный. Открывается долго (пишет- не отвечает) и именно при попытке удалить столбцы виснет насовсем. Только с этим файлом проблема. Не поможете?

Сергей: Присылайте, посмотрю…

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

Сергей: у меня файл открылся. Ничего особо критичного в нем не нашел… Обратите внимание, вы задаете форматирование не только той области, с которой работаете, но и всего листа целиком. Это может замедлять работу Excel. Кроме того, на листе Март были какие-то непонятные нули внизу листа… Что смог, поправил. Попробуйте…

Екатерина: ой, спасибо огромное!!!! работает!!!! учту все ваши замечания обязательно. СПАСИБО.

Спасибо большое Вам! Я уже несколько месяцев мучался с этими таблицами))))

Огромное СПАСИБО Вам за то, что разобрались с недоработками мелко-мягких! Кстати, ссылку на Ваш волшебный скрипт я нашел именно на их сайте техподдержки! Впору требовать у Билла вознаграждение за отловлю его жуков! 🙂
Реально помогло!!!

Спосибо. Очень помогло.

А что делать если среди объектов есть нужные ?

Вадим, если это возможно, перенесите нужное содержимое на отдельный лист, прежде чем запустить макрос. Макрос обрабатывает только текущий лист.

Огромное спасибо! Очень полезная информация!!!

У нас был случай, пользователь создал 16 тыс столбцов, файл вырос до полусотни метров..тоже тормозил..

Добрый день. У меня такая же проблема, файл эксель долго обдумывает каждое действие.
в таблице формул нет есть макрос. Попробовал использовать ваш макрос, но не понял сделал он что ни будь или нет проблема осталась.
Может быть вы сможете посмотреть? Как прикрепить файл?

Огромное спасибо!!! Проблему решила и очень счастлива!

Огромное спасибо за макрос!!! РЕСПЕКТ!!! со вчерашнего дня маялся! Теперь Все ОК!

Огромное Спасибо!!!
Полгода маялся с таблицами, полагая что причина тормоза их большой объём.
СПАСИБО!

Большое спасибо, минуты и все летает. И файл урезался в 6 раз. 🙂

Добрый день,
Подскажите пожалуйста, а как создать этот макрос?
Ни разу этого не делала(

Яна, во-первых, посмотрите иной вариант решения проблемы (без создания макроса) — Excel «тормозит». Что делать? Дубль 2; во-вторых, если всё же без макроса не обойтись, почитайте заметку Excel. Суммирование по ячейкам, выделенным цветом; в ней описано, как создать свой первый макрос.

Спасибо друг, дай Бог тебе здоровья!

«Прилагаю файл Excel2007, содержащий эти злосчастные 14 000 объектов»
Скачал этот файл ради интереса. Открыл в excel 2013, выделил все объекты (…Выделение группы ячеек → Объекты.) и удалил. Выделение заняло ~12 секунд, удаление еще ~18 сек. Еще бы))) 32 Гб оперативки и i5 4670, нагрузка на проц менее 30 %. Хотя в исходном состоянии перемещение по ячейкам немного тормозило. Хвастаюсь? Нет, это для сравнения.

Здравствуйте, подскажите макрос, чтобы удалял только «Text box» и со всех листов книги сразу.

Большое человеческое СПАСИБО!!! Реально помогает! (Причем делала макрос впервые, всё получилось)

Имеем таблицу, около 5000стр. Стоит авто фильтр.
Внизу считается сумма =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;K4:K5000)
При изменении автофильтра , в Excel 2010 пересчет занимает около 5 с., тот же файл, та же операция на более сильном компьютере в Excel 2013 занимает около 45с и 80%-90% загрузки процессора.
В чем может быть проблема?

Update. Тормоза происходят не только при изменении автофильтра, но и при ручной пересчете листа.
Компьютер с Excel 2010 намного слабее, чем компьютер с Excel 2013 на котором наблюдаются тормоза.

Пришлите файл в личку. У меня тоже Excel 2013. Попробую «покрутить»…

скажите, пожалуйста, Вам ответили?
у мен ятож фильтр тормозит и приручн пересчете…

Спасибо за скрипт. Очень помог. Единственный момент, если на листе имеются комментарии к ячейкам, но при выполнении скрипта они тоже удаляются вместе с «TextBox». Возможно ли решить эту задачу?

Я правильно понял, что «комментарии к ячейкам» выполнены в виде текстовых блоков? Если, да. То не представляю, как их отделить от «мусорных» текстовых блоков… Если таких комментариев не очень много, попробуйте их все выделить (удерживая Ctrl), и перенести на пустой лист. После обработки макросом, верните комментарии назад.

Спасибо. Я ниже предложил решение. У меня работает.
Очень вам благодарен за находку, вы спасли целый отдел на работе моей супруги 🙂 Бедные девчонки уже месяца три обзывали майкрософт нехорошими словами

Кстати, чтобы выделить объекты, не обязательно кликать по каждому, удерживая Ctrl, достаточно выделить один и нажать Ctrl+A. Выберутся все подобные объекты (это я испробовал на пустых, невидимых текстбоксах, в моем случае их было 60000+, только отображались они в итоге минут 5 на достаточно мощной машине, не говорю уж про удаление или другие действия)

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

Sub DeleteAllTextBox()
Dim oSh As Shape
For Each oSh In ActiveSheet.Shapes
If oSh.Type = 17 Then oSh.Delete
Next oSh
End Sub

DonRozario, отличное решение! Если кому-то понадобится удалять только определенные фигуры, воспользуйтесь перечнем типов фигур, например, здесь

О, гран мерси! Я вчера полтора часа убил на поиски числовых значений типов, так и не нашел, в итоге методом тыка нащупал 🙂

DonRozario, можешь дополнить свой макрос, что бы он удалял со всех листов открытого документа?

Baguzin — вы компьютерный ГЕНИЙ !
DonRozario — вы УМНИЦА !
Огромное ВАМ СПАСИБО !

Автору медаль за статью! Очень полезно!

Спасибо
огромное за макрос
все быстро удалилось не нужное и спасся документ

Спасибо, огромное. Несколько месяцев мучился с EXEL файлом. По несколько часов тратил на свод информации для отчетности. Благодаря Вам в день отчетности иду на работу с величайшим настроением.

Спасибо большое, 3 недели мучился , пока к вам не заглянул) но нашел стандартное решение через «инспектор документов» 40000 знаков удалялись около 7 минут.

У меня точно также тормозит файл, который весит не так много! Но я не разбираюсь в макросах, скажите, Вы решаете такие проблемы за оплату? Если да, очень жду письмо на lexandra.f@gmail.com/ если нет, посоветуйте как быть чайнику в таких вопросах?

Вышлите мне свой файл.

Спасибо большое! Искала ответ на вопрос большого веса excel файлов больше года!

Спасибо вам, огромное человеческое спасибо!!!!!! Я уже думала комп об стену разобью просто!)

Огромное спасибо!

Чума! Спасибо большое!

Происходит торможение под фильтром. 1.Просто видимой области при перемещении стрелками. 2. при заполнении какой-либо области(стобец, строка) любой инфой (текстом, формулой). Усугубляется при добавлении очередного фильтра. По 1-5сек при перемещении на другую видимую ячейку. Файл большой. До какого-то времени все работало-летало, потом тормоз. Связать с с какими-то определенными моими действиями, отличающимимся от предыдущих, не могу.

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

Почитайте заметку Excel «тормозит». Что делать? Дубль 2. Откройте Excel-файл, как архив, и пройдите по папкам xl -> worksheets. Каждый лист представлен отдельным файлом *.xml.

Спасибо, это я читал.
Но порой это дает представление о размере данных на листе, а не сложности расчетов на этом листе.
А я ищу наиболее «тормознутые» листы из-за сложностей расчетов (формул) на листе.

Огромное вам спасибо !
вы меня спасли.

Добрый день.

У меня в файле много картинок. Причем они автоматом подставляются в зависимости от выбранных команд (файл — турнирная таблица).

Если удалять drawings то все настройки слетают. Если запускать макрос, то он просто удаляет все картинки и всё перестает лагать.

Без картинок файл летает, а с ними любое изменение происходит в течении 10-30 секунд, что очень раздражает. Есть ли еще какие-нибудь средства?

Запустите вот этот макрос, он удаляет только объекты TextBox не затрагивая рисунки и комментарии.

Sub DelTextBox()
Dim oSh As Shape
For Each oSh In ActiveSheet.Shapes
If oSh.Type = 17 Then oSh.Delete
Next oSh
End Sub

Если в книге несколько листов, макрос надо запускать на каждом листе.

Спасибо. Не летает, но на много быстрее

О чудо! Спасибо — помогло!

Спасибо вам огромное! 🙂 Я сегодня три часа убила, пытаясь понять, почему у меня вдруг стал безбожно тормозить фильтр на файле всего-то в 2500 строк (ну, он всегда особой быстротой не отличался, но все ж не на полчаса зависал, как сегодня). По второй статье поудаляла все, что можно, но не помогло. С горя разобралась с созданием макроса и — чудо! — теперь фильтр просто летает 🙂

Добрый день! Файл Эксель стал постоянно делать перерасчет формул при любом изменении в ячейках, все это загружает процессор на 100%. Что можно сделать? Граф. объекты макросом удалял и папку drawings тоже.

Максим, пришлите файл на s_bag@mail.ru

Файл объемный содержит порядка 30 листов информации, в том числе таблиц с данными, которые я бы не хотел пересылать.

Максим, в таком случае я могу лишь высказать предположение… Не исключено, что вы используете так называемые летучие функции (англ. – volatile). Летучие функции пересчитываются, даже если данные, относящиеся к формуле, не изменились. Поэтому летучие функции замедляют работу. Вот некоторые действия, которые могут инициировать пересчет летучей функции: ввод новых данных (даже в другой книге), удаление или вставка строки или столбца, выполнение действий автофильтра, переименование листа. Для сравнения, функция СУММ пересчитывается только если изменилось какое-либо значение, на которое ссылается СУММ. Вследствие этого свойства волатильные функции увеличивают время расчета формул.
Летучими являются следующие функции: СМЕЩ, ДВССЫЛ. ЯЧЕЙКА, СЛЧИС, ИНФОРМАЦИЯ, ТДАТА, СЕГОДНЯ (перечень не полный). Минимизируйте использование летучих функций.

Спасибо! Данные функции действительно присутствуют практически на всех листах.

Здравствуйте! Перепробовал вышесказанное, не помогло. Помогите пожалуйста!

Денис, рекомендую перейти на Excel2007 или более позднюю версию. В вашем файле много мусорных объектов на листе «Январь 2015». Я сохранил ваш файл в формате Excel2007, а затем обработал макросом. Ваш файл уменьшился с 28М до 126К. В 200 раз)) Рекомендую также почитать статью Excel «тормозит». Что делать? Дубль 2.

А чего вы зациклились на макросе, есть еще одно решение F5 вызывает окно «переход», жмем «выделить» — «объекты» — Ok. Жмем Delete — объектов нет. Правда, придется повторить операцию на всех листах документа.

ёмаё, НАКОНЕЦ ТОООО ГОСПОДИ!!!!!! РЕШИЛАСЬ проблема с тормозами комп intel i7 оперативки 8 гигов, делаешь фильтр и все, как будто дал вычислить массу черной дыры в космосе , УРА все летает

Автор спасибо.

Огромное спасибо, очень помог!

Благодарю!

Благодарю, очень помог.
20 000 объектов. что б их.

ОГРОМНОЕ СПАСИБО) а то замучался ппц))

Помогло! Спасибо!
Как жаль те миллионы, которые мучаются на офигенных машинах из-за такой вот фигни!

Изменив Office 2010 на Office 2016, Excel стал тормозить при работе с файлами, которые были выгружены/созданы 1С и подобных (ERP) систем…, а именно при попытке скопировать/переместить какие-либо ячейки.
Комп новый, не в нем проблема, к тому же с созданными Excel файлами, такой проблемы не наблюдается. Файл маленького размера до 100 кб. Файл преобразовывал в xlsx. Принтер, который по умолчанию — подключен. Работа в Windows 10 c Office 2016, установлены все обновления. Антивирус отключен.
Ни один из нижеперечисленных методов результата не дал:
1. Поиск объектов: вкладка Главная → Найти и выделить → Выделение группы ячеек → Объекты. Не найдено ни одного объекта!
2. Закрытие Outlook, так как торможение файлов, так же может наблюдаться при открытом почтовом клиенте Outlook, если у него установлены параметры отслеживание действий с файлами для Пользователя. Соответственно отменил отслеживание действий с файлами.
3. Открытия файла как архив. Отсутствие папки drawings.
4. Условное форматирование → Удалить правила → Удалить правила со всего листа.
5. Очистить форматы – не помогло
В чем может быть проблема?!

Покажите файл, посмотрю что с ним.
Залейте на http://rghost.ru/ или любой другой обменник.

Спасибо, дружище!

Реально все было точно как в описании (куча объектов и потом зависло) Макрос сработал!))

а что дописать в макрос чтобы оставить текст-боксы с картинкой или текстом?

Что-то вроде такого, жаль не работает

Sub DeleteAllTextBox()
Dim oSh As Shape
For Each oSh In ActiveSheet.Shapes
If oSh = "" Then oSh.Delete
Next oSh
End Sub

Кирилл, у объектов Shape есть два свойства, которые могут вам помочь: Shape.Type и Shape.AutoShapeType. Например, если вы хотите удалить только текстовые блоки (все) добавьте проверку типа объекта:
Sub DeleteAllTextBox()
Dim oSh As Shape
    For Each oSh In ActiveSheet.Shapes
        If oSh.Type = msoTextBox Then
            oSh.Delete
        End If
    Next oSh
End Sub
Как проверить содержимое текстового блока сходу не придумал…

я решил свою проблему таким макросом

Sub DeleteAllShapes()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
If Not (Shp.Type = 1 Or Shp.Type = 24 Or Shp.Type = 13) Then Shp.Delete
Next Shp
End Sub

т.е. если в таблице пустые рамки не относятся к AutoShape (Type = 1), SmartArt graphic (Type = 24) и Picture (Type = 13) — тогда идет удаление

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

остальные типы Shapes смотреть тут
https://msdn.microsoft.com/en-us/library/office/aa432678(v=office.12).aspx

AutoShape — что стандартная автофигура Excel

в идеале собрать макрос что то вроде:

If Shp.Type = null or «» Then Shp.Delete

чтобы не перечислять все типы шейпов в макросе
удалив только пустые

Спасибо автору за информацию!
Комментарии то же к делу.

Огромное спасибо. Долго соображала как запустить макрос и куда его вообще писать. После долгой и упорной работы — всё получилось!!!!!!!!! УРА!

Супер!!! Все работает, мучилась несколько месяцев, теперь не нарадуюсь!!!!

Спасибо за статью, очень познавательная. К сожалению в моем случае ваши рекомендации не очень сильно помогли. Файл весит 30 мб. притом и очень неповоротлив. 2013 Excel постоянно что-то пересчитывает. Ни объектов, ни форматирования нет. Все удалил. Подскажите может, кто сталкивался как проанализировать какой лист нагружен формулами.

Алексей, пришлите файл в личку. Если смогу, помогу.

Алексей, почитайте заметку Excel «тормозит». Что делать? Дубль 2. Если открыть ваш файл как архив, увидите, что очень много вычислений, и большой объем занимают листы. Если «провалиться» в папку worksheets, увидите, какие листы «весят» больше других.

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

Спасибо Вам!
С наступающим новым годом!

Огромное спасибо!!!!!! Работает

Автору большое спасибо! Полтора дня мучений было, потом воспользовался советом!


Прокомментировать