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

ДИПЛОМНАЯ РАБОТА Решение задач оптимизации с применением пакетов прикладных программ Караганда 2014

• Содержание
• Введение
• 1. Программы математического моделирования
• 1.1 Современные математические пакеты
• 1.2 Функциональные возможности MS Excel
• 1.3 Вычислительные возможности cреды MATLAB
• 2 Решение задач оптимизации с применением пакетов прикладных программ
• 2.1 Решение задач оптимизации с применением MS Excel
• 2.2 Решение задач оптимизации с применением MATLAB
• Заключение
• Список использованной литературы
• Приложение 1
• Приложение 2
• Введение
• Задачи оптимизации встречаются практически во всех сферах человеческой деятельности, так как любое разумное действие является в определенном смысле и оптимальным. Ведь оно выбрано после сравнения с другими (менее предпочтительными) действиями.

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

Оптимизация — целенаправленная деятельность, заключающаяся в получении наилучших результатов при соответствующих условиях.

Поиски оптимальных решений привели к созданию специальных математических методов и уже в 18 веке были заложены математические основы оптимизации (вариационное исчисление, численные методы и др). Однако до второй половины 20 века методы оптимизации во многих областях науки и техники применялись очень редко, поскольку практическое использование математических методов оптимизации требовало огромной вычислительной работы, которую без ЭВМ реализовать было крайне трудно, а в ряде случаев — невозможно. Если отвлечься от экономического, физического, химического или иного содержания этих задач, то все они сводятся к следующей оптимизационной задаче. Найти минимум (или максимум) функции или функционала на некотором множестве некоторого пространства, т. е.

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

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

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

В ходе работы над дипломным проектом ставились следующие задачи:

· Изучение функциональных возможностей MS Excel.

· Изучение вычислительных возможностей системы MATLAB.

· Изучение графических возможностей системы MATLAB.

· Освоение средств программирования в системе MATLAB.

· Изучение численных методов решения задач одномерной оптимизации.

· Изучение численных методов решения задач многомерной оптимизации.

· Построение математических моделей некоторых экономических задач оптимизации.

· Изучение методов решения построенной математической модели.

· Численная реализация построенной математической модели в табличном процессоре MS Excel.

· Численная реализация методов оптимизации в системе MATLAB.

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

«Простые задачи должны решаться просто». Этому постулату как нельзя лучше отвечают вычислительные возможности программы MS Excel, которые без оговорки можно назвать безграничными. Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без проведения расчётов вручную или специального программирования.

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

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

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

MATLAB — это высокопроизводительный язык для технических расчетов. Он включает в себя вычисления, визуализацию и программирование в удобной для пользователя среде. Типичное использование MATLAB — это:

— математические вычисления;

— создание алгоритмов;

— моделирование;

— анализ данных, исследования и визуализация;

— научная и инженерная графика;

— разработка приложений, включая создание графического интерфейса.

Численные методы оптимизации — это новейшие разделы математики, которые получили свое развитие только во второй половине XX века. Многие проблемы в задачах оптимизации остаются актуальными и по сей день. К таким проблемам относится, например, проблема выбора начального приближения к точке экстремума или проблема определения области поиска точек экстремума в задачах нелинейного программирования. Мощные графические средства системы MATLAB позволяют получить трехмерные объемные графики функции в задачах оптимизации. И в таких задачах указанные проблемы решаются графическим способом.

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

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

Во втором разделе решаются следующие задачи:

— Сведение задачи производства к задаче линейного программирования.

— Решение полученной задачи линейного программирования в табличном процессоре MS Excel.

— Численная реализация задач одномерной минимизации, задач безусловной и условной минимизации функций многих переменных в системе MATLAB с привлечением графических средств системы.

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

В приложении 1 представлено поэтапное решение задачи составление производственного плана с применением табличного процессора MS Excel.

В приложении 2 представлены программы, написанные в среде MATLAB, вычисления точек экстремума функций одной и многих переменных различными методами оптимизации.

1. Программы математического моделирования

1.1 Современные математические пакеты Современные математические пакеты можно использовать и как обычный калькулятор, и как средства для упрощения выражений при решении каких-либо задач, и как генератор графики или даже звука. Стандартными стали также средства взаимодействия с Интернетом, и генерация HTML-страниц выполняется теперь прямо в процессе вычислений. Теперь можно решать задачу и одновременно публиковать для коллег ход ее решения на своей домашней странице.

