ПРАКТИКУМ ПО ИНФОРМАТИКЕ Изучение надстроек в Excel Microsoft Office Excel 2003 Для студентов все
Работа добавлена на сайт samzan.net:
PAGE 22
РОССИЙСКИЙ ЗАОЧНЫЙ ИНСТИТУТ ТЕКСТИЛЬНОЙ И ЛЕГКОЙ ПРОМЫШЛЕННОСТИ
Кафедра информатики
ЛАБОРАТОРНЫЙ ПРАКТИКУМ
ПО ИНФОРМАТИКЕ
Изучение надстроек в Excel
(Microsoft Office Excel 2003)
Для студентов всех специальностей
Москва 2008
Составители В.В. Серов, проф. д.т.н.,
А.В. Захаров, ст. преп. (разделы 2, 4.1)
И.М. Шаронова, доц. к.т.н.
Под редакцией проф. д.т.н. В.В. Серова
Лабораторный практикум по информатике. /Российск. заочн. ин-т текстил. и легк. пр-сти; Сост. В.В.Серов, А.В. Захаров, И.М.Шаронова
М.,2008. __с.
Предназначено для студентов всех специальностей.
Редактор О.Л.Лобанова
План 2008г. , поз. .
Сдано в производство . Формат 60х84/16.
Бумага офсетная. Печать офсетная.
Усл. печ. Л_____. Уч.-изд. л. ____Тираж 1000 экз.
Заказ . Цена договорная.
Российский заочный институт текстильной и легкой промышленности.
123423 Москва ул. Демьяна Бедного, 7.
1. Решение нелинейных уравнений и оптимизация в Excel
1.1 Надстройки в Excel
Надстройка – это набор дополнительных программ, который расширяет возможности приложения. Пакет Microsoft Office 2003 поставляется с четырьмя надстройками для приложения Access и шестью – для Excel. Можно приобрести надстройки у независимых поставщиков. В пакет Microsoft Office 2003 встроены достаточно простые в использовании средства, которые позволяют пользователям оформлять в виде надстроек программы, разработанные ими на алгоритмическом языке VBA. Написанные на VBA программы обрабатываются интерпретатором, который переводит их операторы в коды ЭВМ, достаточно медленно. В надстройках размещаются уже оттранслированные программы, поэтому время, необходимое для вычислений в этом случае существенно сокращается.
Рис.1.1 Окно «Надстройки»
Поставляемые вместе с Microsoft Office надстройки устанавливаются, то есть копируются на жесткий диск компьютера во время установки этого пакета. Перед работой надстройка должна быть загружена, то есть, скопирована в оперативную память. Эта операция выполняется с помощью диалогового окна «Надстройки» (рис.1.1), которое открывается .с помощью команд Сервис → Надстройки. Надо щелкнуть левой кнопкой мыши по флажкам, расположенным рядом с названиями нужных надстроек. После этого все функции надстройки становятся доступными в этом и в последующих сеансах работы. Так как надстройка требует для своей работы довольно много ресурсов, после окончания работы эту надстройку следует выгрузить, щелкнув на соответствующем флажке в окне «Надстройки». В противном случае работа компьютера будет замедлена.
Некоторые надстройки после их загрузки (активации) добавляют в меню Сервис команды. Так после активации надстроек «Подбор параметра» и «Поиск решения» в меню Сервис появляются строчки с названиями этих надстроек. После загрузки надстройки «Пакет анализа» в окне мастера функций к списку категорий будут добавлены еще три категории: Инженерные, Информационные и Мат. и тригонометрия.
Надстройка «Подбор параметра» используется для решения одного уравнения, а надстройка «Поиск решения» позволяет решать системы линейных и нелинейных уравнений, находить максимум или минимум функции, зависящей от одного или нескольких переменных (целевой), с учетом ограничений на эти переменные. Надстройка «Поиск решения» используется для решения задач, в которых имеются единственная оптимизируемая функция, выражаемые равенствами или неравенствами ограничения, набор параметров, непосредственно или косвенно влияющих на целевую функцию и/или ограничения. Эта надстройка традиционно используется для решения следующих задач:
- Ассортимент товаров. Максимизация выпуска товаров при ограничениях на необходимое для их выпуска сырье.
- Планирование перевозок. Минимизация затрат на перевозку товаров.
- Составление смеси. Получение заданного количества смеси при минимальных расходах.
- Штатное расписание. Составление штатного расписания, обеспечивающего минимизацию расходов.
1.2 Модели и алгоритмы
Среди многих задач, с которыми сталкиваются в своей работе инженер, экономист, встречается и задача принятия оптимального решения. Для поиска такого решения используются методы математического моделирования, которые требуют создания математической модели, выбора критерия оптимальности, а также выбора метода решения задачи оптимизации. Математическая модель представляет собой набор уравнений, неравенств, таблиц, графиков, описывающих зависимости между исходными данными и искомыми величинами, а также имеющиеся ограничения на эти величины. В модели учитывают лишь главные свойства изучаемого объекта, пренебрегая свойствами второстепенными, так как слишком сложная модель не позволит получить решение в приемлемые сроки.
Качество принимаемого решения характеризует критерий оптимальности, который еще называют целевой функцией. Целевой функцией может служить стоимость произведенной продукции, прибыль, полученная предприятием в результате реализации некоторого проекта, затраты, которые необходимо минимизировать и т.д. Кроме модели и целевой функции для принятия решения важно иметь достоверные исходные данные. Если данные введены в программу с ошибками, то о получении правильного ответа не может быть и речи.
Имеется несколько типов математических моделей. Полезно различать линейные и нелинейные модели. В линейной модели связь между параметрами описывается линейными функциями, которые имеют вид:
Здесь - константы, - искомые значения параметров (переменных). Если выражения для целевой функции и ограничений линейны, и в окне «Параметры поиска решения» (рис.3.6, стр. 16) отмечен флажок Линейная модель, то Excel использует более быстрые и надежные методы поиска решения.
Рассмотрим задачу о минимизации нелинейной функции.
Пример 1. Определить такие размеры и бака (рис.1.2) объемом 20м3, которые соответствуют минимальной площади его поверхности :
Здесь является целевой функцией. Так как объем бака вычисляемый по формуле задан, в модели необходимо учесть ограничение
=20
Искомые величины , называемые также параметрами, должны быть положительны. Поэтому в модели следует учесть еще три ограничения: Решение этой задачи приведено в разделе 3.
← Рис.1.2 Бак
Для определения максимума (минимума) нелинейной функции в окне «Параметры поиска решения» (рис.3.6) предлагается два итерационных метода.При использовании метода Ньютона программа вычисляет первые и вторые производные оптимизируемой функции численными методами. При использовании метода сопряженных градиентов – только первые производные, и поэтому работает несколько быстрее. Дать рекомендации о том, какой метод, в каких случаях использовать не представляется возможным. Если не удалось получить решение методом сопряженных градиентов, следует попытаться использовать метод Ньютона. Если же и это не позволяет получить результат, надо проверить правильность задания ограничений.
При решении задачи линейного программирования следует в окне «Параметры поиска решения» отметить флажок «Линейная модель». В этом случае будут использоваться специальные, быстрые методы, предназначенные для решения именно для этого типа задач.
2. Надстройка «Подбор параметра»
2.1. Введение
На практике часто возникают ситуации, когда желаемый результат одиночной формулы известен, но не известны значения, которые требуется ввести для получения этого результата. В этом случае очень удобно воспользоваться надстройкой Подбор параметра электронной таблицы Microsoft Excel. Воспользоваться средством «Подбор параметра» можно, выбрав команду Подбор параметра в меню Сервис. Говоря математическим языком, мы допускаем, что наша формула (целевая функция) меняется с изменением значения только одного параметра. Подходящее значение этого параметра отыскивается вычислительными методами, производящими итерационные вычисления для нахождения нужного решения (если, конечно, оно существует). Тем самым ищется решение уравнения с одним неизвестным. Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если".
Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. Такой процесс называется итерацией, и продолжается он до тех пор, пока Excel не выполнит 100 попыток или не найдет решение, лежащее в пределах точности 0.001 от точного значения.
Чтобы настроить число попыток итераций и точность результата, необходимо выполнить команду Параметры в меню Сервис и задать их значения на вкладке Вычисления. Данное средство Подбор параметра существенно экономит время по сравнению с методом, основанном на грубой силе –ручном переборе входящих в формулу чисел.
Давайте теперь рассмотрим примеры практического применения надстройки Подбор параметра.
2.2. Пример использования надстройки «Подбор параметра»
Возьмем задачу из школьного учебника: Определение времени встречи двух объектов.
Из одного пункта по одной дороге вышли два путника. Первый идет со скоростью 5 км/ч, второй — 10 км/ч. Когда они встретятся, если первый вышел на два часа раньше?
Задачу можно сформулировать и так: при каком значении переменного параметра «время» разница в расстоянии между пешеходами будет равна нулю?
Откроем программу Excel. Введем все исходные данные в рабочий лист (рис. 1). Путь, пройденный первым путником, вычисляется по формуле =B3×B1, а вторым — по формуле =(B3-B6)×B2. Разница путей, в свою очередь, будет определена по формуле =B4-B5.
Рис. 2.1. Введенные данные задачи и вызванное окно надстройки «Подбор параметра»
Выбрав команду «Подбор параметра» в меню Сервис, видим диалоговое окно «Подбор параметра» (рис. 1). В данном окне укажем, в какой ячейке нужно установить заданный результат (в ячейке B7), чему равняется это значение (0) и за счет изменения какого параметра оно достигается ($B$3). Щелкнув мышью на кнопке ОК, получаем результат (рис. 2.2).
Конечно, приведенный пример легко решается и без Excel, однако, используя ту же методику, в Excel можно столь же быстро решать намного более сложные задачи.
Рис. 2.2. Результат подбора параметра
2.3. Подбор процентной ставки
Рассмотрим еще одну задачу подбора параметра:
Пусть известна сумма займа 100000 рублей. Необходимо подобрать такую процентную ставку, чтобы ежемесячный платеж стал равен 900 руб. Срок займа – 180 месяцев.
Для решения задачи используем финансовую функцию ПЛТ и метод подбора параметра.
Если вы откроете встроенную помощь по программе MS Excel и наберете в строке поиска ПЛТ, то найдете описание этой функции:
"Функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.
Синтаксис
ПЛТ(ставка ;кпер; пс; бс ;тип)
Ставка — процентная ставка по ссуде.
Кпер — общее число выплат по ссуде.
Пс — приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
Бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным нулю, то есть для займа, например, значение Бс равно 0.
Тип — число 0 или 1, обозначающее, когда должна производиться выплата (в начале или конце периода).
Заметки
Выплаты, возвращаемые функцией ПЛТ, включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или комиссий, иногда связываемых со ссудой.
Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов Ставка и КПер. Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента Ставка и 4*12 для задания аргумента КПер. Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента КПер."
Чтобы этот пример проще было понять, скопируйте его на пустой лист, откроем программу Excel и введем необходимые данные на рабочий лист, взяв в качестве начального приближения к ответу 5 %. В ячейку B4 впишем формулу =-ПЛТ(B3/12;B2;B1)
Обратите внимание на поставленный перед функцией знак «минус». Дело в том, что сумма платежа есть сумма займа, и она отрицательна. Чтобы получить положительное число, мы умножили эту сумму на -1 (Рис. 2.3).
Как нетрудно видеть, что полученная сумма платежа меньше, чем необходимое нам.
Рис. 2.3. Введенные данные
Теперь можно воспользоваться командой Подбор параметра для проведения итерационных вычислений и решения поставленной задачи. С этой целью нужно указать, где находится формула, какое значение нас интересует, и где находится изменяемый параметр, являющийся одним из аргументов формулы: в открывшемся окне «Подбор параметра» в поле «Установить в ячейке» укажем $B$3, в поле «Значение» введем 900, а в поле «Изменяя ячейку» обозначим $B$4.
Задавать значения полей ввода Установить в ячейке и Изменяя ячейку удобнее всего следующим образом: активизировать соответствующее поле ввода щелчком мыши, а затем щелкнуть по соответствующей ячейке рабочего листа.
После нажатия кнопки OK будет открыто диалоговое окно Результат подбора параметра, содержащее краткий отчет о результатах проделанных итерационных вычислений (рис. 4).
Рис. 2.4. Найденное решение и окно-отчет «Результат подбора параметра»
Нажмите кнопку OK, чтобы сохранить результаты вычислений в ячейках рабочего листа. Можно вернуть рабочий лист в исходное состояние, нажав кнопку Отмена.
2.4. Поиск корней нелинейного уравнения
Используя возможности Excel можно находить корни нелинейного уравнения в допустимой области определения переменной. Последовательность операций (алгоритм решения) нахождения корней следующая:
- Уравнение представляется в виде функции одной переменной;
- Производится табулирование функции в диапазоне вероятного существования корней;
- По таблице фиксируются ближайшие приближения к значениям корней;
- Используя средство Excel Подбор параметра, вычисляются корни уравнения с заданной точностью.
Рассмотрим последовательность отыскания корней нелинейного уравнения на примере.
Требуется найти все корни уравнения:
на отрезке [-1; 1]. Правая часть уравнения представлена полиномом третьей степени, следовательно, уравнение может иметь не более трех корней.
Решим задачу согласно указанному алгоритму решения.
- Представим уравнение в виде функции
Известно, что корни исходного уравнения находятся в точках пересечения графика функции с осью .
- Для выбора начальных приближений необходимо определить интервалы значений , внутри которых функция пересекает ось абсцисс, то есть функция меняет знак. С этой целью табулируем функцию на отрезке [–1;+1] с шагом 0,2, получим табличные значения функции (Рис. 5). Из полученной таблицы находим, что значение функции трижды пересекает ось , следовательно, исходное уравнение имеет на заданном отрезке три корня.
- Анализ таблицы показывает, что функция меняет знак в следующих интервалах значений аргумента Х: (-1;-0,8), (-0,2;0,4) и (0,6;0,8). Поэтому в качестве начальных приближений возьмем значения Х: -0,8; -0,2 и 0,6 .
- На свободном участке рабочего листа, как показано на рисунке, в ячейки А15:A17 введите начальные приближения, а соответствующие ячейки столбца В скопируйте формулу. Выполните команду меню Сервис/Параметры, во вкладке Вычисления установите относительную погрешность вычислений E=0,00001, а число итераций N=1000, установите флажок Итерации. Выполните команду меню Сервис/Подбор параметра. В диалоговом окне (Рис. 2.5) заполните следующие поля:
Установить в ячейке: в поле указывается адрес ячейки, в которой записана формула правой части функции – B15;
Значение: в поле указывается значение, которое должна получить функция в результате вычислений, то есть правая часть уравнения (в нашем случае 0);
Изменяя значение: в поле указывается адрес ячейки (где записано начальное приближение), в которой будет вычисляться корень уравнения и на которую ссылается формула – $A$15.
После щелчка на ОК получим значение первого корня:
-0,92.
Выполняя последовательно операции аналогичные предыдущим, вычислим значения остальных корней:
-0,209991 и 0,720002.
Рис. 2.5. Поиск решения корней нелинейного уравнения
2.5. Задача о портфеле ценных бумаг
Портфель ценных бумаг состоит из 50 акций первого типа стоимостью 100 руб. с доходностью 10% и 30 акций второго типа стоимостью 50 руб. с доходностью 15%. Определить, сколько надо докупить акций второго типа, чтобы доход по всем ценным бумагам составил 12%.
Составим функцию дохода по всем ценным бумагам, аргумент которой – это количество докупаемых акций второго типа:
Откроем программу Excel. Поскольку в Excel нет такой стандартной функции, то создадим пользовательскую функцию. Нажмем Alt+F11, чтобы открыть VBA. Далее выбираем в меню Вставка (Insert) пункт Модуль (Module), а потом вписываем нашу функцию:
Function fun(x)
fun=(100*0.1*50+50*0.15*(30+x))/(100*50+50*(30+x))
End Function
Вернемся на рабочий лист программы Excel. Введем все исходные данные в рабочий лист: в ячейку B1 впишем начальное число докупаемых акций второго типа (например, 30). В ячейку B2 впишем формулу =fun(B1). Затем выберем команду «Подбор параметра» в меню Сервис. В появившемся диалоговом окне укажем, в какой ячейке нужно установить заданное значение функции (ячейка B2), чему оно равняется (вводим 0,12) и за счет изменения какого параметра это значение достигается ($B$1). Щелкнув мышью на кнопке ОК, получаем результат (рис.2.6)
.
Рис. 2.6. Результат составления портфеля акций
Функция fun в этой задаче принимает значения, не превышающие 0,2. Поскольку по умолчанию установлена относительная погрешность вычислений только 0,001, велика опасность получить неправильный результат. Во вкладке Вычисления окна «Параметры» следует установить относительную погрешность равной 0,000001. Полученный результат надо округлить до ближайшего целого числа.
3. Надстройка «Поиск решения»
3.1. Оптимизация нелинейной функции
3.1.1 Оптимизация нелинейной функции без учета ограничений
Пусть требуется определить минимальное значение функции . Функция достаточно простая, и мы используем ее для ознакомления с правилами применения надстройки. Эта функция имеет единственный минимум в точке , поэтому в качестве начального приближения можно выбрать произвольное значение , например, ноль. Выберем две любые ячейки рабочего листа Excel. Пусть это будут А2 и В2. В первую запишем начальное значение , равное нулю, во вторую – формулу, по которой вычисляется , и нажмем клавишу Enter. Excel вычислит функцию.
В меню Сервис выберем команду Поиск решения. Появится окно «Поиск решения» (рис.3.1). Если перед вызовом команды Поиск решения выделить ячейку, в которой вычислено значение функции , то адрес этой ячейки автоматически запишется в поле «Установить целевую ячейку». В противном случае придется записывать его самостоятельно. В поле «Изменяя ячейки» поместите адрес аргумента (А2). Обратите внимание на то, что адреса в полях записаны со знаком доллара (используются абсолютные адреса). Устанавливайте адреса ячеек с помощью мыши, а не вводите их вручную. Это поможет избежать ошибок ввода.
Рис.3.1. Вычисление функции и окно «Поиск решения»
Для того, чтобы сообщить программе о необходимости вычислить минимум функции, щелкните мышью переключатель рядом с надписью «минимальному значению». Затем щелкните на кнопке Выполнить. После выполнения некоторого количества итераций программа выведет на экран окно «Результаты поиска решения» (рис.3.2), а в ячейках В2 и А2 появятся минимальное значение функции и соответствующее значение аргумента.
В окне «Результаты поиска решения» (рис.3.2) выберите переключатель «Сохранить найденное значение», затем в списке «Тип отчета» - строку «Результаты» и щелкните на кнопке ОК. На рабочем листе «Отчет по результатам 1» будет создан отчет (рис.3.3). Он выдает следующие сообщения.
- Адрес ячейки, в которой записано максимальное (или минимальное) значение целевой функции, имя функции, ее исходное значение и результат.
- Адрес или адреса аргументов (изменяемых ячеек), их имена, исходные значения и результаты.
- Сведения об ограничениях.
Пусть требуется найти минимум функции нескольких аргументов, например . В этом случае разделяйте символом «;» (точка с запятой) адреса отведенных для аргументов ячеек, когда записываете их в поле «изменяя ячейки» окна «Поиск решения».
Рис.3.2 Окно «Результаты поиска решения»
Рис.3.3 Отчет по результатам
3.1.2 Оптимизация нелинейной функции с учетом ограничений
При попытке найти максимум функции будет выдано сообщение об ошибке (рис.3.4). Появление на экране этого диалогового окна является признаком неограниченности целевой функции. Действительно, с ростом абсолютного значения аргумента целевая функция только растет. Однако, если ввести ограничения на , то максимальное значение будет найдено. Максимум будет иметь место при или .
Рис.3.4 Сообщение об ошибке
Ограничения записываются в поле «Ограничения» окна «Поиск решения». Щелкните на кнопке Добавить. Откроется окно «Добавление ограничения» (рис.3.5). В поле «Ссылка на ячейку» укажите адрес ячейки, в которой вычисляется левая часть ограничения. Записывать формулы в этом поле не следует. В рассматриваемом примере в левом поле записан адрес аргумента. В следующем поле следует выбрать нужный знак логической операции ( >=, <=, =) и в поле «Ограничение» надо записать константу или адрес ячейки, в которой она размещена. Затем щелкните на кнопке Добавить, если надо добавить следующее ограничение или на кнопке ОК. В зависимости от того, каким было выбрано начальное значение , будут получены разные результаты. Это объясняется тем, что у функции на отрезке [0,1] имеются максимумы (два) в граничных точках отрезка.
Рис.3.5 Окно «Добавление ограничения»
В диалоговом окне «Поиск решения» имеется кнопка Параметры. Щелчок по этой кнопке вызывает появление диалогового окна «Параметры поиска решения». Большинство принятых по умолчанию параметров, перечисленных в этом окне, обычно обеспечивают получение решения задачи об оптимуме нелинейной функции с приемлемой точностью. Но значения некоторых параметров зависят от типа решаемой задачи. Так переключатель «Линейная модель» лучше отключить, при решении задачи нелинейного программирования и включить, если решается задача с линейной целевой функцией и линейными ограничениями. Автоматическое масштабирование используется в тех случаях, когда аргументы целевой функции различаются по величине. Методы оптимизации хорошо работают, когда аргументы - величины одного порядка. Если аргументы целевой функции могут принимать только положительные значения, следует отметить флажок «Неотрицательные значения» вместо добавления ограничений типа .
Рис.3.6 Окно «Параметры поиска решения»
Рассмотрим приведенный в разделе 1 пример определения таких размеров бака объемом 20м3, которые соответствуют минимуму площади его поверхности (рис.1.2). Чем меньше поверхность бака, тем меньше материала уйдет на его изготовление. В ячейки В1÷В3 рабочего листа Excel запишем значения переменных которые могут быть выбраны произвольно, в пределах разумного конечно. В ячейку В4 запишем формулу вычисления объема бака
=В1*В2*В3
В ячейке В5 разместим формулу вычисления площади поверхности бака.
=2*(В1*В2+(В1+В2)*В3)
В поле «Ограничения» окна «Поиск решения» надо записать ограничения
В4=20
В1<=0
B2<=0
B3<=0
Вместо трех последних неравенств можно отметить флажок «Неотрицательные значения» в окне «Параметры поиска решения». После щелчка на кнопке Выполнить в ячейках В1÷В3 программа разместит оптимальные значения переменных одинаковых по величине, равные 2,7144. Площадь поверхности бака, представляющего собой куб, равна 44,208м2.
Если по условию задачи переменные должны принимать целочисленные значения, следует в поле «Ограничения» окна «Поиск решения» записать ограничения (рис.3.5):
a= целое
b= целое
h= целое
В этом случае получим следующий результат:
a=5, b=2, h=2, V=20, S=48
3.2 Решение задачи линейного программирования
В задаче линейного программирования и целевая функция и ограничения линейно зависят от искомых переменных. Такая задача является частным случаем задачи оптимизации, а ее математическая модель имеет вид:
- целевая функция
- ограничения
Здесь постоянные величины - должны быть заданы в условиях задачи, n – количество искомых переменных , i=1,n – номер переменной, m – количество линейных уравнений, которые представляют собой ограничения, k=1,m. Требуется определить такие значения переменных , которые обеспечивают в зависимости от постановки задачи максимум, минимум или постоянное значение целевой функции.
В качестве примера рассмотрим задачу о выборе ассортимента товаров. Фирма выпускает два вида мороженого: сливочное и шоколадное. Для изготовления мороженого используются два исходных продукта: молоко и наполнители. Расходы исходных продуктов на 1кг мороженого и их суточные запасы даны в таблице 3.1.
Отпускная цена 1кг сливочного мороженого 16 р., шоколадного – 14 р. Определить какое количество продукции каждого вида должна производить фирма, чтобы стоимость этой продукции, а следовательно и доход от ее реализации был максимальным.
Таблица 3.1
Математическая модель
Обозначим X1 суточный объем выпуска сливочного мороженого, X2 – шоколадного. Тогда стоимость произведенной продукции (целевую функцию), которую требуется максимизировать, можно записать:
F=16*X1 + 14*X2 (1)
Учитывая указанные в таблице запасы исходных продуктов, запишем ограничения:
0,8*X1+0,5*X2 ≤ 400 (2)
0,4*X1+0,8*X2 ≤ 365 (3)
X1 ≥ 0 (4)
X2 ≥ 0 (5)
В этой задаче надо найти такие значения X1 и X2, которые соответствуют максимуму функции F при наличии приведенных выше ограничений.
Для того, чтобы не только получить конкретное решение, но и исследовать влияние ограничений на доход предприятия, надо создать на листе Excel таблицу (рис.3.7).
В ячейках G4 и G5 запишем предполагаемый суточный объем выпуска мороженого. Эти величины можно задавать произвольно, так как они используются программой только как начальное приближение. Объем суточного выпуска обоих видов мороженого, соответствующий максимуму стоимости выпущенной продукции, будет вычислен и записан в этих же ячейках.
Стоимость выпущенного мороженого подсчитывается по формулам: =16*G4 и =14*G5. Формулы записываются в ячейки H4 и H5. В ячейке H8 должна быть вычислена суммарная стоимость выпущенного мороженого.
Для того, чтобы записать ограничения (2) и (3) так как это требуется в программе, надо вычислить расход исходных продуктов – расход молока по формуле =E4*G4 и расход наполнителя по формуле =E5*G5. Эти формулы должны быть записаны в ячейках E7 и F7. В поле «Ограничения» окна «Поиск решения» должны быть записаны ограничения в виде неравенств E7<=E6 и F7<=F6. Ограничения (4) и (5) можно добавить в окно «Ограничения». Но проще отметить (щелкнуть мышкой) флажок «Неотрицательные значения» в окне «Параметры» (рис.3.6).
Рис 3.7 Таблица расчета стоимости продукции
На рисунке 3.8 приведено окно «Поиск решения», в котором введены все исходные данные для рассмотренного здесь примера.
Рис. 3.8 Окно «Поиск решения» для задачи линейного программирования
3.3 Решение нелинейных уравнений
Используя надстройку «Поиск решения» можно найти решение одного нелинейного уравнения или системы уравнений. Корень одного уравнения можно получить, если в окне «Поиск решения» отметить переключатель «Установить целевую ячейку: равной значению 0».
Пусть требуется найти корень уравнения
\
Поскольку формула, по которой вычисляется значение функции, сложная, следует оформить алгоритм определения значения этой функции в виде подпрограммы Function. Так как функция может иметь несколько корней, следует построить график зависимости функции от аргумента х и выбрать отрезок, на котором эта функция меняет знак. На рисунке 3.9 представлено окно «Поиск решения», с помощью которого был получен результат х=36,67. Указать в поле «Ограничения», что х – целое число здесь нельзя.
Введем в рассмотрение функцию Z(x), равную (F(x))2. Функция F(x) принимает значение ноль в точке х, в которой Z(x) имеет минимум, и этот минимум равен нулю. Минимум Z(x) определяется в соответствии с алгоритмом, описанным в разделе 3.1. Однако, при вычислении минимума Z(x) надо учесть тот факт, что в окрестности точки минимума значения минимизируемой функции могут оказаться меньше заданной в окне «Параметры» относительной погрешности ε, а это приводит к тому, что программа работать не будет. В этом случае для получения решения надо либо уменьшить величину ε, либо умножить функцию Z(x) на большое положительное число (1000, 10000, …).
Рис.3.9 Решение нелинейного уравнения
Если требуется решить систему уравнений, следует возвести в квадрат каждое из уравнений, просуммировать все полученные выражения и найти минимум этой суммы.
Примеры использования надстройки «Поиск решения» приведены в Excel в файле C:\Program Files\Office11\Samples\SolvSamp.xls.
4. Лабораторные работы
Лабораторная работа №1
Решение нелинейного уравнения
Задание 1
Найти корни нелинейного уравнения:
|
Ва-риант |
Уравнение |
Ответ |
|
1 |
для значений =[-2;2] |
0 |
|
2 |
-0,94644 |
|
|
3 |
для =[-3;3] |
-0,32; 1,229997; 2,010001 |
|
4 |
4,700766 |
|
|
5 |
3,542723 |
Задание 2
Определить, сколько надо докупить акций второго типа, чтобы доход по всем ценным бумагам составил 12%:
|
Вариант |
Кол-во акций 1го типа |
Стоимость акций 1го типа |
Доходность акций 1го типа, % |
Кол-во акций 2го типа |
Стоимость акций 2го типа |
Доходность акций 2го типа, % |
|
1 |
40 |
90 |
10 |
10 |
40 |
11 |
|
2 |
25 |
80 |
11 |
4 |
40 |
14 |
|
3 |
35 |
75 |
11 |
10 |
50 |
16 |
|
4 |
40 |
100 |
10 |
20 |
45 |
16 |
|
5 |
60 |
120 |
10 |
6 |
60 |
15 |
Лабораторная работа №2
Оптимизация нелинейной функции
Задание 1
Выполните все приведенные в разделах 3.1 и 3.2 примеры. Каждый пример размещайте на отдельном рабочем листе Excel.
Задание 2
Для аргумента , изменяющегося от до с шагом 0.1, вычислите функцию, в соответствии с указанным преподавателем вариантом. Постройте график зависимости функции от аргумента. Используя «Поиск решения» найдите экстремум (максимум или минимум) этой функции на заданном отрезке изменения аргумента. Вычисление функции оформите в виде подпрограммы Function. Затем найдите целое максимальное значение функции.
|
№ варианта |
Формула |
|||
|
1 |
0 |
3,25 |
0,2 |
|
|
2 |
-1 |
2,3 |
0,2 |
|
|
3 |
0 |
3,2 |
0,2 |
|
|
4 |
-0,2 |
2,5 |
0,1 |
Лабораторная работа №3
Линейные модели
Задание 1
Предприятие выпускает три вида продукции:
- телевизоры;
- стереосистемы;
- акустические системы.
Каждому виду изделий соответствует своя норма прибыли. На складе запас комплектующих, необходимых для производства продукции, ограничен. Определить какое количество каждого вида изделий надо произвести для того, чтобы прибыль была максимальна.
В таблице на стр.24 представлены исходные данные: планируемые количества изделий, норма прибыли по каждому изделию, запас комплектующих на складе. Здесь же необходимо вычислить прибыль по каждому из изделий, расход комплектующих, общую прибыль. Для вычисления прибыли по видам изделий надо умножить количество изделий на норму прибыли.
Математическая модель
Введем обозначения:
х1 – количество телевизоров,
х2 – количество стереосистем,
х3 – количество акустических систем.
Прибыль по каждому из видов изделий равна норме прибыли на 1 изделие умноженной на количества изделий. Целевая функция («Прибыль всего») вычисляется по формуле
F=75*x1+50*x2+35*x3
Исходные значения х1, х2 и х3 указаны в столбце «План производства». Для максимизации прибыли, надо так выбрать эти значения, чтобы они соответствовали максимуму функции F.
В таблице 1 в столбцах с 3 по 7 приведены количества комплектующих, необходимые для производства каждого из трех видов продукции: телевизора, стереосистемы и акустические системы. Общее количество комплектующих каждого вида (шасси, кинескопы, динамики и т.д.) вычисляются в строке «Расход комплектующих» по формулам:
Kшасси =1*x1+1*x2
Kкинескоп =1*x1
Kдинамик =2*x1+2*x2+1*x3
Kблок_пит =1*x1+1*x2
Kэлектр_пл. =2*x1+1*x2+1*x3
Так как расход комплектующих не должен превышать их количество на складе, указанное в соответствующей строке таблицы, в расчетах следует учесть ограничения:
Kшасси ≤ 450, Kкинескоп ≤ 250, Kдинамик ≤ 800,
Kблок_пит ≤ 450, Kэлектр_пл. ≤ 600.
Следует также учесть не отрицательность величин х1, х2 и х3. Итого, имеем 8 ограничений.
Варианты заданий
Таблица для решения задачи в Excel
|
Наименование продукции |
План производства, шт. |
Наименование комплектующих |
Норма прибыли на 1 изделие |
Прибыль по видам изделий |
||||
|
Шасси |
Кинескопы |
Динамики |
Блоки питания |
Электрон. Платы |
||||
|
Телевизор |
200 |
1 |
1 |
2 |
1 |
2 |
75 |
15000 |
|
Стереосистема |
200 |
1 |
0 |
2 |
1 |
1 |
50 |
10000 |
|
Акустическая система |
0 |
0 |
0 |
1 |
0 |
1 |
35 |
0 |
|
Запас комплектующих на складе |
|
450 |
250 |
800 |
450 |
600 |
|
|
|
Расход комплектующих |
|
0 |
0 |
0 |
0 |
0 |
|
|
|
Прибыль всего |
25000 |
Задание 2.
Транспортная задача. Трем фирмам поставляют комплектующие 3 цеха. Ежедневные потребности в комплектующих для каждого из предприятий равны или больше 200, 100 и 100 единиц соответственно. Из цехов можно вывезти ежедневно: 170 единиц комплектующих изделий из первого, 120 - из второго и 150 - из третьего. Тарифы перевозок даны в таблице A.
таблице A
|
\ Цеха Фирмы \ |
Цех 1 |
Цех 2 |
Цех3 |
|
Фирма 1 |
6 |
8 |
9 |
|
Фирма 2 |
4 |
3 |
5 |
|
Фирма 3 |
9 |
5 |
2 |
Определить из каких цехов и в каких объемах поставлять комплектующие изделия фирмам так, чтобы минимизировать стоимость перевозок.
Математическая модель
Обозначим количество комплектующих изделий, которые вывозятся из цеха с номером j на фирму номер i. Тогда стоимость перевозки (целевая функция) запишется в виде:
Здесь первый индекс означает номер фирмы, второй – номер цеха. Количество изделий (), вывезенных из каждого цеха, подсчитывается по формулам:
Обозначим ограничения по производительности цехов. В поле «Ограничения» окна «Поиск решения» следует записать ограничения:
Количество изделий (), доставленных на каждую из фирм, подсчитывается по формулам:
Если обозначить ограничения количества изделий по потребностям фирм, то в поле «Ограничения» окна «Поиск решения» следует добавить ограничения: .
Варианты заданий
|
№ варианта |
Ограничения по производительности цехов |
Ограничения количества комплектующих по потребностям фирм |
||||
|
Цех 1 |
Цех 2 |
Цех 3 |
Фирма 1 |
Фирма 2 |
Фирма 3 |
|
|
1 |
150 |
200 |
250 |
200 |
160 |
120 |
|
2 |
180 |
180 |
200 |
200 |
160 |
120 |
|
3 |
200 |
150 |
180 |
200 |
160 |
120 |
|
4 |
200 |
150 |
180 |
180 |
150 |
130 |
|
5 |
200 |
150 |
180 |
150 |
200 |
100 |
Таблица для решения задачи в Excel
|
|
Затраты на перевозку одного изделия |
Ограничения количчества изделий по потребностям фирм |
Затраты на перевозку всех изделий |
||
|
Цех 1 |
Цех 2 |
Цех 3 |
|||
|
Фирма 1 |
6 |
8 |
9 |
200 |
|
|
Фирма 2 |
4 |
3 |
5 |
100 |
|
|
Фирма 3 |
9 |
5 |
2 |
100 |
|
|
Ограничения по производительности цехов |
170 |
120 |
150 |
|
|
|
|
Количество перевозимых изделий |
||||
|
Цех 1 |
Цех 2 |
Цех 3 |
Вывезено на фирмы |
||
|
Фирма 1 |
0 |
120 |
80 |
200 |
1680 |
|
Фирма 2 |
30 |
0 |
70 |
100 |
470 |
|
Фирма 3 |
140 |
0 |
0 |
140 |
1260 |
|
Вывезено из цехов |
170 |
120 |
150 |
||
|
Всего |
|
3410 |
Содержание
1. Решение нелинейных уравнений и оптимизация в Excel
1.1 Надстройки в Excel 3
1.2 Модели и алгоритмы 4
2. Надстройка «Подбор параметра»
2.1. Введение 6
2.2. Пример использования надстройки «Подбор параметра» 6
2.3. Подбор процентной ставки 7
2.4. Поиск корней нелинейного уравнения 9
2.5. Задача о портфеле ценных бумаг 11
3. Надстройка «Поиск решения»
3.1. Оптимизация нелинейной функции 13
3.1.1. Оптимизация нелинейной функции без учета ограничений 13
3.1.2. Оптимизация нелинейной функции с учетом ограничений 15
3.2. Решение задачи линейного программирования 17
3.3. Решение нелинейных уравнений 19
4. Лабораторные работы
4.1. Лабораторная работа №1 21
Решение нелинейного уравнения
4.2. Лабораторная работа №2 22
Оптимизация нелинейной функции
4.3. Лабораторная работа №3 22
Линейные модели
Литература
- В. Долженков, Ю. Колесников. Microsoft Excel 2002. Наиболее полное руководство, BHV - Санкт-Петербург, 2002.
- Ф. Новиков, А. Яценко. Microsoft Office XP в целом, BHV - Санкт-Петербург, 2002.
- Разработка бизнес-приложений в экономике на базе MS EXCEL. Учебник. Под редакцией к.т.н. А.Н. Афоничкина, М:. Диалог-МИФИ, 2003.
- Б. Курицкий. Поиск оптимальных решений средствами Excel 7.0, BHV - Санкт-Петербург, 1997.
- Документация Microsoft по Excel 2003.
2. Лингвистические особенности германских языков
3. Тема 1- ПОНЯТТЯ ФАБРИК ДУМКИ ЇХ РОЛЬ І МІСЦЕ В СУЧАСНОМУ СУСПІЛЬНОПОЛІТИЧНОМУ ЖИТТІ 1
4. Архітектура промислових і цивільних будівель МЕТОДИЧНИЙ ПОСІБНИК до виконання курсов.html
5. Поконтурная ведомость земельных участков в границах сельского поселения
6. Организация обслуживания в гостиницах и туристских комплексах ОГЛАВЛЕНИЕ Предисловие Глава 1.
7. Х Объектом нашего исследования стал коллектив ТОО Х
8. Сокращение производства и невыполнение договоров о поставках сырья хозяйствами входящими в сырьевые зоны.html
9. Вариант 14 Вопросы к 4 аттестации 2012 Что понимается под термином объектноориентированный Как соз
10. РЕФЕРАТ дисертації на здобуття наукового ступеня кандидата економічних наук.1
11. Упр Родители на стикерах пишут что они теперь делают когда их дети проявляют агрессию
12. относительно обособленные конкретные направления управленческой деятельности осуществляемые специальным
13. Задание 1. Точка задается координатами xy
14. Чайка по адресу- г
15. Исследование ассортимента и качества игрушек на базе компании 001
16. Дворянство в пьесе АП Чехова Вишневый сад
17. методологические подходы к изучению системы информационного обеспечения управленческих решений ор
18. Волошин Максимилиан Александрович
19. на тему Шлях життя і творчості Кирила МефодіяСтавровецького План 1
20. Темы самостоятельных работ по дисциплине 'Геология месторождений полезных ископаемых' для студентов заочного отделения, обучающихся по направлению 'Геология'