Работа с оптимизатором (стр. 2 из 3)
После ввода всех ограничений нажимаем кнопку «Параметры» и появится след. окошко:
В этом окошке отмечаем «галочкой» пункт «Линейная модель» и нажмем кнопку «ОК».
Далее в предыдущем рисунке нажав кнопку «Выполнить» получим следующий рисунок-окно:
На этом рис. Видно, что найдено оптимальное решение: оптимальные объемы перевозок приведено в ячейках А6:Е9, а оптимальное значение целевой функции дано в ячейке F10 равной значению 1430
Подбор параметра для решения задач моделирования
В экономике чаще используется математическое моделирование с помощью описания экономических задач математическими зависимостями.
Экономико-математические модели включают в себя совокупность математических зависимостей, логических построений, схем, графиков и т.д., связанных в некоторую единую систему, имеющую экономический смысл.
Математические зависимости представляют собой некоторые целостные математические структуры в виде алгебраических, дифференциальных и других уравнений.
Одной из проблем моделирования является задача обеспечения точности решения, получаемого с помощью модели.
Здесь мы рассмотрим решения моделей описываемых уравнением
(1)Для решения данной задачи воспользуемся командой «Подбор параметра» из пакета MS EXCEL. В основу «Подбор параметра» заложен итерационный принцип, когда для нахождения решения уравнения используется последовательные приближения до тех пор, пока не будет достигнута требуемая точность.
Пример 3. Решить уравнение:
(2)Нам известно, что это уравнение имеет единственное решение и оно расположено на отрезке [-1; 0]. В качестве начального значения можно выбирать любую точку отрезка. Мы положим начальное значение
и поместим ее в ячейку В2 на листе EXCEL, а в ячейку В3 вводим функцию 
и тем самым получая в этой ячейке ее значение.В ячейку А2 введем текстовое выражение «х=», а в ячейки А1 и А3 соответственно введем следующие текстовые строки: «Начальное значение аргумента» и «Значение функции» и получим следующее окно в EXCELе:
Далее выделив ячейку В3 мышкой, где хранится значение функции, используем команду «Подбор параметра» из меню «Сервис» главного меню EXCEL.
Тогда получим следующее окно на EXCELе:
В этом окне в ячейке «Установить в ячейке» вводим адрес В3, где хранится значение функции, а в ячейку «Значение» вводим начальное значение аргумента клавиатурой равное 0 и наконец в ячейке «Изменяя значение ячейки» нажимаем мышкой а затем нажимаем на ячейке В2 т.е. введем адрес значения переменной аргумента как показано на рисунке слева.
После этого нажимаем мышкой кнопку «ОК» и получим следующее окно:
В этом окне видно, что мы получили значение аргумента в ячейке В2 = -0,56714, а в ячейке В3 округленное значение функции
7,59Е-06, а в правом окне приведено точное значение функции с 5- знаками после запятой как «Текущее значение: 7,58615Е-06.Итак, мы получили более точное решение уравнения (2) и соответствующее ему более точное значение функции.
Численные методы решения систем линейных алгебраических уравнений на EXCELе
При решении задач линейного моделирования требуется решить систему линейных уравнений. Рассмотрим основную задачу линейного программирования:
Min <c, x>
X Є R1 (1)
где R1 = { x: Ax ≤ b, x ≥ 0 }
Задачу (1) приводим к каноническому виду:
, где новые вспомогательные переменные. Здесь множество R1 представляет множество с угловыми точками, являющимися решениями системы уравнений:а11х1 + а12х2 + … + а1nхn + u1 =b1,
а21х1 + а22х2 + … + а2nхn + u2 =b2, (2)
аm1х1 + аm2х2 + … + аmnхn + um =bm
Как известно, решения задач вида (1) лежат в угловых точках множества R1 , которых необходимо вычислить.
Из курса алгебры известно, что такие системы уравнений вида (2) мы можем решать следующими методами а) метод Крамера; б) метод Обратной матрицы; 3) метод Гаусса.
Мы рассмотрим решение систем линейных алгебраических уравнений методом Гаусса в предположении, что m=n, т.е. число уравнений и неизвестных совпадают.
Пример 4: Решаем систему уравнений методом Гаусса:
х1 + 3х2 -2х3 - х4 =-3,
2х1 - 4х2 + х3 + х4 = 1, (2)
х1 + х2 - 3х3 + 2х4 = 0,
3х1 + х2 - 2х3 + х4 = 3
Откроем окно EXCEL. В диапазоны ячеек А1:D4 введем матрицы коэффициентов системы (2), а в – Е1:Е4 значения столбца свободных членов, как показано справа:
Содержимое ячеек А1:Е1 скопируем в ячейки А6:Е6, А11:Е11 и А16:Е16. В диапазон ячеек А7:Е7 введем формулу:
=А2:Е2-$A$1:$E$1*(A2/$A$1) обращающая в нуль коэффициент х1 во втором
уравнении системы. Выделим диапазон А7:Е7 и протащим маркер заполнения этого диапазона так, чтобы заполнить диапазон А7:Е9. Это действие обратит в нуль коэффициент х1 в третьем и четвертом уравнениях системы (см. рис. выше).
Теперь скопируем значения из диапазона ячеек А7:Е7 в диапазоны А12:Е12 и А17:Е17.
Для копирования значений без формул выделяем мышкой диапазон А7:Е7, затем из меню «Правка» нажимаем «Копировать», после этого мышкой выделяем диапазон А12:Е12 (куда мы должны вставить значения), а теперь из меню «Правка» выбираем подменю «Специальная вставка» и в открывшемся диалоговом окне «Специальная вставка» в группе «Вставить» установим переключатель в положение «Значения» и нажимаем кнопку «ОК».
В диапазон ячеек А13:Е13 вводим формулу: =А8:Е8-$A$7:$E$7*(B8/$B$7).
Результаты преобразований приведены на рисунке выше. Выделим диапазон ячеек А13:Е13 и протащим маркер заполнения этого диапазона так, чтобы заполнить диапазон ячеек А13:Е14, в результате чего вышеуказанная формула обращает в нуль коэффициенты х2 в третьем и четвертом уравнениях системы.
Копируем значения из диапазона ячеек А12:Е12 в диапазон А18:Е18. В диапазон ячеек А19:Е19 вводим формулу: =А14:Е14-$A$13:$E$13*(С14/$С$13), которая обращает в нуль коэффициент при х3 в четвертом уравнении системы. Прямая прогонка метода Гаусса завершена.
Обратная прогонка заключается во вводе в диапазоны G4, G3; G2 и G1 соответственно следующих формул:
= E19/D19
=(E18-D18*G4)/C18 (3)
=(E17-C17*G3-D17*G4)/B17
=(E16-B16*G2-C16*G3-D16*G4)/A16
В диапазоне ячеек: G1:G4 будет получено решение системы (2):
х1=1, х2=2, х3=3, х4=4.
Пример 5: Решаем систему уравнений (2) методом вычисления обратной матрицы. Введем матрицу коэффициентов в ячейки А1:D4 и столбец свободных членов системы (2) в ячейки F1:F4 в таблицу EXCEL, как показано ниже на рис:
Для вычисления обратной матрицы выделим мышкой диапазон А6:D9, затем нажимаем из меню «Вставка» подменю «Функция», после чего появляется окно «Мастер функций» и в нем в строке «Категория» выбираем «Математическое» а в нижнем окошечке выбираем функцию «МОБР» и получим след. вид экрана:
В этом окошке далее нажимаем кнопку «ОК», после этого появится следующее окошко ввода аргумента функции и далее мышкой выбираем диапазон А1:D4, как показано на след. рисунке:
После этого в этом окошке нажимаем кнопку «ОК» и тогда ячейка А6 будет иметь следующий вид:
Затем нажимаем кнопку F2, тогда вид экрана примет следующий вид:
Далее нажимаем три следующие клавиши «Ctrl+Shift+ Enter» одновременно и получим значения коэффициентов обратной матрицы, приведенных ниже:
Далее вводим в ячейки А11:А14 названия переменных: Х1-Х4, затем отмечаем мышкой ячейки В11:В14, в которых будут вычисленные значения переменных Х1-Х4, введем формулу умножения значения коэффициентов обратной матрицы на вектор значений свободных членов. Для этого выделяем ячейки В11-В14, затем из меню «Вставка» выберем пункт подменю «Функция», в строке «Категория» выбираем «Математическое» а в нижнем окошечке выбираем функцию «МУМНОЖ» и получим след. вид экрана как показано в следующем рисунке:
