Создание таблиц подстановки.

Анализ и обобщение данных в электронных ячейках Excel.

Цель работы: освоение операций Подбор параметра и Поиск решения. Создание таблиц подстановки с одной или двумя переменными.

1. запустите табличный процессор Excel.

2. программа Подбор параметра позволяет получить требуемое значение в определенной ячейке (целевой), путем изменения значения другой ячейки (влияющей). При этом целевая ячейка должна прямо или косвенно ссылаться на ячейку с изменяемым значением. Подбор параметра выполняется с помощью команды меню СЕРВИС Подбор параметра.

3. Решите задачу: известен размер вклада, который будет помещен в банк на некоторый срок под определенный процент. Требуется рассчитать сумму возврата вклада в конце периода и определить условия помещения вклада, наиболее подходящее для его владельца.

4. для этого создайте следующую таблицу

5. присвойте листу1 имя Подбор.

6. скопируйте созданную таблицу на этот же лист, а также на листы 2 и 3.

7. используя команду Подбор параметра и в первой копии таблицы на листе Подбор, рассчитайте процентную ставку, при которой сумма возврата вклада будет составлять 8000 руб.

8. на второй копии таблицы на этом же листе рассчитайте срок вклада, при котором сумма возврата вклада будет составлять 8000 руб.

Использование надстройки Поиск решения.

Программа Поиск решения позволяет получить результат на основе изменения значений нескольких ячеек. При выполнении поиска решений можно задать условия – вести ограничения. При поиске решения целевая ячейка должна содержать формулу и быть прямо или косвенно связанной с ячейками с изменяемыми значениями. Введите команду СЕРВИС - НАДСТРОЙКИ и диалоговом окне Надстройки подключите надстройку Поиск решения.

1. сделайте активным лист2 и присвойте ему имя Поиск.

2. сделайте на этом же листе еще одну копию таблицы. В первой копии таблицы, изменяя одновременно 2 параметра, подберите значения срока вклада и процентной вкладки, при которых сумма вклада будет составлять 8000 руб.

для этого выполните действия

3. введите команду СЕРВИС – Поиск решения и в диалоговом окне Поиск решения установите следующие параметры:

Ø адрес целевой ячейки - $B$5 – сумма возврата вклада

Ø подбираемое для целевой ячейки значение – 8000 р.

Ø в поле Изменяя ячейки введите абсолютные адреса ячеек со сроком вклада и величиной процентной ставки.

4. введите ограничения для ячейки со сроком вклада – цел – целое число лет.

5. щелкните по кнопке Выполнить.

6. в диалоговом окне Результаты поиска решения установите

Ø сохранить найденное решение

Ø тип отчета - Результаты

7. во второй копии таблицы на листе Поиск выполните еще раз операцию Поиск решения, установив следующие параметры

Ø адрес и значение целевой ячейки – сумма возврата вклада 8000 р.

Ø в поле Изменяя ячейки введите абсолютные адреса ячеек с размером вклада, сроком вклада и величиной процентной ставки

Ø добавьте ограничения для ячейки с величиной процентной ставки:<=7%.

8. сохраните результаты поиска решения в виде сценария под именем Поиск и восстановите в таблице исходные значения

Создание таблиц подстановки.

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

1. сделайте активным лист 3 и присвойте ему имя Таблицы подстановки.

2. перед началом таблицы вставьте 2 пустые строки

3. сделайте на этом же листе еще 2 копии таблицы с 5 пустыми строками перед каждым экземпляром таблицы.

4. в качестве переменной используйте процентную ставку (ячейку ввода В5), которая может принимать значения от 3 до 10%. Введите эти значения в столбец D согласно приведенному ниже образцу

В ячейку, находящуюся правее и выше первого введенного в столбец D значения, введите формулу для вычисления суммы возврата вклада: =В3*В6. выделите диапазон ячеек D2:E10, содержащий подставляемые значения процентных ставок и формулу для расчета суммы возврата вклада.

5. введите команду ДАННЫЕ – Таблица подстановки и в диалоговом окне Подставлять значения по строкам в: введите абсолютный адрес ячейки ввода (с процентной ставкой) - $B$5.

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

Для создания таблицы подстановки с одной переменной и 2 и более формулами дополнительные формулы вводят справа от ранее введенной формулы в той же строке. При этом следует иметь в виду, что все используемые для подстановки формулы должны быть прямо или косвенно связаны с одной и той же ячейкой ввода.(В5)

1. добавьте в таблицу подстановки в ячейку F2 вторую формулу для расчета коэффициента увеличения вклада: =(1+B5)^B4.

2. выделите необходимый диапазон ячеек (D2:F10), введите команду ДАННЫЕ - Таблица подстановки и в диалоговом окне введите абсолютный адрес ячейки ввода $B$5.