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

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

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

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

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

Решение.

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

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

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

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

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

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

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

20*х1+15*х2+13*х3?27 000

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

80*х1+50*х2+40*х3?99 000

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

Таблица 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-общее количество кормовых единиц в рационе, кг Запишем систему ограничений в развернутом виде.

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 га пашни. Ресурсы труда — 98 700 чел.-ч.

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

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

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

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

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

3. картофель

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

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

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

7. лен

8. овес Севооборот № 2

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

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

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

4. лен

5. картофель

6. ячмень

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

8. картофель

9. ячмень Плановое задание по продажи продукции государству, ц:

1) зерно 18 030

2) картофель 56 120

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

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

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

— концентрированных 17 930

— грубых 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,125×1=0

5) овес

x41−0,125×1=0

6) картофель

x51−0,125×1=0

7) лен

x61−0,125×1=0

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

x71−0,125×1=0

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

x81+x91+x101−0,125×1=0

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

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

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

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

x12+x22−0,22×2=0

12) ячмень

x32−0,22×2=0

13) картофель

x52−0,22×2=0

14) лен

x62−0,11×2=0

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

x72−0,11×2=0

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

x82+x92+x102−0,11×2=0

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

17) 19,6×11+15,6×21+14,5×31+12,7×41+81,8×51+ 129,8×61+ 10,37×71+11,9×81+14,4×91+16,1×10,1+19,6×12+15,6×22+14,5×32+81,8×52+129,8×62+10,37×72+11,9×82+14,4×92+16,1×10,2<=98 700

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

18) зерно, ц

x11+x21+x12+x22>=18 030

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

x51+x52>=56 120

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

4,8×6,1+4,8×6,2>=1050

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

6,9×61+6,9×62>=1830

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

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

38,2×3+26,9×41+38,2×32>=17 930

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

21,3×91+21,3×92>=2380

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

9,9×51+22,4×101+9,9×52+22,4×102>=7280

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

32×71+33×81+32×72+33×82>=8760

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

26) 214,27×11+226,03×21+171,72×31+147,22×41+781,71×51+468,69×61 +227,74×71+120,88×81+241,47×91+273,58×101+214,27×12+226,03×22+171,72×32+781,71×52+468,69×62+227,74×72+120,88×82+241,47×92+273,58×102-x3=0

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

27) 461,6×11+435,28×21+…+190,5×10,1+461,6×12+…+190,5×10,2-x4=0

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

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

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