Перейти к основному контенту
Office

Использование надстройки "Поиск решения" для определения оптимального набора продуктов

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

В этой статье описано использование поиска решения — надстройки Microsoft Excel, которую можно использовать для анализа "что если", чтобы определить оптимальный набор продуктов.

Как определить ежемесячный набор продуктов, который будет максимально подключаться к рентабельности?

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

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

  • У каждого продукта есть ограниченные требования. Мы не можем получить больше товара в течение месяца, чем Диктовка по запросу, так как избыточное производство теряется (например, перишабле лекарство).

Теперь рассмотрим следующий пример проблемы с набором продуктов. Вы можете найти решение этой проблемы в файле Продмикс. xlsx, показанном на рисунке 27-1.

Изображение книги

Рассмотрим, что мы работаем для фармацевтической компании, которая создает шесть разных продуктов на своем предприятии. Для производства каждого продукта требуется материал и сырье. В строке 4 на рисунке 27-1 показано количество рабочих часов, необходимых для производства килограмма каждого продукта, а в строке 5 — количество килограммов сырья, необходимых для производства килограмма каждого продукта. Например, для производства килограмма продукта 1 требуется 6 часов трудозатрат и 3,2 фунта сырья. Для каждого лекарства Цена за штуку выдается в строке 6, стоимость единицы на килограмм дается в строке 7, а прибыль на килограмм — в строке 9. Например, если товар 2 продает на $11,00 для каждого фунта, то на него возмещаются стоимость за единицу $5,70, а для каждого из них — доход $5,30 долларов за каждый фунт. Потребность в месяце для каждого лекарства указана в строке 8. Например, спрос на товар 3 составляет 1041 фунта. В этом месяце на 4500 часов трудозатрат и 1600 килограмма сырья доступны. Как эта компания может максимально увеличить месячный доход?

Если мы ничего не знали о приложении Excel Solver, мы будем использовать эту проблему, создав лист для отслеживания прибыли и использования ресурсов, связанных с набором продуктов. Затем мы будем использовать пробные и видеоошибки для изменения ассортимента продуктов, чтобы оптимизировать прибыль без использования больше трудовых и необработанных материалов, чем доступно, и без производства какого – либо лекарства за пределами спроса. Поиск решения в этом процессе используется только на этапе пробы и ошибки. По сути, поиск решения — это подсистема оптимизации, которая обеспечивает безупречное выполнение поиска с помощью пробных и ошибок.

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

Как пример использования функции СУММПРОИЗВ в нашем примере с продуктом, давайте попробуем вычислить использование ресурсов. Наши трудозатраты рассчитываются с использованием

(Трудозатраты на килограмм лекарства 1) * (произведенные
килограммы лекарства 1) + (трудозатраты на фунты лекарства 2) * (количество килограммов лекарства 2) *.
(Трудозатраты на килограмм лекарства 6) * (произведенные килограммы для лекарства 6)

Мы могли вычислить использование трудовых ресурсов более утомительно, как D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 и I2 * I4. Кроме того, использование сырья может быть вычислено как D2 * D5 + E2 *SHIFT+F5 + F2 * F5 + G2 * G5 + H2 * H5 и I2 * I5. Однако ввод этих формул на листе для шести продуктов занимает много времени. Представьте себе, сколько времени потребуется, если вы работали с компанией, которая создала, например, продукты 50 на своем предприятии. Намного проще всего вычислять ресурсы и использование сырья — копирование из D14 в D15 формулы СУММПРОИЗВ ($D $2: $I $2, D4: i4). В этой формуле представлено значение D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (это наш трудный расход), но это еще проще вводить! Обратите внимание, что я использую знак $ с диапазоном D2: I2, так что при копировании формулы по-прежнему захватывается набор продуктов из строки 2. Формула в ячейке D15 вычисляет необработанное использование сырья.

Аналогичным образом, наш доход определяется с помощью

(Прибыль на лекарства 1 за килограмм) * (произведенные килограммы лекарства 1) +
(стоимость лекарства 2 на фунт) * (произведенные килограммы лекарства 2) *.
(Прибыль на килограмм лекарства 6) * (произведенные килограммы лекарства 6)

Прибыль легко вычисляется в ячейке D12 с помощью формулы СУММПРОИЗВ (D9: I9, $D $2: $I $2).

