Поиск

Полнотекстовый поиск:
Где искать:
везде
только в названии
только в тексте
Выводить:
описание
слова в тексте
только заголовок

Рекомендуем ознакомиться

'Документ'
График проведения методологических семинаров по предмету «Химия» для учителей города и близлежащих районов в рамках семинара «Актуальные проблемы преп...полностью>>
'Документ'
Кризис, испытываемый энергетикой России, вышел минувшей зимой за рамки экономических явлений, обернувшись для отдельных регионов гуманитарной катастро...полностью>>
'Документ'
Что стало итогом путешествия по Европе? . Какой двуединый принцип словесного творчества выдвинул Карамзин? 7. Кто выдвинул идею трех стилей в русском ...полностью>>
'Документ'
Чтобы реализовать аутентификацию и обеспечить конфиденциальность информации в незащищенных сетях (например, в Интернете), требуется привлечь средства ...полностью>>

Главная > Решение

Сохрани ссылку в одной из сетей:
Информация о документе
Дата добавления:
Размер:
Доступные форматы для скачивания:

Лабораторная работа "Использование средства Поиск решения"

Задание:

Решить в Excel все приведенные ниже задачи (каждую на отдельном листе) и сохранить решения в файле LAB4.xls на своем пользовательском диске .

Задача 1 1

Решение задачи линейного программирования с помощью EXCEL. 2

Задача 2 4

Задача планирования производства красок 4

Задача 3 5

Решение транспортной задачи с помощью средства Поиск решения 5

Задача 1

Задача распределения ресурсов.

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

Например:

Требуется определить, в каком количестве надо выпускать продукцию четырех типов Прод1, Прод2, Прод3, Прод4, для изготовления которой требуются ресурсы трех видов: трудовые, сырье, финансы. Количество ресурса каждого вида, необходимое для выпуска единицы продукции данного типа, называется нормой расхода. Нормы расхода, а также прибыль, получаемая от реализации единицы каждого типа продукции, приведена ниже.Составим математическую модель, для чего введем следущие обозначения:

xj - количество выпускаемой продукции j-го типа, j=1,4;

bi - количество располагаемого ресурса i-го вида, i=1,3;

aij - норма расхода i-го ресурса для выпуска единицы продукции j-го типа;

cj - прибыль, получаемая от реализации единицы продукции j-го типа.

Теперь приступим к составлению модели.

Для выпуска единицы Прод1 требуется 6 единиц сырья, значит, для выпуска всей продукции Прод1 требуется 6 х1 единиц сырья, где х1 - количество выпускаемой продукции Прод1. С учетом того, что для других видов продукции зависимости аналогичны, ограничение по сырью будет иметь вид:

1+5х2+4х3<=110.

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

F=60x1+70x2+120x3+130x4--> max

x1+x2+x3+x4<=16

6x1+5x2+4x3+3x4<=110

4x1+6x2+10x3+13x4<=100

xj>=0; j=1,4

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

1
. Сделать активной ячейку F6.

2. Мастер функцийМатематическиеСУММПРОИЗВнажмите кнопку Далее. На экране диалоговое окно




3. Введите зависимости для левых частей ограничений.

Работа в диалоговом окне Поиск решения.

1
. Сервис, Поиск решения...

2. Курсор в поле Установить целевую ячейку и введите адрес F6.

3. Введите направление целевой функции: Максимальному значению.

4. Курсор в поле Изменяя ячейки и введите адреса B3:E3

5. Нажмите кнопку Добавить... и введите граничные условия на переменные

6. После ввода ограничений, нажмите кнопку Выполнить. В результате вычислений в ячейках В3:Е3, будут отражены найденные числовые значения хi , а в ячейке F6 – значение целевой функции.

Т.О, видно, что в оптимальном решении Прод1=В3=10, Прод2=С3=0, Прод3=D3=6, Прод4=Е3=0.

При этом максимальная прибыль будет составлять F6=1320 , количество использованных ресурсов равно трудовых=F9=16, сырья=F10=84, финансов=F11=100.





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

Задача 2

Задача планирования производства красок

Для производства красок для наружных и внутренних работ используют два исходных продукта А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн, соответственно.

Суточный спрос на краску для внутренних работ никогда не превышает спроса на краску для наружных работ более чем на 1т.

Спрос на краску для внутренних работ не превышает 2т. в сутки.

Оптовые цены одной тонны красок равны: 3000 руб. для краски для наружных работ и 2000 руб. для краски для внутренних работ.

Какое количество краски каждого вида следует производить, чтобы доход от реализации был максимальным?

Расходы продуктов А и В на 1т. приведены в таблице:

исходный продукт

расход исходных продуктов на тонну краски

максимально возможный запас

для внутренних работ

х1

для наружных работ

х2

А

2

1

6

В

1

2

8

х1 - суточный объем производства краски для внутренних работ

х2 - суточный объем производства краски для наружных работ

f -суммарная суточная прибыль от производства обоих видов красок (целевая функция)

f = 3000х1+2000х2

Определить при каких допустимых значениях х1 и х2 значение f - максимальное

Ограничения:

1+ x2<= 6

х1+ 2x2 <= 8

x1 - х2 <= 1

x1 <= 2

x1 >= 0

x2 >= 0

Решение задачи в Excel

А

В

С

1

Переменные

2

х2

х1

3

4

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

max

=3000*А3+2000*В3

5

Ограничения

6

=2*А3+В3

<=

6

7

=А3+2*В3

<=

8

8

=А3-В3

<=

1

9

=А3

<=

2

Выполните: Cервис, Поиск решения

Целевая ячейка С4

Установить : Максимальному значению

Изменяемые ячейки: А3:В3

Ограничения:

А6:А9<=С6:С9

А3:В3>=0

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

Полученное решение:

А

В

С

1

Переменные

2

х1

х2

3

3,3333

1,3333

4

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

max

12666,67

5

Ограничения:

6

6

7

8

8

-2

9

1,3333

Вывод: оптимальным является производство 3,3 т. краски для наружных работ и 1,3 т. краски для внутренних работ в сутки. Этот объем принесет прибыль 12,7 тыс. руб.

Задача 3

Решение транспортной задачи с помощью средства Поиск решения

Фирма имеет четыре фабрики: А, В, С, D и пять центров распределения ее товаров: №1, №2, №3, №4, №5.

Производственные возможности фабрик соответственно составляют:

А – 200, В – 150, С – 225, D – 175 единиц продукции ежедневно.

Потребности центров распределения соответственно составляют:

№1 – 100, №2 – 200, №3 – 50, №4 – 250, №5 – 150 единиц продукции ежедневно.

Хранение на фабрике единицы продукции, не поставленной в центр распределения, составляет $0,75 в день.

Штраф за просроченную поставку единицы продукции, заказанной потребителем в центре распределения, но там не находящейся, равен $2,5 в день.

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

№1

№2

№3

№4

№5

A

1,5

2

1,75

2,25

2,25

B

2,5

2

1,75

1

1,5

C

2

1,5

1,5

1,75

1,75

D

2

0,5

1,75

1,75

1,75

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

Модель рассматриваемой задачи сбалансирована (суммарный объем произведенной продукции равен суммарному объему потребностей в ней), значит не нужно учитывать издержки, связанные как со складированием, так и с недопоставками продукции. В противном случае в модель следует ввести:

  • В случае перепроизводства – фиктивный пункт распределения, стоимость перевозок единицы продукции, в который полагается равной стоимости складирования, а объемы перевозок – объемам складирования излишков продукции на фабриках.

  • В случае дефицита – фиктивную фабрику, стоимость перевозок единицы продукции с которой полагается равной стоимости штрафов за недопоставку продукции, а объемы перевозок – объемам недопоставок продукции в пункты распределения.

xij – объем перевозок с i-й фабрики в j-й центр распределения.

cij – стоимость перевозки единицы продукции с i-й фабрики в j-й центр распределения.

аi объем производства на i-й фабрике.

вj – спрос в j-м центре распределения.

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

Ограничения:

x

ij 0 , i [1;4], j [1;5]

Механизм решения задачи в Excel с использованием средства Поиск решения

  1. В ячейки А1:Е4 введите стоимости перевозок.

  2. А6:Е9 – отведите под значения неизвестных (объемы перевозок).

  3. В ячейки G6:G9 введите объемы производства на фабриках.

  4. В А11:Е11 – потребность в продукции в пунктах распределения.

  5. В ячейку F10 – введите целевую функцию

  6. В А10:Е10 –введите формулы, определяющие объем продукции, ввозимой в центры распределения

  7. В F6: F9 – формулы, вычисляющие объем продукции, вывозимой с фабрик.

A

B

C

D

E

F

G

1

1,5

2

1,75

2,25

2,25

2

2,5

2

1,75

1

1,5

3

2

1,5

1,5

1,75

1,75

4

2

0,5

1,75

1,75

1,75

5

6

=СУММ(A6:E6)

200

7

=СУММ(A7:E7)

150

8

=СУММ(A8:E8)

225

9

=СУММ(A9:E9)

175

10

=СУММ(A6:A9)

=СУММ(B6:B9)

=СУММ(C6:C9)

=СУММ(D6:D9)

=СУММ(E6:E9)

=СУММПРОИЗВ(A1:E4;A6:E9)

11

100

200

50

250

150

  1. СервисПоиск решения

  2. В окне диалога Поиск решения:
    Установить целевую ячейку $F$10
    Равной минимальному значению
    Изменяя ячейки: $А$6:$E$9
    Ограничения:
    $А$10:$E$10=$A$11:$E$11
    $А$6:$E$9>=0
    $F$6:$F$9=$G$6:$G$9

  3. Щелкните на кнопке Параметры…и установите флажок Линейная модель

  4. Нажмите кнопку Выполнить

  5. Оптимальное решение транспортной задачи будет отражено в диапазоне А6:Е9

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

А

В

С

D

Е

1

2

7

7

6

20

2

1

1

1

2

50

3

5

5

3

1

10

4

2

8

1

4

20

5

3

2

1

5

17

6

40

30

20

20

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



Похожие документы:

  1. Решение задачи линейного программирования в ms

    Решение
    ... Excel-2003 при решении задач линейного программирования. Приобретение навыков решения задач линейного программирования. Общая формулировка задачи ЛП: найти неотрицательное решение X системы линейных ...
  2. Решение задачи линейного программирования (2)

    Решение
    Решение задачи линейного программирования Решить задачу линейного программирования с помощью программы «Поиск решения» в MS Excel. Формулировка задачи: Рацион ... значение. Ход решения задачи: Для решения задачи на ПК с использованием Excel необходимо: ...
  3. Задачи линейного программирования. Графический метод решения задач линейного программирования

    Решение
    ... Microsoft Excel. Решение задач выпуклого программирования при помощи линейной аппроксимации. Приближённое решение задач математического программирования методом сепарабельного программирования. Экономические задачи, решаемые с помощью ...
  4. Инструкция по использованию microsoft Excel для решения задач лп [5] 5 3 Одноиндексные задачи лп 6 > 3 Ввод исходных данных 6 > 3 Решение задачи 13

    Инструкция
    ... . 1. ЛАБОРАТОРНАЯ РАБОТА №1 “РЕШЕНИЕ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ С ИСПОЛЬЗОВАНИЕМ Microsoft Excel” 1.1. ЦЕЛЬ РАБОТЫ Приобретение навыков решения задач линейного программирования (ЛП) в табличном ...
  5. Некоторые понятия линейного программирования

    Документ
    ... мы приведем решение этой задачи с помощью программы Tora. рассмотрим реализацию задачи линейного программирования в ... задачи с помощью Microsoft Excel. 1. Осуществляем ввод данных в таблицу Excel (рис. 1). Рис. 1. Заполнение листа для решения задачи ...

Другие похожие документы..