Решение задачи экономического и управленческого характера

		Пункты назначения					Наличие
		В1	В2	В3	В4	В5
Пункты отправления	А1	17	3	1	15	39	260
	А2	10	14	12	20	46	100
	А3	5	10	17	17	39	100
	Потребность	100	60	90	70	80

 

Решение:

В общем виде формулировка транспортной задачи осуществляется следующим образом: требуется перевезти  определенное количество однородного  груза из m пунктов отправления в n пунктов назначения. Известны расходы на перевозку единицы груза из каждого пункта отправления в каждый пункт назначения.

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

Для составления  экономико-математической модели задачи введем обозначения:

 n число пунктов отправления;

m число пунктов назначения;

ai общее количество груза в I-м пункте отправления;

bj общее количество груза, необходимое в j-м пункте назначения;

cij затраты на транспортировку единицы груза из I-го пункта

отправления в j-й пункт  назначения;

Z совокупные затраты на перевозку всего груза;

xij исходно неизвестное количество груза, которое перевозится из

i-го пункта отправления  в j-й пункт назначения.

Экономико-математическая модель задачи:

 

              n       m

 Z= å å ® c_y* x_{y        min  (1)}

  i=1     j=1

 

 

   m

å = x_y = a_i,    i= 1,n   (2)

 

   n

å = x_y = b_j,    j= 1,n    (3)

I=1           I

x_y ³ 0, _,    i= 1,n, _,    j= 1,n  (4)

В рассмотренной  модели транспортной задачи предполагается, что суммарные запасы поставщиков  равны суммарынм запросам потребителей, т.е.:

m                      n

å a_i  = åb_j

 i=1                        j=1

Такая задача называется задачей с правильным балансом, а модель задачи закрытая

Математическая формулировка транспортной задачи такова: найти переменные задачи X=(x_ij), i=1,2,...,m; j=1,2,...,n, удовлетворяющие системе ограничений (цифра 2 на математической модели) , (3), условиям неотрицательности (4) и обеспечивающие минимум целевой функции (1).                               Целевая функция минимизирует совокупные затраты на транспортировку всех партий грузов из всех пунктов отправления во все пункты назначения. Система ограничений (2)говорит о том, что весь груз из каждого пункта его сосредоточения должен быть вывезен. Система ограничений (3) говорит о том, что потребность в грузе в каждом пункте назначения должна быть удовлетворена. Система ограничений (4) говорит о том, что по любому маршруту некоторое количество груза либо перевозится, либо нет.

Теперь составим модель транспортной задачи для данной задачи:

 

Вводим переменные задачи (матрицу перевозок):

        x₁₁   x₁₂   x₁₃   x₁₄   x₁₅

Х=   x₂₁   x₂₂   x₂₃   x₂₄   x₂₅

           x₃₁ x₃₂  x₃₃   x₃₄  x₃₅

 

 Записываем матрицу  стоимостей:

             17   3   1 15  39

C=      10  14  12 20  46

             5   10    17  17  39

 

Целевая функция задачи равняется сумме произведений всех соответствующих элементов матриц C и X.

Z(X)= 17x₁₁ + 3 x₁₂ + 1 x₁₃ + 15 x₁₄ + 39x₁₅ + 10x₂₁ + 14x₂₂ + 12x₂₃ +20x₂₄+ 46x₂₅ + 5x₃₁ + 10x₃₂ + 17x₃₃ + 17x₃₄ + 39x₃₅

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

    x₁₁ + x₁₂ +  x₁₃ + x₁₄  + x₁₅ = 260

    x₂₁ + x₂₂ +  x₂₃  + x₂₄  + x₂₅ = 100

       x₃₁ + x₃₂ + x₃₃  + x₃₄ +  x₃₅ = 100

 

Это означает, что запасы поставщиков вывозятся полностью.

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

    x₁₁ + x₂₁ + x₃₁ = 100

    x₁₂ + x₂₂ + x₃₂ = 60

    x₁₃ + x₂₃ + x₃₃ = 90

     x₁₄ + x₂₄ + x₃₄ = 70

     x₁₅ + x₂₅ + x₃₅ = 80

Это означает, что запросы  потребителей удовлетворяются полностью. Необходимо также учитывать, что  перевозки не могут быть отрицательными:

X_iy >=0,  i=1, 2,…, m; j=1, 2…, n

Для решения  задачи средством EXCEL «Поиск решения» необходимо разместить в рабочем листе исходные данные и подготовить поля для размещения условий и результатов решения задачи (выбор ячеек листа произвольный). Таким образом, будет создана «Электронная модель транспортной задачи».

 

