Всё о циклических ссылках в Excel и Google Sheets

Привет!

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

Эта статья — ваше руководство по циклическим ссылкам. Мы разберем:

• Что такое циклические ссылки
• Почему появляются циклические ссылки
• Как найти в Excel: два способа
• Как убрать циклические ссылки
• Намеренные циклы и итеративные вычисления

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

Поехали!

Циклическая ссылка — это формула, которая прямо или косвенно ссылается на свою собственную ячейку.

При прямой ссылке формула в ячейке, например, B13 ссылается на саму себя, то есть на B13. Это самый простой случай, и он обычно вызван простыми опечатками.

Из-за того, что получается замкнутый круг, Excel и Google Sheets не могут выполнить вычисление: во-первых, они не знают, откуда начать; а во-вторых, они ходят по кругу и пересчитывают результаты бесконечно. Чтобы из-за бесконечного пересчета программа не зависала, у нее есть предохранитель — вот то сообщение о циклических ссылках, которое мы показывали чуть выше.

Чаще всего причина банальна, но иногда за ошибкой кроется проблема в логике расчета. Вот основные причины появления циклических ссылок.

Случайная опечатка. Это самая распространенная ситуация всех случаев. Классический пример: хотите просуммировать столбец с A1 по A9 и пишете формулу =СУММ(A1:A10) в ячейке A10. Случайно включили в диапазон суммирования лишнюю ячейку — вот и циклическая ссылка (прямая ссылка ячейки на саму себя).

Логическая ошибка. Иногда циклическая ссылка указывает на то, что сама логика расчетов имеет изъян. Например, считаете премию как процент от чистой прибыли в P&L, но сама чистая прибыль зависит от расходов, в которые попадает и эта премия. Если такой расчет не вынесен в отдельную таблицу, а происходит в P&L — вот и циклическая ссылка. Если же такой расчет сделан намеренно, то нужно использовать итеративные вычисления, чтобы расчет сошелся к сбалансированному результату.

Намеренное использование. Такое часто можно встретить в финансовых моделях. Например, расчет процентов по кредиту, которые зависят от среднего остатка основного долга (тела кредита). При этом тот же кредит используется для покрытия кассовых разрывов. Получается, что кредит берется, чтобы заплатить в т.ч. проценты, и это их увеличивает, что требует еще большего кредита. В реальности такое чаще всего запрещено условиями кредитных договоров, но для финансовых моделей это обычное дело. Для таких случаев в Excel есть специальный режим итеративных вычислений, о котором мы поговорим чуть ниже.

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

1. Перейдите на вкладку ленты «Формулы».
2. Найдите блок «Зависимости формул».
3. Нажмите на стрелку рядом с кнопкой «Проверка ошибок».
4. В выпадающем меню вы увидите пункт «Циклические ссылки». Наведите на него курсор, и Excel покажет перечень ячеек, которые образуют цикл.

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

Недостаток этого метода заключается в том, что Excel показывает только один цикл — первый найденный, который мешает ему произвести вычисления. Если в таблице их несколько, вам предстоит разобраться с ними по-очереди.

Если вы не можете понять, как найденная ячейка создает цикл, вам поможет второй способ — трассировка. Она визуально показывает связи между ячейками с помощью стрелок.

Как работает трассировка?

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

Иногда эти стрелки пропадают, а иногда вам нужны циклы не на том листе, где Excel их подсветил, а на другом, на котором тоже есть циклы. Есть лайфхак, как заставить Excel подсветить цикл, как минимум, на той вкладке, которая вам нужна. Находясь на интересующей вас вкладке, пересчитайте лист (не всю книгу, а только лист) — Excel подсветит цикл синими стрелками и обновит инфо в блоке проверки. Метод работает вне зависимости от того, включены у вас автоматические вычисления или ручные. См. как работает лайфхак в галерее ниже:

Наконец, если вам надоели все эти синие стрелки, то просто нажмите кнопку «Убрать стрелки» в том же блоке «Зависимости формул».

