Поиск решения
Широкий класс экономических задач составляют задачи оптимизации. Задачи оптимизации предполагают поиск значений аргументов, доставляющих функции, которую называют целевой, минимальное или максимальное значение при наличии каких-либо дополнительных ограничений. MS Excel располагает мощным средством для решения оптимизационных задач. Это инструмент-надстройка, который называется Поиск решения (Solver). Поиск решения доступен через меню Сервис/Поиск решения….
Задачу решения СЛАУ (7.1) можно свести к оптимизационной задаче. Для чего одно из уравнений (например, первое) взять в качестве целевой функции, а оставшиеся n-1 рассматривать в качестве ограничений. Запишем систему (7.1) в виде
|
(7.12) |
Тогда задача оптимизации для Поиска решения может звучать следующим образом. Найти значения X = (x1, x2, …, xn)T, доставляющие нуль функции, стоящей слева в первом уравнении системы (7.12) при n-1 ограничениях, представленных оставшимися уравнениями.
Для
решения этой задачи необходимо записать
выражения (формулы) для вычисления
значений функций, стоящих слева в
уравнениях системы (7.12)
.Отведем под эти формулы интервал C7:C9
текущего рабочего листа (Лист3).
В ячейку C7
введем формулу =A3*$B$7+B3*$B$8+C3*$B$9-D3
и скопируем ее в оставшиеся C8
и C9.
В них появятся соответственно
=A4*$B$7+B4*$B$8+C4*$B$9-D4
и =A5*$B$7+B5*$B$8+C5*$B$9-D5.
Осталось, обратившись к пункту меню
Сервис/Поиск
решения…,
в окне диалога (рис.
7.7)
задать параметры поиска (установить
целевую ячейку C7
равной нулю, решение в изменяемых ячейках
B7:B9,
ограничения
заданы формулами в ячейках C8
и С9).
После щелчка по кнопке Выполнить
в интервале B7:B9
получим результат (рис.
7.8)
– решение СЛАУ (7.7).
В
завершение работы можно защитить ячейки
созданных таблиц от несанкционированного,
часто случайного, изменения и скрыть
формулы, по которым находится решение
СЛАУ. Для этого существует стандартное
средство Excel – пункт меню Сервис/Защита/Защитить
лист….
П
еред
этим необходимо снять защиту с ячеек,
содержащих исходные данные (A3:C5
– элементы матрицы A
(7.8)
и D3:D5
– элементы вектора B),
выделив эти интервалы, выбрав меню
Формат/Ячейки…
вкладка Защита
и сбросив флажок Защищаемая
ячейка.
Для ячеек же, содержащих формулы, надо
в этом диалоге (Формат
ячеек)
установить флажок Скрыть
формулы.
Надо знать, что после такой защиты
невозможно будет воспользоваться
средством Поиск
решения.
Поэтому защитить ячейки и скрыть формулы
можно на первом и втором листах. В случае
необходимости можно скрыть и отображаемую
в ячейках информацию, поставив в
соответствие этим ячейкам пользовательский
формат ;;; (три точки с запятой).
7.4.Варианты задания
В соответствии с номером варианта выберите из приведенных ниже систему линейных алгебраических уравнений четвертого (n=4) порядка. Приведите ее к нормальному виду (7.1). Разработайте таблицы Excel для решения выбранной СЛАУ тремя различными способами:
) методом Крамера,
) матричным способом,
) используя Поиск решения.
Варианты систем линейных алгебраических уравнений приведены ниже.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Правильность полученных решений легко проверить. В результате решения выбранной СЛАУ тремя различными способами должны получиться три одинаковых решения. Однако, на всякий случай, приведем решения систем 1)-36):
1)-6) X = (-5,1,-3,4)T; |
7)-12) X = (1,-3,-1,0)T; |
13)-18) X = (1,0,-2,3)T; |
19)-24) X = (1,0,0,1)T; |
25)-30) X = (0,1,1,0)T; |
30)-36) X = (5,0,-5,0)T. |