Порядок решения задачи в программе MSExcel

Система исходных данных

Таблица 1.1 - Технико-экономические коэффициенты

Таблица 1.2 - Коэффициенты функции цели

Характеристика размеров и структуры отраслей организации. Хозяйство имеет 2500 га пашни, 500 га сенокосов и 200 га пастбищ. При необходимости не более 200 га сенокосов может быть трансформировано в пашню

В хозяйстве должны соблюдаться следующие требования к севооборотам: 1) площадь посевов зерновых культур не должна превышать 80% от площади всей имеющейся в наличии пашни; 2) площадь пашни под картофелем должна быть в два раза больше, чем под морковью и турнепсом.

Хозяйство постоянно располагает трудовыми ресурсами в размере 200 000 чел.-час.

Хозяйство имеет обязательства произвести и продать не менее 3000 ц и не более 5000 ц молока, не менее 400 ц мяса, при этом объем производства говядины должен составлять не менее 60% от объема производства всего мяса.

Имеется требование к структуре стада КРС – поголовье коров в структуре стада должно быть не менее 30%.

Формирование матрицы экономико-математической модели.

Таблица 2.1 - Матрица задачи оптимизации производственно-отраслевой структуры сельскохозяйственной организации

Порядок решения задачи в программе MSExcel

Алгоритм решения задачи оптимизации экономико-математической модели в программе MSExcel:

1. Открыть окно программы MSExcel.

2. Ввести исходную матрицу по форме, представленной в таблице 3.1 (Приложение 1):

1) в ячейки строки «Наименование переменных» ввести наименования используемых переменных;

2) в ячейки строки «Значения переменных» ввести нули;

3) выделить блок ячеек (массив), содержащих значения переменных. Выполнить команду Формулы -> Присвоить имя. В поле «Имя» ввести текст «Переменные». -> ОК

4) в ячейки столбца «Наименование и тип ограничения» ввести наименования используемых в модели ограничений, предварительно разделенных на группы по типу (<=, >=, =);

5) в ячейки столбца «№ п.п.» ввести порядковые номера ограничений;

6) в ячейки столбцов «Технико-экономические коэффициенты» по каждому ограничению ввести коэффициенты при переменных;

7) в ячейках столбца «Потребности» записать формулу =СУММПРОИЗВ(Переменные; «Массив ячеек, содержащих коэффициенты при переменных в соответствующем ограничении» ).

Примечание: а) перемножаемый массив ячеек должен иметь одинаковую размерность, равную числу переменных; б) достаточно ввести формулу один раз в одну из ячеек столбца «Потребности», после чего ее следует скопировать в остальные ячейки указанного столбца методом автозаполнения; в) после ввода формул во всех ячейках столбца «Потребности» должны стоять нули;

8) в ячейках столбца «Знак ограничения» ввести знаки ограничений;

9) в ячейках столбца «Размер ограничения» ввести размеры ограничений;

10) в столбце «Потребности» блоку ячеек с ограничением типа «<=» присвоить имя «Потребности1», с ограничением типа «>=» - имя «Потребности2», типа «=» - «Потребности3». В столбце «Размер ограничения» соответствующим блокам ячеек присвоить имена «Ограничения1», «Ограничения2» и «Ограничения3» (см. подпункт 3 алгоритма);

11) в ячейку «Значение F(x)» ввести формулу =СУММПРОИЗВ(Переменные; «Массив ячеек, содержащих коэффициенты функции цели» ).

Примечание: после ввода формулы значение функции цели должно быть равно нулю.

1. Полученную матрицу вывести на принтере.
2. Выполнить команду Данные -> Поиск решения.
3. В окне «Поиск решения» в поле ввода «Оптимизировать целевую функцию» ввести имя ячейки, содержащей значение целевой функции. Из группы переключателей «До» выбрать, соответствующий функции цели задачи: минимизация, максимизация, достижение какого-либо конкретного значения. В поле ввода «Изменяя ячейки переменных» ввести текст Переменные (либо выбрать блок ячеек, содержащих значения переменных). В поле ввода «В соответствии с ограничениями», используя кнопку «Добавить», ввести неравенства, отвечающие заданным ограничениям, следующие: «Потребности1<=Ограничения1», «Потребности2>=Ограничения2», «Потребности3=Ограничения3», «Переменные>=0».

1. Решить задачу оптимизации ЭММ: в окне «Поиск решения» выбрать метод решения «Поиск решения линейных задач симплекс-методом»; запустить решение задачи кнопкой «Найти решение ».

Если в окне «Результаты поиска решения» сказано, что подходящее решение не найдено, необходимо сделать следующее: а) не закрывая указанное окно, проанализировать соответствие полученных потребностей заданным знакам и размерам ограничений; б) выйти из окна «Результаты поиска решения», нажав командную кнопку «Отмена»; в) произвести необходимые корректировки ЭММ и матрицы; г)предпринять дальнейшие попытки решения.

Если в окне «Результаты поиска решения» сказано, что решение найдено, то следует в поле выбора «Отчеты» выделить все три типа отчета: «Результаты», «Устойчивость» и «Пределы», а затем нажать кнопку «Ок». В книге MSExcel появятся дополнительные листы: «Отчет по результатам », «Отчет по пределам» и «Отчет по устойчивости ».

1. Установить числовой формат данных для ячеек содержащих расчетные значения переменных (для целей преодоления экспоненциального представления отдельных значений), выполнив команду Формат – Ячейки. Во вкладке «Число» в списке «Числовые форматы» выбрать формат «Числовой» и установить число десятичных знаков - 0. То же необходимо сделать в отношении данных столбца «Потребности». Функцию цели округлить до двух десятичных знаков.
2. Вывести на принтер результаты решения – матрицу с полученным решением (Таблица 6.7.2, Приложение 3) и три отчета (Приложение 4).

Примечание: при необходимости результаты решения и данные отчетов следует отформатировать (изменить ширину столбцов и высоту строк, количество десятичных знаков в значениях и т. д.).

Анализ прямого решения

Для анализа прямого решения задачи оптимизации производственно-отраслевой структуры сельскохозяйственной организации необходимо заполнить и проанализировать следующие таблицы.

Таблица 4.1 - Значения переменных по факту и по решению

Содержание анализа: 1) какие переменные вошли в оптимальный план; 2) как отличается оптимальный план от фактического.

Таблица 4.2 - Значения ограничений по факту (размер ограничения) и по

решению (потребности)

Содержание анализа: как отличаются полученные в результате решения потребности (возможности) от заданных размеров ограничений.

Таблица 4.3 - «Критерий оптимальности» по факту и по решению

Содержание анализа: как изменилось значение функции цели по каждой переменной и в целом.