 |
Этап. Составление математической модели
|
|
|
|
Пусть x – количество изделий модели A, выпускаемой в течение недели, y – количество изделий модели B. Тогда прибыль от этих изделий равна 2x+4y долл. Эту прибыль нужно максимизировать.
Функция, для которой ищется экстремум (максимум или минимум), носит название целевой функции.
Беспредельному увеличению количества изделий препятствуют ограничения.
Следовательно, 2x+4y – это целевая функция рассматриваемой задачи. Ограничения задачи:
3x+4y£1700 (ограничения количества материала для полок);
0,2x+0,5y£160 (ограничения на машинное время изготовления полок);
x³0, y³0 (т.к. количество изделий число неотрицательное).
Таким образом, задача оптимизации для рассматриваемого примера записывается так:
Этап. Решение задачи средствами табличного процессора Microsoft Excel
1. Запустим табличный процессор Microsoft Excel. Для этого выполним последовательность команд Пуск Þ Программы Þ Microsoft Excel.
2. Введем в ячейки столбца A данные, как указано на рис. 1
рис. 1
3. В столбец B введем формулы для расчета целевой функции и ограничений как указано на рис. 2.
рис.2
4. Воспользуемся средством Поиск решения.
Для его активизации выполните последовательность команд: на панели нажать кнопку Настройка панели быстрого доступа 
. В меню выбрать пункт Другие команды…
В диалоговом окне выбрать вкладку Надстройки 
В нижней части окна нажмите кнопку Перейти… 
В открывшемся окне активизировать опции Пакет анализа и Поиск решени. Нажать Ок. Данные опции появятся в конце панели Данные 
Для его запуска: в конце панели Данные – Поиск решения
В диалоговом окне:
· В поле Установить целевую ячейку – укажем адрес ячейки, значение которой должно быть изменено (максимизировано, минимизировано или приравнено к какому-либо определенному указанному значению). В нашем случае это адрес ячейки Прибыль (т.е. B6).
|
|
|
· В поле Равной: - установим переключатель на максимальное значение (т.к. по условию задачи мы максимизируем прибыль).
· В поле Изменяя ячейки – установим ссылку на ячейки, которые будут изменены. В нашем случае это диапазон ячеек $B$2:$B$3.
· В поле Ограничения – щелкнем на кнопке Добавить. В появившемся диалоговом окне:
ü В поле Ссылка на ячейку – укажем адрес ячейки, для которой должно действовать ограничения. В нашем случае это адрес ячейки Материал (т.е. В9).
ü В поле операторов выберем знак соотношения. В данном случае знак <=.
ü В поле Ограничение - укажем границу ограничения. В случае материала это число 1700.
ü Щелкнем OK
· Аналогичным образом, добавим ограничения на время изготовления и ограничения на x и y.
· Таким образом, диалоговое окно Поиск решения должно быть настроено следующим образом:
· Щелкнем по кнопке Параметры и установим флажок Линейная модель. Щелкнем по OK.
· Щелкнем по кнопке Выполнить.
5. Прочтем сообщение в окне Результаты поиска решения. Щелкнем по OK. Результаты решения видны в ячейках таблицы.
6. Сохраните результат в файле под именем оптимизация.xls
Пример решения задачи из раздела III «Регрессия»
Задача. Ниже приводиться урожайность зерновых (y) в СССP в 1922-1934 гг. (x).
| x | |||||||||||||
| y, ц/га | 7,6 | 7,2 | 6,2 | 8,3 | 8,2 | 7,6 | 7,9 | 7,5 | 8,5 | 8,7 | 7,0 | 8,8 | 8,5 |
Требуется найти коэффициенты a и b зависимости y от x вдоль прямой f(x) = ax+b, пользуясь методом наименьших квадратов, для того чтобы значения f наилучшим образом приближали значения y. Построить диаграмму с исходными данными и приближающим их линейным графиком.
Ход решения
| 1. Запустим табличный процессор Microsoft Excel. Для этого выполним последовательность команд Пуск Þ Программы Þ Microsoft Excel. | ||
| 2. Введем в ячейки данные задачи как указано на рис. 1. 3. В столбце C рассчитаем значение f(x) = ax+b. Для этого в ячейку C2 введем формулу: = $B$16*A2+$B$17 Введем формулу методом автозаполнения по столбцу. 4. В столбце D рассчитаем остатки. Для этого в ячейку D2 введем формулу: = B2-C2 Введем формулу методом автозаполнения по столбцу. 5. В ячейке D16 подсчитаем сумму квадратов остатков. Для этого в ячейку D16 введем формулу: = СУММКВ(D2:D14) |  рис. 1
| |
| 6. Далее решим задачу оптимизации. Целевая функция – ячейка D16, которую следует минимизировать путем изменений a и b без ограничений. Решение задачи оптимизации см. на рис. 2. Целевая ячейка D16 – стремится к минимуму. Изменяемые ячейки В16 и В17 (коэффициенты а и b). Ограничений нет. Функция прямой будет иметь вид: f(x)=ax+b=0,1x-185,207 | 
рис. 2
| |
7. Построим диаграмму с исходными данными и приближающим их линейным графиком f(x) = ax+b. Для этого выполним нижеуказанную последовательность шагов.
|
|
|
Нажмем кнопку Мастер диаграмм 
.
Шаг 1. Выбор типа и вида диаграммы.
ü Во вкладке Стандартные выберем Тип – Точечная, вид – Точечная диаграмма со значениями, соединенными сглаживающими линиями без маркеров.
ü Прочтем описание выбранного вида диаграммы.
ü Нажмем кнопку Далее>.
Шаг 2. Источник данных диаграммы.
ü Во вкладке Диапазон данных активизируем опцию Ряды в столбцах.
ü Переключимся на вкладку Ряд.
ü Щелкнем по кнопке Добавить.
ü В поле Имя введем текст - Реальные данные.
ü В поле Значения X нажмем кнопку 
для возврата в таблицу и выделим там диапазон с годами, т.е. диапазон A2:A14. Нажмем кнопку 
для возврата в диалоговое окно Мастер диаграмм.
ü В поле Значения Y нажмем кнопку для возврата в таблицу и выделим там диапазон B2:B14. Нажмем кнопку для возврата в диалоговое окно Мастер диаграмм.
ü Щелкнем по кнопке Добавить для добавления второго ряда данных.
ü В поле Имя введем текст - Расчетные данные.
ü В поле Значения X укажем диапазон A2:A14.
ü В поле Значения Y диапазон С2:С14.
ü Нажмем кнопку Далее>.
Шаг 3. Параметры диаграммы.
| ü Введем с клавиатуры Название диаграммы – Урожайность зерновых в СССР в 1922-1934 гг. ü Введем с клавиатуры Ось X (категорий) – год ü Введите с клавиатуры Ось Y (значений) – ц/га ü Переключимся на вкладку Линии сетки и активизируем опции, как указано на рис. 3. Переключимся на вкладку Легенда и активизируем опции, как указано на рис. 4. ü Нажмем кнопку Далее>. Шаг 4. Размещение диаграммы. ü Выберем размещение диаграммы на отдельном листе и укажем имя листа Диаграмма. ü Щелкнем по кнопке Готово. Результат см. на рис. 5. | 
рис. 3
рис. 4
|
|
|
|
рис. 5
10. Сохраните результат в файле под именем регрессия.xls.
|
|
|
