Сказ о том, как оптимизировать формулы в Excel⁠⁠

Всем доброго дня. Сегодня хочу предложить вашему вниманию решение одной реальной задачи, которую мне прислала слушательница. Сразу отмечу, что похожие вопросы были и ранее. Поэтому решил аж целую статью накатать по этому поводу :)

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

Лень читать? Вот видео

Формульный детокс: как сократить Excel-формулу размером с простыню до одной строки

Если не все, то наверняка многие сталкивались когда-нибудь с чем-то похожим:

Открываешь ячейку, а там… формула длиной в три экрана, собранная из костылей, велосипедов и какой-то матери. Если вы думаете, что это я специально написал в горячечном бреду, то нет. Самое забавное, что ЭТО даже как-то работало. И так было. Пока не появились они - счастливые обладатели компьютеров Apple. У них почему-то это не работало. Но это было не самое важное (не в обиду пользователям Apple). Куда важнее было оптимизировать формулу до адекватных размеров. Именно с таким вопросом ко мне и обратились. И сейчас я хочу представить вашему вниманию алгоритм решения похожих задач.

СПОЙЛЕР: задача была решена.

Шаг 1: определяем цель.

Прежде чем досконально разбираться в самой формуле (если честно, я этого так и не сделал), мне нужно было понять, какую вообще цель мы преследуем. Всё оказалось очень просто. Формула должна была решать тривиальную задачу: брать стоимость товара в долларах и переводить её в рубли по курсу ЦБ на определенную дату отгрузки. Казалось бы, обычный ВПР (VLOOKUP). Но автор исходника столкнулся с проблемой: курс есть на рабочие дни. На субботу и воскресенье банк его не выкатывает — нужно брать значение за пятницу. Чтобы это обойти, в формулу зашили кучу условий ЕСЛИ (IF), текстовые функции СЦЕПИТЬ (CONCATENATE), вытаскивание по отдельности дней, месяцев и годов... В общем, получился кошмар, который к тому же не работал на MacOS. Скорее всего, из-за разных стандартов в системах. Конкретную причину я не выяснял, так как после того, как отправил свой вариант, слушательница написала, что теперь всё работает даже на маках.

Шаг 2: подбираем инструменты.

После того, как наметили цель, нужно понять, что нам поможет в достижении этой цели. Так, данные нужно подтянуть из одной таблицы в другую. Всё просто: ВПР (VLOOKUP). Движемся дальше.

Шаг 3: дьявол кроется в деталях.

И вот теперь переходим к самому интересному. Если задача такая простая, почему формула-то такая сложная?! Предлагаю вам взглянуть на таблицу с датами и попробовать самим понять, что с ними не так:

Посмотрели? Поняли? Кто понял, тот молодец, кто не понял, но продолжит читать, тот тоже молодец.

А царь-то даты-то ненастоящие! Выровнены по левому краю. Значит, это текст. И нельзя с ними работать, как с нормальными датами. Именно по этой причине формула и была такой страшной. Excel не умеет сопоставлять "правильную" дату из вашего отчета с текстовой строкой "13.07.2026". Автор извлекал по отдельности день, месяц, год. Далее с помощью функции ТЕКСТ(FORMAT) преобразовывал всё это в дату, и только потом сравнивал с датой в основной таблице. Вот этот кусок:

Решение очевидное: преобразовать текстовые даты в корректные даты. Тут стоит отметить, что нельзя это сделать простым изменением формата. Нельзя и всё тут. Далее те самые 5 стадий: отрицание, гнев, торг, депрессия, принятие. И теперь решение. В общем, есть безумно простой способ сделать это. Выделяем столбец, нажимаем Ctrl + H (Поиск и замена). В поле "Найти" пишем точку "." и в поле «Заменить на» пишем тоже точку ".". Жмем "Заменить все". Excel принудительно перезапишет ячейки, поймет, что это числа, и выровняет их по правому краю. Даты вылечены! Если в датах у вас дефисы или слэшы (/), то заменяйте их.

Теперь, когда даты стали датами, выбрасываем 90% старой формулы. Нам нужен ВПР. Но как заставить его автоматически брать курс за пятницу, если дата отгрузки выпала на субботу или воскресенье?

Все привыкли писать в конце ВПР ноль (0 или ЛОЖЬ), требуя точного совпадения. Но если мы поставим туда 1 или ИСТИНА, включится приблизительное совпадение.

Как это работает: если Excel не находит в таблице точную дату (например, субботу), он автоматически берет ближайшее меньшее значение. А ближайшим меньшим значением для субботы и воскресенья в календаре как раз и будет пятница!

!!! Важнейший нюанс !!!: чтобы этот трюк сработал, таблица с курсами валют ОБЯЗАТЕЛЬНО должна быть отсортирована по возрастанию (от старых дат к новым). Иначе ВПР сойдет с ума.

О чудо: огромная простыня текста превратилась в изящное:

Бонус 1: для зажиточных бояр.

Если у вас Excel 2021 года или свежее, можно использовать преемника — функцию ПРОСМОТРX (XLOOKUP). Она еще круче, потому что ей плевать на сортировку. Даже если даты в базе идут в полном хаосе, вы просто выставляете в пятом аргументе режим сопоставления -1 (точное совпадение или следующее меньшее). И всё работает идеально.

Бонус 2: для всех.

Что делать, если вам всё-таки нужно разобраться в чужой сложной формуле, а она выдает ошибку? Не пытайтесь расшифровать её глазами.

В Excel есть встроенный отладчик. Встаньте на ячейку со страшной формулой, перейдите на вкладку "Формулы" - группа "Зависимости формул" - кнопка "Вычислить формулу" (иконка с лупой и fx).

Excel откроет окно, в котором можно посмотреть, что происходит в формуле. Нажимая кнопку "Вычислить", вы будете видеть, как постепенно, шаг за шагом, считаются внутренние функции. Вы сразу (или не сразу, если формула сложно-огромная) поймете, на каком именно этапе и почему формула спотыкается и выдает ошибку.

Резюме:

У самурая нет цели, есть только путь — но в Excel этот подход не работает. Прежде чем писать гигантские формулы:

1. Приведите данные к правильному формату.

2. Определитесь с целью.

3. Подберите инструменты и изучите их.

Стоит отметить, что сократить формулу можно не всегда. Порой логика сложная и формула должна быть соответствующей. Тут уже ничего не попишешь. Либо смотреть в сторону Power Query, Pivot или VBA.

На этом, пожалуй, всё. Спасибо всем, кто дочитал до конца. Надеюсь, было полезно. В комментариях делитесь своими формулами-монстрами. Если статья "зайдёт", то, думаю, будет ещё несколько похожих, так как за время преподавания уже накопился определённый материал.