Контрольная работа по "Информатика"

      Формирование  математической модели задачи включает в себя:

      - формулирование цели;

      - создание целевой функции.

     Наиболее  распространен симплексный метод  для задачи на определения максимума  целевой функции с ограничениями-неравенствами.

     Идея  метода состоит:

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

     - оценки этого выбора с точки зрения оптимальности. Если выбор неоптимален - определение перехода к другой вершине;

     - переход к новой вершине, т.е. получение нового допустимого плана.

      Функция MS Excel «Поиск решения» использует данный алгоритм.

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

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

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

• показатель оптимальности L(X) представляет собой линейную функцию от элементов решения Х = (х₁, х₂, ..., х_n);
• ограничительные условия, налагаемые на возможные решения, имеют вид линейных равенств или неравенств.

      Общая форма записи модели задачи линейного программирования:

L(X) = с₁x₁ + с₂x₂ + ... +с_nx_n → max (min),

при ограничениях

а₁₁x₁ + а₁₂x₂ + ... +а_1nx_n ≤ (≥, =)b₁,

а₂₁x₁ + а₂₂x₂ + ... +а_2nx_n ≤ (≥, =)b₂,

...

а_m1x₁ + а_m2x₂ + ... +а_mnx_n ≤ (≥, =)b_m,

x₁, x₂, ...x_k ≥ 0 (k ≤ n).

      Оптимальное решение – это план, при котором целевая функция принимает свое максимальное (минимальное) значение. 

      3.2. Описание решения  задачи

     Рассчитаем  данную модель средствами MS Excel:

     Левую часть неравенств можно представить в виде таблицы. Диапазон ячеек B1:F5 сделаем шапкой таблицы (х₁, х₂, х₃, х₄, х₅).

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

      В диапазон ячеек В3:F6 занесем значения параметров х₁, х₂, х₃, х₄, х_5.

      В диапазоне ячеек B7:F7 разместим ограничения (из условия 4, 6, -14, 0, 49 соответственно)

      За  целевую ячейку примем ячейку G2, в которую введем формулу:

     =B2*$B$7+C2*$C$7+D2*$D$7+E2*$E$7+F2*$F$7

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

      Скопируем эту формулу для ячеек G3, G4, G5, G6. Для этого будем использовать метод автозаполнения.

      Таким образом, в ячейках G3:G6 будут содержаться следующие формулы:

      =B3*$B$7+C3*$C$7+D3*$D$7+E3*$E$7+F3*$F$7 – ячейка G3.

      =B4*$B$7+C4*$C$7+D4*$D$7+E4*$E$7+F4*$F$7 – ячейка G4.

      =B5*$B$7+C5*$C$7+D5*$D$7+E5*$E$7+F5*$F$7 – ячейка G5.

      =B6*$B$7+C6*$C$7+D6*$D$7+E6*$E$7+F6*$F$7 – ячейка G6.

      В диапазоне ячеек H3:H6 запишем знаки неравенств (по условию).

      В диапазон ячеек I3:I6 разместим правую часть неравенств (значения 58, 290, 72 и 140 соответственно).

      Представим  графически разработанный электронный документ в режиме формул и в обычном режиме: 

 

      Для решения этой задачи воспользуемся  надстройкой «Поиск решения».

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

      В поле Изменяя ячейки необходимо указать диапазон ячеек, включающих ячейки, значения которых программа должна изменить для получения оптимального результата. Указываем диапазон ячеек B7:F7.  Отмечаем, что рассчитываемая в целевой ячейке величина должна быть равна минимальному значению.

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

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

      В нашем задании необходимо указать  пять ограничений:

$В$7 >= $F$7

$G$3 >= $I$3

$G$4 <= $I$4

$G$5 = $I$5

$G$6 >= $I$6

     

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

     После нажатия кнопки «Выполнить», в окне выбираем Тип отчёта - Результаты. Нажимаем ОК.

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

 

      3.3. Анализ отчета  по результатам 

     Рассмотрим  отчет по результатам, сформированный при решении задачи с целочисленными ограничениями.

      Отчет по результатам состоит из трех таблиц:

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

     Для каждого ограничения выводятся «статус» и «разница». Разница - это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, заданным в правой части формулы ограничения. Статус может принимать три состояния: «Связанное» (ограничение, для которого значение разницы равно 0), «Не связанное» (ограничение, которое было выполнено с ненулевым значением разницы) и «Не выполненное». 

      Функция L(X) = 4x₁ + 6x₂ - 14x₃ + 49x₅ достигает минимального значения равного -89,59 при х₁ =3,6, х₂ =0, х₃ =7,4, х₄ =9,2 и х₅ =0.

 

4. Заключение 

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

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

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

 

5. Графический материал 

Приложение  к заданию 1 

     Таблица в режиме отображения формул 

 

 

      Таблица в обычном режиме  

 

 

      Диаграмма для функции y=f(x)

 

     Диаграмма для функции g=f(x)

 

 

Приложение  к заданию 2 

     Метод Крамера (в режиме формул)

 

 

      Метод Крамера (в обычном режиме)

 
 

 

      Матричный способ (в режиме формул)

     Матричный способ (в обычном режим)

 

 

Приложение  к заданию 3 

     Электронный документ (в обычном режиме)

 

     Электронный документ (в режиме формул)

 
 
 
 
 
 
 
 

 

      Окно «Поиск решения»

     Окно  «Результаты поиска решения»

     Электронный документ (с расчетами)

 

      Отчет «Результаты»

 
 
 
 

 

6. Список использованной литературы 

1. Вардомацкая, Е. Информатика. В двух частях. Часть II. Excel: учебное пособие / Е.Ю. Вардомацкая, Т.Н. Окишева. - Витебск: УО «ВГТУ», 2007. - 237 с.
2. Гельман В.Я. Решение математических задач средствами Excel: Практикум.- СПб.: Питер, 2003.- 237 с.
3. Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0. СПб.: BHV-Санкт-Петербург, 1997. 384 с.
4. Лавренов С.М. Excel: Сборник примеров и задач.- М.: Финансы и статистика, 2002.- 336 с.
5. Мачула В. Г. Excel 2007 на практике. М.: Феникс, 2009. – 160 с.
6. Мур Дж., Уэдерфорд Л. Экономическое моделирование в Microsoft Excel, 6-е изд.: Пер. с англ. - М.: Издательский дом «Вильямс», 2004. - 1024 с.
7. Пикуза В., Гаращенко А. Экономические и финансовые расчеты в Excel. Самоучитель. 2-е изд. С-Пб, 2008. - 384 с.
8. Решение уравнений средствами Excel: Методические указания к лабораторным работам/ Сост.: Е.Е. Дадонова, А.Г. Пимонов, М.А. Тынкевич; ГУ Кузбас. гос. техн. ун-т.- Кемерово, 2002.- 22 с.
9. Шарстнев В.Л., Бром Е.Л.,  Вардомацкая Е.Ю., Калиновская Е.А. Методические указания к лабораторному практикуму по «Экономико-математическим методам и моделям». Раздел «Оптимальное решение экономических задач методами линейного программирования». - Витебск, ВГТУ, 2001. - 17с.