Содержание
Надстройка Excel Solver выполняет математическую оптимизацию. Это обычно используется для подгонки сложных моделей к данным или для поиска итерационных решений проблем. Например, вы можете захотеть провести кривую по некоторым точкам данных, используя уравнение. Решатель может найти константы в уравнении, которые наилучшим образом соответствуют данным. Другое применение — это то, где трудно изменить модель, чтобы сделать требуемый результат предметом уравнения.
Где Солвер в Excel?
Надстройка Solver включена в Excel, но она не всегда загружается как часть установки по умолчанию. Чтобы проверить, загружено ли оно, выберите ДАННЫЕ вкладку и искать решающее устройство значок в Анализ раздел.
Если вы не можете найти Солвер на вкладке ДАННЫЕ, вам нужно загрузить надстройку:
Выберите ФАЙЛ вкладка, а затем выберите Параметры.
в Параметры диалоговое окно выбора Add-Ins из вкладок на левой стороне.
В нижней части окна выберите Надстройки Excel из управлять выпадающий и выберите Идти…
Установите флажок рядом с решающее устройство и выберите Хорошо.
решающее устройство Теперь команда должна появиться на ДАННЫЕ Вкладка. Вы готовы использовать Солвер.
Использование Солвера в Excel
Давайте начнем с простого примера, чтобы понять, что делает Солвер. Представьте, что мы хотим знать, какой радиус даст круг с площадью 50 квадратных единиц. Мы знаем уравнение для площади круга (A = pi r2). Конечно, мы можем изменить это уравнение, чтобы получить радиус, необходимый для данной области, но для примера давайте представим, что мы не знаем, как это сделать.
Создайте электронную таблицу с радиусом в B1 и рассчитать площадь в Би 2 используя уравнение = Р () * В1 ^ 2.
Мы могли бы вручную настроить значение в B1 до Би 2 показывает значение, которое достаточно близко к 50. В зависимости от того, насколько мы должны быть точными, это может быть практический подход. Однако, если мы хотим быть очень точными, потребуется много времени, чтобы внести необходимые корректировки. На самом деле, именно это и делает Solver. Он вносит коррективы в значения в определенных ячейках и проверяет значение в целевой ячейке:
Выбрать ДАННЫЕ вкладка и решающее устройство, загрузить Параметры решателя диалоговое окно
Установить цель клетка для Района, Би 2. Это значение будет проверяться, настраивая другие ячейки, пока эта ячейка не достигнет правильного значения.
Выберите кнопку для Значение: и установите значение 50. Это значение, которое должно достичь B2.
В коробке под названием Изменяя переменные ячейки: введите ячейку, содержащую радиус, B1.
Оставьте остальные параметры по умолчанию и выберите Решать. Оптимизация выполняется, значение B1 корректируется до тех пор, пока B2 не станет равным 50, и Результаты решателя диалог отображается.
Выбрать Хорошо сохранить решение.
Этот простой пример показал, как работает решатель. В этом случае мы могли бы легче получить решение другими способами. Далее мы рассмотрим некоторые примеры, где Solver дает решения, которые было бы трудно найти другим способом.
Подгонка сложной модели с помощью надстройки Excel Solver
В Excel есть встроенная функция для выполнения линейной регрессии, позволяющая подгонять прямую линию через набор данных. Многие распространенные нелинейные функции могут быть линеаризованы, что означает, что линейная регрессия может использоваться для подбора функций, таких как экспоненты. Для более сложных функций Солвер может использоваться для минимизации наименьших квадратов. В этом примере мы рассмотрим подгонку уравнения вида ах ^ Ь + сх ^ д к данным, показанным ниже.
Это включает в себя следующие шаги:
Расположите набор данных по значениям x в столбце A и значениям y в столбце B.
Создайте 4 значения коэффициента (a, b, c и d) где-нибудь в электронной таблице, им могут быть заданы произвольные начальные значения.
Создайте столбец с подогнанными значениями Y, используя уравнение формы ax ^ b + cx ^ d, которое ссылается на коэффициенты, созданные на шаге 2, и значения x в столбце A. Обратите внимание, что для копирования формулы вниз по столбцу используются ссылки коэффициенты должны быть абсолютными, а ссылки на значения х должны быть относительными.
Хотя это и необязательно, вы можете получить визуальное представление о том, насколько хорошо подходит уравнение, нанося оба столбца y на значения x на одной диаграмме рассеяния XY. Имеет смысл использовать маркеры для исходных точек данных, так как это дискретные значения с шумом, и использовать линию для подобранного уравнения.
Далее нам нужен способ количественной оценки разницы между данными и нашим уравнением. Стандартный способ сделать это — рассчитать сумму квадратов разностей. В третьем столбце для каждой строки исходное значение данных для Y вычитается из подобранного значения уравнения, а результат возводится в квадрат. Итак, в D2, значение определяется как = (С2-В2) ^ 2. Затем вычисляется сумма всех этих квадратов. Поскольку значения возведены в квадрат, они могут быть только положительными.
Теперь вы готовы выполнить оптимизацию с помощью Солвера. Существует четыре коэффициента, которые необходимо отрегулировать (a, b, c и d). У вас также есть единственное объективное значение для минимизации, сумма квадратов разностей. Запустите решатель, как указано выше, и установите параметры решателя для ссылки на эти значения, как показано ниже.
Снимите флажок с Сделайте неограниченные переменные неотрицательными, это заставит все коэффициенты принимать положительные значения.
Выберите «Решить» и просмотрите результаты. Диаграмма обновится, что даст хорошее представление о хорошей подгонке. Если с первой попытки солвер не подходит, вы можете попробовать запустить его снова. Если соответствие улучшилось, попробуйте разрешить из текущих значений. В противном случае, вы можете попробовать вручную улучшить подгонку перед разрешением.
Как только будет получено хорошее соответствие, вы можете выйти из решателя.
Решение модели итеративно
Иногда существует относительно простое уравнение, которое дает результат в терминах некоторого ввода. Однако, когда мы пытаемся перевернуть проблему, невозможно найти простое решение. Например, мощность, потребляемая транспортным средством, приблизительно равна P = av + bv ^ 3 где v — скорость, a — коэффициент сопротивления качению, а b — коэффициент аэродинамического сопротивления. Хотя это довольно простое уравнение, нелегко переставить уравнение скорости, которую транспортное средство достигнет при заданной потребляемой мощности. Однако мы можем использовать Солвер, чтобы итеративно найти эту скорость. Например, найти скорость, достигнутую при подводимой мощности 740 Вт.
Создайте простую электронную таблицу со скоростью, коэффициентами a и b и рассчитанной по ним мощностью.
Запустите Солвер и войдите во власть, B5, как цель. Установите объективное значение 740 и выберите скорость, Би 2, как переменные ячейки для изменения. Выбрать решать чтобы начать решение.
Решатель корректирует значение скорости до тех пор, пока мощность не станет очень близкой к 740, обеспечивая необходимую нам скорость.
Решение моделей таким способом часто может быть быстрее и менее подвержено ошибкам, чем инвертирование сложных моделей.
Понимание различных вариантов, доступных в решателе, может быть довольно сложным. Если у вас возникают трудности с получением разумного решения, часто полезно применить граничные условия к изменяемым ячейкам. Это предельные значения, выше которых они не должны корректироваться. Например, в предыдущем примере скорость не должна быть меньше нуля, и было бы также возможно установить верхнюю границу. Это будет скорость, на которой вы уверены, что автомобиль не может двигаться быстрее, чем. Если вы можете установить границы для ячеек изменяемой переменной, то это также заставит лучше работать другие более продвинутые опции, такие как мультистарт. Это будет запускать несколько разных решений, начиная с разных начальных значений для переменных.
Выбор метода решения также может быть трудным. Simplex LP подходит только для линейных моделей, если проблема не является линейной, она выдаст сообщение о том, что это условие не было выполнено. Два других метода подходят для нелинейных методов. GRG Nonlinear является самым быстрым, но его решение может сильно зависеть от начальных условий запуска. Он обладает такой гибкостью, что не требует, чтобы переменные устанавливали границы. Эволюционный решатель часто является наиболее надежным, но он требует, чтобы все переменные имели как верхнюю, так и нижнюю границы, что может быть затруднительно определить заранее.
Надстройка Excel Solver — очень мощный инструмент, который можно применять для решения многих практических задач. Чтобы полностью использовать возможности Excel, попробуйте объединить Solver с макросами Excel.
