Как найти ячейку циклической ссылки в Excel

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

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

Как найти круговые ссылочные ячейки

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

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

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

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

Что такое круговые ссылочные ячейки и почему они представляют собой проблему?

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

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

Пример круговой ссылочной ячейки

Существует множество различных способов появления круговых ссылочных ячеек. Вот элементарный пример, иллюстрирующий, как и почему возникает эта проблема:

1. Представьте, что вы хотите использовать Excel, чтобы составить список чисел и сложить их. Для начала вы можете записать список чисел в ячейках от A1 до A10.
   
2. Затем в ячейке A11 вы можете отобразить общую сумму. Для этого вы обычно используете формулу « =SUM(A1:A10)», чтобы добавить все в строке от ячейки A1 до ячейки A10.
   
3. Однако вы случайно допустили опечатку и ввели формулу типа « =SUM(A1:A11)».
   

Круговая ошибка возникает потому, что ячейке A11 присвоена формула, которая включает в себя саму себя. По сути, его просят добавить число из его собственной ячейки, которая на самом деле еще не существует. Это нарушит расчет «СУММ» и приведет к появлению сообщения об ошибке.

Как исправить круговые ссылочные ячейки

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

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

Удаление и перезапись формулы

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

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

Использование функций трассировки Excel

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

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

Как следует из названия, «Отслеживать прецеденты» покажет вам все ячейки, которые по сути предоставляют данные для формулы в выбранной вами ячейке. Между тем, «Зависимые от трассировки» позже в цепочке вычислений будут показывать ячейки, которые ссылаются на выбранную вами ячейку.

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

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

Часто задаваемые вопросы

Есть ли смысл использовать круговые ссылочные ячейки?

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

Каковы различные типы циклических ссылок?

Существует два основных типа ячеек циклической ссылки: прямая и косвенная. Прямая циклическая ссылка — это ссылка, в которой формула в ячейке напрямую ссылается на ту же ячейку. Например, если вы поместите в ячейку A10 формулу, которая каким-то образом ссылается на ячейку A10, это приведет к ошибке. Косвенные ссылки возникают, когда цепочка формул в разных ячейках на каком-то этапе ссылается сама на себя.

Решение проблем с циклическими ссылками в Excel

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

Были ли у вас проблемы с круглыми эталонными ячейками? Знаете ли вы еще какие-нибудь полезные советы по исправлению этих ссылок при их появлении? Сообщите всем об этом в разделе комментариев.