Задания к лабораторной работе

Задание 1. Решите следующую транспортную задачу.

На трех складах A, B, C сосредоточен однородный товар в А – 42 т, в В – 36 т, в С – 40 т. Этот товар необходимо перевезти в четыре пункта назначения P, Q, R, S. Потребность пунктов: P – 26 т, Q – 41 т, R – 25 т, S – 31 т. Стоимости перевозок 1 т. груза из i-го пункта отправления в j-й пункт назначения заданы матрицей.

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

Решение:

1. В Excel создайте новую книгу. Сохраните ее, используя имя следующего формата: «Ваша фамилия, Лабораторная работа №2, Вариант№_». Переименуйте первый лист книги в «Задание№1».

2. Для решения этой задачи необходимо на лист «Задание№1» ввести условие задачи. Для этого необходимо оформить таблицу (или несколько таблиц) в которой отражена следующая информация: матрица стоимости перевозок, наличие товаров на складах, потребность в товарах в пунктах приёма. К примеру, таблица с условиями задачи может выглядеть так:

Здесь в центре, в ячейках В3:D6 содержится матрица стоимости перевозок, в ячейках Е3:Е6 отображены потребности пунктов приёма, а в ячейках В7:D7 отображена информация о наличии товаров на складе.

3. Далее необходимо оформить таблицу, где будет размещаться решение. В этой таблице должны быть следующие области:

· изменяемые ячейки (в виде матрицы перевозок) в которых будет сохраняться результат поиска решения, т.е. план перевозок (сколько тонн товара перевезено из каждого склада в каждый пункт) ячейки В11:D15;

· ячейки в которых отображается сколько фактически было взято товаров со складов В15:D15;

· ячейки где отображается информация сколько тонн товара было фактически получено пунктами Е11:Е14;

· вспомогательные ячейки В16:D16, в которых подсчитываются расходы на вывоз товаров со складов (можно было использовать ячейки F11:F14 для подсчета расходов на транспортировку к пунктам);

· целевая ячейка (В18), в которой будет расположена целевая функция.

4. Далее необходимо ввести в Е11:Е14 формулы, которые подсчитывают, сколько товара фактически получено пунктами. Для этого выделите ячейку Е11, щелкните на кнопке - автосуммирование, выделите ячейки B11:D11 и нажмите клавишу Enter. Затем скопируйте полученную формулу =CУММ(B11:D11) в ячейки Е12, Е13 и Е14 (можно использовать маркер заполнения).

5. Теперь введите в В15:D15 формулы, которые подсчитывают сколько фактически было взято товаров со складов. Для этого в ячейку В15 введите =CУММ(В11:В14) и затем скопируйте эту формулу в другие ячейки диапазона.

6. Далее в ячейки В16:D16 необходимо ввести формулы подсчитывающие фактические затраты на вывоз из товаров из складов. Для этого необходимо использовать данные двух матриц: В11:D15 (где будет содержаться информация о перевозках от складов к пунктам) и В3:D6 (где содержатся расценки на перевозки). Для вычисления затрат будем использовать функцию СУММПРОИЗВ, которая перемножает соответствующие элементы заданных массивов и вычисляет сумму этих произведений. Итак, в ячейку В16 введите следующую формулу: =СУММПРОИЗВ(B3:B6;B11:B14) и затем скопируйте эту формулу в ячейки С16 и D16.

7. Осталось в целевую ячейку (В18) ввести формулу =СУММ(B16:D16), которая подсчитывает все затраты на перевозки. (Можно было обойтись без вспомогательных ячеек В16:D16, а в ячейке В18 ввести достаточно громоздкую формулу: =СУММПРОИЗВ(B3:B6;B11:B14)+ СУММПРОИЗВ(С3:С6;С11:С14)+ СУММПРОИЗВ(D3:D6;D11:D14)).

8. Теперь необходимо использовать надстройку Поиск решения. Для этого выберите в меню пункт Сервис/ Поиск ре­шения.

9. В открывшемся окне (рис. 2) установите целевую ячейку. Для указания целевой ячейки, необходимо ввести «вручную» её абсолютный адрес ($B$18) в поле Установить целевую ячейку или выбрать адрес ячейки щелкнув на ней мышкой (предварительно установив курсор в вышеописанное поле).

10. Затем переключатель Равной: установите в положение минимальному значению.

11. В поле изменяя значения введите адрес диапазона изменяемых ячеек $B$11:$D$14.

12. Теперь необходимо ввести ограничения. Первое ограничение указывает, на то, что количество перевезенного товара с какого-либо склада в какой-либо пункт величина положительная или равна 0. Для ввода этого ограничения щелкните на кнопку Добавить. В появившемся окне (рис. 3) в поле Ссылка на ячейку выберите адрес ячеек $B$11:$D$14. Далее выберите знак >= и в поле Ограничения ввести 0 и щелкнуть на кнопке Добавить.

