Алматинский институт энергетики и связи

 

 

Кафедра экономики и менеджмента в связи

 

 

ЭКОНОМИКО-МАТЕМАТИЧЕСКИЕ МЕТОДЫ И МОДЕЛИ В  СВЯЗИ

 

 Методические указания к выполнению лабораторных работ (для студентов очно-заочной формы  обучения по специальности 07.13 -   Экономика и менеджмент в связи)

 

 

Алматы 2004

 

 

 

СОСТАВИТЕЛИ: Е.Г.Дворникова, Э.М.Лещинская. Экономико-математические методы и модели в связи. Методические указания к выполнению лабораторных  работ (для студентов очно-заочной формы обучения специальности 07.13 -  Экономика и менеджмент в связи).- Алматы: АИЭС, 2004. -19с.

 

 

Методические указания представляют собой систематический курс лабораторных работ по дисциплине « Экономико-математические методы и модели в связи ». В качестве базовой сервисной программы использован редактор электронных таблиц.

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

        

 

Рецензент: канд. техн. наук, доц.  К.Х. Туманбаева

 

 

Печатается по плану издания Алматинского института энергетики и связи на 2004 г.

 

 

 

© Алматинский  институт энергетики и связи, 2004 г.

        

 

 

Введение

 

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

         Курс «Экономико-математические методы и модели в связи» представляет собой одну из фундаментальных дисциплин, в которой изучаются практически ценные для отрасли связи экономико-математические методы и модели. Объектом экономико-математического моделирования являются не только производственно-технологические или организационные структуры, но и экономические интересы субъектов производства.

         Программа курса «Экономико-математические методы и модели в связи» для студентов специальности     Экономика и менеджмент на предприятиях связи  очно-заочной формы обучения предусматривает выполнение лабораторных работ. Лабораторные работы проводятся с целью углубления и закрепления теоретических знаний студентов, полученных ими при изучении лекционных материалов по предмету, а также для повышения мастерства использования программных средств EXCEL применительно к решению экономических задач.

 

Организация выполнения студентами лабораторной работы:

 

- изучаются теоретические сведения;

- прорабатываются контрольные примеры;

- выполняется самостоятельная работа;

- все исходные данные, результаты и их анализ оформляются в  отчёте, который предоставляется преподавателю на защиту;

- защищается лабораторная работа.

 

 

 

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

 

Применение программных средств  EXCEL  для экономических расчётов

 

         Цель работы:  Освоить программные средства  EXCEL  «Мастер функций » и «Подбор параметра », научиться  применять их в экономических расчётах.

 

         1.1 Анализ возможностей  электронного редактора  EXCEL

 

1.1.1   Работа с формулами в редакторе  EXCEL

         В ячейки рабочей таблицы EXCEL можно вводить значения (тексты, числа) и формулы. В EXCEL все формулы начинаются со знака  = . Если поместить курсор в любую ячейку и ввести знак  =  и далее расчетную формулу , то эта информация появится в строке формул. После нажатия на клавишу  <ENTER>  EXCEL  выполнит расчеты, и в ячейке с формулой появится число – результат расчета по формуле.

Для редактирования формулы, заданной в ячейке, нужно её выделить и исправления производить в строке формул.

1.1.2   Относительные и абсолютные координаты ячеек

         Абсолютные координаты не меняются, когда ячейка, содержащая формулу, копируется в другое место. Относительные координаты в этом случае изменяются. Для того, чтобы указать абсолютные координаты ячейки, нужно поместить перед координатой строки и (или) столбца знак  $. Этот символ указывает редактору EXCEL не менять координаты ячейки ни при каких операциях.

В большинстве случаев удобно, чтобы при копировании ячейки в другое место изменялись её координаты, но иногда этого не нужно, и тогда координаты ячейки помечаются  знаком  $. Для того, чтобы пометить и строку, и столбец одновременно, нужно выделить ячейку и нажать клавишу F4.

Примеры. 

1 Пусть ячейка B5 содержит формулу  B3+B4. Если скопировать содержимое B5 в ячейку D5 , то в ячейке D5 появится формула D3+D4.

2 Пусть ячейка B5 содержит формулу  $B$3+$B$4. После копирования содержимого B5 в ячейку D5 , в D5 будет формула  $B$3+$B$4.

1.1.3  Использование функции « автосуммирование »

         Очень часто в экономических выкладках требуется произвести суммирование ряда чисел. Для этого  на панели инструментов есть кнопка  . Если поместить курсор ниже столбца чисел, которые нужно просуммировать, и нажать на , то в ячейке появится предлагаемый диапазон ячеек для суммирования. Если этот диапазон подходит, то нужно нажать на <ENTER>.

1.1.4   Мастер функций

Для обработки экономической информации  EXCEL  предлагает мощное средство «Мастер функций» – возможность использования встроенных функций:  математических, статистических, финансовых и других.

Перечень математических функций содержит элементарные функции (sin, cos, exp, ,!,…), а также СУММ (), СУММПРОИЗВ (сумма произведений двух массивов).

Финансовые функции позволяют автоматизировать вычисление будущей величины  инвестиций (БЗ), сумму платежей по процентам (ППЛАТ), величину амортизации активов (АПЛ) и т.д.