Рассказывать о программах математического моделирования и возможных областях их применения можно очень долго, но мы ограничимся лишь кратким обзором ведущих программ, укажем их общие черты и различия. В настоящее время практически все современные CAE-программы (Computer Aided Engineering, пакеты математического моделирования) имеют встроенные функции символьных вычислений.

Так что же делают эти программы и как они помогают математикам? С помощью описываемого ПО можно сэкономить массу времени и избежать многих ошибок при вычислениях. Отметим, что спектр задач, решаемых подобными системами, очень широк [2]:

— проведение математических исследований, требующих вычислений и аналитических выкладок;

— разработка и анализ алгоритмов;

— математическое моделирование и компьютерный эксперимент;

— анализ и обработка данных;

— визуализация, научная и инженерная графика;

— разработка графических и расчетных приложений.

Наиболее известными и приспособленными для математических символьных вычислений считаются следующие математические пакеты:

— Maple;

— MathCad;

— Mathematica;

— MATLAB.

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

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

Рисунок 1. Mathematica

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

К недостаткам системы Mathematica следует отнести разве что весьма необычный язык программирования, обращение к которому, впрочем, облегчает подробная система помощи.

Программа Maple — своего рода патриарх в семействе систем символьной математики и до сих пор является одним из лидеров среди универсальных систем символьных вычислений. Она предоставляет пользователю удобную интеллектуальную среду для математических исследований любого уровня и пользуется особой популярностью в научной среде. Отметим, что символьный анализатор программы Maple является наиболее сильной частью этого ПО, поэтому именно он был позаимствован и включен в ряд других CAE-пакетов, таких как MathCad и MATLAB, а также в состав пакетов для подготовки научных публикаций Scientific WorkPlace и Math Office for Word.

Maple предоставляет удобную среду для компьютерных экспериментов, в ходе которых пробуются различные подходы к задаче, анализируются частные решения, а при необходимости программирования отбираются требующие особой скорости фрагменты. Пакет позволяет создавать интегрированные среды с участием других систем и универсальных языков программирования высокого уровня. Когда расчеты произведены и требуется оформить результаты, то можно использовать средства этого пакета для визуализации данных и подготовки иллюстраций для публикации. Для завершения работы остается подготовить печатный материал в среде Maple, а затем можно приступать к очередному исследованию. Работа проходит интерактивно — пользователь вводит команды и тут же видит на экране результат их выполнения (рисунок 2). При этом пакет Maple совсем не похож на традиционную среду программирования, где требуется жесткая формализация всех переменных и действий с ними. Здесь же автоматически обеспечивается выбор подходящих типов переменных и проверяется корректность выполнения операций, так что в общем случае не требуется описания переменных и строгой формализации записи.

Рисунок 2. Maple

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

К недостаткам системы Maple можно отнести лишь ее некоторую «задумчивость», причем не всегда обоснованную, а также очень высокую стоимость этой программы.

Система MATLAB, представленная на рисунке 3, относится к среднему уровню продуктов, предназначенных для символьной математики, но рассчитана на широкое применение в сфере CAE.

MATLAB — одна из старейших, тщательно проработанных и проверенных временем систем автоматизации математических расчетов, построенная на расширенном представлении и применении матричных операций. Это нашло отражение и в самом названии системы — MATrix LABoratory, то есть матричная лаборатория. Однако синтаксис языка программирования системы продуман настолько тщательно, что данная ориентация почти не ощущается теми пользователями, которых не интересуют непосредственно матричные вычисления.

Библиотеки MATLAB отличаются высокой скоростью численных вычислений. Однако матрицы широко применяются не только в таких математических расчетах, как решение задач линейной алгебры и математического моделирования, обсчета статических и динамических систем и объектов. Они являются основой автоматического составления и решения уравнений состояния динамических объектов и систем. Именно универсальность аппарата матричного исчисления значительно повышает интерес к системе MATLAB, вобравшей в себя лучшие достижения в области быстрого решения матричных задач. Поэтому MATLAB давно уже вышла за рамки специализированной матричной системы, превратившись в одну из наиболее мощных универсальных интегрированных систем компьютерной математики.

