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

8 апреля 2022 г.

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

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

Циклическая ссылка в Excel — это указание на то, что формула ссылается на собственное или другое значение ячейки более одного раза во время вычислений. В Excel ссылки возникают, когда формула в одной ячейке использует информацию из другой ячейки для выполнения вычислений. Когда ячейки ссылаются сами на себя или другая ячейка ссылается на исходную ячейку, Excel не может завершить формулы, поскольку им нужен результат, зависящий от исходной ячейки. Например, формула =B1+B2+B3 не работает, так как находится в ячейке B3 и не может вычислить сама себя. Любые другие формулы или расчеты, относящиеся к B3, создадут циклическую ссылку.

Циклические ссылки создают бесконечные циклы, которые могут снизить скорость вашей электронной таблицы и привести к тому, что ваши формулы будут вычисляться неправильно, поскольку они каждый раз меняют значение ваших ячеек. Они могут возникать как прямо, так и косвенно. Прямая ссылка возникает, когда вы переходите к ячейке и вводите ее значение, например «=C4» для ячейки C4, или когда вы выбираете диапазон ячеек и включаете исходную ячейку, например «=СУММ(C1:C4). ” Косвенные ссылки возникают, когда две ячейки нацелены друг на друга, например, при вводе «=E3» в ячейку A3, когда ячейка E3 имеет ссылку из A3.

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

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

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

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

2. Выберите «Проверка ошибок».

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

3. Щелкните ячейку в разделе «Циклические ссылки».

После того, как вы выберете «Циклические ссылки» в раскрывающемся меню, появится строка состояния, сообщающая, обнаружена ли циклическая ссылка в вашей книге. Если он есть на вашем рабочем листе, в строке состояния отображается последняя ячейка, в которой это произошло. Например, если в ячейке C4 есть циклическая ссылка, в строке состояния будет указано Циклическая ссылка: C4. Вы можете повторить эти шаги, чтобы увидеть, есть ли какие-либо другие циклические ссылки.

4. Исправьте циклические ссылки

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

Различные типы циклических ссылок в Excel

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

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

• Предназначено: опытные пользователи Excel могут использовать предполагаемые циклические ссылки для выполнения итерационных или повторяющихся вычислений на своих листах.

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

Когда важно использовать циклические ссылки в Excel?

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

Советы по поиску и разрешению циклических ссылок в Excel

Вот несколько советов, которые помогут вам найти и разрешить циклические ссылки в Excel:

• Используйте прецеденты трассировки. Инструмент отслеживания прецедентов в Excel, к которому вы можете получить доступ, нажав кнопку «Отслеживание прецедентов» в разделе «Аудит формул» на вкладке «Формула», может помочь вам выяснить, почему циклическая ссылка возникла в вашей электронной таблице, показывая вам, какие другие ячейки конкретная ячейка делает ссылки.

• Увеличьте количество итераций. Чтобы круговая ссылка работала в вашей электронной таблице, включите итеративный расчет, щелкнув вкладку «Файл», «Параметры», «Формулы» и флажок «Включить итеративный расчет».

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

Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.