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

Дядя Влад

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

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

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

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

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

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

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

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

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

  1. В ячейках A1 , A2 и A3 введите числа 100, 200 и 300.
  2. Выделите ячейку A4 и в строке формул введите формулу ниже:
     =SUM(A1+A2+A3+A4)

    Это простая функция СУММ, которая суммирует ячейки A1, A2, A3 и A4.

  3. После того, как вы ввели формулу, нажмите Enter . Появится сообщение об ошибке.
  4. В появившемся сообщении об ошибке Excel предупреждает, что в этой электронной таблице существует циклическая ссылка. Щелкните ОК .

Эта формула создает циклическую ссылку, в которой 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. Нажмите Enter . Появится сообщение об ошибке.
  5. В сообщении об ошибке нажмите ОК .

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

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

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

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

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

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

  1. В ячейках A1 , A2 и A3 введите 1, 2 и 3.
  2. Выделите ячейку A4 .
  3. В строке формул введите формулу ниже и нажмите Enter :
     =SUM(A1+A2+A3+A4)

    Как и в первом примере, это также прямая круговая ссылка, где A4 относится к самому себе.

  4. В сообщении об ошибке нажмите ОК .

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

  1. В ячейке C1 введите 20 .
  2. Введите формулу ниже для ячейки E4 :
     =G1+3
  3. Введите формулу ниже для ячейки G1 :
     =C1+2
  4. Наконец, выберите ячейку C1 в строке формул и замените 20 строкой ниже:
     =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.