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

Работа в электронных таблицах

Возникли проблемы с циклическими ссылками в Excel? Обычно вы можете решить проблему, изменив формулу. Вот как.

Функции Excel позволяют создавать мощные формулы для управления данными.

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

Что происходит, когда формула перестает работать или выдает ошибку? Одним из наиболее распространенных является ошибка круговой ссылки в Excel. Это останавливает вашу формулу от отображения ваших данных.

Если у вас возникли проблемы, вот как найти циклическую ссылку в Excel (и устранить проблему).

Что такое циклическая ссылка в Excel?

Как следует из названия, циклическая ссылка в Excel — это формула, которая ссылается на ячейку, в которой находится формула.

Например, формула может напрямую ссылаться на ячейку, в которой находится формула:

Прямая циклическая ссылка в Excel

Формула также может косвенно ссылаться на себя, ссылаясь на другие ячейки, которые, в свою очередь, ссылаются на ячейку, в которой находится формула:

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

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

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

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

Предупреждение о циклической ссылке в Excel

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

Чтобы найти циклическую ссылку в Excel:

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

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

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

Чтобы проследить циклическую ссылку в Excel:

  1. Выполните действия, описанные в разделе выше, чтобы перейти к ячейке, содержащей циклическую ссылку.
  2. Нажмите на вкладку Формулы.
    Меню формул Excel
  3. Щелкните Показать формулы. Это делает вашу формулу видимой в ячейке, а не результат.
    Показать формулы в Excel
  4. Нажмите «Отследить прецеденты».
    Отслеживание прецедентов в Excel
  5. Стрелка может появиться, а может и не появиться. Если это так, повторяйте шаг 4, пока не перестанут появляться стрелки.
    Прецедент формулы Excel
  6. Выберите Зависимые от трассировки.
    Отслеживание прецедентов в Excel
  7. Стрелка может появиться, а может и не появиться. Если это так, повторяйте шаг 6, пока не перестанут появляться стрелки.
  8. Теперь вы можете увидеть все ячейки, которые ссылаются или на которые ссылается ячейка, содержащая циклическую ссылку.

Прецеденты и иждивенцы в формулах Excel

Как исправить циклическую ссылку в Excel

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

В этом примере мы видим, что формула СУММ в последней ячейке ссылается не только на ячейки выше, но и на себя.

Циклическая ссылка в формуле СУММ в Excel

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

Редактирование формулы Excel

Вот еще один пример — мы видим, что, хотя ни одна ячейка не ссылается непосредственно на себя, каждая ячейка оказывается своим собственным прецедентом.

Прецеденты и иждивенцы в формулах Excel

Другими словами, ячейка 1 берет свое значение из ячейки 2, которая берет свое значение из ячейки 3, которая берет свое значение из ячейки 1. Ячейка 1 косвенно ссылается на себя.

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

Используйте возможности формул Excel

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

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

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *