Подбор параметра и поиск решения в MS Excel

ЛАБОРАТОРНАЯ РАБОТА № 9

(С пояснениями)

Часть I

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

Например, в ячейку A2 введено число 5, в ячейку A3 – формула =A1+A2.

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

Необходимо выполнить действия Данные – Анализ что-если ® Подбор параметра, затем в появившемся диалоговом окне Подбор параметра указать: адрес ячейки, в которой будет получен результат (A3); планируемое значение результата (25); адрес ячейки с изменяемым значением (A1). После нажатия кнопки < OK > ответ будет получен путем последовательных итераций (приближений). Каждый шаг этого процесса даст приближение к искомой величине.

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

Задание

Имеется информация о наращении сумм и выплате процентов в потребительском кредите, выданном банком при простых кредитных ставках:

Первоначальная сумма ссуды, руб.	Срок кредита, лет	Ставка наращения	Наращенная сумма долга, руб.	Число платежей в году	Величина разового погасительного платежа, руб.
		15%

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

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

2. Скопируйте строку с данными и формулами семь раз. (Выделяем строку, которую необходимо скопировать – Копировать, указываем место, в которое необходимо скопировать – Вставить).

3. В трех первых скопированных строках подберите значения первоначальной суммы ссуды, срока кредита и ставки наращения при фиксированной величине наращенной суммы долга 150000 руб. (Сервис – Подбор параметра – затем в появившемся диалоговом окне Подбор параметра указываем: адрес ячейки, в которой будет получен результат; планируемое значение результата; адрес ячейки с изменяемым значением ­– ОК).

4. В четырех последних скопированных строках подберите значения первоначальной суммы ссуды, срока кредита, ставки наращения и числа платежей в году при фиксированной величине разового погасительного платежа 8000 руб. (Сервис – Подбор параметра – затем в появившемся диалоговом окне Подбор параметра указываем: адрес ячейки, в которой будет получен результат; планируемое значение результата; адрес ячейки с изменяемым значением ­– ОК).

Получаем следующую таблицу:

Дата добавления: 2014-01-15