Тверской государственный университет
Опубликован: 13.09.2006 | Доступ: свободный | Студентов: 5132 / 387 | Оценка: 4.23 / 3.83 | Длительность: 28:12:00
Специальности: Программист, Менеджер
Лекция 2:

Excel для математиков

Инструментальное средство Excel - "Решатель"(Solver)

Для транспонирования, умножения и обращения матриц Excel имеет стандартные функции: ТРАНСП, МУМНОЖ и МОБР. Но в инструментарии Excel, доступном пользователю, есть более мощное средство, позволяющее в общем случае решать задачи нелинейного программирования, - Решатель (Solver). Как частный случай, он позволяет, например, найти решение нелинейных уравнений, которые рассматривались в задачах 3 и 4 предыдущей главы, и для решения которых я применял метод Ньютона. Частным случаем для Решателя является и решение систем линейных уравнений.

Общая постановка задачи

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

Найти:

x1, x2, … xn

такие, что:

F(x1, x2, ….xn) -> {Max; Min; =Value}

при ограничениях:

GI(x1, x2, …xn) -> {<= Value; >= Value; = Value}	I = 1…N

Искомые переменные - ячейки рабочего листа Excel - называются регулируемыми (adjustable) ячейками. Целевая функция F(x1, x2, …xn), называемая иногда просто целью, должна задаваться в виде формулы в ячейке рабочего листа. Эта формула может содержать функции, определенные пользователем, и должна зависеть от регулируемых ячеек. В момент постановки задачи определяется, что делать с целевой функцией. Возможен выбор одного из вариантов:

  • найти максимум целевой функции F(x1, x2, …xn) ;
  • найти минимум целевой функции F(x1, x2, …xn) ;
  • добиться того, чтобы целевая функция F(x1, x2, …xn) имела фиксированное значение: F(x1, x2, ….xn) = A.

Функции GI(x1, x2, …xn) называются ограничениями. Их можно задать как в виде равенств, так и неравенств. На регулируемые ячейки можно наложить дополнительные ограничения: положительности и/или целочисленности, тогда искомое решение ищется в области положительных и/или целых чисел.

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

Задача 13 Нахождение корней уравнения

Постановка задачи: Используя Решатель, найти решение уравнения: F(x) = a*x^4 + b*x^3 + c*x^2 + d*x + e =0,

В задачах 3 и 4 предыдущей главы для нахождения корней этого уравнения я применил метод Ньютона, и сам определял процесс построения решения. Покажу теперь, как воспользоваться Решателем. Наша задача без труда укладывается в общую постановку. Целевая функция - уравнение F(x). Требуется найти такое значение регулируемой ячейки x, чтобы функция F(x) приняла фиксированное значение, равное 0. Никаких ограничений на переменную x не накладывается. Так что все, что нужно, - записать в одну из ячеек рабочего листа переменную x, в другую - формулу, задающую функцию F(x), и вызвать Solver (Решатель) из меню Сервис. Вот как это выглядит на рабочем листе:

Вызов Решателя для поиска корней нелинейного уравнения

увеличить изображение
Рис. 2.9. Вызов Решателя для поиска корней нелинейного уравнения

Как видите, в диалоговом окне Решателя:

  • в окне "Установить ячейку цели" (Set Target Cell) задана ссылка на ячейку, содержащую формулу для вычисления значения функции F(x) ;
  • включен переключатель "Значение" (Value of) и задано значение 0 в его окошке;
  • в окне регулируемых ячеек (By Changing Cells) задана ссылка на ячейку, содержащую x ; кстати, можно щелкнуть командную кнопку "Догадка" (Guess), и Решатель автоматически попытается сформировать весь список регулируемых ячеек, от которых зависит целевая функция;
  • так как в нашем случае ограничений задавать не нужно, то больше ничего делать не нужно и можно запустить процесс поиска решения щелчком командной кнопки "Решение" (Solve).

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

Окно Решателя при успешном поиске решения

увеличить изображение
Рис. 2.10. Окно Решателя при успешном поиске решения

Наряду с появлением уведомления об успехе поиска в окне "Отчеты" (Reports) предлагается выбрать любые из трех возможных отчетов по результатам работы. Кроме того, можно сохранить полученные результаты или восстановить первоначальные значения. Можно также запомнить созданную постановку задачи. Вот как выглядит отчет "Результаты" (Answer) в нашем случае:

Отчет "Результаты", уведомляющий о результатах решения

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

Добрый день. Подскажите формулы при решении задачи на рис. 2.2 в лекции №2. Закон Ома, какие должны использоваться формулы для I и R

Курс: Основы офисного программирования и документы Excel

Серегй Лушников
Серегй Лушников