Циклічне посилання відбувається в 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 може не відображати попередження знову. Це залежить від обставин того, як і де створюються додаткові циркулярні посилання.
Приклади того, коли вікно попередження, що містить попереджувальне повідомлення, відображається для наступних циклічних посилань:
- Перший примірник циклічного посилання в будь-якій відкритій робочій книзі.
- Після видалення всіх циклічних посилань у всіх відкритих книгах створюється нове кругове посилання.
- Після закриття всіх робочих книг у новій робочій книзі створюється формула, що містить циклічне посилання.
- Робоча книга, що містить циркулярне посилання, відкрита.