Лабораторная работа №4 работа с надстройкой «поиск решения»




Скачать 166.12 Kb.
НазваниеЛабораторная работа №4 работа с надстройкой «поиск решения»
Дата публикации12.02.2014
Размер166.12 Kb.
ТипЛабораторная работа
zadocs.ru > Математика > Лабораторная работа

Лабораторная работа № 4
РАБОТА С НАДСТРОЙКОЙ «ПОИСК РЕШЕНИЯ» В MICROSOFT EXCEL. Нахождение корней трансцендентного уравнения, поиск экстремума функции, решение задач линейного программирования


Цель занятия

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


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

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



Рис. 1. Подключение надстройки Поиск решения

Задания

  1. ^

    Нахождение корней трансцендентного уравнения


ПРИМЕР. Найти все корни трансцендентного уравнения

3x2 + cos(x+1) – 3 = 0.

РЕШЕНИЕ. Построим график (тип Точечный) функции y = 3x2 + cos(x+1) – 3 в диапазоне x = [-5; 5].



Рис. 2. Формулы для расчета координат точек графика

По графику определим приближенное значение корней (точка пересечения кривой с осью Х, где значение функции равно нулю): xк1 -0,9; xк2  0,9.



^ Рис. 3. Приближенное определение корней по графику

Далее подготавливаем данные для расчета точного значения первого корня.

На Листе2 в одной из ячеек задаем начальное значение х для поиска корня (оно называется начальное приближение), в другой ячейки – формулу для расчета значения функции y на основе х из ячейки с начальным значением. Добавляем комментарии.



^ Рис. 4. Подготовка данных для расчета точного значения первого корня

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



Рис. 5. Настройка диалогового окна поиска решения

Надстройка Поиск решения запишет в изменяемую ячейку значение, при котором функция обращается в ноль. Это будет первый корень, равный –0,82.

Для поиска второго корня зададим новое начальное приближение 0,9 и запустим надстройку Поиск решения.

Выполнить задание по вариантам и оформить согласно образцу в Microsoft Excel.

Значение параметра b равно номеру Вашего варианта. Значение переменной произвольно.

Вариант

Функция

Вариант

Функция

1



8



2



9



3



10



4



11



5



12



6



13



7



14





  1. ^

    Поиск экстремума функции


ПРИМЕР. Найти минимум функции

y = 3x2 + cos(x+1) – 3.

РЕШЕНИЕ. Построим график (тип Точечный) функции y = 3x2 + cos(x+1) – 3. Определяем по графику количество локальных экстремумов (экстремум – максимум или минимум). В нашем случае – один.

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

Значение параметра b равно номеру Вашего варианта. Значение переменной произвольно.

Вычислить корень x0 уравнения f(x)=0 c точностью до тысячных.

Вариант

Функция

Вариант

Функция

1



8



2



9



3



10



4



11



5



12



6



13



7



14



Примечание. Решить уравнение с использованием стандартных возможностей MS Excel (Подбор параметра).

  1. ^

    Поиск экстремума функции


На отрезке [a,b] с шагом h=(b-a)/20 составить таблицу значений и построить график функции, заданной в таблице. Результаты вывести с точностью до сотых.


Вариант

Функция

Вариант

Функция

1



8



2



9



3



10



4



11



5



12



6



13



7



14






Вариант

a

b

Вариант

a

b

1

-1

1

7

-2

2

2

-1

1

8

-1

1

3

-1

1

9

-1

2

4

-1

1

10

-1

1

5

-1

2

11

0

2

6

-1

1

12

-1

2



  1. ^

    Решение задачи линейного программирования


ПРИМЕР. Условие задачи. Предприятие производит два продукта А и В, рынок сбыта которых неограничен. Каждый продукт должен быть обработан последовательно на станках I, II. Время обработки в часах на каждом из них соответственно изделий А и В приведено ниже:

Продукт

Станок

Время обраб.,ч

А

В

I

0,4

0,2

II

0,2

0,3

Время работы станков I, II ограничено соответственно 41 и 36 часами в неделю. Прибыль от изделий А и В составляет соответственно 500 и 300 руб.

Определить недельные нормы выпуска изделий А и В, максимизирующие прибыль.
РЕШЕНИЕ. Запишем все условия задачи в одну таблицу.

Продукт

Станок

Время обраб.,ч

Фонд работы станков, ч/нед.

А

В

I

0,4

0,2

41

II

0,2

0,3

36

Прибыль, руб.

500

300




1. Зададим неизвестные: x, y – количество выпускаемых в неделю изделий А и В соответственно.

