Как убрать циклическую ссылку в экселе – Excel () »

Как убрать циклическую ссылку в экселе – Excel () »

Содержание

Циклические ссылки в Excel — Excel works!

Абсолютные и относительные ссылки Excel Как преобразовать текст в число в Excel?

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

Теория

Далее будем именовать циклические ссылки Ц/С для удобства. А теперь рассмотрим основное понятие о Ц/С, как они возникают, и как их обнаружить в книге.
Итак, всё дело в том, что данный вид ссылок создается, в том случае, если формула в любой из ячеек,  ссылается непосредственно на себя через другие ячейки.

К примеру, в формуле участвует результат вычисления этой формулы или ячейка =A3=B3, =B3=C3, =C3=A3. В результате A3 ссылается на A3. То есть если образуется циклическая зависимость. Пример на картинке показан для режима Формулы — Показать формулы

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

Обозначение Ц/С в Excel
Появление Ц/С довольно просто распознать. Во время их появления или присутствия в уже созданном файле Excel тотчас же возникает окно с сообщением (как на первой картинке). Если нажать на кнопку ОК, то предупреждение будет скрыто, а в ячейке, которая содержит Ц/С отобразится значение 0.
Предупреждение, возникает при изначальном появлении Ц/С, либо же открытии книги, которая её содержит (частая проблема от многих пользователей). Если вы принимаете предупреждение программы, то далее при наличии Ц/С, оно может и не появляться.

Способы найти Ц/С
Ц/С в редакторе eсxel могут быть созданы как преднамеренно, для того, что бы решить те или иные задачи табличных финансовых моделей (об этом далее), так и возникнуть случайно, как техническая ошибка в формулах их построения.
Способов нахождения Ц/С существует несколько:

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

Но можно и воспользоваться инструментом для поиска ошибок. Просто нажимаем вкладку «Формулы» и в разделе – «Поиск ошибок» и далее в выпадающем списке выбираете «Циклические ссылки». Программа отобразит ячейку с первой Ц/С. А после её коррекции или удаления, отобразиться следующая. Вы можете любым методом для их определение, который будет вам наиболее удобен.

Как можно полезно использовать Циклические ссылки в Excel?

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

Включить итеративные вычисления!

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

Конечно же в Excel есть возможность включить Файл (круглая кнопка в левом верхнем углу) — Параметры — Формулы. Поставить галочку «Включить итеративные вычисления».

Так же необходимо установить предельное число итераций и относительную погрешность, ниже

Не рекомендую ставить большое кол-во вычислений — система может основательно подвиснуть.

Поделитесь нашей статьей в ваших соцсетях:

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

(Visited 1 443 times, 1 visits today)

Абсолютные и относительные ссылки Excel Как преобразовать текст в число в Excel?

excelworks.ru

Циклические ссылки — Хитрости работы с Excel – таблицы, функции, формулы, списки, рабочие книги, макросы

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

Чтобы выявить циклическую ссылку:

Нажмите вкладку Формулы

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

Просмотрите формулу ячейки.

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

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

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

Чтобы ввести функцию:

Щелкните ячейку, куда вы хотите ввести функцию.

Введите знак равенства (=), введите имя функции, а затем откройте скобку.

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

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

Нажмите кнопку Ввод в строке формул или нажмите клавишу Enter

Excel автоматически закроет скобку, чтобы завершить ввод функции.

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

Чтобы ввести функцию при помощи команды Вставить функцию:

Щелкните ячейку, в. которую вы хотите ввести функцию.

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

Введите краткое описание нужной вам функции в окно поиска и нажмите Найти

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

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

Нажмите ОК

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

Нажмите ОК.


Newer news items:

Older news items:


www.teachexcel.ru

Циклические ссылки в excel как сделать

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

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

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

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

Создание циклической ссылки

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

  1. Выделяем элемент листа A1 и записываем в нем следующее выражение:

    =A1

    Далее жмем на кнопку Enter на клавиатуре.

  2. После этого появляется диалоговое окно предупреждения о циклическом выражении. Щелкаем в нем по кнопке «OK».
  3. Таким образом, мы получили цикличную операцию на листе, в которой ячейка ссылается сама на себя.

Немного усложним задачу и создадим циклическое выражение из нескольких ячеек.

  1. В любой элемент листа записываем число. Пусть это будет ячейка A1, а число 5.
  2. В другую ячейку (B1) записываем выражение:

    =C1

  3. В следующий элемент (C1) производим запись такой формулы:

    =A1

  4. После этого возвращаемся в ячейку A1, в которой установлено число 5. Ссылаемся в ней на элемент B1:

    =B1

    Жмем на кнопку Enter.

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

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

  1. Чтобы зациклить формулу в первой строчке, выделяем элемент листа с количеством первого по счету товара (B2). Вместо статического значения (6) вписываем туда формулу, которая будет считать количество товара путем деления общей суммы (D2) на цену (C2):

    =D2/C2

    Щелкаем по кнопке Enter.

  2. У нас получилась первая циклическая ссылка, взаимосвязь в которой привычно обозначена стрелкой трассировки. Но как видим, результат ошибочен и равен нулю, так как уже было сказано раньше, Excel блокирует выполнения цикличных операций.
  3. Скопируем выражение во все остальные ячейки столбца с количеством продукции. Для этого устанавливаем курсор в нижний правый угол того элемента, который уже содержит формулу. Курсор преобразуется в крестик, который принято называть маркером заполнения. Зажимаем левую кнопку мыши и тянем этот крестик до конца таблицы вниз.
  4. Как видим, выражение было скопировано во все элементы столбца. Но, помечена стрелкой трассировки только одна взаимосвязь. Заметим это на будущее.

Поиск циклических ссылок

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

  1. Итак, если при запуске файла Excel у вас открывается информационное окно о том, что он содержит циклическую ссылку, то её желательно отыскать. Для этого перемещаемся во вкладку «Формулы». Жмем на ленте на треугольник, который размещен справа от кнопки «Проверка наличия ошибок», расположенной в блоке инструментов «Зависимости формул». Открывается меню, в котором следует навести курсор на пункт «Циклические ссылки». После этого в следующем меню открывается список адресов элементов листа, в которых программа обнаружила цикличные выражения.
  2. При клике на конкретный адрес происходит выделение соответствующей ячейки на листе.

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

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

Урок: Как найти циклические ссылки в Excel

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

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

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

  1. В нашем случае, несмотря на то, что программа верно указала на одну из ячеек цикла (D6), реальная ошибка кроется в другой ячейке. Выделяем элемент D6, чтобы узнать, из каких ячеек он подтягивает значение. Смотрим на выражение в строке формул. Как видим, значение в этом элементе листа формируется путем умножения содержимого ячеек B6 и C6.
  2. Переходим к ячейке C6. Выделяем её и смотрим на строку формул. Как видим, это обычное статическое значение (1000), которое не является продуктом вычисления формулы. Поэтому можно с уверенностью сказать, что указанный элемент не содержит ошибки, вызывающей создание циклических операций.
  3. Переходим к следующей ячейке (B6). После выделения в строке формул мы видим, что она содержит вычисляемое выражение (=D6/C6), которое подтягивает данные из других элементов таблицы, в частности, из ячейки D6. Таким образом, ячейка D6 ссылается на данные элемента B6 и наоборот, что вызывает зацикленность.

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

  4. Теперь нам нужно понять, в какой именно ячейке (B6 или D6) содержится ошибка. Хотя, формально это даже не ошибка, а просто избыточное использование ссылок, которое приводит к зацикливанию. Во время процесса решения того, какую ячейку следует отредактировать, нужно применять логику. Тут нет четкого алгоритма действий. В каждом конкретном случае эта логика будет своя.

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

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

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

Разрешение выполнения цикличных операций

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

  1. Прежде всего, перемещаемся во вкладку «Файл» приложения Excel.
  2. Далее щелкаем по пункту «Параметры», расположенному в левой части открывшегося окна.
  3. Происходит запуск окна параметров Эксель. Нам нужно перейти во вкладку «Формулы».
  4. Именно в открывшемся окне можно будет произвести разрешение выполнения цикличных операций. Переходим в правый блок этого окна, где находятся непосредственно сами настройки Excel. Мы будем работать с блоком настроек «Параметры вычислений», который расположен в самом верху.

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

    Итак, устанавливаем галочку около параметра «Включить итеративные вычисления», а затем, чтобы новые настройки вступили в силу, жмем на кнопку «OK», размещенную в нижней части окна параметров Excel.

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

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

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

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

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

Помогла ли вам эта статья?

Да Нет

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

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

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

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

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

Способ 2: стрелка трассировки

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

  1. В диалоговом окне, сообщающем о наличии циклических ссылок, жмем на кнопку «OK».
  2. Появляется стрелка трассировки, которая указывает зависимости данных в одной ячейки от другой.

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

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

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

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

Помогла ли вам эта статья?

Да Нет

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 нужно когда программа не может провести вычисление книг, так как если есть циклическая ссылка, то в таком случае формула ссылается на себя. Рассмотрим несколько способов как её найти и выключить.

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

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

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

Рубрика Excel

Также статьи о ссылках в Экселе:

  • Ссылка на лист в формуле Excel
  • Как убрать гиперссылки в Excel?
  • Как сделать ссылку на ячейку в другом файле Excel?
  • Ссылка на файл в Эксель

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

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

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

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

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

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

word-office.ru

Как проверить формулы в Excel.

Как проверить формулы в Excel. — EXCEL-OFFICE.RU





Формулы в Excel Как проверить формулы в Excel.

      Рассмотрим, как проверить формулу в Excel, как найти циклическую ссылку, т. д. Когда формулы большие, трудно понять, на каком этапе вычисления сделали ошибку. Но в Excel есть функция, которая поможет это сделать. «Как написать формулу в Excel» смотрите, какими способами и какие формулы можно написать. 
      Если формула составлена с ошибкой, то в ячейке с формулой будет стоять зеленый треугольник.
     Есть несколько способов проверить поэтапный расчет формул в Excel
Итак, заходим на закладку «Формулы» в раздел «Зависимости формул». Сначала выделяем ячейку, затем нажимаем кнопку соответствующей функции. При работе с формулами, удобнее проверить формулу, когда визуально видны все ячейки, указанные в формуле.
      Кнопка «Влияющие ячейки» — показывает стрелками, из каких ячеек цифры считаются  формулой в выделенной ячейки. Здесь, в ячейке Е52 стоит формула. Стрелками показаны ячейки, которые посчитались, чтобы получился результат в ячейке Е52.
             Кнопка «Зависимые ячейки» — показывает стрелками, в каких ячейках (в каких формулах) используется цифра из этой ячейки.
Здесь, в примере, ячейка D49 установлена только в одной формуле, расположенной в ячейке D52.А здесь, от цифры в ячейке D52 зависит результат двух ячеек. В двух формулах, в ячейках D53 и Е52, указана ячейка D52.
      Кнопка «Убрать стрелки» убирает стрелки.
     Чтобы вместо чисел, в ячейках Excel были написаны формулы, нажимаем кнопку «Показать формулы» (на рисунке обведена красным цветом). 
Получилось так.
Чтобы убрать формулы и показать результат в ячейках Excel, снова нажимаем на кнопку «Показать формулы».
Если пишем много разных формул, то можем ошибочно в формулу поставить адрес самой ячейки (в которой пишем формулу) – получится циклическая ссылка.
      Циклическая ссылка в Excel – это, когда в формуле указан адрес ячейки, в которой записана эта формула, т.е. ячейка ссылается сама на себя.
      Так делать нельзя, это ошибка, п.ч. при вычислении по такой формуле будут происходить бесконечные вычисления. Тогда выходит окно с предупреждением о циклической ссылке.Нам нужно найти эту формулу, где есть циклическая ссылка, и исправить её.
      Как найти циклическую ссылку в Excel.
