Что такое циклическая ссылка в excel как убрать
Перейти к содержимому

Что такое циклическая ссылка в excel как убрать

  • автор:

Циклические ссылки в Microsoft Excel

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

Использование циклических ссылок

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

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

Создание циклической ссылки

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

  1. Выделяем элемент листа A1 и записываем в нем следующее выражение: =A1 Далее жмем на кнопку Enter на клавиатуре. Создание простейшей циклической ссылки в Microsoft Excel
  2. После этого появляется диалоговое окно предупреждения о циклическом выражении. Щелкаем в нем по кнопке «OK».
  3. Таким образом, мы получили цикличную операцию на листе, в которой ячейка ссылается сама на себя.

Ячейка ссылается сама на себя в Microsoft Excel

Немного усложним задачу и создадим циклическое выражение из нескольких ячеек.

  1. В любой элемент листа записываем число. Пусть это будет ячейка A1, а число 5. Число 5 в ячейке в Microsoft Excel
  2. В другую ячейку (B1) записываем выражение: =C1 Ссылка в ячейке в Microsoft Excel
  3. В следующий элемент (C1) производим запись такой формулы: =A1 Одна ячейка ссылается на другую в Microsoft Excel
  4. После этого возвращаемся в ячейку A1, в которой установлено число 5. Ссылаемся в ней на элемент B1: =B1 Жмем на кнопку Enter. Установка ссылки в ячейке в Microsoft Excel
  5. Таким образом, цикл замкнулся, и мы получили классическую циклическую ссылку. После того, как окно предупреждения закрыто, мы видим, что программа пометила цикличную связь синими стрелками на листе, которые называются стрелками трассировки.

Пометка циклической связи в Microsoft Excel

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

Расчет выручки в таблице в Microsoft Excel

  1. Чтобы зациклить формулу в первой строчке, выделяем элемент листа с количеством первого по счету товара (B2). Вместо статического значения (6) вписываем туда формулу, которая будет считать количество товара путем деления общей суммы (D2) на цену (C2): =D2/C2 Щелкаем по кнопке Enter. Вставка циклической ссылки в таблицу в Microsoft Excel
  2. У нас получилась первая циклическая ссылка, взаимосвязь в которой привычно обозначена стрелкой трассировки. Но как видим, результат ошибочен и равен нулю, так как уже было сказано раньше, Excel блокирует выполнения цикличных операций. Циклическая ссылка в таблице в Microsoft Excel
  3. Скопируем выражение во все остальные ячейки столбца с количеством продукции. Для этого устанавливаем курсор в нижний правый угол того элемента, который уже содержит формулу. Курсор преобразуется в крестик, который принято называть маркером заполнения. Зажимаем левую кнопку мыши и тянем этот крестик до конца таблицы вниз. Маркер заполнения в Microsoft Excel
  4. Как видим, выражение было скопировано во все элементы столбца. Но, помечена стрелкой трассировки только одна взаимосвязь. Заметим это на будущее.

Циклические ссылки скопированы в таблице в Microsoft Excel

Поиск циклических ссылок

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

  1. Итак, если при запуске файла Excel у вас открывается информационное окно о том, что он содержит циклическую ссылку, то её желательно отыскать. Для этого перемещаемся во вкладку «Формулы». Жмем на ленте на треугольник, который размещен справа от кнопки «Проверка наличия ошибок», расположенной в блоке инструментов «Зависимости формул». Открывается меню, в котором следует навести курсор на пункт «Циклические ссылки». После этого в следующем меню открывается список адресов элементов листа, в которых программа обнаружила цикличные выражения. Поиск циклических ссылок в Microsoft Excel
  2. При клике на конкретный адрес происходит выделение соответствующей ячейки на листе.

Переход к ячейке с циклической ссылкой в Microsoft Excel

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

Сообщение о циклической ссылке на панели состояния в Microsoft Excel

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

Циклическая ссылка на другом листе в Microsoft Excel

