Добавление ограничения в Excel

Обновлено: 21.11.2024

Большинство программ для работы с электронными таблицами позволяют решать небольшие задачи линейного программирования. В Excel есть инструмент под названием SOLVER, который решает различные задачи математического программирования. В этой заметке описывается использование SOLVER для решения задачи линейного программирования. Для этого вы (1) создаете представление модели на рабочем листе; (2) определить проблему надстройки решателя (3) решить проблему; (4) просмотреть и/или распечатать результаты и (5) сохранить задачу и/или результаты.
1. Представление модели на рабочем листе:
Задачу можно представить самыми разными способами. Жестких требований к форме нет. Единственное требование состоит в том, что у вас есть ячейки для проблемных переменных; формула, представляющая целевую функцию; и формулы, представляющие левые части ограничений. Хотя возможны многие форматы, один из широко используемых форматов упорядочивает представление проблемы в виде электронной таблицы в том виде, в котором она обычно записывается. т. е. строки, представляющие ограничения, и столбцы, представляющие переменные. Например, для решения рассмотрите следующий рабочий лист для той же проблемы

Здесь столбец A используется для описательных меток для различных строк; B и C используются для двух переменных. Строка 2 содержит коэффициенты целевой функции, количество строк 3 (решение) и, наконец, строки 7-11 представляют ограничения. Целевая функция представлена формулой = B2*B3 + C2*C3 в ячейке D5 (или, что лучше, СУММПРОИЗВ(B2.C2,$B$3.$C$3), которая вводится в ячейку D5. Столбец с меткой LHS содержит формулы для вычисления левых частей соответствующих ограничений, например, D7 имеет формулу =B3*B7+ C3*C7 (или форму СУММПРОИЗВ) и т. д. На самом деле, если вы запишете формулу в D7 как СУММПРОИЗВ(B2..C2 ,$B$3..$C$3), вы можете просто скопировать его в ячейки с D8 по D11. Наконец, столбец с меткой RHS содержит правые части или константы ограничений.

<р>2. Определение проблемы для Солвера.
Вы должны определить проблему для решателя, указав несколько вещей:

Ячейка целевой функции (в примере это ячейка D5).
Максимизируете вы или минимизируете (здесь Макс)
Диапазон ячеек, содержащих проблемные переменные (термин Excel — изменение ячеек переменных). В примере это диапазон B3..C3.
Формулы ограничений. Формулы Excel, представляющие левые части (LHS) ограничений. (В примере это диапазон D7..D11).

Совет по скорости: вы можете ввести более одного ограничения одновременно, если все они имеют одинаковое направление. Например, вы могли бы ввести первые два ограничения вместе за одну операцию. В диалоговом окне «Добавить ограничение» в «поле ссылки на ячейку» введите D7..D8, выберите «Последнее, что нужно сделать, это выбрать в разделе «Выбрать метод решения» «Симплексный LP» и установить флажок «Сделать неограниченные переменные неотрицательными». Для решения проблемы нажмите кнопку Решить. Вы можете получить одно из следующих трех сообщений:

"Решатель нашел решение. Все ограничения и условия оптимальности выполнены" Нормальное завершение - проблема решена.

"Решатель не смог найти приемлемое решение:" означает, что нет ни одного набора значений, удовлетворяющего всем ограничениям — неразрешимая проблема

3 отчета
В случае нормального завершения, в дополнение к оптимальному ответу на самом рабочем листе, Solver предоставляет дополнительный вывод по текущей проблеме, который предоставляется в виде рабочих листов Excel для просмотра, сохранения или печати. В диалоговом окне «Результаты решения» выберите любой или все три отчета («Ответ», «Чувствительность», «Пределы») и нажмите «ОК».

Модель решателя Excel настраивается с помощью диалогового окна "Параметры решателя". Вы используете поле «Установить цель», чтобы указать целевую ячейку, и вы используете группу «Кому», чтобы сообщить Excel Solver, что вы хотите от целевой ячейки: максимально возможное значение; минимально возможное значение; или конкретное значение. Наконец, вы используете поле «Изменяя ячейки переменных», чтобы указать ячейки, которые Solver может использовать для подстановки значений для оптимизации результата.

Оптимизация результатов Excel Solver

Когда Solver найдет решение, вы можете выбрать «Сохранить решение Solver» или «Восстановить исходные значения». Если вы выберете «Сохранить решение решения», Excel навсегда изменит рабочий лист. Вы не можете отменить изменения.

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

Excel открывает диалоговое окно "Параметры решателя".

Обратите внимание: если щелкнуть ячейку, чтобы войти в нее, Solver автоматически введет абсолютный адрес ячейки (например, $B$14 вместо B14 ). Решатель работает нормально в любом случае.

Макс.: возвращает максимально возможное значение.
Мин.: возвращает минимально возможное значение.
Значение: введите число, чтобы установить ячейку цели на это число.

Для примера модели было выбрано значение Value Of и введено 0 в текстовом поле.

В этом примере меняющиеся ячейки — это B4 и C4. На следующем изображении показано заполненное диалоговое окно «Параметры решателя».

Заполненное диалоговое окно "Параметры решателя".

Решатель приступает к делу. По мере того как Solver работает над проблемой, вы можете увидеть, что диалоговые окна Show Trial Solution появляются один или несколько раз.

По завершении оптимизации Excel отображает диалоговое окно "Результаты решения".

Диалоговое окно "Результаты решения" и решение проблемы безубыточности.

Если вы не хотите принимать результат, вместо этого выберите параметр "Восстановить исходные значения".

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

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

Excel Solver может использовать один из нескольких методов решения. В диалоговом окне «Параметры решателя» в списке «Выбор метода решения» выберите один из следующих вариантов:

Simplex LP: используйте, если модель вашего листа является линейной. Проще говоря, линейная модель — это модель, в которой переменные не возводятся ни в какие степени и не используются ни одна из так называемых трансцендентных функций, таких как SIN и COS.
GRG Nonlinear: используйте, если модель вашего рабочего листа нелинейная и гладкая. В общих чертах, гладкая модель – это модель, в которой на графике используемого уравнения нет острых краев или разрывов.
Эволюционная: используйте, если модель вашего рабочего листа нелинейная и негладкая.

Добавление ограничений в Excel Solver

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

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

Такие ограничения и условия являются примерами того, что Solver называет ограничениями. Добавление ограничений указывает Solver найти решение, чтобы эти условия не нарушались.

Вот как запустить Solver с ограничениями, добавленными в оптимизацию:

Excel открывает диалоговое окно "Параметры решателя".

Excel отображает диалоговое окно "Добавить ограничение".

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

В большинстве случаев вы используете оператор сравнения, например, равно (=) или больше (>). Используйте оператор int (integer), когда вам нужно, чтобы ограничение, такое как общее количество сотрудников, было целым числом, а не действительным числом (то есть числом с десятичным компонентом; у вас не может быть 10,5 сотрудников!). Используйте оператор bin (binary), если у вас есть ограничение, которое должно быть TRUE или FALSE (либо 1, либо 0).

На этом изображении показан пример заполненного диалогового окна "Добавить ограничение". В примере модели это ограничение говорит Солверу найти решение, при котором прибыль от продукта надувной мишени для дротиков (ячейка B12) равна 0.

Заполненное диалоговое окно "Добавить ограничение".

Например, вы добавляете ограничение, которое требует, чтобы прибыль от продукта Dog Polisher (ячейка C12) была равна 0.

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

На изображении ниже показан пример безубыточного решения с добавленными ограничениями.Обратите внимание, что значение 0 установлено не только в ячейке "Общая прибыль" (B14), но и в двух ячейках "Прибыль от продукта" (B12 и C12).

Диалоговое окно "Результаты решения" и окончательное решение проблемы безубыточности.

Если вы не хотите принимать результат, вместо этого выберите параметр «Восстановить исходные значения».

Вы можете добавить не более 100 ограничений. Кроме того, если вам необходимо внести изменения в ограничение перед началом решения, выберите ограничение в поле списка Subject to the Constraints, нажмите кнопку «Изменить», а затем внесите необходимые изменения в появившемся диалоговом окне «Изменить ограничение». Если вы хотите удалить ограничение, которое вам больше не нужно, выберите его и нажмите «Удалить».

Сохранить решение Excel Solver как сценарий

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

Выполните следующие действия, чтобы сохранить решение Solver в виде сценария:

Excel открывает диалоговое окно "Параметры решателя".

По завершении оптимизации Excel отображает диалоговое окно "Результаты решения".

Excel отображает диалоговое окно "Сохранить сценарий".

Excel возвращает вас в диалоговое окно "Результаты решения".

Если вы не хотите принимать результат, вместо этого выберите параметр «Восстановить исходные значения».

Об этой статье

Эта статья взята из книги:

Об авторе книги:

Пол МакФедрис — настоящий фанат эпохи Возрождения. Он был программистом, консультантом, разработчиком баз данных и создателем веб-сайтов. Он также является автором более 90 книг, в том числе бестселлеров по Windows, Office и macOS.

В этом руководстве объясняется, как добавить и где найти Решатель в разных версиях Excel с 2016 по 2003 год. В пошаговых примерах показано, как использовать Решатель Excel для поиска оптимальных решений для линейного программирования и других задач. проблемы.

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

В этом руководстве мы рассмотрим все основные аспекты надстройки Excel Solver и предоставим пошаговое руководство по ее наиболее эффективному использованию.

Что такое Excel Solver?

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

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

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

Как добавить решатель в Excel

Надстройка "Поиск решения" включена во все версии Microsoft Excel, начиная с 2003, но по умолчанию она не включена.

Чтобы добавить Solver в Excel, выполните следующие действия:

Чтобы установить Solver в Excel 2003, перейдите в меню Инструменты и выберите Надстройки. В списке Надстройки доступные установите флажок Надстройка Solver и нажмите ОК. р>

Примечание. Если в Excel отображается сообщение о том, что надстройка «Поиск решения» в настоящее время не установлена на вашем компьютере, нажмите Да, чтобы установить ее.

Где находится Solver в Excel 2019, 2016, 2013, 2010 или 2007?

В современных версиях Excel кнопка "Решатель" отображается на вкладке Данные в группе Анализ:

Где находится Solver в Excel 2003?

После загрузки надстройки "Поиск решения" в Excel 2003 ее команда добавляется в меню Инструменты:

Теперь, когда вы знаете, где найти Solver в Excel, откройте новый лист и приступим!

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

Как использовать Solver в Excel

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

Проблема. Предположим, вы владелец салона красоты и планируете предоставлять своим клиентам новую услугу. Для этого вам необходимо купить новое оборудование стоимостью 40 000 долларов США, которое необходимо оплатить в рассрочку в течение 12 месяцев.

Цель. Рассчитать минимальную стоимость услуги, которая позволит вам оплатить новое оборудование в указанные сроки.

Для этой задачи я создал следующую модель:

А теперь давайте посмотрим, как Excel Solver может найти решение этой проблемы.

1. Запустить решатель Excel

На вкладке Данные в группе Анализ нажмите кнопку Решатель.

2. Определите проблему

Откроется окно Параметры решателя, в котором необходимо настроить 3 основных компонента:

Ячейка цели
Ячейки переменных
Ограничения

Что именно делает Excel Solver с указанными выше параметрами? Он находит оптимальное значение (максимальное, минимальное или заданное) для формулы в ячейке Цель путем изменения значений в ячейках Переменная и с учетом ограничений в ячейке Ограничения ячеек.

Цель

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

В этом примере целевой ячейкой является B7, которая рассчитывает срок платежа по формуле =B3/(B4*B5), и результат формулы должен быть равен 12:

Ячейки переменных

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

В этом примере у нас есть несколько ячеек, значения которых можно изменить:

Прогнозируемое количество клиентов в месяц (B4) должно быть меньше или равно 50; и
Стоимость услуги (B5), которую мы хотим рассчитать с помощью Excel Solver.

Совет. Если переменные ячейки или диапазоны в вашей модели не являются смежными, выберите первую ячейку или диапазон, а затем нажмите и удерживайте клавишу Ctrl при выборе других ячеек и/или диапазонов. Или введите диапазоны вручную, разделив их запятыми.

Ограничения

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

Чтобы добавить ограничения, выполните следующие действия:

Нажмите кнопку "Добавить" справа от поля "С учетом ограничений".

В окне Ограничение введите ограничение.
Нажмите кнопку "Добавить", чтобы добавить ограничение в список.

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

Excel Solver позволяет указать следующие отношения между указанной ячейкой и ограничением.

Меньше или равно, равно и больше или равно.Вы устанавливаете эти отношения, выбирая ячейку в поле Ссылка на ячейку, выбирая один из следующих знаков: =, а затем вводя число, ссылку на ячейку/имя ячейки или формулу в поле Ограничение. (см. скриншот выше).
Целое число. Если ячейка, на которую указывает ссылка, должна быть целым числом, выберите целое число, и целое слово появится в поле Ограничение.
Разные значения. Если каждая ячейка в диапазоне, на который указывает ссылка, должна содержать разные значения, выберите dif, и слово AllDifferent появится в поле Ограничение.
Двоичный. Если вы хотите ограничить ссылочную ячейку значением 0 или 1, выберите bin, и слово двоичный появится в поле Ограничение.

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

В диалоговом окне Параметры решателя щелкните ограничение.
Чтобы изменить выбранное ограничение, нажмите Изменить и внесите нужные изменения.
Чтобы удалить ограничение, нажмите кнопку Удалить.

B3=40000 – стоимость нового оборудования составляет 40 000 долларов США.
Ячейки переменных B4 (B7:E8).
Каковы ограничения? Запасы, доступные на каждом складе (I7:I8), не могут быть превышены, и должно быть доставлено количество, заказанное каждым клиентом (B10:E10). Это ограниченные ячейки .
Какова цель? Минимальная общая стоимость доставки. А это наша ячейка цели (C12).

Следующее, что вам нужно сделать, это рассчитать общее количество товаров, отгруженных с каждого склада (G7:G8), и общее количество товаров, полученных каждым клиентом (B9:E9). Вы можете сделать это с помощью простых формул суммы, показанных на снимке экрана ниже. Кроме того, вставьте формулу СУММПРОИЗВ в C12, чтобы рассчитать общую стоимость доставки:

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

td> < /tr>

Имя диапазона	Ячейки	Параметр решателя
Products_shipped	B7:E8	Ячейки переменных
Доступно	I7:I8	Ограничение
Total_shipped	G7:G8	Ограничение
По порядку	B10:E10	Ограничение
Всего_получено	B9:E9	Ограничение
Стоимость_доставки	C12	Цель

Последнее, что вам осталось сделать, это настроить параметры Excel Solver:

Цель: Shipping_cost установить на минимум
Ячейки переменных: Products_shipped
Ограничения: Total_received = Ordered and Total_shipped

39 комментариев к "Как использовать Solver в Excel с примерами"

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

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

Поиск решения – это решение Excel, используемое для анализа "что, если". Excel Solver похож на Goal Seek тем, что работает в обратном направлении для достижения числовой цели путем изменения переменных.

Однако его возможности превосходят возможности Goal Seek. Решатель позволяет использовать до 200 переменных ячеек, чтобы найти оптимальное значение для целевой ячейки, в отличие от ограничения Goal Seek по одной переменной за раз.

Кроме того, параметр Solver «Ограничения» добавляет сложности инструменту, поскольку вы можете указать Excel, что он может и чего не может делать при поиске оптимального значения.

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

Как добавить решатель в Excel

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

Если вы его не видите, сделайте следующее:

<р>1. На вкладке "Файл" нажмите "Параметры".

<р>2. В разделе «Надстройки» выберите «Надстройка решения» и нажмите кнопку «Перейти».

<р>3. Проверьте надстройку Solver и нажмите OK.

Команда "Решатель" теперь отображается на вкладке "Данные".

Загрузите бесплатный файл практики!

Используйте этот бесплатный файл Excel Solver, чтобы практиковаться вместе с учебным пособием.

Как добавить решатель в Excel (Mac)

В поле «Доступные надстройки» установите флажок «Надстройка решения». Нажмите "ОК".

Если надстройка Solver не указана в списке доступных надстроек, нажмите кнопку «Обзор», чтобы найти ее.

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

Как использовать Solver — пример

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

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

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

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

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

Предположим, что у нас есть три недели на выполнение этих проектов и 10 сотрудников. Все, что нам нужно сделать, это распределить 1065 часов на три недели и распределить эти часы между 10 сотрудниками.

Ячейки с H11 по H20 настроены для расчета количества часов работы оператора в каждой строке.

Ячейка H21 предназначена для расчета общего количества часов для всех операторов.

В качестве менеджера по персоналу вы можете поэкспериментировать с несколькими сценариями, например:

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

Чтобы упростить работу с диапазонами ячеек, мы создали именованные диапазоны следующим образом:

Agt_Hrs (ячейки H11:H20) и
Сдвиги (ячейка B11:G20).

Сформулировать модель

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

Давайте определим первые три термина в этом окне.

Установить цель:

Это поле относится к ячейке, которая будет содержать один результат, поддающийся количественной оценке.

До макс./мин./значения:

Решите, хотите ли вы найти минимальное, максимальное или точное значение.

путем изменения ячеек переменных:

Это ячейки, которые Excel может изменить для достижения цели. Excel Solver позволяет указать до 200 переменных ячеек.

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

Давайте рассмотрим распределение необходимых 1065 часов примерно на три недели. Это будет означать планирование 355 часов каждую неделю. Выберите переключатель Значение: и введите 355.

Изменяя переменные ячейки: будет диапазон, который мы назвали Shifts.

Ограничения

Теперь мы можем перейти к ограничениям, то есть к тому, что может и что не может делать Excel при попытке решить проблему. Ограничения в Solver относятся к сфере:

Значение в этой ячейке должно быть больше или равно ….

Нажмите "Добавить", чтобы добавить другое ограничение.

Изучите другие полезные инструменты Excel с помощью нашего курса Excel Basic и Advanced уже сегодня!

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