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

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

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

Как найти круговую ссылку в Microsoft Excel

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

«Существует одна или несколько циклических ссылок, в которых формула ссылается на собственную ячейку прямо или косвенно. Это может привести к неправильному расчету».

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

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

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

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

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

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

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

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

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

1. Перейдите на вкладку «Формулы» в верхней части таблицы.
   
2. Перейдите в категорию «Аудит формул».
   
3. Выберите «Прецеденты трассировки» или «Зависимые от трассировки».
   

Вы можете выбрать только один за раз. Более быстрый способ сделать это — использовать следующие сочетания клавиш: «Alt + TUT» для прецедентов трассировки или «Alt + TUD» для зависимостей трассировки.

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

Дополнительные часто задаваемые вопросы

Как удалить циклическую ссылку

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

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

1. Перейдите на вкладку «Файл» в верхнем левом углу экрана.

2. Перейдите к разделу «Параметры» в нижней части меню.

3. Выберите «Формулы» на левой боковой панели всплывающего окна.

4. В разделе «Параметры расчета» установите флажок «Включить итеративный расчет» и проверьте, установлен ли он.

5. Снимите флажок, чтобы отключить итерации.

6. Нажмите кнопку «ОК».

Этот метод можно применить к Excel 2010, Excel 2013, Excel 2016 и Excel 2019. Если у вас есть Excel 2007, вы найдете функцию итерационного расчета, когда нажмете кнопку Office и перейдете в «Параметры Excel». Раздел «Область итерации» будет находиться на вкладке «Формулы». Если у вас Excel 2003 или более ранней версии, вам нужно перейти в «Меню», а затем на вкладку «Инструменты». Вкладка «Расчет» будет находиться в разделе «Параметры».

Найдите все циклические ссылки в своей книге Excel

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

Вы когда-нибудь пытались найти циклическую ссылку на листе Excel? Как вы это нашли? Дайте нам знать в разделе комментариев ниже.