Теперь мы можем идентифицировать три компонента нашей модели "Поиск решения" для набора продуктов.

  • Целевая ячейка.Наша цель – это максимизировать прибыль (вычисленные в ячейках D12).

  • Изменяемые ячейки.Количество произведенных единиц продукта (указывается в диапазоне ячеек D2: I2)

  • Unique. Существуют указанные ниже ограничения.

    • Не используйте больше трудовых и необработанных материалов, чем доступно. Таким образом, значения в ячейках D14: D15 (используемые ресурсы) должны быть меньше или равны значениям в ячейках F14: F15 (доступные ресурсы).

    • Не делайте лекарства больше, чем по запросу. Таким образом, значения в ячейках D2: I2 (произведенные килограммы для каждого лекарства) должны быть меньше или равны спросу на каждое лекарство (в ячейках D8: I8).

    • Мы не можем выдать отрицательную сумму лекарства.

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

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

Примечание:  Как описано в главе 26, "Общие сведения об оптимизации с помощью Excel", "Поиск решения" устанавливается с помощью кнопки Microsoft Office, а затем параметров Excel и надстроек. В списке Управление выберите пункт надстройки Excel, установите флажок Поиск решения и нажмите кнопку ОК.

Появится диалоговое окно Параметры поиска решения, как показано на рисунке 27-2.

Изображение книги

Щелкните поле задать целевую ячейку и выберите нашу ячейку дохода (ячейка D12). Щелкните поле изМеняя ячейки и наведите указатель мыши на диапазон D2: I2, который будет содержать произведенные килограммы для каждого лекарства. В диалоговом окне должно появиться представление 27-3.

Изображение книги

Теперь мы готовы к добавлению ограничений в модель. Нажмите кнопку Добавить. Появится диалоговое окно Добавление ограничения, показанное на рисунке 27-4.

Изображение книги

Чтобы добавить ограничения на использование ресурсов, щелкните поле Ссылка на ячейку и выберите диапазон D14: D15. Выберите _Лт_ = из среднего списка. Щелкните поле ограничения и выберите диапазон ячеек F14: F15. Диалоговое окно Добавление ограничения теперь должно выглядеть примерно так, как показано на рисунке 27-5.

Изображение книги

Теперь мы доходили, что когда поиск решения попытается использовать различные значения для изменяющихся ячеек, только комбинации, которые удовлетворяют обоим D14< = F14 (использование трудовых и недоступных), и D15< = F15 (использование сырья не меньше или равно необработанные сырье) будут учитываться. Нажмите кнопку Добавить, чтобы ввести ограничения спроса. ЗаПолните диалоговое окно Добавление ограничения, как показано на рисунке 27-6.

Изображение книги

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

  • D2< = D8 (объем лекарства 1 меньше или равен спросу на лекарство 1)

  • E2< = E8 (объем произведенного лекарства 2 меньше или равен спросу на лекарство 2)

  • F2< = F8 (произведенная сумма лекарства 3 меньше или равна спросу на лекарство 3)

  • G2< = G8 (произведенная сумма лекарства 4 меньше или равна спросу на лекарство 4).

  • H2< = H8 (произведенное количество лекарства 5 меньше или равно спросу на лекарство 5)

  • I2< = i8 (произведенное количество лекарства 6 меньше или равно спросу на лекарство 6)

Нажмите кнопку "ОК" в диалоговом окне "добавить ограничение". Окно поиска решения должно выглядеть примерно так, как показано на рисунке 27-7.

Изображение книги

В диалоговом окне Параметры поиска решения не нужно отрицательно менять ячейки. Нажмите кнопку "Параметры" в диалоговом окне "Параметры поиска решения". Установите флажок Линейная модель и поле предполагать неОтрицательное значение, как показано на рисунке 27-8 на следующей странице. Нажмите кнопку "ОК".

Изображение книги

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

  • Целевая ячейка вычисляется путем сложения условий формы (изменяемой ячейки) * (константы).

  • Каждое ограничение удовлетворяет требованиям линейной модели. Это означает, что каждое ограничение оценивается путем сложения условий формы (изменяемой ячейки) * (константы) и сравнения сумм с константой.