Нажимаем в диалоговом окне «Предупреждение о циклической ссылке» кнопку «Отмена». Нажимаем на стрелку, расположенную рядом с кнопкой «Проверка наличия ошибок» (на рисунке обведена зеленым цветом), наводим мышку на слова «Циклические ссылки» и рядом появляется адрес ячейки, где находится эта циклическая ссылка. 
В нашем случае, в ячейке Е43 стоит такая формула.
Ячейка ссылается сама на себя. Нашли ошибку – в ячейке Е43 стоит ссылка на ячейку Е43 (саму на себя). Исправили ошибку так – изменили ссылку в формуле с ячейки Е43 на ячейку D43.  Получилось так.
Все исправили, все считает правильно.
      Кнопка «Вычислить формулу»  — считает по формуле, но выдает результат, постепенно считая каждое действие, каждый этап вычисления.  У нас такая формула.  Мы её проверяем. Выделяем ячейку с этой формулой, нажимаем кнопку «Вычислить формулу». Появилось такое диалоговое окно.В нем подчеркнут адрес первой ячейки, указанной в формуле. Нажимаем кнопку «Вычислить». Вместо адреса ячейки D52 (были подчеркнуты) теперь стоит цифра из этой ячейки. Теперь подчеркнут адрес ячейки Е52. Нажимаем кнопку «Вычислить». Получилось так.
Если нажмем ещё раз кнопку «Вычислить», то первые две цифры сосчитаются по формуле. У нас, в примере, 6-2=4. Получилось так.И так до конца формулы.
      О других, часто встречающихся ошибках в формулах, что они означают и как их справить, читайте в статье «Ошибки в формулах Excel».
      В Excel можно проводить подсчет, анализ данных, составляя формулы с определенными условиями. Смотрите статью «Функция «СЧЁТЕСЛИ» в Excel».




www.excel-office.ru

как их найти, понять и исправить

Дата: 24 декабря 2015
Категория: Excel

Поделиться, добавить в закладки или распечатать статью

Ошибки в Экселе – непременный спутник всех, кто пишет формулы. Когда выражение в ячейке рассчитать невозможно, программа отображает в ячейке сообщение об ошибке. Оно начинается со знака «#», после которого записывается имя ошибки. Этого не нужно пугаться, если вы ориентируетесь в функциях Эксель и умеете следовать простейшей логике математических операций – легко найдёте и исправите ошибку.

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

Типы ошибок

Если ошибка всё же произошла, помочь в её исправлении поможет расшифровка:

ОшибкаОписание
#ДЕЛ/0!Ошибка возникает при попытке деление на ноль
#ИМЯ?Программа не может распознать введенное имя. Например, вы ошиблись при написании имени функции, или не заключили текстовую строку в кавычки
#Н/ДДанные недоступны. Например, функция поиска не нашла ни одного значения
#ПУСТО!Вы запросили пересечение диапазонов, которые не пересекаются
#ЧИСЛО!Проблема в одном из числовых значений, которые используются в формуле. Например, Вы пытаетесь извлечь квадратный корень из отрицательного числа. В классической математике эта операция лишена смысла
#ССЫЛКА!Формула содержит ссылку, которой нет. Например, вы удалили ячейку, на которую она ссылается
#ЗНАЧ!Формула содержит недопустимые компоненты. Часто такая ошибка возникает при нарушении синтаксиса формул

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

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

Отслеживание ошибки через этапы вычисления

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

Еще один тип ошибки – это циклическая ссылка. Она возникает, когда вы ссылаетесь на ячейку, значение которой зависит от той, в которой вы пишете формулу. Например, в клетке А1 записана формула =А2+1, а в А2 написать =A1, возникнет циклическая ссылка, которая будет пересчитываться бесконечно. В этом случае, программа предупреждает о появлении циклической ссылки, останавливает расчет «зацикленных формул». В левой части ячеек появляется двунаправленная стрелка. Придётся исправить возникшую ошибку и повторить расчет.

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

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

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

