Совхоз для кормления животных использует два вида корма в дневном рационе excel

Обновлено: 05.07.2024

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

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

Решение оптимизационных задач в среде электронных таблиц Excel Для установки надстройки Поиск решения необходимо: Знак оффиса в левом верхнем углу Excel Копка «Параметры Excel» Вкладка «Надстройки» → Перейти установить флажок около пункта Поиск решения; щелкнуть на кнопке ОК.

Задача1. Пусть в колхозе требуется распределить площадь пашни между двумя культурами в соответствии со следующими данными: Заданы ресурсы производства: земли – 1800 га, человеко-дней – 8000. Величины x и y являются неизвестными и подлежат определению. Культура Площадь га Урожай, ц/га Затраты, руб./га Цена за 1 ц, руб. Затраты, человеко-дней на 1 га. 1 x 10 50 6 2 2 y 15 80 8 10

Решение оптимизационных задач в среде электронных таблиц Excel Построение математической модели задачи включает в себя: задание целевой функции (ее надо максимизировать или минимизировать); задание системы ограничений в форме линейных уравнений и неравенств; Требование неотрицательности переменных.

Решение оптимизационных задач в среде электронных таблиц Excel Решим задачу по оптимизации критерия, а именно по максимуму прибыли. Для прибыли (согласно данным таблицы) имеем формулу:

Решение оптимизационных задач в среде электронных таблиц Excel Ограничения имеют следующий вид: ограничение по площади: ограничение по человеко-дням: или Кроме того, ясно, что

Решение оптимизационных задач в среде электронных таблиц Excel Учтя все условия задачи, приходим к её математической модели неотрицательных целочисленных решений системы линейных неравенств Среди них найти такие, которые соответствуют максимуму линейной функции

Решение оптимизационных задач в среде электронных таблиц Excel Теперь заполним расчетную форму в табличном процессоре Excel. Введем: в столбец А - подписи к величинам и расчетным формулам, в столбец В – расчетные формулы (отображаются вычисленные по этим формулам значения),

Решение оптимизационных задач в среде электронных таблиц Excel В меню Данные выбираем Поиск решения и заполняем открывшееся диалоговое окно следующим образом:

Решение оптимизационных задач в среде электронных таблиц Excel Далее щелкаем в этом окне на кнопке Параметры и в открывшемся диалоговом окне Параметры поиска решения устанавливаем флажки Линейная модель и Неотрицательные значения. Щелкаем по кнопке ОК. В окне Поиск решения щелкаем на кнопке Выполнить.

Решение оптимизационных задач в среде электронных таблиц Excel Задача 2. Предполагается, что рацион коров составляется из двух видов кормов – сена и концентратов. Суточная потребность кормов на 1 корову равна 20 кормовых единиц. В таблице приведены числовые данные о себестоимости кормов в данном хозяйстве.

Решение оптимизационных задач в среде электронных таблиц Excel Найти самый дешевый рацион, если ежедневный рацион кормления сельскохозяйственных животных должен включать не менее 16 кг сена. Виды кормов Содержание кормовых единиц в 1 кг кормов Себестоимость кормов, в рублях. Сено 0,5 1,5 Концентраты 1,0 2,5

Решение оптимизационных задач в среде электронных таблиц Excel Ограничения: Целевая функция:

Решение оптимизационных задач в среде электронных таблиц Excel Задача3. Мебельная фабрика выпускает кресла двух типов. На изготовление кресла первого типа расходуется 2 м досок стандартного сечения, 0,8 м2 обивочной ткани и затрачивается 2 человеко-часа, а на изготовление кресла второго типа – соответственно 4 м, 1,25 м2 и 1,75 человеко-часа. Известно, что цена одного кресла первого типа равна 1500 рублей, второго типа – 2000 рублей. Сколько кресел каждого типа надо выпускать, чтобы стоимость выпускаемой продукции была максимальной, если фабрика имеет в наличии 4400 м досок, 1500 м2 обивочной ткани и может затратить 3200 человеко-часов рабочего времени на изготовление этой продукции?

Решение оптимизационных задач в среде электронных таблиц Excel Ограничения: Целевая функция:

Решение оптимизационных задач в среде электронных таблиц Excel Задача 4 Хозрасчетной бригаде выделено для возделывания кормовых культур 100 га пашни. Эту пашню предполагается занять кукурузой и свеклой, причем свеклой решено занять не менее 40 га. Как должна быть распределена площадь пашни по культурам, чтобы получилось наибольшее число кормовых единиц? При этом должно быть учтено следующее: 1ц кукурузного силоса содержит 0,2 кормовой единицы, 1ц свеклы – 0,26 кормовой единицы; на возделывание 1га кукурузного поля необходимо затратить 38 человеко-часов труда механизаторов и 15 человеко-часов ручного труда, а на возделывание 1га поля, занятого свеклой, соответственно 43 и 185 человеко-часов; ожидаемый урожай кукурузы – 500 ц с 1 га, а свеклы – 200 ц с 1 га; наконец, всего на возделывание кормовых культур можно затратить 4000 человеко-часов механизаторов и 15000 человеко-часов ручного труда.

Решение оптимизационных задач в среде электронных таблиц Excel Ограничения: Целевая функция:

Совхоз для кормления животных использует два вида корма. В дневном рационе должно содержаться не менее 6 единиц питательного вещества А и не менее 12 единиц питательного вещества В.

Какое количество корма надо расходовать ежедневно на одно животное, чтобы затраты были минимальными? Использовать данные таблицы.

Количество питательных веществ в 1 кг корма

Цена 1 кг корма, тыс. руб.

Построить экономико-математическую модель задачи, дать необходимые комментарии к ее элементам и получить решение графическим методом. Что произойдет, если решать задачу на минимум, и почему?

Решение.

1)Построение экономико-математической модели задачи

Введем переменные : X₁- количество корма 1, X₂ - количество корма 2 (в кг).

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

Целевая функция задачи :

Найдём минимум целевой функции.

Область допустимых решений (ОДР) задачи, согласно условию:

2) Построим область допустимых решений (ОДР) задачи.

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

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

Пересечение указанных полуплоскостей в первой четверти представляет собой область АВС (заштрихованная область для всех ограничений задачи ОДР).

3) Для определения направления движения к оптимуму построим вектор-градиент, соединив его вершину с началом координат О (0, 0). Строим градиент функции - вектор, показывающий направление возрастания функции f(x).

4) Построим некоторую линию уровня .

Пусть, например, а = 0. На эскизе такой линии уровня отвечает прямая ОХ, перпендикулярная вектор-градиенту.

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

6)Определим координаты точки В, являющейся точкой пересечения граничных прямых , решив систему уравнений :

Точка 0( 0; 0 ) - точка начала координат.

Получаем точку В (2; 2) - вершину многоугольника (сектора) планов.

7) Точка В является так называемым оптимальным планом. В точке В целевая функция принимает свое минимальное значение при заданной системе ограничений. Эта точка отвечает минимально возможным затратам на корма при заданной ОДР. При заданной ОДР отсутствует точка максимума для целевой функции Смысл данного факта: затраты на корма при данной ОДР никак не ограничиваются (хотя в реальных случаях такая ситуация невозможна). Таким образом, целевая функция в задаче линейного программирования принимает, при заданной системе ограничений :

минимальное значение-min(f)=f(В)=0,2*2 + 0,3 *2 = 1. (тыс. руб).

максимальное значение - отсутствует (функция неограниченна сверху на ОДР). С помощью надстройки ЕХСЕL «Поиск решения" минимум целевой функции, также как и при использовании графического метода. Максимум найти не удается (сообщается, что результат не сходится); в таблице помещено только одно из возможных значений.

Ответ: максимального значения - нет (ОДР неограничен сверху);

min( x) = (2; 2); min(f)= 1 (тысяч денежных единиц).

C - градиент ЦФ ОПР

B(min)

