Зазвичай, коли ви створюєте циклічне посилання, навіть випадково, Excel зазвичай попереджає вас про це, але це трапляється не завжди. Тому важливо знати, як їх виявляти.
У цьому матеріалі я покажу вам, як можна їх виявити та виправити.
Отже, розпочнемо!
Що таке циклічне посилання?
Усе дуже просто: ви вводите формулу в комірку, де використовується та ж сама комірка. Можливо, це може бути важко зрозуміти за визначенням, тому давайте розглянемо приклад.
У клітинці A6 можна вказати наступне:
=СУМ(A1:A6)
Excel негайно сповістить вас про те, що ви формуєте циклічне посилання.
Якщо говорити простими словами, то при виклику функції в такий спосіб, ви фактично утворюєте цикл.
Яким чином знаходити подібні посилання?
Чому варто їх шукати, якщо ви їх не створювали? Насправді, ви могли випадково згенерувати таке посилання. Хоча Excel зазвичай сповіщає про створення циклічного посилання, це трапляється не завжди.
Отже, варто уважно переглянути вашу таблицю, особливо коли ви стикаєтеся з помилками, які не піддаються поясненню.
Інструкція для перевірки в кілька етапів:
- Обирайте “Формули” -> “Перевірка помилок”;
- Наступний пункт – “Циклічні гіперпосилання”;
- Якщо ви натрапите на будь-який елемент, просто натисніть на нього.
Якщо ви помітили якийсь елемент і натиснули на нього – Excel його виділить. Потім ви можете внести зміни або видалити його. Якщо ж нічого не виявили — вітаю, у вашій таблиці немає циклічних посилань.
Ключові аспекти:
- У разі наявності посилання на іншому аркуші, ви не зможете переглянути адрес осередку;
- Якщо Excel сигналізував про наявність циклічного посилання, але ви закрили це попередження, наступні сповіщення більше не з’являться;
- Коли ви відкриваєте файл, що містить циклічне посилання, Excel негайно надасть вам попередження.
Яким чином позбутися?
Таким чином, ми освоїли методи пошуку подібних посилань, але яким чином їх можна видалити?
Є два способи вирішення: або позбутися всього осередку разом з посиланням, або переробити формулу таким чином, щоб уникнути циклічності.
Однак існують і непрості ситуації, коли виникає чисельний обіг циклічних посилань, і їх зміна може зайняти досить багато часу.
Ось зразок значного кола подібних посилань:
Безумовно, це доволі простий випадок, однак він чудово ілюструє основну ідею. Насправді ж ви можете потрапити в ситуацію, коли виявити ці посилання виявляється вкрай важко.
Які кроки варто вжити в подібних обставинах?
Для розв’язання таких проблем існує функція «Впливають комірки», що дозволяє виявити циклічні зв’язки.
Інструкція поетапного використання:
- Обберіть осередок з циклічним посиланням і натисніть на кнопку «Формули»;
- Потім виберіть опцію «Впливають осередки».
Готово! Ви помітите сині лінії, що вкажуть, які дані надходять і з яких джерел.
Ітеративні розрахунки
Чому Excel нас сповіщає? Тому що програма усвідомлює, що безкінечний цикл є негативним явищем. Принаймні, це відбувається тому, що якщо Excel не буде контролювати виконання функції, ваш комп’ютер витрачатиме всі свої ресурси на безкінечне повторення однієї й тієї ж функції.
Проте іноді справді потрібно створювати циклічні посилання. Вам лише слід позначити кількість виконань функції, після якої вона зупиниться (аби уникнути безкінечного виконання).
Це називається «Ітеративні розрахунки».
Яким чином їх можна налаштувати?
Інструкція поетапно:
1. Оберіть вкладку “Файл” і перейдіть до “Параметри”.
2. Потім знайдіть розділ «Формули».
- Оберіть опцію «Увімкнути ітеративні розрахунки» та задайтесь необхідною кількістю ітерацій;
Виконано!
Вважаю, що це всім зрозуміло, але все ж поясню. Цей параметр вказує, скільки разів має бути виконана функція, що виконується в циклі.
Проте пам’ятайте, що чим вищим буде зазначене вами число, тим більше можливостей ваш комп’ютер витрачатиме на виконання обчислень за такими посиланнями в Excel. Також врахуйте, що при активованій опції «Ітеративні обчислення» програма перестане повідомляти вас про наявність циклічних посилань.
Яка мета циклічних посилань
Зазвичай люди без особливого обдумування формують подібні посилання.
Проте це трапляється не завжди. Існують особи, які навмисно створюють подібні посилання.
Чому? Розгляньмо це на прикладі.
Наприклад, ви розробляєте функцію, яка фіксує час у клітинці щоразу, коли вносите зміни в цю клітинку:
Ось так він виглядає:
=якщо(A2″”;якщо(B2″”;B2;ТЕПЕРІШНЄ());””) Ось і вся суть. Звичайно, є й інші випадки, де свідомо можна використовувати циклічні посилання.
Це може бути корисним застосуванням, яке ми обговорювали. Проте, я б порадив уникати циклічних посилань, оскільки важко визначити, скільки ресурсів ваш комп’ютер витратить на їх обробку. Ви можете це не врахувати, і в результаті Excel може «перевантажити» ваш комп’ютер, призводячи до його зависання.
Сподіваюся, що ця інформація була для вас корисною!
