Презентация к уроку по информатике Оптимизация параметров и поиск решения

ОПТИМИЗАЦИЯ ПАРАМЕТРОВ И ПОИСК РЕШЕНИЯПреподаватель информатики ФФ МГОУ Пысенкова Л.А.Фрязино 2016 Подбор параметраЗадачи оптимизации можно решать с помощью надстройки электронных таблиц «Подбор параметра». Процедура поиска параметра позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, связанных с формулой в целевой ячейке. Процедура изменения значений во влияющих ячейках позволяет получить заданный результат по формуле, содержащейся в целевой ячейке. Для сужения множества значений применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.Технология использования надстройки Подбор параметра на примере1. Создание штатного расписания больницы Задача 1.Штатное расписание больницы Заведующий больницей должен составить штатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. Общий месячный фонд зарплаты составляет 10 000$. Для нормальной работы больницы нужно: 5 - 7 санитарок, 8 - 10 медсестер, 10 - 12 врачей, 1 зав. аптекой, 3 зав. отделениями,1 главврач, 1 завхоз, 1 зав. больницей. На некоторых должностях число людей может меняться. Например, зная, что найти санитарок трудно, руководитель может принять решение сократить число санитарок, чтобы увеличить оклад каждой из них. Допустим, решено, что:медсестра должна получать в 1,5 раза больше санитарки, т.е. A=1,5, B=0;врач  в 3 раза больше санитарки, т.е. А=3, B=0 зав. отделением  на 30$ больше, чем врач, т.е. A=3, B=30зав. аптекой в 2 раза больше санитарки, т.е. A=2, B=0 завхоз  на 40$ больше медсестры, т.е. A=1,5, B=40 главврач  в 4 раза больше санитарки, т.е. A=4, B=0 зав. больницей  на 20$ больше главврача, т.е. A=4, B=20 Задав количество человек на каждой должности, можно составить уравнение:N1  ( A1  C + B1 ) + N2  ( A2  C + B2 ) + ... + N8  ( A8  C + B8 ) = 10000где N1 - количество санитарок, N2 - количество медсестер, и т.д. Введём исходные данные в рабочий лист электронной таблицы Создаём компьютерную модельВ столбце D вычислим заработную плату для каждой должности, а в столбце F – суммарную зарплату всех сотрудников: D3*E3, формулу скопируем до ячейки F10 Рабочий лист электронной таблицы будет выглядеть так: В ячейке F12вычислим суммарный фонд заработной платы больницы Чтобы определить оклад санитарки так, чтобы расчетный фонд был равен заданному надо:Активизировать команду Подбор параметра во вкладке Данные | Работа с данными | Анализ «Что, если»;В поле "Установить в ячейке" появившегося окна ввести ссылку на ячейку F12, содержащую формулу;В поле "Значение" набрать искомый результат 10 000;В поле "Изменяя значение ячейки" ввести ссылку на изменяемую ячейку D3 и щелкните на кнопке ОК. Надстройка Подбор параметра Определили, какими должны быть оклады сотрудников больницы. Поиск решенияЗадачи оптимизационного моделирования можно решать с помощью надстройки электронных таблиц «Поиск решения». Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, связанных с формулой в целевой ячейке. Процедура изменения значений во влияющих ячейках позволяет получить заданный результат по формуле, содержащейся в целевой ячейке. Для сужения множества значений применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.Технология использования надстройки Поиск решения показана на примере задачи:Производство конфет Задача 2Производство конфетПредположим, что мы решили производить несколько видов конфет. Назовем их условно «А», «В», «С». Известно, что реализация 10 кг конфет «А» дает прибыль 9 у.е., «В» − 10 у.е., «С» − 16 у.е.Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько десятков килограмм необходимо производить, чтобы общая прибыль от реализации была максимальной.Нормы расхода сырья на производстве 10 кг конфет каждого вида приведены в таблице 1. Таблица 1Нормы расхода сырья{284E427A-3D55-4303-BF80-6455036E1DE7}СырьеНормы расхода сырьяЗапас сырьяАВСКакао181512360Сахар648192Наполнитель533180Прибыль91016 Выполнение работыЗапустите табличный процессор Excel.Заполните таблицу в соответствии с образцом: Технология работы:В меню Сервис активизируйте команду Поиск решения Установите целевую ячейку: равной значениюУкажите изменяемые ячейки:Опишите ограничения:В Параметрах укажите Линейность моделиЗапустите Поиск решения$C$6 максимальному$B$3:$B$5$A$10<=360 $B$10<=192 $C$10<=180$B3>=0 $B$4>=0 $B$5>=0 Поиск решения Результаты вычислений:Из решения видно, что оптимальный план выпуска предусматривает изготовление 80 кг конфет «В» и 20 кг конфет «С». Конфеты «А» производить не стоит. Полученная прибыль составит 400 у.е.