2X₁+X₂ = 6

0,2 X₁ +0,3 X₂ = 0

2X₁+4X₂=12

Совхоз для кормления животных использует два вида корма. В дневном рационе животного должно содержаться не менее 6 единиц питательного вещества А и не менее 12 единиц питательного вещества В. Какое количество корма надо расходовать ежедневно на одно животное, чтобы затраты были минимальными? Использовать данные таблицы.

Питательное

Количество питательных веществ в 1 кг корма

Цена 1 кг корма, тыс. руб.

Пусть: х₁ (кг) – количество корма типа 1, которое следует включить в дневной рацион животного.

х₂ (кг) - количество корма типа 2, которое следует включить в дневной рацион животного.

Таким образом дневной рацион животного формально представляет собой вектор Х = (х₁; х₂) .

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

min f( x ) = 0.2x₁ + 0.3x₂

Построим ОДР этой задачи.

Прямые ограничений означают, что ОДР задачи будет лежать в I четверти прямоугольной системе координат. Функциональные ограничения неравенства определяющие область, являются пересечением полуплоскостей с граничными прямыми.

I. 2x₁ + x₂ = 6

Пересечение указанных полуплоскостей в I четверти представляет собой неограниченную многоугольную область с вершинами АВС (ОДР).

Для определения направления движения к оптимуму, построим вектор-градиент, соединив его вершину (0,2;0,3) с началом координат О (0;0).

Построим некоторую линию уровня перпендикулярно вектору градиенту. Этой линией уровня отвечает прямая ОХ.

При минимизации ЦФ необходимо перемещать линию уровня противоположно направлению вектора-градиента. Придельными точками при таком движении линии уровня ОХ является, соответственно, точка В, далее она выходит из ОДР.

Координаты точки В определим, решив систему уравнений:

Решением этой системы уравнений являются сведущие значения переменных:

Минимальное значение ЦФ равно:

min f( x ) = 0.2*2 + 0.3*2 = 1

ВЫВОД:

Таким образом в рассматриваемой задаче Савхозу следует рекомендовать включать в дневной рацион одного животного ежедневно 2 кг корма типа 1 и 2 кг корма типа 2. В этом случае ожидаются минимальные затраты в сумме 1 тыс. руб.

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

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

Нормы расхода сырья на одно изделие

Запасы сырья

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

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

3. Пояснить нулевые значения переменных в оптимальном плане.

4. На основе свойств двойственных оценок и теорем двойственности:

Пусть x₁, x₂, x₃ и х₄ – объемы производства продукции каждого вида.

Целевая функция имеет вид: max f( x ) = 9x₁ + 6x₂ + 4x₃ + 7x₄,

Поиск оптимально плана выпуска продукции.

Решим задачу при помощи настройки Excel.

Введем исходные данные.

Опишем ЦФ с помощью функции – «СУММПРОИЗВ».

Введем данные для левых частей ограничений. В «Поиске решений» введем направления ЦФ, адреса искомых переменных, добавим ограничения.

Введем параметры для решения ЗЛП.

После ввода параметров следует нажать кнопку «Выполнить».

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

Отчет по устойчивости.

В отчете по устойчивости мы видим, что нормированная стоимость для производства продукций В и Г видов равна, соответственно, -0,5 и -5 – это означает, что если несмотря на оптимальный план (95, 210, 0, 0), попробуем включить в план выпуска продукцию В и Г вида, то новый план выпуска принесет нам доход 2109,5 ед., что на 5,5 ед. меньше, чем прежнее оптимальное решение.

Предельные значения приращения целевых коэффициентов, при которых сохраняется первоначальное оптимальное решение. Допустимое увеличение цены продукции В и Г видов равно, соответственно, 0,5 ед. и 5 ед., а допустимое уменьшение практически неограниченно 1E+30. Это означает, что если цена продукции В и Г видов возрастет более чем на 0,5 ед. и 5 ед., то оптимальное решение изменится: станет целесообразным производить продукцию видов В и Г. А если их цена будет снижаться вплоть до нуля, то оптимальное решение (95, 210, 0, 0) останется прежним.