Исправление циклических ссылок

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

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

  1. В нашем случае, несмотря на то, что программа верно указала на одну из ячеек цикла (D6), реальная ошибка кроется в другой ячейке. Выделяем элемент D6, чтобы узнать, из каких ячеек он подтягивает значение. Смотрим на выражение в строке формул. Как видим, значение в этом элементе листа формируется путем умножения содержимого ячеек B6 и C6. Выражение в программе в Microsoft Excel
  2. Переходим к ячейке C6. Выделяем её и смотрим на строку формул. Как видим, это обычное статическое значение (1000), которое не является продуктом вычисления формулы. Поэтому можно с уверенностью сказать, что указанный элемент не содержит ошибки, вызывающей создание циклических операций. Статическое значение в Microsoft Excel
  3. Переходим к следующей ячейке (B6). После выделения в строке формул мы видим, что она содержит вычисляемое выражение (=D6/C6), которое подтягивает данные из других элементов таблицы, в частности, из ячейки D6. Таким образом, ячейка D6 ссылается на данные элемента B6 и наоборот, что вызывает зацикленность. Циклическая ссылка в ячейке таблицы в Microsoft ExcelТут взаимосвязь мы вычислили довольно быстро, но в реальности бывают случаи, когда в процессе вычисления участвует множество ячеек, а не три элемента, как у нас. Тогда поиск может занять довольно много времени, ведь придется изучить каждый элемент цикличности.
  4. Теперь нам нужно понять, в какой именно ячейке (B6 или D6) содержится ошибка. Хотя, формально это даже не ошибка, а просто избыточное использование ссылок, которое приводит к зацикливанию. Во время процесса решения того, какую ячейку следует отредактировать, нужно применять логику. Тут нет четкого алгоритма действий. В каждом конкретном случае эта логика будет своя. Например, если в нашей таблице общая сумма должна вычисляться путем умножения количества фактически проданного товара на его цену, то можно сказать, что ссылка, рассчитывающая количество от общей суммы продажи, тут явно лишняя. Поэтому мы её удаляем и заменяем на статическое значение. Ссылка заменена на значения в Microsoft Excel
  5. Подобную операцию проводим и над всеми другими цикличными выражениями, если они имеются на листе. После того, как абсолютно все циклические ссылки были удалены из книги, сообщение о наличие данной проблемы должно исчезнуть из строки состояния. Кроме того, полностью ли были удалены цикличные выражения, можно узнать, воспользовавшись инструментом проверки наличия ошибок. Переходим во вкладку «Формулы» и жмем уже знакомый нам треугольник справа от кнопки «Проверка наличия ошибок» в группе инструментов «Зависимости формул». Если в запустившемся меню пункт «Циклические ссылки» не будет активен, то, значит, мы удалили все подобные объекты из документа. В обратном случае, нужно будет применить процедуру удаления к элементам, которые находятся в списке, тем же рассматриваемым ранее способом.

Циклических ссылок в книге нет в Microsoft Excel

Разрешение выполнения цикличных операций

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

Блокировка циклических ссылок в Microsoft Excel

  1. Прежде всего, перемещаемся во вкладку «Файл» приложения Excel. Перемещение во вкладку Файл в Microsoft Excel
  2. Далее щелкаем по пункту «Параметры», расположенному в левой части открывшегося окна. Переход в окно параметров в Microsoft Excel
  3. Происходит запуск окна параметров Эксель. Нам нужно перейти во вкладку «Формулы». Переход во вкладку Формулы в Microsoft Excel
  4. Именно в открывшемся окне можно будет произвести разрешение выполнения цикличных операций. Переходим в правый блок этого окна, где находятся непосредственно сами настройки Excel. Мы будем работать с блоком настроек «Параметры вычислений», который расположен в самом верху. Чтобы разрешить применение цикличных выражений, нужно установить галочку около параметра «Включить итеративные вычисления». Кроме того, в этом же блоке можно настроить предельное число итераций и относительную погрешность. По умолчанию их значения равны 100 и 0,001 соответственно. В большинстве случаев данные параметры изменять не нужно, хотя при необходимости или при желании можно внести изменения в указанные поля. Но тут нужно учесть, что слишком большое количество итераций может привести к серьезной нагрузке на программу и систему в целом, особенно если вы работаете с файлом, в котором размещено много цикличных выражений. Итак, устанавливаем галочку около параметра «Включить итеративные вычисления», а затем, чтобы новые настройки вступили в силу, жмем на кнопку «OK», размещенную в нижней части окна параметров Excel. Включение итеративных вычислений в Microsoft Excel
  5. После этого мы автоматически переходим на лист текущей книги. Как видим, в ячейках, в которых располагаются цикличные формулы, теперь значения рассчитываются корректно. Программа не блокирует вычисления в них.

