Bazaprogram.ru

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

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

Microsoft Excel

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

Как в Excel с помощью итерационных вычислений посчитать чистую прибыль от вклада

Обычной проблемой для бизнеса является вычисление определенного вклада как процента от чистой прибыли компании. Это не простая проблема умножения, поскольку чистая прибыль также учитывает данный вклад. Например, если доходы компании составили 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, вы сможете видеть результат вычисления очередной итерации.

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

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

Формула =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, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

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

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

Ранее я описал, как найти и исправить циклическую ссылку. Напомню, что циклическая ссылка появляется, если в ячейку Excel введена формула, содержащая ссылку на саму эту ячейку (напрямую или через цепочку других ссылок). Например (рис. 1), в ячейке С2 находится формула, ссылающаяся на саму ячейку С2.

Рис. 1. Пример циклической ссылки

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

Скачать заметку в формате Word, примеры в формате Excel

Рис. 2. Параметры Excel

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

Читать еще:  Новый comобъект excel application

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

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

Решим уравнение третьей степени: х 3 – 4х 2 – 4х + 5 = 0 (рис. 4). Для решения этого уравнения (и любого другого уравнения совершенно произвольного вида) понадобится всего одна ячейка Excel.

Рис. 4. График функции f(x)

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

(1) x = x – f(x)/f’(x), где

f(x) – функция, задающая уравнение, корни которого мы ищем; f(x) = х 3 – 4х 2 – 4х + 5

f’(x) – производная нашей функции f(x); f’(x) = 3х 2 – 8х – 4; производные основных элементарных функций можно посмотреть здесь.

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

Итоговая рекуррентная формула имеет вид:

(2) х = x – (х 3 – 4х 2 – 4х + 5)/(3х 2 – 8х – 4)

Выберем любую ячейку на листе Excel (рис. 5; в нашем примере это ячейка G19), присвоим ей имя х, и введем в нее формулу:

Можно вместо х использовать адрес ячейки… но согласитесь, что имя х, смотрится привлекательнее; следующую формулу я ввел в ячейку G20:

Рис. 5. Рекуррентная формула: (а) для поименованной ячейки; (б) для обычного адреса ячейки

Как только мы введем формулу и нажмем Enter, в ячейке сразу же появится ответ – значение 0,77. Это значение соответствует одному из корней уравнения, а именно второму (см. график функции f(x) на рис. 4). Поскольку начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке х и равного нулю. Как же получить остальные корни уравнения?

Для изменения стартового значения, с которого рекуррентная формула начинает свои итерации, предлагается использовать функцию ЕСЛИ: [1]

Здесь значение «-5» – начальное значение для рекуррентной формулы. Изменяя его, можно выйти на все корни уравнения:

Начальное значениеКорень уравнения
10,77
-5-1,40
84,63

[1] Идея подсмотрена здесь

7 комментариев для “Excel. Использование циклических ссылок для решения уравнений итерационным способом”

Офигенный сайт!
И как всегда когда не нужно все находишь!
Блин у меня по экономическому моделированию в Excell курсовик был в институте, вот время помню кучу потерял а тут все в одном флаконе:)
Все равно инфа пригодится, даже очень!

И нам зараза в этом гребанном институте мать их так этих учителей даже близко ничего подобного не рассказывали. Я не про этот пример говорю а про остальные..
«Вычисление стандартного отклонения для данных с тенденцией», «Нормальное распределение» и т.п.. даже объяснить не могли или не хотели как это все применять, а тут все наглядно и понятно! Огромное спасибо! Не зря я на этот сайт наткнулся, чую он мне еще окажет неплохую помощь))))

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

У меня график получается, который с осью Х не имеет вообще пересечений, мож где накосячила?
Пожалуйста подскажите, а то у меня взрыв мозга будет скоро…((((

Тамара, если Вы строите график на основе моих данных, откройте файл Excel; если Вы используете собственные данные, пришлите мне на mail Ваш файл, попробую помочь))

Спасибо заранее за беспокойство, вот такое уравнение у^3-20у^2-158у-420=0, если не трудно объясните пожалуйста как вы определяте предел в каких знчениях надо считать корни.

Финансы в Excel

Перекрестный расчет себестоимости

Содержание
Пример задачи
Преобразование математической модели
Назначенная себестоимость
Подбор значения
Итерационный расчет
Заключение
Вложения:

crosscosts.xls[Перекрестная себестоимость]30 kB

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

Пример задачи

На крупном производственном предприятии имеется 2 цеха вспомогательного производства:

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

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

Читать еще:  Как отменить функцию в excel

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

Упрощенная форма задачи представлена в файле примера к статье:

Формула в ячейке В9 (=B8+B7*E10) определяет, что итоговые затраты энергоцеха складываются из бюджета внутренних расходов (в примере представлен одним числом) и стоимостью потребления тепла (объем, умноженный на цену). Ячейка B10 вычисляет себестоимость кВт/ч электроэнергии (=B9/B6). Если попытаться скопировать формулу расчета себестоимости единицы электроэнергии в формулу расчета единицы теплоэнергии (B10 в E10), то логично получим сообщение о циклической ссылке:

Какие же варианты решения имеются и используются на практике?

Преобразование математической модели

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

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

Назначенная себестоимость

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

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

Подбор значения

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

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

Итерационный расчет

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

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

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

Заключение

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

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

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

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