Статистические функции позволяют вычислять некоторые статистические характеристики в экономических расчётах.

Мастер функций помогает  пользователю вводить функцию в создаваемую формулу. Для этого необходимо выполнить последовательность действий:

1) Выделить ячейку для формулы.

2) Щёлкнуть на кнопке .

3) Выбрать нужную категорию функции (10 недавно использовавшихся, финансовые, дата и время, математические, статистические, ссылки и массивы  и т.д.).

4) Из списка функций выбрать нужную.

5) В зависимости от типа выбранной функции появится диалоговое окно. Теперь следует     ввести нужные значения или диапазон ячеек.

6) Щелкнуть на кнопке ОК

 

         1.2    Применение категории «математические» в статистике

 Контрольный пример -  Для изучения уровня квалификации рабочих связи  ПТУС  методом случайного отбора из 12000 рабочих были выбраны 80 человек и на основе изучения уровня их квалификации составлена таблица:

Разряд          xi

1

2

3

4

5

6

Кол-во раб.  ni

8

10

20

22

14

6

Найти средний разряд , дисперсию и среднее квадратичное отклонение разряда рабочего.

Указания:

Известны формулы для вычисления указанных параметров:

Среднее  ;    дисперсия   ;среднее квадратичное отклонение   

Для знаменателя  в   нужно использовать функцию  СУММ или  .

Для числителя в   нужно использовать функцию  СУММПРОИЗВ из категории математические,  для   - использовать функцию  КОРЕНЬ.

.

Методика выполнения расчётов

Занесём в столбцы  EXCEL    исходные данные в виде:

 

A

B

1

разряд xi

кол-во рабочих ni

2

1

8

3

2

10

4

3

20

5

4

22

6

5

14

7

6

6

В ячейке В8 подсчитаем сумму (80).

Выделим ячейку для подсчёта   , например, В9.  Нажимаем     , выбираем категорию  математические     , выбираем функцию  СУММПРОИЗВ.  В появившемся окне   МАССИВ1  заполняем значениями  А2:А7, МАССИВ2  заполняем значениями  В2:В7. Нажимаем  OK  и в ячейке В9 получаем число 282 (282=1*8+2*10+3*20+4*22+5*14+6*6).

В ячейку В10  заносим формулу  =В9/В8. В результате в В10 получаем число 3,525 . =3,525

Для подсчёта заполним сначала столбец С  значениями . Для этого в ячейку С2 занесём формулу  =А2^2 и скопируем вниз до С7. Затем в ячейке С9 с помощью функции СУММПРОИЗВ(С2:С7;В2:В7) получим  значение , равное 1146. В ячейку С10 заносим формулу  =С9/В8 и получаем значение 14,325. Дисперсия  будет равна: 14,325-3,525^2=1.899. Среднее квадратичное отклонение ==1,378 находим с помощью функции КОРЕНЬ из категории математические.

 

1.3  Применение категории « финансовые» в банковских операциях

 

1.3.1  Контрольный пример 1 -  Предприятие связи покупает для своего сотрудника квартиру в кредит.  Квартира стоит 80000. Срок погашения в течение 30 лет из расчета  10,5% годовых. Какая сумма  должна выплачиваться  ежемесячно?

Методика выполнения расчётов

         Мастер функций предлагает среди перечня других функций категорию финансовые, находим среди них функцию ППЛАТ, которая даёт сумму периодического платежа на основе постоянства сумм платежей и постоянства процентной ставки (величину выплаты за один период годовой ренты):

  ППЛАТ ( ставка; кпер; нз; бз; тип ).

  Ставка – процентная ставка;

  Кпер      -  количество периодов платы;

  Нз          -   общая сумма кредита (ссуды);

  Бз           -   баланс наличности после последней выплаты (если опущено, то бз=0);

  Тип        -  0 или 1. Если 0 – то выплаты производятся в конце периода, если 1 – в начале.

Последовательность действий

         Выделяем ячейку для суммы ежемесячного платежа,  , финансовые, ППЛАТ, ОК, появляется окно « Аргументы функции ». Заносим информацию:

Ставка:  0,105/12

Кпер     :   30*12

Нз          :    80000

Бз  и  Тип   пропускаем. 

Щёлкаем  на  ОК.

В ячейке появляется результат  - 731,79 – сумма ежемесячного платежа.

 

1.3.2 Контрольный пример 2 -  Предположим, что нужно рассчитать сумму ежемесячных выплат для сумм кредита  60000, 80000, 100000, 120000  при постоянных  КПЕР=30 лет и СТАВКе=10,5%.

Методика выполнения расчётов

         Заносим в столбец (например, в А2, А3, А4, А5 ) числа 60000, 80000, 100000, 120000. Заносим в ячейку В2 число 30,  в ячейку С2 – число 0,105. В столбце D ( в ячейках D2-D5) хотим получить результат.

Последовательность действий

         Выделим ячейку D2 ,   ,  финансовые , ППЛАТ , ОК , появляется окно с аргументами.

Ставка:  $C$2/12

Кпер      :  $B$2*12

Нз          :   А2