Ячейки с циклическими формулами отображают корректные значения в Microsoft Excel

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

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

Циклические ссылки проектов не разрешены

Циклические ссылки на проекты не разрешены. Например, если ссылается на YourProj , то YourProj (или проект, ссылающийся на YourProj ) не может ссылаться на YourProj , то YourProj (или проект, ссылающийся на YourProj ) не может ссылаться на MyProj . MyProj Эта ошибка имеет следующие причину и решение:

  • Вы попытались добавить ссылку на проект, который уже является частью проекта. Удалите циклическую ссылку или ссылки.

Для получения дополнительной информации выберите необходимый элемент и нажмите клавишу F1 (для Windows) или HELP (для Macintosh).

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Обратная связь

Были ли сведения на этой странице полезными?

Циркулярные ссылки в формулах Excel и способы их обработки

Снимок экрана Excel с круговой ссылкой

Циклическая ссылка происходит в Excel , когда формула содержит ссылку на ячейку для ячейки, содержащей формулу.

Информация в статье относится к версиям Excel 2019, 2016, 2013, 2010, Excel Online и Excel для Mac.

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

Пример циклической ссылки этого типа показан на рисунке ниже, где формула в ячейке C1 содержит ссылку на ячейку C1 в формуле:

= А1 + А2 + А3 + С1

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

Снимок экрана Excel с круговой ссылкой

Циркулярное предупреждение

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

Параметры пользователя

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

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

Непреднамеренные циркулярные ссылки

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

Снимок экрана Excel, показывающий проверку ошибок

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

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

Включение итерационных вычислений

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

Чтобы включить итерационные вычисления:

Выберите « Файл» (или кнопку « Office» в Excel 2007).
Выберите « Параметры», чтобы открыть диалоговое окно «Параметры Excel».
\ На левой панели диалогового окна выберите « Формулы» .
В правой части диалогового окна установите флажок Включить итеративный расчет .

Под флажком доступны варианты:

  • Установить максимальное количество итераций; это количество раз, которое Excel должен пересчитать формулу.
  • Установите максимально допустимое изменение между результатами расчета — чем меньше число, тем точнее результат.

Отображение нулей в затронутых клетках

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

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

Снимок экрана Excel, показывающий настройки проверки ошибок

Подробнее о циркулярном справочном предупреждении

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

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

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

Циклическая ссылка в Excel

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

Циклические ссылки в Excel

  1. Например, формула в ячейке A3 на рисунке ниже ссылается на ячейку A3 (т.е. на саму себя), чего быть не может.

Примечание: Если вы создадите такую циклическую ссылку, Excel вернёт «0».

  1. Ещё один пример. Формула в ячейке С2 на следующем рисунке ссылается на ячейку C1. Циклические ссылки в Excel
  2. Формула в ячейке C3 ссылается на ячейку С2. Циклические ссылки в Excel
  3. Формула в ячейке C4 ссылается на ячейку С3. Циклические ссылки в Excel
  4. Пока все нормально. Теперь измените значение в ячейке C1 на следующую формулу: Циклические ссылки в ExcelПояснение:
    • Ячейка С1 ссылается на ячейку C4.
    • С4 ссылается на ячейку C3.
    • С3 ссылается на ячейку C2.
    • С2 ссылается на ячейку C1.
    • Другими словами, формула в ячейке C1 косвенно ссылается на саму себя, чего быть не должно.

Примечание: Если вы создадите такую циклическую ссылку, Excel вернёт «0».

Циклические ссылки в Excel

  1. Чтобы найти циклические ссылки в документе, на вкладке Formulas (Формулы) кликните по стрелке вниз рядом с иконкой Error Checking (Проверка наличия ошибок) и нажмите Circular References (Циклические ссылки).

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

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