Задание 3: Использование надстроек для анализа данных и моделирования в MS Excel

1.

Задание 3: Использование
надстроек для анализа данных и
моделирования в MS Excel

2. Рассмотрим еще один способ анализа данных на основе технологии подбора параметра, поиска решения, таблиц подстановок,

организации сценариев.
Задание :
• Изучить возможности надстроек Поиск решения
и Подбор параметра ориентируясь на
приведенные в презентации примеры
• Выполнить приведенный в презентации пример
и сохранить его в общей книге заданий ms excel
(эти листы можно назвать – подбор
параметра_пример, поиск решения_ пример)
• Сформировать СВОЮ задачу по анализу данных
с помощью надстроек Поиск решения и Подбор
параметра

3. Работа с надстройками

• Надстройка «Подбор параметра»
• Надстройка «Поиск решения»

4. Надстройка «Поиск решения». Формулировка задачи

• Какие должны быть ежемесячные вклады в
течение 12 месяцев при процентной ставке
8% годовых, чтобы по истечении срока на
счету накопилась сумма в 150096,52 руб.?
• То есть: Что будет, если?

5. Пример использования – расчет процентной ставки и выплат по вкладу

• Определим входные параметры вклада:
– Простая годовая ставка в процентах.
– Ежемесячный вклад.
– Начальный взнос.
– Срок размещения вклада в месяцах.
– Количество выплат в году.
• Что нужно вычислить:
– Эффективная годовая ставка.
– Будущая стоимость вклада.

6.

7. В MS Excel создадим таблицу

• Будущая стоимость вклада:
– Есть функция =БС(B2/12;G2;-D2).
– Вызов – вставка->функции-> в описании
финансовые (или найти БС).

8.

9.

10.

11.

12.

Ответ: для достижения требуемой суммы ежемесячные вклады
должны быть равны 12000,44 рубля

13. Надстройка «Поиск решения».

• В Excel существует инструмент анализа «Поиск решения»
(как сделать, чтобы).
• Процедура поиска решения позволяет найти оптимальное
значение формулы содержащейся в ячейке, которая
называется целевой. Эта процедура работает с группой
ячеек, прямо или косвенно связанных с формулой в
целевой ячейке.
• Чтобы получить по формуле, содержащейся в целевой
ячейке, заданный результат, процедура изменяет значения
во влияющих ячейках. Чтобы сузить множество значений,
используемых в модели, применяются ограничения.

14. Вызов надстройки

• Сервис -> Надстройки (включение).
• Сервис -> Поиск решения.
• ИЛИ
• ФАЙЛ –ПАРАМЕТРЫ – НАДСТРОЙКИ –
выбрать ПОИСК РЕШЕНИЯ

15. Пример применения надстройки

Задача: сколько нужно производить изделий, чтобы получить нужную прибыль?
Создадим исходную таблицу и вставим формулы

16.

17.

18. Типичный вопрос для этого случая

• Сколько производить изделий, чтобы
прибыль была как можно больше
(целевая ячейка B7).

19.

20.

21.

22.

23.

24.

Ответ: для прибыли в 20400 у.е. необходимо, чтобы
изделие A производилось в количестве 25 штук, а изделие B – 6 штук