Контрольная работа по "Математическому моделированию"

     Нахождение  обратной матрицы  к матрице (Е-А).

     Внесем  исходные данные в ячейки А3:Е6 (см. Приложение 2.1).

       Сформируем искомую матрицу (Е-А), внеся ее элементы в диапазон ячеек В8:Е11.  

 

     Выделим область (диапазон из 16 ячеек) В13:Е16 для размещения обратной матрицы.

     Найдем  обратную матрицу с помощью функции  =МОБР(B8:E11), вводя ее как операцию над массивами, то есть одновременно нажав клавиши <CTRL>, <SHIFT>, <ENTER>.

     На  экране в выделенном диапазоне получим  обратную матрицу. 

 

      Выполнение операции умножения матрицы (Е-А)^-1 и вектор-столбца конечного продукта Y.

     Занесем вектор-столбец Y в ячейки В18:В21.

 
 

     Выделим диапазон E18:E21 для размещения вектора решений X.

     В соответствии с формулами умножение матриц В и Y выполним с помощью функции  =МУМНОЖ(B13:E16;B18:B21) вводя ее как операцию над массивами, то есть одновременно нажав клавиши <CTRL>, <SHIFT>, <ENTER>.

     На  экране в выделенном диапазоне в  ячейках E18:E21 получим вектор-столбец произведения матриц  

 

      Вывод:  X₁, X₂, X₃ равны -13.54, -4.83, 9.92, -146.80 соответственно. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

   2.3 Анализ полученных результатов. 

   Для того чтобы проверить правильность решения  достаточно просто подставить полученные результаты в уравнение.

   В ячейки C23:C26 сделаем проверку.

   =$E$18*B8+$E$19*C8+$E$20*D8+$E$21*E8

   В результате проверки получим  следующий столбец:

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

   Задание 3. 

   Решить  линейную оптимизационную задачу.

   Найти оптимальное решение для модели линейного программирования. Решить задачу с помощью пакета «Поиск решения» ЭТ MS Excel. 

   L(X)=2x₁-x₂+2x₃-x₄+2x₅-3x₆+x₇-x₈à max 

    x₁+x₂+х₃+х₄+4х₅-4х₆+2x₇+2x₈=4,

   x₁+2x₂+3x₃+4х₄+10х₅-10х₆+7x₇+3x₈=9,

   х₁- 3x₂+6x₃+10x₄+20х₅-20x₆ +16x₇+4x₈=16,

   х₁+4х₂+10x₃+20x₄+35х₅-35x₆+30x₇+5x₈=25,

   x_j≥0 (j=8). 
 

   Решение задания 3 предполагает:

1. Математическую постановку задачи.
2. Размещение на рабочем листе ТП MS Excel исходных данных, расчет значений ограничений, расчет значения целевой функции.
3. Формулировка математической модели задачи в терминах ячеек рабочего  листа ТП MS Excel.
4. Поиск оптимального решения поставленной задачи средствами  надстройки «Поиск решения».
5. Анализ результатов.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

   3.1 Построение математической модели для решения оптимизационной задачи 

   Математическая  модель задачи имеет вид:

   Целевая функция L(X)=2x₁-x₂+2x₃-x₄+2x₅-3x₆+x₇-x₈à max

   Система ограничений:      

    x₁+x₂+х₃+х₄+4х₅-4х₆+2x₇+2x₈=4,

   x₁+2x₂+3x₃+4х₄+10х₅-10х₆+7x₇+3x₈=9,

   х₁- 3x₂+6x₃+10x₄+20х₅-20x₆ +16x₇+4x₈=16,

   х₁+4х₂+10x₃+20x₄+35х₅-35x₆+30x₇+5x₈=25,

   X₈≥0 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

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

   Размещение  данных на рабочем  листе ТП MS Excel 

   Создадим  таблицу данных  Х-ов. Для этого  в ячейках А1:А8 запишем названия строк (т.е. х1, х2 и т.д.). В ячейках В1:В8 ничего записывать не будем, так как мы не знаем, какими будут значения Х.

   В ячейках А11:A15 запишем уравнения и целевую функцию. А в ячейках В11:В15 рабочего листа ТП MS Excel внесем формулы расчета. Так, например в  ячейке В11 мы запишем формулу нахождения 1 уравнения:

   =B1+B2+B3+B4+4*B5-4*B6+2*B7+2*B8

   В ячейке В15 рассчитаем значение целевой функции.

   Формулировка  математической модели задачи в терминах ячеек рабочего  листа ТП MS Excel 

   Целевая функция:            ячейка  В15 à max 

    Система ограничений:    

   В11=4

   В12=9

   В13=16

   В14=25

   В8≥0

                           
 

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

   добиться  максимального значения в ячейке В15, изменяя значения ячеек    В1:В8 так, чтобы значения в ячейках В11:В14 были равны заданным значениям при В8≥0. 

   Поиск оптимального решения 

   Окно  надстройки «Поиск решения» (ДанныеèПоиск решения) с постановкой задачи в терминах ячеек рабочего листа Excel приведено ниже: 

   

    

     Окно надстройки «Поиск решения»

    

   Решение задания 3 средствами ТП MS Excel в режиме значений и в режиме формул приведено в Приложениях 3.1-3.2.  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

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

   Вывод: В результате оптимизации получено:

   максимальная  значение 53687096 будет получена при: 

   х₁= 0,267155779

   х₂= 0,267139992

   х₃= 0,899822374

   х₄= -1,100192563

   х₅= -53687091,17

   х₆= -53687091,9

   х₇= 0,367340907

   х₈= 0 

   Результаты  оптимизации можно посмотреть в Отчете по результатам (Приложение 3.3), сформированном «Поиском решения»

 

   

Заключение 
 

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

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

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

• финансовые;
• дата и время;
• математические;
• статистические;
• ссылки и массивы;
• работа с базой данных;
• текстовые;
• логические;
• инженерные.