Щелкаем на ОК.

В ячейке D2  получаем результат - 548,84.

Выделяем эту ячейку, превращаем курсор в  +  в нижнем правом углу и протаскиваем мышь вниз по ячейкам D3-D5 . Этим действием производится копирование формулы из ячейки D2 в ячейки D3-D5 , причём  относительный адрес А2 меняется соответственно на А3, А4, А5, а абсолютные адреса  $C$2 и  $B$2  не меняются.
Таким образом, столбец D  заполняется результатами расчетов для соответствующих значений кредита из ячеек  А2, А3, А4, А5 (-548,84  -731,79  -914,74  -1097,69).

 

1.3.3 Контрольный пример 3 -  Предположим, что предприятие связи желает зарезервировать деньги для спецпроекта, который будет осуществлен через 1 год. Предприятие кладёт в банк 1000 под 6% годовых ( процент в месяц  0,5%= 6%:12). Предприятие предполагает вкладывать по 100 в начале каждого следующего месяца в течение следующих 12 месяцев. Сколько денег будет на счету  в конце 12 месяцев ?

Методика выполнения расчётов

Финансовая функция   БЗ(ставка; кпер; плата; нз; тип) вычисляет будущее значение вклада с периодическими постоянными платами и постоянным процентом.

Ставка  -  процентная ставка ;

Кпер     -  количество периодов платежей;

Плата    -  плата, производимая в каждый период;

Нз          - стоимость инвестиции на текущий момент;

Тип       -  0 или 1. Если 0 – то выплаты производятся в конце периода, если 1 – в начале.

         Таким образом, для решения примера необходимо ввести : ставка = 0,005; кпер=12; плата=-100; нз=-1000; тип=1, т.е.  БЗ(0,005; 12; -100; -1000; 1)=2301,4.

 

1.4  Подбор параметра  ( из меню « СЕРВИС » )

Эта программа применяется, когда желаемый результат формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата. Разберём действие программы на примере.

В результате решения контрольного примера 3.1 определилась сумма ежемесячного платежа -731,79.

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

Методика выполнения расчётов для подбора суммы кредита:

Приготовим в редакторе  EXCEL  информацию в виде

 

                     А

      В

1

Объём ссуды

80000

2

Процент годовых

0,105

3

Срок выплаты

30

4

Ежемесячная выплата

 

В ячейке В4  получим значение ежемесячных выплат так:

 ,  финансовые , ППЛАТ

Ставка:  В2/12

Кпер      :  В3*12

Нз           :  B1

ОК

В результате в ячейке В4 получаем число -731,79 .

       Последовательность действий для подбора суммы кредита:

Выделим  ячейку  В4.

СЕРВИС, ПОДБОР ПАРАМЕТРА.

 В появившемся окне :

УСТАНОВИТЬ В ЯЧЕЙКЕ               В4

ЗНАЧЕНИЕ                                         -650

ИЗМЕНЯЯ ЗНАЧЕНИЕ ЯЧЕЙКИ    $В$1

ОК

Появляется окно с результатом.  Одновременно в ячейке В1 появилась сумма кредита 71058,5  для ежемесячного взноса в 650.

Можно нажать  ОК  или  ОТМЕНА.

 

1.5   Самостоятельная работа

Задание 1.

 Для изучения продолжительности междугородных телефонных разговоров (в минутах) был составлен ряд распределения в виде таблицы:

Продолжительность телефонного разговора          xi

2

4

6

8

10

12

Кол-во разговоров  ni

4

10

26

48

10

2

Требуется определить среднюю продолжительность одного телефонного разговора и среднее квадратичное отклонение.

Задание 2.

 Рассчитать  ежемесячные выплаты для сумм кредита в  5000, 6000, 6500, 7200, 8700,10000 при постоянном сроке в 25 лет и ставке 11,5%.

Задание 3.

С помощью команды « Подбор параметра»  рассчитать процентную ставку, при которой ежемесячная выплата по кредиту будет равна 650. Объем ссуды 80000, срок кредита 30 лет.

Задание 4.  Клиент положил в банк 1000 под 8% годовых  на 2 года и собирается вкладывать по 100 в начале каждого месяца в течение 2 лет.  Сколько денег будет на счету в конце двух лет.

Задание 5.  Рассчитать сумму вклада в конце двухлетнего периода для исходных сумм 1500, 2000, 2500, 3000 под 10% годовых и ежемесячных вкладов по 150 в начале каждого месяца.

Задание 6.  Рассчитать ежемесячный вклад при первоначальном вкладе 1000 под 9% годовых, чтобы окончательная сумма в конце двухлетнего периода составила  4000.

Задание 7.  Рассчитать конечные суммы на счету  в конце трехлетнего периода при ежемесячных взносах  по 100,150,200,250,300  и первоначальном вкладе 1000 под 6% годовых.

 

 

 

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

 

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

 

         Цель работы:   Освоить способ решения задач линейного программирования  с помощью  средства     « ПОИСК  РЕШЕНИЯ »  из меню  «СЕРВИС»

 

2.1  Теоретические сведения

 

Общей задачей линейного программирования называется задача, которая состоит в определении  максимального (минимального) значения функции  (целевой функции)

              

при ограничениях:

               

где          -  заданные  постоянные величины и

Совокупность чисел  , удовлетворяющих ограничениям, называется допустимым решением (или планом ).

План  , при котором целевая функция принимает своё максимальное (минимальное ) значение, называется оптимальным.

Решение любой  задачи линейного программирования можно найти либо симплексным методом, либо методом искусственного базиса.

Программа   « Поиск решения » позволяет автоматизировать процесс нахождения решения указанных задач.

 

         2.2      Практическая часть

 

2.2.1 Контрольный пример -  Найти максимум линейной функции  

         
           при ограничениях:

                    

                      

 

Методика выполнения расчётов:

 

а) заполним таблицу исходных данных:

 

 

    A

 B

  C

D

E

F

G

1

Названия перемен ных

Значения перемен ных

Ограничения

Коэффициенты целевой функции

2

       X1

0

2

1

1

 

2

3

       X2

0

1

2

-1

 

-3

4

       X3

0

-2

4

2

 

6

5

       X4

0

1

0

0

 

1

6

 

 

24

22

10

 

 

7

     F(X)

 

 

 

 

 

 

8

0

 

0

0

0

 

 

 

В ячейки  В2-В5  вводим начальные значения переменных  Xi (i=1,2,3,4). В этих ячейках будут сформированы результаты задачи.
В ячейки  G2-G5  вводим коэффициенты линейной функции. В диапазон ячеек  С2-Е5  вводим коэффициенты при соответствующих неизвестных в системе ограничений. В ячейки  C6-E6  вводим правые части системы ограничений;

б) в ячейку А8  вносим формулу для вычисления значения целевой функции  F(X). Это значение можно определить при помощи встроенной математической функции  СУММПРОИЗ (1-ый массив; 2-ой массив).  Первый массив состоит из коэффициентов целевой функции (ячейки G2-G5 ), второй массив – из значений переменных  ( ячейки В2-В5).

         Таким образом, в ячейке А8  получаем значение выражения  2X1-3X2+6X3+X4.

В ячейку  С8  вводим формулу  СУММПРОИЗ (С2:С5; $В$2:$В$5), т.е. в ячейке  С8  получим  значение выражения  2X1+X2-2X3+X4.
Копируем формулу из ячейки  С8  в ячейки  D8,E8 ( через  +  в правом нижнем углу ячейки  С8 );

в)  в меню  СЕРВИС  выбираем команду  ПОИСК  РЕШЕНИЯ. Заполняем поля появившегося диалогового окна:   адрес ячейки целевой функции (целевая ячейка) ( у нас - А8 ),  тип оптимизации  ( у нас  -  максимальному значению ), адреса ячеек с переменными  ( изменяя ячейки)( у нас - $B$2:$B$5 ), ограничения  ( у нас:

$B$2:$B$5
$C$8=$C$6
$D$8<=$D$6

$E$8>=$E$6 ).

Для создания системы ограничений используется кнопка ДОБАВИТЬ. При её нажатии появляется окно, в поле которого вводятся ссылки на ячейки  и ограничения, накладываемые на переменные в задаче. Сформированную систему ограничений в дальнейшем можно редактировать с помощью кнопок  ИЗМЕНИТЬ  и  УДАЛИТЬ.

         Далее следует нажать кнопку ВЫПОЛНИТЬ, после чего будет выполнена программа  ПОИСК РЕШЕНИЯ, по результатам которой будет выведено сообщение о найденном решении. Из окна-сообщения видно, что полученные результаты можно сохранить, изменив содержимое ячеек с переменными, можно восстановить исходные значения.

 

Результаты

 

 

    A

 B

  C

D

E

F

G

1

Названия перемен ных

Значения перемен ных

Ограничения

Коэффициенты целевой функции

2

       X1

0

2

1

1

 

2

3

       X2

0

1

2

-1

 

-3

4

       X3

5,5

-2

4

2

 

6

5

       X4

35

1

0

0

 

1

6

 

 

24

22

10

 

 

7

     F(X)

 

 

 

 

 

 

8

68

 

24

22

11

 

 

 

Выводы:

Таким образом, максимальное значение целевой функции, равное  68, достигается при значениях переменных:  Х1=0; Х2=0; Х3=5,5; Х4=35. Сравнивая восьмую и шестую строки ,  делаем вывод:  ограничения исходной задачи выполняются, причём третье ограничение представляет собой строгое неравенство при оптимальных значениях переменных.

 

2.2.2    Самостоятельная работа. Найти решение задач линейного программирования

Задание 1.                                                   Задание 2.

                   

Задание 3.                                                      Задание 4.

   

 

 

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

 

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

 

Цель работы:  Освоить способ решения транспортных задач с помощью  программы    «ПОИСК  РЕШЕНИЯ»  из меню  «СЕРВИС»

 

3.1  Теоретические сведения

 

Общая постановка транспортной задачи состоит в определении оптимального плана перевозок некоторого однородного груза из   пунктов отправления   в   пунктов назначения  . При этом в качестве критерия оптимальности обычно берётся минимальная стоимость перевозок всего груза.