Рисунок 3. MATLAB

Из недостатков системы MATLAB можно отметить невысокую интегрированность среды (очень много окон, с которыми лучше работать на двух мониторах), не очень внятную справочную систему (объем фирменной документации достигает почти 5 тыс. страниц, что делает ее трудно обозримой) и специфический редактор кода MATLAB-программ (рисунок 4). Сегодня система MATLAB широко используется в технике, науке и образовании, но все-таки она больше подходит для анализа данных и организации вычислений, нежели для чисто математических выкладок.

В отличие от мощного и ориентированного на высокоэффективные вычисления при анализе данных пакета MATLAB, программа MathCad — это, скорее, простой, но продвинутый редактор математических текстов с широкими возможностями символьных вычислений и прекрасным интерфейсом. MathCad не имеет языка программирования как такового, а движок символьных вычислений заимствован из пакета Maple. Зато интерфейс программы MathCad очень простой, а возможности визуализации богатые. Все вычисления здесь осуществляются на уровне визуальной записи выражений в общеупотребительной математической форме. Пакет имеет хорошие подсказки, подробную документацию, функцию обучения использованию, целый ряд дополнительных модулей и приличную техническую поддержку производителя. Однако пока математические возможности MathCad в области компьютерной алгебры намного уступают системам Maple, Mathematica, MATLAB. Однако по программе MathCad выпущено много книг и обучающих курсов. Сегодня эта система стала международным стандартом для технических вычислений, и даже многие школьники осваивают и используют MathCad.

Рисунок 4. MathCad

Для небольшого объема вычислений MathCad идеален — здесь все можно проделать очень быстро и эффективно, а затем оформить работу в привычном виде (MathCad предоставляет широкие возможности для оформления результатов, вплоть до публикации в Интернете). Пакет имеет удобные возможности импорта/экспорта данных. Например, можно работать с электронными таблицами Microsoft MS Excel прямо внутри MathCad-документа.

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

В качестве более дешевых, простых, можно отметить такие пакеты, как UMS, Microsoft MS Excel.

Когда-то системы символьной математики были ориентированы исключительно на узкий круг профессионалов и работали на больших компьютерах. Но с появлением ПК эти системы были переработаны под них и доведены до уровня массовых серийных программных систем. Сейчас на рынке сосуществуют системы символьной математики самого разного калибра — от рассчитанной на широкий круг потребителей системы MathCad до компьютерных монстров Mathematica, MATLAB и Maple, имеющих тысячи встроенных и библиотечных функций, широкие возможности графической визуализации вычислений и развитые средства для подготовки документации.

Отметим, что практически все эти системы работают не только на персональных компьютерах, оснащенных популярными операционными системами Windows, но и под управлением операционных системы Linux, UNIX, Mac OS, а также на КПК.

Перейдем к пакетам наиболее часто используемых в школах при проведении уроков математики в старших классах. К ним относятся: Universal Math Solver (UMS), Microsoft MS Excel.

Программа UMS — «Универсальный математический решатель» позволяет решать задания из многих разделов алгебры и анализа. Знания «Универсального решателя» охватывают почти весь курс по алгебре и анализу средней школы и первых курсов вузов.

В отличие от ряда мощных математических пакетов, UMS доступен для быстрого изучения благодаря простому интерфейсу и расправляется с предложенными задачами исключительно «школьными» методами, оформляя все этапы решения так, как это бы сделал учитель (рисунок 5).

Если смотреть на практическую ценность Universal Math Solver шире, то приложение с успехом сослужит службу родителям, привыкшим контролировать выполнение домашних заданий ребёнком, и учителям математики. Последние могут использовать интерактивные возможности программы в учебном процессе, возлагая объяснение решений задач на «плечи» электронного педагога.

Universal Math Solver поставляется в двух редакциях — стационарной и сетевой. Стоимость годичной лицензии за одну инсталляцию первой версии составляет 3000 тенге, цена сетевой редакции — в три раза выше.

Рисунок 5. Universal Math Solve

