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

Поиск циклической ссылки в Excel

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

Выявление циклических связей

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

Способ 1: кнопка на ленте

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

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

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

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

      В диалоговом окне, сообщающем о наличии циклических ссылок, жмем на кнопку «OK».

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

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

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

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

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

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

    Простым примером такой ситуации является следующий вариант:
    — ячейка C3 ссылается на B6
    — ячейка B6 ссылается на D6
    — ячейка D6 ссылается на C3

    Тут найти проблему просто.

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

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

    Выделение группы ячеек

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

    Эта функция расположена на вкладке «Home» в группе «Найти и выделить» — «Выделение группы ячеек».

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

    Отслеживание связей ячейки

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

    Для начала нужно идентифицировать влияющие ячейки.

    — Самый простой способ – установить курсор в ячейку для анализа и нажать кнопку F2. Влияющие ячейки будут выделены тем же цветом, что и формула в активной ячейке.
    — Обозначив активную ячейку, нажать сочетание клавиш Ctrl+[ — будут отмечены все задействованные ячейки
    — Аналогичный вариант — сочетание клавиш Ctrl+Shift+[ — в этом случае на активном листе будут отмечены и прямо, и косвенно влияющие ячейки
    — Выделение группы ячеек по формулам (как описано выше).
    — Функция «Влияющие ячейки» на вкладке «Формула» показывает все задействованные в вычислениях ячейки стрелочками.

    Проверка на ошибки

    Можно воспользоваться штатной функцией Excel версии старше 2010.

    В меню «Формула» есть проверка на наличие ошибок, включая поиск циклических ссылок.

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

    Читать еще:  Как в excel рисовать схемы

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

    Фоновый поиск ошибок

    В параметрах Excel в группе настроек «Формулы» можно включить фоновый поиск ошибок. Это надстройка позволяет проводить автоматический пересчет формул на рабочем листе.

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

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

    Это осуществляется в меню «Файл» — «Параметры» в группе настроек «Формулы».

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

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

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

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

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

    Ошибка некоторые формулы содержат циклические ссылки

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

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

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

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

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

    Как удалить или разрешить циклическую ссылку

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

    Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить эту проблему, можно переместить формулу в другую ячейку. Нажмите клавиши CTRL + X , чтобы вырезать формулу, выберите другую ячейку и нажмите клавиши CTRL + V , чтобы вставить ее.

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

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

    Если вы только что ввели формулу, начните с этой ячейки и проверьте, не ссылается ли вы на саму ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Формулы, например = a1 + 1 (в ячейке a1), также вызывают ошибки циклических ссылок.

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

    Читать еще:  Excel как использовать

    Если найти ошибку не удается, на вкладке Формулы щелкните стрелку рядом с кнопкой Проверка ошибок, выберите пункт Циклические ссылки и щелкните первую ячейку в подменю.

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

    Продолжайте находить и исправлять циклические ссылки в книге, повторяя действия 1–3, пока из строки состояния не исчезнет сообщение «Циклические ссылки».

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

    При наличии циклических ссылок на других листах, кроме активного, в строке состояния выводится сообщение «Циклические ссылки» без адресов ячеек.

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

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

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

    Когда вы закроете сообщение, в ячейке будет отображено либо нулевое, либо последнее вычисленное значение. И теперь, наверное, говорят: «повесить последнее вычисленное значение?» Да. В некоторых случаях можно успешно выполнить формулу, прежде чем выполнять вычисление. Например, формула, использующая функцию если , может работать, пока пользователь не введет аргумент (часть данных, которая должна выполняться правильно), которая приводит к тому, что формула будет вычислять саму себя. В этом случае Excel сохраняет значение из последнего успешного вычисления.

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

    Щелкните формулу в строке формулы и нажмите клавишу ВВОД.

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

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

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

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

    Пользователь открывает книгу, содержащую циклическую ссылку.

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

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

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

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

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

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

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

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

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

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

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

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

    Читать еще:  Как в excel добавить проценты

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

    Дополнительные сведения

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

    Дополнительные сведения

    Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Заключение

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

    Похожие статьи

    Ссылка на основную публикацию
    Похожие публикации
    Adblock
    detector