ИНВЕСТИЦИОННЫЙ ПОРТАЛ ВОЛГОГРАДСКОЙ ОБЛАСТИ

Удаление или разрешение циклической ссылки

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

Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить проблему, можно переместить формулу в другую ячейку. Нажмите клавиши CTRL+X , чтобы вырезать формулу, выделите другую ячейку и нажмите клавиши CTRL+V , чтобы вставить ее.

Советы: 

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

• Дополнительные сведения о написании формул см. в статье Общие сведения о формулах в Excel .

Другая распространенная ошибка связана с использованием функций, которые включают ссылки на самих себя, например ячейка F3 может содержать формулу =СУММ(A3:F3). Пример:

Вы также можете попробовать один из описанных ниже способов.

• Если вы только что ввели формулу, начните с этой ячейки и проверьте, ссылаетесь ли вы на саму ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Такие формулы, как =A1+1 (в ячейке A1), также вызывают ошибки циклической ссылки.

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

• org/ListItem»>

  Если найти ошибку не удается, на вкладке Формулы щелкните стрелку рядом с кнопкой Проверка ошибок, выберите пункт Циклические ссылки и щелкните первую ячейку в подменю.

• Проверьте формулу в ячейке. Если вам не удается определить, является ли эта ячейка причиной циклической ссылки, выберите в подменю Циклические ссылки следующую ячейку.

• Продолжайте находить и исправлять циклические ссылки в книге, повторяя действия 1–3, пока из строки состояния не исчезнет сообщение «Циклические ссылки».

Советы    

• org/ListItem»>

• В строке состояния в левом нижнем углу отображается сообщение Циклические ссылки и адрес ячейки с одной из них.

  При наличии циклических ссылок на других листах, кроме активного, в строке состояния выводится сообщение «Циклические ссылки» без адресов ячеек.

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

  Формулы, а затем — Прецеденты трассировки или Трассировка Зависимости.

Предупреждение о циклической ссылке

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

При закрытии сообщения Excel отображает нулевое или последнее вычисляемое значение в ячейке. И теперь вы, вероятно, говорите: «Повесьте, последнее вычисляемое значение?» Да. В некоторых случаях формула может успешно выполниться до того, как она попытается вычислить себя. Например, формула, использующая функцию IF , может работать до тех пор, пока пользователь не введет аргумент (фрагмент данных, необходимый для правильного выполнения формулы), который приведет к вычислению самой формулы. В этом случае Excel сохраняет значение из последнего успешного вычисления.

Если есть подозрение, что циклическая ссылка содержится в ячейке, которая не возвращает значение 0, попробуйте такое решение:

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

• Пользователь создает первый экземпляр циклической ссылки в любой открытой книге.

• Пользователь удаляет все циклические ссылки во всех открытых книгах, после чего создает новую циклическую ссылку.

• Пользователь закрывает все книги, создает новую и вводит в нее формулу с циклической ссылкой.

• Пользователь открывает книгу, содержащую циклическую ссылку.

• org/ListItem»>

  При отсутствии других открытых книг пользователь открывает книгу и создает в ней циклическую ссылку.

Итеративные вычисления

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

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

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

1. Щелкните Файл > Параметры > Формулы. Если вы работаете в Excel для Mac, откройте меню Excel и выберите Настройки > Вычисление.

2. В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac щелкните Использовать итеративное вычисление.

3. В поле

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

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

Итеративное вычисление может иметь три исход:

• Решение сходится, что означает получение надежного конечного результата. Это самый желательный исход.

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

• Решение переключается между двумя значениями. Например, после первой итерации результат равен 1, после следующей итерации — 10, после следующей итерации — 1 и т. д.

К началу страницы

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Совет: Если вы владелец малого бизнеса и хотите получить дополнительные сведения о настройке Microsoft 365, посетите раздел Справка и обучение для малого бизнеса.

Дополнительные сведения

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Сочетания клавиш и горячие клавиши в Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)

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

Как найти и удалить

Главная » Уроки MS Excel

Автор Елизавета КМ На чтение 5 мин Опубликовано 29.12.2020

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

Содержание

1. Что такое циклическая ссылка
2. Визуальный поиск
3. Использование инструментов программы
4. Отключение блокировки и создание циклических ссылок
5. Заключение

Что такое циклическая ссылка

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

Если циклическая ссылка – ошибка, которую пользователь допустил случайно при заполнении таблицы, введении определенных функций, формул, необходимо найти ее и удалить. На этот случай существует несколько эффективных способов. Стоит подробно рассмотреть 2 наиболее простых и проверенных на практике.

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

Окно оповещения о наличии циклических ссылок в таблице

Визуальный поиск

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