2. Запишем целевую функцию – величина недельной прибыли (руб.), зависящая от x и y:

P = 500x + 300у  mах

3. При поиске максимума на целевую функцию накладываются ограничения – лимиты недельных фондов работы станков:

0,4x + 0,2y  41;

0,2x + 0,3y  36.

Значения x и y должны быть положительны:

x  0; y  0.

4. Разместим исходные данные для решения задачи на листе Excel (порядок размещения – любой). Снабдим данные и формулы комментариями. Начальные приближения для x и y зададим равными 1 (так проще проверять формулы).



^ Рис. 6. Исходные данные к решению задачи линейного программирования
(ячейки с белым фоном содержат текстовые комментарии)

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

Будет получен ответ: недельная программа выпуска изделий А и В равна 63,75 и 77,5 шт. соответственно (дробная часть означает величину незавершенного производства).



^ Рис. 7 . Первый результат работы команды Поиск решения

Для получения ответа в целых числах, добавим ограничения:



Будут получены следующие результаты.



Рис. 8. Окончательный результат работы команды Поиск решения
^

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



Наиболее простым и наглядным методом линейного программирования (ЛП) является графический метод. Он применяется для решения задач ЛП с двумя переменными.

Рассмотрим задачу ЛП в стандартной форме записи:



Положим n=2, т.е. рассмотрим эту задачу на плоскости. Пусть система неравенств совместна (имеет хотя бы одно решение).

Каждое неравенство этой системы геометрически определяет полуплоскость с граничной прямой ai1 x1 + ai2 x2 = bi , i=1,2,…m. Условия неотрицательности определяют полуплоскости, соответственно, с граничными прямыми x1=0,x2 =0. Система совместна, поэтому полуплоскости, как выпуклые множества, пересекаясь, образуют общую часть, которая является выпуклым множеством и представляет собой совокупность точек, координаты каждой из которых являются решением данной системы. Совокупность этих точек называют многоугольником решений. Он может быть точкой, отрезком, лучом, многоугольником, неограниченной многоугольной областью.

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

Линейное уравнение описывает множество точек, лежащих на одной прямой. Линейное неравенство описывает некоторую область на плоскости. Определим, какую часть плоскости описывает неравенство 1+3х2£ 12. Во-первых, построим прямую 1+3х2=12. Эта прямая проходит через точки (6, 0) и (0, 4). Для того чтобы определить, какая полуплоскость удовлетворяет неравенству необходимо выбрать любую точку на графике, не принадлежащую прямой, и подставить ее координаты в неравенство. Если неравенство будет выполняться, то данная точка является допустимым решением и полуплоскость, содержащая точку, удовлетворяет

неравенству. Удобной для использования при подстановке в неравенство является начало координат. Подставим х1=х2=0 в неравенство 1+3х2£12. Получим 2´0+3´0£12. Данное утверждение является верным, следовательно, неравенству 2х1+3х2£12 соответствует нижняя полуплоскость, содержащая точку (0.0). Это отражено на графике, изображенном на рис.9.




Рис. 9. Неравенству 2х1+3х2£12 соответствует нижняя полуплоскость.
Аналогично можно изобразить графически каждое ограничение задачи линейного программирования.

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

.

условиям неотрицательности (xj ³0, j=1,…,n). Координаты любой точки, принадлежащей области определения являются допустимым решением задачи.

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

.

Этот вектор показывает направление наискорейшего изменения це­левой функции. Прямая , перпендикулярная вектору–градиенту, является линией уровня целевой функции. В любой точке линии уровня целевая функция принимает одно и то же значение. Приравняем целевую функцию постоянной величине “a”. Меняя значение “a”, получим семейство параллельных прямых, каждая из которых является линией уровня.

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

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

Графический метод решения ЗЛП состоит из следующих этапов.

  1. Строится многоугольная область допустимых решений ЗЛП – ОДР,

  2. Строится вектор-градиент ЦФ в какой-нибудь точке Х0 принадлежащей ОДР –

.

3. Линия уровня C1x1+C2x2 = а (а–постоянная величина) - прямая, перпендикулярная вектору –градиенту – передвигается в направлении этого вектора в случае максимизации f(x1,x2) до тех пор, пока не покинет пределов ОДР. Предельная точка (или точки) области при этом движении и является точкой максимума f(x1,x2).

4. Для нахождения ее координат достаточно решить два уравнения прямых, получаемых из соответствующих ограничений и дающих в пересечении точку максимума. Значение f(x1,x2), найденное в получаемой точке, является максимальным.

При минимизации f(x1,x2) линия уровня перемещается в направлении, противоположном вектору-градиенту. Если прямая при своем движении не покидает ОДР, то соответствующий максимум или минимум f(x1,x2) не существует.

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

Рассмотрим графическое решение задач линейного программирования на следующем примере.

Задача 1. о планировании выпуска продукции пошивоч­ному предприятию. (Задача о костюмах).

Намечается выпуск двух видов костюмов - мужских и женских. На женский костюм требуется 1 м шерсти, 2 м лавсана и 1 человеко-день трудозатрат. На мужской костюм - 3,5 м шерсти, 0,5 м лавсана и 1 человеко-день трудозатрат. Всего имеется 350 м шерсти, 240 м лавсана и 150 человеко-дней трудозатрат. Tребуется определить, сколько костюмов каждого вида необходимо сшить, чтобы обеспечить максимальную прибыль, если прибыль от реализации женского костюма составляет 10 денежных единиц, а от мужского - 20 денежных единиц. При этом следует иметь в виду, что необходимо сшить не менее 60 мужских костюмов.

Модель задачи.

Введем следующие обозначения: х1 - число женских костюмов; x2 - число мужских костюмов.

Прибыль от реализации женских костюмов составляет 10х1, а от реализации мужских 20х2, т.е. необходимо максимизировать целевую функцию

f(x) = 10´ х1 + 20´ х2 -> max.

Ограничения задачи имеют вид:

х1 + х2 £ 150

2 х1 + 0.5 х2 £ 240

х1 + 3.5 х2 £ 350

х2³ 60

х1 ³ 0

Первое ограничение по труду х1 + х2 £ 150. Прямая х1 + х2 = 150 проходит через точки (150, 0) и (0, 150).

Рис. 10. Решением первого неравенства является нижняя полуплоскость.
Второе ограничение по лавсану 2 х1 + 0.5 х2 £ 240. Прямая 2 х1 + 0.5 х2 = 240 проходит через точки (120, 0) и (0, 480). Третье ограничение по шерсти х1 + 3.5 х2 £ 350. Добавим четвертое ограничение по количеству мужских костюмов х2 ³ 60. Решением этого неравенства является полуплоскость, лежащая выше прямой х2 = 60. На рис.11. заштрихована область допустимых решений.




Рис. 11. Заштрихована область допустимых решений.
Для определения направления движения к оп­тимуму построим вектор-градиент Ñ, координаты которого являются частными производными целевой функции, т.е. = (10;20).

Что бы построить этот вектор, нужно соединить точку (10;20) с началом координат. При макси­мизации целевой функции необходимо двигаться в направ­лении вектора-градиента, а при минимизации — в противо­положном направлении. Для удобства можно строить век­тор, пропорциональный вектору Ñ. Так, на рис. 2.1.6. изобра­жен вектор градиент (30;60).

В нашем случае движение линии уровня будем осущест­влять до ее выхода из области допустимых решений. в крайней, угловой точке достигается максимум целевой функции. Для нахождения координат этой точки достаточно решить два уравнения прямых, получаемых из соответствующих ограничений и дающих в пересечении точку максимума: х1 + 3.5 х2 = 350

х1 + х2 = 150 .

Отсюда лег­ко записать решение исходной ЗЛП: max f(x) = 2300 и дости­гается при x1=70 и x2=80 (рис. 12.)

Рис.12. Максимум целевой функции достигается в точке (70, 80).


Решить графическим

Вариант 1

Max f ( x ) = 3X1 + 2X2

X1 + 2X2 ≤ 11

2X1 - X2 ≥ 5

X1 + 3X2 ≥ 14

X1 , X2 ≥ 0

Вариант 2

Max f ( x ) = 3X1 + 2X2

X1 + 2X2 ≤ 12

2X1 - X2 ≥ 7

X1 + 3X2 ≥ 14

X1 , X2 ≥ 0
Вариант 3

Max f ( x ) = 3X1 + 2X2

X1 + 2X2 ≥ 10

2X1 - X2 ≤ 18

X1 + 3X2 ≤ 13

X1 , X2 ≥ 0

Вариант 4

Min f ( x ) = 3X1 + 2X2

X1 + 2X2 ≥10

2X1 - X2 ≥ 10

X1 + 3X2 ≤ 13

X1 , X2 ≥ 0
Вариант 5

Max f ( x ) = 4х1+ 3х2

х1 + 2х2 £ 10

х1 + 2х2 ³ 2

2х1 + х2 £ 10

х1 ³ 0, х2 ³ 0

Вариант 6

Min f ( x ) = 3X1 + 2X2

X1 + 2X2 ≥12

