Решение задач экономико-математического моделирования с помощью программы Excel

Решение задач экономико-математического моделирования с помощью программы Excel

Решение задач экономико-математического моделирования с помощью программы Excel

Определить оптимальное сочетание трех зерновых культур: пшеницы, ячменя и овса

Производство культур характеризуется показатели таблицы.

Производственные ресурсы: пашня-1600 га, труд - 27000 чел.-ч., удобрения - 99000 руб.

В структуре посевов площадь под оз. пшеницы должна составлять не менее 50 %. Критерий оптимальности максимальное производство зерна.

Решение.

Обозначим через:

х1-площадь оз. пшеницы, га.

х2-площадь яр. ячмень, га.

х3-площадь овса, га.

Запишем условие задач в виде системы ограничений (уравнений и неравенств).

)        По уборки площади посевов трех зерновых, га

Х1+х2+х3=1600

)        По использовании ресурсов труда

*х1+15*х2+13*х3≤27000

)        По затратам удобрений, руб.

*х1+50*х2+40*х3≤99000

)        По структуре посевной площади

х1≥0,5(х1+х2+х3)

х1≥0,5*х1+0,5*х2+0,5*х3

,5х1-0,5х2-0,5х3≥0

ЦФ= критерий max производства зерна.

ЦФ=40*х1+35*х2+30*х3→max

Решение задачи в Excel

В ячейку F4 вставим формулу « =СУММПРОИЗВ(С4:Е4;$C$9:$E$9)», затем растянем форму до целевой ячейки включительно.

Затем находим решение задачи с помощью «поиска решения». Находим: Данные→Анализ→Поиск решения. Как видно на рисунке: устанавливаем целевую ячейку; равный- max значению; изменяя ячейки и ограничение.

программирование excel оптимальный модель

После выполненных операций нажимаем, на «выполнить» и получаем возможное решение задачи:

Вывод: Оптимальное сочетание трех зерновых культур, максимальное производства зерна будет при площади уборки: оз. пшеницы - 800 га, яр. ячмень - 300 га, овес - 500 га.

Экономико-математические модели для расчета оптимального распределения минеральных удобрений

Рассчитать план распределения минеральных удобрений, при котором достигается максимум стоимости прибавки урожая. При этом по плану необходимо получить не менее 23 000 ц продовольственного и 17 000 ц фуражного зерна. Площадь посева культур, рекомендуемые дозы внесения удобрений и прибавка урожая показаны в табл.7.1. Сведения о количестве, качестве и стоимости минеральных удобрений приведены в табл.7.2.

Таблица 7.1

Таблица 7.2

Решение:

ЭММ для расчета оптимальных рационов кормления с/х животных

Рассчитать оптимальный суточный рацион кормления нетелей средней живой массой 480 кг. В рационе должно содержаться не менее 8,8 кг кормовых единиц, 980 г переваримого протеина, 36 г кальция, 32 г фосфора, 103 мг каротина и не более 19,6 кг сухого вещества.

Рацион составляется из комбикорма, сена лугового, сена клеверотимофеечного, соломы овсяной, силоса кукурузного и кормовой свеклы. Содержание питательных веществ в кормах и их себестоимость представлены в таблице 9.5.

В соответствии с зоотехническими требованиями отдельные группы кормов в рационе могут изменяться в следующих пределах, % к общему количеству кормовых единиц: концентрированные - от 9 до 20, грубые - от 13 до 20, сочные - от 20 до 50 и корнеклубнеплоды - от 2 до 12. Удельный вес соломы в грубых кормах должен составлять не менее 50%.

Таблица 9.5

Решение:

Определим перечень переменных. Количество кормов, которая может войти в рацион нетелей, обозначим через:

Х1-количество комбикорма в рационе, кг

Х2-количество сена лугового в рационе, кг

Х3-количество сена клеверотимофеечного в рационе, кг

Х4-количество соломы овсяной в рационе, кг

Х5-количество силоса кукурузного в рационе, кг

Х6-количество кормовой свеклы в рационе, кг

Х7-общее количество кормовых единиц в рационе, кг

Запишем систему ограничений в развернутом виде.

. Ограничения по балансу питательных веществ в рационе:

) общее количество кормовых единиц

,9х1+0,42х2+0,5х3+0,31х4+0,2х5+0,12х6=х7

или после преобразования

,9х1+0,42х2+0,5х3+0,31х4+0,2х5+0,12х6-х7=0

) кормовые единицы не менее

Х7 ≥ 8,8

) переваримого протеина не менее

х1+48х2+52х3+14х4+14х5+9х6 ≥ 980

) кальция не менее

х1+6х2+7,4х3+4,3х4+1,5х5+0,4х6 ≥ 36

) фосфора не менее

) каротина не менее

х2+30х3+4х4+15х5 ≥ 103

. Ограничения по содержанию сухого вещества в рационе:

) 0,87х1+0,85х2+0,83х3+0,85х4+0,26х5+0,13х6 ≤ 19,6

. Ограничения по содержанию отдельных групп кормов в рационе:

) концентрированных не менее

,9х1 ≥ 0,09х7

или после преобразований

,9х1-0,2х7 ≥ 0

) концентрированных не более

,9х1 ≤ 0,2х7

или после преобразований

,9х1-0,2х7 ≤ 0

) грубых не менее

,42х2+0,5х3+0,31х4 ≥ 0,13х7

или после преобразований

,42х2+0,5х3+0,31х4-0,13х7 ≥ 0

) грубых не более

