Итерационное вычисление
В EXCEL итерационные процедуры нужны для пошаговых вычислений. Итерационное вычисление основано на повторении последовательности операций, при котором на каждом последующем шаге вычисления используется результат предыдущего шага.
Для того чтобы итерационный процесс был возможен, одна или несколько ячеек должны содержать циклические ссылки. В стандартном режиме циклическая ссылка воспринимается программой как ошибка, потому что вычисление с никогда не может быть закончено. Для ограничения числа шагов (циклических ссылок) можно воспользоваться встроенными средствами EXCEL. После этого можно использовать циклические ссылки для вычисления методом последовательного приближения (итерации). Разрешение на итерацию можно ввести с помощью меню Сервис->Параметры->Вычисления и поставить флажок в ячейке Итерация. После того как флажок установлен, включается ограничение на количество циклов вычислений на листе. Во-первых, программа прерывает процесс вычисления, обнаружив его “зависание”, когда относительное изменение величины не превышает некоторого порогового значения . Оно проставляется в окне Максимальное изменение. По умолчанию оно равно 0,001. После того как закончен ввод данных, программа автоматически производит первое вычисление всех значений на листе. Повторить вычисление можно, нажав клавишу F9.
Рассмотрим примеры итерационных процессов.
Задание 20. Разрешите циклическую ссылку. Необходимо решить следующую задачу: вычислить полагающийся торговому агенту бонус, составляющий 12% от чистой прибыли. Сумма общей прибыли составляет 1,5 млн. рублей, чистая прибыль равна величине общей прибыли минус выплачиваемый бонус.
Для этого составьте следующую таблицу, рис.11:
Измените величину общей прибыли и посмотрите, как изменится бонус.
Задание 21. Составьте свой пример на использования итерации. Результата покажите преподавателю и оформите его в тетради.
Подбор параметра.
Программа EXCEL обладает замечательным свойством — подбирать параметр какой-то зависимости, функции для получения заведомо желаемого (заданного) результата. Это может быть использовано, например, для определения процента погашения за ссуду при известном сроке погашении. И много еще других случаев в жизни, когда необходимо «подогнать» какой-либо параметры при остальных фиксированных.
Для использования этого режима необходимо в меню Сервис выбрать позицию Подбор параметра. В появившемся окне диалога необходимо ввести три параметра:
1) Указать адрес ячейки, в которой должно находится целевое значение функции, которое вы хотите достичь.
2) Указать в выбранной ячейке числовое значение целевой функции.
3) Указать адрес ячейки, содержимое которой позволено изменять для получения заданного значения целевой функции.
При указании изменяемой ячейки следует учитывать два нюанса: во-первых, данная ячейка должна содержать числовое значение, а не формулу, во-вторых, итоговая ячейка должна прямо или косвенно ссылаться на изменяемую. В противном случае, несмотря на активные усилия, программа так и не сможет определить нужного исходного значения.
Задание 22. Подобрать радиус круга, площадь которого равна 5000 квадратных метров.
Задание 23. Придумайте свой пример на подбор параметра. Реализуйте его на компьютере и опишите его в тетради.
Как в 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. Циклические ссылки
- Загрузите вашу книгу и введите туда необходимые циклически зависимые расчеты. На рис. 3.5 показана книга, вычисляющая предыдущий рассмотренный пример. При нажатии на Enter — подтверждении формулы расчета вклада — Excel выведет на экран предупреждение о наличии циклов.
- Нажмите на кнопку Файл, далее Параметры, затем вкладка Формулы.
- Включите флажок Включить итеративные вычисления.
- Вы можете использовать поле Предельное число итераций для задания количества итераций для вычисления. Как правило, число 100 является оптимальным между точностью и временем вычисления.
- Также вы можете задать Относительную погрешность в соответствующем иоле. При достижении заданной здесь точности Excel автоматически прекратит вычисления. Опять же, число 0.001, предложенное по умолчанию, является адекватным для большинства ситуаций.
- Нажмите ОК. Excel произведет вычисления и выдаст ответ (см 3.6).
Рис. 3.6. Ответ после итерационных вычислений
Если вы хотите посмотреть, как именно происходит вычисление итераций в вашем случае, установите в параметрах Excel вычисления на листе вручную и предельное число итераций, равное 1. Нажимая на кнопку F9, вы сможете видеть результат вычисления очередной итерации.