1. Когда появится предупреждающее окно, необходимо закрыть его нажатием кнопки «ОК».
2. Программа автоматически обозначит те ячейки, между которыми возникла конфликтная ситуация. Они будет подсвечены специальной стрелкой трассировки.

Обозначение проблемных ячеек стрелкой трассировки

3. Чтобы убрать цикличность, необходимо зайти в обозначенную ячейку и исправить формулу. Для этого необходимо убрать координаты конфликтной клетки из общей формулы.
4. Останется перевести курсор мыши на любую свободную ячейку таблицы, нажать ЛКМ. Циклическая ссылка будет удалена.

Исправленный вариант после удаления циклической ссылки

Использование инструментов программы

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

1. В первую очередь нужно закрыть окно с предупреждением.
2. Перейти на вкладку «Формулы» на основной панели инструментов.
3. Зайти в раздел «Зависимости формул».
4. Найти кнопку «Проверка ошибок». Если окно программы находится в сжатом формате, данная кнопка будет обозначена восклицательным знаком. Рядом с ней должен находиться маленький треугольник, который направлен вниз. Нужно нажать на него, чтобы появился список команд.

Меню для отображения всех циклических ссылок с их координатами по ячейкам

5. Из списка выбрать «Циклические ссылки».
6. Выполнив все описанные выше действия, перед пользователем появится полный список с ячейками, которые содержат циклические ссылки. Для того чтобы понять, где точно находится данная клетка, нужно найти ее в списке, кликнуть по ней левой кнопкой мыши. Программа автоматически перенаправит пользователя в то место, где возник конфликт.
7. Далее необходимо исправить ошибку для каждой проблемной ячейки, как описывалось в первом способе. Когда конфликтные координаты будут удалены из всех формул, которые есть в списке ошибок, необходимо выполнить заключительную проверку. Для этого возле кнопки «Проверка ошибок» нужно открыть список команд. Если пункт «Циклические ссылки» не будет показан как активный – ошибок нет.

Если ошибок нет, пункт поиска циклических ссылок выбрать нельзя

Отключение блокировки и создание циклических ссылок

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

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

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

Важно! Без особой необходимости лучше не изменять максимальное количество итеративных вычислений. Если их будет слишком много, программа будет перегружена, могут появиться сбои с ее работой.

Окно настроек блокиратора циклических ссылок, их допустимого количества в документе

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

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

1. В клетку А1 добавить цифру «2».
2. В ячейку В1 вписать значение «=С1».
3. В клетку С1 добавить формулу «=А1».
4. Останется вернуться в самую первую ячейку, через нее сослаться на клетку В1. После этого цепь из 3 ячеек замкнется.

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

Excel ссылки циклическая ссылка Эксель

Удалить или разрешить циклическую ссылку

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

Формула =D1+D2+D3 не работает, потому что находится в ячейке D3 и пытается вычислить сама себя. Чтобы решить эту проблему, вы можете переместить формулу в другую ячейку. Нажмите Ctrl+X , чтобы вырезать формулу, выберите другую ячейку и нажмите Ctrl+V , чтобы вставить ее.

Советы: 

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

• Кроме того, см. раздел Обзор формул в Excel, чтобы узнать больше о написании формул.

Другой распространенной ошибкой является использование функции, которая включает ссылку на себя; например, ячейка F3 содержит =СУММ(A3:F3). Вот пример:

Вы также можете попробовать один из этих методов:

• Если вы только что ввели формулу, начните с этой ячейки и проверьте, ссылаетесь ли вы на саму ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Такие формулы, как =A1+1 (в ячейке A1), также вызывают ошибки циклических ссылок.

Пока вы ищете, проверьте косвенные ссылки. Они возникают, когда вы помещаете формулу в ячейку A1, и она использует другую формулу в ячейке B1, которая, в свою очередь, ссылается на ячейку A1. Если вас это смущает, представьте, что он делает с Excel.

• Если вы не можете найти ошибку, щелкните вкладку Формулы , щелкните стрелку рядом с Проверка ошибок , выберите Циклические ссылки , а затем щелкните первую ячейку в подменю.

• Просмотрите формулу в ячейке. Если вы не можете определить, является ли ячейка причиной циклической ссылки, щелкните следующую ячейку в подменю Циклические ссылки .

• Продолжайте просматривать и исправлять циклические ссылки в книге, повторяя любой или все шаги с 1 по 3, пока в строке состояния больше не будет отображаться «Циклические ссылки».

Советы     

• Если вы новичок в работе с формулами, см. курс обучения Excel 2016 Essential на LinkedIn Learning.

