Тема 7. Функция автоматизации расчетов. Подбор параметров.

КРАТКАЯ СПРАВКА

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

Данная команда определяет неизвестную величину, приводящую к требуемому результату.

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

Математическая суть задачи состоит в решении уравнения f(х) = а, где функция f(x) описывается заданной формулой, x - искомый параметр, а - требуемый результат формулы, поэтому функцию Подбор параметров очень часто используют при решении уравнений.

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

Примеры выполненных  заданий:

Пример №1:   Решить уравнение   x² = 2

Используя "Подбор параметра" найти оба корня уравнения:

x² = 2

Решение:

Для нахождения всех корней уравнения путем подбора параметра необходимо:

1. Привести уравнение к виду f(x)=а, где f(х) – заданная формула, зависящая от значения х, а параметр а – результат формулы.
   Пусть f(x)= х², а результат этой формулы а=2.
2. Теперь целесообразно создать таблицу значений f(x) для N значений аргумента х. Возьмем для начала N=10 и составим таблицу так, чтобы каждое последующее х зависело от предыдущего. Для этого: первое значение х желательно взять отрицательное (если оно входит в область допустимых значений), а остальные вычислить, например, с шагом 0,5 (рис.7.1.). Получится таблица как на рис.7.2.

Рис.7.1. Пример заполнения таблицы

Рис.7.2. Заполненная таблица

3. Осуществим подбор параметра для первого взятого нами значения х. f(x) должно равняться 2 по условию задачи.

Выберем пункт меню Сервис/Подбор параметра и заполним окно следующими параметрами (рис.7.3.):

Рис.7.3. Образец заполнения окна Подбор параметров

                Нажимаем ОК и получаем следующие изменения в таблице (рис.7.4.):

Рис.7.4. Полученный результат после подбора параметров

4. Отформатируем значения f(x) до 0 знаков после запятой и проанализируем полученные результаты. Там, где f(x)=2, будет искомый результат. Мы видим, что в ячейке В2 получен искомый результат и что между числами 1 и 3 в ячейках В7 и В8 может быть тоже значение 2, но оно не просматривается, поэтому необходимо изменить шаг между значениями х (с 0,5 на меньший – например 0,4) и пересчитать все значения аргумента х, значения f(x) пересчитаются автоматически.  Мы видим, что для f(x)=2 найдено два решения, а для последующих значений х функция f(x) возрастает, т.е. значение 2 принимать не будет. Итак, нами найдено два корня, выделим полученные результаты как показано на рис.7.5.

Рис.7.5. Результат нахождения корней уравнения

Пример №2:     Составление штатного расписания

Пусть известно, что в штате больницы состоит 16 санитарок, 18 медсестер, 10 врачей, 2 заведующих отделениями, главный врач, заведующий аптекой, заведующая хозяйством и заведующий больницей. Общий месячный фонд зарплаты составляет S (200 000 руб.). Необходимо определить, какими должны быть оклады сотрудников больницы.

Известно, что

-     Медсестра получает в 1,5 раза больше санитарки

-     Врач - в 3 раза больше санитарки

-     Зав.отделением – на 1000 руб. больше, чем врач

-     Зав.аптекой – в 2,5 раза больше санитарки

-     Зав.хозяйством – на 1500 руб. больше медсестры

-     Глав.врач – в 4 раза больше санитарки

-     Зав.больницей – на 1500 руб. больше глав.врача.

Решение: Для решения задачи необходимо создать таблицу с исходными данными. За основу взять любой оклад санитарки, например, 2000 руб., а остальные оклады вычислять, исходя из него (рис.7.6.)

Рис.7.6.  Пример создания таблицы.

Получим следующую таблицу данных (рис.7.7.)

Рис.7.7.  Таблица исходных данных

Итак, зарплата сотрудников зависит от зарплаты санитарки. Поэтому можно воспользоваться функцией  Подбор параметров и подобрать параметр – зарплата санитарки такой, чтобы месячный фонд зарплаты составлял 200 000 руб.

Для этого активизируйте команду Подбор параметров из меню Сервис (рис.7.8.).

Рис.7.8.    Образец заполнения окна Подбор параметров

После нажатия ОК, можно заметить изменение величины окладов сотрудников (рис.7.9.)

Рис.7.9.  Таблица полученных данных после Подбора параметров

Скачано с www.znanio.ru