Excel works!

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

Теория

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

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

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

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

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

Способов нахождения Ц/С существует несколько:

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

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

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

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

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

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

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

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

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