Циклічні посилання в excel

Серед користувачів Excel поширена думка, що циклічне посилання в excel є різновидом помилки, і її потрібно обов'язково позбавлятися.

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

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

Для початку розберемося, що таке циклічні посилання в excel 2010.

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

Наприклад, комірка С4 = Е7, Е7 = С11, С11 = С4. У результаті С4 посилається на С4.

Наочно це виглядає так:

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

Попередження про циклічне посилання

Поява циклічних посилань дуже просто визначити. При їх виникненні чи наявності у вже створеній книзі excel одразу ж з'являється попередження про циклічне посилання, Яке за великим рахунком і описує суть явища.

При натисканні на кнопку ОК повідомлення буде закрито, а в комірці містить циклічне посилання в більшості випадків з'явитися 0.

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

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

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

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

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

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

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

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

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

Знайти циклічне посилання можна також за допомогою інструмента пошуку помилок.

На вкладці Формули в групі Залежності формул виберіть пункт Пошук помилок і в розкривному списку пункт Циклічні посилання.

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

Тепер, після того, як ми з'ясували як знайти та прибрати циклічне посиланняРозглянемо ситуації, коли робити це не потрібно. Тобто коли циклічне посилання Excel приносить нам певну користь.

Використання циклічних посилань

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

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

Після введення всіх формул, у нас з'являється циклічне посилання:

Проте ситуація не безнадійна. Нам достатньо змінити деякі параметри Excel і розрахунок буде здійснено коректно.

Ще одна ситуація коли можуть бути потрібні циклічні посилання – це метод взаємних чи зворотних розподілів непрямих витрат між невиробничими підрозділами.

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

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

Поки що нас цікавить сама можливість таких обчислень.

Ітеративні обчислення

Для того, щоб коректний розрахунок був можливий, ми повинні включити ітеративні обчислення параметрів Excel.

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

Увімкнути ітеративні обчислення можна за допомогою вкладки Файл → Установки → Формули. Встановлюємо прапорець "Включити ітеративні обчислення".

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

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

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

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

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

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

Comments є currently closed.

Циклічне посилання. Як виявити та видалити циклічне посилання?

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

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

1 спосіб

• У вікні відкритої книги перейдіть до вкладки «Формули» та у групі «Залежності формул» розкрийте меню кнопки «Про перевірку наявності помилок».
• У списку команд наведіть курсор на пункт «Циклічні посилання» та виберіть в меню адресу комірки з циклічною помилкою (рис. 4.27).

Мал. 4.27. Кнопка «Перевірка помилок». Пункт "Циклічні посилання". Адреса осередку з циклічною помилкою

2 спосіб

• У вікні відкритої книги перегляньте рядок стану. При циклічній помилці на ній буде відображено напис «Циклічні посилання: адреса комірки» (на активному аркуші) або «Цикличні посилання» (не на поточному аркуші) (рис. 4.28).

Мал. 4.28. Рядок стану з адресою осередку з циклічною помилкою