Как удалить циклические ссылки в Excel, используя 3 простых шага

8 апреля 2022 г.

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

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

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

A1 содержит значение 100, а A2 содержит значение 200. Вы знаете, что ввод формулы «=A1+A2» в A3 вычисляет сумму этих двух значений, чтобы получить значение 300. Вам интересно, что произойдет, если вы введете A3 в эту формулу, поэтому вы введете следующую формулу в A3: “=A1+A2+A3”. Формула не знает, как учитывать A3, потому что это ячейка формулы, поэтому она возвращает ошибку циклической ссылки.

Когда появляется ошибка круговой ссылки?

Вы можете увидеть ошибку циклической ссылки, когда вы:

• Создание первой циклической ссылки в книге

• Создать новую циклическую ссылку после удаления предыдущих экземпляров

• Открытие книги с неразрешенной циклической ссылкой

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

Почему возникают циклические ссылки?

Одной из наиболее распространенных причин возникновения циклической ссылки является ошибка. Например, рассмотрим кого-то, кто хочет вычислить сумму от A1 до A10 в A11. После ввода «=СУММ(» в A11 они могут попытаться выбрать ячейки с A1 по A10, чтобы указать эти значения в качестве слагаемых. Если пользователь случайно включает A11 в свой выбор, формула будет выглядеть как «=СУММ(A1:A11) .” A11 не может вычислить себя в формуле, что приводит к циклической ошибке ссылки.

Циклические ссылки также возникают при использовании формулы, зависящей от собственного значения. Например, рассмотрите таблицу ниже, которая иллюстрирует базовую зарплату и премию сотрудника. Формула в B3 «=СУММ(B1:B2)» указывает на общую сумму денег, которую получает сотрудник. Менеджер решает, что премия работника равна 10% от его общей суммы наличных денег, поэтому он вводит эту формулу в B2: «=B3*.1». Эта формула является примером циклической ссылки, поскольку она зависит от собственной ячейки с помощью B3.

AB1Базовый оклад$50,0002Бонус
3Всего наличных

Как проверить циклические ссылки

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

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

2. Перейдите в группу «Аудит формул» и выберите «Проверка ошибок».

3. Нажмите «Циклические ссылки» в раскрывающемся меню.

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

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

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

1. Проверьте формулу на наличие ссылок на ячейку формулы

Выберите ячейку, содержащую циклическую ссылку. Проверьте формулу на наличие ссылок на ячейку формулы. Например, это циклическая ссылка, если формула в ячейке A8 имеет вид «=СУММ(A1:A8)». Вы можете решить эту проблему, изменив A8 на A7 и нажав «Ввод».

2. Проверьте наличие косвенных ссылок

Если формула не содержит прямых ссылок на ячейки, причиной ошибки могут быть косвенные ссылки. Щелкните ссылки в формуле, чтобы изучить их более подробно. Например, представьте, что ячейка B4 содержит следующую формулу: «=СУММ(B3+5)». Вы можете нажать B3, чтобы просмотреть его формулу. Если B3 ссылается на B4 по формуле типа “=B4+20”, это вызывает циклическую ссылку. Вы можете исправить ошибку, отредактировав формулу B3, чтобы она не ссылалась на B4.

3. Скопируйте формулу в другую ячейку.

Последним шагом для удаления циклических ссылок является копирование формулы в другую ячейку. Например, представьте, что вы хотите сложить значения A2, A3 и A4. Если вы случайно ввели формулу «=СУММ(A2:A4)» в A4, вы можете переместить ее в другую ячейку. Щелкните правой кнопкой мыши ячейку A4, выберите «Вырезать», щелкните правой кнопкой мыши новую ячейку, в которой вы хотите разместить формулу, и выберите «Вставить».

Как разрешить циклические ссылки в Excel

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

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

2. Выберите «Параметры» в меню слева.

3. Выберите вкладку «Формулы».

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

5. Измените поле «Максимальное количество итераций» на 100.

6. Нажмите «ОК» в правом нижнем углу диалогового окна.

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