5. Анализ и обобщение данных в электронных таблицах excel
Цель:
1. Освоение операций Подбор параметра и Поиск решения.
2. Создание таблиц подстановки с одной и двумя переменными.
3. Освоение операции автоматического подведения итогов. Работа со структурой электронной таблицы.
4. Выполнение вычислений и построение диаграмм на основе итоговых данных.
5. Выполнение консолидации данных.
6. Создание прайс-листа на основе данных таблицы Excel.
Задание 1. Подбор параметров
Используя инструмент Подбор параметра, решите следующую задачу. Известен размер вклада, который будет помещен в банк на некоторый срок под определенный процент. Требуется рассчитать сумму возврата вклада в конце периода и определить условия помещения вклада, наиболее подходящие для его владельца.
Методические указания по выполнению задания 1:
Программа Подбор параметра позволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки, которую называют влияющей. При этом целевая ячейка должна прямо или косвенно ссылаться на ячейку с изменяемым значением.
Подбор параметра выполняется с помощью команды меню Сервис / Подбор параметра.
Присвойте Лист 1 имя Подбор.
Создайте таблицу по приведенному ниже образцу (рис. 5.1).
Рис.5.1. Образец таблицы для выполнения подбора параметров.
Коэффициент увеличения вклада при начислении сложных процентов вычисляется по формуле: =(1+ВЗ)^В2, где ВЗ – процентная ставка, В2 – срок возврата вклада, символ ^ — оператор возведение в степень. Сумма возврата вклада вычисляется в ячейке В5 по формуле: = В1*В4.
Скопируйте созданную таблицу на этот же лист, а также на листы 2, 3.
Введите команду Сервис / Подбор параметра и в первой копии таблицы на листе Подбор рассчитайте процентную ставку, при которой сумма возврата вклада будет составлять 8000 руб.
Во второй копии таблицы на этом же листе рассчитайте срок вклада, при котором сумма возврата вклада будет составлять 8 000 руб.
Задание 2. Использование надстройки Поиск решения и сценариев
Используя программу Поиск решения, решите следующую задачу: изменяя одновременно два параметра, подберите значения срока вклада и процентной ставки.
Методические указания по выполнению задания 2:
Программа Поиск решения позволяет получить результат на основе изменения значений нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия – ввести ограничения.
При поиске решения так же, как и при подборе параметра, целевая ячейка должна содержать формулу и быть прямо или косвенно связанной с ячейками с изменяемыми значениями.
Введите команду Сервис / Надстройки и в диалоговом окне Надстройки подключите надстройку Поиск решения – установите соответствующий переключатель.
Сделайте активным Лист 2 и присвойте ему имя Поиск.
Сделайте на этом же листе еще одну копию таблицы.
В первой копии таблицы, изменяя одновременно два параметра, подберите значения срока вклада и процентной ставки, при которых сумма возврата вклада будет составлять 8 000 руб. Для этого выполните следующие действия:
Введите команду Сервис / Поиск решения и в диалоговом окне Поиск решения установите следующие параметры:
• адрес целевой ячейки – $В$5 – сумма возврата вклада;
• подбираемое для целевой ячейки значение – 8 000р.;
• в поле Изменяя ячейки введите абсолютные адреса ячеек со сроком вклада и величиной процентной ставки.
Обратите внимание на то, что оба изменяемых параметра косвенно связаны со значением целевой ячейки В5=В1*В4, так как они входят в формулу расчета коэффициента увеличения вклада В4=(1+ВЗ)^В2.
Введите ограничения для ячейки со сроком вклада – цел – целое число лет.
Щелкните по кнопке Выполнить.
В диалоговом окне Результаты поиска решения установите:
• сохранить найденное решение;
• тип отчета – Результаты.
Активизируйте рабочий лист с результатами поиска решения и скопируйте результаты на рабочий лист Поиск.
Проанализируйте полученные результаты.
Во второй копии таблицы на листе Поиск выполните еще раз операцию Поиск решения, установив следующие параметры:
• адрес и значение целевой ячейки – сумма возврата вклада 8 000р.;
• в поле Изменяя ячейки введите абсолютные адреса трех ячеек – с размером вклада, сроком вклада и величиной процентной ставки;
• добавьте ограничения: для ячейки с величиной процентной ставки <=7%; для ячейки со сроком вклада – цел.
Сохраните найденное решение.
Введите команду Сервис / Сценарии и с помощью диалогового окна Диспетчер сценариев для первой копии таблицы добавьте сценарий под именем Поиск, в котором установите значение для ячейки со сроком вклада 10 лет, а для ячейки с процентной ставкой – 10%.
Выведите сценарий Поиск. Создайте отчет по сценарию в виде структуры. Проанализируйте полученные результаты.