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

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

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

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

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

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

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