Решение задача распределения ресурсов с использованием надстройки поиск решения реферат

Обновлено: 04.07.2024

Максимизация прибыли универмага. Расчет прибылей от реализации средствами надстройки "Поиск решения" MS Excel. Распределение аудиторов по фирмам менеджером-координатором с минимизацией затрат времени на подготовку через целевую функцию в MS Excel.

Максимизация прибыли универмага

Большой универсальный магазин собирается заказать новую коллекцию костюмов для весеннего сезона. Решено заказать 4 типа костюмов. Три типа - костюмы широкого потребления (из полиэстеровых смесей, шерстяные, хлопковые). Четвертый тип - дорогие импортные модельные костюмы из различных тканей. Имеющийся у менеджеров магазина опыт и специальные исследования позволяют оценить средние затраты рабочего времени продавцов на продажу одного костюма каждого типа, объём затрат на рекламу и площади в расчете на один костюм каждого типа. Все эти данные, а также прибыль от продажи одного костюма представлены в таблице.

Предполагается, что весенний сезон будет длиться 90 дней. Магазин открыт 10 часов в день, 7 дней в неделю. Два продавца постоянно будут в отделе костюмов. Выделенная отделу костюмов площадь составляет прямоугольник 100 60 м 2 . Бюджет, выделенный на рекламу всех костюмов на весенний сезон, составляет 15000 у. е.

1. Сколько костюмов каждого типа надо закупить, чтобы максимизировать прибыль?

2. Допустим, что менеджмент магазина считает необходимым закупить не менее 200 костюмов каждого типа. Как это требование повлияет на прибыль магазина?

При ответе на следующие вопросы сохраните ограничение (2).

3. Изменится ли оптимальное решение, если прибыль от продажи одного полиэстерового костюма переоценена (недооценена) на 1 у. е, на 2 у. е?

4. Обоснуйте, будет ли каждое из предлагаемых решений полезно для магазина:

отдать в распоряжение отдела костюмов 400 м 2 от отдела женской спортивной одежды. Предполагается, что на этой площади магазин может получить прибыль всего лишь 750 у.е. за последующие 90 дней;

истратить дополнительно 400 у.е. на рекламу.

5. Если общее число закупленных костюмов не может превысить 5000 шт., то как такое ограничение повлияет на оптимальное решение?

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

Обозначим через Х₁ - количество костюмов из полиэстеровых смесей, Х₂ - количество костюмов из шерсти, Х₃ - количество костюмов из хлопка и Х₄ - количество импортных костюмов.

Целевая функция, определяющая прибыль универмага имеет вид:

Ограничение по времени: 90102=1800.

Решим задачу, с помощью табличного процессора MS Excel.

Значения переменных будем получать в ячейках B3:E3. Коэффициенты целевой функции вводим в ячейки B5:E5. Ячейка целевой функции - H5. Поместим в ней курсор, с помощью Мастера функций выберем Категорию Математические и оттуда введем СУММПРОИЗВ, в окне СУММПРОИЗВ указываем адреса массивов B3:E3 и B5:E5.

1). Для получения максимальной прибыли в размере 171500 у.е. следует закупить:

500 костюмов из полиэстеровых смесей,

2000 костюмов из шерсти,

2000 костюмов из хлопка и

не закупать дорогие импортные костюмы.

2). При условии, что следует закупить не менее 200 костюмов каждого типа прибыль составит 170330 у.е., что меньше на 1170 у.е. При этом следует закупать:

733 костюма из полиэстеровых смесей,

1335 костюма из шерсти,

2131 костюма из хлопка и

200 импортных костюмов.

3). Если прибыль от реализации полиэстерового костюма увеличить на 1 у.е. или 2 у.е., то оптимальное решение изменится. В первом случае, максимальная прибыль составит 171065 у.е. при следующих закупках: 734 полиэтеровых костюмов, 1333 шерстяных костюмов, 2133 хлопковых костюмов и 200 импортных костюмов. Во втором случае: максимальная прибыль составит 172225 у.е. при следующих закупках: 1725 костюмов из полиэтеровых тканей, 200 шерстяных, 2700 хлопковых и 200 импортных костюмов.

Если прибыль от реализации полиэстерового костюма уменьшить на 1 у.е., то оптимальное решение не изменится, уменьшится лишь получаемая прибыль и составит 169587 у.е. Если прибыль от реализации полиэстерового костюма уменьшить на 2 у.е., то оптимальное решение изменится. В этом случае общая прибыль составит 169397 у.е. при следующих закупках: 200 костюмов из полиэстера, 2401 шерстяных, 1065 хлопковых и 200 импортных костюма.

