Как найти и удалить круговые ссылки в Excel

Как найти и удалить круговые ссылки в Excel

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





Что такое циркулярные ссылки в Excel?

Циклическая ссылка - это термин, используемый для формулы, которая обращается к ячейке более одного раза в строке своих вычислений. Из-за этого вычисления займут много времени, чтобы вычислить числа, и вполне вероятно, что вычисления будут давать неправильный ответ в большинстве случаев.



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





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

Понимание циркулярных ссылок

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



Прямые циркулярные ссылки

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

  1. В камерах A1 , A2, а также A3 введите числа 100, 200 и 300.
  2. Выбрать ячейку A4 и в строке формул введите следующую формулу: =SUM(A1+A2+A3+A4) Это простая функция СУММ, которая суммирует ячейки A1, A2, A3 и A4.
  3. После того, как вы ввели формулу, нажмите Входить . Появится сообщение об ошибке.
  4. В появившемся сообщении об ошибке Excel предупреждает, что в этой электронной таблице существует циклическая ссылка. Нажмите Ok .

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



В результате этой циклической ссылки Excel вернет 0 для формулы. Вы можете не знать ответ на эту формулу, но это не 0.

Связанный: Функции поиска в Excel для эффективного поиска в таблицах



Косвенные циркулярные ссылки

Косвенная циклическая ссылка - это когда ячейка ссылается на себя через другие ячейки.

  1. В камере A1 введите формулу ниже: =D1 Это установит значение A1 на значение D1. Как бы то ни было.
  2. Введите в ячейки числа 100 и 200. B1 а также C1 , соответственно.
  3. Выбрать ячейку D1 и в строке формул введите следующую формулу: =SUM(A1+B1 + C1) Это еще одна функция СУММ, которая суммирует значения в ячейках A1, B1 и C1 и возвращает результат в D1.
  4. Нажмите Входить . Появится сообщение об ошибке.
  5. В сообщении об ошибке щелкните Ok .

Как и раньше, Excel возвращает 0. Однако, в отличие от предыдущего примера, ячейка D4 не ссылается на себя напрямую. Вместо этого он суммирует A1 с набором значений. Что такое А1? Это D4. Так что же такое D4? Это A1 с массивом значений.

Была сформирована круговая ссылка.

как получить доступ к флешке в Windows 10

Связанный: Как копировать формулы в Microsoft Excel

Поиск и удаление круговых ссылок в Excel

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

Прежде чем приступить к поиску циклических ссылок, давайте добавим их в вашу электронную таблицу:

  1. В камерах A1 , A2 , а также A3 , введите 1, 2 и 3.
  2. Выбрать ячейку A4 .
  3. В строке формул введите формулу ниже и нажмите Входить : =SUM(A1+A2+A3+A4) Как и в первом примере, это также прямая круговая ссылка, где A4 относится к самому себе.
  4. В сообщении об ошибке щелкните Ok .

Excel возвращает 0, потому что формула в A4 является прямой циклической ссылкой. Создадим и косвенный:

  1. В камере C1 , тип двадцать .
  2. Введите формулу ниже для ячейки E4 : =G1+3
  3. Введите формулу ниже для ячейки G1 : =C1+2
  4. Наконец, выберите ячейку C1 в строке формул и замените двадцать со строкой ниже: =E4+1

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

Связанный: Как подсчитать уникальные значения в Excel

Давайте посмотрим, как можно использовать инструменты Excel для поиска циклических ссылок в вашей электронной таблице. Имейте в виду, что ваша электронная таблица содержит четыре циклические ссылки, а именно A4 (прямые), C1, E4 и G1 (косвенные).

  1. В меню ленты перейдите к Формула таб.
  2. в Формула аудита раздел, нажмите кнопку со стрелкой рядом с Проверка ошибок .
  3. В меню наведите указатель мыши на Циркулярные ссылки . Это откроет список циклических ссылок.
  4. Щелкните ячейку в списке. Вы попадете в ячейку с круговой ссылкой.
  5. Вы заметите, что Excel показывает только одну из четырех ссылок. Это потому, что Excel обрабатывает эти цепочки по очереди.
  6. После исправления первой круговой ссылки можно переходить к следующей.
  7. На следующем этапе исправить круговую ссылку .
  8. Возвращаться Проверка ошибок и нажмите Циркулярные ссылки .
  9. Из списка выберите следующую круговую ссылку и приступите к ее исправлению.

Отслеживание отношений между ячейками

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

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

Разорвать круг

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

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

Делиться Делиться Твитнуть Эл. адрес Как использовать операторы IF в Microsoft Excel

Независимо от того, являетесь ли вы опытным экспертом или новичком в работе с электронными таблицами, вам стоит ознакомиться с этим руководством по операторам ЕСЛИ в Excel.

Читать далее Похожие темы
  • Продуктивность
  • Таблица
  • Майкрософт Эксель
  • Microsoft Office 365
  • Советы по Microsoft Office
Об авторе Амир М. Интеллект(Опубликовано 39 статей)

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

Ещё от Amir M. Bohlooli

Подписывайтесь на нашу новостную рассылку

Подпишитесь на нашу рассылку, чтобы получать технические советы, обзоры, бесплатные электронные книги и эксклюзивные предложения!

Нажмите здесь, чтобы подписаться