Таблица тормозит и зависает: пошаговая диагностика, которая реально ускоряет Excel. Разбор реальных причин, почему Excel начинает тормозить: volatile-функции, массивные формулы, условное форматирование, огромные диапазоны, ссылки на другие книги. Пошаговая диагностика + файл.
В прошлой части мы ловили логические ошибки. Сегодня — другая боль. Файл открывается 15 секунд. Пересчёт — ещё 10. Прокрутка — с задержкой. Компьютер мощный, Excel новый. Значит проблема внутри таблицы.
Разберём реальную диагностику, которую можно применить к любой “тяжёлой” книге.
Файл с медленной и оптимизированной версией лежит в Telegram — можно сравнить поведение.
Мини-кейс
Финансовый отчёт на 25 000 строк. Несколько листов. Пара сводных. Каждое изменение — 5–7 секунд ожидания. Где искать причину?
Шаг 1. Проверяем режим пересчёта
Формулы → Параметры вычислений. Если стоит “Автоматически” — Excel пересчитывает всё при каждом изменении.
Что важно
Если таблица тяжёлая, временно переключите на “Вручную” и нажимайте F9 только при необходимости. Это не решение проблемы, а временная мера.
Шаг 2. Volatile-функции (самые коварные)
Есть функции, которые пересчитываются всегда:
- СЕГОДНЯ()
- СЕЙЧАС()
- СЛУЧМЕЖДУ()
- ДВССЫЛ()
- СМЕЩ()
Если таких формул много — Excel пересчитывает всю книгу.
Проверка
Поиск (Ctrl+F) → введите “СЕГОДНЯ” или “СМЕЩ”. Если их десятки — причина найдена.
Решение №1
Заменить volatile-функции на более стабильные аналоги. Например, вместо СМЕЩ использовать ИНДЕКС.
Решение №2
Вынести volatile-функцию в одну ячейку и ссылаться на неё.
Шаг 3. Огромные диапазоны
Формула:
=СУММ(A:A)
Каждый пересчёт Excel анализирует миллион строк. На 10 столбцах — уже 10 миллионов ячеек.
Проверка
Посмотрите, используются ли диапазоны типа A:A или 1:1.
Решение №1
Ограничить диапазон:
=СУММ(A2:A25000)
Решение №2
Преобразовать данные в таблицу (Ctrl+T) и использовать структурированные ссылки.
Шаг 4. Массивные формулы на тысячи строк
Если используется:
- Массив с Ctrl+Shift+Enter
- Много вложенных ЕСЛИ
- Сложные СУММЕСЛИМН
Excel пересчитывает каждую строку отдельно.
Проверка
Посмотрите строку формулы — если она огромная и вложенная, скорее всего, она тяжёлая.
Решение №1
Вынести часть вычислений во вспомогательные столбцы.
Решение №2
Использовать Power Query для подготовки данных.
Шаг 5. Условное форматирование
Часто забывают про него. Если условное форматирование применяется к целому столбцу A:A — Excel проверяет миллион строк.
Проверка
Главная → Условное форматирование → Управление правилами. Посмотрите диапазоны применения.
Решение
Ограничить диапазон реальными строками.
Шаг 6. Ссылки на другие книги
Если формулы ссылаются на внешний файл — Excel проверяет его при пересчёте. Даже если файл закрыт.
Проверка
Данные → Изменить связи.
Решение
Импортировать данные внутрь книги или использовать Power Query.
Шаг 7. “Грязный” формат листа
Нажмите Ctrl+End. Если курсор улетает на строку 1 048 576 — значит Excel “думает”, что лист используется полностью.
Решение
Удалить пустые строки ниже данных → сохранить файл.
Диагностический алгоритм ускорения
Когда файл тормозит:
- Проверить volatile-функции
- Проверить диапазоны A:A
- Проверить условное форматирование
- Проверить внешние ссылки
- Проверить Ctrl+End
- Упростить массивные формулы
Идти по порядку. Не переписывать всё сразу.
Что если…
…таблица нужна огромная?
Используйте Power Query для подготовки данных.
…всё равно тормозит?
Разделите книгу на расчётную и отчётную.
…Excel зависает полностью?
Проверьте надстройки.
Контроль результата
После оптимизации:
- Сохраните копию файла.
- Засеките время открытия до и после.
- Засеките время пересчёта.
Если разница в 2–3 раза — вы на верном пути.
В Telegram — файл:
— медленная версия
— оптимизированная версия
— сравнение времени
В следующей части разберём все реальные причины ошибки #Н/Д.