Линейная оптимизация в Excel (стр. 1 из 2)
Контрольная работа № 16 с «Линейная оптимизация в MSExcel»
Содержание
2. Решение задач линейной оптимизации средствами пакета MS Excel9
Характерной чертой современности является стремительный научно-технический прогресс, что требует от менеджеров и бизнесменов значительного повышения ответственности за качество принятия решений. Это основная причина, которая обусловливает необходимость научного принятия управленческих решений. Одним из направлений научно-технического прогресса стало математическое программирование, которое тесно связанное с практическими проблемами оптимального распределения ресурсов в различных отраслях производства и сферы услуг.
Поскольку различные аспекты оптимизации занимают очень важное место в бизнесе и деятельности современных организаций и предприятий, знание методов линейного программирования может помочь на практике тем людям, которые сталкиваются с такими задачами в своей повседневной роботе (менеджера, экономисты, финансисты, фермеры) или тем, кто просто интересуются этими вопросами.
Проблемы оптимизации присутствуют в самых различных процессах производства:
поставка сырья;
оптимальный выпуск продукции;
оптимальное управление запасами ;
оптимальное распределение ресурсов;
планирования инвестиций;
оптимальный рацион (смесь, сплав);
оптимальная замена оборудования и т. д
Решение задачи оптимизации состоит в поиске оптимального плана с использованием математических моделей и вычислительных методов, которые реализуются с помощью компьютеров и специальных программ-оптимизаторов.
Традиционный способ изучения экономико-математических методов заключается не только в определении их назначения и сути, но и в освоении техники реализации, причем, чтобы сделать доступной «ручную» реализацию, объем обрабатываемых данных приходится максимально сокращать, что, с одной стороны, часто удаляет построенную модель от реальной жизни, а с другой – снижает эффективность применения изучаемых методов.
Цель контрольной работы изучить и научиться пользоваться MSExcel, а именно, такими компонентами, как Вставка формул, Подбор параметра, Поиск решения. Все эти функции MSExcel облегчают задачу математикам, бухгалтерам и специалистам в различных областях.
Существует множество задач, решение которых может быть существенно облегченно с помощью инструмента Поиск решений в MSExcel. Но для этого следует начать с организации рабочего листа в соответствии с пригодной для поиска решений моделью, для чего нужно хорошо понимать взаимосвязи между переменными и формулами. Хотя постановка задачи обычно представляет основную сложность, время и усилия, затраченные на подготовку модели, вполне оправданы, поскольку полученные результаты могут уберечь от излишней траты ресурсов, при неправильном планирование, помогут увеличить прибыль за счет оптимального управление финансами или выявить наилучшее соотношение объемов производства, запасов и наименований продукции.
За своей сущностью задача оптимизации – это математическая модель определенного процесса производства продукции, его распределение, хранение, переработки, транспортирования, покупки или продажи, выполнение комплекса сервисных услуг и т.д. Это обычная математическая задача типа: Дано/Найти/При условии, но которая имеет множество возможных решений. Таким образом, задача оптимизации – задача выбора из множества возможных вариантов наилучшего, оптимального.
Решение такой задачи называют планом или программой, например, говорят – план производства или программа реконструкции. Другими словами это те неизвестные которые нам надо найти, например, количество продукции которое даст максимальную прибыль. Задача оптимизации – поиск экстремума, то есть, максимального или минимального значения определенной функции, которую называют целевой функцией, например, это может быть функция прибыли – выручка минус затраты. Так как и всё в мире ограничено (время, деньги, природные и человеческие ресурсы), в задачах оптимизации всегда есть определенные ограничения, например, количество метала, рабочих и станков на предприятии по изготовлению деталей.
Каждая задача оптимизации обязательно должна иметь три компоненты:
неизвестные (что ищем, то есть, план);
ограничение на неизвестные (область поиска);
целевая функция (цель, для которой ищем экстремум).
Линейное программирование – наиболее развитый раздел математического программирования, вычислительные средства которого позволяют находить глобальный оптимум линейной задачи оптимизации.
На счастье, большинство экономических управленческих задач хорошо описываются линейными моделями – именно этим обстоятельством объясняется успех практического использования линейных моделей для решения больших по размерам задач планирования и управления на уровне отдельных организаций, предприятий и даже отраслей производства.
Линейные модели используют такое прекрасное свойство линейных задач оптимизации, как линейные уравнения или неравенства из неизвестных и целевую функцию. Это означает, что область допустимых решений - выпуклой многоугольник, одна из вершин которого и есть оптимальное решение.
Именно этот эффективный математический результат лежит в основе симплекс-метода – для поиска оптимума нужно в определенном порядке пересмотреть небольшое количество вершин, используя простой и эффективный алгоритм последовательного улучшения значения целевой функции. Мощные и эффективные средства линейного программирования определенным образом используются и в целочисленном программировании для решения более сложных задач оптимизации.[1]
Постановка задачи оптимизации предполагает существование конкурирующих свойств процесса, например:
- количество продукции - расход сырья
- количество продукции - качество продукции
Выбор компромиссного варианта для указанных свойств и представляет собой процедуру решения оптимизационной задачи.
При постановке задачи оптимизации необходимо:
1. Наличие объекта оптимизации и цели оптимизации. При этом формулировка каждой задачи оптимизации должна требовать экстремального значения лишь одной величины, т.е. одновременно системе не должно приписываться два и более критериев оптимизации, т.к. практически всегда экстремум одного критерия не соответствует экстремуму другого.
2. Наличие ресурсов оптимизации, под которыми понимают возможность выбора значений некоторых параметров оптимизируемого объекта.
3. Возможность количественной оценки оптимизируемой величины, поскольку только в этом случае можно сравнивать эффекты от выбора тех или иных управляющих воздействий.
4. Учет ограничений.
Таким образом, задача оптимизации сводится к нахождению экстремума целевой функции.
В зависимости от своей постановки, любая из задач оптимизации может решаться различными методами, и наоборот – любой метод может применяться для решения многих задач. Методы оптимизации могут быть скалярными (оптимизация проводится по одному критерию), векторными (оптимизация проводится по многим критериям), поисковыми (включают методы регулярного и методы случайного поиска), аналитическими (методы дифференциального исчисления, методы вариационного исчисления и др.), вычислительными (основаны на математическом программировании, которое может быть линейным, нелинейным, дискретным, динамическим, стохастическим, эвристическим и т.д.), теоретико-вероятностными, теоретико-игровыми и др. Подвергаться оптимизации могут задачи как с ограничениями, так и без них.
Задача линейного программирования состоит в следующем: максимизировать (минимизировать) линейную функцию
, где 
при ограничениях
причем все
.Если число переменных системы ограничений и целевой функции в математической модели задачи равно 2, то её можно решить графически.
Нахождение решения задачи линейного программирования геометрическим методом включает следующие этапы:
1. Строят прямые, уравнения, которых получаются в результате замены в ограничениях знаков неравенств на знаки точных равенств.
2. Находят полуплоскости, определяемые каждым из ограничений задачи.
3. Находят многоугольник решений.
4. Строят вектор.
5. Строят прямую.
6. Строят параллельные прямые в направлении градиента или антиградиента, в результате чего находят точку, в которой функция принимает максимальное или минимальное значение, либо устанавливают неограниченность сверху (снизу) функции на допустимом множестве.
7. Определяют координаты точки максимума (минимума) функции и вычисляют значение целевой функции в этой точке.[4,c.188}
2. Решение задач линейной оптимизации средствами пакета MS Excel
С развитием средств вычислительной техники большинство разработанных методов решения задач поиска экстремума было алгоритмизировано, переведено на машинный язык и включено в состав специализированного или универсального программного обеспечения.
Электронные таблицы Excel фирмы Microsoft имеют встроенные средства решения задач поиска экстремума, оформленные в виде так называемой надстройки. Перед началом работы надо убедиться, что в составе сгенерированного на вашем компьютере пакета Excel требуемая надстройка установлена. Для этого выберите режим Сервис главного меню и убедитесь, что в открывшемся ниспадающем меню есть пункт Поиск решения (рис. 1). Если режим Поиск решения отсутствует, то выберите пункт меню Сервис/Надстройки и в открывшемся окне включите режим Поиск решения (рис. 2). Если в этом окне пункт Поиск решения отсутствует, произведите переустановку пакета Excel.