Использование команды Подбор параметра при изучении MS Excel

Практическая работа
Использование команды Подбор параметра в MS Exсel

Цель занятия: ознакомиться с работой команды Подбор параметра в программе Microsoft Excel.
.
Задачи занятия: приобрести практические навыки работы с командой Подбор параметра, повторить способы адресации ячеек, их форматирование и различные форматы данных.
Порядок выполнения работы:
Выполните задания, следуя ходу их работы и используя нижеприведенные теоретические сведения.
Теоретические сведения

Команда Подбор параметра может считаться частным вариантом программы Поиск решения. Принцип ее функционирования аналогичен принципу в программе Поиск решения, но возможности ограничиваются заданием одной итоговой и одной изменяемой ячейки. Команда предназначена для получения заданного значения в итоговой ячейке путем подбора значения в изменяемой ячейке. Для получения результата в команде требуется указать итоговую ячейку, ее желаемое значение и изменяемую ячейку, влияющую на содержимое итоговой ячейки. При этом в изменяемой ячейке должно содержаться числовое значение, а не формула. В противном случае команда не сможет найти решение.
Задание 1

Рассмотрим следующую задачу:
Мы потратили кучу денег , чтобы вывести произведенный товар на рынок. Мы знаем, во что нам обошлось его производство. Мы думаем, что сможем продавать его по определенной цене.
Итак, сколько же единиц мы должны продать, чтобы, наконец, получить первую прибыль? Когда некоторое количество проданных единиц товара даст доход, который в точности покроет все издержки, будет достигнута точка безубыточности. Команда Подбор параметра поможет найти решение задачи с помощью поиска точки безубыточности. Воспользуемся этой командой, чтобы выяснить, сколько единиц нам следует продать для достижения точки, в которой Общие доходы- Общие расходы =0

Ход работы:

Посмотрим на рис.1 Общие расходы расположены в ячейке С11. Общее количество произведенных единиц товара будет отображаться в ячейке С13. Общие доходы – в ячейке С5.
Общие доходы – это доходы на единицу продукции, умноженные на количество произведенных единиц.
В меню Сервис выберем команду Подбор параметра (в MS Office 2007 - Данные Анализ “Что - если» Подбор параметра). На экране появится диалоговое окно, показанное на рис.1.
В поле Установить в ячейке заносится ссылка на ячейку с формулой, которая будет использоваться при подборе параметра. В нашем случае это - С14. Мы даем указание команде Подбор параметра подобрать для формулы Общие доходы - Общие расходы конкретное значение. Если сюда поместить не формулу, а что-то другое, то на экране появится сообщение об ошибке.
Программа Подбор параметра позволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки. При этом целевая ячейка должна прямо или косвенно ссылаться на ячейку с изменяемым значением.


Рис. 1

Задание 2

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

Ход работы:

Создайте следующую таблицу:


А
В

1
Размер вклада
50 000 р.

2
Срок вклада, лет
5

3
Процентная ставка
11%

4
Коэффициент увеличения вклада
=(1+В3)^В2

5
Сумма возврата вклада
=В1*В4


Коэффициент увеличения вклада при начислении сложных процентов вычисляется по формуле:
=(1+В3)^В2
где В3 – процентная ставка, В2 – срок возврата вклада, а символ ^ - оператор возведения в степень.
Сумма возврата вклада вычисляется в ячейке В5 по формуле:
=В1*В4
Введите исходные данные и формулы в ячейки В4 и В5.
Скопируйте созданную таблицу на этот же лист 2 раза.
В первой копии таблицы рассчитайте процентную ставку, при которой сумма возврата вклада будет составлять 300 000 рублей.
Во второй копии таблицы рассчитайте срок вклада, при котором сумма возврата вклада будет составлять 300 000 рублей.







Самостоятельно
Задание 3

Фермерскому хозяйству требуется закупить семена картофеля на сумму 400000 руб. Затраты семян при посеве на 1 га.- 1000 кг. Поздним картофелем необходимо засеять 7 га, средним картофелем – 6 га, ранним – 5 га.
Вычислить, сколько гектаров необходимо засеять суперранним картофелем, если стоимость картофеля:
раннего- 12 руб. за кг.; суперраннего в 2 раза больше , чем раннего; среднеспелого картофеля на 4 руб. дешевле, чем раннего, и позднего на 4 руб.дешевле, чем среднеспелого.

Задание 4

Сельхозартель закупила молодняк птицы:

Цыплят – несушек -850 штук
Цыплят-бройлеров -1,2 тыс. штук
Утят -500 штук
Гусят -400 штук

На их кормление ежедневно выделяется 150 тыс. рублей.
Цыпленок-несушка ежедневно съедает 300 г корма. Цыпленок –бройлер- в 2,5 раза больше ,чем цыпленок –несушка. Утенок – на 200 г больше, чем цыпленок- несушка. Гусенок – на 200 г меньше цыпленка- бройлера. Стоимость 1 кг корма 90 рублей.
Определить, сколько цыплят-несушек можно докупить, чтобы укладываться в ежедневную сумму кормления (150 тыс. рублей).

15