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

Размещено на http://www.allbest.ru

Размещено на http://www.allbest.ru

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

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

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

Показатели	Озимая пшеница	Яровой ячмень	Овес
Урожайность с 1 га, ц.	40	35	30
Затраты труда на 1 га, чел.-ч.	20	15	13
Затраты удобрений на 1 га, руб.	80	50	40

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

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

Решение.

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

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

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

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

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

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

Х1+х2+х3=1600

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

20*х1+15*х2+13*х3?27000

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

80*х1+50*х2+40*х3?99000

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

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

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

0,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

Показатели	Озимая пшеница	Озимая рожь	Яровая пшеница	Яровой ячмень	Овес
Площадь посева, га	226	350	189	211	421
Урожайность без внесения удобрений с 1 га, ц	29,7	26,3	25,1	28,9	20,1
Доза внесений удобрений на 1 га, ц действующего вещества:
Азотных	0,6	0,5	0,8	0,5	0,6
Фосфорных	0,7	0,7	0,6	0,5	0,7
Калийных	0,4	0,3	0,2	0,3	0,4
Прибавка урожая за счет внесения удобрений с 1 га:
ц	10,8	9,9	10,1	9,4	9,1
руб	115,88	124,61	108,37	89,02	93,09

Таблица 7.2

показатели	Сульфат аммония N	Суперфосфат Р2О5	Хлористый калий К2О
Количество, ц	3762	4530	743
Содержание действующего вещества, %	21	20	59
Цена 1 ц, руб	4,00	2,39	1,10
Стоимость внесения 1 ц, руб	0,25	0,19	0,26

Решение:

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

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

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

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

Таблица 9.5

Корма	Содержание в 1 кг корма	Себестоимость 1 кг корма, коп
	Кормовых единиц, кг	Переваримого протеина, г	Кальция, г	Фосфора, г	Каротина, мг	Сухого вещества, кг
Комбикорм	0,9	112	15	13	-	0,87	14,5
Сено луговое	0,42	48	6	2,1	15	0,85	3,4
Сено клеверотимофеечное	0,5	52	7,4	2,2	30	0,83	2,1
Солома овсяная	0,31	14	4,3	1	4	0,85	0,2
Силос кукурузный	0,2	14	1,5	0,5	15	0,23	0,6
Кормовая свекла	0,12	9	0,4	0,4	-	0,13	2,1

Решение:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Х7 ? 8,8

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

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

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

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

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

13х1+2,1х2+2,2х3+х4+0,5х5+0,4х6 ? 32

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

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

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

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

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

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

0,9х1 ? 0,09х7

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

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

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

0,9х1 ? 0,2х7

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

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

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

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

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

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

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

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

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

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

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

0,2х5?0,2х7

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

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

0,2х5?0,5х7

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

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

0,12х6?0,02х7

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

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

0,12х6?0,12х7

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

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

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

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

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

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

Целевая функция - минимальная себестоимость рациона

Z=14,5х1+3,4х2+2,1х3+0,2х4+0,6х5+2,1х6>min



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

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

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

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

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

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

3. картофель

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

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

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

7. лен

8. овес

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

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

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

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

4. лен

5. картофель

6. ячмень

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

8. картофель

9. ячмень

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

1) зерно 18030

2) картофель 56120

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

4) льноволокно 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. По площади пашни:

1) х1+х2<=2377

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

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

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

3) озимые зерновые в севообороте №1

x11+x21=0,25x1

4) ячмень

x31-0,125x1=0

5) овес

x41-0,125x1=0

6) картофель

x51-0,125x1=0

7) лен

x61-0,125x1=0

8) однолетние травы

x71-0,125x1=0

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

x81+x91+x101-0,125x1=0

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

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

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

11) озимые зерновые

x12+x22-0,22x2=0

12) ячмень

x32-0,22x2=0

13) картофель

x52-0,22x2=0

14) лен

x62-0,11x2=0

15) однолетние травы

x72-0,11x2=0

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

x82+x92+x102-0,11x2=0

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

17) 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

7. На продажу:

18) зерно, ц

x11+x21+x12+x22>=18030

19) картофель, ц

x51+x52>=56120

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

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

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

6,9x61+6,9x62>=1830

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

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

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

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

21,3x91+21,3x92>=2380

24) сочные корма

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

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

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

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

26) 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

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

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

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

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

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

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

Размещено на Allbest.ru