Подбор параметра
 Скачать 0.59 Mb.
|
|
Подбор параметра При обработке табличных данных часто возникает необходимость в прогнозировании результата на основе известных исходных данных или наоборот, в определении того, какими должны быть исходные значения, позволяющие получить указанный результат. Когда желаемый результат вычислений по формуле известен, но неизвестны значения, необходимые для получения этого результата, можно воспользоваться средством Подбор параметра, выполнив действия: вкладка Данные–группа Работа с данными–кнопка Анализ «что-если»-пункт Подбор параметра. При подборе параметра необходимо, чтобы ячейка с целевым значением содержала формулу со ссылкой на ячейку с изменяемым значением. Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула в целевой ячейке не даст нужного результата. В рассмотренном далее примере роль целевых могут выполнять ячейки столбцов Надбавка за стаж (%) и Зарплата в зависимости от требуемых вычислений. Предположим, что условие задачи сформулировано таким образом: Каким должен быть стаж у Сомовой О.А., чтобы надбавка составляла 20%? Для нахождения требуемого целевого значения: 1. выделить целевую ячейку Е9, содержащую формулу со ссылкой на ячейку с исходным значением; 2. выберите команду: вкладка Данные–группа Работа с данными–кнопка Анализ «что-если»- пункт Подбор параметра; 3. в окне диалога Подбор параметра в поле Установить в ячейке введем ссылку на ячейку с формулой Е9, в поле Значение – ожидаемый результат 20%, в поле Изменяя значения ячейки – ссылку на ячейку, в которой будет храниться значение подбираемого параметра С9 (содержимое этой ячейки не может быть формулой). Диалоговое окно Подбор параметра после заполнения имеет вид: Если подобранное значение необходимо сохранить, то нажмите на Оk, и результат будет сохранен в ячейке, заданной ранее в поле Изменяя значения ячейки. После нажатия на кнопку Ok Excel выведет окно диалога Результат подбора параметра. Для восстановления значения, которое было в ячейке С9 до использования команды Подбор параметра, нажмите кнопку Отмена. В результате выполненных действий исходная таблица примет вид: При подборе параметра Excel использует итерационный (циклический) процесс. Количество итераций и точность устанавливаются в меню Файл– Параметры–Формулы–Параметры вычислений. Если Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне диалога Результат подбора параметра и прервать вычисление, а затем нажать кнопку Шаг, чтобы выполнить очередную итерацию и просмотреть результат. При решении задачи в пошаговом режиме появляется кнопка Продолжить - для возврата в обычный режим подбора параметра. Поиск решения Поиск решения в MS Excel является надстройкой, поэтому она не всегда может быть вызвана с панели управления. В этом случае необходимо выполнить последовательность действий: Файл–Параметры– Надстройки–Поиск решения. В результате на вкладке Данные появится кнопка для вызова данной надстройки, которая позволяет решать задачи оптимизационного моделирования. Оптимизационные модели широко используются в экономике и технике. Среди них задачи подбора сбалансированного рациона питания, оптимизации ассортимента продукции, транспортная задача и пр. Модели всех задач на оптимизацию состоят из следующих элементов: • Переменные – неизвестные величины, которые нужно найти при решении задачи. • Целевая функция – величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели. • Ограничения – условия, которым должны удовлетворять переменные. Рассмотрим использование надстройки Поиск решения при решении задачи, сформулированной следующим образом: Какими должны быть значения оклада и надбавки, чтобы зарплата Сомовой О.А. составляла 15000 рублей при условии, что надбавка за стаж не должна превышать 30% и оклад не должен быть больше 14000 рублей? Поиск решения будет осуществляться для той же таблицы, для которой ранее выполнялся подбор параметра. Давайте разберемся, что здесь является переменными, что целевой функцией, что ограничениями. Нам необходимо найти конкретное значение заработной платы Сомовой О.А. Это и есть целевая функция. Она зависит от оклада и надбавки. Это и будут наши переменные. Теперь ограничения. В условии сказано, что надбавка за стаж не должна превышать 30% и оклад не должен быть больше 14000 рублей. Переменные, то есть оклад и надбавка, находятся в ячейках D12, E12. Целевая функция – в ячейке F12. Обратите внимание, что целевая функция является формулой, содержащей ссылки на ячейки с переменными и исходными данными. После вызова надстройки Поиск решения отрывается диалоговое окно, в котором необходимо задать соответствующие адреса ячеек и условия. Ограничения неотрицательности значений можно также задать с помощью этого диалога, достаточно установить флажок Сделать переменные без ограничений неотрицательными. Для добавления ограничений после нажатия кнопки Добавить появляется диалоговое окно: Если нажать Ок, ограничение будет добавлено, а диалог закроется. Чтобы несколько раз не открывать диалог, сделана кнопка Добавить. Ограничение сохраняется, а диалог очищается для добавления следующего ограничения. После нажатия кнопки Найти решение на экран будет выведено диалоговое окно Результаты поиска решения. Можно сохранить найденное решение или восстановить исходные значения. Результат выполнения процедуры Поиск решения помещается в ячейки таблицы. Решение может быть найдено не всегда. Бывают слишком сложные модели, модели совсем не имеющие решений (модели с несходимыми ограничениями). Кроме того, в параметрах модели можно задать максимальное время решения, число итераций, точность и другие установки, которые накладывают дополнительные ограничения, не позволяющие найти оптимальное решение. Все настройки модели (целевая ячейка, область переменных, ограничения, параметры) сохраняются в книге и при изменении исходных данных их не нужно вводить заново. Достаточно открыть надстройку и запустить повторный поиск решения. |