Решение задач оптимизации с помощью электронных таблиц
Надстройки в электронных таблицах
Возможности электронных таблиц не ограничиваются вычислениями по формулам и построением диаграмм и графиков. С помощью надстроек электронных таблиц можно строить информационные модели, приближенно с заданной точностью решать уравнения методом подбора параметра, решать задачи оптимизационного моделирования методом поиска решений и т.д. Некоторые из надстроек не устанавливаются по умолчанию и требуют установки.
Поиск решения. Поиск решения является надстройкой, которая позволяет решать задачи оптимизационного моделирования. Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, искомый результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут содержать ссылки на другие влияющие ячейки.
Задача оптимизации – поиск оптимального (наилучшего) решения данной задачи при соблюдении некоторых условий.
Алгоритм решения задач
1) Разобрать условие задачи.
2) Построить математическую модель.
3) Выбрать поисковые переменные.
4) Задать ограничения.
5) Выбрать критерий оптимизации.
6) Решить задачу на компьютере.
7) Проанализировать полученные результаты.
Выбрать Главное меню – Данные – группа Работа с данными – кнопка Анализ «Что-если».
Подбор параметра является одним из инструментов анализа «что-если». Этот метод используется при поиске значения аргумента функции, который обеспечивает требуемое значение функции. При подборе параметра изменяется значение в ячейке аргумента функции до тех пор, пока значение в ячейке самой функции не будет возвращать нужный результат. Это как бы подгонка исходных данных задачи к требуемому ответу, известному заранее.
Задача 1. «Покраска пола»
Вычислить количество краски для покрытия пола в спортивном зале.
Сначала измеряют длину a (18,1 ≤ а ≤ 18,3) и
ширину b (7,6 ≤ b ≤ 7,7) пола.
Реальный объект – пол зала – заменяют прямоугольником, для которого S = ab.
При покупке краски выясняют, какую площадь S1 можно покрыть содержимым одной банки (предположим меньше 10 м2), вычисляют необходимое количество банок n=ab/S1.
а, b, S1 – поисковые переменные, значения которых можно изменять.
Необходимо задать ограничения: а ≥ 18,1; а ≤ 18,3; b ≤ 7,6; b ≥7,7; S1 ≤ 10.
Критерий оптимизации: количество банок должно быть минимальным, т.е. n=ab/S1 = min.
Решение на компьютере:
1) Заполнить таблицу, указав произвольные значения для поисковых переменных.
А В
1 Поисковые переменные
2 имя значение
3 а 18,1
4 b 7,6
5 S1 10
6 Критерий оптимизации
7 n =B3*B4/B5
8
2) Найти оптимальное решение, для этого:
• Выделить целевую ячейку В7;
• Нажать кнопку MS Office – кнопка Параметры Excel – Надстройки – Поиск решения (находится во вкладке Данные)
• Установить целевую ячейку, равную минимальному значению.
• Указать мышью диапазон изменяемых ячеек.
• Выбрать кнопку Добавить для записи ограничений.
• После записи ограничения нажать Добавить (для последнего ограничения – ОК).
• Нажать кнопку Выполнить.
• Выбрать Тип отчета, Результаты и нажать ОК.
• На новом листе Отчет по результатам1 можно увидеть:
В электронных таблицах найдено оптимальное решение: для покраски пола в актовом зале необходимо не более 14 банок краски.
Задача 2
На научный семинар собрались ученые и обменялись визитными карточками. Число визитных карточек составило 210 штук. Сколько ученых приехало на семинар, если их было не более 20?
Решение:
х – количество ученых
n – количество карточек
Подумайте, определите, составьте:
- Математическая модель
- Поисковые переменные
- Ограничения
- Критерий оптимизации
Найдите поиск решения в Еxcel, создайте отчет и сохраните документ под именем семинар.xls.
Задача 3
Какие размеры должен иметь бак объемом V = abh = 2000 куб.см, чтобы на его изготовление пошло как можно меньше материала? Сторона а должна быть не менее 10 см.
Выполните поиск решения, заполнив таблицу.
Задача 4
На участке работает 20 человек; каждый из них в среднем работает 1800 часов в год. Выделенные ресурсы: 32 т металла, 54 тыс кВт.ч электроэнергии. План реализации: не менее 2 тыс. изделий А и не менее 3 тыс. изделий Б. На выпуск 1 тыс. изделий А затрачивается 3 т металла, 3 тыс. кВт.ч электроэнергии и 3 тыс. ч рабочего времени. На выпуск 1 тыс. изделий Б затрачивается 1 т металла, 6 тыс. кВт.ч электроэнергии и 3 тыс. ч рабочего времени.
От реализации 1 тыс. изделий А завод получает прибыль 500 тыс.р., от реализации 1 тыс. изделий Б – 700 тыс.р.
Выпуск какого количества изделий А и Б (в тыс. штук) надо запланировать, чтобы прибыль от их реализации была наибольшей? Составьте модель и решите задачу.
Выполните поиск решения, заполнив таблицу. Создайте отчет и сохраните документ под именем работа5.xls.
Задача 5
Кооператив из 20 человек выпускает изделия А и Б (смотри задание 5). Кооператив намерен получать прибыль не менее 6,5 млн. руб. в год. Ему выделили 54 тыс. кВт.ч электроэнергии. Какое минимальное количество металла потребуется кооперативу, чтобы обеспечить нужную прибыль?
Составьте модель и решите задачу. Создайте отчет и сохраните документ под именем работа6.xls.
Задача 6
Начальник участка изучает возможность расширить ассортимент товаров – добавить к выпускаемым изделиям А и Б еще два вида изделий В и Г. Предварительное изучение спроса показало, что можно реализовать не более 5 тыс. изделий В, получив при этом прибыль в размере 1200 руб. с каждого изделия. Можно также реализовать не более 4 тыс. изделий Г, получив прибыль 1000 руб. с изделия. На 1 тыс. изделий В расход металла составляет 0,5 тонн, электроэнергии 4 тыс. кВт.ч, рабочего времени 5 тыс.час. Для выпуска 1 тыс. изделий Г требуется 1,5 т металла, 4 тыс. кВт.ч электроэнергии, 6 тыс. ч рабочего времени. Расширение ассортимента изделий потребует приобретение дополнительного оборудования на сумму 800 тыс. рублей, которая будет возмещена из прибыли. Целесообразно ли расширение ассортимента выпускаемых товаров (можно ли спланировать выпуск товаров А, Б, В, Г так, чтобы получить прибыль большую, чем при выпуске только товаров А и Б)?
Выполните поиск решения, создайте отчет и сохраните документ под именем работа7.xls.
Задача 7
Заведующий хозрасчетной больницей должен составить штатное расписание, т.е. определить, сколько сотрудников, на какие должности и с каким окладом он должен принять на работу. Общий месячный фонд зарплаты составляет 10 000 у.е. Известно, что для нормальной работы больницы нужно 5-7 санитарок, 8-10 медсестер, 10-12 врачей, 1 зав. аптекой, 1 зав. отделением, 1 главный врач, 1 завхоз, 1 зав. больницей.
За основу берется оклад санитарки, а все остальные вычисляются по формуле:
АВ + С, где С – оклад санитарки, А и В – коэффициенты, которые для каждой должности определяются решением совета трудового коллектива.
Допустим, совет решил, что:
- медсестра должна получать в 1,5 раза больше санитарки (А = 1,5; В = 0);
- врач – в 3 раза больше санитарки;
- зав.отделением – на 30 у.е. больше, чем врач;
- зав.аптекой – в 2 раза больше санитарки;
- завхоз – на 40 у.е. больше медсестры;
- главный врач – в 4 раза больше санитарки;
- зав.больницей – на 20 у.е. больше главного врача.
Составьте модель и решите задачу.
1) Заполните таблицу, установив зарплату санитарки 150 у.е. Расположите таблицу на листе Расписание.
2) Составьте штатное расписание с использованием функции автоматизации расчетов Подбор параметра (Меню – Данные – (блок Работа с данными) – Анализ «Что-если»
3) Составьте несколько вариантов штатного расписания, изменяя количество сотрудников на должностях санитарки, медсестры, врача. Подберите зарплату санитарки в новых условиях. Расположите таблицу на листе Варианты.
4) Удалите остальные листы.
5) Сохраните документ под именем госпиталь.xls.