Під час роботи з формулами Excel ви можете інколи побачити таке попередження.
Ця підказка повідомляє вам, що у вашому аркуші є кругове посилання, і це може призвести до неправильного обчислення за формулами. Він також просить вас вирішити цю проблему циркулярного довідки та відсортувати її.
У цьому підручнику я висвітлю все, що вам потрібно знати про кругові посилання, а також як знайти та видалити кругові посилання в Excel.
Тож почнемо!
Що таке кругова довідка в Excel?
Простими словами, кругове посилання відбувається, коли у вас є формула у клітинці - яка сама по собі використовує клітинку (у яку вона була введена) для обчислення.
Спробую пояснити це простим прикладом.
Припустимо, у вас є набір даних у клітинці A1: A5, і ви використовуєте формулу нижче у клітинці A6:
= SUM (A1: A6)
Це дасть вам кругове попередження.
Це тому, що ви хочете підсумувати значення в комірці A1: A6, і результат повинен бути у клітинці A6.
Це створює цикл, оскільки Excel просто продовжує додавати нове значення в клітинку A6, яка постійно змінюється (отже, цикл кругового відліку).
Як знайти кругові посилання в Excel?
Незважаючи на те, що запит на попередження про кругові посилання є достатньо добрим, щоб повідомити вам, що він існує у вашому аркуші, він не повідомляє вам, де це відбувається і які посилання на клітинки викликають це.
Тож якщо ви намагаєтесь знайти та опрацювати кругові посилання на робочому аркуші, вам потрібно знати спосіб їх якось знайти.
Нижче наведено кроки для пошуку кругового посилання в Excel:
- Активуйте аркуш із круговим посиланням
- Перейдіть на вкладку Формули
- У групі Редагування формул клацніть на спадному значку Перевірка помилок (маленька стрілка вниз направо)
- Наведіть курсор на параметр «Кругові посилання». Він покаже вам клітинку з круговим посиланням на аркуші
- Натисніть на адресу комірки (що відображається), і вона перенесе вас до цієї клітинки на аркуші.
Після того, як ви вирішите проблему, ви можете знову виконати ті ж кроки, описані вище, і вона покаже більше посилань на клітинки з циркулярним посиланням. Якщо такої немає, ви не побачите жодного посилання на клітинку,
Ще один швидкий і простий спосіб знайти кругове посилання - це переглянути рядок стану. У його лівій частині він покаже вам текст Circular Reference разом з адресою комірки.
Під час роботи з круговими посиланнями вам потрібно знати кілька речей:
- Якщо ітераційний розрахунок увімкнено (розглянуто далі в цьому посібнику), рядок стану не відображатиме адресу адреси кругової довідкової комірки
- Якщо кругового посилання немає в активному аркуші (але в інших аркушах тієї самої книги), він буде показувати лише кругове посилання, а не адресу комірки
- Якщо ви отримаєте запит попередження про кругове посилання один раз і відхилите його, наступного разу запит не відобразиться знову.
- Якщо ви відкриєте книгу з циркулярним посиланням, вона відобразить запит, як тільки книга відкриється.
Як видалити кругове посилання в Excel?
Після того, як ви визначили, що у вашому аркуші є круглі посилання, настав час їх видалити (якщо ви не хочете, щоб вони були там з якоїсь причини).
На жаль, це не так просто, як натиснути клавішу видалення. Оскільки вони залежать від формул, і кожна формула різна, вам потрібно проаналізувати це для кожного конкретного випадку.
Якщо справа лише в тому, що посилання на клітинку спричиняє проблему помилково, ви можете просто виправити це, змінивши посилання.
Але іноді це не так просто.
Кругові посилання також можуть бути викликані на основі кількох клітин, які сполучаються одна з одною на багатьох рівнях.
Дозвольте мені показати вам приклад.
Нижче в комірці С6 є кругове посилання, але це не просто простий випадок самозвернення. Це багаторівневий, де клітини, які він використовує у розрахунках, також посилаються одна на одну.
- Формули в комірці A6: = SUM (A1: A5)+C6
- Формула клітинки C1 = A6*0,1
- Формула у клітинці C6 = A6+C1
У наведеному вище прикладі результат у комірці С6 залежить від значень у комірці А6 та С1, які, у свою чергу, залежать від комірки С6 (тим самим спричиняючи помилку кругового посилання)
І знову я вибрав дійсно простий приклад лише для демонстраційних цілей. Насправді, це може бути досить важко розібратися і, можливо, далеко на одному робочому аркуші або навіть розкидано по кількох аркушах.
У такому випадку є один спосіб ідентифікувати клітини, що викликають кругове посилання, а потім лікувати їх.
Це за допомогою опції Trace Precedents.
Нижче наведені кроки використання прецедентів трасування для пошуку клітин, які подаються до клітини, що має кругове посилання:
- Виберіть клітинку з круговим посиланням
- Перейдіть на вкладку Формули
- Натисніть на Трасування прецедентів
Наведені вище кроки показуватимуть блакитні стрілки, які вказуватимуть, які клітини надходять у формулу у вибраній клітині. Таким чином, ви можете перевірити формули та комірки та позбутися кругового посилання.
Якщо ви працюєте зі складними фінансовими моделями, можливо, ці прецеденти також заглиблюються на кілька рівнів.
Це добре працює, якщо у вас є всі формули, що посилаються на клітинки на одному аркуші. Якщо він є у кількох робочих аркушах, цей метод неефективний.
Як увімкнути/вимкнути ітеративні обчислення в Excel
Якщо у клітинці є кругове посилання, спочатку ви отримаєте запит попередження, як показано нижче, і якщо ви закриєте це діалогове вікно, воно дасть вам 0 у результаті в комірці.
Це пояснюється тим, що коли існує кругове посилання, це нескінченний цикл, і Excel не хоче зачіпати його. Тому він повертає 0.
Але в деяких випадках вам може знадобитися, щоб кругове посилання було активним і зробили пару ітерацій. У такому випадку замість нескінченного циклу ви можете вирішити, скільки разів цикл потрібно запускати.
Це називається ітераційний розрахунок в Excel.
Нижче наведено кроки, щоб увімкнути та налаштувати ітеративні обчислення в Excel:
- Перейдіть на вкладку Файл
- Натисніть Параметри. Відкриється діалогове вікно Параметри Excel
- Виберіть Формула на лівій панелі
- У розділі Параметри обчислення встановіть прапорець "Увімкнути ітераційний розрахунок". Тут ви можете вказати максимальні ітерації та максимальне значення зміни
Це воно! Наведені вище кроки дозволять ітераційний розрахунок у Excel.
Дозвольте мені також швидко пояснити два варіанти в ітераційному обчисленні:
- Максимальні ітерації: Це максимальна кількість разів, яку потрібно розрахувати Excel, перш ніж дати вам кінцевий результат. Отже, якщо ви вкажете 100, Excel буде запускати цикл 100 разів, перш ніж дати вам кінцевий результат.
- Максимальна зміна: Це максимальна зміна, яка, якщо її не досягти між ітераціями, обчислення буде зупинено. За замовчуванням це значення .001. Чим нижче це значення, тим точнішим буде результат.
Пам’ятайте, що чим більше разів ітерації виконуються, тим більше часу та ресурсів потрібно Excel для цього. Якщо ви зберігаєте максимальну кількість ітерацій, це може призвести до уповільнення роботи Excel або її збоїв.
Примітка: Коли ввімкнено ітеративні обчислення, Excel не відображатиме запит попередження про кругові посилання, а також тепер відображатиме його у рядку стану.Навмисне використання циркулярних посилань
У більшості випадків наявність кругового посилання на вашому аркуші буде помилкою. Ось чому Excel показує вам підказку: «Спробуйте видалити або змінити ці посилання або перемістити формули до різних комірок».
Але можуть бути окремі випадки, коли вам потрібна кругова довідка, щоб ви могли отримати бажаний результат.
Про один такий конкретний випадок, про який я вже писав, - отримання позначки часу у клітинці у комірці в Excel.
Наприклад, припустимо, що ви хочете створити формулу, щоб кожен запис, зроблений у комірці у стовпці А, мітка часу відображалася у стовпці В (як показано нижче):
Хоча ви можете легко вставити мітку часу, використовуючи формулу нижче:
= IF (A2 "", IF (B2 "", B2, NOW ()), "")
Проблема з наведеною вище формулою полягає в тому, що вона оновлюватиме всі часові позначки, як тільки будуть внесені будь -які зміни на аркуші або якщо аркуш буде знову відкритий (оскільки формула NOW є мінливою)
Щоб обійти цю проблему, можна скористатися методом кругового посилання. Використовуйте ту саму формулу, але включіть ітераційний розрахунок.
Існують також деякі інші випадки, коли бажана можливість використання кругового посилання (ви можете знайти один приклад тут).
Примітка: Хоча можуть бути деякі випадки, коли ви можете використовувати кругове посилання, я вважаю за краще уникати його використання. Циркулярні посилання також можуть негативно вплинути на продуктивність вашої робочої книги та уповільнити її роботу. У рідкісних випадках, коли це потрібно, я завжди вважаю за краще використовувати коди VBA для виконання роботи.Сподіваюся, вам цей підручник був корисним!
Інші підручники Excel, які вам можуть стати в нагоді:
- #REF! Помилка в Excel; Як виправити помилку посилання!
- Обробка помилок Excel VBA
- Використовуйте IFERROR з VLOOKUP, щоб позбутися #помилок
- Як звернутись до іншого аркуша чи робочої книги в Excel (з прикладами)
- Абсолютні, відносні та змішані посилання на комірки в Excel
