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

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

У цій статті я продемонструю вам як можна їх знайти та усунути.

Тож почнемо!

Циклічне посилання – що це?

Все досить просто, ви прописуєте формулу в осередок, в якому використовується той самий осередок. Можливо, вам складно зрозуміти, виходячи з визначення, тому розглянемо приклад.

Допустимо, в осередку А6 ви вказуєте:

=СУМ(A1:A6)

Excel відразу попередить вас про те, що ви створюєте циклічне посилання.

Грубо кажучи, коли ви викликаєте функцію таким чином, ви створюєте цикл.

Як шукати такі посилання?

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

Тому краще перевірити вашу табличку, особливо якщо у вас виникають якісь помилки, які ви не можете зрозуміти.

Покрокова інструкція перевірки:

  • Натисніть “Формули” -> “Перевірка помилок”;

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

  • Далі – “Циклічні посилання”;

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

  • Якщо побачите якийсь осередок — клацніть на нього.

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

Важливі моменти:

  1. Якщо посилання все-таки є, але на іншому аркуші, то ви не побачите адресу осередку;
  2. Якщо Excel видав вам попередження про створення циклічного посилання, а ви закрили його, більше попереджень ви не побачите;
  3. При відкритті файлу з циклічним посиланням Excel відразу попередить вас.

Як їх видалити?

Отже, ми навчилися знаходити такі посилання, але як їх видаляти?

Тут два варіанти – або видаляти весь осередок з посиланням, або змінювати формулу так, щоб вона не була циклічною.

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

Ось приклад великого кільця таких посилань:

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

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

Що ж робити у такій ситуації?

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

Покрокова інструкція щодо використання:

  1. Виділіть осередок із циклічним посиланням та клацніть «Формули»;
  2. Далі – «Впливають осередки».

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

Готово! Ви побачите сині лінії, які підкажуть вам які дані, звідки йдуть.

Ітеративні обчислення

Чому Excel попереджає нас? Та тому що програма розуміє, що нескінченний цикл — це погано. Як мінімум тому, що якщо Excel не контролюватиме виконання функції, то ваш комп'ютер витрачатиме всі ресурси на нескінченне виконання однієї і тієї ж функції.

Але іноді дійсно необхідно створювати циклічні посилання. Тільки вам потрібно вказати скільки разів буде виконано функцію до зупинки (щоб вона не виконувалася нескінченно).

І це є «Ітеративні обчислення».

Як їх налаштувати?

Покрокова інструкція:

  1. Натисніть “Файл” -> “Параметри”;
  2. Далі клацніть розділ «Формули»;

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

  • Знайдіть «Включити ітеративні обчислення» та вкажіть потрібну кількість виконань;

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

Готово!

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

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

Для чого використовуються циклічні посилання

Найчастіше люди випадковим чином створюють такі посилання.

Але все-таки так буває не завжди. Деякі люди створюють такі посилання спеціально.

Навіщо? Давайте розглянемо приклад.

Наприклад, ви створюєте таку функцію, яка прописує час у комірку щоразу, коли ви змінюєте комірку:

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

Ось так вона виглядає:

=якщо(A2<>“”;якщо(B2<>“”;B2;ЗАРАЗ());””)

Ось і вся суть. Звичайно, існують і інші приклади, де можна використовувати циклічні посилання свідомо.

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

Сподіваюся, ця стаття виявилася корисною для вас!

nBook
Додати коментар