Как найти круговую ссылку в Excel (быстро и легко)

При работе с формулами Excel вы можете иногда увидеть предупреждение о циклической ссылке.

Это сообщение говорит вам, что на вашем листе есть циклическая ссылка, и это может привести к неправильному расчёту формул. Оно также просит вас решить эту проблему и разобраться с ней.

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

Итак, приступим!

Что такое круговая ссылка в Excel?

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

Простой пример

Предположим, у вас есть данные в ячейках A1:A5, и вы используете формулу =СУММ(A1:A6) в ячейке A6. Это даст предупреждение о круговой ссылке — вы хотите просуммировать значения в диапазоне A1:A6, а результат должен появиться в самой ячейке A6.

Это создаёт цикл, поскольку Excel просто продолжает добавлять новое значение в ячейку A6, которое продолжает меняться (отсюда и название — циклическая ссылка).

Как найти круговые ссылки в Excel

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

Поэтому, если вы пытаетесь найти и обработать циклические ссылки на листе, вам нужно знать способ их отыскать.

Активируйте рабочий лист с круговой ссылкой.

Перейдите на вкладку «Формулы».

В группе «Зависимости формул» щёлкните раскрывающийся значок «Проверка ошибок» (маленькая стрелка справа от значка).

Наведите курсор на опцию «Циклические ссылки». Она покажет вам ячейку с круговой ссылкой на листе.

Щёлкните по адресу ячейки, и вы перейдёте к ней на листе.

Решив проблему, вы можете снова выполнить те же шаги, и Excel покажет другие ссылки на ячейки с циклической ссылкой (если они есть). Если их не осталось, вы не увидите ссылку на ячейку в списке.

Ещё один быстрый способ найти круговую ссылку — посмотреть на строку состояния (внизу окна): в левой её части отображается текст «Циклическая ссылка» вместе с адресом ячейки.

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

Как удалить круговую ссылку в Excel

Как только вы определили, что на вашем листе есть циклические ссылки, пора их удалить (если они там не нужны).

К сожалению, это не так просто, как нажать клавишу Delete. Поскольку это зависит от формул, а каждая формула отличается, вам нужно анализировать ситуацию в каждом конкретном случае.

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

Многоуровневая круговая ссылка

Круговая ссылка также может быть вызвана несколькими ячейками, которые взаимодействуют друг с другом на многих уровнях. Например: формула в ячейке A6 — =СУММ(A1:A5)+C6; формула в ячейке C1 — =A6*0,1; формула в ячейке C6 — =A6+C1. Здесь результат в ячейке C6 зависит от значений в A6 и C1, которые, в свою очередь, зависят от самой C6 — отсюда и циклическая ошибка.

В таком случае есть способ идентифицировать ячейки, вызывающие циклическую ссылку, а затем обработать их — с помощью параметра «Влияющие ячейки» (Trace Precedents).

Выберите ячейку с круговой ссылкой.

Перейдите на вкладку «Формулы».

Нажмите «Влияющие ячейки».

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

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

Как включить / отключить итерационные вычисления в Excel

Когда у вас есть круговая ссылка в ячейке, сначала вы получаете предупреждение, и, если вы закрываете это диалоговое окно, результатом в ячейке будет 0.

Это происходит потому, что циклическая ссылка представляет собой бесконечный цикл, и Excel не хочет зацикливаться на нём — поэтому он возвращает 0.

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

Перейдите на вкладку «Файл».

Щёлкните «Параметры». Откроется диалоговое окно «Параметры Excel».

Выберите раздел «Формулы» на левой панели.

В разделе «Параметры расчёта» установите флажок «Включить итерационные вычисления». Здесь же можно указать максимальное число итераций и максимальное изменение.

Вот и всё! Вышеупомянутые шаги позволят выполнить итеративный расчёт в Excel.

Позвольте также быстро объяснить два параметра итеративного расчёта:

Предельное число итераций — максимальное число раз, которое Excel будет вычислять формулу, прежде чем выдать окончательный результат.
Относительная погрешность — максимальное изменение, при недостижении которого между итерациями вычисление будет остановлено (по умолчанию 0,001). Чем ниже это значение, тем точнее будет результат.

💡 Совет: Чем больше раз выполняются итерации, тем больше времени и ресурсов требуется Excel. Если держать предельное число итераций слишком высоким, это может замедлить работу Excel или привести к сбою.

Когда включены итеративные вычисления, Excel не будет отображать предупреждение о циклической ссылке, но по-прежнему покажет её в строке состояния.

Умышленное использование круговых ссылок

В большинстве случаев наличие круговой ссылки на листе — это ошибка. Вот почему Excel показывает подсказку: «Попробуйте удалить или изменить эти ссылки или переместить формулы в другие ячейки».

Но бывают конкретные случаи, когда круговая ссылка нужна намеренно, чтобы получить желаемый результат.

Пример: отметка времени

Допустим, вы хотите, чтобы при каждой записи в ячейку столбца A в столбце B автоматически проставлялась отметка времени. Формулу вроде =ЕСЛИ(A2<>"";ЕСЛИ(B2<>"";B2;СЕЙЧАС());"") легко вставить, но проблема в том, что она будет обновлять все временные метки при каждом изменении на листе (поскольку функция СЕЙЧАС является волатильной). Чтобы обойти это, можно использовать ту же формулу, но разрешить итеративный расчёт — это и есть намеренное использование циклической ссылки.

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

Надеюсь, вы нашли этот урок полезным!

Другие руководства по Excel, которые могут оказаться полезными:

#ССЫЛКА! Ошибка в Excel — как исправить ошибку ссылки
Обработка ошибок в Excel VBA
Использование ЕСЛИОШИБКА с ВПР, чтобы избавиться от ошибок #Н/Д
Как сослаться на другой лист или книгу в Excel (с примерами)
Абсолютные, относительные и смешанные ссылки на ячейки в Excel

Хотите освоить Excel глубже?

Больше полезных формул, приёмов и готовых решений — на Vip Excel