Матрица исходных данных (стоимость перевозки за единицу товара)
	Пункты назначения									Наличие
		В1		В2		В3		В4	В5
Пункты отправления	А1	17		3		1		15	39	260
	А2	10		14		12		20	46	100
	А3	5		10		17		17	39	100
	Потребность	100		60		90		70	80
Матрица оптимальных перевозок (количество товара)
	Потребители
Поставщики	В1	В2	В3		В4		В5	Итого по поставщикам
А1	1	1	1		1		1	5
А2	1	1	1		1		1	5
А3	1	1	1		1		1	5
Итого по потребителям	3	3	3		3		3
Целевая функция	265

 

В матрице результата во все ячейки диапазона (B12:F15) внести «1» в качестве исходных значений объемов поставок xij, после решения задачи в этих ячейках будут находиться значения поставок, обеспечивающие минимальные затраты на перевозку груза.

Вводим в  ячейки (В15;F15) итог поставок по потребителям: в ячейку В15 ввести формулу =СУММ(В12:В14) − итог поставок по1-му потребителю, скопировать эту формулу в ячейки (С15:F15);

Вводим в  ячейки (G12:G14) итоги реализации мощности каждого из поставщиков: в ячейку G12 ввести формулу =СУММ(В12:F12) − итог реализации поставок от 1-го поставщика, скопировать эту формулу в ячейки (G13:G14);

Выделяем ячейку для ввода формулы целевой  функции,  в этом случае В17 и ввести формулу =СУММПРОИЗВ(C4:G6; B12:F14) − суммарная стоимость перевозок по всем направлениям.

В окне Поиск решения указываем целевую ячейку В21 равную минимальному значению, а также вводим ограничения. Окно Поиска решения будет выглядеть следующим образом:

 

 

После проведенной  подготовительной работы можно запустить задачу на выполнение. В результате решения ячейки (В12:F14) заполнились значениями объемов перевозок, которые обеспечивают минимальные суммарные затраты на транспортировку всего груза:

 

Целевая функция  минимизирует совокупные затраты на транспортировку всех партий грузов из всех пунктов отправления во все пункты назначения. Величина затрат равна 6540.

 

 

 

Глава 2 Создание базы данных в Access

Задача 2

Выполнение второго задания  предполагает определение штатного расписания организации, что включает в себя следующие действия:

1. построение математической модели решения задачи;
2. создание базы данных в Access для хранения штатного расписания
3. экспорт штатного расписания из Excel в СУБД Access;
4. получение решение задачи;
5. создание нескольких вариантов запросов и организация на их основе отчетов.

Условие задачи 2:

(Вариант 2/33)

  Составить штатное  расписание частной  больницы, для обеспечения нормальной работы  которой, требуется 6-7 санитарок, 5-6 медсестер, 8-9 врачей, 2 заведующих  отделениями, 1 завхоз, 1 главный врач и 1 заведующий больницей. Фонд заработной платы составляет 1600000 рублей. Зарплата рассчитывается, исходя из минимальной зарплаты, согласно выражению: k*x+a+b, где х – минимальная зарплата, равная 5 тыс. рублей, коэффициенты k, a и b устанавливаются руководством больницы.

должность	коэффициент к	коэффициент а	коэффициент в	зарплата	количество сотрудников	суммарная зарплата	минимальная зарплата	месячный фонд заработной платы
санитарки				0,0		0,0	5,0	0
медсестры				0,0		0,0
врачи				0,0		0,0
зав отделением				0,0		0,0
завхоз				0,0		0,0
глав врач				0,0		0,0
зав больницей				0,0		0,0

 

Решение:

Построение  математической модели задачи.

За основу возьмем минимальную заработную плату, равная 5 тысячам, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от минимального оклада: k*x+а+b, где x – минимальная заработная плата; k_, а и b – коэффициенты, которые устанавливаются руководством организации:

 

• уборщица получает 9 тыс.рублей (b₁=0,27; k₁=2);
• мл. продавец получает 11 тыс.рублей (b₂=0,35; k₂=4);
• ст. продавец получает 12 тыс.рублей(b₃=0,43; k₃=6);
• товаровед 19 тыс.рублей (b₄=0,55; k₄=8);
• менеджер 19 тыс.рублей(b₅=0,55; k₅=8);
• директор 38 тыс.рублей (b₆=2,00; k₆=20);
• зам. директора 31 тыс. рублей (b₇=0,80; k₇=16).