Создание таблицы подстановки с одной переменной
ПРАКТИЧЕСКАЯ РАБОТА 13
Анализ и обобщение данных в электронных таблицах Excel
Цель работы:
1. Освоение операций Подбор параметра и Поиск решения.
2. Создание таблиц подстановки с одной и двумя переменными.
3. Освоение операции автоматического подведения итогов. Работа со структурой электронной таблицы.
4. Выполнение вычислений и построение диаграмм на основе итоговых данных.
5. Выполнение консолидации данных.
6. Создание прайс-листа на основе данных таблицы Excel.
Продолжительность работы: 4 часа.
Задание 1.Подбор параметров
Запустите табличный процессор Excel.
Создайте новый документ и сохраните его в своей папке под именем Подбор.xls.
Программа Подбор параметрапозволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки, которую называют влияющей. При этом целевая ячейка должна прямо или косвенно ссылаться на ячейку с изменяемым значением.
Подбор параметра выполняется с помощью команды меню СЕРВИС →Подбор параметра.
Используя инструмент Подбор параметра,решите следующую задачу:
Известен размер вклада, который будет помещен в банк на некоторый срок под определенный процент. Требуется рассчитать сумму возврата вклада в конце периода и определить условия помещения вклада, наиболее подходящие для его владельца.
Присвойте листу 1имя Подбор.
Создайте приведенную на рис. 4.15 таблицу.
Рис. 4.15. Таблица для выполнения подбора параметров
Коэффициент увеличения вклада при начислении сложных процентов вычисляется по формуле: =(1+ВЗ)^В2,где ВЗ - процентная ставка, В2 - срок возврата вклада, а символ ^ — оператор "возведение в степень".
Сумма возврата вклада вычисляется в ячейке В5по формуле: =В1*В4.
Введите формулы в ячейки В4 и В5.
Скопируйте созданную таблицу на этот же лист, а также на листы 2 и 3.
Сохраните работу.
Введите команду СЕРВИС →Подбор параметраи в первой копии таблицы на листе Подборрассчитайте процентную ставку, при которой сумма возврата вклада будет составлять 8 000 руб.
Во второй копии таблицы на этом же листе рассчитайте срок вклада, при котором сумма возврата вклада будет составлять 8 000 руб.
Сохраните работу.
Задание 2. Использование надстройки Поиск решения и сценариев
Программа Поиск решенияпозволяет получить результат на основе изменения значений нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия - ввести ограничения.
При поиске решения, так же как и при подборе параметра, целевая ячейка должна содержать формулу и быть прямо или косвенно связанной с ячейками с изменяемыми значениями.
Введите команду СЕРВИС →Надстройкии в диалоговом окне Надстройкиподключите надстройку Поиск решении- установите соответствующий переключатель.
Сделайте активным лист 2 и присвойте ему имя Поиск.
Сделайте на этом же листе еще одну копию таблицы.
В первой копии таблицы, изменяя одновременно два параметра, подберите значения срока вклада и процентной ставки, при которых сумма возврата вклада будет составлять 8 000 руб. Для этого выполните следующие действия:
1. Введите команду СЕРВИС →Поиск решенияи в диалоговом окне Поиск решенияустановите следующие параметры:
• адрес целевой ячейки - $В$5 - сумма возврата вклада;
• подбираемое для целевой ячейки значение - 8 000р.;
• в поле Изменяя ячейкивведите абсолютные адреса ячеек сосроком вклада и величиной процентной ставки.
Обратите внимание на то, что оба изменяемых параметра косвенно связаны со значением целевой ячейки В5 = В4*В1, так как входят в формулу расчета коэффициента увеличения вклада В4 = (1+ВЗ)^В2.
2. Введите ограничения для ячейки со сроком вклада - цел -целое число лет.
3. Щелкните по кнопке Выполнить.
В диалоговом окне Результаты поиска решения установите:
• Сохранить найденное решение;
• Тип отчета - Результаты.
Активизируйте рабочий лист с результатами поиска решения и скопируйте результаты на рабочий лист Поиск.
Проанализируйте полученные результаты.
Сохраните работу.
Во второй копии таблицы на листе Поиск выполните еще раз операцию Поиск решения, установив следующие параметры:
• адрес и значение целевой ячейки - сумма возврата вклада 8000р.;
• в поле Изменяя ячейки введите абсолютные адреса ячеек с размером вклада, сроком вклада и величиной процентной ставки;
• добавьте ограничения для ячейки с величиной процентной ставки: < = 7 %.
Сохраните результаты поиска решения в виде сценария под именем Поиск и восстановите в таблице исходные значения.
Введите команду СЕРВИС →Сценарии и с помощью диалогового окна Диспетчер сценариев для второй копии таблицы добавьте новый сценарий под именем Поиск 1, в котором установите значение для ячейки со сроком вклада 10 лет, а для ячейки с процентной ставкой -10%.
Выведите сценарий Поиск 1 и создайте отчет по сценариям в виде структуры.
Проанализируйте полученные результаты.
Сохраните документ.
Используя программу Поиск решения, решите задачу оптимизации выпуска изделий на предприятии "Протон".
Для этого скопируйте в свою папку файл Oplim.xls, который находится в папке Common. Откройте в своей папке файл Oplim.xls и выполните приведенное в нем задание.
Задание 3. Создание таблиц подстановки
Таблицы подстановки позволяют вычислять и анализировать данные в тех случаях, когда необходимо найти результат для нескольких значений в одной или двух исходных (влияющих) ячейках.
Excel позволяет создавать таблицы подстановки следующих типов:
• таблицы подстановки с одной переменной и с одной или несколькими формулами;
• таблицы подстановки с двумя переменными.
С помощью таблиц подстановки также можно выполнить анализ примера с помещением вклада, т. е. определить влияние изменения:
1) процентной ставки на сумму возврата вклада;
2) процентной ставки на коэффициент увеличения вклада;
3) величины процентной ставки и изменения срока вклада на сумму возврата вклада.
Создание таблицы подстановки с одной переменной
Для решения первых двух задач используйте таблицы подстановки с одной переменной.
Сделайте активным лист 3 и присвойте ему имя Таблицы подстановки.
Перед началом таблицы вставьте две пустые строки.
Сделайте на этом же листе еще две копии таблицы с пятью пустыми строками перед каждым экземпляром таблицы.
В качестве переменной используйте процентную ставку (ячейку ввода В5), которая может принимать значения от 3 до 10%. Введите эти значения в столбец D согласно приведенному ниже образцу (рис. 4.16).
В ячейку, находящуюся правее и выше первого введенного в столбец D значения, введите формулу для вычисления суммы возврата вклада: = ВЗ*В6.
Выделите диапазон ячеек D2: E10, содержащий подставляемые значения процентных ставок и формулу для расчета суммы возврата вклада.
Рис. 4.16.Данные для таблицы подстановки с одной переменной
Введите команду ДАННЫЕ →Таблица подстановкии в диалоговом окне в поле Подставлять значения но строкам в: введите абсолютный адрес ячейки ввода (с процентной ставкой) - $В$5.