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

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

Розуміння посилань на Excel

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

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

Виключіть циркулярне посилання в Excel

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

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

  1. Відкрийте таблицю

  2. Відкрийте електронну таблицю Microsoft Excel, яка містить кругові посилання.

  3. Використовуйте меню формул

  4. Клацніть на вкладку "Формули" у меню стрічки у верхній частині вікна Excel. Клацніть на маленьку стрілку поруч із кнопкою "Перевірка помилок" у цій області. Наведіть курсор миші на "Кругові посилання", і з'явиться останнє введене кругове посилання. Клацніть на це посилання, щоб перейти до цієї комірки електронної таблиці.

  5. Шукайте циркулярну довідку

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

  7. Трасування прецедентів за допомогою Excel

  8. Натисніть кнопку "Відстежити прецеденти" в області "Аудит формул" на вкладці Формула меню стрічки. Це створить стрілку в електронній таблиці від кожної комірки, згаданої у вказаній комірці, до вказаної комірки. Натисніть кнопку "Відстежити залежних", яка створить стрілки від вказаної комірки до кожної комірки, яка посилається на вказану комірку.

  9. Дотримуйтесь посилального ланцюга

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

  11. Знайдіть більше кіл, як потрібно

  12. Клацніть на стрілку спадного меню поруч із пунктом "Перевірка помилок" ще раз, щоб побачити, чи з'являється інше кругове посилання. Якщо так, повторіть цей процес, щоб очистити його, і продовжуйте, доки більше не з’являться кругові посилання.

Ітераційний розрахунок Excel

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

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

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