4). Ни увеличение площади, ни увеличение денег на рекламу к увеличению общей прибыли не приводит. При неизменных площадях магазин мог получить прибыль в размере 172250 у.е., при передаче части площади его прибыль составит 170897 у.е. Увеличение денег на рекламу приводит к уменьшению общей прибыли и составит 171056 у.е.

5). Условие о том, что общее число закупленных костюмов не превосходит 5000, выполняется при данных условиях задачи.

Задание 2.4

Распределение аудиторов по фирмам

Для учеников 1-11 классов и дошкольников
Бесплатные сертификаты учителям и участникам

Описание презентации по отдельным слайдам:

Технология использования надстройки Поиск решения показана на примере трех задач: 1. Производство конфет 2. Задача о рюкзаке 3. Транспортная задача

Таблица 1 Нормы расхода сырья СырьеНормы расхода сырьяЗапас сырья АВС Какао181512360 Сахар648192 Наполнитель533180 Прибыль91016

Технология работы: Запустите табличный процессор Excel. Заполните таблицу в соответствии с образцом:

Технология работы: В меню Сервис активизируйте команду Поиск решения Установите целевую ячейку: равной значению Укажите изменяемые ячейки: Опишите ограничения: В Параметрах укажите Линейность модели Запустите Поиск решения $C$6 максимальному $B$3:$B$5 $A$10 =0$B$4>=0$B$5>=0

Имеется 4 предмета, каждый из которых характеризуется весом и ценой. Нужно выбрать из них такие и столько, чтобы их общий вес не превышал 83, а суммарная цена была максимальной.

Запустите табличный процессор Excel. Заполните таблицу в соответствии с образцом:

В меню Сервис активизируйте команду Поиск решения Установите целевую ячейку: равной значению Укажите изменяемые ячейки: Опишите ограничения: В Параметрах укажите Линейность модели Запустите Поиск решения $G$7 $E$3:$E$6 максимальному $E$3:$E$6 >=0 количество предметов $E$3:$E$6 = целое предметы не разделяются $F$7 =0 $С$9:$F$9>=$C$10:$F$10

Результаты вычислений: Из решения видно, что минимальный грузооборот перевозок при соблюдении всех условий равен 3540 т.-км.

Спасибо за работу!

Краткое описание документа:

Задачи: отработать навык применения ЭТ при решении оптимизационных экономических задач, используя надстройку электронной таблицы Поиск решения;

Задача оптимизации – это математическая модель определенного процесса производства продукции, его распределение, хранение, переработки, транспортирования, покупки или продажи, выполнение комплекса сервисных услуг и т.д. Это обычная математическая задача типа: Дано/Найти/При условии, но которая имеет множество возможных решений. Таким образом, задача оптимизации – задача выбора из множества возможных вариантов наилучшего, оптимального.

2.2 Краткая теоретическая часть

Для этого в меню Сервис выберите команду Поиск решения. В поле установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу. Установите переключатель в положение максимального, минимального или конкретного значению;

В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.

В поле Ограничения введите все ограничения, накладываемые на поиск решения.

2.3. Индивидуальное задание

Решите задачу максимизации полезности по исходным данным. 12 Вариант: a=1/4 ; b=1/4 ; px=1; py=2; l=24.

2.4. Схема алгоритма выполнения индивидуального задания

Подбор параметра. Таблица подстановки.

3.2. Краткая теоретическая часть.

Таблица данных представляет собой диапазон ячеек, значения которых можно изменять для получения различных решений проблемы. Например, можно использовать функцию ПЛТ с разными суммами кредитов и процентными ставками для расчета возможного кредита на жилье или автомобиль. Эксперименты с изменяемыми значениями с целью получения разных результатов — часть дисциплины, известной как анализ данных. В зависимости от числа переменных и формул, которые нужно протестировать, можно создать таблицу данных с одной или двумя переменными.

Таблицы данных с одной переменной. Если нужно отображать как различные значения одной переменной в одну или несколько формул будут изменяться результаты этих формул с помощью таблицы данных с одной переменной. Например, можно использовать таблицу данных с одной переменной для просмотра различных процентной ставки влияют на ежемесячный платеж по ипотеке с помощью функции ПЛТ. Ввод значений переменных в один столбец или строку, а результаты, отображаются в смежных столбцах или строках.

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

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