Обозначим  через  - тарифы перевозки единицы груза из  i-го пункта отправления в  j-й  пункт назначения. Обозначим через   - запасы груза в i-м  пункте отправления, через     - потребности в грузе в j- м  пункте назначения, причём . Обозначим через  - количество единиц груза, перевозимого из  i -го пункта отправления в j -й  пункт назначения.

Тогда математическая постановка задачи состоит в определении минимального значения функции

        

при ограничениях:

        

        

         .

Таким образом, транспортная задача является задачей линейного программирования. Для определения оптимального плана перевозок разработаны специальные методы, в которых начальным этапом решения является нахождение начального (опорного) плана. Опорный план можно определить методом северо-западного угла либо методом минимального элемента.

Программное средство EXCEL   « Поиск решения » позволяет автоматизировать процесс нахождения решения транспортных задач.

 

         3.2    Практическая часть

 

3.2.1   Контрольный пример - В узлах отправления   А1,А2,А3  находятся избыточные порожние контейнера для перевозки почтовых отправлений в количестве соответственно равных 110,190 и 90 .  Эти контейнера должны быть доставлены в четыре узла назначения В1,В2,В3,В4, потребность которых  80,60,170 и 80 порожних контейнеров соответственно.  Стоимость доставки одного контейнера из каждого узла отправления в соответствующий узел назначения в денежных единицах указана в таблице:

Пункты отправления

Пункты  назначения

Запасы

В1

В2

В3

В4

А1

8

1

9

7

110

А2

4

6

2

12

190

А3

3

5

8

9

90

Потребности

80

60

170

80

 

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

Методика выполнения расчётов

Пусть -  количество груза, перевозимое  с узла Аi  в пункт назначения  Bj.  Находим допустимое начальное решение методом северо-западного угла:

X0=

Составляем математическую модель задачи:

Найти такие значения переменных  , при которых  функция

      

принимает минимальное значение.

При этом    должны удовлетворять следующей системе ограничений:

     

       

         Для решения данной транспортной задачи при помощи команды ПОИСК РЕШЕНИЯ  заполним сначала таблицу исходных данных:

 

   A

 B

C

D

E

F

G

H

I

J

K

L

1

 

Значения переменных

 

 

 

 

 

 

 

 

 

Тарифы

2

X11

80

1

0

0

1

0

0

0

 

 

8

3

X12

30

1

0

0

0

1

0

0

 

 

1

4

X13

0

1

0

0

0

0

1

0

 

 

9

5

X14

0

1

0

0

0

0

0

1

 

 

7

6

X21

0

0

1

0

1

0

0

0

 

 

4

7

X22

30

0

1

0

0

1

0

0

 

 

6

8

X23

160

0

1

0

0

0

1

0

 

 

2

9

X24

0

0

1

0

0

0

0

1

 

 

12

10

X31

0

0

0

1

1

0

0

0

 

 

3

11

X32

0

0

0

1

0

1

0

0

 

 

5

12

X33

10

0

0

1

0

0

1

0

 

 

8

13

X34

80

0

0

1

0

0

0

1

 

 

9

14

 

 

110

190

90

80

60

170

80

 

 

 

15

F(x)

 

 

 

 

 

 

 

 

 

 

 

16

1970

 

110

190

90

80

60

170

80

 

 

 

 

а) в ячейки  В2:В13  вводим начальные значения переменных  . В этих ячейках после выполнения команды ПОИСК РЕШЕНИЯ  будут сформированы результаты решения задачи.

В ячейки  L2:L13  вводим коэффициенты целевой  функции  F(X) – тарифы перевозок 1контейнера с  i-го узла  в  j-ый пункт назначения.

В диапазон ячеек  С2:I13  вводим коэффициенты при соответствующих неизвестных в системе ограничений задачи.

В ячейки  C14:I14  вводим правые части системы ограничений;

б) в ячейку А16  вносим формулу для вычисления значения целевой функции  F(X). Это значение можно определить при помощи встроенной математической функции  СУММПРОИЗВ (1-ый массив; 2-ой массив).  Первый массив состоит из коэффициентов целевой функции                 (ячейки L2:L13 ), второй массив – из значений переменных  (ячейки В2:В13 ). Таким образом, в ячейке А16  получаем формулу  =СУММПРОИЗВ(L2:L13;B2:B13).

В ячейку  С16 вводим формулу  СУММПРОИЗ (С2:С13; $В$2:$В$13), т.е. в ячейке  С16  формируется  значение левой части первого уравнения системы ограничений.
Копируем формулу из ячейки  С16  в ячейки  D16:I16 ( через  +  в правом нижнем углу ячейки  С16 ).

в)  в меню  СЕРВИС  выбираем команду  ПОИСК  РЕШЕНИЯ. Заполняем поля появившегося диалогового окна:   адрес ячейки целевой функции (целевая ячейка) ( у нас – А16 ),  тип оптимизации  ( у нас  -  минимальному значению ), адреса ячеек с переменными  ( изменяя ячейки)( у нас - $B$2:$B$13 ), ограничения  ( у нас:

$B$2:$B$13=целое

$B$2:$B$13

$C$16=$C$14

$D$16=$D$14

$E$16=$E$14

$F$16=$F$14

$G$16=$G$14

$H$16=$H$14

$I$16=$I$14    ).

