Vba_расчеты

При решении задачи A действия выполняются над числовыми коэффициентами, а имена неизвестных не участвуют в вычислениях. Поэтому при решении задачи в Excel будем хранить только коэффициенты и значения правых частей уравнений, записывая их в расширенную (n*(n+1))-матрицу Ab: A x b с расширенной (n (n 1)) -матрицей Ab

Для изучения в Excel метода Гаусса с выбором главного элемента по столбцу для решения СЛАУ используем следующий алгоритм:

расширенную матрицу Ab. Матрица перестановок – это единичная матрица, в которой единицы с главной диагонали k-го и l-го столбца переставлены соответственно в l-ый и k-ый столбцы.

ного из строк с (k+1)-ой по n-ую, то есть фактически обнуляются соответствующие элементы расширенной матрицы.

xn 1, и так далее. То есть осуществляется обратная подстановка метода Гаусса.

Отметим особенности реализации алгоритма

Перестановку строк выполняется умножением матрицы перестановок на матрицу Ab. Матрица перестановок k-ой и l-ой строк – это единичная матрица, у которой в k-ой и l-ой строках единицы с главной диагонали смещены соответственно в l-ой и k-ой столбцы.

Для умножения матриц в Excel имеется функция МУМНОЖ, а вот для автоматического создания единичной матрицы E в выделенной квадратной области следует подготовить специальный макрос (который будем вызывать по нажатию клавиш Ctrl+Shift+E или щелчку по связанной с макросом фигуре – кружку E, см. табл. 1.1). Операцию перестройки единичной матрицы в матрицу перестановок будем выполнять вручную.

Исключение неизвестных на k-ом шаге выполняется умножением матрицы исключений на расширенную матрицу. Матрица исключений получается из единичной после замены на сходственных элементов в k-ом столбце и их нормировании – делении на элемент Abk,k . Эту замену можно выполнять вручную (специальной вставкой), а вот для нормирования полезно разработать специальный макрос (его будем вызывать по нажатию клавиш Ctrl+Shift+L или щелчку по L- кружку).

Обратный ход метода Гаусса можно выполнять вручную, но можно подготовить для этого (и с целью контроля) специальный макрос, ко-

12

торый будет решать СЛАУ с верхней треугольной матрицей (его будем вызывать по нажатию клавиш Ctrl+Shift+x или щелчку по x-кружку).

Макрос, используемый на этапах a и b алгоритма:

n = TR.Count: i1 = TR.Row: j1 = TR.Column ' опр. числа эл-тов и индексов For i = i1 + 1 To i1 + n - 1

Cells(i, j1) = - Cells(i, j1) / Cells(i1, j1)

Next i Cells(i1, j1) = 1

Next End Sub

Макрос для этапа d алгоритма:

If n + 1 <> TR.Columns.Count Then

13

MsgBox "Выделена не подходящий диапазон", , "TrSolve": Exit Sub

End If

Dim b( ) As Double: ReDim b(n)

For i = 1 To n: b(i) = TR.Cells(i, n + 1): Next 'готовим массив b правых чаcтей

b(i) = b(i) - TR.Cells(i, k) * Cells(in + k - 1, jn + n + 1)

Next i Next k

Next End Sub

Описание решения на Excel задачи A

При решении задачи A (на этапе 1 ) расширенную матрицу Ab1 поме-

В 1-ом столбце Ab1 главный элемент равен 4, он находится во 2-ой строке. В соответствии с этим, (на этапе 2 ) слева в ячейках B2:D4 на-

14

биваем (или переделываем из единичной) матрицу перестановок P1, которая будет задавать перестановки 1-ой и 2-ой строк. Под матрицей Ab1 (в диапазоне G6:J8) разместим (на этапе 3 ) матрицу, которая получится как результат умножения P1 на Ab1.

Напомним, как выполняется в Excel умножение матриц. Для этого: выделяем G6:J8, нажимаем F2, в ячейку G6 вставляем как формулу функцию МУМНОЖ. Вызвав эту функцию, указываем сначала диапазон B2:D4 первой матрицы, а затем – второй G2:J4. Для записи матрицы результата в диапазон, начинающийся в ячейке G6, следует нажать

Ctrl+Shift+Enter.

После получения матрицы P1*Ab1 с переставленными 2-ой и 1-ой строками готовим (на этапе 4 ) слева матрицу L1 для исключения неиз-

вестных из строк со 2-ой по 3-ью (c k+1 по n) первого столбца. Для этого выделяем диапазон B6:D8 (строк c k по n) и запускаем макрос E создания единичной матрицы (по Ctrl+Shift+E или кликнув по кружку E - фигуры, с которой мы связали этот макрос). Затем выделяем в матрице P1*Ab1 1-ый столбец (G6:G8) с главным элементом. Копируем значе-

