Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Как Составить Математическую Модель Задачи в Excel

«Поиск решений» — функция Excel, которую используют для оптимизации параметров: прибыли, плана продаж, схемы доставки грузов, маркетингового бюджета или рентабельности. Она помогает составить расписание сотрудников, распределить расходы в бизнес-плане или инвестиционные вложения. Знание этой функции экономит много времени и сил. Рассказываем, как освоить функцию поиска решений.

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

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

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

Целевая функция — результирующий показатель, для которого Excel подбирает наилучшие показатели. Чтобы программа понимала, какие данные наилучшие, мы задаем функцию в виде формулы. Эту формулу мы отображаем в отдельной ячейке. Результирующий показатель может принимать максимальное или минимальное значения, а также быть конкретным числом.

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Мы посчитали, сколько суммарно продавалось каждый январь каждый февраль и так далее, а потом разделили это на среднее значение продаж за все два периода. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Нам осталось рассчитать коэффициент сезонности для каждого периода. Учитывая, что у нас есть исторические данные за два года, разумно будет учесть это при расчете. Можем сделать следующим образом: в ячейке J4 прописываем формулу =(C4+C16)/СРЗНАЧ($C$4:$C$27)/2 и протягиваем вниз на 12 месяцев (т.е. до J15).
Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Методы прогнозирования в Excel — примеры и расчеты — Блог SF Education

  • нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
  • в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
  • в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.

Например, в первом узле второй импульс напряжения появляется через время t=2×t12=2×l1/n1=2×1500/300=10мкс с отрицательной амплитудой, определяемой коэффициентом отражения в узле b21= (ZВ2 –ZВ1)/ (ZВ2 +ZВ1) = (40 – 400)/(40 +400) = – 0,82.

Решение задач оптимизации в Excel

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

b) Следует расходовать не более 40% бюджета на телевидение и не более 20% бюджета на рекламные щиты;

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

1. Сформулировать и решить задачу распределения средств по различным источникам для получения максимальной прибыли от рекламы;

3. Определить изменится ли оптимальный план распределения средств, если увеличение прибыли от газетной рекламы снизится до 5 долларов в расчете на 1 доллар, затраченный на рекламу;

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

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Составим математическую модель задачи, выбрав в качестве переменных , х4 — количество средств, затраченных на телевидение, рекламные плакаты, радио и газеты соответственно. Тогда ожидаемая прибыль от рекламы может быть подсчитана по формуле

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

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

Чтобы привлечь компьютер к решению этой задачи необходимо ввести исходные данные на лист Excel.

Отчет состоит из трех таблиц, расположенных на одном листе книги Excel.

В первой таблице выводятся сведения о целевой функции. В столбце Исходное значение приведено значение целевой функции до начала вычислений, в столбце Результат — после оптимизации.

Следующая таблица содержит значения искомых переменных (изменяемых ячеек) до и после решения задачи.

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

Отчет состоит из двух таблиц, расположенных на одном листе книги Excel.

В первой таблице (Изменяемые ячейки) приводится следующая информация о переменных:

· результирующее значение — оптимальные значения переменных;

· нормированная стоимость — ее величина равна значению соответствующей симплексной оценки с противоположным знаком. Для невыпускаемой продукции нормированная стоимость показывает, на сколько изменится целевая функция при принудительном включении единицы этой продукции в оптимальное решение;

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

Во второй таблице (ограничения) приводятся аналогичные значения для ограничений задачи:

· величины использованных ресурсов (левые части ограничений) при оптимальном плане выпуска продукции;

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

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

Используем результаты отчета по устойчивости для проведения постоптимального анализа в данной задаче:

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Как Составить Математическую Модель Задачи в Excel • Постановка задачи

Исследуем сначала влияние на оптимальный план изменений коэффициентов целевой функции — прибыль от 1 затраченного $ на рекламу определенного вида.

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Рассчитать во временном диапазоне от 0 до 40 мкс изменения напряжения в узлах схемы при воздействии заданного импульса напряжения. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
При неучете потерь в линии (R=G=0) отраженные волны будут падающими для узлов, через времена, равные временам пробега волн по линии, т.е. уравнения, связывающие узлы 1 и 2, записываются в виде

Как сделать задачу в excel?

Введем систему декартовых координат на плоскости x1Ox2 и построим множество планов задача (I)-(III). Каждое линейное неравенство системы определяет полуплоскость по одну сторону от граничной прямой, заданной соответствующим равенством. Множество планов задачи есть пересечение полуплоскостей, представляющих собой выпуклый многоугольник.

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector