Меню

Excel 2016 циклические ссылки как найти

Удаление или разрешение циклической ссылки

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

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

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

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

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

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

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

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

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

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

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

Можно перемещаться между ячейками в циклической ссылке, дважды щелкнув стрелку трассировки. Стрелка указывает ячейку, которая влияет на значение выбранной ячейки. Чтобы отобразить стрелку трассировки, щелкните «Формулы», а затем выберите «Трассировка » или «Зависимые от трассировки».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник статьи: http://support.microsoft.com/ru-ru/office/%D1%83%D0%B4%D0%B0%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5-%D0%B8%D0%BB%D0%B8-%D1%80%D0%B0%D0%B7%D1%80%D0%B5%D1%88%D0%B5%D0%BD%D0%B8%D0%B5-%D1%86%D0%B8%D0%BA%D0%BB%D0%B8%D1%87%D0%B5%D1%81%D0%BA%D0%BE%D0%B9-%D1%81%D1%81%D1%8B%D0%BB%D0%BA%D0%B8-8540bd0f-6e97-4483-bcf7-1b49cd50d123

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

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

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

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

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

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

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

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

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

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

    В любой элемент листа записываем число. Пусть это будет ячейка A1, а число 5.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Источник статьи: http://lumpics.ru/working-circular-references-excel/

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

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

    ​Смотрите также​: а вот и​ обновлять?​ ячейке​ называется циклической ссылкой.​ значения рассчитываются корректно.​ операций. Переходим в​ объекты из документа.​ просто избыточное использование​. Выделяем её и​ оно расположено, а​ файла Excel у​Enter​ на элемент​ и вычисления, что​ довольно просто, особенно​имеется кнопка​Циклические ссылки представляют собой​ наш файл))) таблицу​Guest​C1​ Такого быть не​ Программа не блокирует​ правый блок этого​ В обратном случае,​ ссылок, которое приводит​

    ​ смотрим на строку​ на другом, то​

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

    ​ вас открывается информационное​.​B1​ создает дополнительную нагрузку​ если знать алгоритм​«Проверка наличия ошибок»​ формулу, в которой​ все также глючит,​: если «не обновлять»,​косвенно ссылается на​ может!​ вычисления в них.​

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

    1. ​ окна, где находятся​ нужно будет применить​ к зацикливанию. Во​ формул. Как видим,​ в этом случае​ окно о том,​У нас получилась первая​:​ на систему.​ поиска. Можно воспользоваться​

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

    ​ Excel. Мы будем​ элементам, которые находятся​ того, какую ячейку​ значение (​

  • ​ будет отображаться только​ циклическую ссылку, то​ в которой привычно​Жмем на кнопку​ простейшее цикличное выражение.​ способов нахождения подобных​ треугольника рядом с​ другими ячейками, в​
  • Способ 2: стрелка трассировки

      ​на рисунке ниже​ операций злоупотреблять не​ работать с блоком​ в списке, тем​​ следует отредактировать, нужно​​1000​

  • ​ сообщение о наличие​ её желательно отыскать.​ обозначена стрелкой трассировки.​Enter​
  • ​ Это будет ссылка,​ зависимостей. Несколько сложнее​ этой кнопкой. В​ конечном итоге ссылается​Файл удален​vikttur​Если вы создадите​ ссылается на ячейку​ стоит. Применять данную​

    ​ настроек​ же рассматриваемым ранее​ применять логику. Тут​), которое не является​ ошибки без адреса.​ Для этого перемещаемся​ Но как видим,​.​ расположенная в той​ определить, нужна ли​ появившемся меню выбираем​ сама на себя.​- велик размер.​: Циклическая ссылка –​

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

    ​A3​ возможность следует только​«Параметры вычислений»​ способом.​ нет четкого алгоритма​ продуктом вычисления формулы.​Урок: Как найти циклические​ во вкладку​ результат ошибочен и​Таким образом, цикл замкнулся,​ же ячейке, на​ данная формула в​ пункт​ В некоторых случаях​ [Модераторы]​ вычисления в формулах​ Excel вернёт «0».​

    ​(т.е. на саму​ тогда, когда пользователь​

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

    ​, который расположен в​В предшествующей части урока​ действий. В каждом​ Поэтому можно с​ ссылки в Excel​«Формулы»​ равен нулю, так​ и мы получили​ которую она ссылается.​ действительно или это​«Циклические ссылки»​ пользователи осознано применяют​Guest​

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

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

    ​ просто ошибка, а​. После перехода по​ подобный инструмент для​: ой,​ себя (вычисления «по​ в документе, на​

      ​ не может.​​ её необходимости. Необоснованное​​Чтобы разрешить применение цикличных​ основном, как бороться​

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

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

  • ​ кругу»). Такую проверку​ вкладке​Примечание:​ включение цикличных операций​ выражений, нужно установить​
  • ​ с циклическими ссылками,​Например, если в нашей​ содержит ошибки, вызывающей​

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

    ​ подход может помочь​​Андрей​​ можно отключить, включив​

    ​Если вы создадите​​ может не только​​ галочку около параметра​ или как их​

    ​ создание циклических операций.​ – это зло,​​ кнопки​​ операций.​ мы видим, что​​ нем следующее выражение:​​Автор: Максим Тютюшев​ все координаты ссылок​​ при моделировании. Но,​​: Ловите. В ячейки​

    ​ итерации (Меню Сервис-Параметры-Вычисления-Итерации-включить​

    ​(Формулы) кликните по​​ такую циклическую ссылку,​​ привести к избыточной​

  • ​«Включить итеративные вычисления»​ найти. Но, ранее​ должна вычисляться путем​Переходим к следующей ячейке​ от которого следует​«Проверка наличия ошибок»​Скопируем выражение во все​ программа пометила цикличную​=A1​Принято считать, что циклические​
  • ​ циклического характера в​ в большинстве случаев,​ выделенные красным нормальные​ (поставить галку)). Но​ стрелке вниз рядом​ Excel вернёт «0».​ нагрузке на систему​. Кроме того, в​ разговор шел также​ умножения количества фактически​ (​ избавляться. Поэтому, закономерно,​, расположенной в блоке​ остальные ячейки столбца​ связь синими стрелками​Далее жмем на кнопку​ ссылки в Экселе​ данной книге. При​

      ​ данная ситуация –​ формулы внесите.​ осторожно! – могут​ с иконкой ​Ещё один пример. Формула​​ и замедлить вычисления​​ этом же блоке​ о том, что​​ проданного товара на​​B6​ что после того,​ инструментов​ с количеством продукции.​ на листе, которые​​Enter​​ представляют собой ошибочное​​ клике на координаты​​ это просто ошибка​

    ​ быть ошибки в​​Error Checking​​ в ячейке​

    ​ при работе с​ можно настроить предельное​ в некоторых случаях​ его цену, то​). После выделения в​ как цикличная связь​«Зависимости формул»​ Для этого устанавливаем​ называются стрелками трассировки.​на клавиатуре.​ выражение. Действительно, довольно​

    ​ конкретной ячейки, она​ в формуле, которую​- велик размер.​ расчетах, если циклические​(Проверка наличия ошибок)​С2​ документом, но пользователь​ число итераций и​ они, наоборот, могут​ можно сказать, что​ строке формул мы​ обнаружена, нужно её​. Открывается меню, в​ курсор в нижний​Теперь перейдем к созданию​

  • ​После этого появляется диалоговое​ часто это именно​ становится активной на​ юзер допустил по​ [Модераторы]​ сделаны не специально.​ и нажмите ​
  • Поиск циклических ссылок

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

      ​ умолчанию их значения​ осознанно использоваться пользователем.​ от общей суммы​ содержит вычисляемое выражение​ формулу к нормальному​ курсор на пункт​ элемента, который уже​ примере таблицы. У​ циклическом выражении. Щелкаем​​ не всегда. Иногда​​Путем изучения результата устанавливаем​ другим причинами. В​ такой проблемой как​ формулах.​​(Циклические ссылки).​​C1​ которое по умолчанию​​ равны 100 и​​ Например, довольно часто​ продажи, тут явно​ (​​ виду.​​«Циклические ссылки»​ содержит формулу. Курсор​ нас имеется таблица​ в нем по​ они применяются вполне​ зависимость и устраняем​

  • ​ связи с этим,​ циклические ссылки. Документ​Таня​Урок подготовлен для Вас​
  • ​.​ тут же было​ 0,001 соответственно. В​ данный метод применяется​ лишняя. Поэтому мы​=D6/C6​Для того, чтобы исправить​. После этого в​ преобразуется в крестик,​ реализации продуктов питания.​ кнопке​ осознанно. Давайте выясним,​ причину цикличности, если​ чтобы удалить ошибку,​ Excel открывается без​: «сервис — параметры»​ командой сайта office-guru.ru​Формула в ячейке​ бы заблокировано программой.​ большинстве случаев данные​

    ​ для итеративных вычислений​ её удаляем и​), которое подтягивает данные​ цикличную зависимость, нужно​ следующем меню открывается​ который принято называть​ Она состоит из​«OK»​ чем же являются​ она вызвана ошибкой.​ следует сразу найти​ проблем, но в​

    ​ не активны, когда​Источник: http://www.excel-easy.com/examples/circular-reference.html​

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

    ​C3​Как мы видим, в​ параметры изменять не​ при построении экономических​ заменяем на статическое​ из других элементов​ проследить всю взаимосвязь​ список адресов элементов​ маркером заполнения. Зажимаем​ четырех колонок, в​.​ циклические ссылки, как​

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

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

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

    ​ продукции, цена и​ листе, в которой​​ в документе, как​​ циклических ссылок. На​Скачать последнюю версию​ без ячеек. Кто​ пустота серая)​: Ребята, Помогите, Плиз. ​​Формула в ячейке​​ которым нужно бороться.​ изменения в указанные​ того, осознанно или​ цикличными выражениями, если​​. Таким образом, ячейка​​ может крыться не​​При клике на конкретный​​ таблицы вниз.​ сумма выручки от​​ ячейка ссылается сама​​ работать с ними​ этот раз соответствующий​

    ​ Excel​ сталкивался — тот​Таня​Вопрос жизни и​C4​ Для этого, прежде​ поля. Но тут​ неосознанно вы используете​ они имеются на​D6​ в ней самой,​ адрес происходит выделение​Как видим, выражение было​

    ​ продажи всего объема.​ на себя.​ или как при​​ пункт меню должен​​Если в книге присутствует​​ знает что это​​: ЭТО УЖАС КАКОЙ-ТО!!​ смерти!​ссылается на ячейку​ всего, следует обнаружить​ нужно учесть, что​ циклическое выражение, Excel​ листе. После того,​ссылается на данные​ а в другом​ соответствующей ячейки на​ скопировано во все​ В таблице в​Немного усложним задачу и​ необходимости удалить.​

    ​ быть вообще не​ циклическая ссылка, то​ такое, остальным объяснять​Пытаюсь открыть файл​была таблица объемная​С3​ саму цикличную взаимосвязь,​ слишком большое количество​ по умолчанию все​ как абсолютно все​ элемента​ элементе цепочки зависимости.​ листе.​ элементы столбца. Но,​

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

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

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

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

      ​В диалоговом окне, сообщающем​ Так что с​​ добился. Знающие люди,​​ не отображает. ​

    ​ с табл 3.​​C1​​ коррективы. Но в​ особенно если вы​

    ​ системы. В таком​ исчезнуть из строки​ довольно быстро, но​​ ячеек цикла (​​ и адрес элемента,​

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

    ​ состояния.​ в реальности бывают​D6​​ содержащего подобное выражение,​​ выше, не во​ первой строчке, выделяем​, а число​ которое посредством формул​ ссылок, жмем на​ такой формулы проблем​Brownie​ их поменьше (10)​ не открывает Табл1,​Пояснение:​ операции могут быть​ в котором размещено​ вопрос принудительного отключения​Кроме того, полностью ли​ случаи, когда в​), реальная ошибка кроется​ располагается в левой​ всех случаях программа​ элемент листа с​5​ в других ячейках​ кнопку​ не возникнет. Как​: Привет! Просто какой-то​ и погрешность увеличила,​

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

  • ​.​ ссылается само на​«OK»​ же найти проблемную​ ячейке в формуле​ чтоб быстрее соображал,​ ничего! говорит, сначала,​С1​ и производиться пользователем​
  • ​Итак, устанавливаем галочку около​ как это сделать.​ выражения, можно узнать,​ множество ячеек, а​ Выделяем элемент​ которая находится внизу​ ссылки с объектами,​ счету товара (​В другую ячейку (​ себя. Так же​.​ область на листе?​ ссылка на саму​в итоге, в​ что «эта таблица​ссылается на ячейку​ осознанно. Но даже​ параметра​Прежде всего, перемещаемся во​ воспользовавшись инструментом проверки​

    ​ не три элемента,​D6​ окна Excel. Правда,​ даже если она​B2​B1​ ею может являться​Появляется стрелка трассировки, которая​Чтобы узнать, в каком​ себя. Открой Вид​ строке состояния вместо​ содержит связи с​C4​ тогда стоит к​«Включить итеративные вычисления»​ вкладку​ наличия ошибок. Переходим​ как у нас.​, чтобы узнать, из​ в отличие от​ имеется на листе.​). Вместо статического значения​) записываем выражение:​ ссылка, расположенная в​ указывает зависимости данных​ именно диапазоне находится​ — Панели инструментов​ «Цикл» стало «Вычислить»,​

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

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

      ​ предыдущего варианта, на​​ Учитывая тот факт,​​ (​=C1​​ элементе листа, на​​ в одной ячейки​ такая формула, прежде​ — Зависимости. Включай/выключай​

    ​ но попрежнему серый​​ нажимаю «Обновить», потом​С4​ с осторожностью, правильно​

      ​ новые настройки вступили​приложения Excel.​​«Формулы»​​ занять довольно много​ подтягивает значение. Смотрим​​ строке состояния отображаться​​ что в подавляющем​

    ​6​​В следующий элемент (​​ который она сама​​ от другой.​​ всего, жмем на​

    ​ кнопки Влияющие ячейки,​​ фон, сами листы​​ возникает сообщение, что​​ссылается на ячейку​​ настроив Excel и​

    ​ в силу, жмем​Далее щелкаем по пункту​и жмем уже​​ времени, ведь придется​​ на выражение в​

    ​ будут адреса не​

    • ​ большинстве цикличные операции​​) вписываем туда формулу,​​C1​​ ссылается.​​Нужно отметить, что второй​
    • ​ кнопку в виде​​ Зависящие ячейки и​​ документа не отбражаются. ​​ «невозможно вычислить формулу.​
    • ​C3​​ зная меру в​​ на кнопку​​«Параметры»​
    • ​ знакомый нам треугольник​​ изучить каждый элемент​​ строке формул. Как​​ всех элементов, содержащих​
    • ​ вредны, их следует​ которая будет считать​​) производим запись такой​​Нужно отметить, что по​ способ более визуально​ белого крестика в​

    ​ походи по ячейкам​​Что значит «Вычислить»​ ячейка в формуле​.​

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

    ​ красном квадрате в​ таблицы. Стрелки помогут​
    ​ . ​
    ​ ссылается на результат​

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

    ​ которые при использовании​​, размещенную в нижней​

    ​ части открывшегося окна.​«Проверка наличия ошибок»​

    ​Теперь нам нужно понять,​ этом элементе листа​ их много, а​ этого их нужно​ деления общей суммы​=A1​

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

    ​ сначала отыскать. Как​ (​После этого возвращаемся в​ процесс выполнения цикличной​

    ​ не всегда даёт​​ тем самым закрывая​ Удачи.​

    ​: поставьте вообще 1​​ ссылку.»​C2​ способны замедлить работу​ Excel.​

    ​ Эксель. Нам нужно​​«Зависимости формул»​ ячейке (​ содержимого ячеек​ них, который появился​ же это сделать,​D2​ ячейку​ операции. Это связано​ четкую картину цикличности,​ его.​Не нажимать​vikttur​
    ​в нажимаю ОК.​.​

    ​ системы.​​После этого мы автоматически​ перейти во вкладку​. Если в запустившемся​B6​B6​ раньше других.​

    ​ если выражения не​​) на цену (​

    ​A1​ с тем, что​ в отличие от​Переходим во вкладку​: Создай новую книгу,​:​

    ​ И НИЧЕГО. Ничего​С2​Автор: Максим Тютюшев​ переходим на лист​

    ​«Формулы»​ меню пункт​или​и​К тому же, если​ помечены линией со​

    ​ такие выражения в​​ первого варианта, особенно​

    ​«Формулы»​​ скопируй туда свои​

    ​Таня, пора показать файл.​ не отображает. ОЧЕНЬ​ссылается на ячейку​

    ​Формула в ячейке, которая​​ текущей книги. Как​.​«Циклические ссылки»​D6​C6​ вы находитесь в​
    ​ стрелками? Давайте разберемся​​):​ число​

    ​ подавляющем большинстве ошибочные,​​ в сложных формулах.​

    ​ данные, циклическую ссылку​​ Подозреваю, он немалентький,​ СТРАШНО ПОТЕРЯТЬ Табл-1. ​C1​
    ​ прямо или косвенно​​ видим, в ячейках,​Именно в открывшемся окне​

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

    ​не будет активен,​) содержится ошибка. Хотя,​.​ книге, содержащей цикличное​ с этой задачей.​=D2/C2​5​ а зацикливание производит​Как видим, отыскать циклическую​ блоке инструментов​ — не копируй,​ поэтому сначала сюда:​Guest​
    ​.​ ссылается на эту​ в которых располагаются​ можно будет произвести​ то, значит, мы​

    ​ формально это даже​​Переходим к ячейке​ выражение, не на​Итак, если при запуске​Щелкаем по кнопке​. Ссылаемся в ней​ постоянный процесс пересчета​ ссылку в Эксель​«Зависимости формул»​ все должно получиться. ​Таня​: а если не​Другими словами, формула в​

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

    Источник статьи: http://my-excel.ru/voprosy/kak-v-excel-najti-ciklicheskuju-ssylku.html

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Заключение

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

    Источник статьи: http://microexcel.ru/cziklicheskaya-ssylka/

    Циклическая ссылка в Excel. Как найти и удалить — 2 способа

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

    Что такое циклическая ссылка

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

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

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

    Визуальный поиск

    Самый простой метод поиска, который подойдет при проверке небольших таблиц. Порядок действий:

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

    Обозначение проблемных ячеек стрелкой трассировки

    1. Чтобы убрать цикличность, необходимо зайти в обозначенную ячейку и исправить формулу. Для этого необходимо убрать координаты конфликтной клетки из общей формулы.
    2. Останется перевести курсор мыши на любую свободную ячейку таблицы, нажать ЛКМ. Циклическая ссылка будет удалена.

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

    Использование инструментов программы

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

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

    Меню для отображения всех циклических ссылок с их координатами по ячейкам

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

    Если ошибок нет, пункт поиска циклических ссылок выбрать нельзя

    Отключение блокировки и создание циклических ссылок

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

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

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

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

    Самый простой вариант создания циклической ссылки – выделить любую клетку таблицы, в нее вписать знак «=», сразу после которого добавить координаты этой же ячейки. Чтобы усложнить задачу, расширить циклическую ссылку на несколько ячеек, нужно выполнить следующий порядок действий:

    1. В клетку А1 добавить цифру «2».
    2. В ячейку В1 вписать значение «=С1».
    3. В клетку С1 добавить формулу «=А1».
    4. Останется вернуться в самую первую ячейку, через нее сослаться на клетку В1. После этого цепь из 3 ячеек замкнется.

    Заключение

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

    Источник статьи: http://office-guru.ru/excel/ciklicheskaya-ssylka-v-excel-kak-najti-i-udalit-2-sposoba.html

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Источник статьи: http://excel-training.ru/tsiklicheskie-ssyilki-v-excel/

    Циклическая ссылка в Excel. Как найти и удалить — 2 способа

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

    Что такое циклическая ссылка

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

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

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

    Окно предупреждения о наличии циклических ссылок в таблице

    Визуальный поиск

    Это простейший метод поиска, который работает при проверке небольших таблиц. Процедура:

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

    Обозначьте проблемные ячейки стрелкой следа

    1. Чтобы убрать цикличность, нужно перейти в указанную ячейку и исправить формулу. Для этого нужно удалить конфликтующие координаты ячеек из общей формулы.
    2. Осталось переместить курсор мыши в любую свободную ячейку таблицы, нажать ЛКМ. Циклическая ссылка будет удалена.

    Правильный вариант после удаления круговой ссылки

    Использование инструментов программы

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

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

    Меню для отображения всех круговых ссылок с координатами их ячеек

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

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

    Отключение блокировки и создание циклических ссылок

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

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

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

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

    Окно настроек блокировки циклических ссылок, их количество разрешено в документе

    1. Чтобы изменения вступили в силу, необходимо нажать кнопку «ОК». После этого программа перестанет автоматически блокировать вычисления в ячейках, связанных круговыми ссылками.

    Самый простой способ создать круговую ссылку — выбрать любую ячейку в таблице, ввести знак «=» сразу после добавления координат той же ячейки. Чтобы усложнить задачу, чтобы расширить круговую ссылку на большее количество ячеек, необходимо выполнить следующую процедуру:

    1. Добавьте цифру «2» в ячейку A1».
    2. Введите значение «= C1» в ячейку B1».
    3. Добавьте формулу «= A1» в ячейку C1».
    4. Осталось вернуться к самой первой ячейке, через которую обращаться к ячейке B1. После этого цепочка из 3 ячеек замкнется.

    Заключение

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

    Источник статьи: http://excel-home.ru/articles/ciklicheskaya-ssylka-v-excel-kak-nayti-i-udalit-2-sposoba/

    VBA Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Параметры вычислений

    Следующий список поясняет опции, которые доступны в разделе Calculation options (Параметры вычислений):

    • Automatic (Автоматически) – пересчитывает все зависимые формулы и обновляет все открытые или внедрённые диаграммы при любом изменении значения, формулы или имени. Данная настройка установлена по умолчанию для каждого нового рабочего листа Excel.
    • Automatic except for data tables (Автоматически, кроме таблиц данных) – пересчитывает все зависимые формулы и обновляет все открытые или внедрённые диаграммы, за исключением таблиц данных. Для пересчета таблиц данных, когда данная опция выбрана, воспользуйтесь командой Calculate Now (Пересчет), расположенной на вкладке Formulas (Формулы) или клавишей F9.
    • Manual (Вручную) – пересчитывает открытые рабочие листы и обновляет открытые или внедрённые диаграммы только при нажатии команды Calculate Now (Пересчет) или клавиши F9, а так же при использовании комбинации клавиши Ctrl+F9 (только для активного листа).
    • Recalculate workbook before saving (Пересчитывать книгу перед сохранением) – пересчитывает открытые рабочие листы и обновляет открытые или внедрённые диаграммы при их сохранении даже при включенной опции Manual (Вручную). Если Вы не хотите, чтобы при каждом сохранении зависимые формулы и диаграммы пересчитывались, просто отключите данную опцию.
    • Enable iterative calculation (Включить итеративные вычисления) – разрешает итеративные вычисления, т.е. позволяет задавать предельное количество итераций и относительную погрешность вычислений, когда формулы будут пересчитываться при подборе параметра или при использовании циклических ссылок. Более детальную информацию о подборе параметров и использовании циклических ссылок можно найти в справке Microsoft Excel.
    • Maximum Iterations (Предельное число итераций) – определяет максимальное количество итераций (по умолчанию – 100).
    • Maximum Change (Относительная погрешность) – устанавливает максимально допустимую разницу между результатами пересчета (по умолчанию – 0.001).

    Вы также можете переключаться между тремя основными режимами вычислений, используя команду Calculation Options (Параметры вычислений) в разделе Calculation (Вычисление) на вкладке Formulas (Формулы). Однако, если необходимо настроить параметры вычислений, все же придется обратиться к вкладке Formulas (Формулы) диалогового окна Excel Options (Параметры Excel).

    Руководство по проверке данных Excel

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

    • значение является числом от 1 до 6
    • дата произойдет в следующие 30 дней
    • текстовая запись содержит менее 25 символов

    Проверка данных может просто отображать сообщение пользователю с информацией, что разрешено, как показано ниже:

    Сообщение отображается автоматически при выборе ячейки

    Проверка данных также может остановить неправильный ввод данных пользователем. Например, если код сотрудника не проходит проверку, вы можете увидеть следующее сообщение:

    Пример сообщения об ошибке

    Кроме того, проверка данных может использоваться для предоставления пользователю определенного выбора в раскрывающемся меню:

    Пример раскрывающегося меню проверки данных

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

    Контроль достоверности данных

    Проверка данных осуществляется с помощью правил, определенных в пользовательском интерфейсе Excel на вкладке «Данные» на ленте.

    Элементы управления проверкой данных на вкладке ДАННЫЕ

    Важное ограничение

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

    Определение правил проверки данных

    Проверка данных определяется в окне с 3 вкладками: Параметры, Сообщение для ввода и Сообщение об ошибке:

    Окно проверки данных имеет три основные вкладки

    На вкладке Параметры вы можете ввести критерии проверки. Существует ряд встроенных правил проверки с различными параметрами, также можно выбрать «Другой» и использовать собственную формулу для проверки ввода, как показано ниже:

    Пример вкладки настроек проверки данных

    Вкладка «Сообщение для ввода» определяет сообщение, отображаемое при выборе ячейки с правилами проверки. Оно не является обязательным.

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

    Входное сообщение не влияет на то, что пользователь может ввести — оно просто отображает сообщение, чтобы сообщить пользователю, что разрешено или ожидается.

    Вкладка настройки сообщения проверки данных

    Вкладка «Сообщение об ошибке» определяет, как выполняется проверка. Например, когда вид установлен на «Останов», неверные данные вызывают окно с сообщением, и ввод не разрешен.

    Вкладка предупреждения об ошибке проверки данных

    Пользователь видит сообщение, подобное этому:

    Пример сообщения об ошибке проверки данных

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

    Не позволяет пользователям вводить недопустимые данные в ячейку. Пользователи могут повторить попытку, но должны ввести значение, которое проходит проверку данных. В окне предупреждения «Останов» есть три опции: «Повторить», «Отмена» и «Справка».

    Предупреждает пользователей о том, что данные неверны. Предупреждение ничего не делает, чтобы остановить ввод неверных данных. В окне «Предупреждение» есть три параметра: «Да» (для принятия недействительных данных), «Нет» (для редактирования недействительных данных), «Отмена» (для удаления недействительных данных) и «Справка».

    Параметры проверки данных

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

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

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

    Действительное — работает как опция целого числа, но допускает десятичные значения. Например, если для параметра «Действительное» задано значение от 0 до 3, допустимы все значения, такие как 0,5 и 2,5.

    Список — разрешены только значения из предварительно определенного списка. Значения представляются пользователю как выпадающее меню. Допустимые значения могут быть жестко заданы непосредственно на вкладке «Параметры» или указаны в виде диапазона на рабочем листе.

    Дата — разрешены только даты. Например, вам может потребоваться дата между 1 января 2018 года и 31 декабря 2021 года или дата после 1 июня 2018 года.

    Время — разрешено только время. Например, вы можете указать время между 9:00 и 17:00 или разрешить время только после 12:00.

    Длина текста — проверяет ввод на основе количества символов или цифр. Например, вам может потребоваться код из 5 цифр.

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

    На вкладке параметров также есть два флажка:

    Игнорировать пустые ячейки — говорит Excel не проверять ячейки, которые не содержат значений. На практике этот параметр влияет только на команду «Обвести неверные данные». Когда эта опция включена, пустые ячейки не обведены, даже если они не прошли проверку.

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

    Простое выпадающее меню

    Вы можете предоставить пользователю раскрывающееся меню опций, жестко закодировав значения в поле настроек или выбрав диапазон на листе. Например, чтобы ограничить записи действиями «ПРИНЯТ», «В ОБРАБОТКЕ» или «ОТГРУЖЕН», вы можете ввести эти значения через точку с запятой:

    Раскрывающееся меню проверки данных с жестко заданными значениями

    При применении к ячейке на рабочем листе раскрывающееся меню работает следующим образом:

    Используются жестко заданные значения выпадающего меню проверки данных

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

    Значения выпадающего меню проверки данных со ссылкой на диапазон

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

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

    Вы также можете использовать именованные диапазоны для указания значений. Например, с именованным диапазоном под названием «размер» для F4:F6, вы можете ввести имя непосредственно в окне, начиная со знака равенства:

    Значения выпадающего меню проверки данных с именованным диапазоном

    Именованные диапазоны автоматически являются абсолютными, поэтому они не изменятся.

    Вы также можете создавать зависимые выпадающие списки с пользовательской формулой.Совет.

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

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

    Проверка данных с помощью пользовательской формулы

    Формулы проверки данных должны быть логическими формулами, которые возвращают ИСТИНА, если ввод действителен, и ЛОЖЬ, если ввод недействителен. Например, чтобы разрешить ввод любого числа в ячейку A1, вы можете использовать функцию ЕЧИСЛО (ISNUMBER) в формуле, подобной этой:

    Если пользователь вводит значение 10 в A1, ЕЧИСЛО (ISNUMBER) возвращает ИСТИНА, и проверка данных завершается успешно. Если вводится значение типа «яблоко» в A1, ЕЧИСЛО (ISNUMBER) возвращает ЛОЖЬ, и проверка данных завершается неудачно.

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

    Формулы устранения неполадок

    Excel игнорирует формулы проверки данных, которые возвращают ошибки.

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

    Фиктивные формулы — это просто формулы проверки данных, введенные непосредственно на листе, чтобы вы могли легко увидеть, что они возвращают. На приведенном ниже экране показан пример:

    Проверка достоверности данныхс помощью фиктивных формул

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

    Примеры формул проверки данных

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

    Чтобы разрешить только 5 символьных значений, начинающихся с «z», вы можете использовать:

    = И (ЛЕВСИМВ (А1) = «z»; ДЛСТР (A1) = 5)

    Эта формула возвращает ИСТИНА только тогда, когда код длиной 5 цифр и начинается с «z». Два значения в примере выше возвращают ЛОЖЬ с этой формулой.

    Чтобы разрешить ввод даты в течение 30 дней с сегодняшнего дня:

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

    Задание является следующим. В одном из столбцов в разных ячейках находятся какие-то значения (в данном случае текстовые строки “граница”). Они определяют начало и конец секторов (диапазонов). Эти значения вставлены автоматически и могут появляться в разных ячейках. Их размеры и количество в них ячеек также может быть разным. Например, на рисунке ниже выбран сектор данных (диапазон) номер 2.

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

    Динамическое определение границ выборки ячеек

    Для наглядности приведем решение этой задачи с использованием вспомогательного столбца. В первую ячейку в вспомогательном столбце (A7) вводим формулу:

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

    Следующим шагом будет динамическое определение адреса для выборки диапазона данных из исходного списка в соответствии с критерием отбора.

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

    Заполнение диапазона

    Чтобы заполнить диапазон, следуйте инструкции ниже:

    1. Введите значение 2 в ячейку B2.
    2. Выделите ячейку В2, зажмите её нижний правый угол и протяните вниз до ячейки В8.

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

  • Введите значение 2 в ячейку В2 и значение 4 в ячейку B3.
  • Выделите ячейки B2 и B3, зажмите нижний правый угол этого диапазона и протяните его вниз.

    Excel автоматически заполняет диапазон, основываясь на шаблоне из первых двух значений. Классно, не правда ли? Вот еще один пример:

  • Введите дату 13/6/2013 в ячейку В2 и дату 16/6/2013 в ячейку B3 (на рисунке приведены американские аналоги дат).
  • Выделите ячейки B2 и B3, зажмите нижний правый угол этого диапазона и протяните его вниз.
  • Перемещение диапазона

    Чтобы переместить диапазон, выполните следующие действия:

    1. Выделите диапазон и зажмите его границу.
    2. Перетащите диапазон на новое место.

    Копировать/вставить диапазон

    Чтобы скопировать и вставить диапазон, сделайте следующее:

    1. Выделите диапазон, кликните по нему правой кнопкой мыши и нажмите Copy (Копировать) или сочетание клавиш Ctrl+C.
    2. Выделите ячейку, где вы хотите разместить первую ячейку скопированного диапазона, кликните правой кнопкой мыши и выберите команду Paste (Вставить) в разделе Paste Options (Параметры вставки) или нажмите сочетание клавиш Ctrl+V.

    Примеры использования функции АГРЕГАТ в Excel

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

    Для расчета используем следующую формулу:

    • 1 – число, соответствующее функции СРЗНАЧ;
    • 3 – число, указывающее на способ расчета (не учитывать скрытые строки и коды ошибок);
    • B3:B13 – диапазон ячеек с данными для определения среднего значения.

    В результате формула вернула правильное число среднего значения в обход значениям с ошибками #Н/Д.

    Панель формул

    Существует ещё третий способ запустить функцию «СРЗНАЧ». Для этого, переходим во вкладку «Формулы». Выделяем ячейку, в которой будет выводиться результат. После этого, в группе инструментов «Библиотека функций» на ленте жмем на кнопку «Другие функции». Появляется список, в котором нужно последовательно перейти по пунктам «Статистические» и «СРЗНАЧ».

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

    Дальнейшие действия точно такие же.

    Ручной ввод функции

    Но, не забывайте, что всегда при желании можно ввести функцию «СРЗНАЧ» вручную. Она будет иметь следующий шаблон: «=СРЗНАЧ(адрес_диапазона_ячеек(число); адрес_диапазона_ячеек(число)).

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

    Расчет среднего значения

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

    Использование арифметического выражения

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

    1. Встаем в нужную ячейку, ставим знак “равно” и пишем арифметическое выражение по следующем принципу:
      =(Число1+Число2+Число3. )/Количество_слагаемых .
      Примечание: в качестве числа может быть указано как конкретное числовое значение, так и ссылка на ячейку. В нашем случае, давайте попробуем посчитать среднее значение чисел в ячейках B2,C2,D2 и E2.
      Конечный вид формулы следующий: =(B2+E2+D2+E2)/4 .
    2. Когда все готово, жмем Enter, чтобы получить результат.

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

    Использование функции СРЗНАЧ

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

    1. Встаем в ячейку, куда планируем выводить результат. Кликаем по значку “Вставить функци” (fx) слева от строки формул.
    2. В открывшемся окне Мастера функций выбираем категорию “Статистические”, в предлагаемом перечне кликаем по строке “СРЗНАЧ”, после чего нажимаем OK.
    3. На экране отобразится окно с аргументами функции (их максимальное количество – 255). Указываем в качестве значения аргумента “Число1” координаты нужного диапазона. Сделать это можно вручную, напечатав с клавиатуры адреса ячеек. Либо можно сначала кликнуть внутри поля для ввода информации и затем с помощью зажатой левой кнопки мыши выделить требуемый диапазон в таблице. При необходимости (если нужно отметить ячейки и диапазоны ячеек в другом месте таблицы) переходим к заполнению аргумента “Число2” и т.д. По готовности щелкаем OK.
    4. Получаем результат в выбранной ячейке.
    5. Среднее значение не всегда может быть “красивым” за счет большого количества знаков после запятой. Если нам такая детализация не нужна, ее всегда можно настроить. Для этого правой кнопкой мыши щелкаем по результирующей ячейке. В открывшемся контекстном меню выбираем пункт “Формат ячеек”.
    6. Находясь во вкладке “Число” выбираем формат “Числовой” и с правой стороны окна указываем количество десятичных знаков после запятой. В большинстве случаев, двух цифр более, чем достаточно. Также при работе с большими числами можно поставить галочку “Разделитель групп разрядов”. После внесение изменений жмем кнопку OK.
    7. Все готово. Теперь результат выглядит намного привлекательнее.

    Присвоение диапазона ячеек переменной

    Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:

    Источник статьи: http://exceltut.ru/vba-excel/

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *