Як знайти циклічні посилання в Excel 4 способами

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=СУММ(A2:A10)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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