← Все курсы · Курс Excel

Когда книга работает медленно: выясним, что на самом деле пересчитывает Excel

Майкл Носито · Обновлено в августе 2026 г. · Все числа на этой странице были обработаны до их публикации.

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

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

Краткая версия. Excel пересчитывает цепочку зависимостей. Все, что заставляет цепочку касаться миллиона строк, делает цепочку дорогой.

Сначала сделайте файл снова пригодным для использования.

1. Формулы, Параметры расчета, Руководство. Теперь редактирование не вызывает полного перерасчета.
2. Нажмите F9 один раз и посчитайте секунды. Это ваш базовый уровень, и он нужен вам, чтобы знать, помогли ли изменения.
3. Shift+F9 пересчитывает только активный лист, что сужает стоимость.
4. Когда закончите, выключите «Вручную». Общая книга, оставленная в режиме «Вручную», является источником неправильных чисел, которые выглядят правильно.

Восемь причин: самая дорогая в первую очередь

1. Летучие функции

NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, CELL, INFO. Они пересчитываются всякий раз, когда что-либо в книге меняется, как и все, что от них зависит. Найдите их с помощью Ctrl+Ф, посмотрите в формулах, найдите OFFSET( и INDIRECT(.

заменять OFFSET с INDEX, который не является нестабильным. Заменять INDIRECT с реальной ссылкой или именем таблицы. Заменять TODAY() в десять тысяч рядов с одной ячейкой, вмещающей TODAY() на что указывают строки.

2. Ссылки на целые столбцы внутри тяжелых формул.

=SUMIFS(B:B, A:A, $D2) скопировав 50 000 строк, Excel запрашивает около миллиона строк 50 000 раз. Преобразуйте источник в таблицу и используйте структурированные ссылки, которые охватывают только используемые строки и автоматически расширяются:

=SUMIFS(Sales[Amount], Sales[Region], $D2)

3. Поисковые запросы, которые должны быть сводными или запросами

Существует 200 000 поисков, позволяющих получить сводку из сорока строк. Сводная таблица выполняет то же агрегирование за один проход. Еще лучше сделать это в Power Query, чтобы это происходило один раз при обновлении, а не при каждом вычислении.

4. Условное форматирование для целых столбцов

Это самый тихий. Применение правила к A:A вместо A2:A5000, а затем копирование и вставка ячеек разбивает их на сотни перекрывающихся правил. «Главная страница», «Условное форматирование», «Управление правилами», затем «Этот рабочий лист». Если список прокручивается, это ваша проблема. Удалите их все и повторно примените два или три к реальным диапазонам.

5. Завышенный запас хода.

Ctrl+Конец должен попасть в последнюю ячейку данных. Если он попадает на XFD1048576, файл содержит миллион пустых отформатированных строк. Выберите каждую строку под данными, щелкните правой кнопкой мыши, выберите «Удалить», затем сохраните, закройте и снова откройте. Требуется повторное открытие: используемый диапазон пересчитывается при загрузке файла.

6. Ссылки на закрытые книги

Данные, Редактировать ссылки. Каждая внешняя ссылка заставляет Excel искать информацию, а ссылка на файл в медленном сетевом ресурсе может добавить много секунд. Разбейте те, которые вам не нужны, и вставьте значения там, где исходный код закончен.

7. Располагайте формулы больше, чем нужно

A SUMPRODUCT или динамический массив с диапазоном в миллион строк выполняет расчеты в миллион строк. Привяжите диапазон к данным и отдайте предпочтение современным функциям динамических массивов, которые обычно работают быстрее, чем устаревшие формы Ctrl+Shift+Ввод, выполняющие ту же работу.

8. Формы, изображения и скрытые объекты

Главная страница, «Найти и выбрать», «Панель выбора» отобразит их список. Книга, в которую неоднократно вставлялись диаграммы, может накапливать сотни невидимых объектов, каждый из которых перерисовывается.

Структурные исправления

Как применить это в своей работе

1. Нажмите F9 на самом медленном файле и запишите номер, прежде чем что-либо трогать.
2. Искать OFFSET( и INDIRECT( и посчитайте попадания. Обычно здесь находятся секунды.
3. Откройте «Управление правилами» на каждом листе и посмотрите, сколько существует условных форматов. Часто это число исчисляется сотнями.
4. Нажмите Ctrl+Конец на каждом листе. Исправьте все отклонения, затем сохраните и снова откройте.
5. Переместите одно повторяющееся вычисление в Power Query и засеките разницу. Именно это сравнение убеждает команду изменить способ создания файла.

Единственная привычка, которую нужно сохранить

Когда работа книги замедляется, спросите, что изменилось, а не что удалить. Файлы редко замедляются постепенно; они замедляются, когда кто-то добавил столбец поиска или применил формат ко всему листу.

Сколько времени сейчас занимает один перерасчет вашей основной книги?