Как найти циклическую ссылку в Excel
Многие считают, что циклические ссылки в Excel — ошибки, но на самом деле не всегда так. Они могут помочь в решении некоторых задач, поэтому иногда используются намерено. Давайте подробно разберем, что же такое циклическая ссылка, как убрать циклические ссылки в Excel и прочее.
Что есть циклическая ссылка
Циклическая ссылка — это ссылка на ссылающуюся саму на себя ячейку. Это может быть не одна, а целая группа ссылок, которые ссылаются сами на себя, образуя замкнутый круг. В новых версиях приложения циклические ссылки блокируются, а если они есть в открытом вами документе, на экране появится соответствующее сообщение.
Как найти циклическую ссылку
Чаще всего программа находит их сама, и поскольку ссылки данного типа вредны, их необходимо устранить. Что же делать, если программа не смогла их найти сама, то есть, не пометила выражения линиями со стрелками? В таком случае:
- На верхней панели программы нужно перейти в раздел «Формулы». Далее открываем меню «Зависимости формул», а там «Проверка наличия ошибок». Появится меню, где нужно выбрать «Циклические ссылки». Откроется следующее меню, где будут отображены найденные программой адреса элементов, содержащих циклические ссылки.
- Если кликнуть на любой из адресов, выделится соответствующая ячейка на листе.
Устраняем циклические ссылки
После обнаружения ссылки возникает вопрос, каким образом можно удалить циклическую ссылку в Microsoft Excel. Для исправления следует хорошенько просмотреть зависимость между ячейками. Допустим вами была введена какая-либо формула, но в итоге она не захотела работать, а программа выдает ошибку, указывая на наличие циклической ссылки.
Например, эта формула не является работоспособной поскольку находится в ячейке D3 и ссылается на себя же. Ее нужно вырезать из этой ячейки, выделив формулу в строке формул и нажав CTRL+X, после чего вставить в другой ячейке, выделив ее и нажав CTRL+V.
Это лишь простой пример, для более сложных вычислений проверка может затянуться надолго.
Интеративные вычисления
Многие, наверное, слышали такое понятие, как итерация и хотят знать, итеративные вычисления в Excel, что это. Это вычисления, которые продолжают выполняться до тех пор, пока не будет получен результат, соответствующий указанным условиям.
Для включения функции следует открыть параметры программы и в разделе «Формулы» активировать «Включить итеративные вычисления».
Заключение
Теперь вы знаете, что делать, если зациклилось вычисляемое выражение. Иногда нужно применить логику и хорошенько подумать, чтобы устранить циклические ссылки.
Циклические ссылки в 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 и т. д.
Циклическая ссылка. Как обнаружить и удалить циклическую ссылку?
Циклическая ссылка – это последовательность ссылок, при которой формула ссылается на саму себя. Excel не может автоматически подсчитать все открытые книги, если одна из них содержит циклическую ссылку.
Циклическую ссылку можно или удалить, или вычислить значение каждой ячейки, включенной в замкнутую последовательность, используя результаты предыдущих итераций.
1 способ
- В окне открытой книги перейдите к вкладке «Формулы» и в группе «Зависимости формул» раскройте меню кнопки «Проверка наличия ошибок».
- В списке команд наведите курсор на пункт «Циклические ссылки» и выберите в меню адрес ячейки с циклической ошибкой (рис. 4.27).
Рис. 4.27. Кнопка «Проверка наличия ошибок». Пункт «Циклические ссылки». Адрес ячейки с циклической ошибкой