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

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

Наприклад, у вас є два значення в клітинках B1 і B2. Коли формула =B1+B2 вводиться в B2, вона створює кругове посилання; формула в B2 багаторазово перераховує себе, тому що щоразу, коли вона обчислює, значення B2 змінювалося.

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

Тому в цій статті ми пояснимо все, що вам потрібно знати про кругові посилання, а також про те, як знайти, виправити, видалити та використовувати кругові посилання в Excel.

Як знайти й обробити кругове посилання в Excel

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

Наприклад, у вас є стовпець чисел у клітинці A1:A4, і ви використовуєте функцію SUM (=SUM(A1:A5)) у клітинці A5. Комірка A5 безпосередньо посилається на свою власну комірку, що неправильно. Таким чином, ви отримаєте таке кругле довідкове попередження:

Отримавши наведене вище попередження, ви можете натиснути кнопку «Довідка», щоб дізнатися більше про помилку, або закрити вікно повідомлення про помилку, натиснувши кнопку «ОК» або «X», і отримати «0» як результат.

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

Прямі та непрямі циркулярні посилання

Циркулярні посилання можна розділити на два типи: прямі кругові посилання та непрямі кругові посилання.

Пряме посилання

Пряме кругове посилання досить просте. Попередження про пряме кругове посилання з’являється, коли формула звертається безпосередньо до власної клітинки.

У наведеному нижче прикладі формула в клітинці A2 безпосередньо посилається на її власну клітинку (A2).

Коли з’явиться попередження, ви можете натиснути «ОК», але це призведе лише до «0».

Непряма циркулярна довідка

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

Давайте пояснимо на цьому простому прикладі.

Тепер значення починається з A1, яке має значення 20.

Далі клітинка C3 відноситься до комірки A1.

Тоді клітинка A5 відноситься до клітинки C3.

Тепер замініть значення 20 у клітинці A1 на формулу, як показано нижче. Кожна інша клітинка залежить від комірки A1. Коли ви використовуєте посилання на будь-яку іншу попередню клітинку формули в A1, це спричинить кругове попередження про посилання. Тому що формула в A1 відноситься до комірки A5, яка відноситься до C3, а клітинка C3 повертається до A1, отже, це кругове посилання.

Коли ви натискаєте кнопку «ОК», це призводить до значення 0 у клітинці A1, і Excel створює зв’язаний рядок, що показує прецеденти і залежні трасування, як показано нижче. Ми можемо використовувати цю функцію, щоб легко знаходити та виправляти/видаляти кругові посилання.

Як увімкнути / вимкнути кругові посилання в Excel

За замовчуванням ітераційні обчислення вимкнено (вимкнено) в Excel. Ітераційні обчислення — це повторювані обчислення, поки вони не відповідають певній умові. Якщо його вимкнено, Excel відображає повідомлення кругової довідки та повертає 0 як результат.

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

У Excel 2010, Excel 2013, Excel 2016, Excel 2019 і Microsoft 365 перейдіть на вкладку «Файл» у верхньому лівому куті Excel, а потім натисніть «Параметри» на лівій панелі.

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

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

Щоб досягти цього в попередніх версіях Excel, виконайте такі дії:

  • У Excel 2007 натисніть кнопку Office > Параметри Excel > Формули > Область ітерацій.
  • У Excel 2003 та попередніх версіях потрібно перейти до Меню > Інструменти > Параметри > вкладка Розрахунок.

Максимальні ітерації та максимальні параметри змін

Увімкнувши ітераційні обчислення, ви зможете керувати ітеративними обчисленнями, вказавши два параметри, доступні в розділі Увімкнути ітераційні обчислення, як показано на знімку екрана нижче.

  • Максимальна кількість ітерацій – Це число вказує, скільки разів формула повинна бути перерахована, перш ніж дати вам остаточний результат. Значення за замовчуванням — 100. Якщо змінити його на «50», Excel повторить обчислення 50 разів, перш ніж надати вам остаточний результат. Пам’ятайте, що чим більше число ітерацій, тим більше ресурсів і часу потрібно на обчислення.
  • Максимальна зміна – Визначає максимальну зміну між результатами розрахунку. Це значення визначає точність результату. Чим менше число, тим точнішим буде результат і тим більше часу потрібно для обчислення робочого аркуша.

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

Знайдіть кругову довідку в Excel

Припустимо, що у вас великий набір даних і ви отримали попередження про кругле посилання, вам все одно потрібно буде з’ясувати, де (в якій клітинці) сталася помилка, щоб виправити її. Щоб знайти циркулярні посилання в Excel, виконайте такі дії:

Використання інструмента перевірки помилок

Спочатку відкрийте робочий аркуш, на якому відбулося кругове посилання. Перейдіть на вкладку «Формула», натисніть стрілку поруч із інструментом «Перевірка помилок». Потім просто наведіть курсор на параметр «Кругові посилання», Excel покаже вам список усіх комірок, які беруть участь у круговому посиланні, як показано нижче.

