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

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

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

Інша поширена помилка – використання функції, яка містить посилання на себе; Наприклад, клітинка F3 містить формулу =SUM(A3:F3). Ось приклад.

Ваш браузер не підтримує відео.

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

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

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

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

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

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

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

Поради    

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

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

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

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

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

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

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

Якщо є підозра, що у клітинці, у якій не відображається 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 чи отримати підтримку в спільноті Answers.

Додаткові відомості

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

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

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

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

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

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

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

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

Чи були ці відомості корисні?

Наскільки ви задоволені якістю перекладу?
Що вплинуло на ваші враження?

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

×