 |
АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомДругоеЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция
Решение задач линейного программирования в Excel
Для решения задач линейного программирования в Excel используют команду Поиск решения.
В офисах до 2003 включительно эта команда находится в меню Сервис.
Если ее там нет, нужно выполнить команду Сервис→Надстройки…
В офисе 2007 и выше команда Поиск решения находится в меню Данные.
Если ее там нет, нужно сделать следующее.
В Настройке панели быстрого доступа выбрать Другие команды.
Появится окно Параметры Excel. Здесь выбрать пункт Надстройки и щелкнуть по кнопке Перейти.
Далее установить Поиск решения.
ЗАДАЧА 1. При откорме животных каждое животное ежедневно должно получить не менее 60 ед питательного вещества А, и не менее 50 ед вещества В и не менее 12 ед вещества С. Указанные питательные вещества содержат три вида корма. Содержание единиц питательных веществ в 1 кг каждого из видов приведено в таблице:
| Питательные вещества | Количество единиц питательных веществ в 1кг корма вида | ||
| I | II | III | |
| А | |||
| В | |||
| С |
Составить дневной рацион, обеспечивающий получение необходимого количества питательных веществ при минимальных денежных затратах, если цена 1кг I вида составляет 9 грн., норма II вида – 12 грн, и норма III вида – 10 грн.
Постановка задачи.
Пусть 
– количество кормов каждого вида в кг.
Ограничения:
Функция цели:
Решение задачи:
Введем исходные данные в рабочий лист.
Пусть значения хранятся в ячейки B8:D8.
Значение функции цели F в ячейке
F8 = СУММПРОИЗВ(B8:D8;B6:D6).
Ограничения соответственно:
E3 = СУММПРОИЗВ($B$8:$D$8;B3:D3)
E4 = СУММПРОИЗВ($B$8:$D$8;B4:D4)
E5 = СУММПРОИЗВ($B$8:$D$8;B5:D5)
Таким образом, было задано условие исходной задачи линейного программирования.
Установим курсор в ячейку F8 и выполним команду Сервис®Поиск решения. Появится окно Поиск решения.
В поле Установить целевую функцию должен быть указан адрес ячейки, содержащей целевую функцию. В нашем случае это F8.
Поскольку мы ищем минимум функции, то установим опцию «равной минимальному значению».
В поле Изменяя ячейки необходимо указать адреса ячеек, в которых хранятся неизвестные (искомые) значения. В нашем случае это ячейки B8:D8.
Для добавления ограничений необходимо щелкнуть по кнопке Добавить, появится диалоговое окно Добавить ограничение.
В поле Ссылка на ячейку необходимо ввести адрес ячейки, где хранится ограничение, затем, выбрать знак и ввести значение ограничения в поле Ограничение.
Щелчок по кнопке Добавить позволит вводить очередное ограничение, находясь в окне Добавить ограничение.
Щелчок по кнопке OK означает ввод очередного ограничения и возврат к диалоговому окну Поиск решения.
В нашем случае окно Поиск решения будет иметь вид:
Щелчок по кнопке Выполнить начнет процесс решения задачи, завершится который появлением диалогового окна:
Щелчок по кнопке OK приведет к появлению в F8 значения целевой функции F, а в B8:D8 — значений переменных , при которых целевая функция достигает минимального значения.
Если задача не имеет решения или не верно были заданы исходные данные, в окне Результаты поиска решения может появиться сообщение о том, что решение не найдено.
Итак, назначение основных кнопок и окон диалогового окна Поиск решения:
Поле Установить целевую ячейку -определяет целевую ячейку, значение которой необходимо максимизировать или минимизировать, или сделать равным конкретному значению.
Опции «минимальному значению», «максимальному значению» и «значению», определяют, что необходимо сделать со значением целевой ячейки - максимизировать, минимизировать или приравнять конкретному значению.
Поле Изменяя ячейки определяет изменяемые ячейки. Изменяемая ячейка - это ячейка, которая может быть изменена в процессе поиска решения для достижения нужного результата в целевой ячейке с удовлетворением заданных ограничений.
Окно Ограничения перечисляет текущие ограничения в данной задаче. Ограничение это условие, которое должно удовлетворять решению. Ограничения перечисляются в виде ячеек или интервалов ячеек, обычно содержащих формулу, которая зависит от одной или нескольких изменяемых ячеек, значения которых должны попадать внутрь определенных границ или удовлетворять равенству.
Кнопки Добавить, Изменить, Удалить позволяют добавить, изменить, удалить ограничение.
Кнопка Выполнить запускает процесс решения определенной задачи.
Кнопка Закрыть закрывает окно диалога, не решая задачи.
Кнопка Сбросить очищает все текущие установки задачи и возвращает все параметры к их значениям по умолчанию.
С помощью решающего блока можно решить множество различных оптимизационных задач с ограничениями любого типа.
Поиск по сайту: