Лабораторная работа №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 В диалоговом окне Результаты поиска решения установите:
- Сохранить найденное решение;
- Тип отчёта — Результаты.
