Циклическая ссылка происходит в Excel , когда формула содержит ссылку на ячейку для ячейки, содержащей формулу.
Информация в статье относится к версиям Excel 2019, 2016, 2013, 2010, Excel Online и Excel для Mac.
Пример циркулярной ссылки
Пример циклической ссылки этого типа показан на рисунке ниже, где формула в ячейке C1 содержит ссылку на ячейку C1 в формуле:
= А1 + А2 + А3 + С1
Ошибка ссылки может возникнуть, когда формула ссылается на другую формулу, которая в конечном итоге ссылается на ячейку, содержащую исходную формулу.
Циркулярное предупреждение
Как показано на рисунке выше, если в рабочем листе Excel есть циклическая ссылка, программа отображает диалоговое окно Alert, указывающее проблему. Сообщение в диалоговом окне специально сформулировано, потому что не все циклические ссылки в формулах являются непреднамеренными, как описано ниже.
Параметры пользователя
Пользовательские опции, когда появляется это диалоговое окно, должны выбрать « OK» или « Справка» , ни одна из которых не решит проблему с циклической ссылкой. Когда вы прочитаете сообщение в диалоговом окне, вы обнаружите, что:
- Справка предназначена для использования в случае непреднамеренной циклической ссылки. Это приведет вас к справочному файлу Excel по круговым ссылкам.
- OK используется, чтобы сообщить Excel, что циклическая ссылка была сделана намеренно и что она должна оставить ее на месте.
Непреднамеренные циркулярные ссылки
Если циклическая ссылка была сделана непреднамеренно, в файле справки показано, как найти и удалить циклические ссылки. Файл справки указывает вам использовать инструмент проверки ошибок Excel, расположенный на вкладке Формулы. Многие непреднамеренные ссылки на ячейки можно исправить без проверки ошибок, просто исправив ссылки на ячейки, используемые в формуле.
Преднамеренные циркулярные ссылки
Циркулярная ссылка в Excel не предлагает решения проблемы циклической ссылки, потому что не все циклические ссылки являются ошибками. Хотя эти преднамеренные циклические ссылки встречаются реже, чем непреднамеренные, их можно использовать, если вы хотите, чтобы Excel многократно повторял или выполнял формулу до получения результата.
Включение итерационных вычислений
В Excel есть возможность включить эти итеративные вычисления, если вы планируете их использовать.
Чтобы включить итерационные вычисления:
-
Выберите « Файл» (или кнопку « Office» в Excel 2007).
-
Выберите « Параметры», чтобы открыть диалоговое окно «Параметры Excel».
-
\ На левой панели диалогового окна выберите « Формулы» .
-
В правой части диалогового окна установите флажок Включить итеративный расчет .
Под флажком доступны варианты:
- Установить максимальное количество итераций; это количество раз, которое Excel должен пересчитать формулу.
- Установите максимально допустимое изменение между результатами расчета — чем меньше число, тем точнее результат.
Отображение нулей в затронутых клетках
Для ячеек, содержащих циклические ссылки, Excel отображает либо 0 , как показано в ячейке C1 в примере, либо последнее вычисленное значение в ячейке.
В некоторых случаях формулы могут успешно выполняться до того, как они попытаются вычислить значение ссылки на ячейку, в которой они находятся. Когда это происходит, ячейка, содержащая формулу, отображает значение из последнего успешного расчета.
Подробнее о циркулярном справочном предупреждении
После первого экземпляра формулы, содержащей циклическую ссылку в книге , Excel может не отображать предупреждение снова. Это зависит от обстоятельств того, как и где создаются дополнительные циркулярные ссылки.
Примеры того, когда окно предупреждения, содержащее предупреждающее сообщение, отображается для последующих циклических ссылок:
- Первый экземпляр циклической ссылки в любой открытой рабочей книге.
- После удаления всех циклических ссылок во всех открытых книгах создается новая круговая ссылка.
- После закрытия всех рабочих книг в новой рабочей книге создается формула, содержащая циклическую ссылку.
- Рабочая книга, содержащая циркулярную ссылку, открыта.