Круговая ссылка в Excel. Как найти и удалить – 2 способа

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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