Для создания системы ограничений используется кнопка ДОБАВИТЬ. При её нажатии появляется окно, в поля которого вводятся ссылки на ячейки  и ограничения, накладываемые на переменные в задаче. Сформированную систему ограничений в дальнейшем можно редактировать с помощью кнопок  ИЗМЕНИТЬ  и  УДАЛИТЬ.

         Далее следует нажать кнопку ВЫПОЛНИТЬ, после чего будет выполнена программа  ПОИСК РЕШЕНИЯ, по результатам которой будет выведено сообщение о найденном решении. Из окна-сообщения видно, что полученные результаты можно сохранить, изменив содержимое ячеек с переменными; можно восстановить исходные значения.

Результаты :

 

   A

 B

C

D

E

F

G

H

I

J

K

L

1

 

Значения переменных

 

 

 

 

 

 

 

 

 

Тарифы

2

X11

0

1

0

0

1

0

0

0

 

 

8

3

X12

60

1

0

0

0

1

0

0

 

 

1

4

X13

0

1

0

0

0

0

1

0

 

 

9

5

X14

50

1

0

0

0

0

0

1

 

 

7

6

X21

20

0

1

0

1

0

0

0

 

 

4

7

X22

0

0

1

0

0

1

0

0

 

 

6

8

X23

170

0

1

0

0

0

1

0

 

 

2

9

X24

0

0

1

0

0

0

0

1

 

 

12

10

X31

60

0

0

1

1

0

0

0

 

 

3

11

X32

0

0

0

1

0

1

0

0

 

 

5

12

X33

0

0

0

1

0

0

1

0

 

 

8

13

X34

30

0

0

1

0

0

0

1

 

 

9

14

 

 

110

190

90

80

60

170

80

 

 

 

15

F(x)

 

 

 

 

 

 

 

 

 

 

 

16

1280

 

110

190

90

80

60

170

80

 

 

 

 

Выводы:

Минимальные затраты на транспортировку контейнеров  в сумме 1280 денежных единиц будут в том случае , когда  значения переменных :  X12=60, X14=50, X21=20, X23=170, X31=60, X34=30,  остальные  Xij=0. Таким образом, из узла А1 следует отправить 60 контейнеров в узел В2 , из узла А1 следует отправить 50 контейнеров в узел В4 ,из узла А2 следует отправить 20 контейнеров в узел В1 , из узла А2 следует отправить 170 контейнеров в узел В3 , из узла А3 следует отправить 60 контейнеров в узел В1 , из узла А3 следует отправить 30 контейнеров в узел В4 .

 

 

3.2.2      Самостоятельная работа

n  предприятий данного экономического района для производства продукции используют  некоторое сырьё.  Потребности в сырье каждого из предприятий известны (b1,b2,…,bn). Сырьё сосредоточено в m местах его получения, а запасы соответственно равны (a1,a2,…,am). На каждое из предприятий сырьё может завозиться из любого пункта его получения. Тарифы перевозок являются известными величинами и задаются матрицей C. Определить оптимальный план перевозок.

                                                Варианты:

 

1.a=(160,140,170);         2.  a=(180,350,20) ;                3.  a=(320,280,270,350);

   b=(120,50,190,110).        b=(110,90,120,80,150).           b=(450,370,400).                          

 

                                          

 

4. a=(200,270,130);          5.  a=(160,60,140);               6.  a=(220,280,270,250);

    b=(120,80,240,160).          b=(120,40,60,80,60).            b=(350,370,300).                    

    

                                            

 

7. a=(210,260,130);          8.  a=(170,60,140);                  9.  a=(420,280,270,150);

    b=(110,80,230,160).          b=(120,40,60,90,60).               b=(550,370,200).                   

 

                                              

 

10. a=(200,280,130);         11. a=(160,70,140);               12.  a=(220,270,270,250);

      b=(120,90,240,160).          b=(120,50,60,80,60).              b=(350,360,300).                    

    

                                            

 

 

 

 

 

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

 

Прямая и двойственная задачи линейного программирования

 

Цель работы:  Научиться составлять  двойственную задачу,  решать прямую и двойственную задачу средствами EXCEL ,давать экономическую интерпретацию решения двойственной задачи.

 

4.1  Теоретические сведения

 

Каждой задаче линейного программирования можно определённым образом сопоставить некоторую другую задачу, называемую двойственной по отношению к исходной (прямой) задаче.

Пусть прямая задача состоит в нахождении максимального значения функции

при ограничениях:

    

         Двойственная задача по отношению к прямой формулируется следующим образом: найти минимальное значение функции

при ограничениях

    

Правила составления двойственной задачи:

 

а)  целевая функция прямой задачи задаётся на максимум (минимум), а целевая функция двойственной – на минимум (максимум);

б)   матрица

        ,

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

    

из коэффициентов ограничений двойственной задачи  получаются друг из друга транспонированием;

в) число переменных в двойственной задаче равно числу ограничений прямой задачи, а число ограничений двойственной задачи равно числу переменных прямой задачи;