ния из G6:G8 и, выделив B6:B8, помещаем специальной вставкой (зна-

чения) в столбец единичной матрицы. Не сбрасывая выделение B6:B8, запускаем (по Ctrl+Shift+L или кликнув по L-кружку фигуры) макрос Lk нормирования этого вектора. В результате получим показанную в таблице 1.1 (для этапа 4 ) матрицу L1. После умножения (на этапе 5 ) этой

матрицы на расширенную матрицу P1*Ab1 (диапазон G6:J8) заносим в G10:J12 матрицу произведения Ab2=L1*(P1*Ab1) с исключенным неиз-

вестным x1 из G11:G12.

Аналогичным образом действуем дальше (на этапах 6 , 7 , 8 , 9 ) до получения матрицы Ab3 (G18:J20), у которой все элементы под главной диагональю (левой (3х3)-подматрицы Ab3) равны нулю. Расширенная матрица Ab3 соответствует СЛАУ с верхней треугольной матрицей; она просто решается последовательным вычислением x1, x2 , x3 - обратной подстановкой. Еѐ алгоритм реализован в макросе TrSolve, который запускается по Ctrl+Shift+x (или кликнув по соответствующему x-кружку фигуры) при предварительно выделенной расширенной матрице

(G18:J20) и выводит (на этапе 10 ) вычисленные неизвестные x1, x2 , x3

15

системы в ячейки G21:I21 под соответствующими столбцами верхней треугольной матрицы Ab3.

1.3. Применение в Excel процедуры метода Гаусса

Задача В. Используя обращение к стандартной процедуре решения СЛАУ методом Гаусса, составить в Excel «функцию, определѐнную пользователем» для решения СЛАУ.

Замечание. В Excel есть функция МОБР для обращения матриц, с помощью которой решают СЛАУ, по схеме:

сительной погрешностью 5×10-15 представления чисел в машине. Определитель матрицы A, вычисленный с помощью функции МОПРЕД, ра-

вен -122,236. Вычисленные через обратную матрицу A 1 и столбец b правых частей (D1:D3) неизвестные x1, x2, x3, помещены в ячейки A4:C4 под соответствующие столбцы матрицы A.

Встречаются руководства, в которых для «проверки» полученные неизвестные умножают на матрицу A, ожидая получить вектор b. В нашем случае после умножения этот вектор (он записан в ячейках E1:E3) с инженерной погрешностью (до 4%) совпадает с исходным. Но оказывается, мы «решили» систему, которая не имеет решения – решили «без проблем» и не почувствовали этого. То есть процедура МОПР не выявляет особенных матриц и спокойно вычисляет обратные матрицы и в случаях, когда этих матриц не существует. Такие случаи возможны, когда СЛАУ составлена не верно, и какие-либо еѐ уравнения является линейной комбинацией других уравнений системы.

16

Кроме того методически не правильно применять для решения СЛАУ более трудоѐмкую процедуру обращения матриц (вычисление A 1 из

AA 1 E равносильно решению стольких СЛАУ, сколько имеется неизвестных). Поэтому целесообразно пополнить арсенал функций Excel процедурой эффективного решения СЛАУ с оценкой близости системы к вырожденной – с оценкой числа обусловленности.

Решение задачи В. Рассмотренный алгоритм решения СЛАУ методом Гаусса удобно записать на Фортране – языке, хорошо подходящем для записи алгоритма. Упрощѐнный (без оценки числа обусловленности), но более наглядный, вариант процедуры представлен ниже:

Subroutine KGAUSS(Ab, N, X, IAI)

X(k)=Ab(k,N+1)/Ab(k,k)

Ab(1:N-1,N+1) = Ab(1:N-1,N+1) - Ab(1:N-1,k)*X(k)

End Do

Contains ! ------- Внутренние подпрограммы: ----------------------------------------

Subroutine CMEHA ! Процедура выбора гл. элемента и перестановки строк

End Subroutine End

Замечание. В Фортране примечания записываются справа от знака «!». Цикл Do – End Do Фортрана соответствует циклу For – Next Бейсика. Запись A(i, j1:j2) указывает на элементы с j1-го по j2-ой в i-ой строке, а запись A(i, : ) - на все элементы i-ой строки матрицы A.

17

Дополнив процедуру KGAUSS оценкой числа обусловленности, оттранслировав и включив дополненную процедуру KFGAUSS (Карл Фридрих Гаусс) в библиотеку (названную нами Math32.dll) динамического вызова, получим возможность обращаться к ней из VBA. Однако при этом следует учесть ряд особенностей. Во-первых, необходимо в VBA декларировать подключаемую процедуру, записав:

