Циклическая ссылка в Excel возникает, когда ячейка, содержащая формулу, каким-то образом зависит от своего результата, создавая цикл, который не может быть разрешен. Если вы хотите остановить эту ошибку, вам нужно найти и удалить эти ссылки, чтобы позволить Excel завершить вычисления. Вот как это сделать.
Что такое круговые ссылки в Excel?
Чтобы более подробно объяснить, что такое круговые ссылки в Excel, давайте представим следующий сценарий. В примере таблицы Excel есть три ячейки со значениями — A2, A3 и A4.
Каждая из этих ячеек имеет значение, которое создается с помощью простого вычисления суммы. A2 складывает значения из A3 и A4, A3 — это сумма A2 и A4, а A4 — сумма A2 и A3. К сожалению, эти три вычисления не могут сосуществовать, не вызывая круговую ссылку.
A2 не может найти сумму A3 и A4, потому что и A3, и A4 включают вычисления, в которые входит A2.
Это происходит потому, что ячейка, содержащая формулу или вычисление, не может ссылаться на саму себя (прямо или косвенно), не вызывая ошибки круговой ссылки. Создается бесконечный цикл вычислений, который не может быть обработан.
Это означает, что вы не можете увидеть искомое значение — Excel застревает и не может продолжить работу.
Для большинства пользователей Excel это непреднамеренный результат, поскольку вы хотите, чтобы вычисления завершились. Чтобы решить эту проблему, необходимо найти ссылки и исправить их.
Поиск круговых ссылок в Excel
Excel предупредит вас, если круговая ссылка вызывает проблему в вашей рабочей книге. Если вы не можете обнаружить ошибку самостоятельно, вы можете воспользоваться меню «Проверка ошибок», чтобы найти все круговые ссылки в вашей рабочей книге.
Использование меню «Проверка ошибок
Чтобы найти все существующие круговые ссылки, откройте рабочую книгу и выберите вкладку «Формулы» на панели ленты. Затем нажмите кнопку «Проверка ошибок».
В выпадающем меню наведите курсор на опцию «Круговые ссылки», чтобы увидеть список всех круговых ссылок в вашей рабочей книге. Чтобы перейти к содержащей ее ячейке, выберите в меню ссылку на ячейку.
Excel выделит ячейку, содержащую круговую ссылку, что позволит вам устранить ее вручную.
Отслеживание круговой ссылки
Если вы имеете дело со сложными формулами, то определить источник круговой ссылки в Excel может быть непросто из-за большого количества прецедентных или зависимых ячеек. Прецедентные ячейки — это ячейки, которые изменяют значение выделенной ячейки, а зависимые ячейки — это ячейки, которым для завершения вычисления требуется значение выделенной ячейки.
Чтобы помочь вам разобраться с ними, вы можете отследить круговую ссылку до источника с помощью инструментов «Отследить прецеденты» и «Отследить зависимые ячейки». Чтобы отследить круговую ссылку в Excel, откройте рабочую книгу и выберите ячейку, содержащую круговую ссылку.
Далее выберите Формулы > Показать формулы. Это переключит вашу рабочую книгу в режим просмотра формул, что позволит вам просмотреть все используемые формулы без выделения отдельных ячеек.
Это не является строго необходимым, поскольку Excel визуально продемонстрирует круговую ссылку с помощью стрелок, но это может помочь вам быстро определить ссылки на проблемные ячейки в большой рабочей книге.
При активном представлении формул и выделенной ячейке, содержащей круговую ссылку, нажмите кнопку «Проследить прецеденты».
Появится стрелка, указывающая на другие ячейки — обратите внимание на след стрелки, чтобы определить источник проблемы.
После этого нажмите кнопку «Трассировка зависимостей» и отметьте каждую ячейку, на которую она указывает.
Как и раньше, запишите ячейки, вызывающие проблему. Excel визуально определит контур круговой ссылки с помощью стрелок и круглых значков.
Для каждой круговой ссылки, которая у вас есть, вам может потребоваться повторить эти шаги, чтобы получить полный ответ для решения проблемы.
Исправление круговых ссылок в Excel
Единственный способ действительно решить проблему круговой ссылки в Excel — это отредактировать вычисление, вызвавшее ее. Если цикл разорван, Excel сможет завершить вычисление. Самый быстрый (и лучший) способ сделать это — заменить формулы, содержащие ссылки на ячейки, соответствующими значениями.
Вот пример. Предположим, что три ячейки в вашей рабочей книге (A2, A3 и A4) содержат простые вычисления, которые ссылаются друг на друга. Чтобы устранить круговую ссылку, вам нужно заменить вычисления в A2 и A4 одинаковыми значениями.
Условия для вычисления в A3 будут выполнены, что позволит Excel вычислить значение без создания цикла. Если вы не хотите использовать прямое значение, вам нужно обратиться к другой ссылке на ячейку — такой, которая не имеет никакой существующей связи с выбранной ячейкой.
Это простой пример, но тот же принцип применим в больших и более сложных рабочих книгах Excel.
