|
|||||||||||||||||||||||||||||||||||||||||||||||||
АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомДругоеЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция |
Решение задач оптимизации в ExcelРассмотрим решение на примере задачи 1. Фирма производит два вида продукции: А и В. Прибыль на единицу продукции А и В составляет соответственно 20 и 30 рублей. Технология производства требует работы 3 машин. Время работы машин I, II, III в минутах, необходимое для производства единицы продукции каждого вида, приведено в таблице.
Ежедневный резерв времени работы машин равен соответственно 48, 32 и 16 часов в день. Определите ежедневные нормы выпуска продукции А и В, максимизирующие прибыль.
1. Открываем Excel. Нажмите на квадратик между строкой с буквами и столбцом с чиселками.
Теперь, когда все ячейки выделены поставьте 14 шрифт, полужирное выделение и ориентацию текста по центру. Это важно, поверьте. 2. Записываем условия задачи. Копируем таблицу из этого файла и вставляем в ячейку, скажем, А22. Полученную таблицу опять копируем, выделяем ячейку А1, жмем на ней правой кнопкой мыши и выбираем «специальную вставку». В открывшемся окошке, в нижнем право углу, будет чекбокс «транспонировать», ставим туда галочку и жмем ок. Дальше заполняем таблицу, как показано на рисунке. Коэффициенты прибыли – это прибыль на единицу продукции. В ресурсах пишем в ячейку D1 =48*60 – это норма работы первой машины в минутах, для D2 и D3 формулы аналогичные.
3. Продолжаем заполнять табличку. Выделяем ячейку Е1, жмем на значок функций в строке формул и выбираем функцию СУММПРОИЗВ. В качестве первого массива выбираем В2:С2, в качестве второго В8:С8. После этого нужно поставить значок $ во втором массиве, как показано на рисунке. Жмем ОК и растягиваем ячейку до Е4. В ячейке Е8 вычислите значение функции СУММПРОИЗВ как показано на рисунке. Жмем ОК.
4. Поиск решений. Выделяем ячейку Е8 и вызываем «поиск решений» через «сервис». Вводим следующие значения. Чтобы добавить ограничение, жмем кнопочку «добавить», в левом поле для ограничений выделяем Е2-Е4, знак оставляем меньше или равно, в правом поле – D2-D4, жмем ОК. Прежде, чем нажать «выполнить» зайдите в параметры и поставьте галочки напротив «линейная модель» и «неотрицательные значения», потом ОК. Теперь нажимаем выполнить. Выделите типы отчета, если это необходимо и нажмите ОК (если только у вас правильное решение, должно быть как на рисунке внизу). Раскрасьте все это, как-нибудь.
Транспортная задача.
1. Условия. Для начала, повторите пункт 1 задачи 1. Копируем на новый лист эту таблицу в ячейку А1, не транспонируя ее. Приведите ее в божеский вид, как на картинке. Скопируйте эту же таблицу из екселя на одну строку ниже. В ячейку Е10 вводим функцию СУММ, в поле число 1 вводим A10:D10, жмем ОК. Растягиваем Е4 до ячейки Е10. Теперь, в ячейке А15 так же вызываем функцию СУММ, в поле число 1 вводим A10:A14, жмем ОК и растягиваем вправо до D15.
2. Баланс. В ячейку G2 пишем «Произведено», в G3 – «Требуется». В H1 вызываем функцию СУММ, число 1 - E2:E6. Для Н2 аналогично, число 1 - A7:D7. В G3 пишем «Баланс», в Н3 пишем формулу =H1-H2. Должен получиться ноль. Если не получился, то значит сумма строки «Объемы потребления» отличается от суммы столбца «Объемы производства». В бонусной задаче, которую мы решали на лабораторной, меньше были «объемы производства». Чтобы это исправить, нужно еще одну строку в каждую таблицу (фиктивный производитель) и в получившейся пустой ячейке в столбце «Объемы производства» ввести такое число, чтобы баланс стал равен нулю. Дальше решение ничем от обычной задачи не отличается, только дополнительную строку (или столбец) нужно выделить каким-нибудь цветом.
3. Поиск решений. Для начал сформулируем целевую функцию. В ячейке Е18 вызываем функцию СУММПРОИЗВ и вводим аргументы, как показано на рисунке. Жмем ОК. В ячейке Е18 вызываем поиск решений. Вводим аргументы, как на рисунке. В параметрах опять отмечаем «линейная модель» и неотрицательные значения. Жмем выполнить. Ура! Раскрасьте все теперь это. Поиск по сайту: |
Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Студалл.Орг (0.005 сек.) |