Синтаксис ЧПС(ставка; значение1; [значение2]. )

· Ставка Обязательный аргумент. Ставка дисконтирования за один период.

· Значение1, значение2. Аргумент "значение1" является обязательным, последующие значения необязательные. От 1 до 254 аргументов, представляющих выплаты и поступления.

o Аргументы "значение1, значение2, . " должны быть равномерно распределены во времени, выплаты должны осуществляться в конце каждого периода.

o Функция ЧПС использует порядок аргументов "значение1, значение2, . " для определения порядка поступлений и платежей. Убедитесь в том, что ваши платежи и поступления введены в правильном порядке.

o Аргументы, которые являются пустыми ячейками, логическими значениями или текстовыми представлениями чисел, значениями ошибок или текстом, который невозможно преобразовать в числа, игнорируются.

o Если аргумент является массивом или ссылкой, то учитываются только числа в массиве или ссылке. Пустые ячейки, логические значения, текст и значения ошибок в массиве или ссылке игнорируются.

3.3. Индивидуальное задание 1

Вас просят дать в долг Р рублей и обещают вернуть через год F₁ руб., через два - F₂ руб., через три - F₃. При какой процентной ставке эта сделка выгодна? 12 Вариант: P=70000 F1=20000 F2=30000 F3=40000.

3.4 Схема алгоритма выполнения Индивидуального задания 1.

Средство MS Excel Подбор параметра позволяет определить значение одной входной ячейки, которое требуется для получения желаемого результата в зависимой ячейке (ячейке результата).

Функция ЧПС возвращает величину чистой приведенной стоимости инвестиции, используя ставку дисконтирования, а также стоимости будущих выплат (отрицательные значения) и поступлений (положительные значения).

ЧПС (ставка;значение1;значение2; . ).Ставка — ставка дисконтирования за один период.

Значение1, значение2, . — от 1 до 29 аргументов, представляющих расходы и доходы. В Приложении 3 приведены все условия и ответ.

Функция ЧПС возвращает суммарное значение денежных потоков, выраженное в денежных единицах по состоянию на сегодняшний день. С учетом временной стоимости денег один рубль, заработанный сегодня, стоит больше, чем тот же рубль, заработанный завтра. Функция ЧПС вычисляет текущую стоимость каждого из серии денежных потоков и суммирует их, возвращая чистую приведенную стоимость. На примере данной задачи мы легко вычислили выгодную процентную ставку.

3.6. Индивидуальное задание 2

Рассчитайте сумму вклада с помощью таблицы подстановки в зависимости от сроков (от 1 до 10 лет) и процентных ставок (от 5% до 15%). Первоначальную сумму вклада взять из предыдущей задачи.

3.7. Схема алгоритма выполнения индивидуально задания 2.

Расчет сложных процентов осуществляется по формуле:

, где F – будущая стоимость вклада, P – первоначальная стоимость вклада, r – годовая номинальная ставка, n – количество лет

Чистый дисконтированный доход (NPV, Net Present Value, чистая текущая стоимость, чистая дисконтированная стоимость) – показывает эффективность вложения в инвестиционный проект: величину денежного потока в течение срока его реализации и приведенную к текущей стоимости (дисконтирование).

К достоинствам использования данных показателей можно отнести:

Четкие границы выбора и оценки инвестиционной привлекательности проекта;
Возможность учета в формуле (ставке дисконтирования) дополнительных рисков по проекту;
Использования ставки дисконтирования для отражения изменения стоимости денег во времени.

К недостаткам чистого дисконтированного дохода можно отнести следующие:

Трудность оценки для сложных инвестиционных проектов, которые включают в себя множество рисков;
Сложность точного прогнозирования будущих денежных потоков;
Отсутствие влияния нематериальных факторов на будущую доходность (нематериальные активы).

Несмотря на ряд недостатков, показатель чистого дисконтированного дохода является ключевым в оценке инвестиционной привлекательности проекта, сравнении его с аналогами и конкурентами. В добавок к оценке NPV для более четкой картины, необходимо рассчитать такие инвестиционные коэффициенты как IRR и DPI.

В период прохождения учебной практики была выполнена следующая работа:

- закрепление и углубление знаний, полученных в процессе теоретического обучения, приобретение необходимых умений, навыков и опыта работы по специальности

