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

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

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

Теперь нажав на адрес ячейки с ошибкой, вы перейдете к ней и сможете ее скорректировать.
Способ 2. Ошибка на текущем листе
Этот способ подходит только если ошибка на текущем листе и вам по каким то причинам не хочется использовать первый способ (если честно, таких причин я не придумал, но мало ли. ). Итак при циклической ссылке в ячейке на текущем листе в панели уведомлений Excel укажет в какой именно ячейке ошибка.

Как вы видите, проблема в ячейке B5, туда вы можете перейти как просто прокрутив лист до нужного места, так и нажав F5 и в поле Ссылка прописав адрес ячейки.

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

Смотрите также: а вот и обновлять? ячейке называется циклической ссылкой. значения рассчитываются корректно. операций. Переходим в объекты из документа. просто избыточное использование. Выделяем её и оно расположено, а файла Excel уEnter на элемент и вычисления, что довольно просто, особенноимеется кнопкаЦиклические ссылки представляют собой наш файл))) таблицуGuestC1 Такого быть не Программа не блокирует правый блок этого В обратном случае, ссылок, которое приводит
смотрим на строку на другом, то
Выявление циклических связей
вас открывается информационное.B1 создает дополнительную нагрузку если знать алгоритм«Проверка наличия ошибок» формулу, в которой все также глючит,: если «не обновлять»,косвенно ссылается на может! вычисления в них.
Способ 1: кнопка на ленте
- окна, где находятся нужно будет применить к зацикливанию. Во формул. Как видим, в этом случае окно о том,У нас получилась первая: на систему. поиска. Можно воспользоваться




Способ 2: стрелка трассировки
-
на рисунке ниже операций злоупотреблять не работать с блоком в списке, тем следует отредактировать, нужно1000


Это будет ссылка, зависимостей. Несколько сложнее этой кнопкой. В конечном итоге ссылаетсяФайл удаленviktturЕсли вы создадите ссылается на ячейку стоит. Применять данную
настроек же рассматриваемым ранее применять логику. Тут), которое не является ошибки без адреса. Для этого перемещаемся Но как видим,. расположенная в той определить, нужна ли появившемся меню выбираем сама на себя.- велик размер.: Циклическая ссылка –
такую циклическую ссылку,
Циклические ссылки в Microsoft Excel