В рассматриваемой задаче являются дефицитные типы сырья (II и III типы). Чтобы обеспечить увеличение производства продукции необходимо увеличить II тип сырья, самое большое, на 190, а III тип сырья – на 340.

Отчет по результатам.

В отчете по результатам содержатся оптимальные значения переменных х₁, х₂, х₃ и х₄, которые соответственно равны 95; 210; 0; 0, значение целевой функции – 2115, а так же левые части ограничений.

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

Пусть y₁, y₂, y₃ – двойственные оценки типов ресурсов соответственно.

Целевая функция имеет вид: min g( y ) = 180y₁ + 210y₂ + 800y₃

Найдем оптимальный план этой задачи, используя основную теорему двойственности:

Проверим, является ли указанный в условии задачи план допустимым решением:

Так же получим: y₁ (95 – 180) = 0, т.к. 95 < 180, то y₁ = 0

y₂ (210 – 210) = 0

y₃ (800 - 800) = 0

Следовательно, план оптимальный. Ресурс I остается в избытке, а ресурсы II и III расходуются полностью.

Воспользуемся соотношением второй теоремой двойственности:

т.к. х₁ = 95 > 0 и х₂ = 210 > 0, то первое и второе ограничения двойственной задачи обращаются в равенства:

y₁ = 0 y₃ = 2,25

Вычислим значения целевой функции двойственной задачи:

Таким образом, приведенный в условии план является оптимальным.

3. Пояснить нулевые значения переменных в оптимальном плане.

Если изготовление продукции определенного вида вошло в план (х_j > 0), то в двойственных оценках оно не убыточное, т.е. стоимость ресурсов, затраченных на производство единицы продукции, равна его цене. Такая продукция эффективна, выгодна с точки зрения принятого критерия оптимальности. В этой задаче – это продукция видов В и Г.

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

В данной задаче в план производства не вошли продукция видов В и Г, потому что затраты по ним превышают цену на 0,5 ед. и 5 ед. соответственно. Это можно подтвердить, подставив в ограничения двойственной задачи оптимальные значения вектора Y:

Разницу между правыми и левыми частями ограничений двойственной задачи видно в « Отчете по устойчивости» в столбце « Нормируемая стоимость».

4.1 Проанализировать использование ресурсов в оптимальном плане исходной задачи.

Тип сырья I является недефицитным (y₁ = 0). Ресурсы II и III являются дефицитными, причем ресурс III более дефицитный, чем ресурс II ( y₃ = 2,25; y₂ = 1,5; y₃ > y₂).

Найдем норму заменяемости для дефицитных ресурсов:

y₃ : y₂ = 2,25 : 1,5 = 1,5

Следовательно, ресурс III в 1,5 раза более эффективен, чем ресурс II с точки зрения влияния на максимум продукции.

4.2 Определить, как изменятся выручка и план выпуска продукции при увеличении запасов сырья II и III видов на 120 и 160 единиц соответственно и уменьшении на 60 единиц запасов сырья I вида.

Будем считать, что данные изменения объемов ресурсов находятся в пределах устойчивости оптимального решения (в пределах устойчивости двойственных оценок), тогда по третьей теореме двойственности (теореме об оценках) имеем:

Решая эту ЗЛП симплекс-методом при помощи настройки Excel, получим следующее:

Полученное решение означает, что максимальные доход, увеличившийся с 2115 ед. до 2655 ед. предприятие может получить при выпуске 75 ед. первой продукции, 330 ед. второй продукции, 0 ед. третьей продукции и 0 ед. четвертой продукции. Третий и четвертый вид продукции не выгодно выпускать, т.к. затраты превышают цену.

Отчет по результатам.

В отчете по результатам содержатся оптимальные значения переменных х₁, х₂, х₃ и х₄, которые соответственно равны 75; 330; 0; 0, значение целевой функции – 2655, а так же левые части ограничений.