Declare Sub KFGAUSS Lib "С:\Путь\Math32.dll" (ByRef Ab As Double, _

ByVal N As Long, ByRef X As Double, ByRef CD As Double)

Во-вторых, следует учесть, что в процедуру надо передавать адрес начала массивов, а не весь массив, включая границы индексов, как это делается в Бейсике. То есть обращение из VBA к процедуре KFGAUSS может иметь вид:

Call KFGAUSS(AiB(1, 1), N, X(1), CD)

Отметим ещѐ одну особенность реализации процедуры KFGAUSS, позволяющую вычислять определитель левой квадратной (N×N)-части расширенной матрицы AiB. Этот определитель вычисляется как произведение диагональных элементов матрицы, а знак произведения, который зависит от чѐтного или нечѐтного числа выполненных перестановок, кодируется знаком переменной CD, то есть определитель

det(A)= Aib(1,1)×…×AiB(N,N)×знак(CD).

Число обусловленности матрицы системы положительное и равно абсолютной величине |CD|. Если |CD| порядка десятков (не больше 1000), то система достаточно хорошо обусловлена, и точность еѐ решения высокая. Большая величина |CD|, скажем, 1012, говорит о близости системы к вырожденной, |CD|=1E32 указывает на точную вырожденность системы. Отметим, что для представленной на рис. 1.1 СЛАУ число обусловленности равно 3,534×1015. Оно получено в ячейке D4 с помощью разбираемой ниже функции пользователя для решения СЛАУ и указывает на близость рассмотренной системы к вырожденной.

С учѐтом сделанных замечаний в Excel функция, определённая пользователем, для решения СЛАУ и выдачи числа обусловленности матрицы системы, может иметь следующий вид:

18

Public Function xCdGauss(Ab As Range) As Variant

Dim N As Long, CD As Double

N = Ab.Rows.Count

ReDim AiB(1 To N, 1 To N + 1) As Double

ReDim X(1 To N + 1) As Double

For i = 1 To N

For j = 1 To N + 1

AiB(i, j) = Ab(i, j)

Next j

Next i

циентов, например, показанной на рис. 1.3, выделяется горизонтальный диапазон, например, A4:D4 из N+1 ячеек (N=3 – число уравнений). Затем нажимают клавишу F2 и выбирают в ленте: Формулы, Вставить функцию, Категория - Определѐнные пользователем, xCdGauss, OK. В

возникшем окне Аргументы функции (см. рис. 1.2) указывают для аргумента Ab диапазон A1:D3 ячеек с коэффициентами расширенной матрицы СЛАУ, а затем нажимают Ctrl+Shift+Enter. В ячейках A4:C4 появятся вычисленные значения неизвестных СЛАУ, а в ячейке D4 число обусловленности матрицы системы.

Подготовка на листе матриц с вычисляемыми коэффициентами

Часто в математических расчѐтах встречаются СЛАУ, коэффициенты которых могут быть вычислены по сравнительно простым формулам. Как правило, эти СЛАУ имеют большое число уравнений. При необходимости размещения коэффициентов таких систем в ячейках листа Excel целесообразно подготовить формулу, задающую коэффициенты,

19

и использовать вместе с ней заранее составленную (стандартную) процедуру, заполняющую числами ячейки прямоугольного диапазона.

Идею организации подобного заполнения рассмотрим на следующем простом примере

Пример 1.9. Подготовить в выделяемом квадратном диапазоне единичную матрицу E.

Решение примера начнѐм с присвоения имѐн i, j, f соответственно ячейкам I1, J1, F1 рабочего листа. Переменными i и j будем обозначать соответственно строки и столбцы выделенного диапазона, а в ячейку с именем f запишем формулу =ЕСЛИ(i=j; 1; 0) . В процедуре, заполняющей выделяемый диапазон, будем ссылаться на поименованные ячейки:

Sub PutKoeff()

Dim R As Range

Set R = Application.InputBox(prompt:="Укажите матрицу", Type:=8)

Свяжем запуск данного макроса с фигурой (как это упомянуто вскользь в разделе «Описание решения на Excel задачи A»). Для этого вставим на лист (или, как это было сделано ранее, на рисунок SmartArt) какую-либо фигуру, вызвав нажатием правой клавишей мыши контекстное меню, назначим макрос данному объекту. После этого макрос запускается щелчком по фигуре, мы выделяем квадратный диапазон ячеек, в котором затем формируется единичная матрица.

В следующем примере разбирается более сложный случай.

20