Поиск и принятие решений в Excel
Оценка 4.8

Поиск и принятие решений в Excel

Оценка 4.8
pdf
07.05.2020
Поиск и принятие решений в Excel
23. Поиск и принятие решений в Excel.pdf

              Лабораторная работа № 3_9. Поиск и принятие решений в

Excel.

Что осваивается и изучается?

Решение задачи определения оптимального плана и транспортной задачи при помощи надстройки «Поиск решения».

Задание 1. Задача об оптимальном ассортименте. Предприятие выпускает 2 вида продукции. Цена единицы 1-го вида продукции - 25 000, 2-го вида продукции – 50000. Для изготовления продукции используются три вида сырья, запасы которого 37, 57,6 и 7 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице.

 

Продукция

Запасы сырья

 

1-й вид продукции

2-й вид продукции

1,2

1,9

37

2,3

1,8

57,6

0,1

0,7

7

 

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

 

Выполнение.

1.  Такие задачи решаются при помощи инструмента Excel «Поиск решения». Для установки этого инструмента необходимо :

Главное меню: Сервис / Надстройки / Установить флажок «Поиск  решения» / OK.

После загрузки инструмента «Поиск решения» в меню Сервис появляется команда «Поиск решения». Выполнение этой команды начинается с вывода диалогового окна, в котором вводятся исходные данные задачи

 

2.  Математическая модель задачи.

Пусть продукция производится в количестве: 

1-й вид – x1 единиц, 2-й вид – x2 единиц.

Тогда стоимость произведенной продукции выражается целевой функцией f(x1,x2)=25000 x1+50000x2,

для которой необходимо найти максимум.

При этом следует учесть ограничения по запасам сырья: 1,2 x1+1,9 x2 37,

2,3 x1+1,8 x2 57,6, 0,1 x1+0,7 x2 7

и по смыслу задачи  x1, x2 должны быть неотрицательными и целыми:  x10,  x2 0.

 

3.  Ввод исходных данных в компьютер.

3.1.  Введем целевую функцию и ограничения.

Для переменных x1,x2 определим соответственно ячейки С2:D2 и зададим им начальные значения, равные нулю. Затем коэффициенты целевой функции и нормы расхода сырья расположим под неизвестными в ячейках С3:D3 и С6:D8  соответственно. Запасы сырья расположим справа от матрицы норм расхода в ячейках  G6:G8. В ячейке F2 вычислим значение целевой функции, а в ячейках F6:F8 - реальный расход сырья.

 

Ячейка

Формула

F2

= СУММПРОИЗВ(C2:D2;C3:D3)

F6

= СУММПРОИЗВ($C$2:$D$2;C6:D6)

F7

= СУММПРОИЗВ($C$2:$D$2;C7:D7)

F8

= СУММПРОИЗВ($C$2:$D$2;C8:D8)

 

3.2.  Задание параметров для диалогового окна «Поиск решения». Выполнить команду Сервис / Поиск  решения.

В диалоговом окне «Поиск  решения» нужно указать:

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

     цель вычислений (задать критерий для нахождения экстремального значение целевой функции);

     адреса ячеек, в которых находятся значения изменяемых переменных х1, х2;

     матрицу ограничений, для чего нажимается кнопка «Добавить»;

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

 

 

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

Ответ: 825000

 

 

2.   Сетевая транспортная задача

Задание 2.1.

Три поставщика одного и того же продукта располагают в планируемый период следующими запасами этого продукта: первый- 120 условных единиц, второй- 100 и третий 80 единиц. Этот продукт должен быть перевезен к трем потребителям, спросы которых соответственно равны 90, 90 и 120 условных единиц. Приведенная ниже таблица содержит показатели затрат, связанных с перевозкой продукта из i-го пункта отправления в j-й пункт потребления.

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

Поставщики

      Потребители и их спрос 

Запасы 

 

А 

Б 

В 

 

120 

II 

100 

III 

80 

Спрос 

90 

90 

120 

 

Математическая модель задачи выглядит следующим образом.

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

F(x)=7 x11+6 x12+4 x13+3 x21+8 x22+5 x23+2 x31+3 x32+7 x33 min,

Ограничения имеют вид: x11+x12+x13=120,   x21+x22+x23=100,   x31+x32+x33=80,     x11+x21+x31=90, x12+x22+x32=90,  x13+x23+x33=120, xij 0, i, j=.

Искомые значения xij находятся в блоке ячеек B4:D6. Адрес данного блока входит в поле ввода Изменяя ячейки в окне “Поиск решения” . Требования к ограничениям по спросу и запасам представлены соответственно в ячейках B7:D7 и E4:E6. Коэффициенты ЦФ, означающие затраты на доставку расположены в блоке ячеек B12:D14.

Формулы целевой функции и ограничений находятся соответственно в ячейке F8 и ячейках B8:D8 (ограничения по спросу), F4:F6 (ограничения по запасам) . Вид электронной таблицы в режиме отображения формул представлен на рис. 

 

 

Первая запись в группе Ограничения  представляет ограничения по нижней границе xij. Вторая и третья записи выражают ограничения по уровню спроса и запасов соответственно.

 

Окончательный вид электронной таблицы Excel, созданной для решения задачи. 

 

 

Задание 2.2.

На складах имеется груз, количество которого определяется в следующей таблице:

Склады

Склад 1

Склад 2

Склад 3

Наличие груза  на складе

18

75

31

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

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

Пункт 1

Пункт 2

Потребность груза 

45

79

Стоимость перевозок определяется таблицей:

 

Пункт 1

Пункт 2

Склад 1

17

6

Склад 2

12

13

Склад 3

9

8

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

Ответ: 1286.

Задание 3. Задача о смесях. Фирма «Корма»  имеет возможность  покупать 4 различных вида зерна (компонентов смеси) и изготавливать различные виды кормов. Разные зерновые культуры содержат разное количество питательных ингредиентов. Произведенный комбикорм должен удовлетворять некоторым минимальным  требованиям с точки зрения питательности. Требуется определить, какая из возможных смесей является  самой дешевой. Исходные данные приведены в следующей таблице

 

 

Единица веса

 

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

зерна 1

зерна 2

зерна 3

зерна 4

Ингредиент A

2

1

5

0,6

1,2

3              7

0,7           0

2              0,2

0,7           0,5

0,8           0,3

1

2,3

1

1

0

1250

450

900

350

600

Ингредиент B

Ингредиент C

Ингредиент D

Ингредиент E

Затраты в расчете на ед. веса (цена)

41

35

48

42

Минимизировать

 

Ответ: 21778.

 

Задание 4. Балансовые модели. Имеется трехотраслевая балансовая модель экономики с  матрицей ai,j коэффициентов затрат:

0,1   0,05   0,2

                                                                                                 

0,3   0        0,15

                                                                       0,2   0,4     0

Производственные мощности отраслей ограничивают возможности ее валового выпуска числами Mi = {300, 200. 500}. Определить оптимальный валовой выпуск всех отраслей Xi, максимизирующий  стоимость суммарного конечного продукта Yi, если задан вектор цен Ci на конечный продукт (2, 5, 1). Конечный продукт определяется формулой 

3

Yi  = Xi  - (ai, j X j)   i=1,2,3

j1

3

Целевая функция F(x1,x2,x3) = yi ci max

i1

Ограничения валового выпуска xi mi

 

Ответ: 909

 

Задание 4а. Решить эту же задачу, если накладываются следующие ограничения на валовой выпуск продукции и конечный продукт отраслей:

валовый выпуск : X1 : X3 = 2 : 1  , конечный продукт: Y2  <=100

 

Ответ: 907,5

 

Задание 4б. К данным задачи 4 заданы коэффициенты прямых затрат труда на выпуск продукции каждой отрасли. Определить максимально возможный выпуск конечного продукта в стоимостном выражении, если суммарные затраты труда не должны превышать заданного числа единиц.

 

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

Суммарные затраты труда

1-я отрасль

2-я отрасль

3-я отрасль

0,2

0,3

0,15

<= 70

 

Ответ: 789

Лабораторная работа № 3_9. Поиск и принятие решений в

Лабораторная работа № 3_9. Поиск и принятие решений в

Для переменных x 1 ,x 2 определим соответственно ячейки

Для переменных x 1 ,x 2 определим соответственно ячейки

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

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

II 3 8 5 100

II 3 8 5 100

Окончательный вид электронной таблицы

Окончательный вид электронной таблицы

Ответ: 1286. Задание 3. Задача о смесях

Ответ: 1286. Задание 3. Задача о смесях

Ответ: 909 Задание 4а.

Ответ: 909 Задание 4а.
Скачать файл