Ви ввели формулу, але вона не працює. Натомість ви отримали це повідомлення про "циклічне посилання". Мільйони людей мають однакову проблему, тому що формула намагається обчислити себе. Ось як це виглядає:
Формула =D1+D2+D3 розривається, тому що вона розташована в клітинці D3, і вона намагається обчислити себе. Щоб вирішити цю проблему, можна перемістити формулу до іншої клітинки. Натисніть клавіші Ctrl+X , щоб вирізати формулу, виділіть іншу клітинку та натисніть клавіші Ctrl+V , щоб вставити її.
Порада.
- Іноді може знадобитися використати циклічні посилання, оскільки вони призводять до ітерації функцій. У такому разі перейдіть до статті Докладні відомості про повторне обчислення.
- Докладні відомості про написання формул див. в статті Огляд формул у програмі Excel.
Ще однією поширеною помилкою є використання функції, яка містить посилання на себе; Наприклад, клітинка F3 містить формулу =SUM(A3:F3). Ось приклад.
Також можна скористатись одним із наведених нижче методів.
- Ввівши формулу, спочатку переконайтеся, що вона не посилається на власну клітинку. Наприклад, у клітинці A3 може міститися формула =(A1+A2)/A3. Формули на кшталт =A1+1 (у клітинці А1) також спричиняють помилки циклічного посилання.
Також перевірте наявність непрямих посилань. Вони трапляються, якщо формула у клітинці A1 використовує іншу формулу у клітинці B1, яка, у свою чергу, посилається на клітинку A1. Якщо це заплутало вас, уявіть, що означає така ситуація для програми Excel.
- Якщо не вдалося знайти помилку, перейдіть на вкладку Формули , клацніть стрілку поруч із пунктом Перевірка помилок, наведіть вказівник миші на пункт Циклічні посилання, а потім виберіть першу клітинку з вкладеного меню.
- Перегляньте формулу у клітинці. Якщо не вдається визначити, чи є клітинка причиною циклічного посилання, виберіть наступну клітинку в підменю Циклічні посилання .
- Продовжуйте переглядати та виправляти циклічні посилання у книзі, повторюючи деякі або всі кроки з 1 по 3, доки в рядку стану не зникне напис «Циклічні посилання».
Поради
- Якщо ви не знайомі з формулами, див. статтю Excel 2016 Базове навчання в LinkedIn Learning.
- У рядку стану в нижньому лівому куті відображається Циклічні посилання та адреса клітинки одного з циклічних посилань.
Якщо циклічні посилання містяться на інших аркушах, але не на активному аркуші, у рядку стану відображається лише "Циклічні посилання" без адрес клітинок. - У циклічному посиланні можна переходити між клітинками, двічі клацаючи стрілку. Стрілка позначає клітинки, які впливають на значення виділеної клітинки. Щоб відобразити стрілку, виберіть пункт Формули, а потім виберіть впливові клітинки або Залежні клітинки.
Попередження про циклічне посилання
Коли в програмі Excel уперше використовується циклічне посилання, виводиться попередження. Натисніть кнопку OK або закрийте вікно повідомлення.
Коли ви закриваєте повідомлення, Excel відображає в клітинці 0 або останнє обчислюване значення. І тепер ви, напевно, говорите: "Зачекайте, останнє обчислюване значення?" Так. У деяких випадках формула може успішно запуститися, перш ніж вона спробує обчислити себе. Наприклад, формула, яка використовує функцію IF , може працювати, доки користувач не введе аргумент (фрагмент даних, який формула має запускати належним чином), через що формула обчислюється самостійно. У такому разі excel зберігає значення з останнього успішного обчислення.
Якщо є підозра, що у клітинці, у якій не відображається 0, міститься циклічне посилання, спробуйте виконати таку дію:
- Виділіть формулу в рядку формул і натисніть клавішу Enter.
Важливо У багатьох випадках, якщо ви створюєте додаткові формули, які містять циклічні посилання, програма Excel більше не відображатиме попередження. У списку нижче наведено деякі сценарії (але не всі), за яких відображається попередження.
- Користувач створює перший екземпляр циклічного посилання в будь-якій відкритій книзі.
- Користувач видаляє всі циклічні посилання в усіх відкритих книгах, а потім створює нове циклічне посилання.
- Користувач закриває всі книги, створює нову книгу, а потім вводить формулу, яка містить циклічне посилання.
- Користувач відкриває книгу, у якій міститься циклічне посилання.
- Коли інші книги не відкрито, користувач відкриває книгу та створює циклічне посилання.
Повторне обчислення
Іноді може бути потрібно використовувати циклічні посилання, тому що вони спричиняють ітерацію функції – повторення, яке триває до виконання певної числової умови. Це може сповільнити роботу комп’ютера, тому, зазвичай, функцію повторного обчислення у програмі Excel вимкнуто.
Якщо ви не знайомі з повторними обчисленнями, ви, напевно, не залишите жодного циклічного посилання без змін. В іншому разі можна ввімкнути повторне обчислення, але потрібно визначити, скільки разів формула має переобчислюватися. Якщо ввімкнути повторне обчислення, не змінивши значення для максимальної кількості повторів або максимальної зміни, програма Excel припиняє обчислення після 100 повторів або після того, як різниця між значеннями в циклічному посиланні буде меншою за 0,001, залежно від того, яка з цих подій відбудеться першою. Однак ви можете контролювати максимальну кількість повторів і обсяг прийнятних змін.
- Виберітьпункт Формули параметрів>файлу>. Якщо ви використовуєте Excel для Mac, відкрийте меню Excel і виберіть пункт Обчислення параметрів>.
- У розділі Параметри обчислення встановіть прапорець Увімкнути повторне обчислення. На комп'ютері Mac виберіть Використовувати повторне обчислення.
- Щоб задати максимальну кількість повторних обчислень у програмі Excel, введіть число повторів у полі Максимальна кількість повторів. Що більша кількість повторів, то більше часу триватиме обчислення аркуша у програмі Excel.
- У полі Відносна похибка введіть найменше значення, яке потрібне для продовження повторного обчислення. Це найменша зміна в будь-якому обчислюваному значенні. Що менше число, то точніший буде результат, і довше обчислюватиметься аркуш.
Повторне обчислення може мати три результати:
- Рішення зводиться – це означає, що отримано стійкий кінцевий результат. Це потрібний результат.
- Рішення розходиться, тобто з кожною ітерацією різниця між поточним і попереднім результатами збільшується.
- Рішення постійно змінюється між двома значеннями. Наприклад, після першої ітерації результат дорівнює 1, після наступної – 10, після ще однієї ітерації результат знову дорівнює 1 тощо.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті tech Excel або отримати підтримку в спільнотах.
Порада.
Якщо ви власник малого бізнесу, щоб дізнатися більше про те, як налаштувати Microsoft 365, відвідайте сторінку Довідка для малого бізнесу & навчання.
Додаткові відомості
Способи уникнення недійсних формул
Виявлення та виправлення помилок у формулах