Материал: Математические методы и модели в экономике. лабораторный практикум. Амелин С.В
bi - количество располагаемого ресурса i-го вида; aij - норма расхода i-го ресурса для выпуска единицы продукции j-го типа; сj - прибыль, получаемая от реализации единицы продукции j-го типа, m – количество видов ресурсов; n - количество видов продукции.
Подставим исходные данные в модель:
F = 15 Х1 + 24 Х2 + 19 Х3 + 27 Х4 → MAX
2 X1 + 2 X2 + 2 X3 + 2 X4 ≤ 30
12 X1 + 8 X2 + 10 X3 + 6 X4 |
≤ 200 |
8 X1 + 20 X2 + 12 X3 + 26 X4 |
≤ 125 |
Xj ≥ 0; j= 1, 4 .
Ввод условий задачи в электронную таблицу состоит из следующих основных шагов:
1.Создание формы для ввода условий задачи;
2.Ввод исходных данных;
3.Ввод зависимостей из математической модели;
4.Назначение целевой функции;
5.Ввод ограничений и граничных условий.
Форма для ввода условий может иметь следующий вид (рис. 4.1). После подготовки формы таблицы необходимо ввести исходные параметры (коэффициенты функции цели и ограничений и соответствующие зависимости) экономико-математической модели (рис. 4.2)
Для ввода зависимости (формулы) для целевой функции (ЦФ) необходимо выполнить следующее:
выделить ячейку, в которую будет вводиться формула; с помощью мыши нажать кнопку Вставка функции [fx];
вдиалоговом окне вызвать категорию Математические функции;
выбрать в окне Функции СУММПРОИЗВ; нажать [ОК или Далее], (появляется диалоговое окно);
вмассив 1 ввести адреса ячеек, содержащих значения переменных (или с клавиатуры или протаскивая мышь по ячейкам);
вмассив 2 ввести адреса коэффициентов функции цели; нажать [ОК или Готово].
50
|
|
A |
B |
C |
D |
E |
F |
G |
H |
|
1 |
|
|
|
Переменные |
|
|
|
|
|
2 |
Имя |
Прод 1 |
Прод 2 |
Прод 3 |
Прод 4 |
|
|
|
|
3 |
Значение |
|
|
|
|
|
|
|
|
4 |
|
|
|
|
|
ЦФ |
Направл. |
|
51 |
5 |
Коэф. в ЦФ |
|
|
|
|
|
|
|
6 |
|
|
|
|
|
|
|
|
|
|
7 |
|
|
|
Ограничения |
|
|
|
|
|
8 |
Вид |
|
|
|
|
Лев.часть |
знак |
Прав.часть |
|
9 |
Труд |
|
|
|
|
|
|
|
|
10 |
Оборуд. |
|
|
|
|
|
|
|
|
11 |
Сырье |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Рис. 4.1. Форма для ввода исходных данных для математической модели задачи
51
|
|
A |
B |
C |
D |
E |
F |
G |
H |
|
1 |
|
|
|
Переменные |
|
|
|
|
|
2 |
Имя |
Прод 1 |
Прод 2 |
Прод 3 |
Прод 4 |
|
|
|
|
3 |
Значение |
|
|
|
|
|
|
|
|
4 |
|
|
|
|
|
ЦФ |
Направл. |
|
52 |
5 |
Коэф. в |
15 |
24 |
19 |
27 |
= СУММПРОИЗВ |
MAX |
|
|
ЦФ |
|
|
|
|
($B$3:$E$3;B5:E5) |
|
|
|
|
|
|
|
|
|
|
|
||
|
6 |
|
|
|
|
|
|
|
|
|
7 |
|
|
|
Ограничения |
|
|
|
|
|
8 |
Вид |
|
|
|
|
Лев.часть |
знак |
Прав.часть |
|
9 |
Труд |
2 |
2 |
2 |
2 |
= СУММПРОИЗВ |
<= |
30 |
|
|
|
|
|
|
|
($B$3:$E$3;B9:E9) |
|
|
|
10 |
Оборуд. |
12 |
8 |
10 |
6 |
= СУММПРОИЗВ |
<= |
200 |
|
|
|
|
|
|
|
($B$3:$E$3;B10:E10) |
|
|
|
11 |
Сырье |
8 |
20 |
12 |
26 |
= СУММПРОИЗВ |
<= |
125 |
|
|
|
|
|
|
|
($B$3:$E$3;B11:E11) |
|
|
Рис. 4.2. Ввод зависимостей из математической модели в электронную таблицу
52
Поскольку формулы левых частей ограничений имеют то же строение, что и функция цели (меняются только коэффициенты), то их ввод можно осуществить с помощью копирования. При копировании относительные ссылки на адрес ячейки (например, А1) изменятся в зависимости от количества пройденных ячеек по вертикали или горизонтали.
Ссылка на ячейку в виде абсолютного адреса не изменится при копировании содержащей ее формулы. Абсолютный адрес ячейки имеет знак $ перед буквой столбца и номером строки (например, $A$1). В смешанном адресе ячейки только одна из его компонент абсолютна, а другая относительна (например, $A1 или A$1). Нажатием на клавиатуре кнопки F4 тип ссылки на ячейку можно изменить с относительного на абсолютный, смешанный и снова на относительный.
Рис. 4.3. Ввод функции цели (или ограничений)
Копировать формулы можно несколькими способами: выделить ячейку - источник для копирования; нажать [Копировать (в буфер)] - кнопку в виде двух лис-
тов; выделить ячейку в которую будем копировать;
нажать [Вставить (из буфера)] - кнопка в виде папки и листа;
пунктир в источнике убирается клавишей [Esc].
53
Такого же результата можно добиться используя команды из меню Правка. Другим способом является перетаскивание формулы с помощью мыши:
выделить объект копирования и подвести курсор к границе объекта;
нажать [Ctrl] и удерживая переместить копию объекта на новое место;
отпустить кнопку мыши и [Ctrl].
Если область для копирования расположена вплотную к источнику, тогда копирование осуществляется протаскиванием мыши:
выделить ячейку - источник копирования; курсор на квадратик в правом нижнем углу выделенной
ячейки; переместить курсор в виде перекрестия в ячейку(ки) куда
будем копировать и отпустить кнопку мыши.
Для ввода направления поиска оптимального значения целевой функции (ЦФ) и граничных условий вызвать в меню Сервис\Поиск решения и далее в диалоговом окне:
вполе Установить целевую ячейку ввести адрес ячейки,
содержащей формулу ЦФ; выбрать направление поиска оптимального значения ЦФ
(установив точку в круге у max, min или = значению);
вполе Изменяя ячейки ввести адреса ячеек, содержащих значения искомых переменных (до 200 ячеек);
для ввода ограничений нажать кнопку [Добавить];
вдиалоговом окне Добавление ограничения в левом поле Ссылка на ячейку ввести адрес ячейки, содержащей переменную или формулу левой части ограничений, выбрать знак (<=, >=, =), в правом поле Ограничение ввести адрес ячейки, содержащей значение правой части ограничения (таким образом вводятся граничные условия для переменных и условия ограничений); ограничения можно вводить используя ссылки на диапазоны ячеек содержащих формулы левой части и числовые значения правой части ограничений;
после ввода последнего ограничения нажать [ОК].
54