 |
Только после этого экран будет соответствовать таблице 1 и примет следующий вид.
|
|
|
|
Методика решения задач
Информационные технологии управления
МЕТОДИКА РЕШЕНИЯ ЗАДАЧ
СОДЕРЖАНИЕ
1. Линейная оптимизация......................................................................... 3
2. Регрессия. Метод наименьших квадратов........................................ 10
3.Линейная регрессия.............................................................................. 15
4. Транспортная задача.......................................................................... 18
5. Анализ и сравнение инвестиционных проектов.............................. 29
Литература............................................................................................... 36
Контрольные задания............................................................................. 37
Линейная оптимизация
Планирование деятельности фирмы
ЗАДАЧА 1. Фирма производит две модели А и В сборных книжных полок. Их производство ограничено наличием сырья (досок) и временем машинной обработки. Для каждого изделия модели А требуется 3 кв. м досок, а для изделия модели В – 4 кв. м. Фирма может получать от поставщиков до 1700 кв. м досок в неделю. Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В – 30 мин. В неделю можно использовать 160 ч машинного времени. Сколько изделий каждой модели следует выпускать фирме в неделю, если изделие модели А приносит 2 доллара прибыли, а изделие модели В приносит 4 доллара прибыли?
Решение. Составим математическую модель. Введем обозначения: x - количество изделий модели А, а y - количество изделий модели В, выпускаемых в течение недели. Прибыль от реализации этих изделий равна 2x + 4y долларов. Эту прибыль нужно максимизировать. Функция, для которой ищется экстремум (максимум или минимум), называется целевой функцией.
Беспредельному увеличению количества изделий, а значит, и росту целевой функции препятствуют ограничения. Ограничено количество материала для производства полок. Отсюда следует ограничение 3x + 4y 1700.
|
|
|
На производство изделия А требуется 0.2 часа, а на производство изделия В требуется 0.5 часа. Время производства ограничено 160 часами, поэтому имеет место неравенство 0.2x + 0.5y 160.
Кроме того, количества изделий – неотрицательные числа, поэтому x 0, y 0.
Задача оптимизации записывается следующим образом.
2x + 4y max,
3x + 4y 1700,
0.2x + 0.5y 160,
X 0, y 0.
Решим сформулированную задачу линейного программирования в Excel. Создайте новую рабочую книгу, сохраните ее под именем Задача1. Дайте первому листу имя Полки.
Введите в ячейки рабочего листа Excel данные задачи (Таблица 1). Не вводите сразу нули в ячейки столбца В, о них речь пойдет ниже. Во избежание фатальных ошибок делайте все в соответствии с приведенным далее описанием. В ячейки А1, А2, А3, А5, А6, А8, А9 введите текст Переменные, Изделие А и т.д. Этот текст не нужен для поиска решения, но его наличие придает наглядность и удобство для автора и пользователя решения задачи. Текстом являются и формулы и числа, введенные в ячейки столбцов С и D. Поэтому эти текстовые формулы следует ввести, не ставя знака равенства = в первую позицию строки формул, и можно не использовать знака умножения *.
Таблица 1
| A | B | C | D | |
| 1. | Переменные | |||
| 1. | Изделие А | x | ||
| 1. | Изделие В | y | ||
| 1. | ||||
| 1. | Целевая функция | |||
| 1. | Прибыль | 2x+4y | ||
| 1. | ||||
| 1. | Ограничения | |||
| 1. | Материал | 3x+4y | <=1700 | |
| 1. | Время изготовления | 0.2x+0.5y | <=160 |
Ячейкам В2 и В3 присвойте имена x и y. Для этого щелкните на ячейке В2, затем на поле Имя строки ввода и редактирования и вместо В2 введите латинскую букву х и обязательно нажмите клавишу Enter. Затем щелкните на ячейке В3 и на поле Имя строки ввода и редактирования и вместо В3 введите латинскую букву у и обязательно нажмите клавишу Enter. После этого введите начальные значения переменным х и у. Для этого снова выделите ячейку В2 (или х) и введите в строку формул =0, нажмите клавишу Enter. Аналогичные операции проделайте для ячейки В3. Операции присвоения имен переменным и задания их начальных значений (заполнить поле имени и строку формул) для каждой из ячеек можно было произвести за один прием.
|
|
|
Введите, наконец, настоящие формулы, соблюдая синтаксис программы Excel. Сначала в ячейку В6 введите формулу целевой функции (прибыли). Для этого выделите эту ячейку, введите в нее (или в строку формул) следующие символы: =2*х+4*у и нажмите клавишу Enter. Аналогично в ячейки В9 и В10 введите формулы =3*х+4*у и =0.2*х+0.5*у.
Только после этого, если все сделано правильно, в ячейках В6, В9 и В10 должны появиться нули, то есть значения целевой функции и выражений, задающих ограничения задачи линейного программирования, для начальных значений переменных х=0 и у=0. При появлении в ячейке набора символов типа #ССЫЛКА! или сообщения об ошибке проверьте правильность всей введенной информации во всех ячейках столбца В.
Только после этого экран будет соответствовать таблице 1 и примет следующий вид.
Перейдем к непосредственному решению задачи. Выделите ячейку В6, в которой вычисляется целевая функция и вызовем Решатель (меню Сервис – Поиск решения). В появившемся диалоговом окне Поиск решения (см. ниже) в поле ввода Установить целевую ячейку уже должен содержаться адрес $B$6 ячейки, содержащей целевую функцию. Если это по каким-либо причинам не так, введите в это поле набор символов $B$6. Установите переключатель Равной максимальному значению.
Перейдем к полю ввода Изменяя ячейки. В данном случае достаточно щелкнуть кнопку Предположить и в поле ввода Изменяя ячейки появится адрес блока ячеек $B$2:$B$3. При необходимости введите этот адрес вручную.
Перейдем к вводу ограничений. Щелкните кнопку Добавить. Появится диалоговое окно Добавление ограничения (см. ниже). В поле ввода Ссылка на ячейку укажите адрес $B$9 первого ограничения задачи. Правее расположен список с условными операторами (откройте его и посмотрите). Выберите оператор <=. В поле ввода Ограничение введите число 1700.
|
|
|
Не выходя из диалогового окна Добавление ограничения, нажмите кнопку Добавить и аналогично введите второе ограничение $B$10<=160. Ввод ограничений закончен. Нажмите кнопку ОК. Вы вновь окажетесь в диалоговом окне Поиск решения и увидите введенные ограничения.
Справа имеются кнопки Изменить и Удалить. С их помощью можно изменить или удалить ограничение. Если Вы используете Excel 5.0/7.0, то должны ввести еще ограничение неотрицательности переменных $B$2: $B$3>=0.
Нажмите кнопку Параметры. Вы окажетесь в диалоговом окне Параметры поиска решения.
Чтобы узнать назначение полей ввода этого окна, нажмите кнопку Справка. Менять ничего не стоит, вполне разумные параметры установлены по умолчанию. Например, неоправданное увеличение точности или числа итераций может привести к значительному росту времени решения. Поэтому установим только два флажка: Линейная модель (так как наши ограничения и целевая функция являются линейными по переменным х и у) и Неотрицательные значения (для переменных х и у). В Excel 5.0/7.0 этот последний флажок отсутствует, поэтому и нужно было вводить ограничение неотрицательности переменных.
Щелкните кнопку ОК и Вы окажетесь в исходном окне. Теперь задача полностью готова для работы встроенного в Excel алгоритма решения. Нажмите кнопку Выполнить. Появляется диалоговое окно Результаты поиска решения. В нем указано сообщение. Решение найдено. Все ограничения и условия оптимальности выполнены. На выбор предлагаются варианты Сохранить найденное решение или Восстановить исходные значения. Если у Вас не появилось желание изменить условия задачи повторить весь процесс или часть его сначала, выберите первое.
|
|
|