- применение основных операций в Microsoft Excel для составления формул и функций, построение графиков и выполнение расчетов

Задача 1 - Таблица расчета выручки, всех издержек, прибыли. Диаграмма AVC,ATC,MC. Диаграмма TC и TR.

Задача 2 - максимизация полезности.

Задача 3.1- расчет выгодной процентной ставки

Задача 3.2- расчет суммы вклада

Задачи и функции аптечной организации: Аптеки классифицируют на обслуживающие население; они могут быть.

Методы исследования в анатомии и физиологии: Гиппократ около 460- около 370гг. до н.э. ученый изучал.

Категория: Лабораторная работа

Предметная область: Информатика, кибернетика и программирование

Описание: Ниже перечислены основные правила которыми следует руководствоваться чтобы создать качественную модель на основе электронных таблиц: необходимо четко обозначить все переменные; следует четко обозначить входы модели переменные решения и параметры; надо указать критерии эффективности и выходные переменные; не следует жестко привязывать значения параметров к формулам параметры должны храниться в отдельных ячейках рабочего листа для удобства документирования и анализа; если это возможно надо отделять переменные представляющие.

Дата добавления: 2013-10-24

Размер файла: 21.87 MB

Работу скачали: 229 чел.

Лабораторная работа № 2

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

По методической информации настоящего УП, наглядным материалам и учебной литературе ознакомиться с:

- виды и назначения задач.

Кратко законспектировать в своем отчете изученную информацию.
Дополнить отчет ксерокопией рисунка 2 .1.
Оформить отчет и представить его к проверке.
Подготовиться к защите отчета.

2.1. Свойства модели. Правила моделирования на основе электронных таблиц

Моделирование с использованием электронных таблиц не сводится к простому расчету уравнений, связывающих переменные. При задании модели нужно предвидеть, как она будет анализироваться, и, исходя из этого, так представить модель на рабочем листе Ехсе1, чтобы она обладала следующими свойствами:

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

необходимо четко обозначить все переменные;
следует четко обозначить входы модели, переменные решения и параметры;
надо указать критерии эффективности и выходные переменные;
не следует жестко привязывать значения параметров к формулам - параметры должны храниться в отдельных ячейках рабочего листа для удобства документирования и анализа;
если это возможно, надо отделять переменные, представляющие физические величины, от финансовых переменных;
следует использовать предоставляемые Ехсе1 возможности форматирования для выделения заголовков таблиц и ячеек.

Существует проблема, с которой сталкиваются те, кто стремится полностью документировать все параметры модели и в тоже время пытается сделать рабочий лист Excel , на котором представлена модель, компактным и свободным от излишних технических деталей. В Ехсе1есть две возможности, которые помогают разрешить это противоречие:

При расширении модели с целью включения в нее несколькихвременных периодов или нескольких продуктов следует учитывать два фактора.

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

Другой фактор (как следствие первого) заключается в том, что в созданных путем дублирования моделях данные и формулы располагаются в нескольких строках и столбцах, поэтому дальнейшее копирование модели, как это было с моделями, располагающимися в одном столбце, для проведения анализа чувствительности становится затруднительным, а то и вовсе невозможным. В этом случае можно скопировать модели на другие листы рабочей книги Ехсе1, а затем на одном листе свести в общую модель основные переменные со всех листов.

Другим выходом из создавшегося положения является использование таблиц подстановки. Существует два типа этих таблиц.

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

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

Наконец, можно использовать средство Подбор параметра . Это средство используется, если известно желаемое значение выходной переменной (например, дохода как критерия эффективности), и необходимо определить, при каком значении входной переменной получится требуемый результат. Кроме того, Подбор параметра можно использовать для определения точки безубыточности, а также точки безразличия при существовании альтернативных решений. Несмотря на то, что Подбор параметра - мощное средство, у него так же есть определенные недостатки: можно манипулировать только одной входной переменной; нельзя одновременно применять его для нескольких выходных переменных; оно не позволяет проводить оптимизацию, осуществляя поиск наибольшего или наименьшего значения выходной переменной, что часто требуется в управленческих моделях.

2.2. Оптимизационные модели

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

Существует несколько надстроек для программы Excel , предназначенных для оптимизации моделей при наличии ограничений. Примерами таких надстроек являются Solver ( Поиск решения) и What ' sBest . Excel содержит сокращенную версию надстройки Поиск решения.