Клацніть потрібну адресу клітинки в списку, і ви перейдете до цієї адреси осередку, щоб вирішити проблему.

Використання рядка стану

Ви також можете знайти циркулярну довідку в рядку стану. У рядку стану Excel буде показано останню адресу комірки з круглим посиланням, наприклад «Кругові посилання: B6» (див. знімок екрана нижче).

Є певні речі, які ви повинні знати під час роботи з циркулярним посиланням:

  • У рядку стану не відображатиметься адреса осередку циркулярного посилання, якщо увімкнено параметр Ітеративний обчислення, тому вам потрібно вимкнути його, перш ніж почати переглядати книгу для кругових посилань.
  • Якщо циркулярне посилання не знайдено на активному аркуші, рядок стану відображає лише «Кругові посилання» без адреси осередку.
  • Ви отримаєте круглу довідкову підказку лише один раз, і після того, як ви натиснете «ОК», наступного разу підказка не відобразиться.
  • Якщо у вашій книзі є циклічні посилання, вона показуватиме підказку щоразу, коли ви її відкриваєте, доки ви не вирішите кругове посилання або доки не ввімкнете ітераційне обчислення.

Видаліть кругову довідку в Excel

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

Щоб виправити кругові посилання, ви повинні знайти кожне кругове посилання окремо та спробувати змінити його, видалити кругову формулу взагалі або замінити її іншою.

Іноді в простих формулах все, що вам потрібно зробити, це змінити параметри формули, щоб вона не посилалася на себе. Наприклад, змініть формулу в B6 на =SUM(B1:B5)*A5 (змінивши B6 на B5).

Він поверне результат обчислення як «756».

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

Є два методи трасування, які можуть допомогти вам видалити кругові посилання, показуючи зв’язки між формулами та клітинками.

Щоб отримати доступ до методів трасування, перейдіть на вкладку «Формули», а потім натисніть «Прецеденти відстеження» або «Залежні від трасування» в групі «Аудит формул».

Слідові прецеденти

Коли ви вибираєте цей параметр, він відстежує клітинки, які впливають на значення активної клітинки. Він малює синю лінію, що вказує, які клітинки впливають на поточну клітинку. Комбінація клавіш для використання прецедентів трасування Alt + T U T.

У наведеному нижче прикладі синя стрілка показує клітинки, які впливають на значення B6: B1:B6 і A5. Як ви можете бачити нижче, клітинка B6 також є частиною формули, що робить її круговим посиланням і змушує формулу повертати «0» як результат.

Це можна легко виправити, замінивши B6 на B5 в аргументі SUM: =SUM(B1:B5).

Відслідковувати залежні

Функція залежних від трасування відстежує клітинки, які залежать від вибраної комірки. Ця функція малює синю лінію, що вказує, на які клітинки впливає вибрана клітинка. Тобто він показує, які клітинки містять формули, які посилаються на активну клітинку. Комбінація клавіш для використання залежних Alt + T U D.

У наступному прикладі на клітинку D3 впливає B4. Його значення для отримання результатів залежить від B4. Отже, залежно від трасування малює синю лінію від B4 до D3, вказуючи, що D3 залежить від B4.

Навмисне використання кругових посилань у Excel

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

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

Для початку увімкніть «Ітераційне обчислення» у своїй книзі Excel. Після того, як ви ввімкнули ітераційне обчислення, ви можете почати використовувати кругові посилання на свою користь.

Припустимо, що ви купуєте будинок і хочете дати своєму агенту 2% комісійної від загальної вартості будинку. Загальна вартість буде розрахована в клітинці B6, а відсоток комісії (агентська комісія) розраховується в B4. Комісія розраховується із загальної вартості, а загальна вартість включає комісію. Оскільки клітинки B4 і B6 залежать одна від одної, це створює кругове посилання.

Введіть формулу для обчислення загальної вартості в клітинку B6:

=СУМА(B1:B4)

Оскільки загальна вартість включає комісію агента, ми включили B4 у наведену вище формулу.

Щоб розрахувати комісію агента в розмірі 2%, вставте цю формулу в B4:

=B6*2%

Тепер формула в комірці B4 залежить від значення B6 для обчислення 2% загальної комісії, а формула в B6 залежить від B4 для обчислення загальної вартості (включаючи комісію агента), звідси й кругове посилання.

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

Опція ітераційних обчислень зазвичай вимкнена за замовчуванням. Якщо ви не вмикали його, і коли ви вводите формулу в B4, яка створить кругове посилання. Excel видасть попередження, і коли ви натиснете «ОК», з’явиться стрілка трасування.

Це воно. Це все, що вам потрібно знати про циркулярні посилання в Excel.