13. Далее введите следующее ограничение, которое указывает на то, что весь товар со складов должен быть увезен. Для этого в поле Ссылка на ячейку внесите $B$7:$D$7, выберите знак равенства, а в поле Ограничения – $B$15:$D$15 и щелкните на кнопке Добавить.

14. Поскольку у нас товара на складах меньше, чем потребность в них в пунктах (118<123), то необходимо ввести (по аналогии с пунктом 13) следующее условие $E$3:$E$6 >= $E$11:$E$14.

15. Так как больше ограничений не будет, то щелкнем на кнопке Отмена. При этом снова будет доступно окно Поиск решения. Если все сделано правильно нажмите на кнопку Выполнить. Через время откроется окно Результаты поиска решения, в котором будет указано, найдено оптимальное условие или нет. В этом окне активизируйте Сохранить найденное решение и щёлкните на кнопке ОК.

16. Искомое решение – матрица, которая показывает количество перевезенного товара с какого-либо склада в какой-либо пункт находится в изменяемых ячейках. Кроме того, будет подсчитано значение целевой функции (затраты на все перевозки) в ячейке В18. Запишите значение целевой функции и полученное решение в виде таблицы в файл отчета.

Задание 2. Решите задачу на оптимальное распределение ресурсов

Требуется получить 1000 т автомобильного бензина, чтобы октановое число было не ниже 76, а содержание серы - не более 0,3%. Для смешивания используются 4 компонента, которые имеют разные октановые числа, содержание серы и стоимость. Характеристики компонентов даны в следующей таблице:

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

Решение:

1. В своей рабочей книге. Переименуйте второй лист книги в «Задание№2».

2. Для решения этой задачи необходимо на лист «Задание№2» ввести условие задачи. Для этого необходимо оформить таблицу (или несколько таблиц) в которой отражена следующая информация: количество бензина которое необходимо получить, характеристики искомого бензина, матрица характеристик компонентов, используемых для получения бензина. Таблица с условиями задачи может выглядеть так:

3. Далее необходимо оформить таблицу, где будет размещаться решение. В этой таблице должны быть следующие области:

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

· ячейки В11:Е11, в которых отображается доля октанового числа, которую вносит в общую смесь, именно этот компонент;

· ячейки В12:Е12, в которых отображается доля серы, которую вносит в общую смесь, именно этот компонент;

· ячейки В13:Е13, в которых подсчитываются стоимость компонентов;

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

· целевая ячейка (F13), в которой будет расположена целевая функция, показывающая затраты на приобретение компонентов.

4. Далее необходимо ввести в В11:Е11 формулы, которые подсчитывают, долю октанового числа, которую вносит в общую смесь, соответствующий компонент. Для этого в ячейку В11 введите следующую формулу =B$10/$B$3*B5, здесь отношение B$10/$B$3 показывает долю первого компонента во всей смеси бензина, а в ячейке B5 хранится октановое число этого компонента. Теперь скопируйте эту формулу в ячейки С11:Е11.

5. Скопировав формулу из ячейки В11 в ячейки В12:Е12, получим в соответствующих ячейках формулы для подсчета доли серы, которую вносит в общую смесь, соответствующий компонент.

6. Теперь в ячейки В13:Е13 введем формулы, которые подсчитывают стоимость компонентов. Для этого в ячейку В13 введите следующую формулу =B10*B7 и скопируйте в другие ячейки диапазона.

7. Далее в ячейки F10:F13 введите формулы которые подсчитывают соответственно количество тонн всех компонентов бензина, октановое число смеси, содержание смеси и расходы на приобретение компонентов. Для этого в ячейку F10 введите формулу =СУММ(B10:E10) и скопируйте её в другие ячейки диапазона.

8. Теперь необходимо использовать надстройку Поиск решения. Для этого выберите в меню пункт Сервис/ Поиск ре­шения. В открывшемся окне (рис. 2) установите целевую ячейку ($F$13).

9. Затем переключатель Равной: установите в положение минимальному значению.

10. В поле изменяя значения введите адрес диапазона изменяемых ячеек $B$10:$E$10.

11. Теперь необходимо ввести ограничения. Первое ограничение указывает, на то, что количество закупленных компонентов величина положительная или равна 0. Для ввода этого ограничения щелкните на кнопку Добавить. В появившемся окне (рис. 3) в поле Ссылка на ячейку выберите адрес ячеек $B$10:$Е$10. Далее выберите знак >= и в поле Ограничения ввести 0 и щелкнуть на кнопке Добавить.

12. Далее введите следующее ограничение, которое указывает на то, что необходимо изготовить определенное количество бензина из компонентов для этого в поле Ссылка на ячейку внесите $F$10, выберите знак равенства, а в поле Ограничения – $B$3 и щелкните на кнопке Добавить.

13. Теперь введем ограничение, которое показывает, что октановое число бензина не должно быть меньше 76. В поле Ссылка на ячейку внесите $F$11, выберите знак >=, а в поле Ограничения – $С$3 и щелкните на кнопке Добавить.