г) коэффициентами при неизвестных в целевой функции двойственной задачи являются свободные члены прямой задачи, а правыми частями ограничений двойственной задачи являются коэффициенты при неизвестных в целевой функции прямой задачи;

д) если переменная   исходной задачи может принимать только лишь положительные значения, то j – е условие в системе ограничений двойственной задачи является неравенством вида «». Если же переменная  может принимать как положительные, так и отрицательные значения, то j – е соотношение в ограничениях двойственной задачи является уравнением. Аналогичные связи имеют место между ограничениями исходной задачи и переменными двойственной задачи. Если i – е соотношение в ограничениях прямой задачи является неравенством, то i – я  переменная двойственной задачи  . В противном случае переменная может принимать как положительные, так и отрицательные значения.

 

4.2 Практическая часть

 

4.2.1 Контрольный пример - Составить двойственную задачу по отношению к задаче, состоящей в максимизации линейной функции  

              
               при ограничениях:

              

                  

Найти решение прямой и двойственной задачи.

 

Решение:

Умножим последнее неравенство на  -1, чтобы смысл всех неравенств соответствовал функции цели F (max). Теперь прямая задача состоит в максимизации линейной функции  

              

               при ограничениях:

              

                  

Составим матрицу из коэффициентов исходной (прямой) задачи:

Матрица из коэффициентов двойственной задачи получается транспонированием матрицы  А:

Таким образом, двойственная задача состоит в нахождении минимума линейной функции

   

при ограничениях:

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

Решив прямую задачу в EXCEL с помощью средства « ПОИСК  РЕШЕНИЯ »  из меню  «СЕРВИС», находим, что максимальное значение целевой функции равно  68  и достигается при значениях переменных: X1=0; X2=0; X3=5.5; X4=35.

Решив двойственную задачу в EXCEL с помощью средства « ПОИСК  РЕШЕНИЯ »  из меню  «СЕРВИС», находим, что минимальное  значение целевой функции   равно  68  и достигается при значениях переменных: Y1=1; Y2=2; Y3=0.

Выводы:

а) так как в оптимальном плане двойственной задачи  Y1 >0 , Y2 >0 , то это означает, что при оптимальном решении прямой задачи первое и второе ограничения прямой задачи – равенства.  Так как     =0, то третье ограничение прямой задачи при оптимальном решении – строгое неравенство;

б) при увеличении правой части первого уравнения прямой задачи на 1 (вместо 24 – 25) получим новый оптимальный план прямой задачи, при котором целевая функция возрастает на 1 (Y1=1) и станет равна 69.

Аналогично, при увеличении правой части второго уравнения прямой задачи на 1 (вместо 22 – 23) получим новый оптимальный план прямой задачи, при котором целевая функция возрастает на 2 (Y2=2) и станет равной  70;

в) так как  Y3=0, то при увеличении правой части третьего уравнения  на 1 (вместо -10   -9)  оптимальный план прямой задачи не меняется, при этом значение целевой функции  остаётся равным  68.

 

         4.2.2     Самостоятельная работа

                                

Составить двойственные задачи по отношению к заданиям 1-2  и  найти решения прямых и двойственных задач.

 

Задание 1.

 

Задание 2.

 

 

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

 

Прямая и двойственная задачи рациональной загрузки оборудования

 

         Цель работы: Научиться составлять математические модели прямой и двойственной задачи, находить решение  в EXCEL  с помощью  средства « ПОИСК  РЕШЕНИЯ »  из меню  «СЕРВИС», делать экономический анализ решения.

 

5.1   Общая постановка задачи

Предприятие связи за время планового периода Т  условных единиц должно выполнить план производства продукции двух видов Р1  и  Р2 . Плановый объём продукции Р1  составляет N1     ,   Р2  -  N2     условных единиц.

         Для производства продукции каждого вида использовано оборудование групп  А1  и  А2      . Производительность оборудования этих групп различна и определяется величиной  aij    условных единиц, где i  - индекс, отмечающий вид оборудования, j  - вид продукции. Стоимость единицы времени работы оборудования при изготовлении одной единицы продукции составляет  cij     условных единиц (i=1,2; j=1,2).

5.1.1 Требуется сформировать оптимальный план непрерывной работы групп оборудования, при котором будет выполнен план выпуска продукции с минимальной себестоимостью и в заданный срок.

5.1.2      Сформировать двойственную задачу и решить её.

5.1.3      Провести экономический анализ решения двойственной задачи.

 

5.2   Методика решения задачи

 

Приведём  решение задачи рациональной загрузки оборудования  на конкретном примере.

Пусть T=8;  a11=3;   a12=6 ;  a21=8 ;  a22=4 ;       c11=1; c12=5; c21=2; c22=2 ;       N1=40;   N2=48 

Решение:

а) обозначим через

          - время работы по изготовлению одного изделия вида Р1   на оборудовании группы А1 ;

          - время работы по изготовлению одного изделия вида Р2   на оборудовании группы А1 ;

           - время работы по изготовлению одного изделия вида Р1   на оборудовании группы А2 ;

             - время работы по изготовлению одного изделия вида Р2   на оборудовании группы А2 ;

