РЕШЕНИЕ ЭКОНОМИЧЕСКИХ ЗАДАЧ
СРЕДСТВАМИ EXCEL
Цель и задачи работы
Знакомство с элементами математического программирования и способами решения задач математического программирования в Excel 5.0
Теоретические положения
Математическое программирование - это прикладная отрасль математики, которая является теоретической основой решения задач оптимального планирования.
Наиболее разработанным и широко применяемым разделом математического программирования является линейное программирование, целью которого служит отыскание оптимума (максимума или минимума) заданной линейной функции при наличии ограничений в виде линейных уравнений или линейных неравенств.
Задача об оптимальном использовании ресурсов
Для решения задач экономики математическими методами выражают экономическое содержание задачи через определенные математические зависимости, т.е. составляют так называемую экономико-математическую модель задачи.
Постановка задачи. Предположим, что предприятие выпускает n различных изделий. Для их производства требуется m различных видов ресурсов (разных видов сырья, вспомогательных материалов, запасов машинного времени, людских ресурсов и т.д.) Эти ресурсы ограничены и составляют в планируемый периаод соответственно b1, b2,..., bm условных единиц.
Известны также технологические коэффициенты aij, которые показывают, сколько единиц i -го ресурса требуется для производства единицы j -го вида изделия (i =1, 2,..., m; j =1, 2,..., n). Пусть прибыль, получаемая предприятием при реализации единицы изделия j- го вида, равна сj (j= 1, 2,..., n). В планируемый период все показатели aij, cj, и bi предполагаются постоянными.
Требуется составить такой план выпуска продукции, при котором прибыль предприятия от реализации ее была бы наибольшей.
Математическая формулировка задачи выглядит следующим образом.
Требуется найти такие неотрицательные значения x1, x2,... xn (xi - количество единиц изделия i - го вида), которые удовлетворяли бы системе ограничений
причем 
,
и при которых прибыль от реализации всей продукции F=c1x1+c2x2+...+cnxn была бы максимальной. Функция F выражает конечную цель оптимального планирования в данном случае - получение наибольшей прибыли, поэтому эту функцию называют целевой.
Пример 1.Компания производит телевизоры, стерео- и акустические системы. Прибыль от производства одного телевизора - 75 условных единиц, стереосистемы - 50 у.е., акустической системы - 35 у.е. На складе имеется 450 шт. шасси, 250 кинескопов, 800 громкоговорителей, 450 источников питания, 600 радиодеталей. Для производства одной единицы продукции требуется следующее количество деталей
| Название детали | Телевизор | Стерео система | Акустическая система |
| Шасси | |||
| Кинескоп | |||
| Громкоговоритель | |||
| Источник питания | |||
| Радиодетали |
Определить план выпуска продукции при котором прибыль предприятия будет максимальна.
Экономико-математическая модель задачи
F=75x1+50x2+...+35xn -> max
при ограничениях
причем 
,
Для решения задачи с помощью Excel 5.0 введем на рабочий лист следующий данные:
Причем в некоторых ячейках содержатся формулы
| Ячейка | Формула |
| D10 | =75*D2 |
| E10 | =50*E2 |
| F10 | =35*F2 |
| D11 | =D10+E10+F10 |
| С4 | =D2*D4+E2*E4+F2*F4 |
| С5 | =D2*D5+E2*E5+F2*F5 |
| C6 | =D2*D6+E2*E6+F2*F6 |
| C7 | =D2*D7+E2*E7+F2*F7 |
| C8 | =D2*D8+E2*E8+F2*F8 |
Решение задачи производится с помощью пункта меню Данные/ Поиск решения. При этом в диалоговом окне вводятся следующие значения:
1. целевая ячейка - D11;
2. максимальное значение;
3. изменяя ячейки D2:F2;
4. добавить ограничения:
· ссылка на ячейки C4:C8
· ограничение <=
· B4:B8
5. добавить ограничения
· ссылка на ячейки D2:F2
· ограничение >=
· 0
6. выполнить.
После поиска решения Сохранить найденное решение, которое будет иметь вид: количество телевизоров - 200, стереосистем - 200, аудиосистем - 0; полученная прибыль 25000 условных единиц.
Задача о смесях
К группе задач о смесях относятся задачи по отысканию наиболее дешевого набора из отпущенных исходных материалов, обеспечивающего получение смеси с заданными свойствами.
Постановка задачи. Имеется m видов материалов. Стоимость единицы материала i- го вида сi. Каждый вид материалов состоит из не более чем n компонент. Количество j -го компонента в единице i- го материала соответственно aij. Необходимо составить смесь имеющую заданные свойства, т.е. количество j- го компонента в смеси не менее bj. Затраты на приобретение материалов для смеси должны быть минимальны.
Математическая формулировка задачи выглядит следующим образом.
Требуется найти такие неотрицательные значения x1, x2,... xm (xi - количество материала i - го вида в смеси), которые удовлетворяли бы системе ограничений
причем 
,
и при которых стоимость смеси F=c1x1+c2x2+...+cmxm была бы минимальной.
Пример 2. На свиноферме производится откорм свиней. Известно, что каждая свинья должна ежедневно получать не менее 6 единиц жиров, 8 ед. белков, 12 ед. углеводов. Для откорма свиней можно закупить три вида кормов: картофель, жмых и комбикорм. Содержание каждого вещества в различных видах корма и стоимость единицы каждого корма приведены в таблице.
| Вид | Вещества | Стоимость | ||
| корма | жиры | белки | углеводы | единицы корма |
| жмых | ||||
| комбикорм | ||||
| картофель | 1.5 | 2.5 |
Требуется обеспечить наиболее дешевый рацион откорма.
Экономико-математическая модель задачи
F=2x1+3x2+...+2.5 -> min
при ограничениях
причем ,
Для решения задачи с помощью Excel 5.0 введем на рабочий лист следующий данные:
Причем в некоторых ячейках содержатся формулы
| Ячейка | Формула |
| F6 | =СУММПРОИЗВ(B3:B5;F3:F5) |
| C7 | =СУММПРОИЗВ(C3:C5;$F$3:$F$5) |
| D7 | =СУММПРОИЗВ(D3:D5;$F$3:$F$5) |
| E7 | =СУММПРОИЗВ(E3:E5;$F$3:$F$5) |
Решение задачи производится с помощью пункта меню Сервис/ Поиск решения. При этом в диалоговом окне вводятся следующие значения:
1. целевая ячейка - F6;
2. минимальное значение;
3. изменяя ячейки F2:F5;
4. добавить ограничения:
· ссылка на ячейки C7:E7
· ограничение >=
· C6:E6
5. добавить ограничения
· ссылка на ячейки F3:F5
· ограничение >=
· 0
6. выполнить.
После поиска решения Сохранить найденное решение, которое будет иметь вид: жмых использовать не стоит, картофель - 0, 88 единицы, комбикорм - 3,33 ед.; при этом затраты на одну свинью составят 12,.22 условных единиц в сутки.
Транспортная задача
Простейшими транспортными задачами являются задачи о перевозках некоторого однородного груза из пунктов отправления (от поставщиков) в пункты потребления (к потребителям) при обеспечении минимальных затрат на перевозки.
Постановка задачи. На k предприятиях производится некоторый продукт, причем мощность i- го поставщика в планируемый период (т.е. количество производимого продукта) Mi. Продукт необходимо поставить l потребителям, причем мощность j -го потребителя в планируемый период (требуемое количество продукта) Nj. Показатель aij отражает затраты на перевозку единицы груза от i -го поставщика к j -му потребителю. Необходимо найти такое распределение потребителей и поставщиков при котором общие затраты на перевозки были бы минимальны.
Различают закрытую и открытую модели. Модель называется закрытой если 
, в противном случае модель - открытая.
Математическая формулировка задачи для закрытой модели выглядит следующим образом.
Требуется найти такие неотрицательные значения x11, x12,... xkl (xij - количество единиц продукта поставляемого от i - го поставщика к j - му потребителю), которые удовлетворяли бы системе ограничений
причем 
,
и при которых затраты на перевозку груза F=a11 x11 + a12 x12 +...+aijxij +... + aklxkl были бы минимальны.
Пример 3. На четырех складах фирмы хранится сахар в мешках, причем на первом складе - 100 мешков, на втором - 125, на третьем - 75, на четвертом - 75. Фирма заключила договор на поставку пяти организациям: ООО Марс - 85 мешков, АО Рита - 65, ЗАО Град - 80, ТОО Форд - 75, ЧП Шпак - 70 мешков.
Затраты на перевозку из одного пункта в другой приведены в таблице
| ООО Марс | АО Рита | ЗАО Град | ТОО Форд | ЧП Шпак | |
| Склад 1 | |||||
| Склад 2 | |||||
| Склад 3 | |||||
| Склад 4 |
Определить план доставки груза потребителям при котором затраты на перевозку будут минимальны.
Экономико-математическая модель задачи
F=4x11 +2x12 +3x13+1x14+2x15+6x21 +5x22 +3x23+4x24+3x25+1x31 +2x32 +5x33+6x34+5x35+6x41 +
+4x42 +5x43+2x44+3x45 -> min
при ограничениях
причем 
,
Для решения задачи с помощью Excel 5.0 введем на рабочий лист следующий данные
:
Причем в некоторых ячейках содержатся формулы
| Ячейка | Формула |
| B9 | =СУММ(C9:G9) |
| B10 | =СУММ(C10:G10) |
| B11 | =СУММ(C11:G11) |
| B12 | =СУММ(C12:G12) |
| A10 | =СУММПРОИЗВ(C4:G7;C9:G12) |
| C13 | =СУММ(C9:C12) |
| D13 | =СУММ(D9:D12) |
| E13 | =СУММ(E9:E12) |
| F13 | =СУММ(F9:F12) |
| G13 | =СУММ(G9:G12) |
Решение задачи производится с помощью пункта меню Сервис/ Поиск решения. При этом в диалоговом окне вводятся следующие значения:
1. целевая ячейка - A10;
2. минимальное значение;
3. изменяя ячейки C9:G12;
4. добавить ограничения:
· ссылка на ячейки B9:B12
· ограничение =
· B4:B7
5. добавить ограничения
· ссылка на ячейки C13:G13
· ограничение =
· C2:G2
6. добавить ограничения
· ссылка на ячейки C9:G12
· ограничение >=
· 0
7. выполнить.
После поиска решения Сохранить найденное решение, которое будет иметь вид: ООО Марс необходимо доставить 10 мешков со склада 1 и 75 мешков со склада 4; АО Рита - 65 мешков со склада 1; ЗАО Град - 80 со склада 2; ТОО Форд - 25 с первого склада и 50 с второго; ЧП Шпак - 45 со склада 2 и 25 со склада 4. При этом затраты на доставку составят 820 условных единиц.
Оборудование
ПЭВМ IBM PC, операционная среда Windows, Excel с установленным модулем «Поиск решения»..
4. Контрольные вопросы
Назначение функции СУММПРОИЗВ (). - Мощность поставщика и потребителя. - Открытая модель транспортной задачи. - Ограничение типа искомых значений.
Подготовка к работе
5.1. Получить вариант задания у преподавателя.
5.2. Изучить теоретическую часть.
5.3. Ответить на контрольные вопросы.
5.4. Составить математическую модель задачи.
5.5. Составить последовательность действий для выполнения задания.
Программа работы
6.1. Загрузить Excel 5.0
6.2. Внести на рабочий лист исходные данные.
6.3. Произвести поиск оптимального решения.
6.4. Получить отчеты и изучить их содержимое.
6.5. Результаты работы показать преподавателю.
6.6. Сохранить таблицу на диске в своем каталоге.
6.7. Выйти из Excel.
6.8. Оформить отчет.
Оформление отчета
Отчет должен содержать название лабораторной работы, задание на работу, математическую модель задачи, вид рабочего листа с исходными данными и необходимыми формулами, адрес целевой ячейки, адреса изменяемых ячеек, ограничения, найденное оптимальное решение и значение целевой функции.