Как найти циклическую ссылку в 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 специально создают циклические ссылки для выполнения итерационных вычислений. Но, как правило, не рекомендуется включать в листы циклические ссылки.

Дополнительный FAQ

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

Когда вы, наконец, найдете циклическую ссылку, которая вызывает все проблемы в вашей книге 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? Как ты нашел это? Дайте нам знать в комментариях ниже.