 |
|
Методы поиска решения.
В диалоговом окне Параметры поиска решения можно выбрать любой из указанных ниже алгоритмов или методов поиск решения.
· Нелинейный метод обобщенного понижающего градиента (ОПГ) Используется для гладких нелинейных задач.
· Симплекс-метод Используется для линейных задач.
· Эволюционный метод Используется для негладких задач.
В нашем примере используется подходящий для поставленной задачи метод ОПГ.
Алгоритм решения
Лист1:
При помощи генератора случайных чисел (функция СЛУЧМЕЖДУ) заполняем матрицу коэффициентов. Создаем пустую таблицу, где будут храниться переменные. Используя функцию СУММПРОИЗВ, составляем уравнения (коэффициенты перемножаются с соответствующими переменными и затем складываются). Выше было оговорено, почему было выбрано именно такое количество неизвестных и уравнений. В ячейке целевой функции будет храниться сумма набранных нами уравнений (функция СУММ). Создаем первый макрос. Для этого заходим в надстройку Поиск решения на вкладке Данные. Выбираем ячейку целевой функции, выставляем до «Минимум». В поле «Изменяя ячейки переменных» выделяем нашу заготовленную матрицу неизвестных. Выбираем метод решения ОПГ, находим решение, сохраняем результат. Останавливаем запись макроса1. Очевидно, что данный макрос решает систему уравнений и подбирает переменные. Далее создаем макрос2. После нажатия кнопки записи макроса (в группе Макросы вкладки Вид) выделяем и удаляем содержимое целевой ячейки. Точно также поступаем и с матрицей неизвестных. После этого важно не забыть снова прописать в ячейку целевой функции сумму наших уравнений (так как эта функция СУММ удалится вместе с содержимым ячейки-результатом). Останавливаем запись этого макроса. Макрос2 выполняет очистку ячейки целевой функции и матрицы переменных, таким образом можно пошагово наблюдать за процессом подбора и, конечно, использовать многократно (каждый раз будут подобраны новые значения переменных и соответственно будет изменяться значение целевой функции, так как таблица коэффициентов будет при любых действиях обновляться генератором случайных чисел). Для удобства создадим Макрос3 для перехода на следующий лист. Для этого после начала записи макроса просто щелкнем на Лист2 и остановим запись макроса. Создадим на рабочем Листе1 кнопки и присвоим им созданные макросы. Теперь все действия будут выполняться по нажатию этих кнопок, к тому же при хорошем оформлении они наглядно показывают, где какой макрос содержится, что очень удобно для пользователя.
Данный лист демонстрирует, что программа способна работать с большим количество переменных.
Лист2:
Рассчитан на непосредственный ввод уравнений и их последующее решение в системе. Создаем заготовки для ввода данных. Для наглядности столбец уравнений поделим на два: текстовая форма, где уравнение будет записано в более менее привычной для нас форме, и формульная запись-там будет виден результат, зависящей от значения переменных, а в строке формул уравнение со ссылками на соответствующие ячейки неизвестных. Макрос для кнопки «Поиск решений» здесь по аналогии с Листом1 будет включать в себя использование надстройки Поиск решения, куда будет введена ячейка целевой функции (суммы уравнений в формульной записи), стремящаяся к минимуму. Единственное отличие от Макроса1 состоит в том, что в ограничения нужно добавить область уравнений в формульной записи и сделать их равными нулю, так как при записи уравнений для удобства все переменные и константы мы будем переносить в одну часть. Макрос на очистку ячеек (уравнений и значений переменных) пишется точно также, как и на первом листе. Следует заметить, что для наглядности здесь используется всего 20 переменных, чтобы вся форма без лишней прокрутки была умещена в окне; однако демонстрационный пример доказывает, что программа может работать и с бОльшим количеством неизвестных.
Дизайн-проект задачи
Проект состоит из двух листов:
Лист1:
Как уже было сказано, кнопки запускают соответствующий макрос.
Примечание:
В ячейках переменных, где отображены нули, при желании можно увеличить разрядность, чтобы убедиться в работоспособности программы (подбор всех переменных), однако нам в данном случае не требуется высокая точность, поэтому трех знаков после запятой достаточно.
Лист2:
Аналогично с Листом1, кнопки запускают соответствующие макросы. Также здесь намеренно не показана ячейка целевой функции, так как задача состоит в решении системы, то есть в нахождении неизвестных (столбец Значения).
Нули в уравнении в формульной записи, напротив текстовой формы, доказывают, что значения подобраны верно.
Данный пример показывает работоспособность программы с нелинейными системами уравнений.
Текст макросов
Так как макросы второго листа аналогичны первому, то здесь приведу пример лишь макросов первого листа.
Sub Макрос1()
Range("Q14:U16").Select
SolverOk SetCell:="$Q$14", MaxMinVal:=2, ValueOf:=0, ByChange:="$A$16:$J$25", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.000001, Convergence:= _
0.0001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
:=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=30
SolverOk SetCell:="$Q$14", MaxMinVal:=2, ValueOf:=0, ByChange:="$A$16:$J$25", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$Q$14", MaxMinVal:=2, ValueOf:=0, ByChange:="$A$16:$J$25", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub
Sub Макрос2()
Range("Q14:U16").Select
Selection.ClearContents
Range("A16:J25").Select
Selection.ClearContents
Range("Q14:U16").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-11]C[-5]:R[-2]C[4])"
End Sub
Sub Макрос3()
Sheets("Лист2").Select
End Sub
Функции, выполняемые данными макросами, оговаривались выше: расчет (подбор), очистка ячеек, переход на другой лист.
Заключение
В ходе курсовой работы был подробно изучен метод Поиска решения и его использование в макросах. Важно отметить, что по умолчания в Excel макросы не работают с Поиском решения. Для корректной работы и избежания ошибок необходимо зайти в код какого-либо из макросов, остановить запущенный макрос квадратной кнопкой Reset. Далее зайти на вкладку Tools, открыть References и поставить галочку напротив Solver. Нажать OK и выйти. После данной процедуры проблем взаимодействия макросов с надстройкой Поиск решения у меня не возникло.
Я посчитал лишним прикреплять к моей курсовой работе руководство работы, так как она оснащена наглядным удобным интерфейсом, поэтому даже у неопытных пользователей не возникнет вопросов по ее использованию.
Данная работа дала мне возможность углубить свои навыки работы в среде VBA Excel, а также позволила освежить знания по работе с функциями Excel и подбором переменных через надстройку Поиск решения.
Список использованной литературы
1. Гарбер Г.З. Основы программирования на Visual Basic и VBA в Excel.
2. Супрун А.Н. Сборник расчетных работ для студентов всех специальностей. Изучающих курсы «Информатика». «Вычислительная математика». –Н.Н.: Полиграфический центр ННГАСУ, 2005. -34с.
3. Бахвалов Н.С. Численные методы. –М.: Наука, 1975. -631с.