Что такое Excel Solver?

2024 Автор: Abigail Brown | [email protected]. Последнее изменение: 2023-12-17 06:59

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

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

Надстройка Solver включена в Excel, но не всегда загружается как часть установки по умолчанию. Чтобы проверить, загружен ли он, выберите вкладку DATA и найдите значок Solver в разделе Analysis..

Если вы не можете найти Солвер на вкладке ДАННЫЕ, вам необходимо загрузить надстройку:

1. Выберите вкладку ФАЙЛ, а затем выберите Опции.

   

2. В диалоговом окне Опции выберите Надстройки на вкладках с левой стороны.

   

3. В нижней части окна выберите Надстройки Excel в раскрывающемся списке Управление и выберите Перейти…

   

4. Установите флажок рядом с Надстройка Solver и выберите OK.

   

5. Команда Solver должна появиться на вкладке DATA. Вы готовы использовать Solver.

   

Использование Solver в Excel

Давайте начнем с простого примера, чтобы понять, что делает Солвер. Представьте, что мы хотим узнать, какой радиус даст круг площадью 50 квадратных единиц. Мы знаем уравнение площади круга (A=pi r²). Мы могли бы, конечно, изменить это уравнение, чтобы получить радиус, необходимый для данной области, но для примера давайте представим, что мы не знаем, как это сделать.

Создайте таблицу с радиусом в B1 и рассчитайте площадь в B2, используя уравнение =pi()B1^2.

Мы могли бы вручную настроить значение в B1 до тех пор, пока B2 не покажет значение, достаточно близкое к 50. В зависимости от того, насколько мы точны, должно быть, это может быть практический подход. Однако, если нам нужно быть очень точными, потребуется много времени, чтобы внести необходимые корректировки. Собственно, это то, что делает Solver. Он корректирует значения в определенных ячейках и проверяет значение в целевой ячейке:

1. Выберите вкладку ДАННЫЕ и Решатель, чтобы загрузить диалоговое окно Параметры решателя

2. Установите цель в ячейке Area, B2. Это значение будет проверяться, корректируя другие ячейки, пока эта не достигнет правильного значения.

   

3. Выберите кнопку для Value of: и установите значение 50. Это значение, которое должен достичь B2.

   

4. В поле Изменяя ячейки переменных: введите ячейку, содержащую радиус, B1..

   

5. Оставьте остальные параметры такими, какие они есть по умолчанию, и выберите Решить. Выполняется оптимизация, значение B1 регулируется до тех пор, пока B2 не станет равным 50, и отобразится диалоговое окно Результаты решения.

   

6. Выберите OK, чтобы сохранить решение.

   

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

Подгонка сложной модели с помощью надстройки Excel Solver

Excel имеет встроенную функцию для выполнения линейной регрессии, прокладывая прямую линию через набор данных. Многие распространенные нелинейные функции могут быть линеаризованы, что означает, что линейную регрессию можно использовать для подбора таких функций, как экспоненциальные. Для более сложных функций можно использовать Solver для выполнения «минимизации методом наименьших квадратов». В этом примере мы рассмотрим подгонку уравнения вида ax^b+cx^d к данным, показанным ниже.

Это включает в себя следующие шаги:

1. Упорядочить набор данных со значениями x в столбце A и значениями y в столбце B.
2. Создайте 4 значения коэффициента (a, b, c и d) где-нибудь в электронной таблице, им можно присвоить произвольные начальные значения.

3. Создайте столбец подогнанных значений Y, используя уравнение вида ax^b+cx^d, которое ссылается на коэффициенты, созданные на шаге 2, и значения x в столбце A. Обратите внимание, что для копирования формулы вниз столбец, ссылки на коэффициенты должны быть абсолютными, а ссылки на значения x должны быть относительными.

   

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

   

5. Далее нам нужен способ количественной оценки разницы между данными и нашим подобранным уравнением. Стандартный способ сделать это - вычислить сумму квадратов разностей. В третьем столбце для каждой строки исходное значение данных для Y вычитается из значения подобранного уравнения, а результат возводится в квадрат. Итак, в D2 значение задается как =(C2-B2)^2 Затем вычисляется сумма всех этих квадратов значений. Поскольку значения возводятся в квадрат, они могут быть только положительными.

   

6. Теперь вы готовы выполнить оптимизацию с помощью Солвера. Необходимо скорректировать четыре коэффициента (a, b, c и d). У вас также есть единственное целевое значение, которое нужно минимизировать, сумма квадратов разностей. Запустите решатель, как указано выше, и установите параметры решателя для ссылки на эти значения, как показано ниже.

   

7. Снимите флажок с опции Сделать неотрицательные переменные без ограничений, это заставит все коэффициенты принимать положительные значения.

   

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

   

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

Итеративное решение модели

Иногда существует относительно простое уравнение, которое дает результат в терминах некоторого ввода. Однако, когда мы пытаемся инвертировать проблему, невозможно найти простое решение. Например, мощность, потребляемая транспортным средством, приблизительно определяется как P=av + bv^3, где v - скорость, a - коэффициент сопротивления качению, а b - коэффициент аэродинамическое сопротивление. Хотя это довольно простое уравнение, его нелегко изменить, чтобы получить уравнение скорости, которой транспортное средство достигнет при заданной потребляемой мощности. Однако мы можем использовать Solver, чтобы итеративно найти эту скорость. Например, найти скорость, достигаемую при подводимой мощности 740 Вт.

1. Настройте простую электронную таблицу со скоростью, коэффициентами a и b и рассчитанной на их основе мощностью.

   

2. Запустите Решатель и введите мощность B5 в качестве цели. Установите целевое значение 740 и выберите скорость B2 в качестве изменяемой ячейки переменной. Выберите solve, чтобы начать решение.

   

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

   

4. Решение моделей таким способом часто может быть быстрее и менее подвержено ошибкам, чем инвертирование сложных моделей.

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

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

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