К сожалению, в школьной практике нет возможности использовать такие мощные математические пакеты, как Mathematica, Mathcad, MathLab, Maple из-за дороговизны их лицензионных копий. Однако офисные приложения MS Office есть в каждой школе. Применение математической оболочки офисного табличного процессора MS Excel позволяет решать математические задачи высокой сложности.

1.2 Функциональные возможности MS Excel

Microsoft MS Excel — программа, используемая для обработки данных в табличном виде. Термин электронная таблица используется для обозначения простой в использовании компьютерной программы, предназначенной для обработки данных. Обработка включает в себя [9]:

— проведение различных вычислений с использованием мощного аппарата функций и формул;

— исследование влияния различных факторов на данные;

— решение задач оптимизации;

— получение выборки данных, удовлетворяющих определенным критериям;

— построение графиков и диаграмм;

— статистический анализ данных.

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

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

Рассмотрим основные элементы и структуру рабочей книги. Рабочие книги — это файлы MS Excel, которые могут содержать один или несколько рабочих листов. Такие файлы имеют расширение — xls. Рабочий лист — это собственно электронная таблица, основной тип документа, используемый в MS Excel для хранения и манипулирования данными. Он состоит из ячеек, организованных в столбцы и строки, и всегда является частью рабочей книги.

В Ехсel преимущественно используются стандартные для среды Windows элементы интерфейса — рабочая поверхность (окно рабочей книги), строка состояния, меню, диалоговые окна, панели инструментов, полосы прокрутки, кнопки управления и т. д. Эти и другие элементы интерфейса MS Excel отмечены на рисунке 6. Остановимся на них подробнее.

Рисунок 6. Интерфейс MS Excel

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

Непосредственно под строкой заголовка расположена строка главного меню, включающего основные команды управления работой приложения. Взаимодействие с MS Excel осуществляется посредством выбора команд из основного меню. Все команды являются иерархическими. В результате выбора одной из таких команд на экране появляется ее ниспадающее меню. В состав главного меню входят меню File (Файл), Edit (Правка), View (Вид), Insert (Вставка). Format (Формат), Tool (Сервис). Data (Данные), Window (Окно) и Help (?). В левом углу находится значок управляющего меню книги, а в правом — кнопки управления размером окна документа. Чтобы открыть меню, необходимо щелкнуть левой кнопкой мыши на его названии или же нажать комбинацию клавиш <�Аlt+буква>, где параметр буква представляет собой ту букву, которая подчеркнута в имени соответствующего меню.

MS Excel располагает 54 панелями инструментов. По умолчанию в окне MS Excel непосредственно под строкой меню отображается панель инструментов Standard (Стандартная) и панель инструментов Formatting (Форматирование). Панель инструментов Standard фактически представляет собой особый тип меню в котором каждая команда представлена отдельной кнопкой. На этой панели размещены кнопки вызова тех команд, которые используются чаще всего: Create (Создать), Open (Открыть). Save (Сохранить), Print (Печать) и т. д.

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

Рабочий лист MS Excel разделен на строки и столбцы. Заголовки столбцов и строк могут обозначаться целыми числами или же заголовки столбцов — буквами латинского алфавита, а строк — цифрами. На пересечении строки и столбца находится ячейка. Каждая ячейка имеет собственный адрес, например адрес ячейки находящейся на пересечении первого столбца и первой строки, может быть записан как А1. При числовой системе обозначения столбцов и строк, ее адрес выглядит как R1C1. Адрес активной ячейки (т.е. той ячейки, которая выделена в рабочем листе в данный момент) выводится в поле имени, которое расположено слева от строки формул.

Ниже окна рабочей книги находится строка состояния, в которой отображается информация о действиях, выполняемых в рабочей таблице: Ready (Готово), Enter (Ввод), Edit (Правка). Справа от строки состояния находятся индикаторы состояния различных режимов: CAPS, NUM.

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

Ячейка — это основной элемент электронной таблицы только в ней может содержаться какая-либо информация: текст, значения, формулы.

Структура ячейки MS Excel содержит 5 уровней:

1. Видимое на экране изображение (т.е. отформатированный текст) или результат вычисления формулы).

2. Форматы ячейки (формат чисел, шрифты, выключатель (включатель) признак показывать или нет ячейку, вид рамки, защита ячейки).