• org/ListItem»>

  В строке состояния в левом нижнем углу отображается Циклические ссылки и адрес ячейки одной циклической ссылки.

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

• Вы можете перемещаться между ячейками в циклической ссылке, дважды щелкнув стрелку трассировки. Стрелка указывает на ячейку, которая влияет на значение текущей выбранной ячейки. Вы показываете стрелку трассировки, щелкая Формулы , а затем щелкая либо Прецеденты трассировки , либо Зависимые трассировки .

Узнайте о предупреждающем сообщении циклической ссылки

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

При закрытии сообщения Excel отображает либо нулевое значение, либо последнее вычисленное значение в ячейке. И теперь вы, вероятно, скажете: «Подождите, последнее вычисленное значение?» Да. В некоторых случаях формула может успешно работать до того, как попытается вычислить себя. Например, формула, в которой используется функция ЕСЛИ, может работать до тех пор, пока пользователь не введет аргумент (фрагмент данных, необходимый для правильной работы формулы), который заставит формулу вычислить себя. Когда это происходит, Excel сохраняет значение последнего успешного вычисления.

Если вы подозреваете, что у вас есть циклическая ссылка в ячейке, которая не показывает ноль, попробуйте следующее:

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

• org/ListItem»>

  Вы создаете первый экземпляр циклической ссылки в любой открытой книге

• Вы удаляете все циклические ссылки во всех открытых книгах, а затем создаете новую циклическую ссылку

• Вы закрываете все книги, создаете новую книгу, а затем вводите формулу, содержащую циклическую ссылку

• Вы открываете книгу, содержащую циклическую ссылку

• Если другие книги не открыты, вы открываете книгу, а затем создаете циклическую ссылку

Узнать об итеративном расчете

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

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

1. Щелкните Файл > Параметры > Формулы . Если вы используете Excel для Mac, откройте меню Excel и выберите Настройки > Расчет .

2. В разделе Параметры расчета установите флажок Включить итеративный расчет . На Mac нажмите Использовать итеративный расчет.

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

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

Итеративный расчет может иметь три результата:

• Решение сходится, что означает достижение стабильного конечного результата. Это желаемое условие.

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

• Решение переключается между двумя значениями. Например, после первой итерации результат равен 1, после следующей итерации результат равен 10, после следующей итерации результат равен 1 и так далее.

Верх страницы

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Совет:  Если вы владелец малого бизнеса и ищете дополнительную информацию о том, как настроить Microsoft 365, посетите раздел Справка и обучение для малого бизнеса.

См. также

Базовое обучение Excel 2016

Обзор формул в Excel

Как избежать неработающих формул

Найдите и исправьте ошибки в формулах

Сочетания клавиш и функциональные клавиши Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)

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

Автор Shawn McClain Обновлено 12 февраля 2019 г.

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

Понимание ссылок Excel

При использовании Microsoft Excel формула в одной ячейке обычно ссылается на другую ячейку, чтобы использовать данные этой ячейки в вычислениях. Как правило, ячейка обозначается буквой строки и номером столбца, например A5 или B10.

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

Устранить циклическую ссылку в Excel

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

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

1. Откройте электронную таблицу

2. Откройте электронную таблицу Microsoft Excel, содержащую циклическую ссылку.

3. Использование меню формул

4. Щелкните вкладку «Формулы» в меню ленты в верхней части окна Excel. Щелкните маленькую стрелку рядом с кнопкой «Проверка ошибок» в этой области. Наведите указатель мыши на «Циклические ссылки», и появится последняя введенная циклическая ссылка. Нажмите на эту ссылку, чтобы перейти к этой ячейке в электронной таблице.

5. Поиск циклической ссылки

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

7. Отслеживание прецедентов с помощью Excel

8. Нажмите кнопку «Отслеживание прецедентов» в области «Аудит формул» на вкладке «Формула» меню ленты. Это создаст стрелку в электронной таблице от каждой ячейки, упомянутой в указанной вами ячейке, к указанной ячейке. Нажмите кнопку «Отслеживание зависимостей», которая создаст стрелки от указанной вами ячейки к каждой ячейке, которая ссылается на эту указанную ячейку.

9. Следуйте цепочке ссылок

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

11. Найти дополнительные круги по мере необходимости

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

Итеративное вычисление в Excel

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

Чтобы включить итеративный расчет в Excel, щелкните вкладку «Файл», затем «Параметры» и «Формулы». В разделе «Параметры расчета» установите флажок «Включить итеративный расчет». Введите максимальное количество раз, которое Excel должен пересчитывать формулу, в поле «Максимальное количество итераций» и максимальное количество изменений, которое вы позволите, прежде чем Excel потребуется снова пересчитать в поле «Максимальное изменение».

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