Циркулярные ссылки в формулах Excel и способы их обработки

круговая ссылка возникает в Excel, когда формула содержит ссылку на ячейку, содержащую формулу.

Информация в статье относится к версиям Excel 2019, 2016, 2013, 2010, Excel Online и Excel для Mac.

Пример циркулярной ссылки

Пример циклической ссылки этого типа показан на изображении ниже, где формула в ячейке C1 содержит ссылку на ячейку C1 в формуле:

 = A1 + A2 + A3 + C1 

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

Циркулярное предупреждение

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

Параметры пользователя

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

  • Справка предназначена для использования в случае непреднамеренной циклической ссылки. Это приведет вас к справочному файлу Excel по круговым ссылкам.
  • ОК используется, чтобы сообщить Excel, что циклическая ссылка была сделана намеренно, и что она должна оставить ее на месте.

Непреднамеренные циркулярные ссылки

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

Преднамеренные циркулярные ссылки

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

Включение итерационных вычислений

В Excel есть возможность включить эти итеративные вычисления , если вы планируете их использовать.

Чтобы включить итеративные вычисления:

  1. Выберите Файл (или кнопку Office в Excel 2007).

  2. Выберите Параметры , чтобы открыть диалоговое окно «Параметры Excel».

  3. \ На левой панели диалогового окна выберите Формулы.

  4. На правой панели диалогового окна установите флажок Включить итеративный расчет .

Под флажком доступны варианты:

  • Установить максимальное количество итераций; это количество раз, которое Excel должен пересчитать формулу.
  • Установите максимально допустимое изменение между результатами расчета – чем меньше число, тем точнее результат.

Отображение нулей в затронутых клетках

Для ячеек, содержащих циклические ссылки, Excel отображает либо 0 , как показано в ячейке C1 в примере, либо последнее вычисленное значение в ячейке.

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

Подробнее о циркулярном справочном предупреждении

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

Примеры того, когда окно предупреждения, содержащее предупреждающее сообщение, отображается для последующих циклических ссылок:

  • Первый экземпляр циклической ссылки в любой открытой рабочей книге.
  • После удаления всех циклических ссылок во всех открытых книгах создается новая круговая ссылка.
  • После закрытия всех рабочих книг в новой рабочей книге создается формула, содержащая циклическую ссылку.
  • Рабочая книга, содержащая циркулярную ссылку, открыта.
Оцените статью
Solutics.ru
Добавить комментарий