Есть два представления модели: символическая (математическая) модель линейного программирования (ЛП) и ее представление в электронной таблице, которую будем называть табличной моделью.

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

При составлении математической модели в Excel следует придерживаться следующих рекомендаций:

каждая переменная решения располагается в отдельной ячейке, ячейки группируются по строкам или столбцам, каждому ограниченно отводится отдельная строка или столбец таблицы (чаще всего переменные решения расположены в столбцах, а ограничения в с троках);
переменные решения группируются в отдельный блок столбцов/строк;аналогично ограничения группируются в свой блок строк/столбцов;
все ячейки, содержащие переменные решения и целевую функцию, имеют заголовки в верхней части своего столбца, а все ограничения имеют заголовки в крайней слева ячейке своей строки;
коэффициенты целевой функции хранятся в отдельной строке, располагаясь непосредственно под или над соответствующими переменными решения: формула для вычислений целевой функции находится в соседней ячейке:
чтобы модель была понятней, ячейки с переменными решением целевой функцией выделяются рамкой по границе ячеек или заливкой ячеек;
коэффициент перед определенной переменной решения в каком-либо ограничении записывается в ячейку на пересечении столбца (строки), содержащего данную переменную решения, и строки(столбца), содержащей это ограничение;
в каждой строке ограничений за ячейками, содержащими коэффициенты данного ограничения, следует ячейка, в которую записано вычисленное значение функции ограничения (значение плюй частинеравенства), за ней следует ячейка, в которой стоит соответствующий знак неравенства, а затем ячейка, содержащая значение правой части неравенства. Дополнительно может включиться ячейка с формулой вычисления резерва, т. е. разности междузначениями левой и правой частей неравенства, вычисляемой таким образом, чтобы она была неотрицательной при соответствии ограничению;
ячейки, содержащие правые части ограничений, должны включать константы или формулы, в которые не входят переменные решения, - все формулы в правой части, прямо или косвенно связанные с переменными решения, должны быть перенесены в левую часть с помощью алгебраических преобразований данного неравенства;
не следует использовать в формулах модели ЛП функции Е xcel ЕСЛИ, ABS , MAX , MIN и другие нелинейные функции. Такие функции могут использоваться в формулах рабочего листа, но только в том случае, если они не влияют (прямо или косвенно) на вычисление целевой функции;
условия неотрицательности переменных решения не обязательно включать в табличную модель. Как правило, они опускаются и указываются непосредственно в диалоговом окне средства Поиск решения .

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

2.3. Надстройка Поиск решения

Поиск решения - это надстройка, входящая в поставку Е xcel , предназначенная для оптимизации моделей при наличии ограничений, в том числе моделей линейного программирования. Для этого в надстройке используются методы и алгоритмы математического программирования, которые позволяют находить оптимальные решения для табличных моделей. Для задач линейного программирования Поиск решения использует эффективный оптимизационный алгоритм (он подходит только для моделей ЛИ) под названием симплекс - метод.

Средство Поиск решения позволяет оптимизировать линейные и нелинейные модели. В оптимизируемой линейной модели всеформулы, которые непосредственно содержат переменные решения и прямо или косвенно влияют на формулу, по которой вычисляется целевая функция, должны быть линейными. Линейность модели позволяет использовать в средстве Поиск решения алгоритм симплекс - метода, который правильно работает только для формул, отображающих линейные взаимосвязи между переменными.

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

Конечно, допускаются нелинейные формулы, даже содержащие переменные решения, если эти формулы не связаны с вычислением значения целевой функции - ни прямо, ни косвенно, ни через ограничения.

2. 3. 1. Общие положения

Настройка Поиск решения предоставляет широкие возможности и позволяет решать не только уравнения, но и задачи оптимизации. Поиск наилучшего решения из множества возможных типичная задача, решаемая методами линейного программирования. Задачи подобного класса возникают, например, при планировании перевозок, распределении производственных ресурсов, оптимизации структурыпроизводства и т.п. Такие задачи возникают и при проведении инженерных расчетов.

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

2.3.2. Использование надстройки Поиск решения

