Как найти файл цикла

При работе с формулами Excel вы можете иногда увидеть следующее предупреждение.

Это приглашение сообщает вам, что на вашем листе есть циклическая ссылка, и это может привести к неправильному расчету по формулам. Он также просит вас решить эту проблему с циклическими ссылками и отсортировать ее.

В этом уроке я расскажу все, что вам нужно знать о круговой ссылке, а также как найти и удалить циклические ссылки в Excel.

Итак, приступим!

Что такое круговая ссылка в Excel?

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

Позвольте мне попытаться объяснить это на простом примере.

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

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

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

Как найти круговые ссылки в Excel?

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

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

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

1. Активируйте рабочий лист с круговой ссылкой
2. Перейдите на вкладку «Формулы».
3. В группе «Редактирование формул» щелкните раскрывающийся значок «Проверка ошибок» (маленькая стрелка, направленная вниз, справа).
4. Наведите курсор на опцию Циркулярные ссылки. Он покажет вам ячейку с круговой ссылкой на листе.
5. Щелкните адрес ячейки (который отображается), и вы перейдете к этой ячейке на листе.

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

Еще один быстрый и простой способ найти круговую ссылку — это посмотреть на строку состояния. В левой его части будет отображаться текст Циркулярная ссылка вместе с адресом ячейки.

При работе с круговыми ссылками необходимо знать несколько вещей:

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

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

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

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

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

Но иногда все не так просто.

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

Позвольте мне показать вам пример.

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

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

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

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

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

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

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

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

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

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

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

Как включить / отключить итерационные вычисления в Excel

Когда у вас есть круговая ссылка в ячейке, сначала вы получаете предупреждение, как показано ниже, и если вы закроете это диалоговое окно, в качестве результата в ячейке вы получите 0.

Это связано с тем, что циклическая ссылка представляет собой бесконечный цикл, и Excel не хочет зацикливаться на нем. Таким образом, он возвращает 0.

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

Это называется итерационный расчет в Excel.

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

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

Вот и все! Вышеупомянутые шаги позволят выполнить итеративный расчет в Excel.

Позвольте мне также быстро объяснить два варианта итеративного расчета:

• Максимальное количество итераций: Это максимальное количество раз, которое вы хотите, чтобы Excel вычислил, прежде чем выдает окончательный результат. Поэтому, если вы укажете это как 100, Excel выполнит цикл 100 раз, прежде чем выдаст вам окончательный результат.
• Максимальное изменение: Это максимальное изменение, которое, если не достигнуто между итерациями, вычисление будет остановлено. По умолчанию это значение 0,001. Чем ниже это значение, тем точнее будет результат.

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

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

Умышленное использование круговых ссылок

В большинстве случаев наличие круговой ссылки на вашем листе будет ошибкой. Вот почему Excel показывает подсказку: «Попробуйте удалить или изменить эти ссылки или переместить формулы в другие ячейки».

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

Один такой конкретный случай, о котором я уже писал, получение отметки времени в ячейке в ячейке в Excel.

Например, предположим, что вы хотите создать формулу, чтобы каждая запись производилась в ячейке в столбце A, а метка времени отображалась в столбце B (как показано ниже):

Хотя вы можете легко вставить метку времени, используя следующую формулу:
= ЕСЛИ (A2 ""; ЕСЛИ (B2 ""; B2, СЕЙЧАС ()), "")
Проблема с приведенной выше формулой заключается в том, что она обновит все временные метки, как только на листе будет внесено какое-либо изменение или если рабочий лист будет повторно открыт (поскольку формула СЕЙЧАС является изменчивой)

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

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

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

Надеюсь, вы нашли этот урок полезным!

Другие учебники по Excel могут оказаться полезными:

• # ССЫЛКА! Ошибка в Excel; Как исправить ошибку ссылки!
• Обработка ошибок Excel VBA
• Используйте ЕСЛИОШИБКА с функцией ВПР, чтобы избавиться от # ошибок Н / Д
• Как сослаться на другой лист или книгу в Excel (с примерами)
• Абсолютные, относительные и смешанные ссылки на ячейки в 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 и так далее.

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

Теги