Многие считают, что циклические ссылки в Excel — ошибки, но на самом деле не всегда так. Они могут помочь в решении некоторых задач, поэтому иногда используются намерено. Давайте подробно разберем, что же такое циклическая ссылка, как убрать циклические ссылки в Excel и прочее.
Что есть циклическая ссылка
Циклическая ссылка — это ссылка на ссылающуюся саму на себя ячейку. Это может быть не одна, а целая группа ссылок, которые ссылаются сами на себя, образуя замкнутый круг. В новых версиях приложения циклические ссылки блокируются, а если они есть в открытом вами документе, на экране появится соответствующее сообщение.
Как найти циклическую ссылку
Чаще всего программа находит их сама, и поскольку ссылки данного типа вредны, их необходимо устранить. Что же делать, если программа не смогла их найти сама, то есть, не пометила выражения линиями со стрелками? В таком случае:
Циклические ссылки в EXCEL (как создать, найти, отключить, где применить, итеративные вычисления)
- На верхней панели программы нужно перейти в раздел «Формулы». Далее открываем меню «Зависимости формул», а там «Проверка наличия ошибок». Появится меню, где нужно выбрать «Циклические ссылки». Откроется следующее меню, где будут отображены найденные программой адреса элементов, содержащих циклические ссылки.
- Если кликнуть на любой из адресов, выделится соответствующая ячейка на листе.
Устраняем циклические ссылки
После обнаружения ссылки возникает вопрос, каким образом можно удалить циклическую ссылку в Microsoft Excel. Для исправления следует хорошенько просмотреть зависимость между ячейками. Допустим вами была введена какая-либо формула, но в итоге она не захотела работать, а программа выдает ошибку, указывая на наличие циклической ссылки.
Например, эта формула не является работоспособной поскольку находится в ячейке D3 и ссылается на себя же. Ее нужно вырезать из этой ячейки, выделив формулу в строке формул и нажав CTRL+X, после чего вставить в другой ячейке, выделив ее и нажав CTRL+V.
Это лишь простой пример, для более сложных вычислений проверка может затянуться надолго.
Интеративные вычисления
Многие, наверное, слышали такое понятие, как итерация и хотят знать, итеративные вычисления в Excel, что это. Это вычисления, которые продолжают выполняться до тех пор, пока не будет получен результат, соответствующий указанным условиям.
Для включения функции следует открыть параметры программы и в разделе «Формулы» активировать «Включить итеративные вычисления».
Заключение
Теперь вы знаете, что делать, если зациклилось вычисляемое выражение. Иногда нужно применить логику и хорошенько подумать, чтобы устранить циклические ссылки.
Это может быть интересно:
Рекомендуем почитать:
Источник: kakvofise.ru
Удаление или разрешение циклической ссылки
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Вы ввели формулу, но она не работает. Вместо этого вы получили сообщение о «циклической ссылке». Миллионы людей имеют ту же проблему, и это происходит потому, что ваша формула пытается вычислить себя. Вот как это выглядит:
Формула =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 или попросить помощи в сообществе Answers community.
Совет: Если вы владелец малого бизнеса и хотите получить дополнительные сведения о настройке Microsoft 365, посетите раздел Справка и обучение для малого бизнеса.
Источник: support.microsoft.com
Циклические ссылки в Microsoft Excel
Принято считать, что циклические ссылки в Экселе представляют собой ошибочное выражение. Действительно, довольно часто это именно так, но все-таки не всегда. Иногда они применяются вполне осознанно. Давайте выясним, чем же являются циклические ссылки, как их создавать, как отыскать уже существующие в документе, как работать с ними или как при необходимости удалить.
Использование циклических ссылок
Прежде всего, выясним, что же представляет собой циклическая ссылка. По сути, это выражение, которое посредством формул в других ячейках ссылается само на себя. Так же ею может являться ссылка, расположенная в элементе листа, на который она сама ссылается.
Нужно отметить, что по умолчанию современные версии Excel автоматически блокируют процесс выполнения цикличной операции. Это связано с тем, что такие выражения в подавляющем большинстве ошибочные, а зацикливание производит постоянный процесс пересчета и вычисления, что создает дополнительную нагрузку на систему.
Создание циклической ссылки
Теперь посмотрим, как создать простейшее цикличное выражение. Это будет ссылка, расположенная в той же ячейке, на которую она ссылается.
- Выделяем элемент листа A1 и записываем в нем следующее выражение: =A1 Далее жмем на кнопку Enter на клавиатуре.
Немного усложним задачу и создадим циклическое выражение из нескольких ячеек.
-
В любой элемент листа записываем число. Пусть это будет ячейка A1, а число 5.
Теперь перейдем к созданию циклического выражения на примере таблицы. У нас имеется таблица реализации продуктов питания. Она состоит из четырех колонок, в которых указано наименование товара, количество проданной продукции, цена и сумма выручки от продажи всего объема. В таблице в последнем столбце уже имеются формулы. В них производится расчет выручки путем умножения количества на цену.
- Чтобы зациклить формулу в первой строчке, выделяем элемент листа с количеством первого по счету товара (B2). Вместо статического значения (6) вписываем туда формулу, которая будет считать количество товара путем деления общей суммы (D2) на цену (C2): =D2/C2 Щелкаем по кнопке Enter.
- У нас получилась первая циклическая ссылка, взаимосвязь в которой привычно обозначена стрелкой трассировки. Но как видим, результат ошибочен и равен нулю, так как уже было сказано раньше, Excel блокирует выполнения цикличных операций.
- Скопируем выражение во все остальные ячейки столбца с количеством продукции. Для этого устанавливаем курсор в нижний правый угол того элемента, который уже содержит формулу. Курсор преобразуется в крестик, который принято называть маркером заполнения. Зажимаем левую кнопку мыши и тянем этот крестик до конца таблицы вниз.
- Как видим, выражение было скопировано во все элементы столбца. Но, помечена стрелкой трассировки только одна взаимосвязь. Заметим это на будущее.
Поиск циклических ссылок
Как мы уже видели выше, не во всех случаях программа помечает взаимосвязь циклической ссылки с объектами, даже если она имеется на листе. Учитывая тот факт, что в подавляющем большинстве цикличные операции вредны, их следует удалить. Но для этого их нужно сначала отыскать. Как же это сделать, если выражения не помечены линией со стрелками? Давайте разберемся с этой задачей.
- Итак, если при запуске файла Excel у вас открывается информационное окно о том, что он содержит циклическую ссылку, то её желательно отыскать. Для этого перемещаемся во вкладку «Формулы». Жмем на ленте на треугольник, который размещен справа от кнопки «Проверка наличия ошибок», расположенной в блоке инструментов «Зависимости формул». Открывается меню, в котором следует навести курсор на пункт «Циклические ссылки». После этого в следующем меню открывается список адресов элементов листа, в которых программа обнаружила цикличные выражения.
- При клике на конкретный адрес происходит выделение соответствующей ячейки на листе.
Существует ещё один способ узнать, где располагается циклическая ссылка. Сообщение о данной проблеме и адрес элемента, содержащего подобное выражение, располагается в левой части строки состояния, которая находится внизу окна Excel. Правда, в отличие от предыдущего варианта, на строке состояния отображаться будут адреса не всех элементов, содержащих циклические ссылки, если их много, а только одного из них, который появился раньше других.
К тому же, если вы находитесь в книге, содержащей цикличное выражение, не на том листе, где оно расположено, а на другом, то в этом случае в строке состояния будет отображаться только сообщение о наличие ошибки без адреса.
Исправление циклических ссылок
Как уже говорилось выше, в подавляющем большинстве случаев цикличные операции – это зло, от которого следует избавляться. Поэтому, закономерно, что после того, как цикличная связь обнаружена, нужно её исправить, чтобы привести формулу к нормальному виду.
Для того, чтобы исправить цикличную зависимость, нужно проследить всю взаимосвязь ячеек. Даже если проверка указала конкретную ячейку, то ошибка может крыться не в ней самой, а в другом элементе цепочки зависимости.
- В нашем случае, несмотря на то, что программа верно указала на одну из ячеек цикла (D6), реальная ошибка кроется в другой ячейке. Выделяем элемент D6, чтобы узнать, из каких ячеек он подтягивает значение. Смотрим на выражение в строке формул. Как видим, значение в этом элементе листа формируется путем умножения содержимого ячеек B6 и C6.
- Переходим к ячейке C6. Выделяем её и смотрим на строку формул. Как видим, это обычное статическое значение (1000), которое не является продуктом вычисления формулы. Поэтому можно с уверенностью сказать, что указанный элемент не содержит ошибки, вызывающей создание циклических операций.
- Переходим к следующей ячейке (B6). После выделения в строке формул мы видим, что она содержит вычисляемое выражение (=D6/C6), которое подтягивает данные из других элементов таблицы, в частности, из ячейки D6. Таким образом, ячейка D6 ссылается на данные элемента B6 и наоборот, что вызывает зацикленность.
Тут взаимосвязь мы вычислили довольно быстро, но в реальности бывают случаи, когда в процессе вычисления участвует множество ячеек, а не три элемента, как у нас. Тогда поиск может занять довольно много времени, ведь придется изучить каждый элемент цикличности. - Теперь нам нужно понять, в какой именно ячейке (B6 или D6) содержится ошибка. Хотя, формально это даже не ошибка, а просто избыточное использование ссылок, которое приводит к зацикливанию. Во время процесса решения того, какую ячейку следует отредактировать, нужно применять логику. Тут нет четкого алгоритма действий. В каждом конкретном случае эта логика будет своя. Например, если в нашей таблице общая сумма должна вычисляться путем умножения количества фактически проданного товара на его цену, то можно сказать, что ссылка, рассчитывающая количество от общей суммы продажи, тут явно лишняя. Поэтому мы её удаляем и заменяем на статическое значение.
- Подобную операцию проводим и над всеми другими цикличными выражениями, если они имеются на листе. После того, как абсолютно все циклические ссылки были удалены из книги, сообщение о наличие данной проблемы должно исчезнуть из строки состояния. Кроме того, полностью ли были удалены цикличные выражения, можно узнать, воспользовавшись инструментом проверки наличия ошибок. Переходим во вкладку «Формулы» и жмем уже знакомый нам треугольник справа от кнопки «Проверка наличия ошибок» в группе инструментов «Зависимости формул». Если в запустившемся меню пункт «Циклические ссылки» не будет активен, то, значит, мы удалили все подобные объекты из документа. В обратном случае, нужно будет применить процедуру удаления к элементам, которые находятся в списке, тем же рассматриваемым ранее способом.
Разрешение выполнения цикличных операций
В предшествующей части урока мы рассказывали, в основном, как бороться с циклическими ссылками, или как их найти. Но, ранее разговор шел также о том, что в некоторых случаях они, наоборот, могут быть полезными и осознанно использоваться пользователем. Например, довольно часто данный метод применяется для итеративных вычислений при построении экономических моделей.
Но беда состоит в том, что, независимо от того, осознанно или неосознанно вы используете циклическое выражение, Excel по умолчанию все равно будет блокировать операцию по ним, дабы не привести к излишней перегрузке системы. В таком случае актуальным становится вопрос принудительного отключения такой блокировки. Посмотрим, как это сделать.
- Прежде всего, перемещаемся во вкладку «Файл» приложения Excel.
- Далее щелкаем по пункту «Параметры», расположенному в левой части открывшегося окна.
- Происходит запуск окна параметров Эксель. Нам нужно перейти во вкладку «Формулы».
- Именно в открывшемся окне можно будет произвести разрешение выполнения цикличных операций. Переходим в правый блок этого окна, где находятся непосредственно сами настройки Excel. Мы будем работать с блоком настроек «Параметры вычислений», который расположен в самом верху. Чтобы разрешить применение цикличных выражений, нужно установить галочку около параметра «Включить итеративные вычисления». Кроме того, в этом же блоке можно настроить предельное число итераций и относительную погрешность. По умолчанию их значения равны 100 и 0,001 соответственно. В большинстве случаев данные параметры изменять не нужно, хотя при необходимости или при желании можно внести изменения в указанные поля. Но тут нужно учесть, что слишком большое количество итераций может привести к серьезной нагрузке на программу и систему в целом, особенно если вы работаете с файлом, в котором размещено много цикличных выражений. Итак, устанавливаем галочку около параметра «Включить итеративные вычисления», а затем, чтобы новые настройки вступили в силу, жмем на кнопку «OK», размещенную в нижней части окна параметров Excel.
- После этого мы автоматически переходим на лист текущей книги. Как видим, в ячейках, в которых располагаются цикличные формулы, теперь значения рассчитываются корректно. Программа не блокирует вычисления в них.
Но все-таки стоит заметить, что включением цикличных операций злоупотреблять не стоит. Применять данную возможность следует только тогда, когда пользователь полностью уверен в её необходимости. Необоснованное включение цикличных операций может не только привести к избыточной нагрузке на систему и замедлить вычисления при работе с документом, но пользователь может неумышленно внести ошибочное цикличное выражение, которое по умолчанию тут же было бы заблокировано программой.
Как мы видим, в подавляющем большинстве случаев, циклические ссылки представляют собой явление, с которым нужно бороться. Для этого, прежде всего, следует обнаружить саму цикличную взаимосвязь, затем вычислить ячейку, где содержится ошибка, и, наконец, устранить её, внеся соответствующие коррективы. Но в отдельных случаях цикличные операции могут быть полезны при вычислениях и производиться пользователем осознанно. Но даже тогда стоит к их использованию подходить с осторожностью, правильно настроив Excel и зная меру в добавлении подобных ссылок, которые при использовании в массовом количестве способны замедлить работу системы.
Источник: lumpics.ru