Надстройка Поиск решения состоитиз двух программных компонентов. Первая - это встроенная в Excel программа, написанная на языке VisualBasic , которая транслирует представленную на рабочем листе информацию во внутреннее представление, используемое второй. Вторая программа находится в памяти компьютера в виде отдельного программного модуля; именно она выполняет оптимизацию и возвращает найденное решение первой программе, которая, в свою очередь, обновляет данные на рабочем листе. Эти две программы взаимодействуют при помощи внутреннего интерфейса прикладных программ. Когда выбирается команда Поиск решения , происходит обращение к первой программе надстройки Поиск решения , которая подготавливает таблицу к оптимизации и вызывает вторую программу - оптимизатор.

Таким образом, использование надстройки Поискрешения состоит из следующих действий:

Рис.2.1. Этапы работы с надстройкой Поиск решения

Последовательность работы с надстройкой Поиск решения схематично показана на рис. 2.1.

Применение специальной терминологии вызвано тем, что средство Поиск решения воспринимает только ячейки электронной таблицы, а не символическое представление моделей ЛП. С другой стороны, эти отличия часто номинальные. Соответствие между терминами, используемыми в моделях ЛПи средстве Поиск решения показано в табл. 2.1.

Существует еще одно обстоятельство, о котором необходимо помнить при работе с моделями ЛП. Часто отрицательные решения не имеют смысла, когда на переменные решения налагается ограничение отрицательности. Поскольку эти ограничения очевидны, их не перечисляют в табличной модели ЛП. Однако при использовании средства Поиск решения условия неотрицательностипеременных решения необходимо указывать - их пропуск является распространенной ошибкой.

2.3.4 Рекомендации по поиску решения задач ЛП

Для удобства использования средства Поиск решения необходимо следовать трем трем следующим принципам построения моделей линейного программирования.

Во-первых, необходимо масштабировать числа в модели таким образом, чтобы разность между наибольшим и наименьшим в таблице не превышала шесть или семь порядков. Пусть, например, в модели одна из переменных решения - это процентная ставка (имеющая значение порядка 5 %, т. е. 0,5), а показатель эф

В таких случаях нужно изменить масштаб измерения очень больших или очень маленьких чисел модели. В приведенном выше примере можно изменить денежные единицы, и выражать прибыль в миллионах долларов, а не в долларах. Это не приведет к потере общности и позволит сделать числа модели достаточно небольшими: теперь самое маленькое значение (0,5) отличается от самого большого (10) всего на 3 порядка.

Плохое масштабирование моделей является второй по значимости причиной возникновения проблем при оптимизации моделей ЛИ (первая пропуск ограничений неотрицательности).

Совет. Начиная с версии Excel 97, помочь в разрешении проблем масштабирования может опция Автоматическое масштабирование в диалоговом окне Параметры поиска решения, однако это не гарантирует успешного устранения всех проблем.

Другие причины отказов средства Поиск решения найти оптимальное решение обычно легче обнаружить, особенно с помощью специальных отчетов об ошибках, содержащихся в надстройке PremiumSolverforEducationalEdition . Такой причиной может стать, например, отсутствие допустимых решений в модели.

Во-вторых , средство Поиск решения допускает, чтобы правые части неравенств были числовыми константами, адресами ячеек или формулами, что никак не влияет на процесс оптимизации. Тем не менее, не следует в правую часть ограничений помещать формулы или адреса ячеек, значения которых могут меняться при оптимизации, т. е. которые прямо или косвенно связаны со значениями переменных решения.

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

На основе вышесказанного можно сформулировать следующие правилахорошего стиля моделирования:

при задании в диалоговом окне Поиск решения правых частей ограничений всегда следует указывать ссылки на ячейки в табличноймодели;

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

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

2.4. Линейная оптимизационная задача

Рассмотрим, как с помощью средства поиска решений решаются линейные оптимизационные задачи на примере тр ex типичных ситуаций: планирование производства, составление сплавов или смесей и планирование штатного расписания.

2.4.1.Планирование производства шин

Первым типомзадач, относящихся к типу линейных оптимизационных, являются задачи планирования производства.

Продукция обоих видов поступает в оптовую продажу. Для производства шин используются два исходных продукта А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн, соответственно. Расходы продуктов А и В на 1 тыс. шт. шин соответствующего типа приведены в табл. 2.2.

Изучение рынка сбыта показало, что суточный спрос на шипы S никогда не превышает спроса на шины типа W более чем на 1 тыс. шт. в сутки. Установлено, что спрос на шины типа S никогда не превышает 2 тыс. шт. в сутки. Какое количество шин каждого вида должна производить фабрика, чтобы доход от реализации продукции был максимальным?

Читайте также: