Поиск повторяющейся ссылки в Excel

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

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

Выявление повторяющихся связей

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

Метод 1: клавиша на ленте

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

Метод 2: стрелка трассировки

Существует и иной метод определения схожих ненужных зависимостей.

«OK»

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

Как лицезреем, найти циклическую ссылку в Эксель достаточно просто, в особенности если знать метод поиска. Можно пользоваться одним из 2-ух методов нахождения схожих зависимостей. Несколько труднее найти, нужна ли данная формула в вправду либо это просто ошибка, также поправить неверную ссылку.

Мы рады, что смогли посодействовать Для вас в решении препядствия.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

Циклические ссылки в Excel: поиск и исправление

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

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

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

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

Нахождение повторяющихся ссылок

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

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

Способ 1. Зрительный поиск повторяющейся ссылки

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

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

Интересно почитать: Динамическая сортировка в таблице excel

Способ 2. Внедрение инструментов на Ленте

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

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

Заключение

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

Как отыскать радиальную ссылку в Excel (стремительно и просто)

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

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

Что такое радиальная ссылка в Excel?

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

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

Это даст для вас предупреждение о радиальный ссылке.

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

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

Как отыскать радиальные ссылки в Excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Это можно создать при помощи функции «Выслеживать прецеденты».

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

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

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

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

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

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

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

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

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

В Excel это именуется итеративным вычислением .

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

Перейдите на вкладку Файл.
Щелкните Характеристики. Раскроется диалоговое окно «Характеристики Excel».
Изберите формулу на левой панели
В разделе «Характеристики расчета» установите флаг «Включить итеративный расчет». Тут вы сможете указать наибольшее количество итераций и наибольшее значение конфигурации.

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

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

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

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

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

Предумышленное внедрение радиальных ссылок

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

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

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

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

Хотя вы сможете просто вставить метку времени, используя последующую формулу:

Неувязка с приведенной выше формулой заключается в том, что она обновит все временные метки, как на листе будет внесено какое-либо изменение либо если рабочий лист будет повторно открыт (так как формула NOW является изменчивой)

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

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

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