backtotop

Categories: Формулы

Изначально давайте разберемся, что собой представляют циклические ссылки в Excel. Как их можно найти на листе редактора? Чем они плохи и чем хороши?

Циклические ссылки в Excel

Теория

Далее будем именовать циклические ссылки Ц/С для удобства. А теперь рассмотрим основное понятие о Ц/С, как они возникают, и как их обнаружить в книге.
Итак, всё дело в том, что данный вид ссылок создается, в том случае, если формула в любой из ячеек,  ссылается непосредственно на себя через другие ячейки.

Циклические ссылки в Excel

К примеру, в формуле участвует результат вычисления этой формулы или ячейка =A3=B3, =B3=C3, =C3=A3. В результате A3 ссылается на A3. То есть если образуется циклическая зависимость. Пример на картинке показан для режима Формулы — Показать формулы

Как показывает практика присутствует более сложная зависимость, когда итоги одной формулы, которые могут быть весьма сложными, имеют прямое влияние на итоги другой формулы, влияющей на итоги вычисления первой. В этом случае и возникает циклические ссылки в Excel.

Обозначение Ц/С в Excel
Появление Ц/С довольно просто распознать. Во время их появления или присутствия в уже созданном файле Excel тотчас же возникает окно с сообщением (как на первой картинке). Если нажать на кнопку ОК, то предупреждение будет скрыто, а в ячейке, которая содержит Ц/С отобразится значение 0.
Предупреждение, возникает при изначальном появлении Ц/С, либо же открытии книги, которая её содержит (частая проблема от многих пользователей). Если вы принимаете предупреждение программы, то далее при наличии Ц/С, оно может и не появляться.

Способы найти Ц/С
Ц/С в редакторе eсxel могут быть созданы как преднамеренно, для того, что бы решить те или иные задачи табличных финансовых моделей (об этом далее), так и возникнуть случайно, как техническая ошибка в формулах их построения.
Способов нахождения Ц/С существует несколько:

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

Но можно и воспользоваться инструментом для поиска ошибок. Просто нажимаем вкладку «Формулы» и в разделе – «Поиск ошибок» и далее в выпадающем списке выбираете «Циклические ссылки». Программа отобразит ячейку с первой Ц/С. А после её коррекции или удаления, отобразиться следующая. Вы можете любым методом для их определение, который будет вам наиболее удобен.

Как можно полезно использовать Циклические ссылки в Excel?

Иногда в моделировании, например при проработки модели бизнес плана,  когда нам нужно рассчитать уровень показателя зависящий напрямую от прибыли. К примеру, вы выплачиваете процент от прибыли своим сотрудникам. Соответственно прибыль зависит от затрат на премию и наоборот. Что делать?

Включить итеративные вычисления!

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

Конечно же в Excel есть возможность включить Файл (круглая кнопка в левом верхнем углу) — Параметры — Формулы. Поставить галочку «Включить итеративные вычисления».

Циклические ссылки в Excel

Так же необходимо установить предельное число итераций и относительную погрешность, ниже

Не рекомендую ставить большое кол-во вычислений — система может основательно подвиснуть.

  .

Comments

Там нет комментариев

Leave a Comment:

Ваш e-mail не будет опубликован. Обязательные поля помечены *

два × 1 =