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

Что такое циклическая ссылка

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

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

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

Окно уведомления о наличии циклических ссылок в таблице

Визуальный поиск

Самый простой метод поиска, который подходит при проверке небольших таблиц. Подход:

1. Когда появится окно с предупреждением, закройте его, нажав кнопку OK».
2. Программа автоматически обозначит ячейки, между которыми возникла конфликтная ситуация. Они будут выделены специальной стрелкой слежения.

Обозначение проблемных ячеек стрелками слежения

1. Чтобы убрать цикличность, нужно перейти в указанную ячейку и исправить формулу. Для этого необходимо убрать из общей формулы координаты конфликтной ячейки.
2. Осталось подвести указатель мыши к пустой ячейке в таблице, нажать ЛКМ. Циклическая ссылка будет удалена.

Исправленная версия после удаления циклической ссылки

Использование инструментов программы

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

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

Меню для отображения всех циклических ссылок с координатами их ячеек

1. Выберите «Циркулярные ссылки» из списка».
2. После выполнения всех описанных выше шагов пользователь увидит полный список ячеек, содержащих циклические ссылки. Чтобы понять, где именно находится эта ячейка, нужно найти ее в списке, щелкнуть по ней левой кнопкой мыши. Программа автоматически перенаправит пользователя в место, где произошел конфликт.
3. Далее нужно исправить ошибку для каждой проблемной ячейки, как описано в первом способе. При удалении конфликтующих координат из всех формул, находящихся в списке ошибок, необходимо выполнить окончательную проверку. Для этого рядом с кнопкой «Проверить на ошибки» нужно открыть список команд. Если пункт «Круговые ссылки» не отображается как активный, ошибки нет.

Если ошибок нет, поиск круговых опорных элементов не может быть выбран

Читайте также: 4 способа заменить точку на запятую в Excel

Отключение блокировки и создание циклических ссылок

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

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

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

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

Окно настроек блокировки циклических ссылок, их разрешенное количество в документе

1. Чтобы изменения вступили в силу, необходимо нажать кнопку «ОК». После этого программа больше не будет автоматически блокировать вычисления в ячейках, связанных циклическими ссылками.

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

1. В ячейку A1 добавьте цифру «2».
2. В ячейку B1 введите значение «=C1».
3. В ячейку C1 добавьте формулу «=A1».
4. Осталось вернуться к самой первой ячейке, через которую он ссылается на ячейку B1. После этого цепочка из 3-х ячеек замкнется.

Итеративные вычисления

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

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

Включить итерационные расчеты можно через вкладку «Файл» → раздел «Параметры» → пункт «Формулы». Ставим галочку «Включить итерационные расчеты».

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

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

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

Решение сходится, а это значит, что вы получаете достоверный конечный результат.

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

Решение колеблется между двумя значениями, например, после первой итерации значение равно 1, после второй итерации значение равно 10, после третьей снова 1 и т д

Умышленное использование круговых ссылок

В большинстве случаев было бы ошибкой использовать циклическую ссылку в электронной таблице. Вот почему Excel показывает подсказку: «Попробуйте удалить или изменить эти ссылки или переместить формулы в другие ячейки».

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

Один из таких конкретных случаев, о котором я уже писал, — это получение метки времени в ячейке в ячейке Excel.

Например, допустим, вы хотите создать формулу, чтобы каждая запись делалась в ячейке столбца A, а отметка времени отображалась в столбце B (как показано ниже):

Хотя вы можете легко вставить метку времени, используя следующую формулу:
=ЕСЛИ(A2″»;ЕСЛИ(B2″»;B2, СЕЙЧАС ()), «»)
Проблема с приведенной выше формулой заключается в том, что она будет обновлять все метки времени, как только в электронную таблицу будут внесены изменения или если электронная таблица будет повторно открыта (поскольку формулу СЕЙЧАС можно изменить)

Чтобы обойти эту проблему, вы можете использовать метод круговой ссылки. Используйте ту же формулу, но разрешите итеративный расчет.

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

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