Упражнение 2. Использование надстройки. Поиск решения и сценариев

Лабораторная работа №6

Анализ и обобщение данных в электронных таблицах Excel.

Цель работы:

1. Освоение операций Подбор параметра и поиск решения.

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

3. Освоение операции автоматического подтверждения итогов. Работа со структурой электронной таблицы.

4. Выполнение вычислений и построение диаграмм на основе итоговых данных.

5. Выполнение консолидации данных.

6. Создание прайс-листа на основе данных таблицы Excel.

Упражнение 1. Подбор параметров.

1Запустите Windows.

2Запустите табличный процессор Excel.

Создайте новый документ и сохраните его в своей папке под именем Подбор.xls.

4Уточните настройку параметров программы и приведите их в соответствие с установками практической работы 1.

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

Подбор параметра выполняется с помощью команды меню СЕРВИСðПодбор параметра.

7Используя инструмент Подбор параметра, решите следующую задачу:

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

9Присвойте листу 1 имя Подбор.

10Создайте приведенную на рисунке таблицу.

Таблица для выполнения подбора параметров.

	А	В
	Размеры вклада	5 000руб.
	Срок вклада, лет
	Процентная ставка	5 %
	Коэффициент увеличения вклада	=(1+В3)^В2
	Сумма возврата вклада	=В1*В4

Рис. 7.

11Коэффициент увеличения вклада при начислении сложных процентов вычисляется по формуле =(1+В3)^В2,где В3 – процентная ставка, В2 – срок возврата вклада, а символ ^ - оператор «возведения в степень».

12Сумма возврата вклада вычисляется в ячейке В5по формуле:

В1*В4.

13Введите формулу в ячейки В4 и В5.

14Скопируйте созданную таблицу на этот же лист, а также на листы 2 и 3.

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

16Введите команду СЕРВИСðПодбор параметраи в первой копии таблицы на листе Подбор рассчитайте процентную ставку, при которой сумма возврата вклада будет составлять 8 000руб.

17Во второй копии таблицы на этом же листе рассчитайте срок вклада, при котором сумма возврата вклада будет составлять 8 000руб.

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

Упражнение 2. Использование надстройки. Поиск решения и сценариев.

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

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

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

4 Сделайте активным лист2 и присвойте ему имя Поиск.

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

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

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

l адрес целевой ячейки — $B$5 — сумма возврата вклада;

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

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

7 Обратите внимание на то, что оба изменяемых параметра косвенно связаны со значением целевой ячейкиB5=B4*B1, так как входят в формулу расчёта коэффициента увеличения вкладаB4=(1+B3)^B2.

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

3 Щёлкните по кнопкеВыполнить.

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

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

- Тип отчёта — Результаты.