Repeater-zone.ru

ПК Репитер
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Работа с циклическими ссылками в Excel

Exceltip

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

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

связи Excel

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

Создание связей между рабочими книгами

  1. Открываем обе рабочие книги в Excel
  2. В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
  3. Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
  4. В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.

Специальная вставка

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

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

Прежде чем создавать связи между таблицами

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

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

Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.

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

Обновление связей

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

Изменить связи Excel

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

Обновление связи

Разорвать связи в книгах Excel

Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =[Источник.xlsx]Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.

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

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

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

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

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

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

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

Создание простейшей циклической ссылки в Microsoft Excel

Ячейка ссылается сама на себя в Microsoft Excel

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

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

Число 5 в ячейке в Microsoft Excel

Ссылка в ячейке в Microsoft Excel

Одна ячейка ссылается на другую в Microsoft Excel

Установка ссылки в ячейке в Microsoft Excel

Пометка циклической связи в Microsoft Excel

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

Расчет выручки в таблице в Microsoft Excel

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

Вставка циклической ссылки в таблицу в Microsoft Excel

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

Маркер заполнения в Microsoft Excel

Циклические ссылки скопированы в таблице в Microsoft Excel

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

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

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

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

Переход к ячейке с циклической ссылкой в Microsoft Excel

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

Сообщение о циклической ссылке на панели состояния в Microsoft Excel

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

Циклическая ссылка на другом листе в Microsoft Excel

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

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

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

Читать еще:  Как замазать лицо в Фотошопе

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

Выражение в программе в Microsoft Excel

Статическое значение в Microsoft Excel

Циклическая ссылка в ячейке таблицы в Microsoft Excel

Ссылка заменена на значения в Microsoft Excel

Циклических ссылок в книге нет в Microsoft Excel

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

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

Блокировка циклических ссылок в Microsoft Excel

    Прежде всего, перемещаемся во вкладку «Файл» приложения Excel.

Перемещение во вкладку Файл в Microsoft Excel

Переход в окно параметров в Microsoft Excel

Переход во вкладку Формулы в Microsoft Excel

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

Включение итеративных вычислений в Microsoft Excel

Ячейки с циклическими формулами отображают корректные значения в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Циркулярная ссылка в Excel

Циркулярная ссылка в Excel означает ссылку на ту же ячейку, в которой мы работаем, круговая ссылка — это тип всплывающего окна или предупреждения, отображаемого в excel, что мы используем круговую ссылку в нашей формуле, и расчет может быть неверным, например, в ячейке A1, если я напишу формула = A1 * 2, это круговая ссылка, поскольку внутри ячейки A1 я использовал ссылку на ячейку непосредственно A1.

Объяснил

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

Циркулярная ссылка

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

Когда вы получили указанное выше сообщение об ошибке, вы можете щелкнуть «Справка» для получения дополнительных данных или закрыть окно сообщения, щелкнув либо OK, либо крестик в диалоговом окне, которое появляется при обнаружении циклической ссылки. Когда вы закрываете окно сообщения, Excel показывает либо нулевой (0), либо последний определенный стимул в ячейке.

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

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

  • Шаг 1: Перейти в файл,

Циркулярный справочник, шаг 1

  • Шаг 2: см. в последнем клике по опции.

Циркулярный справочник, шаг 2

  • Шаг 3: Перейдите к формулам, см. Параметр расчета вверху и включите итеративный расчет. Затем нажмите ОК.