14. Затем введем ограничение, которое показывает, что количество серы в бензине не должно превышать 0,3%. В поле Ссылка на ячейку внесите $F$12, выберите знак <=, а в поле Ограничения – $D$3 и щелкните на кнопке Добавить.

15. Так как больше ограничений не будет, то щелкнем на кнопке Отмена. При этом снова будет доступно окно Поиск решения. Если все сделано правильно нажмите на кнопку Выполнить. Через время откроется окно Результаты поиска решения, в котором будет указано, найдено оптимальное условие или нет. В этом окне активизируйте Сохранить найденное решение и щёлкните на кнопке ОК.

16. Искомое решение (количество тонн закупленных компонентов) будет находиться в ячейках В10:Е10. Кроме того, будет подсчитано значение целевой функции (стоимость закупленных компонентов) в ячейке F13. Запишите значение целевой функции и полученное решение в виде таблицы в файл отчета.

Задание 3. Решите задачу на получение максимальной прибыли.

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

Решение:

1. В своей рабочей книге. Переименуйте третий лист книги в «Задание№3».

2. Для решения этой задачи необходимо на лист «Задание№3» ввести условие задачи. Для этого необходимо оформить таблицу, в которой отражена следующая информация: удельная прибыль от реализации каждого типа продукции, нормы расхода ресурсов на изготовления каждой продукции, наличие ресурсов. Таблица с условиями задачи может выглядеть так:

3. Далее необходимо оформить таблицу, где будет размещаться решение.

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

5. Далее необходимо ввести в В11:Е11 формулы, которые подсчитывают, прибыль от реализации того или иного вида продукции. Для этого в ячейку В11 введите следующую формулу =B$10*B3. Теперь скопируйте эту формулу в ячейки С11:Е11.

6. Скопировав формулу из ячейки В11 в ячейки В12:Е14, получим в соответствующих ячейках формулы для подсчета расходов различных ресурсов на изготовление данного количества продукции.

7. Далее в ячейку F11 (целевая ячейка) введите формулу, которая подсчитывает полную прибыль от реализации всей продукции =СУММ(B11:E11). Затем скопируйте эту формулу в ячейки F12:F14, где будет вычисляться общий расход ресурсов на изготовление продукции.

8. Теперь необходимо использовать надстройку Поиск решения. Для этого выберите в меню пункт Сервис/ Поиск ре­шения. В открывшемся окне (рис. 2) установите целевую ячейку ($F$11).

9. Затем переключатель Равной: установите в положение максимальному значению.

10. В поле изменяя значения введите адрес диапазона изменяемых ячеек $B$10:$E$10.

11. Теперь необходимо ввести ограничения. Первое ограничение указывает, на то, что количество изготавливаемой продукции величина положительная или равна 0. Для ввода этого ограничения щелкните на кнопку Добавить. В появившемся окне (рис. 3) в поле Ссылка на ячейку выберите адрес ячеек $B$10:$Е$10. Далее выберите знак >= и в поле Ограничения ввести 0 и щелкнуть на кнопке Добавить.

12. Далее введите следующие ограничения, которое указывает на то, что количество затраченного ресурса на изготовление продукции не может быть больше количества ресурсов, которое есть в наличии. Для этого необходимо в поле Ссылка на ячейку внесите $F$12, выберите знак <=, а в поле Ограничения – $F$4 и щелкните на кнопке Добавить. Далее вносим ограничения $F$13<=$F$5 и затем $F$14<=$F$6.

13. Так как больше ограничений не будет, то щелкнем на кнопке Отмена. При этом снова будет доступно окно Поиск решения. Если все сделано правильно нажмите на кнопку Выполнить. Через время откроется окно Результаты поиска решения, в котором будет указано, найдено оптимальное условие или нет. В этом окне активизируйте Сохранить найденное решение и щёлкните на кнопке ОК.

14. Искомое решение количество произведенной продукции будет находиться в ячейках В10:Е10. Кроме того, будет подсчитано значение целевой функции (прибыль от реализации всей продукции) в ячейке F11. Запишите значение целевой функции и полученное решение в виде таблицы в файл отчета.

Контрольные вопросы к защите

1. Какие задачи называются оптимизационными?

2. Назовите цели решения оптимизационных задач в экономике.

3. Какими свойствами обладают все оптимизационные задачи?

4. Что является математической моделью задачи оптимизации?

5. Что называется целевой функцией?

6. С помощью какого инструмента решаются задачи оптимизации в Excel?

7. Опишите этапы решения оптимизационных задач в Excel.

8. Что нужно сделать на этапе оформления задачи оптимизации в Excel?

9. Опишите порядок добавление ограничений?

10. Какие типы отчетов по результату поиска оптимального решения, можно создавать в Excel?

11. Перечислите основные параметры инструмента Поиск решения.

Поиск по сайту: