Анализ данных
Подбор параметра
Пусть имеется формула, которая прямо или косвенно зависит от некоторого параметра. Задача состоит в определении такого значения этого параметра, которое позволяет получить нужный результат формулы.
Математическая суть задачи состоит в решении уравнения 
, где 
описывается заданной формулой, 
- искомый параметр, 
- требуемый результат формулы.
Для решения этой задачи необходимо выполнить следующие действия:
1.Выделить ячейку, содержащую формулу, для которой нужно найти определенное значение.
2.В меню Сервис выбрать команду Подбор параметра.
3.В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).
4.В поле Значение ввести значение, которое нужно получить по заданной формуле.
5.В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).
Если процесс подбора заданного значения успешно завершится, то Excel выведет окно Результат подбора параметра, в котором будет указано Подбираемое значение и Текущее значение (заданное и подобранное значение параметра). При нажатии кнопки ОК значения будут сохранены. При нажатии кнопки Отмена исходные значения будут восстановлены.
Процесс подбора параметра является итерационным: в изменяющуюся ячейку один за другим подставляются значения параметра, пока не будет достигнут заданный результат. По умолчанию команда Подбор параметра прекращает вычисления при получении результата с относительной погрешностью 0,001 или при выполнении 100 итераций. При этом результат подбора параметра не всегда может устраивать. Чтобы изменить параметры выполнения команды на вкладке Вычисления диалогового окна Параметры (Сервис®Параметры…) надо установить новые значения в поле Относительная погрешность и Предельное число итераций.
Разберите пример: найти все корни функции 
в диапазоне значений аргумента 
. Относительная погрешность вычислений 
, число итераций 1000.
1.Выполните на одном листе книги практическое задание: уточните корни четырех уравнений с точностью до 0,001.
| № задания | Уравнение | Интервал | Уравнение | Интервал |
| 
| 
| 
| |
| 
| 
| 
|
Проверьте результаты:
| № задания | Результат | |
| 2,444 | -2,276 | |
| 1,171 | 0,947 |
2. Группе студентов за активное участие в спортивных мероприятиях выделена премия в размере 9500 рублей. Эта сумма должна быть поделена между всеми студентами группы с учетом их вклада. Для этого каждому студенту присваивается коэффициент вклада, который задается в интервале от 0 до 1. Определить сумму премии каждого студента.
Решение.
Создайте на втором листе книги таблицу, содержащую информацию о студентах: фамилия, имя, коэффициент (см. далее).
В ячейку С3 введите значение 150 рублей в предположении, что на коэффициент 1 приходится указанная сумма.
В ячейку D5 введите формулу вычисления суммы премии в рублях: 
.
Рассчитайте сумму премии для каждого студента и общий премиальный фонд.
Выполните подбор параметра в ячейке D25 (значение 9500 рублей), изменяя значение ячейки $C$3.
Проверьте полученное решение:
3. Минимальная продуктовая потребительская корзина содержит:
Известно, что:
· картофель в 2 раза дешевле хлеба;
· крупа на 2 рубля дешевле хлеба;
· масло растительное в 7 раз дороже хлеба;
· масло сливочное в 6 раз дороже хлеба;
· молоко на 1 рубль дороже хлеба;
· мясо в 10 раз дороже хлеба;
· рыба в 3 раза дороже хлеба;
· сахар на 2 рубля дороже хлеба;
· сыр на 5 рублей дороже мяса;
· фрукты на 3 рубля дороже рыбы;
· макаронные изделия на 4 рубля дешевле рыбы;
· соль в 2 раза дешевле хлеба;
· овощи на 1 рубль дешевле хлеба.
Необходимо вычислить стоимость 1 кг хлеба так, чтобы стоимость продуктовой корзины равнялась размеру минимальной зарплаты — 840 рублям.
Указание:
1.Определите числовой формат в ячейках С3:С16 как Основной “кг”.
2.Определите начальное значение стоимости 1 кг хлеба, например, 5 рублей.
3.Заполните ячейки D3:D16 формулами в соответствии с условием задачи.
4.Дополните таблицу столбцом “Стоимость заданного количества кг”, рассчитав по формулам стоимость продуктов корзины.
5.Дополните таблицу итоговой суммой продуктовой корзины.
6.Выполните подбор значения стоимости хлеба, определив значение стоимости продуктовой корзины 840 рублей.
Проверьте результат:
4. Фонд зарплаты технического отдела — 50 тыс. рублей в месяц (это значение равняется сумме всех полученных зарплат в отделе за месяц).
В техническом отделе по штату должен быть следующий состав:
· лаборант — 1 человек;
· техник — 3 человека;
· инженер 3-й категории — 3 человека;
· инженер 2-й категории — 4 человека;
· инженер 1 категории — 4 человека;
· ведущий инженер — 2 человека;
· главный специалист отдела — 1 человек;
· начальник отдела — 1 человек.
Рассчитать зарплату каждого сотрудника отдела, если известно, что:
· техник имеет зарплату в 1,5 раза больше, чем лаборант;
· инженер 3-й категории имеет зарплату в 2 раза больше, чем лаборант;
· инженер 2-й категории имеет зарплату в 1,5 раза больше, чем техник;
· инженер 1-й категории имеет зарплату в 2 раза больше, чем техник;
· ведущий инженер имеет зарплату в 2 раза больше, чем инженер 3-й категории;
· главный специалист отдела имеет зарплату в 2,5 раза больше, чем инженер 3-й категории;
· начальник отдела имеет зарплату в 2,5 раза больше, чем инженер 2-й категории.
Построить диаграмму, показывающую зарплату каждой должностной единицы.
Проверьте результат:
5. Фонд зарплаты сельской поликлиники — 150 тыс. рублей в месяц (это значение равняется сумме всех полученных зарплат в поликлинике за месяц).
В поликлинике по штату должен быть следующий состав:
· лаборант — 1 человек;
· санитарка — 3 человека;
· врач-терапевт — 5 человек;
· врач-специалист — 4 человека;
· медсестра — 9 человек;
· зав. отделением — 2 человека;
· главный врач — 1 человек.
Рассчитать зарплату каждого сотрудника поликлиники, если известно, что:
· медсестра имеет зарплату в 1,5 раза больше, чем лаборант;
· врач-терапевт имеет зарплату в 2 раза больше, чем лаборант;
· врач-специалист имеет зарплату в 2,5 раза больше, чем лаборант;
· санитарка имеет зарплату в 1,5 раза меньше, чем лаборант;
· зав. отделением имеет зарплату в 2 раза больше, чем врач-терапевт;
· главный врач имеет зарплату в 2,5 раза больше, чем врач-специалист.
Построить диаграмму, показывающую зарплату каждой должностной единицы.
Решение оптимизационных задач
С помощью команды Подбор параметра решаются задачи, в которых требуется найти значение одного параметра.
Команда Сервис®Поиск решения... позволяет решать более сложные задачи: находить значения нескольких параметров или комбинации параметров, определяющих оптимальное (наибольшее или наименьшее) или фиксированное значение исследуемой функции. При этом для изменяемых параметров можно задавать ограничения, в пределах которых будет осуществляться поиск их значений.
| Если команды Поиск решения нет в меню Сервис, то надо ее установить, т.к. она является надстройкой: выберите команду Сервис®Надстройки… и в списке Доступные надстройки выполните установку команды Поиск решения (флажок). |
Задачи, решаемые с помощью инструмента Поиск решения, можно сформулировать следующим образом:
дана функция 
(целевая функция);
найти 
такие, что целевая функция достигает минимального значения, максимального значения или некоторого фиксированного значения;
на искомые переменные могут накладываться ограничения (в виде равенств или неравенств).
|
Ø в поле Установить целевую ячейку указывается адрес ячейки, значение которой необходимо максимизировать, минимизировать или установить равной заданному значению;
Ø переключатель Равной служит для выбора варианта оптимизации значения целевой ячейки;
Ø поле Изменяя ячейки используется для указания ячеек, значения которых изменяются в процессе поиска решения (имена ячеек разделяются запятыми);
Ø кнопка Предположить используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую указана в поле Установить целевую ячейку;
Ø поле Ограничения служит для отображения списка граничных условий поставленной задачи (задаются ограничения с помощью кнопок Добавить, Изменить, Удалить);
Ø кнопка Параметры используется для вывода диалогового окна Параметры поиска решения, позволяющего сохранить параметры поиска (Сохранить модель…) или использовать уже сохраненные параметры (Загрузить модель…);
Ø кнопка Выполнить используется для запуска поиска решения поставленной задачи;
Ø кнопка Закрыть позволяет выйти из диалогового окна без запуска поиска решения поставленной задачи;
Ø кнопка Восстановить служит для очистки полей окна и восстановления параметров поиска, используемых по умолчанию.
Прежде чем использовать инструмент Поиск решения, необходимо сформулировать и оформить решаемую задачу: определить целевую функцию (формулу, которая ссылается на изменяемые ячейки); наложить ограничения на величины, участвующие в решении задачи; заполнить электронную таблицу данными.
С помощью инструмента Поиск решения можно решить не всякую задачу оптимизации. Если оптимальное решение не будет найдено, то в диалоговом окне Результаты поиска решения появится сообщение о неудачном завершении поиска.
6. Пусть в колхозе требуется распределить площадь пашни между двумя культурами в соответствии со следующими данными:
| Культура | Площадь, га | Урожай, ц/га | Затраты, руб./га | Цена за 1 ц, руб. | Затраты, человеко-дней на 1 га |
| x | |||||
| y |
Кроме того, заданы ресурсы производства:
земли —1800 га, человеко-дней —8000.
Величины x и y являются неизвестными и подлежат определению.
Построение математической модели задачи включает в себя:
задание целевой функции (ее надо максимизировать или минимизировать);
задание системы ограничений в форме линейных уравнений и неравенств;
требование неотрицательности переменных.
Ограничения задачи имеют вид:
ограничение по площади: 
;
ограничение по человеко-дням: 
, или 
.
Кроме того ясно, что 
, 
.
Для прибыли получаем: 
.
Учитывая все условия задачи, приходим к ее математической модели: найти решение системы неравенств
,
которое соответствует максимуму функции 
.
Щелкните на кнопке Параметры этого диалогового окна Поиск решения и установите флажки Линейная модель и Неотрицательные значения, <ОК>, <Выполнить>.
Оптимальное решение найдено: в ячейке С2 значение x =1250 (га), в ячейке С3 значение y =550 (га).
7. Создайте таблицу по форме, вставьте необходимые формулы в колонку “Сумма выручки” и ячейки “Объем выпуска-итого”.
Используя инструмент Поиск решения, выполните расчет объема выпуска каждого товара, обеспечивающего максимальную сумму общей выручки, считая, что объем выпуска Изделия1<=10. Изделия2<=15, Изделия3<=20, Изделие4<=5, а общее количество выпускаемых изделий <=35.
| Наименование товара | Объем выпуска | Расчет реализации | |
| Цена | Сумма выручки | ||
| Изделие1 | |||
| Изделие2 | |||
| Изделие3 | |||
| Изделие4 | |||
| Итого |
8. Предполагается, что рацион коров составляется из двух видов кормов —сена и концентратов. Суточная потребность кормов на 1 корову равна 20 кормовым единицам. В таблице приведены числовые данные о себестоимости кормов в данном хозяйстве.
| Виды кормов | Содержание кормовых единиц в 1 кг кормов | Себестоимость кормов в коп. |
| Сено | 0,5 | 1,5 |
| Концентраты | 1,0 | 2,5 |
Найти самый дешевый рацион, если ежедневный рацион кормления сельскохозяйственных животных должен включать не менее 16 кг сена.
Указание: ограничения 
, 
, целевая функция 
.
9. Мебельная фабрика выпускает кресла двух видов. На изготовление кресла первого типа расходуется 2 м досок стандартного сечения, 0,8 м2 обивочной ткани и затрачивается 2 человеко-часа, а на изготовление кресла второго типа — соответственно 4 м, 1,25 м2 и 1,75 человеко-часа. Известно, что цена одного кресла первого типа равна 15 рублей, второго типа — 20 рублей. Сколько кресел каждого типа надо выпускать, чтобы стоимость выпускаемой продукции была максимальной, если фабрика имеет в наличии 4400 м досок, 1500 м2 обивочной ткани и может затратить 3200 человеко-часов рабочего времени на изготовление этой продукции?
10. Хозрасчетной бригаде выделено для возделывания кормовых культур 100 га пашни. Эту пашню предполагается занять кукурузой и свеклой, причем свеклой решено занять не менее 40 га. Как должна быть распределена площадь пашни по культурам, чтобы получилось наибольшее число кормовых единиц? При этом должно быть учтено следующее: 1 ц кукурузного силоса содержит 0,2 кормовой единицы, 1 ц свеклы — 0,26 ц кормовой единицы, на возделывание 1 га кукурузного поля необходимо затратить 38 человеко-часов труда механизаторов и 15 человеко-часов ручного труда, а на 1 га поля, занятого свеклой, соответственно 43 и 185 человеко-часов, ожидаемый урожай кукурузы — 500 ц с 1 га, а свеклы — 200 ц с 1 га, наконец, всего на возделывание кормовых культур можно затратить 4000 человеко-часов труда механизаторов и 15000 человеко-часов ручного труда.
