10 Проанализируйте полученные результаты.
11 Сохраните работу.
12 Во второй копии таблицы на листе Поиск выполните ещё раз операцию Поиск решения, установив следующие параметры:
- адрес и значение целевой ячейки — сумма возврата вклада 8 000 руб.;
- в поле Изменяя ячейки введите абсолютные адреса ячеек с размером вклада, сроком вклада и величиной процентной ставки;
- добавьте ограничения для ячейки с величиной процентной ставки: < = 7 %
13 Сохраните результаты поиска решения в виде сценария под именем Поиск и восстановите в таблице исходные значения.
Введите командуСЕРВИСðСценарии и с помощью диалогового окна Диспетчер сценариев для второй копии таблицы добавьте новый сценарий под именем Поиск 1, в котором установите значение для ячейки со сроком вклада 10 лет, а для ячейки с процентной ставкой — 10%.
15 Выведите сценарий Поиск 1 и создайте отчёт по сценариям в виде структуры.
16 Проанализируйте полученные результаты.
17 Сохраните документ.
Используя программуПоиск решения, решите задачу оптимизации выпуска изделий на предприятии «Протон».
19 Для этого скопируйте в свою папку файл Optim.xls, который находится в папке Common. Откройте в своей папке файл Optim.xls и выполните приведённое в нём задание.
Упражнение 3. Создание таблиц подстановки
1 Таблицы подстановки позволяют вычислять и анализировать данные в тех случаях, когда необходимо найти результат для нескольких значений в одной или двух исходных (влияющих) ячейках.
2 Excel позволяет создавать таблицы подстановки следующих типов:
- таблицы подстановки с одной переменой и с одной или несколькими формулами;
- таблицы подстановки с двумя переменными.
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. Сохраните документ.
