Дипломная (вкр): Решение задач оптимизации с применением пакетов прикладных программ

Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле. Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “;”. Скобки позволяют MS Excel определить где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Помните о том, что при записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками.

В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах MS Excel можно использовать до семи уровней вложенности функций.

Задаваемые входные параметры должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.

В формулах можно использовать четыре вида операторов:

1.      Арифметические операторы. Результатом выполнения арифметических операций является число.

2.      Текстовые.

.  Операторы сравнения используются для обозначения операций сравнения. Результатом выполнения операции сравнения является булевская (логическая) переменная, принимающая значение ИСТИНА или ЛОЖЬ.

.  Адресные операторы используются для объединения диапазонов ячеек. Порядок выполнения действий в формулах аналогичен приоритетам математических операций.Excel содержит более 400 встроенных функций. Поэтому непосредственного вводить с клавиатуры в формулу названия функций и значения входных параметров не всегда удобно. В MS Excel есть специальное средство для работы с функциями - Мастер функций. При работе с этим средством вам сначала предлагается выбрать нужную функцию из списка категорий, а затем в окне диалога предлагается ввести входные значения. Мастер функций вызывается командой Вставка → Функции.

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

Всего Microsoft MS Excel предлагает вам 9 типов плоских диаграмм и 6 типов объемных. Эти 15 типов представленных на рисунке 7 включают 102 формата.

Для построения диаграммы выполняют команду: Вставка → Диаграмма Мастер диаграмм. Метками строк и столбцов называются заголовки строк и столбцов. Метки столбцов являются текстом легенды. Легенда представляет собой прямоугольник, в котором указывается каким цветом или типом линий выводятся на графике или диаграмме данные из той или иной строки.

Рисунок 7. Мастер диаграмм

Редактирование диаграммы выполняется двойным щелчком мыши в любом месте диаграммы. Команда Формат обозначения легенды устанавливает цвет линий, их стиль и толщину. Для форматирования объекта диаграммы используют контекстное меню. Для замены одного ряда данных в диаграмме другим применяют команду Формат ряда. Изменяя линии графика, можно изменить данные на рабочем листе. Команда Тип диаграмм позволяет изменять тип уже существующей диаграммы. Команда Автоформат меняет не только тип диаграммы, но и устанавливает стандартные параметры диаграммы. Для получения трехмерной диаграммы на 2 шаге построения диаграммы выберите пространственный образец. Команда Объемный вид изменяет пространственную ориентацию диаграммы.

При создании диаграммы смешанного типа возникают две группы рядов. Одна группа - гистограмма, а другая может быть графиком, с областями или XY-точечной. После создания диаграммы можно использовать для каждого ряда данных любой тип плоской диаграммы.

Можно изменить формат диаграммы, которую MS Excel строит по умолчанию. Команда Автоформат создает пользовательский автоформат для построения диаграмм и графиков. Для изменения формата построения стандартных диаграмм в окне диалога “Параметры” выполните команду Сервис → Параметры и выберите вкладку “Диаграмма”. XY-точечная диаграмма является наиболее подходящим типом диаграммы для обработки результатов лабораторных исследований.

В MS Excel для отображения диаграмм можно использовать не только столбцы, линии и точки, но и произвольные рисунки. При построении графиков математических функций следует использовать в качестве типа диаграммы гладкие кривые. MS Excel поддерживает при построении графиков логарифмическую шкалу как для обычных типов графиков, так и для смешанных, то есть на одной оси можно ввести логарифмическую шкалу, а для другой - линейную.

После ввода данных может потребоваться упорядочить их. Процесс упорядочения записей в базе данных называется сортировкой. Порядок сортировки записей определяется конкретной задачей. При сортировке изменяется порядок следования записей. Таким образом происходит изменение базы данных. Вы должны иметь возможность восстановить исходный порядок следования записей. Универсальным средством для этого является введение порядковых номеров записей. В сочетании со средствами MS Excel по восстановлению данных это полностью защитит вашу базу от потерь при случайных сбоях в работе.

Команда Данные → Сортировка устанавливает порядок строк в таблице в соответствии с содержимым конкретных столбцов. Сортировка по возрастанию предполагает следующий порядок: числа, текст, включая текст с числами, логические значения, значения ошибок, пустые ячейки. Сортировка по убыванию происходит в обратном порядке. Исключением являются пустые ячейки, которые всегда располагаются в конце списка.

При выборе Данные → Сортировка открывается окно диалога “Сортировка диапазона”, которое позволяет указать поля для сортировки и определить критерий сортировки.

Фильтрация данных в списке. В MS Excel списком называется снабженная метками последовательность строк рабочего листа, содержащих в одинаковых столбцах данные одного типа. Фильтрация списка позволяет находить и отбирать для обработки часть записей в списке, таблице, базе данных. В отфильтрованном списке выводятся на экран только те строки, которые содержат определенное значение или отвечают определенным критериям. При этом остальные строки оказываются скрытыми.

В MS Excel для фильтрации данных используются команды Автофильтр и Расширенный фильтр. В случае простых критериев для выборки нужной информации достаточно команды Автофильтр. При использовании сложных критериев следует применять команду Расширенный фильтр. Обе команды вызываются в результате выбора команды Данные → Фильтр.

Большинство задач, решаемых с помощью электронной таблицы, предполагают нахождение искомого результата по известным исходным данным. Но в MS Excel есть инструменты, позволяющие решить и обратную задачу: подобрать исходные данные для получения желаемого результата. Надстройка Microsoft MS Excel «Поиск решения» является мощным средством поиска решений и применяется при решении задач оптимизации. В школьном курсе алгебры такими задачами являются решение уравнения, по начальному значению. Одним из таких инструментов является Поиск решения, который особенно удобен для решения задач оптимизации (рисунок 8).

табличный математических пакет вычислительный

Рисунок 8. Работа надстройки «Поиск решения»

Процедура поиска решения позволяет находить оптимальное значение формулы содержащейся в ячейке, которую называют целевой.

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

-  выбрать команду Сервис, Надстройки;

-  установить флажок «Поиск решения»;

-  щелкнуть на кнопке ОК.

Рассмотрим второй способ установки этой надстройки: произвести установку файла Solver.xla. После установки и подключения надстройки в MS Excel 2007/2010 на вкладке "Данные" появляется группа "Анализ" с новой командой "Поиск Решения". В MS Excel 2003 - появляется новый пункт меню "Сервис" с одноименным названием. Поиск решения - стандартная надстройка, существуют также и другие надстройки для MS Excel <#"869912.files/image014.gif">. Для нахождения корней уравнения выполним следующие действия. В ячейку С3 (рисунок 9) введем формулу для вычисления значения функции, стоящей в уравнении слева от знака равенства. В качестве аргумента используем ссылку на ячейку С2, т.е. =С2^2-5*C2+6.

Рисунок 9. Окно диалога Подбор параметра

В окне диалога Подбор параметра в поле Установить в ячейке введем ссылку на ячейку с формулой, в поле Значение - ожидаемый результат, в поле Изменяя значения ячейки - ссылку на ячейку, в которой будет храниться значение подбираемого параметра (содержимое этой ячейки не может быть формулой).

После нажатия на кнопку Ok MS Excel выведет окно диалога Результат подбора параметра. Если подобранное значение необходимо сохранить, то нажмите на Оk, и результат будет сохранен в ячейке, заданной ранее в поле Изменяя значения ячейки. Для восстановления значения, которое было в ячейке С2 до использования команды Подбор параметра, нажмите кнопку Отмена.

При подборе параметра MS Excel использует итерационный (циклический) процесс. Количество итераций и точность устанавливаются в меню Сервис/Параметры/вкладка Вычисления. Если MS Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне диалога Результат подбора параметра и прервать вычисление, а затем нажать кнопку Шаг, чтобы выполнить очередную итерацию и просмотреть результат. При решении задачи в пошаговом режиме появляется кнопка Продолжить - для возврата в обычный режим подбора параметра.

Для того чтобы найти второй корень, в качестве начального приближения в ячейку C2 поместим константу 5 и после этого запустим процесс Подбор параметра.

После открытия диалога Поиск решения, представленного на рисунке 10, необходимо выполнить следующие действия [12].

В поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу для вычисления значений оптимизируемой функции, в нашем примере целевая ячейка - это С4, а формула в ней имеет вид =C3^2-5*C3 + 6 (рисунок 10).

Рисунок 10. Окно диалога Поиск решения

Для максимизации значения целевой ячейки, установить переключатель максимальному значению в положение 8, для минимизации используется переключатель минимальному значению, в нашем случае устанавливаем переключатель в положение значению и вводим значение 0.

В поле Изменяя ячейки ввести адреса изменяемых ячеек, т.е. аргументов целевой функции (С3), разделяя их знаком ";" (или щелкая мышью при нажатой клавише Сtrl на соответствующих ячейках), для автоматического поиска всех влияющих на решение ячеек используется кнопка Предположить.

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

Для запуска процесса поиска решения нажать кнопку Выполнить.

Для сохранения полученного решения необходимо использовать переключатель Сохранить найденное решение в открывшемся окне диалога Результаты поиска решения. После чего рабочий лист примет вид, представленный на рисунок 11. Полученное решение зависит от выбора начального приближения, которое задается в ячейке С4 (аргумент функции). Если в качестве начального приближения в ячейку С4 ввести значение, равное1,0, то с помощью Поиска решения найдем второй корень, равный 2,0.

Рисунок 11. Результаты поиска

Опции, управляющие работой Поиска решения, задаваемые в окне Параметры (окно появляется, если нажать на кнопку Параметры окна Поиск решения), следующие (рисунок 12).

Максимальное время - ограничивает время, отведенное на процесс поиска решения (по умолчанию задано 100 секунд, что достаточно для задач, имеющих около 10 ограничений, если задача большой размерности, то время необходимо увеличить).

Рисунок 12. Настройка параметров Решателя

Предельное число итераций - еще один способ ограничения времени поиска путем задания максимального числа итераций. По умолчанию задано 100, и, чаще всего, если решение не получено за 100 итераций, то при увеличении их количества (в поле можно ввести время, не превышающее 32767 секунд) вероятность получить результат мала. Лучше попытаться изменить начальное приближение и запустить процесс поиска заново.

Относительная погрешность - задает точность, с которой определяется соответствие ячейки целевому значению или приближение к указанным ограничениям (десятичная дробь от 0 до 1).

Допустимое отклонение - задается в % только для задач с целочисленными ограничениями. Поиск решения в таких задачах сначала находит оптимальное нецелочисленное решение, а потом пытается найти ближайшую целочисленную точку, решение в которой отличалось бы от оптимального не более, чем на указанное данным параметром количество процентов.

Сходимость - когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа (дробь из интервала от 0 до 1), указанного в данном параметре, поиск прекращается.

Линейная модель - этот флажок следует включать, когда целевая функция и ограничения - линейные функции. Это ускоряет процесс поиска решения.

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

Автоматическое масштабирование - этот флажок следует включать, когда масштаб значений входных переменных и целевой функции и ограничений отличается, возможно, на порядки.

Показывать результаты итераций - этот флажок позволяет включить пошаговый процесс поиска, показывая на экране результаты каждой итерации.

Сохранить модель поиска решения можно следующими способами:

1.      При сохранении книги MS Excel после поиска решения все значения, введенные в окнах диалога Поиск решения, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в рабочей книге можно сохранить один набор значений параметров Поиска решения.

2.      Если в пределах одного рабочего листа MS Excel необходимо рассмотреть несколько моделей оптимизации (например найти максимум и минимум одной функции, или максимальные значения нескольких функций), то удобнее сохранить эти модели, используя кнопку Параметры/Сохранить модель окна Поиск решения. Диапазон для сохраняемой модели содержит информацию о целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения диалога Параметры. Выбор модели для решения конкретной оптимизационной задачи осуществляется с помощью кнопки Параметры/Загрузить модель диалога Поиск решения.