Решение проблем с оптимизацией в Excel

Обновлено: 20.11.2024

Чтобы определить модель оптимизации в Excel, выполните следующие основные шаги:

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

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

Создание листа Excel

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

На листе ниже мы зарезервировали ячейки B4, C4, D4 и E4 для представления переменных нашего решения X₁, X₂, X_{3< /sub> и X₄, представляющие количество поддонов каждого типа панелей, которые необходимо произвести. Решатель определит оптимальные значения для этих ячеек. (Нажмите на рабочий лист, чтобы открыть полноразмерное изображение.)}

Обратите внимание, что прибыль от каждого поддона с панелями (450, 1150, 800 и 400 долларов) указана в ячейках B5, C5, D5 и E5 соответственно. Это позволяет нам вычислить цель в ячейке F5 следующим образом:

Формула для ячейки F5: =B5*B4+C5*C4+D5*D4+E5*E4

Формула для ячейки F5: =СУММПРОИЗВ(B5:E5;B4:E4)

В ячейки B8:E11 мы ввели количество ресурсов, необходимых для производства поддона каждого типа панелей. Например, значение 15 в ячейке C9 означает, что для изготовления поддона панелей в тихоокеанском стиле требуется 15 часов прессования. Эти числа получены непосредственно из формул для ограничений, показанных ранее. Имея эти значения, мы можем ввести формулу в ячейку F8, чтобы вычислить общее количество клея, используемого для любого количества произведенных поддонов:

Формула для ячейки F8: =СУММПРОИЗВ(B8:E8,$B$4:$E$4)

Мы можем скопировать эту формулу в ячейки F9:F11, чтобы вычислить общее количество использованной прессованной, сосновой и дубовой щепы. (Знаки доллара в $B$4:$E$4 указывают, что этот диапазон ячеек остается постоянным, а диапазон ячеек B8:E8 становится B9:E9, B10:E10 и B11:E11 в скопированных формулах.) Формулы в ячейках F8:F11 соответствуют левым значениям ограничений.

В ячейки G8:G11 мы ввели доступное количество ресурсов каждого типа (соответствующих правым значениям ограничений). Это позволяет нам выразить показанные ранее ограничения следующим образом:

Нажмите на ссылки ниже, чтобы узнать, как эту модель можно решить с помощью встроенного Решателя Excel (или Решателя класса Premium) или флагманского продукта FrontLine Systems Risk Solver Platform.

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

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

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

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

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

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

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

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

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

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

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

Мы могли бы вычислить использование рабочей силы более утомительным способом: D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Точно так же использование сырья может быть рассчитано как D2*D5+E2*E5+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)

Ограничения. У нас есть следующие ограничения:

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

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

Мы не можем производить отрицательное количество любого лекарства.

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

Для начала перейдите на вкладку "Данные" и в группе "Анализ" нажмите "Решатель".

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

Появится диалоговое окно Solver Parameters, как показано на рис. 27-2.

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

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

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

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

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

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

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

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

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

Наши трудовые ограничения оцениваются путем сравнения значения, полученного из (трудозатраты на фунт лекарства 1)*(произведение килограмма лекарства 1) + (трудозатраты на фунт лекарства 2)*(произведение 2 фунтов лекарства) + …(Трудозатратызатраты на фунт Лекарства 6)*(Произведено 6 фунтов Лекарства) к доступному труду.

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

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

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

После нажатия кнопки «ОК» в диалоговом окне «Параметры решения» мы возвращаемся к основному диалоговому окну «Решатель», показанному ранее на рис. 27-7. Когда мы нажимаем «Решить», Solver вычисляет оптимальное решение (если оно существует) для нашей модели ассортимента продуктов. Как я говорил в главе 26, оптимальным решением для модели ассортимента продуктов будет набор изменяющихся значений ячеек (фунтов, произведенных каждым лекарством), который максимизирует прибыль по сравнению с набором всех возможных решений. Опять же, допустимое решение — это набор изменяющихся значений ячеек, удовлетворяющих всем ограничениям. Изменение значений ячеек, показанное на рис. 27.9, является допустимым решением, поскольку все уровни производства неотрицательны, уровни производства не превышают потребности, а использование ресурсов не превышает доступных ресурсов.

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

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

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

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

После нажатия кнопки «Решить» Solver быстро находит оптимальное решение, показанное на рис. 27-11. Вам необходимо выбрать «Сохранить решение решения», чтобы сохранить оптимальные значения решения на листе.

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

