Вы ввели формулу, но она не работает. Вместо этого вы получили сообщение о "циклической ссылке". Миллионы людей имеют ту же проблему, и это происходит потому, что ваша формула пытается вычислить себя. Вот как это выглядит:
Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить проблему, можно переместить формулу в другую ячейку. Нажмите клавиши CTRL+X , чтобы вырезать формулу, выделите другую ячейку и нажмите клавиши CTRL+V , чтобы вставить ее.
Советы:
-
Иногда может потребоваться использовать циклические ссылки, так как они вызывают итерацию функций. В этом случае перейдите к статье Дополнительные сведения о итеративном вычислении.
-
Кроме того, дополнительные сведения о написании формул см. в статье Общие сведения о формулах в Excel.
Другая распространенная ошибка связана с использованием функций, которые включают ссылки на самих себя, например ячейка F3 может содержать формулу =СУММ(A3:F3). Пример:
Вы также можете попробовать один из описанных ниже способов.
-
Если вы только что ввели формулу, начните с этой ячейки и проверьте, ссылаетесь ли вы на саму ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Такие формулы, как =A1+1 (в ячейке A1), также вызывают ошибки циклической ссылки.
Проверьте наличие непрямых ссылок. Они возникают, когда формула, расположенная в ячейке А1, использует другую формулу в ячейке B1, которая снова ссылается на ячейку А1. Если это сбивает с толку вас, представьте, что происходит с Excel.
-
Если не удается найти ошибку, перейдите на вкладку Формулы , щелкните стрелку рядом с полем Проверка ошибок, наведите указатель на пункт Циклические ссылки, а затем выберите первую ячейку, указанную в подменю.
-
Проверьте формулу в ячейке. Если не удается определить, является ли ячейка причиной циклической ссылки, выберите следующую ячейку в подменю Циклические ссылки .
-
Продолжайте находить и исправлять циклические ссылки в книге, повторяя действия 1–3, пока из строки состояния не исчезнет сообщение "Циклические ссылки".
Советы
-
В строке состояния в левом нижнем углу отображается сообщение Циклические ссылки и адрес ячейки с одной из них.
При наличии циклических ссылок на других листах, кроме активного, в строке состояния выводится сообщение "Циклические ссылки" без адресов ячеек.
-
Вы можете перемещаться между ячейками в циклической ссылке, дважды щелкнув стрелку трассировки. Стрелка указывает ячейку, которая влияет на значение выбранной ячейки. Чтобы отобразить стрелку трассировки, выберите Формулы, а затем выберите Прецеденты трассировки или Зависимые от трассировки.
Предупреждение о циклической ссылке
Когда Excel впервые находит циклическую ссылку, появляется предупреждающее сообщение. Нажмите кнопку ОК или закройте окно сообщения.
При закрытии сообщения Excel отображает нулевое или последнее вычисляемое значение в ячейке. И теперь вы, вероятно, говорите: "Повесьте, последнее вычисляемое значение?" Да. В некоторых случаях формула может успешно выполниться до того, как она попытается вычислить себя. Например, формула, использующая функцию IF , может работать до тех пор, пока пользователь не введет аргумент (фрагмент данных, который формула должна правильно выполнить), который приведет к вычислению самой формулы. В этом случае Excel сохраняет значение из последнего успешного вычисления.
Если есть подозрение, что циклическая ссылка содержится в ячейке, которая не возвращает значение 0, попробуйте такое решение:
-
Выберите формулу в строке формул и нажмите клавишу ВВОД.
Важно Во многих случаях при создании дополнительных формул с циклическими ссылками предупреждающее сообщение в приложении Excel больше не отображается. Ниже перечислены некоторые, но не все, ситуации, в которых предупреждение появится.
-
Пользователь создает первый экземпляр циклической ссылки в любой открытой книге.
-
Пользователь удаляет все циклические ссылки во всех открытых книгах, после чего создает новую циклическую ссылку.
-
Пользователь закрывает все книги, создает новую и вводит в нее формулу с циклической ссылкой.
-
Пользователь открывает книгу, содержащую циклическую ссылку.
-
При отсутствии других открытых книг пользователь открывает книгу и создает в ней циклическую ссылку.
Итеративные вычисления
Иногда может потребоваться использовать циклические ссылки, так как они вызывают итерацию функций— повторять до тех пор, пока не будет выполнено определенное числовое условие. Это может замедлить работу компьютера, поэтому итеративные вычисления обычно отключаются в Excel.
Если вы не знакомы с итеративными вычислениями, вероятно, вы не захотите оставлять активных циклических ссылок. Если же они вам нужны, необходимо решить, сколько раз может повторяться вычисление формулы. Если включить итеративные вычисления, не изменив предельное число итераций и относительную погрешность, приложение Excel прекратит вычисление после 100 итераций либо после того, как изменение всех значений в циклической ссылке с каждой итерацией составит меньше 0,001 (в зависимости от того, какое из этих условий будет выполнено раньше). Тем не менее, вы можете сами задать предельное число итераций и относительную погрешность.
-
Выберите Параметры > файлов > Формулы. Если вы используете Excel для Mac, выберите меню Excel , а затем выберите Параметры > Вычисление.
-
В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac выберите Использовать итеративное вычисление.
-
В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.
-
В поле Относительная погрешность введите наименьшее значение, до достижения которого следует продолжать итерации. Это наименьшее приращение в любом вычисляемом значении. Чем меньше число, тем точнее результат и тем больше времени потребуется Excel для вычислений.
Итеративное вычисление может иметь три исход:
-
Решение сходится, что означает получение надежного конечного результата. Это самый желательный исход.
-
Решение расходится, т. е. при каждой последующей итерации разность между текущим и предыдущим результатами увеличивается.
-
Решение переключается между двумя значениями. Например, после первой итерации результат равен 1, после следующей итерации — 10, после следующей итерации — 1 и т. д.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Совет: Если вы владелец малого бизнеса и хотите получить дополнительные сведения о настройке Microsoft 365, посетите раздел Справка и обучение для малого бизнеса.
Дополнительные сведения
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
