Циклические ссылки в Excel: как найти и убрать
Где Excel показывает адрес зациклившейся ячейки, почему циклы чаще всего появляются в строке итогов и когда итеративные вычисления включать не ошибка, а решение.
Коротко
Циклическая ссылка — это формула, которая прямо или через цепочку соседей ссылается на собственную ячейку. Excel не может её посчитать: чтобы узнать результат, нужен результат. Поэтому он показывает предупреждение, ставит в ячейку 0 и пишет адрес виновника в строке состояния — внизу слева, рядом со словом «Готово».
Самый частый случай выглядит так. В столбце B данные со второй по одиннадцатую строку, а итог по ошибке поставлен в B11 — внутрь диапазона: =СУММ(B2:B11). Формула складывает в том числе саму себя. Второй по частоте — ссылка на весь столбец: =СУММ(B:B), написанная в ячейке этого же столбца.
Найти зациклившуюся ячейку надёжнее всего через Формулы → Проверка ошибок → Циклические ссылки: там список адресов, по каждому можно перейти. Пункт становится доступным, только если циклы в книге есть, — это заодно и способ проверить, что их не осталось.
Важная тонкость, из-за которой люди не могут найти ссылку часами: адрес в строке состояния показывается только для активного листа. Если цикл на другом листе, внизу висит просто надпись «Циклические ссылки» без адреса. Пройдите по листам — на нужном адрес появится. Меню «Проверка ошибок» показывает циклы того листа, который открыт, поэтому обойти листы придётся в любом случае.
И обратная сторона: цикл — не всегда ошибка. Есть задачи, где величина честно зависит сама от себя, и для них в Excel есть итеративные вычисления: Файл → Параметры → Формулы → галочка «Включить итеративные вычисления». Excel начнёт считать формулу по кругу, пока результат не перестанет меняться. Об этом — в разделе ниже, вместе с ценой такого решения.
То же самое формулой в Excel
=СУММ(B2:B11) — итог ставится под данными: диапазон заканчивается на строке выше самой формулы=СУММ(B2:ИНДЕКС(B:B;СТРОКА()-1)) — сумма от B2 до строки над формулой: диапазон не заденет себя, куда ячейку ни перенеси=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B11) — то же, но не считает вложенные итоги и строки, скрытые фильтром=A2/(1-C2) — цена с комиссией, которая берётся от итоговой суммы: решается алгеброй, а не циклом=ЕСЛИ(B2<>"";ЕСЛИ(C2="";ТДАТА();C2);"") — метка времени первой записи — законный цикл, нужны итеративные вычисленияПошагово
- Посмотрите вниз, в строку состояния. Слева написано «Циклические ссылки» и адрес — например,
B11. Кликните по адресу, Excel перейдёт в эту ячейку. - Если адреса нет, а слово есть, цикл на другом листе. Пройдите по листам книги: на нужном адрес появится.
- Откройте список: Формулы → стрелка рядом с «Проверка ошибок» → «Циклические ссылки». В подменю перечислены адреса; когда циклов не остаётся, пункт становится недоступным — это и есть признак, что вы закончили.
- Посмотрите на формулу в найденной ячейке. В девяти случаях из десяти в диапазон попал адрес самой ячейки:
=СУММ(B2:B11)вB11. Сдвиньте границу диапазона на строку выше или перенесите итог из таблицы вниз, оставив пустую строку. - Если формула сложная, включите Формулы → «Влияющие ячейки»: Excel нарисует стрелки к тем ячейкам, от которых зависит эта. Стрелка, ведущая обратно в саму ячейку, и есть цикл. Убрать стрелки — «Убрать стрелки» на той же вкладке.
- Когда цикл нужен по делу: Файл → Параметры → Формулы → «Включить итеративные вычисления». Рядом два поля: предельное число итераций (по умолчанию 100) и относительная погрешность (0,001) — расчёт останавливается, как только выполнено одно из условий. В Google Таблицах то же самое: Файл → Настройки → Вычисления → Итеративный расчёт.
Где обычно ломается
- Формула вернула 0, а предупреждения не было. Значит, итеративные вычисления уже включены — Excel честно посчитал сто кругов и остановился. Ноль здесь не «ошибка», а результат, который получился; проверьте параметры вычислений, прежде чем искать проблему в данных.
- Итеративные вычисления включаются не для одного файла. Excel берёт эту настройку из первой открытой книги и применяет ко всему окну. Включив её ради одного расчёта, вы отключаете предупреждение о циклах и в остальных открытых файлах — там ошибка перестанет быть заметной.
- Цикл через ДВССЫЛ или СМЕЩ Excel может не увидеть. Эти функции собирают ссылку из текста, и до пересчёта неизвестно, куда она ведёт. Предупреждения не будет, а число будет неправильным. Если в книге есть ДВССЫЛ и результат странный, начинайте проверку с неё.
- Ссылки на весь столбец.
=СУММ(B:B)удобна ровно до того момента, когда её саму помещают в столбец B. Указывайте границы диапазона явно — заодно файл будет считаться быстрее. - Условное форматирование и проверка данных тоже умеют зацикливаться. Правило вида «выделить, если значение больше суммы столбца», написанное на весь столбец вместе с ячейкой итога, даёт тот же цикл, только без предупреждения в строке состояния.
- Метка времени через ТДАТА живёт до перерасчёта. Формула
=ЕСЛИ(B2<>"";ЕСЛИ(C2="";ТДАТА();C2);"")запоминает момент первой записи, но держится это на итеративных вычислениях: выключите их — и метки перестанут ставиться, а книга начнёт ругаться на циклы. Если время нужно зафиксировать намертво, проще вводить его вручную сочетанием Ctrl+Shift+;.