Как проверить сложную формулу в Excel: промежуточный расчёт, связи ячеек и клавиша F9

Сложная формула выдаёт правдоподобное число, и на глаз не понять, верно ли оно посчитано. Нужные инструменты найти просто: по справке Microsoft кнопка «Вычислить формулу» стоит на вкладке «Формулы» в группе «Зависимости формул»^[1]. Выделите ячейку, нажмите команду — и Excel раскроет расчёт или нарисует связи. Вся проверка формул сводится к двум вопросам: что посчитано внутри и откуда пришли данные.

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

Ключевые факты: проверка формул в Excel

• Окно «Вычисление формулы» раскрывает формулу по шагам и за один раз работает с одной ячейкой^[1].
• В примере справки формула со СРЗНАЧ из 55, 35, 45 и 25 сначала даёт 40, а итог равен 0^[1].
• Справка Microsoft называет цвета стрелок: синие — ячейки без ошибок, красные — с ошибками^[2].
• Шаг с заходом недоступен, если ссылка встречается второй раз или ведёт в другую книгу^[1].
• Влияющие ячейки отличаются от зависимых направлением: первые ведут к данным формулы, вторые — к формулам, которые используют ячейку.

Что такое проверка формул в Excel и где её инструменты

Проверка формул в Excel собрана в группе «Зависимости формул» и занимает одну вкладку ленты. Каждый инструмент отвечает на свой вопрос, и проверка формул в Excel обычно начинается с того, который ближе к симптому. Зависимости формул показывают связи, а вычисление формулы — порядок расчёта.

Пример — расчёт премии: 7 % от продаж от 100000 при выполненном плане, 5 % — от 50000, иначе ноль.

В D2 стоит =ЕСЛИ(И(B2>=100000;C2="да");B2*7%;ЕСЛИ(B2>=50000;B2*5%;0)). На листе знак > вместо >= дал Петрову с ровно 100000 премию 5000 вместо 7000, и никакой ошибки Excel не показал^[6].

Разобравшись с набором инструментов, переходим к главному — расчёту по шагам.

«Вычислить формулу» по шагам: расчёт изнутри наружу

Вычисление формулы раскрывает вложенную формулу по шагам и показывает промежуточные значения. Справка разбирает пример =ЕСЛИ(СРЗНАЧ(F2:F5)>50;СУММ(G2:G5);0): при значениях 55, 35, 45 и 25 формула превращается в ЕСЛИ(40>50;…), затем в ЕСЛИ(ЛОЖЬ;…) и даёт 0, а СУММ вовсе не вычисляется^[1].

1. Выделите одну ячейку с формулой — окно работает с одной ячейкой за раз.
2. Откройте «Формулы → Зависимости формул → Вычислить формулу».
3. Нажимайте «Вычислить»: подчёркнутая часть заменится значением.
4. Если подчёркнута ссылка на другую формулу, нажмите «Шаг с заходом», а затем «Шаг с выходом».
5. Кнопка «Перезагрузить» начинает просмотр заново, «Закрыть» завершает его.

Если в формулу закрался знак >, окно покажет для Петрова, что условие B4>100000 дало ЛОЖЬ и расчёт ушёл во вторую ветку. Так находят место, где формула свернула не туда. Вычисление формулы работает только с одной ячейкой: соседние строки придётся открыть по очереди.

Часть формулы можно посчитать и без окна: в строке формул выделите фрагмент с полным операндом и нажмите F9^[5]. Справка предупреждает: если после этого нажать ВВОД, выделенная часть заменится значением, и восстановить её уже нельзя^[5]. Однако расчёт по шагам не показывает, откуда пришли данные: для этого есть стрелки.

Чем влияющие ячейки отличаются от зависимых при проверке формул

Зависимости формул рисуют стрелки: влияющие ячейки ведут к данным, зависимые — к формулам. Обе кнопки стоят в той же группе, а цвет стрелки сообщает о состоянии ячейки^[2].

1. Выделите D4 и нажмите «Влияющие ячейки» — стрелки придут от B4 и C4.
2. Нажмите кнопку ещё раз, чтобы увидеть следующий уровень связей.
3. Выделите B4 и нажмите «Зависимые» — стрелка покажет формулу премии.
4. Дважды щёлкните стрелку, чтобы перейти к ячейке на другом конце.

Стрелки исчезают, когда формулу меняют, вставляют или удаляют строки и столбцы^[2]; после правки команду повторяют. Клавиша F2 подсвечивает влияющие ячейки цветом прямо в формуле^[2]. Ячейки другой книги стрелки покажут, только когда эта книга открыта^[2]. Для ячеек вдали от экрана есть окно контрольных значений: оно показывает формулу и результат, пока вы правите другой участок листа^[3]. Формулы целиком на листе показывает команда «Показать формулы» или сочетание CTRL с апострофом^[4]; в Google Таблицах все формулы показывает Ctrl + ~^[7].

Стрелки можно убирать и по одному уровню, начиная с самой удалённой ячейки^[2]. Остаётся понять, где эти инструменты бессильны.

Когда проверка формул не поможет: другие книги, летучие функции, Mac

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

• Если ссылка ведёт в другую книгу, «Шаг с заходом» недоступен^[1], а окно контрольных значений покажет ячейку, только когда та книга открыта^[3].
• При летучих функциях СЛЧИС, СМЕЩ, ДВССЫЛ, ТДАТА или СЕГОДНЯ результат в окне может отличаться от значения в ячейке^[1].
• Невычисляемые ветки ЕСЛИ и ВЫБОР окно показывает кодом недоступного значения, а пустую ссылку — нулём^[1]: это не сбой.
• Стрелки не проводятся к текстовым полям, диаграммам, рисункам, сводным таблицам и именованным константам^[2].
• На Mac справка описывает «Вычисление формулы» для Excel 2021, 2024 и Microsoft 365^[1], а на странице о поиске ошибок окно указано только для Windows^[8] — проверьте свою версию.

Справка Microsoft относит стрелки зависимостей к Excel 2016–2024 и Microsoft 365^[2]. Причины конкретных кодов ошибок разобраны на странице о кодах ошибок в формулах, а составные условия вроде формулы премии — на странице про ЕСЛИ с несколькими условиями. Проверьте формулу премии в тренажёре: урок меняет продажи и показывает, где ломается условие. Остальные функции и приёмы собраны в обзоре формул Excel для сложных расчётов.