Отчет по устойчивости.

В отчете по устойчивости мы видим, что нормированная стоимость для производства продукций В и Г видов равна, соответственно, -0,5 и -5 – это означает, что если несмотря на оптимальный план (75, 330, 0, 0), попробуем включить в план выпуска продукцию В и Г вида, то новый план выпуска принесет нам доход 2649,5 ед., что на 5,5 ед. меньше, чем прежнее оптимальное решение.

Решим эту же задачу «вручную». Запишем исходную и двойственную ЗЛП с измененными объемами ресурсов.

Двойственная:

min g (y) = 120y₁ + 330 y ₂ + 960y₃

Воспользуемся соотношением второй теоремой двойственности (теорема о дополняющей нежестокости):

Рассмотрим первые соотношения (их два):

y₁ + 0y₂ + 4y₃ = 9

Следовательно, про x₁ ничего сказать нельзя.

0y₁ + y₂ + 2y₃ = 6

Следовательно, про x₂ тоже ничего сказать нельзя.

2y₁ + 3y₂ + 0y₃ = 4

y₁ + 2y₂ + 4y₃ = 7

Рассмотрим вторые соотношения:

y₁ = 0, ничего сказать нельзя

y₂ = 1,5 - второе ограничение обращается в равенство

y₃ = 2,25 – третье ограничение обращается в равенство

Запишем систему уравнений и решим ее:

х₃ = 0 х₃ = 0

х₄ = 0 х₄ = 0

Это совпадает с выводом, сделанным ранее на основании «теоремы об оценках».

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

Это задание выполняется на основе третьего свойства двойственных оценок, т.е. оценки как определение эффективности.

Следовательно, данную продукцию выпускать целесообразно (затраты меньше цены).

Промышленная группа предприятий (холдинг) выпускает продукцию трех видов, при этом каждое из трех предприятий группы специализируется на выпуске продукции одного вида: первое предприятие специализируется на выпуске продукции одного вида, второе предприятие – продукции второго вида; третье предприятие – продукции третьего вида. Часть выпускаемой продукции потребляется предприятиями холдинга (идет внутреннее потребление), остальная часть поставляется за его пределы (внешним потребителям, является конечным продуктом). Специалистам управляющей компании получены экономические оценки a_ij ( i = 1, 2, 3; j = 1, 2, 3) элементов технологической матрицы А (норм расхода, коэффициентов прямых материальных затрат) и элементов y_i вектора конечной продукции Y.

1. Проверить продуктивность технологической матрицы А = (a_ij) (матрицы коэффициентов прямых материальных затрат).

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

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

Рассмотрим решение простейшей задачи оптимизации кормового рациона средствами Excel.

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

Итак, Задача.

в продукте M - 150 ккалорий и 14 единиц жира;
в продукте N - 200 ккалорий и 4 единицы жира.

Экономико-математическая постановка задачи:

Ограничение по количеству ккалорий:
150x₁+200x₂>=200
Ограничение по количеству жира:
14x₁+4x₂ =0; x₂>=0

Целевая функция - минимум стоимости рациона:
1,5x₁+2,3x₂->min

Ввод исходных данных в ячейки Excel:

Итак, в ячейки А2 и А3 вводим начальные значения x₁ и x₂ - нули.
В ячейки А4 и А5 вводим левые части ограничений (первоначально получатся нули), в ячейки В4 и В5 - правые части соответствующих ограничений.
В ячейку А6 вводим целевую функцию.
Ввод исходных данных завершен.

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

Последовательностью команд меню Сервис - Поиск решения вызываем инструмент "Поиск решения".

Итак, с использованием красной стрелки (переход на рабочий лист)
Устанавливаем целевую ячейку - $A$6
Равной минимальному значению
Изменяя ячейки - $A$2:$A$3
с использованием кнопки Добавить последовательно добавляем три исходных ограничения.
Нажимаем кнопку Выполнить.

Интерпретация результатов.

После вычислений на рабочем листе получили следующие результаты (см.рис.ниже):

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