Циклические ссылки возникают, когда формула прямо или косвенно ссылается на ячейку, в которой она хранится. Например, если B3 содержит формулу = B2 + B3, то B3 содержит круговую ссылку.

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

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

По большей части Excel очень снисходительно относится к циклическим ссылкам, если у вас включен элемент управления Enable Iterative Calculation. (Откройте диалоговое окно «Параметры Excel» и нажмите «Формулы» в левой части диалогового окна.) Если вы установите флажок «Включить итеративное вычисление», а затем введите циклическую ссылку, Excel не протестует. Вместо этого он использует настройки в диалоговом окне «Параметры Excel», чтобы контролировать, сколько раз циклическая ссылка будет повторяться, прежде чем она будет считаться выполненной.

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

  • Вы открываете любую другую книгу, кроме книги по умолчанию, созданной при первом запуске Excel.

  • Вы меняете флажок Итерация, когда отображается книга по умолчанию.

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

Кроме того, если в вашей системе определена личная книга, то установка флажка «Включить итеративный расчет» в этом файле всегда используется по умолчанию. Зачем? Поскольку Личная книга всегда открывается первой, а первая открытая книга всегда определяет значение по умолчанию для параметра.

Если у вас есть сохраненная книга, в которой используются циклические ссылки, и флажок «Включить итеративное вычисление» снят (по умолчанию или явно), то при открытии книги, содержащей циклические ссылки, Excel отображает предупреждение. Если вы не хотите видеть это предупреждение, то очевидным решением будет либо убедиться, что вы открываете книгу раньше любой другой книги (чтобы использовалась ее настройка Iteration), либо явно установите флажок Enable Iterative Calculation перед открытием рабочая тетрадь.

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

Private Sub Workbook_Open()

Application.Iteration = True End Sub

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

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9748) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Iterating_Circular_References [Итерация циклических ссылок].