Предположим, что спрос на каждый продукт должен быть удовлетворен. (См. рабочий лист Нет возможного решения в файле Prodmix.xlsx.) Затем мы должны изменить наши ограничения спроса с D2:I2 =D8:I8. Для этого откройте Solver, выберите D2:I2 = и нажмите OK. Теперь мы уверены, что Solver рассмотрит возможность изменения только тех значений ячеек, которые соответствуют всем требованиям. Когда вы нажмете «Решить», вы увидите сообщение «Решатель не смог найти подходящее решение». Это сообщение не означает, что мы допустили ошибку в нашей модели, а скорее то, что с нашими ограниченными ресурсами мы не можем удовлетворить спрос на все продукты. Solver просто говорит нам, что если мы хотим удовлетворить спрос на каждый продукт, нам нужно добавить больше труда, больше сырья или больше того и другого.

Давайте посмотрим, что произойдет, если мы допустим неограниченный спрос на каждый продукт и позволим производить отрицательное количество каждого лекарства. (Вы можете увидеть эту проблему Решателя на рабочем листе Установить значения не сходятся в файле Prodmix.xlsx.) Чтобы найти оптимальное решение для этой ситуации, откройте Решатель, нажмите кнопку «Параметры» и снимите флажок Предположим, что поле неотрицательное. В диалоговом окне "Параметры решателя" выберите ограничение спроса D2:I2

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

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

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

Если вы узнали, как получить решение с помощью функций исчисления, вы почти закончили! Настроить параметр или задачу динамической оптимизации очень просто, выполнив всего пару шагов:

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

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

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

= Исходное значение решения – Целевое значение

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

Обратите внимание. В версиях до ExceLab 7.0 использовались функции критериев для наложения ограничений на результаты массива. Эти функции устарели и больше не поддерживаются в ExceLab 7.0 и более поздних версиях. Теперь вы можете определять произвольные формулы ограничения для результата массива, просто ссылаясь на любое значение массива с помощью DYNVAL . См. примеры преобразования ограничений критериальных функций.

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

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

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

Переменные решения. Переменные решения часто неизвестны при первоначальном подходе к проблеме. Эти переменные обычно представляют собой идентифицируемые «вещи» или входные данные, которыми может управлять менеджер (например, сколько стиральных машин каждой конкретной модели необходимо произвести). Таким образом, цель состоит в том, чтобы определить те значения, которые максимизируют или минимизируют целевую функцию.
Целевая функция: это математическая функция, которая включает переменные решения для выражения целей менеджера. Цель менеджера — либо максимизировать, либо минимизировать целевую функцию.
Ограничения. Это математические функции, которые включают переменные решения для выражения границ возможных решений.
Границы переменных. Переменные решения редко могут принимать любые значения (от минус бесконечности до плюс бесконечности). Вместо этого они обычно имеют границы (например, ≥ 0).

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

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

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

Математическое представление бизнес-задачи Beacon

Пример из управленческого учета

Beacon Co. – производитель стиральных машин. В настоящее время она продает две модели стиральных машин: Arkel и Kallex. В начале каждого производственного цикла компания Beacon должна решить, сколько единиц каждой стиральной машины производить с учетом доступных ресурсов. В предстоящем производственном цикле Beacon сталкивается с ключевыми ограничениями ресурсов. В частности, в наличии всего 3132 часа рабочей силы, 1440 футов резиновых шлангов и 200 бочек.

Продажа каждого устройства Arkel приносит компании прибыль в размере 350 долларов США, а продажа каждого устройства Kallex приносит компании прибыль в размере 300 долларов США. В то же время, для производства каждого блока Arkel требуется 18 часов труда, 6 футов резинового шланга и 1 барабан, а для производства каждого блока Kallex требуется 12 часов труда, 8 футов резинового шланга и 1 барабан. Подробная информация о соответствующих фактах представлена в таблице «Сводная информация о производстве стиральных машин».

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

Сводка производства стиральных машин

Выполнение линейного программирования в Excel

Первым шагом в линейном программировании является разработка математического представления бизнес-задачи и ее моделирование в электронной таблице.Математически проблема в примере может быть представлена, как показано на диаграмме «Математическое представление бизнес-задачи Beacon», где X₁ и X₂ представляют собой переменные решения, которые это количество произведенных единиц Arkel и Kallex соответственно.

Далее мы реализуем математическую модель в электронной таблице Excel. См. таблицу «Модель электронной таблицы» для используемой модели электронной таблицы и таблицу «Формулы Excel» для получения подробной информации о формулах, используемых в модели.

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