Циркулярне посилання та ітераційний розрахунок
Ми часто стикаємося з повідомленням про помилку в Excel про те, що в нашій конкретній формулі є кругове посилання, тому це може призвести до неправильних розрахунків, і Excel просить нас видалити ці посилання. Однак іноді ми навмисно хочемо виконати розрахунок таким чином, найчастіше у фінансових моделях, це ітераційний розрахунок, ітерація.
Кругова довідка
Ви можете завантажити файл, що використовується в завданні, звідси.
Повідомлення про помилку кругового посилання виглядає так:
Це робиться у більшості випадкових випадкових помилок, наприклад, коли ми включаємо клітинку, де ми хочемо обчислити результат, у функції SUM.
У цьому випадку діапазон функції SUM також містить клітинку B23, тому Excel не може обчислити для нас запитаний результат і 0 як результат. Коли ми закінчили редагувати формулу, ми отримали повідомлення про помилку вище, тому Excel "каже", що сталася помилка.
Якщо ви відкриєте файл, який ви не редагували, ви все одно побачите це повідомлення про помилку, коли відкриєте його, але воно не покаже вам, де саме помилка.
Для цього скористайтеся Перевірка формули -> Перевірка помилок -> Команда Кругові посилання під пунктом меню Формули.
Клацнувши, ви побачите клітинки, в яких ви знайдете кругове посилання, а також перейдете до нього, коли ви клацнете на адресу комірки. Якщо помилка знаходиться на іншому аркуші, посилання на клітинку також містить ім’я аркуша.
Ітерація, ітераційний розрахунок
Окрім випадкових помилок, звичайно, можуть бути випадки, коли ми хочемо виконати такий розрахунок навмисно, наприклад у випадку наступної моделі.
Тут ми розраховуємо ціну брутто продукту. Наведені витрати, і залежно від ціни, ми повинні додати прибуток та ПДВ, щоб отримати валову ціну продукту. Стовпець формул це чітко показує як прибуток, так і ПДВ можна отримати з валової ціни С13, тоді як функція SUM у клітинці C13 також містить ці суми. Таким чином, це обчислення також веде до кругового посилання, яке в цьому випадку навіть синя стрілка вказує, між якими клітинками існує ця помилка.
Математично розрахунок буде таким:
1000 + 1500 + 120 + 0,1 * ЦІНА + 0,27 * ЦІНА = ЦІНА
У цьому випадку проблему можна легко розв’язати навіть математично, але у випадку з більш серйозними, більш складними моделями вже не впевнено, що ми можемо її уникнути.
На щастя, Excel може також обробляти ці обчислення, нам просто потрібно дозволити йому виконувати обчислення ітеративним, приблизним способом. Якщо ви зробите це налаштування, обчислення спрацює і не дасть повідомлення про помилку через кругове посилання.
Увімкнути ітераційний розрахунок
На щастя, Excel може також обробляти ці обчислення, нам просто потрібно дозволити йому виконувати обчислення ітеративним, приблизним способом. Якщо ви зробите це налаштування, обчислення спрацює і не дасть повідомлення про помилку через кругове посилання.
Цей параметр можна знайти у меню Файл -> Налаштування на вкладці Формули. Праворуч знаходиться пункт Увімкнути підхід, який, якщо його вибрати, буде обчислюватися Excel таким чином.
У цьому випадку розрахунки в моделі були зроблені без особливих проблем і розраховано валову ціну продукту.
Природно ми повинні бути дуже обережними з цим налаштуванням, тому що якщо залишити його таким чином, циркулярні посилання на основі випадкової помилки також будуть розраховані, і ми також не отримаємо повідомлення про помилку, що б нас попередило. Тому рекомендується, обчисливши кожну таку модель, відновіть Excel до початкового режиму, щоб уникнути проблем деінде.
Якщо ви знайшли його корисним і сподобався, поділіться ним з іншими!:)