Что такое циклические ссылки и как их найти в Excel

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

Циклическая ссылка в таблице

Что представляет собой циклическая ссылка

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

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

Предупреждение о циклической ссылке

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

• некорректная работа формул из-за нарушения зависимости;
• появление ошибок, влияющих на отображение ячеек;
• бесконечный цикл вычисления, который приводит к снижению производительности ПК.

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

Проверка формул в MS Excel

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

1. В первую очередь необходимо прореагировать на предупреждение, которое возникает в Excel при обнаружении взаимозависимых значений. Для этого просто нажимаем OK.
   

   Жмем кнопку ок
2. Далее открываем вкладку «Формулы». Здесь находим «Зависимости формул», а потом ищем инструмент «Проверка наличия ошибок». После этого нажимаем стрелку справа от нее. Затем в выпадающем окне выберите «Циклические ссылки» и найдите адрес ячейки, в которой есть такая ошибка.
   

   Находим циклическую ссылку

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

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

Вычисление формулы

Поиск циклических ссылок: альтернативные способы

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

Для примера, в нашей таблице явно присутствует циклическая ссылка, так как в результате вычисления на месте одного из значений появляется ноль. Такое обычно возникает при работе с числовыми данными. В данном случае переходим во вкладку «Формулы» и нажимаем на кнопку «Показать формулы».

Просматриваем формулы

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

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

Стрелка при обнаружении ссылки

Тем не менее стоит учитывать, что сама программа не всегда отображает линии, указывающие зависимость значений в клетках. В этом случае их можно включить самостоятельно. Для этого переходим во вкладку «Формулы» и разделе «Зависимости формул» находим инструменты «Влияющие ячейки» и «Зависимые ячейки». С их помощью можно определить, какие значения взаимозависимые и цикличные. В дальнейшем, если потребуется отключение линий, достаточно нажать кнопку «Убрать стрелки».

Зависимые и влияющие ячейки

Подводим итоги

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

Видео по теме