Нелинейные уравнения. Оптимизационные задачи

"Смотри: шатнулся ствол сосны,

На склоне этой крутизны

Лавины жди наверняка!"

Но донеслось издалека:

Excel sior!

Лонгфелло

Поиск решения

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

Общая задача математического программирования формулируется следующим образом: найти вектор , удовлетворяющий системе ограничений типа «равенств»

(1.1)

и (или) «неравенств»

(1.2)

и доставляющий экстремум функции

.

(1.3)

На некоторые переменные часто накладывается условие неотрицательности и целочисленности. Если хотя бы одна из функций в (1.1)- (1.3) является нелинейной, то и задача математического программирования считается нелинейной.

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

Рис. 1.13. Поиск решения

Решая задачу с помощью системы " Поиск Решения " (Сервис/Поиск решения), необходимо указать (рис. 1.13):

- целевую ячейку (с формулой, определяющей целевую функцию (1.3)), в которой требуется установить максимальное, минимальное или некоторое определенное значение;

- изменяемые ячейки (до 200), переменные , значения которых определяются методами математического программирования (область «изменяя ячейки»;

- ячейки ограничений, значения которых должны лежать в определенных пределах или соответствовать некоторым заданным значениям программирования (область «ограничения»). Для формирования области допустимых значений (1.1), (1.2) можно указать до 500 ограничений, по 2 для каждой переменной плюс 100 дополнительных ограничений.

Excel позволяет устано-вить параметры процесса решения задачи (метод поиска решения, максимальное время решения, предельное число итераций, относительная погрешность и др. (рис. 1.14).

Рис. 1.14. Параметры решения

Ограничения на неотрицательность решения можно задавать не в области ограничений, а в параметрах (см. рис. 1.14).

Подбор параметра

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

1.7.3. Проектирование вычислений с помощью таблицы
подстановки

Введя формулы на рабочем листе, работой команды Данные/Таблица подстановки можно осуществить анализ "что - если", чтобы увидеть, как изменение определенных значений в формулах влияет на результаты формул. Таблицы подстановки сокращают этот процесс варьирования до одной операции. Таблица подстановки - это область ячеек, показывающая результаты подстановки различных значений параметра в заданные формулы функций. Есть два типа таблиц подстановки: с одним параметром и с двумя параметрами. В первом случае вводятся различные значения для одной переменной x и рассматриваются результаты вычисления одной или более функций y (x), z (x),… В случае таблицы подстановки с двумя параметрами x, y вводятся различные значения для этих переменных и рассматривается результат вычислений одной функции z (x,y),…..

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

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

Внешняя среда

Печать

Процесс печати в Excel практически не имеет отличий от печати в других Windows-приложениях и начинается с задания параметров страницы. Команда (Файл/Параметры страницы) выводит на экран окно настройки параметров страницы, практически одинаковое для всех приложений Ms Office. В этом окне задаются такие параметры документа, как размеры листа и полей, масштаб изображения, ориентация страниц (портрет, ландшафт), направление печати листов (вниз - направо или направо - вниз). Здесь можно при необходимости определить колонтитулы и др.

Путем использования разделителя страниц можно регулировать количество информации на каждой странице. Границы печатных страниц в документе представляются пунктирными линиями. Пользователь может вставить разделители страниц вручную. Для этого следует активизировать ячейку, расположенную сверху или слева (в зависимости от направления печати) от позиции, где устанавливается разделитель. Далее выполняется: Вставка/Конец страницы. Пунктирная линия жесткого разделителя выглядит более темной по сравнению с автоматическими разделителями. Для удаления жесткого разделителя необходимо активизировать ячейку, расположенную непосредственно под линией разделителя, и выполнить команду меню Вставка/Убрать конец страницы.

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

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

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

Настройка колонтитулов выполняется в меню Вид/колонтитулы.

Рис. 1.15. Панель колонтитула

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

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

Для оформления данной процедуры необходимо выполнить: Файл/Параметры страницы/Лист. В окнах "сквозные строки" или "сквозные столбцы" необходимо указать адреса строк или столбцов заголовков. Это лучше всего выполнить мышкой, выделяя нужные области.

Если активизирован лист рабочей книги "диаграмма", то на панели "Параметры страницы" появляется еще одна вкладка - "Диаграмма", которая позволяет максимально адаптировать диаграмму к листу бумаги при выводе ее на печать.

Экспорт и импорт данных

Связь и внедрение объектов (Object Linking and Embedding, или OLE) - одна из наиболее интересных особенностей Windows. OLE используется при создании сложного документа, состоящего из двух или более типов документов. Представьте, вы создаете отчет, написанный в Word. При этом на с. 27. вы вставляете таблицу (или ее фрагмент), построенную в Excel, на с. 37 - диаграмму, созданную в том же Excel, а на с.47 собственный рисунок, созданный в CorelDraw. Таким образом, отчет состоит из некоторого множества компонентов, созданных в разных приложениях и внедренных в один большой документ.

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

Вставлять можно как новые, так и существующие объекты. При создании нового объекта на соответствующей панели (рис. 1.16) необходимо выбрать тип объекта из предлагаемого множества.

Рис. 1.16. Вставка объекта

Единая методология MS Office через преобразующие программы обеспечивает максимально полный перенос объектов из одного приложения в другое. Идеология OLE позволяет экономить память за счет отказа от дублирования объектов, а также максимально использовать возможности специализированных программ.

Для того чтобы преобразовать большинство типов файлов, сохраненных в другом формате, необходимо просто открыть файл в используемом приложении Office (импорт). Когда приложение Office распознает формат файла, документ преобразуется и открывается. Например, можно открыть документ, созданный в приложении Word, в Excel, а Excel автоматически преобразует документ Word в формат файла Excel.

В некоторых случаях следует сначала открыть файл в исходном приложении и сохранить его в формате файла другого приложения (экспорт). После преобразования исходного файла следует открыть его прямо в другом приложении. Например, чтобы передать таблицу базы данных (БД) MS Access в MS Excel, следует в приложении Access сохранить таблицу в формате Excel (Файл/Сохранить как/экспорт), а затем открыть файл непосредственно в Excel.

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

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

Примечание. Для задания способа вставки данных следует воспользоваться командой Специальная вставка (меню Правка).

Связь с базами данных

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

Фамилия	Имя	Отчество	Табл. номер	Год рождения	Место рождения
Лисицын	Павел	Анатольевич			Екатеринбург
Волков	Анатолий	Павлович			Ревда

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

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

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

Рис. 1.17. Выбор источника данных

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

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

Вызов программы MS Query

Рис. 1.18. Выбор файла

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

Рис. 1.19. Выбор полей

Эта программа может использоваться автономно, чтобы просмотреть или откорректировать данные той или иной таблицы. Для запуска программы из Excel выполните команду Данные/Импорт внешних данных/Создать запрос (программа MS Query должна быть установлена). В появившемся окне (рис. 1.18) выберите тот источник, данные из которого нужно поместить в таблицу. Использование мастера запросов поможет быстро и без ошибок определить критерии отбора данных.

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

Мастер запросов

Если данные расположены в одной внешней таблице, то мастер запросов может создать практически любую их выборку по требуемым условиям. Перенесите столбцы (или всю таблицу), из которых будут извлекаться данные, в правую половину окна, отмечая название столбцов и нажимая клавишу [>] (при необходимости можно просмотреть данные из этого столбца, нажав клавишу "Просмотр" (см. рис. 1.19). После завершения отбора нажмите клавишу "Далее", которая становится активной после выбора полей.

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

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

К одному и тому же столбцу можно применить несколько условий выборки, используя операции И или ИЛИ. После выбора операции станут доступными поля задания условия и параметров отбора данных.

Рис. 1.20.Фильтрация данных

Такие правила отбора можно задать для каждого используемого для отбора столбца. Критерии отбора по каждому столбцу объединяются в итоговом запросе по принципу И, т.е. будут одновременно выполняться условия, заданные как для первого столбца, так и для второго, третьего и т.д.

Следующим окном мастера запросов будет окно задания критериев сортировки результатов. При этом можно определить несколько уровней сортировки (по аналогии с простой сортировкой в Excel).

Последним окном диалога является запрос о месте размещения итоговой таблицы.

Макросы

Набрел на тело верный пес.

Героя снег почти занес,

Но флаг в ладони ледяной

Сжимал он с твердостью стальной.

Excel sior!

Лонгфелло

 

Макрос – это список команд, записанных в виде одной выполняемой процедуры. С помощью макросов можно автоматизировать повторяющиеся действия.

Запись макроса

Запись макроса начинается командой Сервис / Макрос / Начать запись.

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

В более ранних версиях Excel комбинация клавиш и некоторые другие атрибуты макроса задаются через дополнительную панель “ Параметры ”.

Поле “ Описание " предназначено для комментариев по макросу. Не следует его игнорировать - через неделю-другую вы будете усиленно вспоминать, для чего был записан этот макрос.

После нажатия кнопки [ OK ] в статусной строке появляется слово "Запись". Активизируется панель, содержащая две кнопки - “ Остановить запись ” и “ Относительная ссылка ”. Теперь все выполняемые команды и действия будут записаны в макросе. Будьте внимательны! Не делайте того, что не должно входить в макрос!

Запись макроса прекращается через меню Сервис\Макрос или с помощью кнопки “ Остановить запись ”.

 

Рис. 1.22. Редактирование макроса

Текст макроса на языке программирования Visual Basic находится в отдельном модуле книги (его можно обнаружить по ярлычку в ранних или через меню Сервис/Макрос в современных версиях Excel). Макросы можно редактировать (добавлять, удалять, изменять). Для этого в меню Сервис/Макрос/Макросы выбирается требуемый макрос, после чего Excel вызовет редактор Visual Basic (рис. 1.22), который позволит изменить записанную процедуру.

Относительные ссылки

По умолчанию при записи макроса используются абсолютные ссылки, например $A$1. Макрос, записанный с абсолютными ссылками, при запуске всегда обрабатывает те же ячейки, которые обрабатывались при его записи.

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

Следует помнить:

· Кнопка “ Начать запись макроса ” расположена на панели инструментов “ Visual Basic ”.

· Соответствующий макросу модуль Visual Basic всегда можно переименовать.

· Запись макроса можно использовать для создания исходного текста модуля Visual Basic, содержащего вызовы необходимых объектов, методов или свойств.

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

1.9.3. Выполнение макроса

Рис. 1.23. Панель «Макрос»

Если макрос не имеет клавиш вызова, то его запуск возможен: командой Сервис/Макрос/Макросы/ “ Имя ” (название макроса)/“ Выполнить ” (рис. 1.23); из модуля Visual Basic. Установите курсор внутри процедуры Sub. На панели инструментов “Visual Basic” нажмите кнопку “ Выполнить макрос ”.