Тогда математическая модель основной (прямой) задачи формулируется  следующим образом:

     Найти минимум линейной  функции  

          
               при ограничениях:

              

             

Решим  данную  задачу в EXCEL с помощью средства «ПОИСК РЕШЕНИЯ »  из меню  «СЕРВИС». В результате  получим, что минимальное значение целевой функции равно  45,333  и достигается при значениях переменных: t1=2.667; t2=5.333; t3=4; t4=4.

Оптимальная загрузка оборудования такова:

Оборудование А1      должно производить продукцию Р1   в течение   2,667 единиц времени;

Оборудование А1      должно производить продукцию Р2   в течение   5,333 единиц времени;

Оборудование  А2     должно производить продукцию Р1   в течение   4 единиц времени;

Оборудование  А2     должно производить продукцию Р2   в течение   4 единиц времени

 При этом получится продукция с минимальной себестоимостью   = 45,333;

б) умножим последние два неравенства на  -1, чтобы смысл всех неравенств соответствовал функции цели F (min). Теперь прямая задача оптимальной загрузки оборудования состоит в минимизации линейной функции  

              

                при ограничениях:

              

                  

Составим матрицу из коэффициентов исходной (прямой) задачи:

          

Матрица из коэффициентов двойственной задачи получается транспонированием матрицы  А:

         

Таким образом, двойственная задача состоит в нахождении максимума линейной функции

             

при ограничениях:

                  

    

В двойственной задаче переменные   имеют следующий смысл:

 -  оценка стоимости плановых изделий  Р1;

 - оценка стоимости плановых изделий  Р2;

 -  оценка стоимости планового времени работы оборудования А1;

 -  оценка стоимости планового времени работы оборудования А2;

Решив двойственную задачу в EXCEL с помощью средства « ПОИСК  РЕШЕНИЯ »  из меню  «СЕРВИС», находим, что минимальное  значение целевой функции   равно  45,333  и достигается при значениях переменных:

=0,444;  =0,889;    =0,333   =1,555.

 

Экономический смысл результата

 

При уменьшении плана по Р1   на  1 изделие себестоимость продукции  Fmin уменьшится на 0,444.  При уменьшении плана по Р2   на  1 изделие себестоимость продукции  Fmin уменьшится на 0,889.  При увеличении планового времени работы на оборудовании А1  на  единицу   Fmin  уменьшится на 0,333. При увеличении планового времени работы на оборудовании А2  на единицу  Fmin  уменьшится на 1,555.

 

5.3 Самостоятельная работа

 

5.3.1 Составить прямую и двойственную задачи по своему варианту расчётно-графической работы.

5.3.2      Найти решение обеих задач через «ПОИСК РЕШЕНИЯ»  в «СЕРВИСе».

5.3.3      Провести экономический анализ решения двойственной задачи.

 

 

Список  литературы

 

1.     Губин Н.М., Добронравов А.С., Дорохов Б.С.  Экономико-математические методы и модели в планировании и управлении в отрасли связи. – М.: Радио и связь, 1993.-376с.

2.     Барсук В.А., Губин Н.М., Батый А.Р.  Экономико-математические методы и модели в планировании и  управлении в отрасли связи. – М.: Радио и связь, 1984. – 264с.

3.     Кузнецов Ю.Н., Кузубов В.И., Волощенко А.Б.  Математическое программирование: Учебное пособие для экономических специальностей вузов. – М:Высшая школа, 1980. – 352с.

4.     Акулич И.Л.  Математическое программирование в примерах и задачах. – М: Высшая школа, 1986. – 319с.

5.     Исследование операций в экономике / Под ред. Н.Ш. Кремера / – М., 1997/

6.     Терехов Л.Л.  Экономико-математические методы. – М.: Статистика, 1972. – 360с.

7.     Агапов Г.В., Колбанев М.О., Кузина Л.К.  Методы оптимизации в задачах организации и управления сетями и предприятиями связи. – Л., 1986.

8.     Велямов Т.Т.  Экономико-математические методы и модели: Конспект лекций для студентов специальности -07.10. – Алма-Ата: АЭИ, 1992 – 51с.

9.     Замков О.О., Толстопятенко А.В., Черемных Ю.Н.  Математические методы в экономике. –М.,1997.

10. Эдвард Джонс и Дерек Саттон.  Библия пользователя  Microsoft Office Professional для Windows 95. – Киев: Диалектика, 1996 -502с.

 

 

 

 

Содержание

 

Введение…………………………………………………………………………3

1 Лабораторная работа №1. Применение программных средств EXCEL
 для экономических    расчётов ..………………………………………………4

2 Лабораторная работа №2. Решение задач линейного программирования

средствами EXCEL ……………………………………………………………10

3 Лабораторная работа №3. Решение транспортной задачи средствами EXCEL………………………………………………………………….………13

4 Лабораторная работа №4. Прямая и двойственная задачи линейного

 программирования ………………………………………………………….. 18

5 Лабораторная работа №5. Прямая и двойственная задачи рациональной загрузки оборудования ………………………………………………………………………………...22

Список  литературы ……………………………………………………….….25