Отслеживание циклических ссылок

В Excel можно попытаться рассчитать результат зацикленных формул. Для этого установите галочку Файл – Параметры – Формулы – Включить итеративные вычисления. В этом же блоке можно задать максимальное количество итераций (просчётов) для нахождения баланса и допустимую погрешность. В большинстве случаев этого делать не нужно, поэтому я рекомендую эту галку не устанавливать. Тем не менее, когда Вы знаете, что зацикленные формулы верны и их расчет приведет к устойчивому результату — почему бы это не сделать?

Вот и всё про типы ошибок в Эксель. В этой короткой статье вы получили достаточно информации, чтобы справиться с самыми распространенными ошибками в Excel путем анализа возвращаемого значения. А вот расширенный перечень ошибок читайте в моей новой статье! Готов ответить на ваши вопросы – пишите в комментариях.

В следующей статье я расскажу о применении функций в расчетах. Стоит ли говорить, что функции Эксель — «наше всё»?
Думаю, нет. Потому, переходите и читайте, это будет первый шаг в мир сложных формул с правильными результатами!

Поделиться, добавить в закладки или распечатать статью

officelegko.com

как удалить циклическую ссылку в excel видео Смотреть видео

3 лет назад

Циклическая ссылка в Excel последних версий не будет выполняться бесконечно и сразу блокируется. При обнаруж…

5 лет назад

Нужно быстро удалить (отредактировать) много ссылок в документе Excel? Посмотрите данное видео, которое покаж…

4 лет назад

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

2 лет назад

Бесплатный мастер-класс по SEO Регистрация здесь https://goo.gl/k2dKBQ ✅ Хочешь гарантированно поднять свой…

2 лет назад

Циклические ссылки надо убирать. По крайней мере, так говорят сеошники:) Вот и разберемся как это сделать…

3 лет назад

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

3 лет назад

Ссылка на ячейку в другом файле Excel позволяет сложить ячейки из разных файлов в Excel. Сделать ссылку на ячейку…

4 лет назад

Подробная статья на блоге: http://kostyakhmelev.ru/texnicheskaya-chast/tsiklicheskie-i-dubliruyushhie-ssyilki-na-sayte.html.

2 лет назад

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

2 лет назад

Excel#22 Гиперссылка в Excel.

1 лет назад

Тайм-менеджмент в Excel — это нужно всем: http://www.subex.su Вступайте в группу ВК: https://vk.com/excel_subex Ссылка для скачивания…

3 лет назад

Защитить отдельные ячейки в Excel не затронув при этом другие не так просто. Любая настроенная защита включае…

2 лет назад

Робота з VBA Excel набагато полегшує виконання однотипних завдань, а у вправних руках — це сильний оператор,…

3 лет назад

Ссылка на файлв в Excel позволяет сделать подобие каталога и одним нажатием на ссылку открывать любой файл…

3 лет назад

При работе с электронными таблицами Excel возникают ситуации, когда при открытии книги столбцы вместо привыч…

2 лет назад

Использование гиперссылки для ускорения навигации в Excel. Для тех , кто хочет получить более глубокие знания…

2 лет назад

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

7 лет назад

Из этого видео вы узнаете, что такое абсолютные и относительные ссылки в Excel, для чего они нужны и как с ними…

3 лет назад

Гиперссылки в Excel, горячее сочетание клавиш для изменения гиперссылки, анкорная и безанкорная гиперссылка….

3 лет назад

JOIN VSP GROUP PARTNER PROGRAM: https://youpartnerwsp.com/ru/join?87632.

video-base.ru

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

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

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

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

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

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

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

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

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


www.softo-mir.ru

alexxlab

    Отправить ответ

    avatar
      Подписаться  
    Уведомление о