Как удалить или разрешить циклическую ссылку

Вы ввели формулу, но она не работает. Вместо этого появляется сообщение о "циклической ссылке". Миллионы людей сталкиваются с этой проблемой. Это происходит, когда формула пытается посчитать собственную ячейку при отключенной функции итеративных вычислений. Вот как это выглядит:

Формула, из-за которой возникает циклическая ссылка

Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить проблему, вы можете переместить формулу в другую ячейку (в строке формул нажмите сочетание клавиш CTRL+X, чтобы вырезать формулу, выберите другую ячейку и нажмите сочетание клавиш CTRL+V).

Другая распространенная ошибка связана с использованием функций, которые включают ссылки на самих себя, например ячейка F3 может содержать формулу =СУММ(A3:F3). Пример:

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

Вы также можете попробовать один из описанных ниже способов.

  • Если вы только что ввели формулу, сначала проверьте ячейку, в которой она расположена. Проверьте, не ссылается ли формула на собственную ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Такие формулы, как =A1+1 (в ячейке A1) также приводят к ошибкам циклической ссылки.

Проверьте наличие непрямых ссылок. Они возникают, когда формула, расположенная в ячейке А1, использует другую формулу в ячейке B1, которая снова ссылается на ячейку А1. Если это сбивает с толку вас, представьте, что происходит с Excel.

  • Если найти ошибку не удается, на вкладке Формулы щелкните стрелку рядом с кнопкой Проверка ошибок, выберите пункт Циклические ссылки и щелкните первую ячейку в подменю.

    Команда "Циклические ссылки"

  • Проверьте формулу в ячейке. Если вам не удается определить, является ли эта ячейка причиной циклической ссылки, выберите в подменю Циклические ссылки следующую ячейку.

  • Продолжайте находить и исправлять циклические ссылки в книге, повторяя действия 1–3, пока из строки состояния не исчезнет сообщение "Циклические ссылки".

Советы    

  • В строке состояния в левом нижнем углу отображается сообщение Циклические ссылки и адрес ячейки с одной из них.

    При наличии циклических ссылок на других листах, кроме активного, в строке состояния выводится сообщение "Циклические ссылки" без адресов ячеек.

  • Вы можете перемещаться по ячейкам в циклической ссылке с помощью двойных щелчков по стрелке трассировки, которая указывает на ячейки, влияющие на значение в текущей ячейке. Чтобы отобразить стрелку трассировки, нажмите кнопку Влияющие ячейки или Зависимые ячейки на вкладке Формулы.

    Влияющие ячейки

Предупреждение о циклической ссылке

Когда Excel впервые находит циклическую ссылку, отображается предупреждающее сообщение. Нажмите кнопку ОК или закройте окно сообщения.

Если вы закроете сообщение, то Excel отобразит в ячейке либо нулевое значение, либо последнее вычисленное значение. В некоторых случаях формула может успешно работать до тех пор, пока она не попытается вычислить себя. Например, формула, использующая функцию "ЕСЛИ" может работать до тех пор, пока пользователь не введет аргумент (данные, необходимые для нормальной работы формулы), который заставит формулу попытаться вычислить саму себя. Если это произойдет, Excel оставит в ячейке последнее успешно вычисленное значение.

Если есть подозрение, что циклическая ссылка содержится в ячейке, которая не возвращает значение 0, попробуйте такое решение:

  • Щелкните формулу в строке формулы и нажмите клавишу ВВОД.

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

  • Пользователь создает первый экземпляр циклической ссылки в любой открытой книге.

  • Пользователь удаляет все циклические ссылки во всех открытых книгах, после чего создает новую циклическую ссылку.

  • Пользователь закрывает все книги, создает новую и вводит в нее формулу с циклической ссылкой.

  • Пользователь открывает книгу, содержащую циклическую ссылку.

  • При отсутствии других открытых книг пользователь открывает книгу и создает в ней циклическую ссылку.

Итеративные вычисления

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

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

  1. Если вы работаете в Excel 2010 или более поздней версии, последовательно выберите элементы Файл > Параметры > Формулы. Если вы работаете в Excel для Mac, откройте меню Excel, выберите пункт Настройки и щелкните элемент Вычисление.

    Если у вас Excel 2007, нажмите кнопку Microsoft Office  Изображение кнопки Office , щелкните Параметры Excel и выберите категорию Формулы.

  2. В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac щелкните Использовать итеративное вычисление.

  3. В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.

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

Итеративное вычисление может иметь три исход:

  • Решение сходится, что означает получение надежного конечного результата. Это самый желательный исход.

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

  • Решение колеблется между двумя значениями, например, после первой итерации получается значение 1, после второй — значение 10, после третьей — снова 1 и т. д.

К началу страницы

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Дополнительные сведения

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Сочетания клавиш и горячие клавиши в Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×