Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
Установить целевую ячейку. 199 4) Нажать кнопку Добавить, чтобы ввести ограничения задачи. 5) В раскрывшемся при этом окне диалога Добавление ограничения указать: а) В левом поле редактирования Ссылка на ячейку – адрес ячейки или диапазона ячеек, на значения которых необходимо наложить ограни- чение. В нашем случае – это ячейка A9. б) В правом поле редактирования Ограничение – число, формулу, ссылку на ячейку или диапазон ячеек, задающие ограничения. Для нашего примера – это ячейка B9. в) В раскрывающемся списке – тип соотношения между левой и правой частями ограничения. Этот список состоит из таких элементов: <= – меньше или равно, = – равно, >= – больше или равно, цел – переменные могут принимать только целочисленные значения, двоич – переменные могут быть только двоичными, т.е. принимать значения 0 или 1. В нашем примере это равно (=). г) Нажать кнопку: Добавить – чтобы добавить очередное ограничение и, не возвращаясь в окно диалога Поиск решения, при- ступить к формированию следующего ограничения. OK – чтобы закончить ввод ограничений и вернуться в окно диалога Поиск решения. В нашем примере необходимо щелкнуть по кнопке OK. При этом сформированное ограничение появится в списке Ограничения. Довольно часто в процессе поиска решения задач оптимизации при- ходится изменять их ограничения. Для этого в окне диалога Поиск решения предназначены кнопки Удалить и Изменить. Они позво- ляют удалять или изменять, соответственно, выделенное в списке ог- раничение. В последнем случае раскрывается окно диалога Измене- ние ограничения, аналогичное окну диалога Добавление ограниче- ния, только со всеми заполненными полями. 6) Нажать кнопку Параметры. 200 7) В раскрывшемся окне диалога Параметры поиска решения: а) выставить флажок Неотрицательные значения для того, чтобы учесть граничные условия задачи, и б) нажать кнопку OK. 8) Окно диалога Параметры поиска решения закроется, и на экране вновь появится диалоговое окно Поиск решения, в котором необходи- мо нажать кнопку Выполнить для того, чтобы начать процесс поиска решения. Слишком затянувшийся процесс поиска решения можно прервать, нажав клавишу Esc. 4. По окончании решения задачи найденные значения будут занесены в таблицу, и в появившемся окне диалога Результаты поиска решения необходимо: 1) выставить переключатель в положение • Сохранить найденное решение, чтобы сохранить полученные зна- чения, или • Восстановить исходные значения – чтобы оставить без изменения значения, которые ранее были на рабочем листе. 2) Нажать кнопку OK. Для нашего примера решение, найденное с помощью надстройки Поиск решения, полностью соответствует сделанному в начале предположению. Рассмотренный выше пример проектирования бака показал, что: 1. Надстройка Поиск решения предназначена для решения оптимизационных задач. 2. Ее применение представляет собой двухэтапную процедуру, в которой не- обходимо «всего лишь» соответствующим образом заполнить: а) электронную таблицу, и б) окно диалога Поиск решения. То есть, для того чтобы воспользоваться надстройкой Поиск решения, необходимо «всего лишь» правильно заполнить электронную таблицу, и одноименное окно диалога. Поэтому далее, на различных примерах, «всего лишь» более подробно рассмотрим, как необходимо создавать исходную элек- тронную таблицу, и заполнять окно диалога Поиск решения, для решения са- мых разнообразных задач, а также параметры, которые на них влияют. 201 9.1. Параметры поиска решения Параметры работы надстройки Поиск решения устанавливаются при помощи диалогового окна Параметры поиска решения. В нем можно прове- рить и уточнить параметры поиска решения для линейных и нелинейных за- дач, а также загружать и сохранять оптимизируемые модели. Оно активизируется по нажатию кнопки Параметры в окне диалога Поиск решения. Окно диалога Параметры поиска решения содержит сле- дующие элементы управления, которые имеют такие значения: 1. Поле редактирования Максимальное время содержит макси- мальное время в секундах, которое может быть затрачено на поиск решения. Оно не может быть больше 32767 секунд. 2. Поле редактирования Предельное число итераций содержит мак- симальное число итераций, которое может быть выполнено при по- иске решения задачи. Одна итерация заключается в вычислении оче- редного значения независимых переменных и целевой функции, а также проверки, насколько это значение подходит в качестве ре- зультата. 3. Поле редактирования Относительная погрешность задает точ- ность вычисления ограничений. Она должна находиться в интервале 0÷1. 4. Поле редактирования Допустимое отклонение используется только для целочисленных задач и показывает насколько близко, в процентном отношении, должен быть ответ к возможному наилуч- шему решению. 5. Поле редактирования Сходимость применяется только для нели- нейных задач и показывает, что если за последние пять итераций от- носительное изменение значения целевой ячейки будет меньше этого 202 числа, поиск прекращается. Это поле должно содержать значение из диапазона 0÷1. 6. Флажок Линейная модель служит для ускорения поиска решения задач линейной оптимизации. Линейной является модель, в которой целевая функция и ограничения линейны относительно своих пере- менных. Для решения линейной и нелинейной задач оптимизации используются различные методы поиска. 7. Флажок Неотрицательные значения устанавливает граничные условия неотрицательности всех переменных, для которых не заданы явные ограничения в виде неравенств. 8. Флажок Автоматическое масштабирование служит для включе- ния автоматической нормализации переменных, значения которых отличаются на несколько порядков. Например, оптимизация прибыли исчисляемой в процентах по отношению к суммам, исчисляемых в миллионах денежных единиц. 9. Флажок Показывать результаты операций служит для приоста- новки процесса поиска решения и просмотра результатов отдельных итераций. При этом появляется окно диалога Текущее состояние поиска решения, в котором для продолжения поиска решения необ- ходимо каждый раз нажимать кнопку Продолжить, а для останова – Стоп. 10. Групповой переключатель Оценки служит для выбора метода экст- раполяции используемого для получения оценок значений перемен- ных в каждом одномерном поиске. Может принимать два значения: • линейная – для использования линейной экстраполяции вдоль ка- сательного вектора. • квадратичная – для использования квадратичной экстраполяции, которая дает лучшие результаты при решении нелинейных задач. 11. Групповой переключатель Разности служит для указания метода численного дифференцирования, который используется для вычисле- ния частных производных целевой функции и ограничений. Он мо- жет принимать два значения: • прямые – используется в большинстве задач, где скорость изме- нения ограничений относительно невысока. 203 • центральные – используется для функций, имеющих разрывы производной. Данный способ требует больше вычислений, однако его применение может быть оправданным, если выдается сообщение о том, что получить более точное решение не удается. 12. Групповой переключатель Метод поиска служит для выбора алго- ритма нелинейной оптимизации. Он имеет два значения: • Ньютона – запрашивает больше памяти, но выполняет меньше итераций, чем в методе сопряженных градиентов. • сопряженных градиентов – запрашивает меньше памяти, но выполняет больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно велика, и необходимо экономить память, а также, если итерации дают слишком малое отличие в последовательных приближениях. 13. Кнопка Сохранить модель служит для отображения на экране диа- логового окна Сохранить модель, в котором можно задать ссылку на область ячеек, предназначенную для хранения модели оптимизации. Данный вариант предусмотрен для хранения на рабочем листе более одной модели оптимизации. Первая модель сохраняется автоматически. Число необходимых для хранения модели ячеек на три больше, чем количество ограничений. При последующем выборе рабочего листа и активизации окна диалога Поиск решения его элементы управления будут содержать те же пара- метры, которые были установлены во время его последнего открытия. 14. Кнопка Загрузить модель служит для отображения на экране диало- гового окна Загрузить модель, аналогичного окну диалога Сохра- нить модель. В нем можно указать ссылку на область ячеек, содер- жащих загружаемую модель. Значения и состояния элементов управления окна диалога Параметры поиска решения, используемые по умолчанию, подходят для решения боль- шинства практических задач. 204 9.2. Сохранение сценариев и создание отчетов После того, как решение найдено, появляется окно диалога Результаты поиска решения, которое имеет следующий вид: С его помощью можно сохранить ссылки на изменяемые ячейки, чтобы можно было их использовать затем в окне диалога Диспетчер сценариев. Для этого необходимо: 1. Нажать кнопку Сохранить сценарий. 2. В появившемся окне диалога Сохранение сценария в поле редактирования Название сценария ввести имя, под которым будут сохранены на рабочем листе значения, содержащиеся в изменяемых ячейках. 3. Нажать кнопку OK. Таким способом можно сохранить несколько вариантов решения, а за- тем, с помощью Диспетчера сценариев, просмотреть, и сравнить их. Текущее состояние изменяемых ячеек на очередной итерации в качестве поименован- ного сценария можно сохранить также по нажатию кнопки Сохранить сцена- рий в окне диалога Текущее состояние поиска решения во время выполне- ния поиска в пошаговом режиме. Отчеты служат для более глубокого анализа полученного решения. Они бывают трех типов: 1. Результаты – состоит из целевой ячейки и списка влияющих ячеек, их исходных и конечных значений, а также списка ограничений и дополнительных сведений о них. 2. Устойчивость – содержит сведения о чувствительности решения к малым изменениям в функции цели и ограничениях. Такие отчеты не создаются для целочисленных моделей. В случае нелинейных моделей отчет содержит данные градиентов и множителей Лагранжа. 205 3. Пределы – состоит из целевой ячейки и списка влияющих ячеек, их значений, а также нижних и верхних границ. Такой отчет не создается для целочисленных моделей. Нижним пределом является наименьшее зна- чение, которое может содержать влияющая ячейка, в то время как значения всех остальных влияющих ячеек фиксированы и удовлетворяют наложен- ным ограничениям. Соответственно, верхним пределом называется наи- большее значение. Необходимые типы отчетов выбираются в окне диалога Результаты поиска решения с помощью элементов списка Тип отчета. Каждый из них создается на отдельном листе. Соответствующие отчеты для рассмотренного ранее Примера 9.1 приведены далее: 206 9.3. Неоднозначность решений Прогнозируемое решение, полученное в предыдущем примере, не может служить гарантией того, что все решения, получаемые с помощью надстройки Поиск решения, действительно являются оптимальными. В подтверждение этому рассмотрим следующий пример. Пример 9.2 Найти максимум функции ( ) 2 x x f = , если x принадлежит диапазону [ ] 1 , 2 − В начале построим математическую модель поставленной задачи: 1 2 max, 2 ≤ ≤ − → x x Далее на рабочем листе Microsoft Excel сформируем следующую таблицу: и с помощью надстройки Поиск решения попытаемся решить поставленную задачу из стартовой точки 0 0 = x 207 При этом будет получено следующее решение: функция цели (ячейка A6) равна 0, независимая переменная x (ячейка A3) также равна 0. То есть, используемый в надстройке метод оптимизации не смог улуч- шить начальное приближение, поскольку в точке 0 = x первая производная функции цели ( ) x x f 2 = ′ обращается в 0, т.е. необходимое условия для точки локального экстремума выполнено. Хотя на заданном интервале поиска на са- мом деле это точка минимума, а не искомого максимума. Так происходит по- тому, что надстройка Поиск решения «не утруждает себя» проверкой доста- точных условий оптимума, и вследствие чего в подобных ситуациях макси- мум от минимума она не отличает. Далее, попытаемся решить поставленную задачу при начальном при- ближении 5 0 0 = x . Для этого всего лишь изменим содержимое ячейки A3 на 0.5 и запустим Поиск решения. Максимум, равный 1, будет найден на правой границе интервала поиска в точке 1 = x И, наконец, попытаемся решить нашу задачу из начальной точки -1. При этом максимум функции цели, равный 4, будет найден в точке 2 − = x Все три рассмотренные варианты решения поставленной задачи из различных «стартовых точек» сведены в представленной далее таблице. Таблица 9.2− Варианты решения задачи Вариант x 0 y 0 x * y * 1 0 0 0 0 2 0.5 0.25 1 1 3 -1 1 -2 4 Из рассмотренных трех вариантов поиска, с различными начальными приближениями, только последний является верным. Пример продемонстрировал, что в задачах нелинейной оптимизации на решения, выдаваемые надстройкой Поиск решения, полагаться нельзя, и необ- ходимо привлекать дополнительные методы анализа полученных результатов. 208 Так происходит потому что, к сожалению, до сих пор еще не разработа- но универсальных методов поиска глобального оптимума. Поэтому получен- ные решения необходимо дополнительно проанализировать с помощью отчетов и графиков (конечно, если количество независимых переменных не более 2-х). Приведенные отчет по пределам и график целевой функции с ограничения- ми показывают, что максимум, равный 4, действительно находится в точке 2 − = x 9.4. Решение систем нелинейных уравнений Решение систем нелинейных уравнений с помощью надстройки Поиск решения рассмотрим на следующем примере. Пример 9.3 Решить систему нелинейных уравнений − = − = − 2 1 2 y x y x , т.е. найти точки их пересечения. Как видно из графика система имеет два решения: одно на интервале [ ] 1 , 2 − − и второе на интервале [ ] 3 , 2 209 Одним из возможных решение данной задачи может быть приведение ее к решению задачи безусловной оптимизации. Для этого необходимо заполнить такую исходную таблицу. В ячейках A10 и B10 этой таблицы записаны первое и второе уравнения, соответственно, в ячейке A6 – функция СРОТКЛ(), которая возвращает сред- нее отклонение своих аргументов от их среднего значения, а в ячейке A3 – искомая независимая переменная x Для поиска первого решения необходимо: 1. В ячейку независимой переменной A3 занести любое значение из диапазона [ ] 1 , 2 − − , например, -1.5. Причем, чем ближе это значение будет к искомому значению, тем быстрее будет выполнен поиск. 2. Найти минимум функции цели, содержащейся в ячейке A6, заполнив окно диалога Поиск решения следующим образом. При этом список Ограничения не содержит никаких ограничений, поскольку решается задача безусловной оптимизации. 210 В результате будет получено следующее решение: − = x 1.30277565138664. Поиск следующего решения необходимо начинать из любой точки диапазона [ ] 3 , 2 . Надстройка Поиск решения в этом случае выдаст такой результат: 2.30277482125311. 9.5. Решения систем линейных алгебраических уравнений Один из возможных способов решения систем линейных алгебраических уравнений с помощью надстройки Поиск решения рассмотрим на Примере 9.4. Пример 9.4 Решить следующую систему линейных алгебраических уравнений: = − = − 3 2 7 2 4 2 1 2 1 x x x x Для этого на рабочем листе необходимо заполнить такую таблицу: В ней в ячейках A7:B8 записаны коэффициенты уравнений, в ячейках C7:C8 – свободные члены, а в ячейках D7:D8 – функции СУММПРОИЗВ(), которые возвращают вычисленные значения свободных членов при некоторых значе- ниях независимых переменных, которые размещены в ячейках |