3. Формулу, которая может состоять из текста, числа или встроенных функций.

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

5. Примечания данной ячейки (произвольный текст). Если ячейка содержит примечание, то в правом верхнем углу появляется красный маркер (точка).

Копирование и редактирование данных осуществляется стандартным для Windows способом.

Данные в MS Excel выводятся на экран в определенном формате. По умолчанию информация выводиться в формате Общий. Можно изменить формат представления информации в выделенных ячейках. Для этого выполните команду Формат > Ячейки. Появится окно диалога «Формат ячеек», в котором нужно выбрать вкладку «Число».

В левой части окна диалога «Формат ячеек» в списке Числовые форматы приведены названия всех используемых в MS Excel форматов. Из списка Числовые форматы выбрана строка (все форматы). В правом окне Тип можно просмотреть все форматные коды, которые используются для представления на экране информации.

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

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

— ввести значение первого элемента прогрессий в выбранную ячейку;

— выделить блок ячеек под требуемое количество членов прогрессии (либо в дальнейшем потребуется указать значение последнего элемента);

— выполнить команду меню Правка > Заполнить > Прогрессия;

— в появившемся диалоговом окне Прогрессия указать тип и параметры формируемой последовательности значений;

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

Для удобства работы функции в MS Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок, статистические, текстовые и математические.

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

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

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

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

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

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

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

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

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

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

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

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

4. Адресные операторы используются для объединения диапазонов ячеек. Порядок выполнения действий в формулах аналогичен приоритетам математических операций.

MS 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, служащие для добавления в MS Excel различных специальных возможностей.

Раcсмотрим порядок работы Поиска решения. Поиск решения является надстройкой, которая позволяет решать задачи оптимизированного моделирования. Процедура поиска решения дает возможность найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Это процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке искомый результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут содержать ссылки на другие влияющие ячейки Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму зависимой ячейки — например можно изменить объем планируемого бюджета рекламы и увидеть, как это повлияет на проектируемую сумму расходов. В состав Microsoft MS Excel в папке OfficeSamples включена книга с примерами (Solvsamp.xls) использования процедуры поиска решения (Solver.xls). В книге Solvsamp. xls, содержит шест примеров: «Структура производства», «Транспортная задача», «График занятости», «Управление капиталом», «Портфель ценных бумаг» и «Проектирование цепи». В примерах уже подобраны целевая и влияющие ячейки, а также ограничения.

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

Возьмем в качестве примера все то же квадратное уравнение. Для нахождения корней уравнения выполним следующие действия. В ячейку С3 (рисунок 9) введем формулу для вычисления значения функции, стоящей в уравнении слева от знака равенства. В качестве аргумента используем ссылку на ячейку С2, т. е. =С2²−5*C2+6.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. Сохранение параметров поиска в виде именованных сценариев. Для этого необходимо нажать на кнопку Сохранить сценарий диалогового окна Результаты поиска решений.

1.3 Вычислительные возможности cреды MATLAB

Система MATLAB (сокращение от MATrix LABoratory — МАТричная ЛАБоратория) является интерактивной системой для выполнения инженерных и научных расчетов, ориентированной на работу с массивами данных. Система использует математический сопроцессор и допускает возможность обращения к программам, написанным на языках Fortran, C, C++.

В MATLAB содержатся специальные средства для электротехнических и радиотехнических расчетов (операции с комплексными числами, матрицами, векторами и полиномами, обработка данных, анализ сигналов и цифровая фильтрация), обработки изображений, реализации нейронных сетей, а также средства, относящиеся к другим новым направлениям науки и техники. К разработкам расширений для системы MATLAB привлечены многие научные школы мира и руководящие ими крупные ученые и педагоги университетов.

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

Возможности MATLAB весьма обширны, а по скорости выполнения задач система нередко превосходит своих конкурентов. Она применима для расчетов практически в любой области науки и техники. Например, очень широко используется при математическом моделировании механических устройств и систем. В частности в динамике, гидродинамике, аэродинамике, акустике, энергетике и т. д. Этому способствует не только расширенный набор матричных и иных операций и функций, но и наличие пакета расширения (toolbox) Simulink, предназначенного для решения задач блочного моделирования динамических систем и устройств, а также десятков других пакетов расширений.