Удаление или разрешение циклической ссылки
Вы ввели формулу, но она не работает. Вместо этого вы получили сообщение о "цикл такой ссылке". Миллионы людей имеют одинаковые проблемы, и это происходит из-за того, что формула пытается вычислить себя, а функция итеративных вычислений отключена. Вот как он выглядит:
Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить эту проблему, вы можете переместить формулу в другую ячейку. Нажмите CTRL+X, чтобы вырезать формулу, выберите другую ячейку и нажмите CTRL+V, чтобы вжать ее.
Другая распространенная ошибка связана с использованием функций, которые включают ссылки на самих себя, например ячейка F3 может содержать формулу =СУММ(A3:F3). Пример:
Вы также можете попробовать один из описанных ниже способов.
Если вы только что ввели формулу, начните с нее и проверьте, не ссылаетесь ли вы на эту ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Формулы, такие как =A1+1 (в ячейке A1), также приводят к ошибкам цикловой ссылки.
Проверьте наличие непрямых ссылок. Они возникают, когда формула, расположенная в ячейке А1, использует другую формулу в ячейке B1, которая снова ссылается на ячейку А1. Если это сбивает с толку вас, представьте, что происходит с Excel.
Если найти ошибку не удается, на вкладке Формулы щелкните стрелку рядом с кнопкой Проверка ошибок, выберите пункт Циклические ссылки и щелкните первую ячейку в подменю.
Проверьте формулу в ячейке. Если вам не удается определить, является ли эта ячейка причиной циклической ссылки, выберите в подменю Циклические ссылки следующую ячейку.
Продолжайте находить и исправлять циклические ссылки в книге, повторяя действия 1–3, пока из строки состояния не исчезнет сообщение "Циклические ссылки".
В строке состояния в левом нижнем углу отображается сообщение Циклические ссылки и адрес ячейки с одной из них.
При наличии циклических ссылок на других листах, кроме активного, в строке состояния выводится сообщение "Циклические ссылки" без адресов ячеек.
Можно перемещаться между ячейками в циклской ссылке, дважды щелкнув стрелку трассировки. Стрелка указывает на ячейку, которая влияет на значение выбранной ячейки. Чтобы отследить стрелку трассировки, щелкните "Формулы" и выберите "Зависимые зависимые от трассировки" ссылки.
Предупреждение о циклической ссылке
Когда Excel впервые находит циклическую ссылку, появляется предупреждающее сообщение. Нажмите кнопку ОК или закройте окно сообщения.
При закрытии сообщения Excel отображает в ячейке нулевые значения или последнее вычисляемого значения. А теперь вы, вероятно, сказали: "Повесить трубку, последнее вычисляемая величина?" Да. В некоторых случаях формула может выполниться успешно, прежде чем она пытается вычислить себя. Например, формула, использующая функцию ЕСЛИ, может работать, пока пользователь не вложит аргумент (часть данных, которая должна правильно работать) для вычисления формулы. В этом случае Excel сохраняет значение последнего успешного вычисления.
Если есть подозрение, что циклическая ссылка содержится в ячейке, которая не возвращает значение 0, попробуйте такое решение:
Щелкните формулу в строке формулы и нажмите клавишу ВВОД.
Важно Во многих случаях при создании дополнительных формул с циклическими ссылками предупреждающее сообщение в приложении Excel больше не отображается. Ниже перечислены некоторые, но не все, ситуации, в которых предупреждение появится.
Пользователь создает первый экземпляр циклической ссылки в любой открытой книге.
Пользователь удаляет все циклические ссылки во всех открытых книгах, после чего создает новую циклическую ссылку.
Пользователь закрывает все книги, создает новую и вводит в нее формулу с циклической ссылкой.
Пользователь открывает книгу, содержащую циклическую ссылку.
При отсутствии других открытых книг пользователь открывает книгу и создает в ней циклическую ссылку.
Итеративные вычисления
Иногда может потребоваться использовать циклические ссылки, так как они приводят к итерации функций. Повторяйте эти ссылки до тех пор, пока не будет выполнены определенное числовые условия. Это может замедлить компьютер, поэтому итеративные вычисления обычно отключаются в Excel.
Если вы не знакомы с итеративными вычислениями, вероятно, вы не захотите оставлять активных циклических ссылок. Если же они вам нужны, необходимо решить, сколько раз может повторяться вычисление формулы. Если включить итеративные вычисления, не изменив предельное число итераций и относительную погрешность, приложение Excel прекратит вычисление после 100 итераций либо после того, как изменение всех значений в циклической ссылке с каждой итерацией составит меньше 0,001 (в зависимости от того, какое из этих условий будет выполнено раньше). Тем не менее, вы можете сами задать предельное число итераций и относительную погрешность.
Если вы работаете в Excel 2010 или более поздней версии, последовательно выберите элементы Файл > Параметры > Формулы. Если вы работаете в Excel для Mac, откройте меню Excel, выберите пункт Настройки и щелкните элемент Вычисление.
Если вы используете Excel 2007, нажмите Microsoft Office кнопку , выберите "Параметры Excel"и щелкните категорию "Формулы".
В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac щелкните Использовать итеративное вычисление.
В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.
В поле Относительная погрешность введите наименьшее значение, до достижения которого следует продолжать итерации. Это наименьшее приращение в любом вычисляемом значении. Чем меньше число, тем точнее результат и тем больше времени потребуется Excel для вычислений.
Итеративное вычисление может иметь три исход:
Решение сходится, что означает получение надежного конечного результата. Это самый желательный исход.
Решение расходится, т. е. при каждой последующей итерации разность между текущим и предыдущим результатами увеличивается.
Решение переключается между двумя значениями. Например, после первой итерации получается результат 1, после второй — 10, после следующей — 1 и так далее.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Как в Экселе найти и исправить циклические ссылки
Привет. В жизни каждого новичка Excel рано или поздно появляется непонятная циклическая ссылка, которую сложно определить и исправить. В этой статье разберем простой способ, как эту некорректность убрать.
Циклическая ссылка – это ошибка, возникающая, когда формула в ячейке напрямую или косвенно (через другие ячейки) ссылается на саму себя. Такое положение обычно вызывает бесконечные вычисления, которые не дают результата и, порой, приводят к зависанию программы.
Эксель отслеживает появление циклических ссылок и при обнаружении показывает предупреждение: «Некоторые формулы содержат циклические ссылки и напрямую или косвенно ссылаются на самих себя, то есть, на ячейки, в которых находятся. И-за этого формулы могут вычисляться неправильно.». Нельзя назвать нормой такое положение дел, но по молчанию программа не показывает место, в котором возник конфликт ссылок.
Чтобы исправить это, действуйте по алгоритму:
- Нажмите на ленте Формулы – Зависимости формул – Проверка ошибок – Циклические ссылки
- Вы увидите в выпавшем контекстном меню перечень ячеек, в которых есть циклические ссылки. Кликните по любой из них, курсор переместится в то место
- Пришло время поразмыслить. Изучите и тщательно проверьте всю цепочку вычислений в найденной ячейке, проверьте логику работы формул. Найдите ошибку, которая привела к «замыканию» вычислений в этом месте и исправьте её.
В некоторых очень редких случаях циклическая ссылка – нормальное явление, и программа может попытаться достичь той точки в вычислениях, когда ошибка никак себя не проявляет. В моей практике не было задач подобного характера, и у вас вряд ли появятся. Поэтому, при появлении циклической ссылки – ищите её источник и исправляйте ошибки в логике расчёта!