#Что означает ошибка
В книге есть одна или несколько циклических ссылок Circular reference warning
Предупреждение о циклической ссылке появляется, когда формула прямо или косвенно ссылается на свою же ячейку. Excel не может посчитать результат и показывает 0 или последнее значение.
Нужно найти ячейку цикла и разорвать цепочку, либо осознанно включить итеративные вычисления, если цикл задуман.
Разберём оба случая.
Циклическая ссылка возникает, когда формула прямо или косвенно ссылается на собственную ячейку. Excel не может вычислить такое выражение и показывает предупреждение.
#Причины и решения
| Возможная причина | Как узнать | Что делать |
|---|---|---|
| Формула ссылается на свою ячейку | Например, сумма включает себя. | Исправьте диапазон. |
| Косвенная цепочка ссылок | A → B → A. | Найдите цикл. |
| Намеренные итерации | Нужен расчёт по кругу. | Включите итеративные вычисления. |
| Скопированная формула | Смещение ссылок. | Проверьте адреса. |
#Пошаговое решение
- Откройте список циклических ссылокОткройте Excel, выберите «Формулы» → «Проверка ошибок» → «Циклические ссылки». Если список активен, он покажет адреса ячеек, где расчёт возвращается к самому себе. Прежде чем править, сохраните копию книги с новым именем: исправление формулы может изменить связанные результаты.
- Перейдите к первой ячейкеЩёлкните адрес первой ячейки в раскрытом списке циклических ссылок. Excel выделит эту ячейку; посмотрите строку формул и запишите ссылку. Если цикл проходит через другие листы, скопируйте адрес каждого промежуточного звена, чтобы не потерять логику вычисления.
- Посмотрите, на что она ссылаетсяВыделив ячейку, на вкладке «Формулы» нажмите «Влияющие ячейки» и последовательно проследите стрелки зависимостей. Если стрелки ведут к ней же или образуют кольцо через другие ячейки, найдите формулу, где ссылка ошибочна. Не удаляйте весь диапазон без понимания расчёта.
- Исключите саму себя из диапазонаЕсли итоговая ячейка B10 содержит
=СУММ(B2:B10), исправьте на=СУММ(B2:B9), но только если речь об итоговой сумме строк 2–9. Для других циклов проверьте требуемую формулу по смыслу. Нажмите Enter, затем F9 для пересчёта и убедитесь, что полученное значение соответствует исходным данным. - Проверьте остальные циклыПовторно откройте «Формулы» → «Проверка ошибок» → «Циклические ссылки» и проверьте, исчезли ли адреса. Если ссылки остались, переходите к следующему адресу и повторяйте диагностику. Итерационные вычисления включайте лишь в специально разработанной модели, где круговые ссылки предусмотрены автором.
#Как найти цикл
Откройте «Формулы → Проверка наличия ошибок → Циклические ссылки»: список покажет адреса ячеек. Включите «Показать формулы» (Ctrl+) и просмотрите их. Используйте «Влияющие ячейки» и «Зависимые ячейки» (стрелки на листе). Чаще всего причина: формула =СУММ(A1:A10)` стоит в A10, итог включает сам себя, или таблица ссылается на итог в своём столбце.
#Исправление
Исключите ячейку из диапазона (=СУММ(A1:A9)), вынесите итог в отдельную ячейку и используйте структурированные ссылки в таблице. Замените взаимные ссылки между листами одной формулой. Следите за функциями СМЕЩ и ДВССЫЛ, которые маскируют цикл. Если это ошибка копирования формулы, протяните её заново.
#Итерации
Для намеренных циклов (расчёт процентов от суммы с процентами, итеративные модели) включите «Файл → Параметры → Формулы → Включить итеративные вычисления», задайте предельное число итераций (100) и относительную погрешность (0,001). Используйте с осторожностью и документируйте: расчёты могут расходиться.