Почему Excel тормозит? 8 причин и как это исправить
Вступление.
Всем привет.
Знакома ли вам такая ситуация: приходишь на работу, открываешь свой файл Excel, и идёшь за кофе. Возвращаешься, а файл всё ещё открывается. Спустя несколько мучительных минут открывается, пытаешься поставить фильтр и снова ждёшь... Если да, то, возможно, эта статья тебе поможет. Да даже если не было, всё равно прочитай. Вдруг пригодится :)
Как обычно в своих постах (ну да, последний был года два назад, лень творческий кризис) я не изобрету колесо. Хотя вот кто-то смог. Всё это уже есть на просторах интернета. Так на то он и интернет: хочешь читать - читаешь, не хочешь - не читаешь. Я просто делюсь своим опытом, надеясь, что это кому-то поможет :) В общем, поехали.
1. Условное форматирование.
Достаточно часто встречаю это в файлах, которые мне присылают. Условное форматирование - очень увлекательный и полезный инструмент. Но и довольно ресурсоёмкий. Особенно, если используется какая-то формула. И если создать правило целиком для столбца, то высока вероятность того, что файл будет тормозить.
Также проверьте, нет ли в принципе тех правил, которые уже старые и ненужные. В случае нахождения таких, удаляйте их.
Что делать?
Тут два варианта.
1. Если правило нужно, то меняйте диапазон на конкретный, выделив только те ячейки, которые нужно разукрасить.
2. Если правило не нужно, то удаляйте его без какого-либо сожаления.
2. Проверяем активный диапазон.
Тут вот какое дело. Не всегда последняя строка таблицы является последней активной ячейкой, которую видит Excel. Проверить это очень легко: нажимаем Ctrl+End и смотрим, в какой ячейке оказались. Если далеко-далеко внизу, то это как раз ваш случай.
Что делать?
Нужно удалить все неиспользуемые ячейки. Выделяем все строки под таблицей (проще всего сделать с помощью Ctrl+Shift+Enter), далее удаляем строки. ВАЖНО! После удаления нужно обязательно сохранить книгу! Сохранили? Отлично, проверяем ещё раз, нажав сочетание Ctrl+End.
3. Криво преобразовали диапазон в "умную" таблицу.
Проверяя различные работы не так уж и редко с этим сталкивался. При преобразовании данных в "умную" таблицу (Главная - Стили - Форматировать как таблицу или Ctrl+T) пользователь выбирает целиком столбцы. Никогда. Нет, не так. НИКОГДА так делайте. Моментально файл начнёт тормозить.
Что делать?
Решение простое. Но долгое. Необходимо преобразовать таблицу обратно в диапазон. Встаём в любую ячейку таблицы, вкладка Конструктор - слева внизу на этой вкладке Преобразовать в диапазон.
Но будьте готовы к тому, что операция это долгая (минута-две).
4. Ссылки на ячейки других книг.
Да, как бы грустно это не звучало, но если в книге у вас много (тысячи тысяч) ссылок на ячейки других книг, то файл будет тормозить.
Что делать?
Проверить (Данные - Запросы и подключения - Workbook links (Изменить связи)), а все ли связи вам нужны. Если ответ отрицательный, то разрываем связь.
2. Если связи нужны, то:
а) Держите все связанные книги открытыми (да, это может быть дико не удобно, но файл будет работать быстрее).
б) Отключите автоматический пересчёт формул.
В принципе, это вообще универсальный вариант, чтобы Excel начал работать быстрее. При включении этого параметра формулы будут пересчитываться только тогда, когда пользователь этого захочет. Например, при сохранении книги. Или при нажатии на кнопки Произвести вычисления (только на этом листе) или Пересчёт (формулы пересчитаются во всей книге). Но помните, что данная настройка работает для всех ваших файлов. То есть в одном файле поставили ручной пересчёт, в остальных будет тоже самое. И ещё важный момент. Когда вы запустите пересчёт, Excel зависнет. Ведь ему нужно будет все изменения пересчитать. Но зависать он будет только тогда, когда вы ему это разрешите, а не каждый раз при выполнении какой-либо операции на листе.
5. Сложные формулы и/или огромное количество формул.
По поводу огромного количества формул нужно иметь в виду, что если у вас в книге их сотни тысяч, то хоть ты тресни, а Excel будет тормозить (степень тормознутости зависит от вашего железа). По поводу сложных формул есть моменты, которые вы можете оптимизировать.
Что делать?
Если формул много, то отключайте автоматический пересчёт формул (см. пункт выше).
Если формулы сложные, то можно их попробовать оптимизировать. Например, у меня вот такая формула массива:
Копирую я её всего-то на 1000 строк. Но в таком виде данная операция занимает на моём железе (не самое слабое - i7 12700kf, 64 гига оперативки) около 15 секунд. А всё почему? Потому что в формуле я указывал диапазоны столбцами. Если формулу переписать вот так:
То работать она станет в десятки (!!!) раз быстрее. Так что выделение целиком столбцов в формулах - это стильно, модно и молодёжно, сам постоянно так делаю, но если формула сложная, то старайтесь выделять ячейки аккуратно.
6. Изображения.
Кто же не любит красивые картинки в Excel? :) В большинстве случаев они там не нужны, но их всё равно добавляют. Или "случайно как-то само появилось". Так вот изображения очень сильно раздувают размер файла.
Что делать?
Если изображения всё-таки нужны, то банально сжать их размер. Выделяем их, а потом вот тыкаем сюда и выбираем Электронная почта:
Кстати, выделить разом все изображения можно следующим образом: нажимаем Ctrl+G - в следующем Выделить - в следующем окне Объекты.
2. Если изображения не нужны, то просто их удаляем с глаз долой, из Excelя вон.
7. "Летучие" функции.
В английской версии звучит как volatile (волатильные), но англицизмы запрещены, поэтому пусть будут летучими. Функций этих немного, вот список:
Смысл в том, что функции эти пересчитываются ПРИ ЛЮБОМ ИЗМЕНЕНИИ, которые мы делаем на листе. То есть написали в ячейке =1+1, функции эти пересчитываются. И если их тысячи, то файл начнёт тормозить.
Что делать?
Вышеупомянутое отключение автоматического пересчёта формул (если формулы всё-таки нужны).
Если есть возможность, то формулы превращаем в значения. Копируем их, потом вставляем как значения.
8. Скрытые листы.
Вот такая простая причина :) Листы же можно скрывать. И вот кто-то давным-давно лист с огромным объёмом данных скрыл (авось пригодится), и вам он в наследство достался. Тут не должен работать принцип "нести тяжело, а бросить жалко". Если листы не нужны, без сожаления и страха их удаляем.
Что делать?
Проверить, нет ли скрытых листов. ПКМ по ярлычку любого листа - Показать (если активна, значит кому-то есть чего скрывать).
Далее всё просто. Если чего-то не нужно, отображаем - удаляем. Но убедитесь, что лист действительно бестолковый (нет ли в ваших формулах ссылок на данные с этого листа).
Бонус: сохраняем файл в формате двоичной книги (*.xlsb).
Очень действенный способ уменьшить размер файла в несколько раз и ускорить его работу. Когда-то давно на одном форуме кто-то написал примерно следующее: единственный минус формата xlsb - это то, что теперь формат xlsx вообще не нужен (там по-другому было написано немного, но вдруг мой пост будут читать дети). Но написано это было давно. И минусы всё же есть.
По поводу самого сохранения, формат так и называется - двоичная книга Excel:
Заключение.
Это далеко не все причины, но, думаю, основные и наиболее распространённые. И нужно иметь в виду, что Excel не вытягивает огромные таблицы. 500 тысяч строк, заполненных просто значениями, без формул, без форматирования уже будут трудно даваться старичку. Но для таких объёмов у нас есть Power Query/Power Pivot.
На этом, пожалуй, всё. Спасибо всем, кто осилил данную "простыню". Надеюсь, было полезно. В комментариях делитесь своим опытом, как решали вопрос оптимизации файла. А ещё давайте в комментариях мериться п устроим заочное соревнование: пишите максимальный размер файла, с которым вы работали в Excel.
Кому лень читать - вот есть видео






