Ок, вы знаете, как в Excel найти циклические ссылки. Давайте их исправим. Способ исправления напрямую зависит от причины их появления.

Если это опечатка, посмотрите на формулу в найденной ячейке. Неверный диапазон в функции СУММ? Ссылка не на ту ячейку? Исправьте адрес ячейки или диапазон в формуле.

Если это логическая ошибка, пересмотрите логику вашей модели, или отчета, или таблицы. Возможно, какой-то показатель нужно считать по-другому: используйте данные предыдущего периода вместо текущего для расчета показателя, вынесите промежуточный результат в отдельную ячейку и вместо сложной зависимости создайте пошаговый расчет — иногда это решает проблему.

Что делать, если вы не можете найти ошибку? Иногда цикл сложный, и разобраться, какую именно ячейку в цикле нужно изменить, чтобы разорвать его с минимальным ущербом, трудно. Попробуйте пойти от обратного: по шагам, временно удаляйте формулу из ячейки цикла и заменяйте её числом; если ошибка исчезает — вы на верном пути. Попробуйте восстановить формулу и проанализировать каждую ячейку, на которую она ссылается.

Циклические ссылки исправляются только вручную или включением итеративных вычислений, о которых поговорим дальше. Специального инструмента для автоматического исправления циклических ссылок нет. Почему? Потому что только вы знаете, намеренно они созданы или нет.

Как мы говорили выше, циклы в финансовом моделировании — это нормально. Почему? В некоторых случаях они позволяют реализовать максимально аргументированную логику расчета показателей, и для таких случаев в Excel и в Google Sheets есть итеративные вычисления.

Что такое итерация? Итерация — это шаг. Вторая итерация — еще такой же шаг. Затем еще такой же шаг и т.д. — повторение одного и того же элементарного действия. В Excel, когда вы включаете режим итеративных вычислений, вы разрешаете просчитывать цикл не бесконечно, а несколько раз подряд, например, 100 раз = 100 итераций. За 1 итерацию происходит вычисление каждой ячейки книги 1 раз.

В небольших моделях за 100 итераций цикл сходится к стабильному результату, который находится в равновесии. Конечно, если этот цикл вообще умеет сходиться к равновесию. В больших моделях для достижения результата может потребоваться 1000 и более итераций. Но, если циклы вообще сходятся к равновесному результату, то 1000 итераций — не такая большая плата. Ниже вы сможете открыть в Google Sheets или скачать в Excel модель, в которой есть намеренные циклы — попробуйте включить итеративные вычисления и пересчитать модель несколько раз до схождения результатов. Поэкспериментируйте. Вы всегда сможете скачать его заново, если что-то пойдет не так.

Как включить итеративные вычисления?

В общем случае вам нужно найти соответствующий раздел «Итеративные вычисления» в параметрах или настройках табличного редактора. В разных программах доступ к таким настройкам выглядит по-разному. См. некоторые примеры на изображениях ниже:

У итеративных вычислений два типовых параметра:

• Предельное число итераций — сколько раз Excel или Google Sheets будут пытаться пересчитать цикл. Обычно 100 достаточно, но иногда нужно и 5000.
• Относительная погрешность — насколько маленьким должно быть изменение между последними двумя результатами пересчета, чтобы Excel или Google Sheets сочли вычисление завершенным. Для большинства задач достаточно 0,001; для «Сладкого Транзита» из наших примеров лучше использовать 10^-6 (или 0,000001).

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

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

Циклические ссылки в Excel — это не критично и не смертельно. Они заставляют лучше понимать и продумывать логику моделей и отчетов, а также проверяют бдительность.

Подводя итог, теперь вы знаете:

• Что такое циклические ссылки
• Как их найти с помощью возможностей Excel
• Как их исправить в зависимости от причины
• Как и зачем использовать итеративные вычисления для циклов

Вы знаете, что делать.

Хотите узнать больше про финансовое моделирование? Это можно сделать на GrossMargin — заглядывайте в тренажерную.