Видалення чи використання циклічних посилань

Ви ввели формулу, але вона не працює. Натомість ви отримали повідомлення про «циклічне посилання». Мільйони користувачів мають таку саму проблему, яка виникає, тому що формула намагається обчислити сама себе, а функцію повторного обчислення при цьому вимкнуто. Ось як це виглядає:

формула, яка призведе до циклічного посилання

Формула =D1+D2+D3 не працює, тому що її розташовано в клітинці D3, тож вона намагається обчислити саму себе. Щоб вирішити цю проблему, ви можете перемістити формулу в іншу клітинку (у рядку формул натисніть клавіші Ctrl+X, щоб вирізати формулу, виділіть іншу клітинку та натисніть клавіші Ctrl+V).

Користувачі також часто припускаються помилки, коли використовують функцію, яка містить посилання на саму себе. Наприклад, клітинка F3 містить формулу =SUM(A3:F3). Ось приклад:

Ваш браузер не підтримує відео. Інсталюйте Microsoft Silverlight, Adobe Flash Player або Internet Explorer 9.

Ви також можете скористатись одним із методів нижче.

  • Ввівши формулу, спочатку переконайтеся, що вона не посилається на власну клітинку. Наприклад, у клітинці A3 може міститися формула =(A1+A2)/A3. Формули на кшталт =A1+1 (у клітинці А1) також спричиняють помилки циклічного посилання.

Також перевірте наявність непрямих посилань. Вони трапляються, якщо формула у клітинці A1 використовує іншу формулу у клітинці B1, яка, у свою чергу, посилається на клітинку A1. Якщо це заплутало вас, уявіть, що означає така ситуація для програми Excel.

  • Якщо вам не вдалося знайти помилку, перейдіть на вкладку Формули, клацніть стрілку поруч із кнопкою Контроль помилок, наведіть вказівник миші на пункт Циклічні посилання, а потім клацніть першу клітинку зі списку у вкладеному меню.

    Команда «Циклічні посилання»

  • Перегляньте формулу у клітинці. Якщо не вдалося визначити, чи спричиняє ця клітинка циклічне посилання, виберіть наступну клітинку в підменю Циклічні посилання.

  • Продовжуйте переглядати та виправляти циклічні посилання у книзі, повторюючи деякі або всі кроки з 1 по 3, доки в рядку стану не зникне напис «Циклічні посилання».

Поради    

  • У рядку стану в нижньому лівому куті відображається Циклічні посилання та адреса клітинки одного з циклічних посилань.

    Якщо циклічні посилання містяться на інших аркушах, крім активного, у рядку стану відображатиметься лише напис «Циклічні посилання» без адрес клітинок.

  • Щоб переходити між клітинками в циклічному посиланні, двічі клацніть стрілку. Стрілка позначає клітинки, які впливають на значення виділеної клітинки. Щоб відобразити стрілку, на вкладці Формули натисніть кнопку Впливові клітинки чи Залежні клітинки.

    Впливові клітинки

Попередження про циклічне посилання

Коли Excel уперше виявляє циклічне посилання, відображається попередження. Натисніть кнопку OK або закрийте вікно повідомлення.

Після закриття повідомлення програма Excel відображатиме або нуль, або останнє обчислене значення у клітинці. Це може здаватися дивним, але, так, у деяких випадках формула може успішно обчислити попередні клітинки перед спробою обчислити саму себе. Наприклад, формула, яка використовує функцію IF, може працювати, доки користувач не введе аргумент (частину даних, яка потрібна формулі для правильної роботи), який призведе до того, що функція обчислюватиме саму себе.

Якщо є підозра, що у клітинці, у якій не відображається 0, міститься циклічне посилання, спробуйте виконати таку дію:

  • Клацніть формулу в рядку формул і натисніть клавішу Enter.

Увага!    У багатьох випадках після створення додаткових формул, що містять циклічні посилання, попередження програми Excel більше не відображатиметься. У списку нижче наведено деякі сценарії (але не всі), за яких відображається попередження.

  • Користувач створює перший екземпляр циклічного посилання в будь-якій відкритій книзі.

  • Користувач видаляє всі циклічні посилання в усіх відкритих книгах, а потім створює нове циклічне посилання.

  • Користувач закриває всі книги, створює нову книгу, а потім вводить формулу, яка містить циклічне посилання.

  • Користувач відкриває книгу, у якій міститься циклічне посилання.

  • Коли інші книги не відкрито, користувач відкриває книгу та створює циклічне посилання.

Повторне обчислення

Іноді може бути потрібно використовувати циклічні посилання, тому що вони спричиняють ітерацію функції – повторення, яке триває до виконання певної числової умови. Це може сповільнити роботу комп’ютера, тому, зазвичай, функцію повторного обчислення у програмі Excel вимкнуто.

Якщо ви не знайомі з повторними обчисленнями, ви, напевно, не залишите жодного циклічного посилання без змін. В іншому разі можна ввімкнути повторне обчислення, але потрібно визначити, скільки разів формула має переобчислюватися. Якщо ввімкнути повторне обчислення, не змінивши значення для максимальної кількості повторів або максимальної зміни, програма Excel припиняє обчислення після 100 повторів або після того, як різниця між значеннями в циклічному посиланні буде меншою за 0,001, залежно від того, яка з цих подій відбудеться першою. Однак ви можете контролювати максимальну кількість повторів і обсяг прийнятних змін.

  1. В Excel 2010 або новішої версії виберіть Файл > Параметри > Формули. В Excel для Mac відкрийте меню Excel, а потім виберіть Параметри > Обчислення.

    В Excel 2007 натисніть кнопку Microsoft Office Зображення кнопки Office , клацніть Параметри Excel, а потім виберіть категорію Формули.

  2. У розділі Параметри обчислення встановіть прапорець Увімкнути повторне обчислення. На комп’ютері з Mac клацніть Use iterative calculation (використовувати повторне обчислення).

  3. Щоб задати максимальну кількість повторних обчислень у програмі Excel, введіть число повторів у полі Максимальна кількість повторів. Що більша кількість повторів, то більше часу триватиме обчислення аркуша у програмі Excel.

  4. У полі Відносна похибка введіть найменше значення, яке потрібне для продовження повторного обчислення. Це найменша зміна в будь-якому обчислюваному значенні. Що менше число, то точніший буде результат, і довше обчислюватиметься аркуш.

Повторне обчислення може мати три результати:

  • Рішення зводиться – це означає, що отримано стійкий кінцевий результат. Це потрібний результат.

  • Рішення розходиться, тобто з кожною ітерацією різниця між поточним і попереднім результатами збільшується.

  • Рішення постійно змінюється між двома значеннями. Наприклад, після першої ітерації результат дорівнює 1, після наступної – 10, після ще однієї ітерації результат знову дорівнює 1 тощо.

На початок сторінки

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Див. також

Огляд формул в Excel

Способи уникнення недійсних формул

Виявлення та виправлення помилок у формулах

Сполучення клавіш і функціональні клавіші в Excel

Функції Excel (за алфавітом)

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

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×