Сделай Сам Свою Работу на 5

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

Подбор параметра

Создайте новый документ, сохраните его в своей папке и присвойте ему имя Подбор.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- 2017 stydopedia.ru Все материалы защищены законодательством РФ.