Циркулярные ссылки могут быть довольно сложными, поэтому важно знать, как их найти. В Excel есть встроенный механизм, который может обнаруживать циклические ссылки и предотвращать бесконечный цикл вычислений. Вам просто нужно знать, как включить и использовать эту функцию в своих интересах.
В этой статье мы покажем вам, как найти циклические ссылки в Excel. Кроме того, мы объясним, что такое циклические ссылки в первую очередь и почему их следует избегать.
Как найти круговую ссылку в Microsoft Excel
Когда вы пытаетесь ввести формулу в книгу Excel и сталкиваетесь с какой-либо проблемой, возможно, вы имеете дело с циклической ссылкой. Это происходит, когда формула пытается использовать собственное значение для вычислений. На этом этапе Excel отправит вам предупреждающее сообщение:
«Есть одна или несколько циклических ссылок, в которых формула прямо или косвенно ссылается на свою ячейку. Это может привести к неправильному расчету ».
Поскольку бесконечный цикл может продолжаться бесконечно или завершиться до того, как будет достигнут правильный ответ, лучше держаться подальше от циклических ссылок в Excel. Более того, циклические ссылки также могут значительно замедлить весь процесс вычислений в ваших книгах. Однако в большинстве случаев самая большая проблема с циклическими ссылками - это их идентификация.
Есть три типа циклических ссылок: непреднамеренные, преднамеренные и скрытые. Большинство циклических ссылок являются непреднамеренными, поскольку для создания преднамеренных циклических ссылок потребуется кто-то, имеющий опыт работы с Excel. Наконец, у нас есть скрытые циклические ссылки. Хотя случайные циклические ссылки легко найти, Excel не всегда может обнаружить скрытые циклические ссылки, поэтому вам придется использовать другие методы.
Когда появится предупреждающее сообщение, вы можете нажать кнопку «ОК» или «Справка». Последний только даст вам больше информации о циклических ссылках, не указывая, где они находятся в вашей книге. С другой стороны, если вы выберете «ОК» или просто отключите сообщение, вы найдете либо последнее вычисленное значение, либо ноль в последней ячейке. Также важно отметить, что это уведомление не всегда появляется. Например, если вы намеренно или непреднамеренно продолжите создавать больше циклических ссылок, Excel перестанет уведомлять вас об этом.
В очень редких случаях формула, содержащая одну круговую ссылку, может быть завершена до того, как будет запущен механизм самоисчисления. В этом случае в результате будет отображаться только последнее успешное значение. Другими словами, циклическая ссылка может привести к зависанию системы. Вот почему его определение - самый важный шаг.
Чтобы исправить ошибку циклической ссылки в Excel, вам нужно сначала найти ее. Чтобы узнать, как это сделать, выполните следующие действия:
- Отключите предупреждающее сообщение, отображаемое в Excel.
- Перейдите на вкладку «Формулы» в верхнем меню.
- Перейдите на вкладку «Проверка ошибок» и щелкните по ней.
- В раскрывающемся меню нажмите «Циркулярные ссылки». Здесь будут обнаружены любые циклические ссылки.
- Щелкните значение во всплывающем списке, и вы попадете прямо к этой циклической ссылке.
Когда вы щелкаете ячейку, содержащую круговую ссылку, она также отображается в адресной строке внизу листа.
Если вам нужна дополнительная помощь с циклической ссылкой, есть два инструмента, которые могут вам помочь - трассировка прецедентов и трассировка зависимостей. Первый инструмент, отслеживание прецедентов, отображает синие линии в книге Excel, которые показывают, какие ячейки влияют на ячейку, на которую вы щелкнули. Зависимые от трассировки, с другой стороны, делают наоборот. Они обводят линии, чтобы показать вам, на какие ячейки влияет ячейка, на которую вы щелкнули. Эти две функции помогут вам найти циклические ссылки, которые Excel не может обнаружить. Имейте в виду, что эти индикаторы не показывают точное местонахождение круговой ссылки, а только подсказку, которая поможет вам быстрее ее найти.
Если вы хотите включить прецеденты трассировки и трассировку иждивенцев, выполните следующие действия:
- Перейдите на вкладку «Формулы» в верхней части таблицы.
- Перейдите в категорию «Формульный аудит».
- Выберите «Отслеживать прецеденты» или «Отслеживать зависимые».
Вы можете выбрать только один за раз. Более быстрый способ сделать это - использовать следующие сочетания клавиш: «Alt + T U T» для прецедентов трассировки или «Alt + T U D» для зависимых трассировок.
Некоторые пользователи 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 раньше? Как ты это нашел? Дайте нам знать в комментариях ниже.