Почему эта проблема поиска решения является линейной? Наша целевая ячейка (прибыль) вычисляется как

(Прибыль на лекарства 1 за килограмм) * (произведенные килограммы лекарства 1) +
(стоимость лекарства 2 на фунт) * (произведенные килограммы лекарства 2) *.
(Прибыль на килограмм лекарства 6) * (произведенные килограммы лекарства 6)

Это вычисление соответствует шаблону, в котором значение целевой ячейки извлекается путем сложения термов формы (изменяемой ячейки) * (константы).

Наши ограничения на трудовые ресурсы оцениваются за счет сравнения значения Derived ("трудозатраты на килограмм" лекарства 1) * (произведенные килограммы лекарства 1) + (трудозатраты на килограмм килограмма лекарства 2) * (количество килограммов лекарства 2) *. (Труд. США ) ED на килограмм лекарства 6) * (произведенные килограммы лекарства 6) * для доступа к вашим трудозатратам.

Таким образом, ограничение на труд вычисляется путем сложения условий формы (изменяемой ячейки) * (константы) и сравнения сумм с константой. Как ограничение трудозатрат, так и ограничение сырья, удовлетворяющие требованиям линейной модели.

Наши ограничения на спрос принимают форму

(Лекарства 1) _лт_ = (спрос на лекарства 1)
(лекарства 2) _лт_ = (спрос на лекарства 2)
§
(лекарства 6), _лт_ = (спрос на лекарство 6 )

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

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

  • Если модель поиска решения является линейной и выбирается линейная модель, надстройка "Поиск решения" гарантирует Поиск оптимального решения для модели "Поиск решения". Если модель поиска решения не является линейной, поиск решения может быть или может не найти оптимальное решение.

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

После нажатия кнопки ОК в диалоговом окне Параметры поиска решения мы вернемся к Главному диалоговому окну Поиск решения, показанному ранее на рисунке 27-7. После нажатия кнопки "решение" Поиск решения вычислит оптимальное решение (если оно существует) для нашей модели номенклатуры продуктов. Как было сказано в главе 26, оптимальным решением для модели "набор продуктов" является набор значений ячеек (произведенных килограммов каждого лекарства), который обеспечивает максимально возможную прибыль по набору всех возможных решений. Опять же, подходящим решением является набор изменяемых значений ячеек, удовлетворяющий всем ограничениям. Значения изменяемых ячеек, показанные на рисунке 27-9, являются подходящими решением, так как все уровни производства не являются отрицательными, но производственные уровни не превосходят требования, а использование ресурсов не превышает доступные ресурсы.

Изображение книги

Значения изменяемых ячеек, показанные на рисунке 27-10 на следующей странице, представляют собой неприменимое решение по следующим причинам.

  • Мы создаем больше из лекарства 5, чем спрос на него.

  • Мы используем больше трудовых затрат, чем доступно.

  • Мы используем больше необработанных материалов, чем доступно.

Изображение книги

После нажатия кнопки "решить" Поиск решения быстро находит оптимальное решение, показанное на рисунке 27-11. Чтобы сохранить оптимальные значения решений на листе, необходимо выбрать команду Сохранить найденное решение.

Изображение книги

Наша компания может максимально увеличить месячный доход на уровне $6 625,20, выполнив 596,67 фунта на лекарства 4, 1084 фунта на лекарства 5, и ни один из других наркотиков! Мы не можем определить, можно ли достичь максимального дохода в $6 625,20 в других случаях. Все, что мы можем сделать, – это то, что с нашими ограниченными ресурсами и спросом не существует способа внести более $6 627,20 в этом месяце.

Предположим, что требуется выполнение требования для каждого продукта. (Просмотрите лист "нет подходящего решения " в файле продмикс. xlsx.) Мы должны изменить ограничения на спрос с D2: I2< = D8: I8 на D2: I2> = D8: I8. Для этого откройте "Поиск решения", выберите ограничение D2: I2< = D8: I8 и нажмите кнопку Изменить. Откроется диалоговое окно Изменение ограничения, показанное на рисунке 27-12.

Изображение книги

