28

Российский заочный институт текстильной и легкой промышленности

Кафедра информатики

ЛАБОРАТОРНЫЙ ПРАКТИКУМ

ПО ИНФОРМАТИКЕ

Изучение надстроек в Excel

(Microsoft Office Excel 2003)

Для студентов всех специальностей

Москва 2008

Составители В.В. Серов, проф. д.т.н.,

А.В. Захаров, ст. преп. (разделы 2, 4.1)

И.М. Шаронова, доц. к.т.н.

Под редакцией проф. д.т.н. В.В. Серова

Лабораторный практикум по информатике. /Российск. заочн. ин-т текстил. и легк. пр-сти; Сост. В.В.Серов, А.В. Захаров, И.М.Шаронова

М.,2008. __с.

Предназначено для студентов всех специальностей.

Редактор О.Л.Лобанова

План 2008г. , поз. .

Сдано в производство . Формат 60х84/16.

Бумага офсетная. Печать офсетная.

Усл. печ. Л_____. Уч.-изд. л. ____Тираж 1000 экз.

Заказ . Цена договорная.

Российский заочный институт текстильной и легкой промышленности.

123423 Москва ул. Демьяна Бедного, 7.

1. Решение нелинейных уравнений и оптимизация в Excel

1.1 Надстройки в Excel

Надстройка – это набор дополнительных программ, который расширяет возможности приложения. Пакет MicrosoftOffice2003 поставляется с четырьмя надстройками для приложенияAccessи шестью – дляExcel. Можно приобрести надстройки у независимых поставщиков. В пакетMicrosoftOffice2003 встроены достаточно простые в использовании средства, которые позволяют пользователям оформлять в виде надстроек программы, разработанные ими на алгоритмическом языкеVBA. Написанные наVBAпрограммы обрабатываются интерпретатором, который переводит их операторы в коды ЭВМ, достаточно медленно. В надстройках размещаются уже оттранслированные программы, поэтому время, необходимое для вычислений в этом случае существенно сокращается.

Рис.1.1 Окно «Надстройки»

Поставляемые вместе с MicrosoftOfficeнадстройки устанавливаются, то есть копируются на жесткий диск компьютера во время установки этого пакета. Перед работой надстройка должна быть загружена, то есть, скопирована в оперативную память. Эта операция выполняется с помощью диалогового окна «Надстройки» (рис.1.1), которое открывается .с помощью командСервис → Надстройки.Надо щелкнуть левой кнопкой мыши по флажкам, расположенным рядом с названиями нужных надстроек. После этого все функции надстройки становятся доступными в этом и в последующих сеансах работы. Так как надстройка требует для своей работы довольно много ресурсов, после окончания работы эту надстройку следует выгрузить, щелкнув на соответствующем флажке в окне «Надстройки». В противном случае работа компьютера будет замедлена.

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

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

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

• Планирование перевозок. Минимизация затрат на перевозку товаров.

• Составление смеси. Получение заданного количества смеси при минимальных расходах.

• Штатное расписание. Составление штатного расписания, обеспечивающего минимизацию расходов.

1.2 Модели и алгоритмы

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

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

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

Здесь - константы, - искомые значения параметров (переменных). Если выражения для целевой функции и ограничений линейны, и в окне «Параметры поиска решения» (рис.3.6, стр. 16) отмечен флажокЛинейная модель, тоExcelиспользует более быстрые и надежные методы поиска решения.

Рассмотрим задачу о минимизации нелинейной функции.

Пример 1. Определить такие размерыибака (рис.1.2) объемом 20м³, которые соответствуют минимальной площади его поверхности:

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

=20

Искомые величины, называемые также параметрами, должны быть положительны. Поэтому в модели следует учесть еще три ограничения:Решение этой задачи приведено в разделе 3.

← Рис.1.2 Бак

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

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