Решение оптимизационных задач средствами электронной таблицы Excel

Особенности использования электронной таблицы Microsoft Excel для решения оптимизационных задач. Выполнение команды "Поиск решения" в меню "Сервис". Запись ограничений через использование кнопки "Добавить". Сообщение о найденном решении на экране.

Рубрика Программирование, компьютеры и кибернетика
Вид лабораторная работа
Язык русский
Дата добавления 03.08.2011
Размер файла 4,5 M

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

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

Размещено на http://www.allbest.ru/

Министерство образования Республики Беларусь

Учреждение образования

Белорусский государственный университет информатики и радиоэлектроники

Инженерно-экономический факультет

Кафедра экономической информатики

Отчёт по лабораторной работе

Тема " РЕШЕНИЕ ОПТИМИЗАЦИОННЫХ ЗАДАЧ СРЕДСТВАМИ ЭЛЕКТРОННОЙ ТАБЛИЦЫ EXCEL "

Выполнил: студент гр.

Проверила: Верняховская В.В.

Минск, 2009

Цель работы:

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

Задание 1.

Собственные средства банка составляют 100 единиц. Банк получает прибыль, выдавая кредиты и покупая ценные бумаги. Доходность кредитов составляет 15%, ценных бумаг - 10%. Таким образом, годовая прибыль банка составляет:

F (x, y) =0,15x+0,1y

где x объем средств, выданных в виде кредитов, а y средства, затраченные на покупку ценных бумаг.

Используя "Поиск решения" найдём максимальную прибыль банка при условии ограничений:

Выполнение:

Создадим таблицу, как указано на рис.1.

рис. 1

Для ячейки E4 введём формулу: =СУММПРОИЗВ ($B$3: $C$3,B4: C4) и скопируем ее в ячейки E7, E8, E9. Выполним команду Сервис/Поиск решения… Заполним вызванное окно в соответствии с рис.2.

рис. 2

Ограничения записываются через использование кнопки Добавить, где вносятся обозначения ограничений задачи. рис.3

рис. 3

После заполнения окна Поиска решений… перейдём по кнопке Параметры в окно Параметры поиска решений (рис.4) и установим там флажки напротив полей: Линейная модель и Неотрицательные значения. Далее выполним команду ОК, и нажмем клавишу Выполнить в окне Поиска решений.

рис. 4

Т.к. все выполнено правильно, на экране появилось сообщение о найденном решении. рис.5

рис. 5

В результате выполнения задания появилось следующее решение данной оптимизационной задачи. рис.6

рис. 6

Задание 2.

Процесс изготовления двух видов (А и В) изделий заводом требует, во-первых последовательной обработки на токарных и фрезерных станках, и, во-вторых затрат двух видов сырья: стали и цветных металлов. Данные о потребности каждого ресурса на единицу выпускаемой продукции и общие запасы ресурсов приведены в таблице.

Прибыль от реализации единицы изделия А - 3 тыс. руб., а единицы Б - 8 тыс. руб.

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

Выполнение: производиться аналогично заданию 1.

Результат на рис.7

рис. 7

Задание 3.

Предприятие выпускает три вида продукции А, В и С. Реализация единицы продукции А даёт прибыль 9 руб., В - 10 руб. а С - 16 руб. Сбыт продукции обеспечен, т.е. её можно производить в любых количествах, но запасы сырья ограничены. В таблице приведены нормы расхода сырья на производство единицы продукции и запасы трёх видов необходимого сырья. Найти план выпуска продукции, при котором прибыль будет максимальна.

Выполнение: производиться аналогично заданию 1.

Результат на рис.8

рис. 8

Задание 4.

Предприятие располагает ресурсами сырья трёх видов: С1, С2 и С3. Используя это сырьё, оно выпускает четыре вида продукции: П1, П2, П3 и П4. В таблице указаны затраты каждого вида сырья на изготовление 1 тонны продукции каждого вида и объём ресурсов сырья. Прибыль, получаемая от реализации 1 тонны продукции равна: П1 - 48, П2 - 25, П3 - 56, П4 - 30. Определить ассортимент выпускаемой продукции, при котором прибыль будет максимальной, при условии, что продукции П2 необходимо выпустить не менее 8 т, продукции П4 не более 5 т, а продукции П1 и П3 в отношении 3: 1.

Выполнение: производиться аналогично заданию 1.

Результат на рис.9

рис. 9

Задание 5.

Месячный фонд зарплаты сотрудников больницы составляет 50000 руб. Штат больницы и коэффициенты в формуле для расчёта окладов сотрудников приведены в таблице. Оклады определяются по формуле: оклад=А*x+В, где x - оклад санитара. Определить оклады всех сотрудников.

Выполнение: производиться аналогично заданию 1, только здесь ЦФ стремится к опред. значению и изменяем ячейку С3. Все остальные рассчитываюстя по формуле Ax+B.

Результат на рис.10

рис. 10

Задание 6.

С трех баз надо перевезти грузы в два магазина. Количество груза (в штуках) на базах - в таблице:

База 1

База 2

База 3

18

75

31

Потребность магазинов в этих грузах в таблице:

Магазин 1

Магазин 2

База 1

17

6

База 2

12

13

База 3

9

8

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

Выполнение: Создадим таблицу, как указано на рис.11. Для ячеек введем следующие формулы:

рис. 11

Выполним команду Сервис/Поиск решения… Заполним вызванное окно в соответствии с рис.12.

рис. 12

После заполнения окна Поиска решений… перейдём по кнопке Параметры в окно Параметры поиска решений (рис.4) и установим там флажки напротив полей: Линейная модель и Неотрицательные значения. Далее выполним команду ОК, и нажмём клавишу Выполнить в окне Поиска решений. В результате выполнения задания появитлось следующее решение данной оптимизационной задачи рис.13.

электронная таблица поиск решение

рис. 13

Задание 7.

Три автобазы А1, А2 и А3 предоставляют бульдозеры для работы на четырёх объектах О1, О2, О3 и О4. Число бульдозеров на автобазах представлено в таблице:

А1

А2

А3

11

11

8

Количество бульдозеров, необходимых на объектах - в таблице:

О1

О2

О3

О4

5

9

9

7

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

О1

О2

О3

О4

А1

7

8

5

3

А2

2

4

5

9

А3

6

3

1

2

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

Выполнение: производиться аналогично заданию 6, найденное решение на рис.14.

рис. 14

Задание 8.

Известно, что в разных странах цены трудовых ресурсов, сырья и т.п. могут существенно различаться. Фирме необходимо разместить четыре заказа. Любой из этих заказов может быть размещен в любой из четырёх стран, но только целиком (т.е. нельзя один заказ разместить в двух странах). Нельзя размещать два заказа в одной стране. Стоимости выполнения заказов в каждой из четырёх стран приведены в таблице:

Заказ 1

Заказ 2

Заказ 3

Заказ 4

Страна 1

1

4

6

3

Страна 2

9

10

7

9

Страна 3

4

5

11

7

Страна 4

8

7

8

5

Найти оптимальный план размещения заказов.

Выполнение: производиться аналогично заданию 6, найденное решение на рис.15.

рис. 15

Выполнение дополнительного задания:

Обозначения:

n - Количество новых салфеток (ценой 25 центов);

k - Количество салфеток, отправленных в прачечную короля (2 дня стирки + цена 15 центов за штуку);

q - Количество салфеток, отправленных в прачечную королевы (3 дня стирки + цена 10 центов за штуку);

d - Количество грязных салфеток, не отправляемых в стирку.

Для решения данной задачи была создана таблица 1, где в ячейки G4: G10, J4: J10, K4: K10 введены формулы (ограничения), так же в ячейку L4 формула целевой функции:

=25* (B4+B5+B6+B7+B8+B9+B10) +15* (C4+C5+C6+C7+C8) +10* (D4+D5+D6+D7)

Таблица 1

Заполнение диалогового окна поиск решения:

Решение задачи:

Вывод:

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

Размещено на Allbest.ru


Подобные документы

  • Построение и использование математических и алгоритмических моделей для решения линейных оптимизационных задач. Освоение основных приемов работы с инструментом "Поиск решения" среды Microsoft Excel. Ввод системы ограничений и условий оптимизации.

    лабораторная работа [354,7 K], добавлен 21.07.2012

  • Примеры решения математических и экономических задач, выполняемых с помощью средств электронной таблицы Excel и логических функций. Создание и форматирование таблиц. Создание разных баз данных с помощью системы Microsoft Access с использованием запроса.

    контрольная работа [88,7 K], добавлен 28.05.2009

  • Анализ метода линейного программирования для решения оптимизационных управленческих задач. Графический метод решения задачи линейного программирования. Проверка оптимального решения в среде MS Excel с использованием программной надстройки "Поиск решения".

    курсовая работа [2,2 M], добавлен 29.05.2015

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

    курсовая работа [668,2 K], добавлен 13.12.2012

  • Использование электронной таблицы как базы данных. Сортировка и фильтрация данных в Microsoft Excel 97. Сортировка - это упорядочение данных по возрастанию или по убыванию. При фильтрации базы отображаются только записи, обладающие нужными свойствами.

    реферат [6,6 K], добавлен 17.11.2002

  • Ознакомление с разнообразными надстройками, входящими в состав Microsoft Excel; особенности их использования. Примеры решения задач линейного программирования с помощью вспомогательных программ "Подбор параметра", "Поиск решения" и "Анализ данных".

    реферат [2,5 M], добавлен 25.04.2013

  • Изучение и укрепление на практике всех моментов графического метода решения задач линейного программирования о производстве журналов "Автомеханик" и "Инструмент". Построение математической модели. Решение задачи с помощью электронной таблицы Excel.

    курсовая работа [663,9 K], добавлен 10.06.2014

  • Общее понятие о Microsoft Excel. Главное назначение таблицы. Процесс составления таблицы в Excel, правила оформления. Основные способы выделения. Формулы, главные особенности их применения. Использование сводной таблицы в бухгалтерии и экономике.

    контрольная работа [526,0 K], добавлен 29.12.2012

  • Использование электронных таблиц Microsoft Excel в решении производственных задач. Определение инерционных характеристик главного вала горячештамповочного автомата. Обработка эксперимента по определению приведенного модуля объемной упругости жидкости.

    методичка [429,3 K], добавлен 06.06.2011

  • Использование информационных технологий для решения транспортных задач. Составление программ и решение задачи средствами Pascal10; алгоритм решения. Работа со средствами пакета Microsoft Excel18 и MathCad. Таблица исходных данных, построение диаграммы.

    курсовая работа [749,1 K], добавлен 13.08.2012

Работы в архивах красиво оформлены согласно требованиям ВУЗов и содержат рисунки, диаграммы, формулы и т.д.
PPT, PPTX и PDF-файлы представлены только в архивах.
Рекомендуем скачать работу.