Циклические ссылки как найти: Циклические ссылки в excel | Excel Training

Циклические ссылки в excel | Excel Training

25.10.2013 Григорий Цапко Полезные советы

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

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

Эта заметка как раз и будет призвана дать ответ на вопрос:  а всегда ли циклические ссылки – это плохо? И как с ними правильно работать, чтобы максимально использовать их вычислительный потенциал.

Для начала разберемся, что такое циклические ссылки в excel 2010.

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

Например, ячейка С4 = Е 7, Е7 = С11, С11 = С4. В итоге, С4 ссылается на С4.

Наглядно это выглядит так:

 

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

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

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

 

При нажатии на кнопку ОК, сообщение будет закрыто, а в ячейке содержащей циклическую ссылку в большинстве случаев появиться 0.

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

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

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

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

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

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

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

 

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

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

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

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

 

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

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

Использование циклических ссылок

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

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

После введения всех формул, у нас появляется циклическая ссылка:

 

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

 

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

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

На тему методов распределения затрат в ближайшее время появиться отдельная статья.

Пока же нас интересует сама возможность таких вычислений.

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

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

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

Включить итеративные вычисления можно через вкладку Файл → раздел Параметры → пункт Формулы. Устанавливаем флажок «Включить итеративные вычисления».

 

 

 

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

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

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

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

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

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

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

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

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

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

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

Содержание

  1. Нахождение циклических ссылок
    • Метод 1. Визуальный поиск циклической ссылки
    • Метод 2. Использование инструментов на Ленте
  2. Заключение

Нахождение циклических ссылок

Смотрите также: “Как построить график в Excel”

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

Следовательно, ломать голову над тем, если ли в книге циклическая ссылка (ссылки) или нет, не нужно, так как это понятно в момент его открытия. Остается только определить, где именно она находится.

Метод 1. Визуальный поиск циклической ссылки

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

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

Метод 2. Использование инструментов на Ленте

Смотрите также: “Как объединить столбцы в Excel”

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

  1. Начнем с того, что закроем информационное окно о наличии циклической ссылки.
  2. Теперь переключаемся во вкладку “Формулы”. Обращаем внимание на раздел “Зависимости формул”. Здесь нас интересует кнопка “Проверка ошибок” (в некоторых случаях, когда размеры окна сжаты по горизонтали, отображается только значок кнопки в виде восклицательного знака). Щелкаем по небольшому треугольнику, направленному вниз, справа от кнопки. Откроется перечень команд, среди которых выбираем пункт “Циклические ссылки”, после чего откроется список всех ячеек, содержащих эти самые ссылки.
  3. Если мы щелкнем на адрес ячейки, программа сразу же выделит ее, независимо от того, в какой ячейке мы находились до того, как решили воспользоваться данной функцией.
  4. Нам остается только разобраться с формулой и исправить допущенные в ней ошибки. В нашем случае в диапазон суммируемых ячеек была включена и ячейка, куда записана сама формула, что конечно же, неверно.
  5. Корректируем координаты диапазона в формуле, чтобы избавиться от цикличности.
  6. Чтобы удостовериться в том, что теперь все в порядке, снова раскрываем перечень команд рядом с кнопкой “Проверка ошибок”. На этот раз пункт “Циклические ссылки” неактивен, что свидетельствует о том, что ошибки устранены.

Заключение

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

Смотрите также: “Как в Экселе закрепить столбец таблицы”

[Совет] Как быстро найти и найти циклические ссылки в Excel?

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

Формула Excel Vlookup – Путеводитель

Надоело загружать электронные книги с объемным текстом или копией?

Если Да , вам понравится это руководство по формулам Excel Vlookup — VLOOKUP, HLOOKUP, MATCH & INDEX.

Это просто означает, что ваша формула включает ячейку, на которую распространяется формула. Простой пример: Циклическая справочная работа в Excel 2006, Excel 2007, Excel 2010, Excel 2013, Excel 2016 и Excel 2019.

Циклическая ссылка Excel — это формула в Excel Cell, которая прямо или косвенно ссылается на свои собственные ячейки. Итак, в этом учебнике по Excel мы видим, как найти циклическую ссылку Excel.

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

Что такое поиск циклической ссылки в Excel?

Циклическая ссылка Предупреждение:

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

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

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

Чтобы найти циклическую ссылку в Excel, выполните следующие действия.

  • Выберите «Проверка ошибок» и укажите «Циклические ссылки», после чего откроется раскрывающийся список.
  • Нажмите Циклические ссылки в раскрывающемся списке, и Excel выдаст ячейку с циклическими ссылками
  • Это найдет функцию Excel и удалит циклические ссылки.

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

    Формула Excel Vlookup – Путеводитель

    Надоело загружать электронные книги с объемным текстом или копией?

    Если Да , вам понравится это руководство по формулам Excel Vlookup — VLOOKUP, HLOOKUP, MATCH & INDEX.

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

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

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

    Использование циклической ссылки в Excel для вашего удобства:

    Итак, вы, должно быть, думаете, что циклическая ссылка бесполезна, и ваша цель — найти циклическую ссылку и исправить ее. Вы не правы!. Существуют формулы и уравнения, которые могут понадобиться вам для создания циклической ссылки. Позвольте мне привести вас через один пример. Кроме того, вы можете ознакомиться с нашими бесплатными онлайн-советами по Excel, PowerPoint и VBA.

    Вы строите здание и решили дать своему строителю комиссию в размере 2% от общей стоимости здания. Просто… верно? Нет, если 2% включают в себя стоимость вознаграждения застройщика! Если вы хотите сделать это с помощью формулы, вы можете это сделать. Но представьте, что у вас есть сотни таких факторов и итераций. Просто следуйте простым шагам, как показано ниже: Введите формулу общей стоимости, которая включает ячейку C3.

    15 Руководство по очистке данных Excel

    Надоело загружать электронные книги с большим объемом текста или копией вставки? Если Да , вам понравится это руководство (43 страницы) 

    Затем, за комиссию, введите приведенную ниже формулу, которая создаст круговую ссылку, и стрелка трассировщика покажет это. Что касается окна предупреждения о круговой ссылке, просто нажмите «ОК».

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

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

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

    Чтобы найти циклические ссылки в Excel, выполните следующие шаги;

    1. Щелкните вкладку «Формулы», чтобы найти циклическую ссылку в Excel.
    2. Щелкните стрелку кнопки «Проверка ошибок», выберите «Циклические ссылки», а затем щелкните первую ячейку в подменю.
    3. Просмотрите ячейку формулы, и Excel выдаст ячейку с циклическими ссылками
    4. Таким образом, вы можете найти циклические ссылки в Excel.

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

    15 хитростей со сводными таблицами для профессионалов

    15 хитростей со сводными таблицами, которые сделают анализ данных в Excel еще умнее! 5600+ загрузок.

    Самые популярные фокусы №3, №7 и №12

    unix — Как найти циклические символические ссылки?

    спросил

    Изменено
    9лет, 4 месяца назад

    Просмотрено
    18 тысяч раз

    Я работаю над системой HP-UX и хочу найти циклические символические ссылки.

    Пока я использую команду:

     ls -lrt `find ./ -follow -type l`
     

    Но в результате выполняется только ls -lrt для текущего каталога.

    Какую команду следует использовать для поиска всех циклических символических ссылок в системе?

    • Юникс
    • найти
    • символическая ссылка
    • hp-ux

    4

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

     find . -следовать -printf ""
     

    в поиске GNU, который смог найти циклы вида ./a -> ./b и ./b -> ./a печатая ошибку

     find: `./a': Too много уровней символических ссылок
    find: `./b': слишком много уровней символических ссылок
     

    (это также работало на a->b->c->a )

    Аналогично, циклы вида ./foo/x -> .. и ./foo/a -> ./ bar + ./bar/b -> ./foo напечатал ошибки

     find: Обнаружена петля файловой системы; `.

    This entry was posted in Популярное