МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ. Программа Поиск решения позволяет получить результат при изменении значений нескольких ячеек

Программа Поиск решения позволяет получить результат при изменении значений нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия – ввести ограничения.

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

Введите команду СЕРВИСàНастройки и в диалоговом окне Настройки подключите настройку Поиск решения – установите соответствующий переключатель.

Сделайте активным лист 2 и назовите его Поиск.

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

В первой копии таблицы, изменяя одновременно два параметра, подберите значения срока вклада и процентной ставки, при которых сумма возврата вклада будет составлять 8 000 рублей. Для этого выполните следующие действия:

1. Введите команду СЕРВИСà Поиск решения и в диалоговом окне Поиск решения установите следующие параметры:

- адрес целевой ячейки - $В$5 – сумма возврата вклада;

- подбираемое для целевой ячейки значение – 8 000 р.;

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

Обратите внимание на то, что оба изменяемых параметра косвенно связаны со значениями целевой ячейки В5 равной В4*В1, так как входят в формулу расчета коэффициента увеличения вклада В4=(1+В3)^В2.

2. Введите ограничения для ячейки со сроком вклада – цел – целое число лет.

3. Нажмите кнопку Выполнить.

В диалоговом окне Результаты поиска решения установите:

- Сохранить найденное решение;

- Тип отчета – результаты.

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

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

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

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

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

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

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

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

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

Проанализируйте результат и сохраните работу.

Предъявите преподавателю результаты работы:

- сохраните в своей папке файл Подбор.xls,

- результаты подбора параметров и поиска решения;

- отчет по сценарию.

Контрольные вопросы к лабораторной работе № 5

1. Какие средства условного анализа имеются в Excel?

2. Каково назначение инструмента Подбор параметра?

3.Каково назначение надстройки Поиск решения? Опишите технологию выполнения этой операции.

4.Для чего в Excel используют сценарии? Как создать сценарий?

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