Подбор параметра – имитационное моделирование в Excel.

УПРАЖЕНЕНИЕ 1. Решение упражнений средствами программы Excel.

Задача. Найти решение уравнения .

1. Новому листу рабочей книги присвойте имя Уравнение.

2. Занесите в ячейку А1 значение 0.

3. Занесите в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1. Соответствующая формула может, например, иметь вид =А1^3-3^A1^2+A1.

4. Дайте команду Сервис – Подбор параметра.

5. В поле установить в ячейке укажите В1, в поле Значение задайте –1, в поле Изменяя значение ячейки укажите А1.

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

7. Повторите расчет, задавая в ячейке А1 другие начальные значения, например 0,5 или 2. Совпали ли результаты вычислений? Чем можно объяснить различия? Сохраните файл.

1. Выделите диапазон ячеек В2:С2. Щелкните на раскрывающей кнопке рядом с кнопкойГраницына панели инструментовФорматированиеи задайте для этих ячеек широкую внешнюю рамку (кнопка в правом нижнем углу открывающейся палитры).

2. Дважды щелкните на границе между заголовками столбцов АиВ,ВиС,СиД. Обратите внимание как при этом изменяется ширина столбцовА,ВиС.

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

4. Сохранить рабочую книгу.

УПРАЖНЕНИЕ 2 Подбор параметра.

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

Пусть размер вклада составляет 150000 грн, срок вклада – 20 лет, процентная ставка – 5% (Рис.3). Коэффициент нарощения вычислен по формуле

(1+проц.ставка)^срок вклада,

а сумму выплат по формуле размер вклада * коэффициент нарощения.

Рис.3. Таблица для иллюстрации задачи.

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

Допустим, что необходимо определить какими должны быть входные данные , чтобы через 20 лет сумма выплат составляла 500000 грн. Тут речь идёт или же об увеличении размера вклада, или об увеличении процентной ставки. Решить такую задачу можно с помощью команды Сервис → Подбор параметра.

Для использования этой функции необходимо выделить ячейку, в которой будет находиться результат (В6), и вызвать программу Подбор параметра. При этом откроется диалоговое окно, в котором необходимо заполнить соответствующие поля. В нашем случае ищем ответ на вопрос: каким должен быть размер вклада, чтобы через 20 лет сумма выплат составила 500000 грн.

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

Рис. 4. Результат подбора параметра в исходной таблице.

Инструктивно-методические указания по проведению практического занятия обсуждены и одобрены на заседании кафедры КЭЭМ

Протокол № ___ от «___» __________ 200__ г.