Циклические ссылки в Excel¶

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

¶Причины возникновения

Циклические ссылки появляются по нескольким типичным причинам:

• Опечатка в формуле — пользователь случайно включил в формулу адрес собственной ячейки или диапазон, содержащий её.
• Копирование формул — при заполнении диапазона относительные ссылки сдвигаются так, что формула начинает ссылаться на ячейку, в которую она помещена.
• Итоги в собственном диапазоне — формула вида =СУММ(A1:A10) помещена в одну из ячеек A1:A10.
• Каскадные зависимости — цепочка формул A→B→C→A, где каждая ячейка зависит от следующей.
• Ссылки на листы и книги — формула ссылается на другой лист, где, в свою очередь, стоит формула, ссылающаяся обратно.

¶Как найти циклические ссылки

¶Инструмент «Обнаружение циклических ссылок»

Excel предоставляет встроенный инструмент для поиска проблемных формул. Порядок действий:

1. Открыть вкладку «Формулы» на ленте.
2. В группе «Зависимости» нажать кнопку «Обнаружение циклических ссылок».
3. В открывшемся диалоговом окне будет перечислен адрес ячейки с циклической ссылкой (или несколько адресов, если циклов несколько).

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

¶Статусная строка

Если циклическая ссылка возникла при открытии файла, в левом нижнем углу окна Excel в статусной строке появляется сообщение «В формуле обнаружена циклическая ссылка: A1» (адрес может отличаться). Клик по этому сообщению также переводит курсор к проблемной ячейке.

¶Ручной поиск

Если инструмент поиска не срабатывает (например, цикл охватывает внешние книги), можно проверить зависимости вручную:

• Выделить ячейку и нажать Ctrl+[ — Excel покажет все ячейки, на которые ссылается формула.
• Использовать «Просмотр зависимостей» (вкладка «Формулы» → «Обнаружение циклических ссылок» → «Просмотр зависимостей») — диалог показывает цепочку «Зависит от» и «Зависимые ячейки».
• Включить подсветку формул: «Формулы» → «Выделение формул» — все формулы на листе будут окрашены, что упрощает визуальный поиск.

¶Поиск по всему файлу

Циклические ссылки могут находиться на любом листе книги. Инструмент «Обнаружение циклических ссылок» проверяет всю книгу целиком, поэтому отдельно листы искать не нужно. Если в книге несколько циклов, диалог покажет их все — устранять их следует по одному, перепроверяя после каждого исправления.

¶Как убрать циклические ссылки

¶Исправление формулы

Основной способ — отредактировать формулу так, чтобы она не ссылалась на собственную ячейку. Типичные исправления:

• Итог вне диапазона: если =СУММ(A1:A10) стоит в A10, перенести итог в ячейку A11 или в другую часть листа.
• Замена адреса: если формула случайно ссылается на собственную ячейку, заменить адрес на правильный.
• Разрыв цепочки: если цикл образован несколькими формулами, определить, какая из них ссылается «назад», и исправить её.

¶Удаление формулы

Если формула не нужна, достаточно удалить её содержимое (нажать Delete). После удаления циклическая ссылка исчезнет, а зависимые ячейки пересчитаются.

¶Режим итеративных вычислений

В отдельных случаях циклические ссылки допустимы — например, при расчёте процентов, зависящих от суммы, которая сама включает проценты (сложные проценты, амортизация). В таких ситуациях включают итеративные вычисления:

1. «Файл» → «Параметры» → «Формулы».
2. Установить флажок «Включить итеративные вычисления».
3. Задать максимальное количество итераций и допустимое изменение.

При включённом режиме Excel повторяет вычисления заданное число раз, пока результат не стабилизируется. Значение ячейки с циклической ссылкой при этом будет приблизительным и зависит от настроек итераций. Для большинства задач этот режим не рекомендуется, так как маскирует ошибки.

¶Профилактика

Чтобы избежать циклических ссылок, рекомендуется:

• Проверять формулу перед заполнением диапазона (F9 пересчитывает выделенные ячейки).
• Располагать итоговые формулы за пределами суммируемых диапазонов.
• Использовать «Проверку зависимостей» при построении сложных моделей.
• После копирования формул проверять адреса ссылок (Ctrl+` — показать формулы).

¶Источники

• Microsoft Support. Обнаружение и устранение циклических ссылок.
• Microsoft Support. Итеративные вычисления в Excel.
• Справка Microsoft Excel. Формулы и зависимости.