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