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

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

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

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

Зміст

• Що таке циклічні посилання
• Вікно циклічного довідкового повідомлення про помилку
• Як знайти циклічні посилання в Excel
• Як знайти циклічні посилання в рядку стану Excel
• Як знайти приховані циклічні посилання в Excel
  • Як користуватися інструментом Trace Dependent
  • Інструмент Trace Precedents
• 2 способи, як видалити циклічні посилання в Excel
  • 2. Використовуйте альтернативні формули
• 5 порад щодо запобігання циклічним посиланням
  • 1. Будьте обережні при виборі діапазонів
  • 2. Ретельно плануйте свої формули
  • 3. Використовуйте альтернативні формули
  • 4. Розбийте складні формули на менші частини
  • 5. Усувайте проблеми по ходу роботи
• Як увімкнути ітераційне обчислення та навіщо?

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

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

Це легше зрозуміти на простому прикладі.

Припустімо, що у клітинках A1 і B1 є два числа. Ви хочете використати формулу в клітинці A3, яка додає два числа. Формула в клітинці A3 має виглядати так:

=A1 + B1

Однак якщо ви помилитесь і включите C1 в обчислення, тепер у вас буде циклічне посилання:

=A1 + B1 + C1

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

На щастя, Excel створено для вирішення таких ситуацій, відображаючи попередження, коли виявлено циклічне посилання.

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

Вікно циклічного довідкового повідомлення про помилку

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

Ось пояснювальний текст:

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

Коли ви побачите це діалогове вікно, вам слід натиснути «OK» і скористатися методами, описаними в цій статті, щоб вирішити проблему.

Однак вам не потрібно негайно виправляти помилку. Ви можете продовжити роботу з електронною таблицею з наміром вирішити проблему пізніше.

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

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

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

На цьому малюнку показано розташування в групі аудиту формул.

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

1. Перейдіть на вкладку Формули.

1. Натисніть спадне меню Перевірка помилок у групі Аудит формул.

2. У розкривному списку виберіть «Циклові посилання».

3. Натисніть посилання, щоб перемістити курсор до першої клітинки з проблемою.

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

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

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

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

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

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

Інший спосіб виявити циклічне посилання — подивитися на рядок стану внизу аркуша Excel.

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

Наприклад, якщо в клітинці C1 є циклічне посилання, а з електронною таблицею немає інших проблем, у рядку стану відображатиметься: «Циклові посилання: C1».

Це зображення показує, де можна побачити дисплей.

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

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

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

Excel має два інструменти, які допомагають відстежувати зв’язки та досліджувати ці проблеми:

1. Інструмент відстеження залежних

2. Інструмент Trace Precedents

Як користуватися інструментом Trace Dependent

Досвідчені користувачі Excel використовують інструмент Trace Dependents, щоб дослідити ці проблеми та виправити циклічні посилання. Вибравши одну або кілька клітинок і використовуючи цей інструмент, Excel покаже вам залежні клітинки.

Інструмент доступний у розділі «Аудит формул» на вкладці «Формули».

Щоб скористатися інструментом, виконайте такі дії:

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

2. Перейдіть на вкладку «Формули» на стрічці Excel.

3. Натисніть кнопку «Відстеження залежних» у групі «Аудит формули».

4. Перегляньте стрілку або стрілки, що показують залежності.

Excel показує стрілки від значення активної комірки до будь-якої клітинки, яка від неї залежить. Синя стрілка на зображенні нижче показує, що клітинка D6 має залежність від клітинки B2.

Інструмент Trace Precedents

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

Інструмент доступний у розділі «Аудит формул» на вкладці «Формули».

Щоб скористатися інструментом, виконайте такі дії:

1. Натисніть клітинку, що містить формулу, яку ви хочете дослідити.

2. Перейдіть на вкладку «Формула» на стрічці Excel.

3. Натисніть кнопку «Trace Precedents» у групі «Formula Auditing».

4. Перегляньте стрілки трасування, які відображає Excel.

Сині стрілки на малюнку нижче показують, що клітинка D6 залежить від діапазону клітинок A2:B5.

Використання обох інструментів допоможе вам знайти циклічні посилання Excel, які не відразу помітні. Наступним кроком є ​​усунення проблеми у вибраній клітинці. Потім ви можете продовжити пошук тієї самої проблеми в інших клітинках.

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

Існує два основні методи вирішення циклічних посилань:

• порушення кругового посилання

• використовуючи альтернативні формули

Давайте пояснимо обидва способи.

1. Порушення циклічного посилання

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

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

Потім вам слід перевірити формулу на панелі формул. Шукайте посилання на саму комірку.

У нашому першому прикладі циклічного посилання формула додавання в комірці C1 містила посилання на саму комірку:

=A1 + B1 + C1

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

2. Використовуйте альтернативні формули

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

У нашому попередньому прикладі формула використовувала оператори додавання для обчислення суми діапазону клітинок. Альтернативним методом є використання функції SUM Excel.

Щоб виправити приклад, замініть формулу на:

=СУМ(A1:B1)

Так само ви можете усереднити діапазон за допомогою формули на зразок =(A1+B1)/2. Однак функція AVERAGE з меншою ймовірністю призведе до помилки.

=СЕРЕДНЄ(A1:B1)

5 порад щодо запобігання циклічним посиланням

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

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

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

1. Будьте обережні при виборі діапазонів

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

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

Ця помилка зазвичай виникає під час роботи з великими наборами даних, наприклад під час виконання тестів хі-квадрат. Рішення полягає в тому, щоб бути обережним з вибором асортименту.

2. Ретельно плануйте свої формули

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

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

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

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

Деякі функції, такі як INDIRECT або OFFSET , можуть ненавмисно створювати циклічні посилання при неправильному використанні.

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

4. Розбийте складні формули на менші частини

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

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

5. Усувайте проблеми по ходу роботи

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

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

Це полегшує їх виправлення та запобігає їх впливу на вашу робочу книгу.

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

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

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

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

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

Виконайте такі дії, щоб циклічна довідка працювала у вашому файлі Excel:

1. Перейдіть на вкладку Файл.

2. Виберіть Параметри.

3. Виберіть категорію «Формули».

4. У розділі «Параметри обчислення» встановіть прапорець «Увімкнути ітераційне обчислення».

5. Змініть число в полі максимальної кількості ітерацій або залиште 100 за умовчанням.

6. Використовуйте вікно максимальних змін, щоб зменшити ітерації відповідно до ваших вимог.