Создание таблицы подстановки с одной переменной
Подбор параметра
Создайте новый документ, сохраните его в своей папке и присвойте ему имя Подбор.xls.
Программа Подбор параметра позволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки, которую называют влияющей. При этом целевая ячейка должна прямо или косвенно ссылаться на ячейку с изменяемым значением.
Подбор параметра осуществляется с помощью команды Меню СЕРВИСà Подбор инструмента.
Используя инструмент Подбор инструмента, решите следующую задачу:
Известен размер вклада, который будет помещен в банк на некоторый срок под определенный процент. Требуется рассчитать сумму возврата вклада в конце периода и определить условия помещения вклада наиболее подходящие для его владельца.
Присвойте листу 1 имя Подбор.
Создайте таблицу.
Таблица 5. Данные для выполнения подбора параметра
Коэффициент увеличения вклада при начислении сложных процентов вычисляется по формуле: = (1+В3)^В2, где В3 – процентная ставка, В2 – срок возврата вклада, а символ : ^ - это оператор «возведение в степень».
Сумма возврата вклада вычисляется в ячейке В5 по формуле: =В1*В4.
Введите формулы в ячейки В4 и В5.
Скопируйте созданную таблицу на этот же лист, а также на листы 2 и 3.
Сохраните работу.
Введите команду СЕРВИСà Подбор параметра и в первой копии таблицы на листе Подбор рассчитайте процентную ставку при которой сумма возврата вклада будет составлять 8 000 рублей.
Во второй копии таблицы рассчитайте на этом же листе срок вклада при котором сумма возврата вклада будет составлять 8 000 рублей.
Сохраните работу.
Использование надстройки Поиск решения
Программа Поиск решения позволяет получить результат при изменении значений нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия – ввести ограничения.
При поиске решения, также как и при подборе параметра, целевая ячейка должна содержать формулу и быть прямо или косвенно связанной с ячейками с изменяемыми значениями.
Введите команду СЕРВИСàНастройки и в диалоговом окне Настройки подключите настройку Поиск решения – установите соответствующий переключатель.
Сделайте активным лист 2 и назовите его Поиск.
Создайте на этом же листе еще одну копию таблицы.
В первой копии таблицы, изменяя одновременно два параметра, подберите значения срока вклада и процентной ставки, при которых сумма возврата вклада будет составлять 8 000 рублей. Для этого выполните следующие действия:
1. Введите команду СЕРВИСà Поиск решения и в диалоговом окне Поиск решения установите следующие параметры:
- адрес целевой ячейки - $В$5 – сумма возврата вклада;
- подбираемое для целевой ячейки значение – 8 000 р.;
- в поле Изменяя ячейки введите абсолютные адреса ячеек со сроком вклада и величиной процентной ставки.
Обратите внимание на то, что оба изменяемых параметра косвенно связаны со значениями целевой ячейки В5 равной В4*В1, так как входят в формулу расчета коэффициента увеличения вклада В4=(1+В3)^В2.
2. Введите ограничения для ячейки со сроком вклада – цел – целое число лет.
3. Нажмите кнопку Выполнить.
В диалоговом окне Результаты поиска решения установите:
- Сохранить найденное решение;
- Тип отчета – результаты.
Активизируйте рабочий лист с результатами поиска решения и скопируйте результаты на рабочий лист Поиск.
Проанализируйте полученные результаты.
Во второй копии таблицы на листе Поиск еще раз выполните операцию Поиск решения, установив следующие параметры:
- адрес и значение целевой ячейки – сумма возврата вклада 8 000 рублей;
- в поле Изменяя ячейки введите абсолютные адреса ячеек с размером вклада, сроком вклада и величиной процентной ставки;
- добавьте ограничения для ячейки с величиной процентной ставки: <= 7%.
Сохраните результаты поиска решения в виде сценария под именем Поиск и восстановите в таблице исходные значения.
Введите команду СЕРВИСà Сценарии и с помощью диалогового окна под названием Диспетчер сценариев для второй копии таблицы добавьте новый сценарий под именем Поиск 1, в котором установите значение для ячейки со сроком вклада 10 лет, а для ячейки с процентной ставкой – 10%.
Выведите сценарий Поиск 1 и создайте отчет по сценариям в виде структуры. Проанализируйте полученные результаты.
Проанализируйте результат и сохраните работу.
Предъявите преподавателю результаты работы:
- сохраните в своей папке файл Подбор.xls,
- результаты подбора параметров и поиска решения;
- отчет по сценарию.
Контрольные вопросы к лабораторной работе № 2
1. Какие средства условного анализа имеются в Excel?
2. Каково назначение инструмента Подбор параметра?
3. Каково назначение надстройки Поиск решения? Опишите технологию выполнения этой операции.
4. Для чего в Excel используют сценарии? Как создать сценарий?
Лабораторная работа №3. Обобщение данных. Создание таблицы подстановки. Подведение итогов
Цель работы:
1. Создание таблиц подстановки с одной или двумя переменными.
2. Освоение операции автоматического подведения итогов. Работа со структурой электронной таблицы.
3. Выполнение вычислений и построение диаграмм на основе итоговых данных.
Создание таблицы подстановки с одной переменной
Таблицы подстановки позволяют вычислять и анализировать данные в тех случаях, когда необходимо найти результат для нескольких значений в одной или двух исходных (влияющих) ячейках.
Excel может создавать таблицы подстановки следующих типов:
- таблицы подстановки с одной переменной и с одной или несколькими формулами;
- таблицы подстановки с двумя переменными.
С помощью таблиц подстановки также можно выполнить анализ примера с помещением вклада, т. е. определить влияние изменения:
1) процентной ставки на сумму возврата вклада;
2) процентной ставки на коэффициент увеличения вклада;
3) величины процентной ставки и изменения срока вклада на сумму возврата вклада.
Для решения первых двух задач используйте таблицы подстановки с одной переменной.
Откройте файл Подбор.xls (см. лабораторную работу №5) и активизируйте лист 3, присвойте ему имя Таблицы подстановки.
Перед началом таблицы вставьте две пустые строчки.
Сделайте на этом же листе еще две копии таблицы с пятью пустыми строками перед каждым экземпляром таблицы.
В качестве переменной используйте процентную ставку (ячейка ввода В5), которая может принимать значения от 3 до 10% . Введите эти значения в столбец D согласно приведенному образцу (Таблица 6).
Таблица 6. Данные для таблицы подстановки с одной переменной
| A
| B
| C
| D
| E
|
|
|
|
| Процент
| Сумма возврата
|
|
|
|
|
| =B3*B6
|
| Размер вклада
| 5000руб.
|
| 3%
|
|
| Срок вклада
|
|
| 4%
|
|
| Процентная ставка
| 5%
|
| 5%
|
|
| Коэфф. увеличения
| 1,28
|
| 6%
|
|
| Сумма возврата
| 6381руб.
|
| 7%
|
|
|
|
|
| 8%
|
|
|
|
|
| 9%
|
|
|
|
|
| 10%
|
| В ячейку, находящуюся правее и выше первого введенного в столбец D значения, введите формулу для вычисления суммы вклада: =В3*В6.
Выделите диапазон ячеек D2:E10, содержащий подставляемые значения процентных ставок и формулу для расчета суммы возврата вклада.
Введите команды ДАННЫЕ à Таблица подстановки и в диалоговом окне в поле Подставлять значения по строкам в: введите абсолютный адрес ячейки ввода (с процентной ставкой) - $B$5.
Для создания таблицы подстановки с одной переменной и двумя и более формулами дополнительные формулы вводят справа от ранее введенной формулы в той же строке.
При этом следует иметь в виду, что все используемые для подстановки формулы должны быть прямо или косвенно связаны с одной и той же ячейкой ввода, в рассматриваемом примере – с ячейкой В5, содержащей значение процентной ставки.
Добавьте в таблицу подстановки в ячейку F2 вторую формулу для расчета коэффициента увеличения вклада: = (1+В5)^В4.
Выделите необходимый диапазон ячеек (D2:F10), введите команду ДАННЫЕ à Таблица подстановки и в диалоговом окне введите абсолютный адрес ячейки ввода $B$5.
Проанализируйте полученные результаты.
Обратите внимание на то, что обе формулы связаны с одной и той же ячейкой ввода. Сохраните документ.
Не нашли, что искали? Воспользуйтесь поиском по сайту:
©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.
|