Анализ данных. Подбор параметра. Решение оптимизационных задач

↑

▷ Содержание

Стр 1 из 3Следующая ⇒

Анализ данных

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

Пусть имеется формула, которая прямо или косвенно зависит от некоторого параметра. Задача состоит в определении такого значения этого параметра, которое позволяет получить нужный результат формулы.

Математическая суть задачи состоит в решении уравнения , где   описывается заданной формулой,  - искомый параметр,  - требуемый результат формулы.

Для решения этой задачи необходимо выполнить следующие действия:

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 раза больше, чем врач-специалист.

Построить диаграмму, показывающую зарплату каждой должностной единицы.

С помощью команды Подбор параметра решаются задачи, в которых требуется найти значение одного параметра.

Команда Сервис®Поиск решения... позволяет решать более сложные задачи: находить значения нескольких параметров или комбинации параметров, определяющих оптимальное (наибольшее или наименьшее) или фиксированное значение исследуемой функции. При этом для изменяемых параметров можно задавать ограничения, в пределах которых будет осуществляться поиск их значений.

Если команды Поиск решения нет в меню Сервис, то надо ее установить, т.к. она является надстройкой: выберите команду Сервис®Надстройки… и в списке Доступные надстройки выполните установку команды Поиск решения (флажок).

Задачи, решаемые с помощью инструмента Поиск решения, можно сформулировать следующим образом:

дана функция  (целевая функция);

найти  такие, что целевая функция достигает минимального значения, максимального значения или некоторого фиксированного значения;

на искомые переменные  могут накладываться ограничения (в виде равенств или неравенств).

Рис.9.4. Диалоговое окно команды Поиск решения

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

Ø переключатель Равной служит для выбора варианта оптимизации значения целевой ячейки;

Ø поле Изменяя ячейки используется для указания ячеек, значения которых изменяются в процессе поиска решения (имена ячеек разделяются запятыми);

Ø кнопка Предположить используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую указана в поле Установить целевую ячейку;

Ø поле Ограничения служит для отображения списка граничных условий поставленной задачи (задаются ограничения с помощью кнопок Добавить, Изменить, Удалить);

Ø кнопка Параметры используется для вывода диалогового окна Параметры поиска решения, позволяющего сохранить параметры поиска (Сохранить модель…) или использовать уже сохраненные параметры (Загрузить модель…);

Ø кнопка Выполнить используется для запуска поиска решения поставленной задачи;

Ø кнопка Закрыть позволяет выйти из диалогового окна без запуска поиска решения поставленной задачи;

Ø кнопка Восстановить служит для очистки полей окна и восстановления параметров поиска, используемых по умолчанию.

Прежде чем использовать инструмент Поиск решения, необходимо сформулировать и оформить решаемую задачу: определить целевую функцию (формулу, которая ссылается на изменяемые ячейки); наложить ограничения на величины, участвующие в решении задачи; заполнить электронную таблицу данными.

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

6.Пусть в колхозе требуется распределить площадь пашни между двумя культурами в соответствии со следующими данными:

Познавательные статьи:



Техника нижней прямой подачи мяча

Комплекс физических упражнений для развития мышц плечевого пояса

Стандарт Порядок надевания противочумного костюма

Общеразвивающие упражнения без предметов