Активизируйте рабочий лист с результатами поиска решения и скопируйте результаты на рабочий листПоиск

10Проанализируйте полученные результаты.

11Сохраните работу.

12Во второй копии таблицы на листеПоиск выполните ещё раз операцию Поискрешения, установив следующие параметры:

- адрес и значение целевой ячейки — сумма возврата вклада8 000руб.;

- в поле Изменяя ячейки введите абсолютные адреса ячеек с размером вклада, сроком вклада и величиной процентной ставки;

- добавьте ограничения для ячейки с величиной процентной ставки: < = 7 %

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

Введите командуСЕРВИСðСценарии и с помощью диалогового окна Диспетчер сценариев для второй копии таблицы добавьте новый сценарий под именем Поиск 1, в котором установите значение для ячейки со сроком вклада 10 лет, а для ячейки с процентной ставкой — 10%.

15Выведите сценарий Поиск 1 и создайте отчёт по сценариям в виде структуры.

16Проанализируйте полученные результаты.

17Сохраните документ.

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

19Для этого скопируйте в свою папку файл Optim.xls, который находится в папке Common. Откройте в своей папке файл Optim.xls и выполните приведённое в нём задание.

Упражнение 3. Создание таблиц подстановки

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

2Excel позволяет создавать таблицы подстановки следующих типов:

- таблицы подстановки с одной переменой и с одной или несколькими формулами;

- таблицы подстановки с двумя переменными.

3С помощью таблиц подстановки также можно выполнить анализ примере с помещением вклада, т.е. определить влияние изменения:

1) процентной ставки на сумму возврата вклада;

2) процентной ставки на коэффициент увеличения вклада;

3) величины процентной ставки и изменения срока вклада на сумму возврата вклада.

Создание таблицы подстановки с одной переменной.

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

5Сделайте активным лист 3 и присвойте ему имя Таблицы подстановки.

6Перед началом таблицы вставьте две пустые строки.

7Сделайте на этом же листе еще две копии таблицы с пятью пустыми строками перед каждым экземпляром таблицы.

8В качестве переменной используйте процентную ставку (ячейку ввода В5), которая может принимать значение от 3 до 10%. Введите эти значения в столбец Dсогласно приведенному ниже образцу (рис. 8).

9В ячейку, находящуюся правее и выше первого введенного в столбец D значения, введите формулу для вычисления суммы возврата вклада: = В3*В6.

10Введите диапазон ячеек D2:E10, содержащий подставляемые значения процентных ставок и формулу для расчета суммы возврата вклада.

	A	B	C	D	E
				Процент	Сумма возврата
					=B3*B6
	Размер вклада	5 000руб.		3 %
	Срок вклада			4 %
	Процентная ставка	5 %		5 %
	Коэфф. Увеличения	1,28		6 %
	Сумма возврата	6 381руб.		7 %
				8 %
				9 %
				10 %

Рис. 8. Данные для таблицы подстановки с одной переменной.

11Введите команду ДАННЫЕðТаблица подстановкии в диалоговом окне в поле Подставлять значения по строкам в: в введите абсолютный адрес ячейки ввода (с процентной ставкой) -$B$5.

Создание таблицы подстановки с одной

Переменной и двумя формулами

1.Для создания таблицы подстановки с одной переменной и двумя и более формулами дополнительные формулы вводят справа от ранее введенной формулы в той же строке.

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

3.Добавьте в таблицу подстановки в ячейку F2 вторую формулу для расчета коэффициента увеличения вклада: =(1+B5)^B4.

Выделите необходимый диапазон ячеек (D2:F10), введите команду ДАННЫЕðТаблица подстановки и в диалоговом окне введите абсолютный адрес ячейки ввода$B$5.

5.Проанализируйте полученные результаты.

6.Обратите внимание на то, что обе формулы связаны с одной и той же ячейкой ввода.

7.Сохраните документ.