Як знайти циклічні посилання в Excel, щоб уникнути помилкових даних

Ви бачили помилку циклічного посилання на аркуші Excel і цікавилися, чи робочий аркуш не зламаний? Довірся мені! Ви не перший, хто зіткнувся з цією помилкою Excel. Сотні й тисячі користувачів Excel щодня стикаються з помилками циклічного посилання. Не хвилюйтеся! Ось як знайти циклічні посилання в Excel і вирішити їх.

Будучи завзятим користувачем Excel, ви рідко вводите однакові дані в багато клітинок. Натомість ви імпортуєте набір даних із джерела та використовуєте посилання на клітинки для створення формул. Таким чином, ви створюєте динамічну електронну таблицю.

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

Однак проблема виникає, коли ви навмисно чи випадково вводите адресу комірки, у якій зараз виконуєте обчислення. Або, коли ви створюєте багато формул на аркуші, клітинка, що містить формулу, опосередковано з’являється в іншій формулі.

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

Що таке циклічне посилання в Excel?

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

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

Ви намагаєтеся помножити 10 із клітинки B1 і помістити результат у саму клітинку B1 .

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

Що таке циклічні посилання в Excel

А тепер уявіть, що є сотні формул у робочому аркуші бухгалтерії, робочому аркуші фінансового планування, платіжній відомості співробітників, робочому аркуші оплати постачальників тощо.

Ви довірятимете помилковим даним, обчисленим Excel через помилку циклічного посилання, і це, у свою чергу, вплине на прийняття бізнес- чи професійних рішень.

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

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

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

Коли ви бачите помилку циклічного посилання в Excel?

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

Щоб уникнути будь-яких збитків, спричинених циклічними посиланнями у формулах Excel, ви повинні знати, коли вони з’являються, а коли ні. Знайдіть приклади нижче:

• На будь-якому відкритому аркуші чи книзі ви створюєте формулу та вперше вводите циклічне посилання.
• Ви або хтось видалив усі циклічні посилання з файлу Excel і зберіг його. Тепер ви або інший користувач знову вводите циклічне посилання в той самий файл.
• Ви закрили всі відкриті книги. Тепер відкрийте іншу робочу книгу та введіть формулу, яка містить циклічні посилання.

Коли ви бачите помилку циклічного посилання в Excel

• Ви щойно відкрили робочу книгу, яка раніше містила одне або багато циклічних посилань. Ви побачите попередження.
• Після того, як Excel один раз покаже вам попередження про циклічне посилання, і ви натиснете OK , це спливаюче вікно більше не відображатиметься під час того самого сеансу, навіть якщо ви продовжуєте створювати нові циклічні посилання на формули.

Таким чином, ви повинні ретельно шукати наведені вище випадки, щоб уловлювати попередження про циклічні посилання та вирішувати їх, якщо ви не шукаєте ітераційних обчислень.

Типи циклічних посилань в Excel

Якщо ви хочете навчитися знаходити циклічні посилання в Excel, ви повинні розуміти їх типи. Оскільки пошук і видалення циклічних посилань у Excel не є автоматизованим завданням. Знайдіть нижче поширені типи цієї помилки.

Приклад використання циклічного посилання

• Передбачувані циклічні посилання виникають, коли ви навмисно використовуєте їх для ітераційних обчислень. Наприклад, вам потрібно ввести мітку часу в іншу клітинку, коли хтось вводить значення в клітинку, на яку посилається, за допомогою змінних і логічних функцій, як-от ЯКЩО, АБО, СУМЕСЛИ тощо.

Ненавмисне циклічне посилання в Excel

• Ненавмисне циклічне посилання, коли ви намагаєтеся швидше працювати над формулою Excel і помилково посилаєтеся на клітинку, що містить формулу. Наприклад, вам потрібно застосувати функцію SUM з клітинки A1 до A9 та отримати результат у клітинці A10 . Але ви помилково ввели таку формулу в клітинку A10:

=СУММ(A2:A10)

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

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

• Непрямі циклічні посилання також популярні, і вони виникають, коли ви створюєте нову формулу в будь-якій клітинці, наприклад, клітинці A1 , і ця нова формула використовуватиме іншу функцію в іншій клітинці, як клітинка B1 , яка вже використовує клітинку A1 як посилання .

Тепер, коли ви зрозуміли основи циклічних посилань в Excel, давайте дізнаємося, як знайти циклічні посилання в Excel нижче:

Як знайти циклічні посилання в Excel: у рядку стану

Якщо функцію ітераційного обчислення вимкнено, у рядку стану Excel можна побачити такі сповіщення про циклічні посилання:

• Циклічні посилання: C3  (або будь-яке інше посилання на клітинку), коли ви перебуваєте на робочому аркуші, що містить циклічну формулу в книзі.

Відображення циклічних посилань у рядку стану Excel

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

Як знайти циклічні посилання в Excel: за допомогою перевірки помилок

Це найкращий спосіб перевірити наявність циклічних посилань у книзі чи аркуші. Ось кроки, які ви повинні знати:

• Відкрийте аркуш, який, як ви підозрюєте, містить циклічні формули.
• Натисніть меню «Формули» на стрічці Excel.

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

• У групі команд «Аудит формул» клацніть розкривний список «Перевірка помилок»  .
• Наведіть курсор на опцію Циклічні посилання , щоб побачити всі доступні екземпляри на всіх аркушах.
• Виберіть кожен екземпляр, і Excel автоматично визначить ці клітинки.

Як знайти циклічні посилання в Excel: за допомогою трасування формул

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

• Виберіть будь-яку клітинку, на яку є посилання, або клітинку, що містить формулу.
• Перейдіть на вкладку «Формули» в меню стрічки Excel і клацніть «Простежити прецеденти» .
• Excel виділить стрілками клітинки, які впливають на результат формули в поточній клітинці.

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

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

Цей метод особливо важливий, коли функції ітераційного обчислення активні на аркуші Excel.

Як знайти циклічні посилання в Excel: використання параметрів Excel

• Натисніть вкладку «Файл» на стрічці Excel, а потім відкрийте «Параметри Excel» на бічній панелі, що з’явиться.
• У діалоговому вікні «Параметри Excel» виберіть меню «Формули» на бічній панелі, а потім зніміть прапорець « Увімкнути ітераційне обчислення» .

Як вимкнути ітераційне обчислення в Excel

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

Як розв’язати циклічні посилання в Excel

Вірний спосіб виправити одну чи багато помилок циклічного посилання в Excel – редагувати вручну кожну формулу, яка її містить. Таким чином ви можете вирішити як випадкові, так і непрямі циклічні посилання в Excel.

Як розв’язати циклічні посилання в Excel

Ще один простий спосіб — видалити формулу з поточної комірки та розмістити її в іншому місці на тому самому аркуші.

Як знайти циклічні посилання в таблицях Google (бонусний метод)

Як і Excel, Google Таблиці не допускають будь-яких циклічних посилань в одній або кількох формулах, якщо ви не активували функцію ітераційного обчислення. Таким чином, Google Sheets негайно покаже #REF! помилка в клітинці, яка містить циклічну формулу.

Таблиці Google Як знайти циклічні посилання

Таким чином, ви можете просто вибрати всі клітинки, які містять #REF! попередження про пошук і вирішення циклічних посилань у Google Таблицях.

Циркулярні посилання Excel: заключні слова

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

Далі  найкращі шаблони календарів Excel на будь-який рік  і  найкращі шаблони бюджету Excel .