2X1 - X2 ≥ 12

X1 + 3X2 ≤ 14

X1 , X2 ≥ 0

Вариант 7

Max f ( x ) = 3х1+ 5х2

х1 + х2 £ 5

3х1 + 2 х2 £ 8

х1 ³ 0, х2 ³ 0
Вариант 8

Min f ( x ) = 3X1 + 2X2

X1 + 2X2 ≤ 11

2X1 - X2 ≥ 5

X1 + 3X2 ≥ 14

X1 , X2 ≥ 0
Вариант 9

Max f ( x ) = 3х1+ х2

2х1 + 3х2 ³ 12

-х1 + х2 £ 2

2х1 - х2 £ 2

х1 ³ 0, х2 ³ 0
Вариант 10

Max f ( x ) = 3х1+ х2

х1 + х2 £ 5

0.5х1 + х2 ³ 3

х1 - х2 ³ 1
Вариант 11

Max f ( x ) = X1 + 2X2

X1 - 2X2 ≤ 10

2X1 - X2 ≥ 17

X1 + 3X2 ≥ 1

X1 , X2 ≥ 0
Вариант 12

Max f ( x ) = 5X1 + 12X2

X1 + 6X2 ≤ 120

4X1 - X2 ≥ 77

X1 + 3X2 ≥ 14

X1 , X2 ≥ 0
Вариант 13

Max f ( x ) = 3х1- х2

2х1 - 3х2 ³ 2

-х1 - х2 £ 20

2х1 + х2 £ 22

х1 ³ 0, х2 ³ 10
Вариант 14

Max f ( x ) = 3х1- х2

2х1 + 3х2 ³ 32

-х1 + х2 £ 52

2х1 - х2 £ 25

х1 ³ 0, х2 ³ 0


Добавить документ в свой блог или на сайт

Похожие:

Лабораторная работа №4 работа с надстройкой «поиск решения» iconЛабораторная работа № Лабораторная работа №3 Тема: «Работа с панелью...
Основные приемы работы(контекстное меню, выделение, группирование объектов, перетаскивание мышью, получение справки)

Лабораторная работа №4 работа с надстройкой «поиск решения» iconЛабораторная работа № Работа с массивами и записями
Получить представление о том, что такое массив и научиться разрабатывать алгоритмы решения задач с использованием массивов в среде...

Лабораторная работа №4 работа с надстройкой «поиск решения» iconЛабораторная работа №5. Генерация отчетов в субд access лабораторная...
Лабораторная работа №3. Изменения экранного образа таблицы в субд access лабораторная работа №4. Простые и сложные запросы к базе...

Лабораторная работа №4 работа с надстройкой «поиск решения» iconЛабораторная работа №2 «Поиск информации в Internet»
Современные службы поиска в Internet ориентированы, прежде всего, на поиск в Web-пространстве Internet и условно могут быть разделены...

Лабораторная работа №4 работа с надстройкой «поиск решения» iconЛабораторная работа №1. Основные этапы создания программ (на примере...
Лабораторная работа №1. Основные этапы создания программ (на примере программы для решения квадратных уравнений) 2

Лабораторная работа №4 работа с надстройкой «поиск решения» iconЛабораторная работа № Решение нелинейных уравнений заданным методом
На первом этапе производится отделение корней – поиск интервалов, в которых содержится только по одному корню. Второй этап решения...

Лабораторная работа №4 работа с надстройкой «поиск решения» iconЛабораторная работа выполняется по темам: «Оптимизационные экономико-математические...
Лабораторная работа выполняется и защищается в соответствии с утвержденным расписанием занятий

Лабораторная работа №4 работа с надстройкой «поиск решения» iconЛабораторная работа по теме «Тема 10. Лабораторная работа «Текстовые файлы»
Цель лабораторной работы состоит в изучении средств vb и средств vs для работы с текстовыми файлами

Лабораторная работа №4 работа с надстройкой «поиск решения» iconЛабораторная работа №5 Планирование работ средствами Microsoft Excel
Цель. Изучить некоторые возможности Microsoft Excel (условное форматирование, проверка ввода, работа со ссылками и массивами и т...

Лабораторная работа №4 работа с надстройкой «поиск решения» iconЗакон Ома для участка цепи без эдс. Сопротивление проводника. Падение...
Лабораторная работа: «Измерение длины световой волны с помощью дифракционной решётки»

Вы можете разместить ссылку на наш сайт:
Школьные материалы


При копировании материала укажите ссылку © 2013
контакты
zadocs.ru
Главная страница

Разработка сайта — Веб студия Адаманов