A3 возможность следует только«Параметры вычислений» способом. нет четкого алгоритма продуктом вычисления формулы.Урок: Как найти циклические во вкладку результат ошибочен иТаким образом, цикл замкнулся, же ячейке, на данная формула в пункт В некоторых случаях [Модераторы] вычисления в формулах Excel вернёт «0».
(т.е. на саму тогда, когда пользователь
Использование циклических ссылок
, который расположен вВ предшествующей части урока действий. В каждом Поэтому можно с ссылки в Excel«Формулы» равен нулю, так и мы получили которую она ссылается. действительно или это«Циклические ссылки» пользователи осознано применяютGuest
завязаны сами наЧтобы найти циклические ссылки себя), чего быть полностью уверен в самом верху. мы рассказывали, в конкретном случае эта уверенностью сказать, чтоКак уже говорилось выше,. Жмем на ленте как уже было классическую циклическую ссылку.Выделяем элемент листа
Создание циклической ссылки
просто ошибка, а. После перехода по подобный инструмент для: ой, себя (вычисления «по в документе, на
-
не может. её необходимости. НеобоснованноеЧтобы разрешить применение цикличных основном, как бороться
логика будет своя.
указанный элемент не в подавляющем большинстве на треугольник, который



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


таблице общая сумма

итерации (Меню Сервис-Параметры-Вычисления-Итерации-включить
(Формулы) кликните по такую циклическую ссылку, привести к избыточной


циклического характера в в большинстве случаев, выделенные красным нормальные (поставить галку)). Но стрелке вниз рядом Excel вернёт «0». нагрузке на систему. Кроме того, в разговор шел также умножения количества фактически ( избавляться. Поэтому, закономерно,, расположенной в блоке остальные ячейки столбца связь синими стрелкамиДалее жмем на кнопку ссылки в Экселе данной книге. При

-
данная ситуация – формулы внесите. осторожно! – могут с иконкой Ещё один пример. Формула и замедлить вычисления этом же блоке о том, что проданного товара наB6 что после того, инструментов с количеством продукции. на листе, которыеEnter представляют собой ошибочное клике на координаты это просто ошибка
быть ошибки вError Checking в ячейке




Поиск циклических ссылок
на следующем рисунке может неумышленно внести относительную погрешность. По быть полезными и ссылка, рассчитывающая количество видим, что она исправить, чтобы привести котором следует навести правый угол того циклического выражения на окно предупреждения о так, но все-таки листе. невнимательности или поСегодня впервые столкнулся сИщите ошибки вCircular References ссылается на ячейку ошибочное цикличное выражение,
-
умолчанию их значения осознанно использоваться пользователем. от общей суммы содержит вычисляемое выражение формулу к нормальному курсор на пункт элемента, который уже примере таблицы. У циклическом выражении. Щелкаем не всегда. ИногдаПутем изучения результата устанавливаем другим причинами. В такой проблемой как формулах.(Циклические ссылки).C1 которое по умолчанию равны 100 и Например, довольно часто продажи, тут явно ( виду.«Циклические ссылки» содержит формулу. Курсор нас имеется таблица в нем по они применяются вполне зависимость и устраняем


. тут же было 0,001 соответственно. В данный метод применяется лишняя. Поэтому мы=D6/C6Для того, чтобы исправить. После этого в преобразуется в крестик, реализации продуктов питания. кнопке осознанно. Давайте выясним, причину цикличности, если чтобы удалить ошибку, Excel открывается без: «сервис — параметры» командой сайта office-guru.ruФормула в ячейке бы заблокировано программой. большинстве случаев данные

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

не активны, когдаИсточник: http://www.excel-easy.com/examples/circular-reference.html
Исправление циклических ссылок
C3Как мы видим, в параметры изменять не при построении экономических заменяем на статическое из других элементов проследить всю взаимосвязь список адресов элементов маркером заполнения. Зажимаем четырех колонок, в. циклические ссылки, как
После выполнения необходимых действий саму циклическую ссылку. одной из книг открываешь файл (тамПеревела: Ольга Гелихссылается на ячейку подавляющем большинстве случаев, нужно, хотя при моделей. Но беда значение.
-
таблицы, в частности, ячеек. Даже если листа, в которых левую кнопку мыши которых указано наименованиеТаким образом, мы получили их создавать, как снова переходим по Посмотрим, как это существует циклическая ссылка. появляется общее окноАвтор: Антон АндроновС2 циклические ссылки представляют необходимости или при состоит в том,Подобную операцию проводим и из ячейки проверка указала конкретную программа обнаружила цикличные и тянем этот товара, количество проданной цикличную операцию на



Excel сталкивался — тотТаняВопрос жизни иC4 Для этого, прежде поля. Но тут неосознанно вы используете они имеются наD6 в ней самой, адрес происходит выделениеКак видим, выражение было
быть вообще не циклическая ссылка, то такое, остальным объяснятьПытаюсь открыть файлбыла таблица объемнаяС3 саму цикличную взаимосвязь, слишком большое количество по умолчанию все как абсолютно все элемента элементе цепочки зависимости. листе. элементы столбца. Но,

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

Разрешение выполнения цикличных операций
говорится в Помощи, файл уже открыт, другой табл(2), а измените значение в и, наконец, устранить на программу и дабы не привести сообщение о наличие вызывает зацикленность. программа верно указала циклическая ссылка. Сообщение Заметим это на выручки путем умножения записываем число. Пусть же представляет собой зависимостей. об этом факте. но ничего не но нигде его потом связь табл2 ячейке её, внеся соответствующие систему в целом, к излишней перегрузке данной проблемы должноТут взаимосвязь мы вычислили на одну из о данной проблеме будущее. количества на цену. это будет ячейка циклическая ссылка. По

-
В диалоговом окне, сообщающем Так что с добился. Знающие люди, не отображает.



состояния. в реальности бываютD6 содержащего подобное выражение, выше, не во первой строчке, выделяем, а число которое посредством формул ссылок, жмем на такой формулы проблемBrownie их поменьше (10) не открывает Табл1,Пояснение: операции могут быть в котором размещено вопрос принудительного отключенияКроме того, полностью ли случаи, когда в), реальная ошибка кроется располагается в левой всех случаях программа элемент листа с5 в других ячейках кнопку не возникнет. Как: Привет! Просто какой-то и погрешность увеличила,
просто не отображаетЯчейка полезны при вычислениях много цикличных выражений. такой блокировки. Посмотрим, были удалены цикличные процессе вычисления участвует в другой ячейке. части строки состояния, помечает взаимосвязь циклической количеством первого по


Итак, устанавливаем галочку около как это сделать. выражения, можно узнать, множество ячеек, а Выделяем элемент которая находится внизу ссылки с объектами, счету товара (В другую ячейку ( себя. Так же. область на листе? ссылка на самув итоге, в что «эта таблицассылается на ячейку осознанно. Но даже параметраПрежде всего, перемещаемся во воспользовавшись инструментом проверки
не три элемента,D6 окна Excel. Правда, даже если онаB2B1 ею может являтьсяПоявляется стрелка трассировки, котораяЧтобы узнать, в каком себя. Открой Вид строке состояния вместо содержит связи сC4 тогда стоит к«Включить итеративные вычисления» вкладку наличия ошибок. Переходим как у нас., чтобы узнать, из в отличие от имеется на листе.). Вместо статического значения) записываем выражение: ссылка, расположенная в указывает зависимости данных именно диапазоне находится — Панели инструментов «Цикл» стало «Вычислить»,
другими источниками», я
Циклическая ссылка в Excel
. их использованию подходить, а затем, чтобы«Файл» во вкладку Тогда поиск может каких ячеек он
-
предыдущего варианта, на Учитывая тот факт, (=C1 элементе листа, на в одной ячейки такая формула, прежде — Зависимости. Включай/выключай

но попрежнему серый нажимаю «Обновить», потомС4 с осторожностью, правильно
-
новые настройки вступилиприложения Excel.«Формулы» занять довольно много подтягивает значение. Смотрим строке состояния отображаться что в подавляющем




будут адреса не
- большинстве цикличные операции) вписываем туда формулу,C1 ссылается.Нужно отметить, что второй
- кнопку в виде Зависящие ячейки и документа не отбражаются. «невозможно вычислить формулу.
- C3 зная меру в на кнопку«Параметры»
- знакомый нам треугольник изучить каждый элемент строке формул. Как всех элементов, содержащих
- вредны, их следует которая будет считать) производим запись такойНужно отметить, что по способ более визуально белого крестика в
походи по ячейкамЧто значит «Вычислить» ячейка в формуле.
- добавлении подобных ссылок,«OK», расположенному в левой справа от кнопки цикличности. видим, значение в циклические ссылки, если удалить. Но для количество товара путем формулы: умолчанию современные версии нагляден, но в

красном квадрате в таблицы. Стрелки помогут
.
ссылается на результат
Циклические ссылки
которые при использовании, размещенную в нижней
части открывшегося окна.«Проверка наличия ошибок»
Теперь нам нужно понять, этом элементе листа их много, а этого их нужно деления общей суммы=A1
Excel автоматически блокируют то же время диалоговом окне предупреждения, найти циклическую ссылку.Guest формулы, образуя циклическуюссылается на ячейку в массовом количестве части окна параметровПроисходит запуск окна параметровв группе инструментов в какой именно формируется путем умножения только одного из
сначала отыскать. Как (После этого возвращаемся в процесс выполнения цикличной
не всегда даёт тем самым закрывая Удачи.
: поставьте вообще 1 ссылку.»C2 способны замедлить работу Excel.
Эксель. Нам нужно«Зависимости формул» ячейке ( содержимого ячеек них, который появился же это сделать,D2 ячейку операции. Это связано четкую картину цикличности, его.Не нажиматьvikttur
в нажимаю ОК..
системы.После этого мы автоматически перейти во вкладку. Если в запустившемсяB6B6 раньше других.
если выражения не) на цену (
A1 с тем, что в отличие отПереходим во вкладку: Создай новую книгу,:
И НИЧЕГО. НичегоС2Автор: Максим Тютюшев переходим на лист
«Формулы» меню пунктилииК тому же, если помечены линией со
C2, в которой установлено
такие выражения в первого варианта, особенно
«Формулы» скопируй туда свои
Таня, пора показать файл. не отображает. ОЧЕНЬссылается на ячейку
Формула в ячейке, которая текущей книги. Как.«Циклические ссылки»D6C6 вы находитесь в
стрелками? Давайте разберемся): число
подавляющем большинстве ошибочные, в сложных формулах.
данные, циклическую ссылку Подозреваю, он немалентький, СТРАШНО ПОТЕРЯТЬ Табл-1. C1
прямо или косвенно видим, в ячейках,Именно в открывшемся окне
Excel. Циклические ссылки.
не будет активен,) содержится ошибка. Хотя,. книге, содержащей цикличное с этой задачей.=D2/C25 а зацикливание производитКак видим, отыскать циклическую блоке инструментов — не копируй, поэтому сначала сюда:Guest
. ссылается на эту в которых располагаются можно будет произвести то, значит, мы
формально это дажеПереходим к ячейке выражение, не наИтак, если при запускеЩелкаем по кнопке. Ссылаемся в ней постоянный процесс пересчета ссылку в Эксель«Зависимости формул» все должно получиться. Таня: а если неДругими словами, формула в
же самую ячейку, цикличные формулы, теперь разрешение выполнения цикличных удалили все подобные не ошибка, аC6
Как проверить циклические ссылки excel
Вы ввели формулу, но она не работает. Вместо этого вы получили сообщение о «циклической ссылке». Миллионы людей имеют ту же проблему, и это происходит потому, что ваша формула пытается вычислить себя. Вот как это выглядит:

Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить проблему, можно переместить формулу в другую ячейку. Нажмите клавиши CTRL+X , чтобы вырезать формулу, выделите другую ячейку и нажмите клавиши CTRL+V , чтобы вставить ее.
Иногда может потребоваться использовать циклические ссылки, так как они вызывают итерацию функций. В этом случае перейдите к статье Дополнительные сведения о итеративном вычислении.
Кроме того, дополнительные сведения о написании формул см. в статье Общие сведения о формулах в Excel.
Другая распространенная ошибка связана с использованием функций, которые включают ссылки на самих себя, например ячейка F3 может содержать формулу =СУММ(A3:F3). Пример:

Вы также можете попробовать один из описанных ниже способов.
Если вы только что ввели формулу, начните с этой ячейки и проверка, чтобы узнать, ссылаетесь ли вы на саму ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Такие формулы, как =A1+1 (в ячейке A1), также вызывают ошибки циклической ссылки.
Проверьте наличие непрямых ссылок. Они возникают, когда формула, расположенная в ячейке А1, использует другую формулу в ячейке B1, которая снова ссылается на ячейку А1. Если это сбивает с толку вас, представьте, что происходит с Excel.
Если не удается найти ошибку, перейдите на вкладку Формулы , щелкните стрелку рядом с полем Проверка ошибок, наведите указатель на пункт Циклические ссылки, а затем выберите первую ячейку, указанную в подменю.
Проверьте формулу в ячейке. Если не удается определить, является ли ячейка причиной циклической ссылки, выберите следующую ячейку в подменю Циклические ссылки .
Продолжайте находить и исправлять циклические ссылки в книге, повторяя действия 1–3, пока из строки состояния не исчезнет сообщение «Циклические ссылки».
В строке состояния в левом нижнем углу отображается сообщение Циклические ссылки и адрес ячейки с одной из них.
При наличии циклических ссылок на других листах, кроме активного, в строке состояния выводится сообщение «Циклические ссылки» без адресов ячеек.
Вы можете перемещаться между ячейками в циклической ссылке, дважды щелкнув стрелку трассировки. Стрелка указывает ячейку, которая влияет на значение выбранной ячейки. Чтобы отобразить стрелку трассировки, выберите Формулы, а затем выберите Прецеденты трассировки или Зависимые от трассировки.

Предупреждение о циклической ссылке
Когда Excel впервые находит циклическую ссылку, появляется предупреждающее сообщение. Нажмите кнопку ОК или закройте окно сообщения.
При закрытии сообщения Excel отображает нулевое или последнее вычисляемое значение в ячейке. И теперь вы, вероятно, говорите: «Повесьте, последнее вычисляемое значение?» Да. В некоторых случаях формула может успешно выполниться до того, как она попытается вычислить себя. Например, формула, использующая функцию IF , может работать до тех пор, пока пользователь не введет аргумент (фрагмент данных, который формула должна правильно выполнить), который приведет к вычислению самой формулы. В этом случае Excel сохраняет значение из последнего успешного вычисления.
Если есть подозрение, что циклическая ссылка содержится в ячейке, которая не возвращает значение 0, попробуйте такое решение:
Выберите формулу в строке формул и нажмите клавишу ВВОД.
Важно Во многих случаях при создании дополнительных формул с циклическими ссылками предупреждающее сообщение в приложении Excel больше не отображается. Ниже перечислены некоторые, но не все, ситуации, в которых предупреждение появится.
Пользователь создает первый экземпляр циклической ссылки в любой открытой книге.
Пользователь удаляет все циклические ссылки во всех открытых книгах, после чего создает новую циклическую ссылку.
Пользователь закрывает все книги, создает новую и вводит в нее формулу с циклической ссылкой.
Пользователь открывает книгу, содержащую циклическую ссылку.
При отсутствии других открытых книг пользователь открывает книгу и создает в ней циклическую ссылку.
Итеративные вычисления
Иногда может потребоваться использовать циклические ссылки, так как они вызывают итерацию функций— повторять до тех пор, пока не будет выполнено определенное числовое условие. Это может замедлить работу компьютера, поэтому итеративные вычисления обычно отключаются в Excel.
Если вы не знакомы с итеративными вычислениями, вероятно, вы не захотите оставлять активных циклических ссылок. Если же они вам нужны, необходимо решить, сколько раз может повторяться вычисление формулы. Если включить итеративные вычисления, не изменив предельное число итераций и относительную погрешность, приложение Excel прекратит вычисление после 100 итераций либо после того, как изменение всех значений в циклической ссылке с каждой итерацией составит меньше 0,001 (в зависимости от того, какое из этих условий будет выполнено раньше). Тем не менее, вы можете сами задать предельное число итераций и относительную погрешность.
Выберите Параметры > файлов > Формулы. Если вы используете Excel для Mac, выберите меню Excel, а затем выберите Параметры > Вычисление.
В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac выберите Использовать итеративное вычисление.
В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.
В поле Относительная погрешность введите наименьшее значение, до достижения которого следует продолжать итерации. Это наименьшее приращение в любом вычисляемом значении. Чем меньше число, тем точнее результат и тем больше времени потребуется Excel для вычислений.
Итеративное вычисление может иметь три исход:
Решение сходится, что означает получение надежного конечного результата. Это самый желательный исход.
Решение расходится, т. е. при каждой последующей итерации разность между текущим и предыдущим результатами увеличивается.
Решение переключается между двумя значениями. Например, после первой итерации результат равен 1, после следующей итерации — 10, после следующей итерации — 1 и т. д.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Совет: Если вы владелец малого бизнеса и хотите получить дополнительные сведения о настройке Microsoft 365, посетите раздел Справка и обучение для малого бизнеса.
Поиск циклической ссылки в Excel

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

- Чтобы узнать, в каком именно диапазоне находится такая формула, прежде всего, жмем на кнопку в виде белого крестика в красном квадрате в диалоговом окне предупреждения, тем самым закрывая его.
- Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» имеется кнопка «Проверка наличия ошибок». Кликаем на пиктограмму в виде перевернутого треугольника рядом с этой кнопкой. В появившемся меню выбираем пункт «Циклические ссылки». После перехода по этой надписи в виде меню показываются все координаты ссылок циклического характера в данной книге. При клике на координаты конкретной ячейки, она становится активной на листе.
Способ 2: стрелка трассировки
Существует и другой способ определения подобных нежелательных зависимостей.
- В диалоговом окне, сообщающем о наличии циклических ссылок, жмем на кнопку «OK».
- Появляется стрелка трассировки, которая указывает зависимости данных в одной ячейки от другой.
Нужно отметить, что второй способ более визуально нагляден, но в то же время не всегда даёт четкую картину цикличности, в отличие от первого варианта, особенно в сложных формулах.
Как видим, отыскать циклическую ссылку в Эксель довольно просто, особенно если знать алгоритм поиска. Можно воспользоваться одним из двух способов нахождения подобных зависимостей. Несколько сложнее определить, нужна ли данная формула в действительно или это просто ошибка, а также исправить ошибочную ссылку.