19. Производительность формул

На маленькой таблице любая формула считается мгновенно. Но в файле на 100 000 строк неудачные приёмы превращают каждое изменение в ожидание. Главные источники тормозов:

• Повторный поиск. =ЕСЛИ(ЕНД(ВПР(…));0;ВПР(…)) ищет одно и то же дважды. Один поиск: =ПРОСМОТРX(B2;G2:G6;H2:H6;0) (четвёртый аргумент — что вернуть, если не найдено), =ЕСНД(ВПР(…);0) или ПУСТЬ, чтобы сохранить результат в переменную. Если одно значение нужно в нескольких столбцах, найдите номер строки один раз во вспомогательном столбце через ПОИСКПОЗ, а потом берите ИНДЕКС.
• Ссылки на целые столбцы вроде B:B в СУММЕСЛИ и особенно в формулах массивов заставляют Excel просматривать миллион строк. Берите точный диапазон, «умную» таблицу или разлитый диапазон J2#.
• Летучие функции — СЕГОДНЯ, ТДАТА, СЛЧИС, СЛУЧМЕЖДУ, СМЕЩ, ДВССЫЛ, ЯЧЕЙКА — пересчитываются при любом изменении в книге, а вместе с ними и все зависящие от них формулы. СМЕЩ заменяют на ИНДЕКС или ВЗЯТЬ: =СУММ(ВЗЯТЬ(D2:D13;H8)). Дату отчёта держат в одной ячейке-параметре, а не пишут СЕГОДНЯ() в каждой строке.
• Одна формула вместо сотни. =СУММЕСЛИ(B2:B13;J2:J6;D2:D13) в одной ячейке разливается на весь список и считается за один проход.

Режимы вычислений (Формулы → Параметры вычислений): «Автоматически» — пересчёт после каждого изменения; «Вручную» — пересчёт по F9 (Shift+F9 — только текущий лист). Ручной режим выручает при правке тяжёлой модели, но легко забыть пересчитать и отправить устаревшие цифры.

Честно о тренажёре: таймера пересчёта здесь нет, таблица маленькая, и разницу в скорости вы не увидите. Проверка смотрит на приёмы: результат должен остаться прежним, а медленные конструкции — исчезнуть.