Решение задач транспортного типа в среде EXCEL

Федеральное агентство по образованию

Новосибирский государственный университет

экономики и управления

Кафедра ЭММ и П

Лабораторная работа №3

по курсу экономико-математических методов, на тему:

«Решение задач транспортного типа в среде EXCEL»

                                       Выполнила студентка гр.4046

Желтова Е.А.

Проверил: Доцент кафедры

 ЭММиП Савиных В.Н.

Новосибирск,2006

План лабораторной работы.

1. Составление ЭММ расчета оптимального плана перевозок

2. Решение транспортной модели на основе компьютерного аналога ЭММ.

3. Составление ЭММ оптимальной расстановки механизмов по участкам работ

4. Решение задачи об оптимальной расстановки механизмов с помощью надстройки «поиск решения»

5. Формулировка ЭММ формирования оптимальных пар сотрудников

6. Решение задачи о напарниках в среде EXCEL.

1.  Составление экономико-математической модели  расчета оптимального плана перевозок

Мощность поставщиков: 52, 39, 97

Спрос: 62, 29, 81, 46, 32.

Матрица тарифов:

13 14 12 9  11

14 15 12 10 13

16 14 13 13 14

 У каждого поставщика можно вывезти не более того, что он произведет

x11+х12+х13+х14+х15<=52

x21+x22+x23+x24+x25<=39

x31+x32+x33+x34+x35<=97

Каждый потребитель должен быть удовлетворен в своем спросе

x11+x21+x31>=62

x12+x22+x32>=29

x13+x23+x33>=81

x14+x24+x34>=46

x15+x25+x35>=32

Xij>=0 i=1,2,3; j=1,2,3,4,5

Целевая функция:

Z=x11+x12+x13+x14+x15+x21+x22+x23+x24+x25+x31+x32+x33+x34+x35-> min

2.  Решение транспортной модели на основе компьютерного аналога ЭММ.

Проверка условия баланса

52+39+97=188

62+29+81+46+32=250

Т.к. спрос превышает предложение, то необходимо ввести фиктивного поставщика, который будет поставлять 250-188=62 единицы.

      Составляя таблицу на рабочем листе Excel, в ячейки заносим необходимые данные: в ячейках В3:U3 ставим 1, эти ячейки после необходимых расчетов принимают значения для наиболее оптимальной производственной программы фирмы. В ячейку V6 записываем формулу, которая находит сумму произведений значений Х и затрат. Для  ячейки V6 вызываем «мастер функций».  В появившемся окне «мастера функций» указываем значения Х (В3:U3) как массив 1, а массив 2 – ячейки В6:U6, соответственно. Копируем эту формулу до конца таблицы.

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

            Для запуска вычислительного процесса необходимо нажать кнопку ВЫПОЛНИТЬ, после чего в случае верного решения появляется окно «Результаты поиска решения». При этом на рабочем листе происходят соответствующие изменения в ячейках  В3, U3, найдены оптимальные значения переменных, в целевой ячейке – минимальное значение целевой функции. Внизу экрана появился корешок – отчет по результатом.

С помощью этих расчетов мы нашли наиболее оптимальный план перевозок.

Zmin=2238  

       0  0  0  20 32

  X=0  0  13 28 0

       0  29 68 0  0

       62 0  0  0  0

Из последней строки матрицы следует, что первый потребитель не получит ни одной единицы товара, т.к. последний поставщик фиктивный. Остальные потребители будут полностью удовлетворены в своем спросе.

3.  Составление ЭММ оптимальной расстановки механизмов по участкам работ.

Парк механизмов:

1=70

2=42

3=77

4=45

Спрос участка:

A=91

В=62

С=81

Матрица производительности:

3 5 4

7 4 8

3 3 4

6 3 4

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

X11+x12+x13<=70

X21+x22+x23<=42

X31+x32+x33<=77

X41+x42+x43<=45

Спрос каждого участка должен быть удовлетворен:

X11+x21+x31+x41>=91

X12+x22+x32+x42>=62

X13+x23+x33+x43>=81

Xij>=0 i=1,2,3,4; j=1,2,3

Целевая функция:

Z=3x11+5x12+4x13+7x21+4x22+8x23+3x31+3x32+4x33+6x41+3x42+4x43-> max

4. Решение задачи об оптимальной расстановки механизмов с помощью надстройки «поиск решения»

Проверка условия баланса

70+42+77+45=234

91+62+81=234

      Составляя таблицу на рабочем листе Excel, в ячейки заносим необходимые данные: в ячейках В3:М3 ставим 1, эти ячейки после необходимых расчетов принимают значения для наиболее оптимальной производственной программы фирмы. В ячейку N6 записываем формулу, которая находит сумму произведений значений Х и затрат. Для  ячейки N6 вызываем «мастер функций».  В появившемся окне «мастера функций» указываем значения Х (В3:M3) как массив 1, а массив 2 – ячейки В6:M6, соответственно. Копируем эту формулу до конца таблицы.

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

            Для запуска вычислительного процесса необходимо нажать кнопку ВЫПОЛНИТЬ, после чего в случае верного решения появляется окно «Результаты поиска решения». При этом на рабочем листе происходят соответствующие изменения в ячейках  В3, U3, найдены оптимальные значения переменных, в целевой ячейке – минимальное значение целевой функции. Внизу экрана появился корешок – отчет по результатом.

С помощью этих расчетов мы нашли наиболее оптимальную расстановку механизмов.

Zmax=1210  

        4  62  4 

  X=40  0  0

       0  0 77  

       45 0  0   

На участке А: Мех. 1=8ед

                           Мех. 2=38ед

                           Мех. 3=0ед

                           Мех. 4=45ед

На участке В: Мех. 1=62ед

                          Мех. 2=0 ед

                          Мех. 3=0ед

                           Мех. 4=0ед