Циркулярный справочник, шаг 3

  • Шаг 4: После того, как вы включите опцию Итеративного вычисления на вкладке формул. Пожалуйста, проверьте и укажите параметры, как указано ниже:
    • Максимальное количество итераций: Он показывает, как часто уравнение следует пересчитывать. Чем выше количество акцентов, тем больше времени занимает вычисление..

    Циркулярный справочник, шаг 4

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

      Циркулярный справочник, шаг 4

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

      Как использовать круговую ссылку в Excel?

      Ниже приведены данные.

      Циркулярный справочный пример 1

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

      Циркулярный справочный пример 1-1

      • Шаг 2: Ячейка, содержащая общее значение, вернулась к 41 300 вместо 413 после включения итеративного вычисления.

      Циркулярный справочный пример 1-2

      Как найти круговые ссылки в Excel?

      • Чтобы найти круговую ссылку, сначала перейдите на вкладку «Формулы», щелкните опцию «Проверка ошибок», а затем перейдите к «Циркулярные ссылки», и здесь отображается последняя введенная круговая ссылка:

      Круговой пример 1-3

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

      Справочный пример 1-5

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

      Ссылочный пример 1-6

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

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

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

      Вставка ссылки в ячейку

      Такой объект очень легко отличить от остального текста. Он имеет другой цвет и оформлен подчёркиванием.

      Ссылка на сайт

      Если вы хотите сделать в документе переход на интернет-страницу, вот как вставить гиперссылку в Excel:

      1. Выделите нужную клетку. Можете оставить её пустой или что-то написать. Текст вы всегда сумеете удалить.
      2. Кликните по ней правой кнопкой мыши.
      3. Строка «Добавить гиперссылку» («Hyperlink»). Аналогичная кнопка есть в меню «Вставить».
      4. В блоке «Связать» нажмите «Веб-страница» («Web Page»).
      5. Чуть правее кликните на «Просмотренные страницы» и выберите нужную.
      6. Если вы хотите задать URL сайта, который не посещали до этого, напишите его в поле «Адрес» («Asdress»).
      7. В пункте «Замещающий текст» укажите то, что должно замещать гиперссылку. Эта фраза отобразится в ячейке. При клике на неё откроется веб-страница. Можете оставить поле пустым.
      8. Кнопка «Подсказка» («ScreenTip») служит для создания всплывающих подсказок Excel.

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

      Ссылка на сайт

      Можно сделать переход на интернет-страницу

      Ссылка на файл

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

      1. Клик правой кнопкой мыши — Добавить гиперссылку.
      2. В разделе «Связать» отметьте «Файл».
      3. Отобразится папка, в которой сейчас находится документ.
      4. Кнопка «Недавние файлы» откроет список данных, которые вы использовали в последнее время. Их нельзя удалить из этого раздела.
      5. Если и там нет того, что вы ищите, задайте путь самостоятельно. Для этого кликните «Текущая папка». Под замещающим текстом будет выпадающий список. В нём выберите каталог.
      6. Задать путь можно и при помощи кнопки «Найти». Она находится чуть правее выпадающего списка.

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

      В Excel можно сослаться на ещё несуществующий документ и сразу его создать.

      1. В области «Связать» отметьте «Новым документом».
      2. В блоке «Путь» («Full path») указано, куда будет сохранён файл.
      3. В поле «Имя документа» напишите его будущее имя.
      4. Нажмите «OK». Будет добавлена новая таблица. И она сразу привяжется к ячейке.
      5. Чтобы задать свои параметры и создать файл другого формата, кликните «Изменить» рядом с блоком «Путь».
      6. Выберите желаемый тип документа, введите название и укажите, куда его сохранять.

      Создание гиперссылки

      Кликните правой кнопкой мыши — Добавить гиперссылку

      Когда вы нажмёте на ячейку, к которой привязаны данные на компьютере, система безопасности Excel выдаст предупреждение. Оно сообщает о том, что вы открываете сторонний файл, и он может быть ненадёжным. Это стандартное оповещение. Если вы уверены в данных, с которыми работаете, в диалоговом окне на вопрос «Продолжить?» ответьте «Да».

      Ссылка на почту

      Можно связать ячейку с e-mail. Тогда при клике на неё откроется ваш почтовый клиент, и в поле «Кому» уже будет введён адрес.

      1. В блоке «Связать» выберите «Электронной почтой».
      2. В строке «Адрес» напишите е-майл. Он автоматически преобразуется в формулу. Её не надо стирать.
      3. В поле «Текст» введите фразу, которая должна отображаться вместо e-mail. Если вы там ничего не напишите, в ячейке будет адрес с формулой. Её можно удалить только вместе с е-майлом.
      4. В поле «Тема» можете указать тему для отправки письма.

      Ссылка на другую ячейку

      1. В разделе «Связать» нажмите «Местом в документе» («Place in This Document»).
      2. В области «Выберите место в документе» отметьте желаемый лист.
      3. В «Адрес» введите название ячейки. Оно состоит из буквы, обозначающей столбец, и номера строки. Чтобы узнать это название, кликните на нужную клетку. Оно отобразится в специальном поле слева вверху.
      4. Нажмите «OK».

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

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

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

      После этого сошлитесь на диапазон так же, как на клетку.

      Ссылка на другую таблицу

      Вот как сделать гиперссылку в Excel на другую таблицу:

      1. В блоке «Связать» выберите «Файлом».
      2. Укажите путь к документу.
      3. Нажмите кнопку «Закладка» («Bookmark»).
      4. В поле «Адрес» напишите имя клетки в другой таблице.

      Так можно сделать связь не со всем файлом, а с конкретным местом в файле.

      Умная таблица

      Создание гиперссылки на другую таблицу

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

      Допустим, вам прислали для работы документ и в нём есть циклическая ссылка в Excel — как убрать её? Ведь такие объекты будут мешаться при подсчёте формул. Да и работать с чужими связками не совсем удобно.

      Для начала такие объекты нужно найти.

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

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

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

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

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

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

      Но могут возникать ситуации, когда использование рекурсии необходимо. Для этого в параметрах Excel необходимо включить поддержку итеративных вычислений (Файл => Параметры => Формулы => Параметры вычислений):

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

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

      У Вас недостаточно прав для комментирования.

      трюки • приёмы • решения

      Обычной проблемой для бизнеса является вычисление определенного вклада как процента от чистой прибыли компании. Это не простая проблема умножения, поскольку чистая прибыль также учитывает данный вклад. Например, если доходы компании составили 1 000 000 рублей, а расходы 900 000, валовая прибыль при этом составляет 100 000 рублей. При этом компания выделяет в сторону в качестве вклада 10% от чистой прибыли.

      Чистая прибыль при этом будет вычисляться по следующей формуле: Чистая прибыль = Валовая прибыль – Вклад . Это называется циклически зависимая формула, поскольку выражения в левой и правой части зависят друг от друга. В данном случае сумма вклада будет вычисляться по формуле: Вклад = (Чистая прибыль)*0.1 .

      Одним из способов решения данной проблемы является возможность предположить правильный ответ и затем посмотреть, насколько вы близко находитесь от реального результата. Например, в данном случае можно предположить, что, если вклад составляет 10% от чистой прибыли, мы можем взять эти 10% от валовой прибыли в 100 000. При этом сумма вклада получается равной 10 000. Если мы используем это число в формуле, то получим, что чистая прибыль равна 90 000, а 10% от нее равны 9 000. Таким образом, мы ошиблись на 1 000. Можно попробовать ещё раз. Возьмем вклад, равный 9 000. При этом чистая прибыль будет равна 91 000, а сумма вклада в 10% от этой суммы будет равна 9 100. Мы ошиблись уже всего на 100 рублей.

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

      Рис. 3.5. Циклические ссылки

      1. Загрузите вашу книгу и введите туда необходимые циклически зависимые расчеты. На рис. 3.5 показана книга, вычисляющая предыдущий рассмотренный пример. При нажатии на Enter — подтверждении формулы расчета вклада — Excel выведет на экран предупреждение о наличии циклов.
      2. Нажмите на кнопку Файл, далее Параметры, затем вкладка Формулы.
      3. Включите флажок Включить итеративные вычисления.
      4. Вы можете использовать поле Предельное число итераций для задания количества итераций для вычисления. Как правило, число 100 является оптимальным между точностью и временем вычисления.
      5. Также вы можете задать Относительную погрешность в соответствующем иоле. При достижении заданной здесь точности Excel автоматически прекратит вычисления. Опять же, число 0.001, предложенное по умолчанию, является адекватным для большинства ситуаций.
      6. Нажмите ОК. Excel произведет вычисления и выдаст ответ (см 3.6).

      Рис. 3.6. Ответ после итерационных вычислений

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

      голоса
      Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector