Как найти циклические ссылки в Excel

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

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

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

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

Видео

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

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

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

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

Панель формул

Существует ещё третий способ запустить функцию «СРЗНАЧ». Для этого, переходим во вкладку «Формулы». Выделяем ячейку, в которой будет выводиться результат. После этого, в группе инструментов «Библиотека функций» на ленте жмем на кнопку «Другие функции». Появляется список, в котором нужно последовательно перейти по пунктам «Статистические» и «СРЗНАЧ».

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

Дальнейшие действия точно такие же.

Как найти циклическую ссылку

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

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

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

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

Если циклическая ссылка одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.

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

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

Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.

На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.

Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После ее корректировки или удаления – со второй и т.д.

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

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

Данный способ самый простой, однако, удобен лишь при работе с небольшими таблицами.

1. Для начала нам нужно закрыть всплывшее диалоговое окно, предупреждающее нас о том, что в книге присутствует циклическая ссылка.
2. После этого в таблице отобразится так называемая стрелка трассировки, наглядно демонстрирующая связь между ячейками, которая вызывает ошибку.
3. Переходим в ту ячейку, куда указывает стрелка и исправляем формулу, чтобы избавиться от цикличности. В нашем случае лишним элементом является ячейка F13, поэтому убираем ее и формулы.
4. Все готово. Теперь в нашей таблице нет циклических ссылок.

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

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

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

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

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

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

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

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

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

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

​.​ их использованию подходить​, а затем, чтобы​«Файл»​ во вкладку​ Тогда поиск может​ каких ячеек он​

1. ​ предыдущего варианта, на​​ Учитывая тот факт,​​ (​=C1​​ элементе листа, на​​ в одной ячейки​ такая формула, прежде​ — Зависимости. Включай/выключай​

   

​ но попрежнему серый​​ нажимаю «Обновить», потом​С4​ с осторожностью, правильно​

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

   

2. ​6​​В следующий элемент (​​ который она сама​​ от другой.​​ всего, жмем на​

   

3. ​ кнопки Влияющие ячейки,​​ фон, сами листы​​ возникает сообщение, что​​ссылается на ячейку​​ настроив Excel и​

   

4. ​ в силу, жмем​Далее щелкаем по пункту​и жмем уже​​ времени, ведь придется​​ на выражение в​

   

   ​ будут адреса не​

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

​ походи по ячейкам​​Что значит «Вычислить»​ ячейка в формуле​.​

1. ​ добавлении подобных ссылок,​«OK»​, расположенному в левой​​ справа от кнопки​​ цикличности.​ видим, значение в​ циклические ссылки, если​​ удалить. Но для​​ количество товара путем​ формулы:​​ умолчанию современные версии​​ нагляден, но в​

​ красном квадрате в​ таблицы. Стрелки помогут​​ ????​​ ссылается на результат​

​С3​

Устраняем циклические ссылки

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

Например, эта формула не является работоспособной поскольку находится в ячейке D3 и ссылается на себя же. Ее нужно вырезать из этой ячейки, выделив формулу в строке формул и нажав CTRL+X, после чего вставить в другой ячейке, выделив ее и нажав CTRL+V.

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

Чем плохи циклические ссылки?

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

Как удалить круговую ссылку в Excel?

Как только вы определили, что на вашем листе есть циклические ссылки, пора их удалить (если вы не хотите, чтобы они были там по какой-либо причине).

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

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

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

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

• Формула в ячейке A6: =SUM(A1:A5)+C6.
• Формула: ячейка C1 = A6 * 0,1
• Формула в ячейке C6: = A6 + C1.

В приведенном выше примере результат в ячейке C6 зависит от значений в ячейках A6 и C1, которые, в свою очередь, зависят от ячейки C6 (что приводит к ошибке циклической ссылки)

И снова я выбрал очень простой пример только для демонстрационных целей. На самом деле, это может быть довольно сложно понять, и, возможно, они находятся далеко на одном листе или даже разбросаны по нескольким листам.

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

Это можно сделать с помощью опции «Отслеживать прецеденты».

Ниже приведены шаги по использованию прецедентов трассировки для поиска ячеек, которые передаются в ячейку с циклической ссылкой:

• Выберите ячейку с круговой ссылкой
• Перейдите на вкладку «Формулы».
• Нажмите на прецеденты трассировки

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

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

Это хорошо работает, если у вас есть все формулы, относящиеся к ячейкам на одном листе. Если он находится на нескольких листах, этот метод неэффективен.

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

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

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

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

   Если вы используете Excel 2007, нажмите кнопку Microsoft Office нажмите кнопку Параметры Excel ивыберите категорию Формулы.

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

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

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

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

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

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

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

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

Теги