,42х2+0,5х3+0,31х4 ≤ 0,2х7

или после преобразований

,42х2+0,5х3+0,31х4-0,2х7 ≤ 0

) сочных не менее

,2х5≥0,2х7

или 0,2х5-0,2х7≥0

) сочных не более

,2х5≤0,5х7

или 0,2х5-0,5х7≤0

) корнеклубнеплодов не менее

,12х6≥0,02х7

или 0,2х6-0,02х7≥0

)корнеклубнеплодов не более

,12х6≤0,12х7

или 0,12х6-0,12х7≤0

. ограничения по содержанию отдельных видов кормов

) удельный вес соломы в группе грубых

,31х4≥0,5*(0,42х2+0,5х3+0,31х4)

или после преобразований

,21х2-0,25х3+0,155х4≥0

Целевая функция - минимальная себестоимость рациона=14,5х1+3,4х2+2,1х3+0,2х4+0,6х5+2,1х6→min

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

Данные при возделывании культур представлены в таблице:

Сельскохозяйственные культуры могут размещаться по двум вариантам севооборотов:

Севооборот №1

. однолетние травы

. озимые зерновые

. картофель

. ячмень с подсевом многолетних трав

. многолетние травы

. озимые зерновые

. лен

. овес

Севооборот №2

. однолетние травы

. озимые зерновые с подсевом многолетних трав

. многолетние травы

. лен

. картофель

. ячмень

. озимые зерновые

. картофель

. ячмень

Плановое задание по продажи продукции государству, ц:

) картофель 56120

) льносемена 1050

) льноволокно 1830

Для обеспечения животноводства кормами необходимо произвести следующее количество кормов, ц корм.ед.:

концентрированных 17930

грубых 2380

сочных 7280

зеленых 8760

Основные переменные:

В севообороте №1:

х11 - озимая пшеница

х21 - озимая рожь

х31 - яровой ячмень

х41 - овес

х51 - картофель

х61 - лен

х71 - однолетние травы

х81 - многолетние травы на зеленый корм

х91 - многолетние травы на сено

х101 - многолетние травы на силос

х1 - площадь севооборота №1

В севообороте №2

х12 - озимая пшеница

х22 - озимая рожь

х32 - яровой ячмень

х52 - картофель

х62 - лен

х72- однолетние травы

х82 - многолетние травы на зеленый корм

х92 - многолетние травы на сено

х102 - многолетние травы на силос

х2 - площадь севооборота №2

х3 - материально-денежные затраты, руб.

х4 - Валовая продукция, руб.

Система ограничений:

. По площади пашни:

) х1+х2<=2377

. По площади севооборота №1:

) х11+х21+х31+х41+х51+х61+х71+х81+х91+х101-х1<=0

. По площади отдельных сельскохозяйственных культур внутри севооборота №1:

) озимые зерновые в севообороте №1+x21=0,25x1

) ячмень-0,125x1=0

) овес-0,125x1=0

) картофель-0,125x1=0

) лен-0,125x1=0

) однолетние травы-0,125x1=0

) многолетние травы+x91+x101-0,125x1=0

. По площади севооборота №2:

) x12+x22+x32+x52+x62+x72+x82+x92+x102-x2<=0

. По площади отдельных сельскохозяйственных культур внутри севооборота №2:

) озимые зерновые+x22-0,22x2=0

) ячмень-0,22x2=0

) картофель-0,22x2=0

) лен-0,11x2=0

) однолетние травы-0,11x2=0

) многолетние травы+x92+x102-0,11x2=0

. По использованию трудовых ресурсов:

) 19,6x11+15,6x21+14,5x31+12,7x41+81,8x51+ 129,8x61+ 10,37x71+11,9x81+14,4x91+16,1x10,1+19,6x12+15,6x22+14,5x32+81,8x52+129,8x62+10,37x72+11,9x82+14,4x92+16,1x10,2<=98700

. На продажу:

) зерно, ц+x21+x12+x22>=18030

) картофель, ц+x52>=56120

) льносемена

,8x6,1+4,8x6,2>=1050

) льноволокно

,9x61+6,9x62>=1830

. По производству кормов:

) концентрированные корма, ц.корм.ед.

,2x3+26,9x41+38,2x32>=17930

) грубые корма

,3x91+21,3x92>=2380

,9x51+22,4x101+9,9x52+22,4x102>=7280

) зеленый корм

x71+33x81+32x72+33x82>=8760

. Материально-денежные затраты, руб.

) 214,27x11+226,03x21+171,72x31+147,22x41+781,71x51+468,69x61 +227,74x71+120,88x81+241,47x91+273,58x101+214,27x12+226,03x22+171,72x32+781,71x52+468,69x62+227,74x72+120,88x82+241,47x92+273,58x102-x3=0

. Валовая продукция, руб.

) 461,6x11+435,28x21+…+190,5x10,1+461,6x12+…+190,5x10,2-x4=0

ЦЕЛЕВАЯ ФУНКЦИЯ: х4 стремится к максимуму

Решение задачи:

В ячейку Z4 вставим формулу « =СУММПРОИЗВ(С4:Y4;$C$31:$Y$31)», затем растянем форму до целевой ячейки включительно.

Затем находим решение задачи с помощью «поиска решения». Находим: Данные→Анализ→Поиск решения. Как видно на рисунке: устанавливаем целевую ячейку; равный- max значению; изменяя ячейки и ограничение.