Выберите _Гт_ =, а затем нажмите кнопку ОК. Теперь мы тем, что поиск решения поможет изменить только те значения ячеек, которые удовлетворяют всем требованиям. После нажатия кнопки "разрешить" появится сообщение "Поиск не может найти подходящее решение". Это сообщение не означает, что мы сделали ошибку в нашей модели, но это не повлияет на требования для всех продуктов. Поиск решения — это просто указание, что если нам нужно соблюсти спрос на каждый продукт, нам нужно добавить больше труда, более сырье и т. д.

Давайте посмотрим, что произойдет, если мы разрешающими неограниченные требования для каждого продукта, и мы допуским, что каждое лекарство будет производиться отрицательным количеством. (Вы можете увидеть эту проблему с поиском на странице " заданные значения " на листе "файл продмикс. xlsx". Чтобы найти оптимальное решение для этой ситуации, откройте окно "Поиск решения", нажмите кнопку "Параметры" и снимите флажок "не отрицательно". В диалоговом окне Параметры поиска решения выберите ограничение спроса D2: I2< = D8: I8 и нажмите кнопку Удалить, чтобы удалить ограничение. После нажатия кнопки "Поиск решения" будет возвращено сообщение "Установка значений ячеек не сходит". Это сообщение означает, что если целевая ячейка должна быть развернута (как в нашем примере), существуют подходящие решения с произвольно большими значениями целевых ячеек. (Если целевая ячейка должна быть минимизирована, сообщение "Установка значений ячеек не сходит" означает, что существует подходящее решение с произвольно небольшими значениями целевой ячейки.) В нашей ситуации, разрешая отрицательное производство лекарства, мы применяем "создание" ресурсов, которые можно использовать для создания произвольно большого количества других наркотиков. Это позволяет нам без ограничений получить неограниченные доходы. В реальной ситуации мы не можем выпустить деньги на неограниченный объем денег. Коротко говоря, если вы видите сообщение "заДанные значения не сходятся", в вашей модели возникла ошибка.

  1. Предположим, что наша компания может приобрести до 500 часов трудозатрат на $1 больше в час, чем стоимость текущих трудозатрат. Как добиться максимальной прибыли?

  2. На заводской фабрике (A, B, C и D) выводятся три продукта (товары 1, 2 и 3). В этом месяце производитель микросхем может продавать 80 единиц продукта 1, 50 единиц продукта 2 и не менее 50 единиц продукта 3. Специалист по A может вносить только продукты 1 и 3. Специалист B может вносить только продукты 1 и 2. Специалист на языке C может сделать только продукт 3. Техника D может быть доступна только на товар 2. Для каждой изготовленной единицы продукты вносят следующие прибыль: товар 1, $6; Продукт 2, $7; и Product 3, $10. Время (в часах), необходимое каждому специалисту по производству продукта, можно получить, выполнив указанные ниже действия.

    Продукт

    Техника А

    Техника B

    Техника C

    Техника Г

    1

    2

    2,5

    Невозможно

    Невозможно

    2

    Невозможно

    3

    Невозможно

    3,5

    3

    3

    Невозможно

    4

    Невозможно

  3. Каждый техник может работать до 120 часов в месяц. Как производитель микросхем может максимально увеличить месячный доход? Предположим, что может быть произведено дробное количество единиц.

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

    Мыши

    Клавиатуры

    Джойстики

    Прибыль/ед.

    $8

    $11

    $9

    Использование трудозатрат/ед.

    .2 часа

    .3 часа

    .24 часа

    Машинное время/единица

    .04 час

    .055 час

    .04 час

    Месячный спрос

    15 000

    27 000

    11 000

  5. В каждом месяце доступно всего 13 000 человеко-часы и 3000 часов машинного времени. Как производитель может добиться максимальной ежемесячной прибыли от завода?

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

  7. Джейсон создает ромбы браслета, некклацес и серьги. Он хочет работать в течение не более 160 часов в месяц. Он имеет 800 унция бубен. Прибыль, трудозатраты и унции, которые требуются для создания каждого продукта, приведены ниже. Что делать, если спрос на каждый продукт неограниченен, как это может Джейсон увеличить его прибыль?

    Продукт

    Прибыль за единицу

    Количество рабочих часов на ед.

    Унции — ромбы на единицу

    Дружбы

    300р.

    .35

    1,2

    Некклаце

    200 ₽

    .15

    .75

    Серьги

    100р.

    0,05

    0,5

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×