Добавил:

Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.

Вуз:

Башкирский Государственный Университет

Предмет:

[НЕСОРТИРОВАННОЕ]

Файл:

Vba_расчеты

.pdf

Скачиваний:

Добавлен:

17.05.2015

Размер:

1.73 Mб

Скачать

☆

<<< < Предыдущая 1 23 / 63 4 5 6 > Следующая >>>

Пример 1.10. Подготовить в выделенном квадратном диапазоне матрицу A, заполнить (справа от матрицы) столбец свободных членов b, решить СЛАУ с подготовленной расширенной матрицей n=6.

Решение примера, как и предыдущего,

n		2	1	0	0		2

	2	n	2	1	0		2
A	1	2	n 2 1			b n

0		1	2	n	2	2
	0	0	1	2	n		2
	0	0	1	2	n		2

начинается с присваивания имѐн i, j, f ячейкам I1, J1, F1, а также имени n ячейке N1. Однако формула для формирования матрицы данного примера сложнее. Еѐ приходится записывать с несколькими вложенными функциями «ЕСЛИ»:

=ЕСЛИ(i=j; n; ЕСЛИ(ABS(j-i)=1; 2; ЕСЛИ(ABS(j-i)=2; 1; 0)))

или только после математической проработки с одной функцией ЕСЛИ:

=ЕСЛИ(ABS(j-i)>2;0; n+ABS(j-i)*(7-3*n+ABS(j-i)*(n-3))/2)

При сложной структуре формулы еѐ удобнее программировать на

VBA, то есть составить функцию, определѐнную пользователем. Для этого она снабжается атрибутом Public (общая):

Public Function Func(i, j) Select Case Abs(j - i) Case 0

Func = Range("n")

Case 1 Func = 2

Case 2 Func = 1

Case Else Func = 0

End Select

End Function

А в ячейке f готовится формула =Func(i;j) (набивкой или цепочкой вызовов: формулы, вставить функцию, определенные пользователем,

Func, OK). После запуска макроса PutKoeff и выделения квадратного диапазона ячеек в нѐм будет сформирована требуемая матрица A. За-

полнение столбца свободных членов трудностей не вызывает. После формирования расширенной матрицы Ab решаем СЛАУ с помощью ра-

Рис.1.4.

зобранной выше функции, определѐнной пользователем, xCdGauss. Результаты представлены на рис. 1.4.

1.4. Задачи

1. Автоматизировать вычисления (в					Excel) при
пошаговом LU -разложении симметричных										6	2	1	0
пошаговом LU -разложении симметричных
матриц с	диагональным преобладанием (по							A		2	6	2	1
методу Гаусса без выбора главного элемента).								A		1	2	6	1


U – верхняя треугольная матрица (см. в задаче A),										0	1	2
U – верхняя треугольная матрица (см. в задаче A),										0	1	2	6

L L11 L31 –		нижняя	треугольная		матрица		с
единицами на главной диагонали. Проверить							для A справедливость
выполненного разложения, т.е. LU A ? Замечание.									Матрица Lk1 по-
лучается из Lk сменой знаков элементов вне главной диагонали.
2. Автоматизировать (в			Excel)
пошаговое		решение	СЛАУ
пошаговое		решение	СЛАУ			1 0 0			3 2 1				14
LUx b ,						1 0 0			3 2 1				14
LUx b ,	где U – верхняя тре-
угольная матрица, L – нижняя				L		2 1 0 U			0 2 1 b					33
угольная матрица, L – нижняя
треугольная матрица с едини-						1 2 1			0 0 1					26
треугольная матрица с едини-

цами на главной диагонали. Проверить подстановкой результат решения. Указание. Считая, что y Ux, сначала с помощью макроса решить систему Ly b , а затем окончательно найти x , решив систе-

му Ux y (см. в задаче A).
му Ux y (см. в задаче A).	2		1	0	0		d1
3. Записать макрос для решения методом
3. Записать макрос для решения методом		1	2	1	0		d2
Гаусса СЛАУ с трѐхдиагональной матри-		0				x
Гаусса СЛАУ с трѐхдиагональной матри-		0
		0	0	1	2
		0	0	1	2		dn
22
22

цей размером (n n) с преобладающей главной диагональю. Указание. Перестановку строк не выполнять, действия прямого и обратного хода выполнять над ненулевыми диагональными элементами.

4. Записать макрос для решения методом Га-

усса СЛАУ с трѐхдиагональной матрицей

размером (n n) с преобладающей главной

диагональю. Указание. Перестановку строк

n 1

не выполнять, действия прямого и обратно-

го хода выполнять над одномерными массивами коэффициентов.

5. Записать макрос, который в Excel выполнит по шагам метода Гаусса

вычисление определителя неособенной (n n) -матрицы A. Указание.
Выполняя прямой ход метода Гаусса, при-
				6	2	1	0
вести матрицу к верхнему треугольному ви-

ду. Затем вычислить определитель по фор-			A	2	6	2	1
муле ( 1)	p	a11 ann , где p – число вы-		1	2	6	1


полненных перестановок строк матрицы.				0	1	2
							6
6. Записать макрос, который, используя обра-

щение к библиотечной процедуре решения СЛАУ методом Гаусса, выполнит вычисление определителя (n n) -матрицы A . Указание. См. задачу B.

7. Записать макрос, который, используя обращение к библиотечной процедуре решения СЛАУ методом Гаусса, обеспечит нахождения неизвестных для расширенной вещественной матрицы, заданной в выделенной (n (n 1)) прямоугольной области.

Указание. См. задачу B.

8. Записать макрос, который, используя обращение к библиотечной процедуре решения СЛАУ методом Гаусса, обеспечит нахождения неизвестных для расширенной комплексной матрицы, заданной в выделенной (n 2(n 1)) прямоугольной области. Замечание. Хотя при использовании процедуры Фортрана достаточно поменять тип переменных с Real на Complex, в Бейсике, увы, такого сделать нель-

зя. Указание. Для решения в VBA СЛАУ (A i B) z c i d	с ком-
плексными числами необходимо сформировать систему	вида:
23

B x	c
		, где	x c i d, и решить еѐ как СЛАУ с действи-

A y	d

тельными числами (см. задачу B).

9.Оформить в виде функции, определѐнной пользователем, макрос для получения единичной матрицы. Указание: см. «Макрос для этапа b алгоритма» метода Гаусса. Диапазон ячеек задавать как аргумент функции.

10.Оформить в виде функции, определѐнной пользователем, макрос для нормирования вектор-столбца по первому элементу. Указание: см. «Макрос для этапа b алгоритма» метода Гаусса.

11.Оформить в виде функции, определѐнной пользователем, макрос для решения СЛАУ с верхней треугольной матрицей. Указание: см. «Макрос для этапа d алгоритма» метода Гаусса.

12.Переписать на VBA процедуру KGAUSS из раздела 1.3. Протестировать процедуру на рассмотренных примерах СЛАУ. Сравнить число строк VBA-процедуры и процедуры из раздела 1.3.

13.Подготовить в выделенном квадратном диапазоне матрицу A, заполнить (справа от матрицы) столбец свободных членов b, решить СЛАУ с подготовленной расширенной матрицей:

i)как в задаче A - по шагам, принимая на каждом шаге решение о необходимости перестановки строк, ii) вызвав функции xCdGauss.

а)		6	0	2	1	0		1	б)
		6	0	2	1	0		1

		0	6	0	2	1		2
	A	2 0		6	0	2	b	3
		1	2
		1	2	0	6	0		2
		0	1	2 0		6
		0	1	2 0		6		1

в)		6	0	2	0	1	0		2	г)
		6	0	2	0	1	0		2

		0	6	0	2	0	1		1
		2	0	6	0	2	0		1
	A							b
		0	2	0	6	0	2		1

		1	0	2	0	6	0		1
		0	1	0	2	0
		0	1	0	2	0	6		2

	6	2	0	0	1			1

	2	6	2	0	1			2
A	0	2	6	2	1	b		3

	0	0	2	6	2			2
	1	1	1	2	6
	1	1	1	2	6			1
	6	2	1	0	0	0			1

	2 6		2	1	0	0			2
	0	2	6	2	1	0			2
A							b
	0	0	2	6	2	1			2

	0	0	0	2	6	2			2
	1	1	1	1	2
	1	1	1	1	2	6			1

1.5.Решение переопределѐнной системы n линейных уравнений с m неизвестными (n > m)

Задача С. Дана переопределѐнная система n линей-

		с m (n m) неизвестными
ных уравнений Fc y
(см. рис. 1.4). Решить		еѐ, т.е. найти такие c0 , c1 ,

чтобы несоответствия между правой и левой частя-

ми		уравнений было наименьшим (вектор

	Fc y невязок имел минимальную длину).


Fc y

1 с0 0 с1 0
		1 с1 0
1 с0
1 с		2 с 1
	0	1

Рис. 1.4.


Метод решения (метод наименьших квадратов). Уравнения F c y (на-
зываемые уравнениями наблюдений) можно записать в виде
1 1	с1	0	1		0		0
	с1			c1		c2		F 1 c1 F 2 c2
1 2		0	1	c1	1	c2	0	F 1 c1 F 2 c2	y ,
	с2
1 3		1	1		2		1
			где F j		означает вектор - j-столбец матрицы

(т.е. первый индекс « » пробегает все значения номеров строк). Изобразим на рис. 1.5 координатную систему, по осям которой будем откладывать каждую из трех координат

		векторов-столбцов.
				c1 F 2 c2
		Векторы v Fc F 1		c1 F 2 c2			при различ-
		ных c1, c2 лежат в плоскости ,						определяе-
		мой векторами F 1, F 2
		мой векторами F 1, F 2				. Вектор		Fc y -
	Рис. 1.5.
	Рис. 1.5.	это вектор, соединяющий концы векторов y

и v	(вспомним правила сложения векторов				y v). При каком v
(каких c1,c2) длина вектора
(каких c1,c2) длина вектора			наименьшая?	Очевидно,			что кратчай-

шим расстоянием от конца вектора y до плоскости будет перпенди-

куляр, опущенный из этой точки на плоскость.						Вектор	* , совпадаю-

щий с этим перпендикуляром, будет наименьшим, и ему соответствует

Ac b


вектор v* , который совпадет с проекцией вектора	y на плоскость


(см.	рис. 1.5). Естественно,			что лежащий в плоскости , вектор v*

перпендикулярен вектору				* . Условие перпендикулярности векторов
			приравняв нулю их скалярное произведение (т.к.
v и	запишем,		приравняв нулю их скалярное произведение (т.к.
v	cos(900 ) 0 ):

	vT	(Fc)T (Fc y) (cTFT )(Fc y) cT (FTFc FTy) 0.


Изначально c 0, а должен определяться по точкам данных, поэтому
	F	T	T
FTFc FTy 0	F	Fc F	y ,

а вектор c* , доставляющий минимум вектору невязок, должен определяться решением нормальной системы уравнений

		A FTF , а
Ac b , где			b FTy .

Решение задачи C. Разместим коэффициенты системы с рис. 1.4 в ячейках диапазона B2:D4 (см. рис. 1.7), а коэффициенты нормальной систе-

мы с расширенной матрицей Ab, будем хранить в ячейках диапазона E2:G3. Для заполнения указанного диапазона (а также элементов матрицы Ab) можно использовать следующий программный код:

Sub Ab()

For i = 1 To 2 For j = 1 To 3

S = 0

For k = 1+1 To 1+3 ' - строки матр. F S = S + Cells(k, i) * Cells(k, j)

Next k

Cells(i+1, j + 4) = S ' заполнение матр. Ab

Next j Next i

End Sub


Ac b
3	3		с	1
			1
3	5	с2		2

с1 0,166667 с2 0,5

Рис. 1.6.

Но вместо этого проще воспользоваться стандартными процедурами Excel. Для этого следует: выделить диапазон ячеек E2:G3, нажать F2, набить формулу: =МУМНОЖ(ТРАНСП(B2:C4);B2:D4), а затем на-

жать Ctrl+Shift+Enter.

Решение нормальной системы уравнений (см. рис. 1.6) выполним обращением к функции xCdGauss, определѐнной пользователем. Вычисленные коэффициенты с1, с2 занесены в ячейки E4:F4 (см. рис. 1.7).

Замечание. Систему уравнений в решѐнной задаче С удобно трактовать как условия с1 1 с2 xk yk проведения прямой с1 1 с2 x y через точкиxk , yk , k 1, , 3 . Можно изобразить их на точечной диаграмме, а также рассчитать точки на найденной прямой, добавить их к точечной диаграмме и соединить отрезками (подробности построения точечных диа-

Рис. 1.7.

грамм см. в разделе 3). Получится картинка, показанная на рис. 1.7, где прямая сглаживает точки, полученные в результате эксперимента и содержащие случайные погрешности замеров.

Множители при коэффициентах в	с1 1 с2 xk yk можно рассматри-
вать как базисные функции f1(x) 1,	f2 (x) x приближающей кривой
27

f (x) c1 f1(x) c2 f2 (x) , а элементы в k ой строке матрицы F как значения этих базисных функций при аргументах xk .

Отметим, что при решении ячейкам E4, F4 присвоены соответственно имена с1 и с2 (с русскими буквами «с»). Эти имена использованы в формуле, записанной в ячейке C6, для расчѐта ординат линейной функции f (x) .

1.6. Задачи

1.Заданы массивы точек xk , yk , k 1,2, ,n, (n 2) . Записать условия про-

хождения приближающей их кривой y(x) f1(x) c1 f2 (x) c2 через заданные точки в виде системы n линейных относительно коэффици-

ентов		с
	cT с		уравнений и решить эту систему (вычислить c ) так,
	1	2

чтобы вектор несоответствия (невязок) между правой и левой частями этого уравнения был минимальным. Показать на графике заданные точ-

ки и кривую. Указание. Коэффициенты c приближающей кривой най-

T T

ти, решив систему F Fc F y ; координаты точек данных приведены в таблице 1, там же даны приближающие функции – они заключены в рамки.

2. Заданы массивы	точек xk , yk , k 1,2, ,n, (n 3) . Записать		условия
прохождения	приближающей	их	кривой

y(x) f1(x) c1 f2 (x) c2 f3(x) c3 через заданные точки в виде сис-

		c2	c3
темы n	линейных относительно коэффициентов cT c1

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

Указание. Коэффициенты c приближающей кривой найти, решив сис-

T T

тему F Fc F y ; данные и приближающие функции даны в таблице 1.1.

Варианты заданий

Таблица 1.1

					c x3
f ( x )		c1x c2 x2			c x3			; x [0,9].		f ( x ) c			c2 x c3 / x		;	x [1,9]
						3					1

	вар.1		вар.2	вар.3			вар.4		вар.5		вар.6		вар.7	вар.8			вар.9	вар10

x	yi		yi		yi			yi	yi	x	yi		yi	yi			yi	yi
i										i

1.0	1.0		2.0		0.0			-1.0	-1.0	1.0	2.0	4.0		-1.0			3.0	-1.0

2.0	2.0		3.5		0.0			-1.5	-1.0	2.0	3.5	2.0		1.0			1.0	1.0

3.0	2.0		3.0		1.0			-1.0	0.5	3.0	3.0	2.0		2.0			0.5	2.0

4.0	2.5		3.5		2.5			0.0	0.0	4.0	3.5	1.0		2.0			2.0	1.0

5.0	2.0		3.0		2.0			0.0	1.5	5.0	3.0	1.0		2.5			2.5	1.5

6.0	2.5		2.5		2.0			0.5	2.5	6.0	2.5	0.5		3.0			2.0	1.0

7.0	3.5		2.5		1.0			2.0	2.0	7.0	2.5	1.0		2.5			3.0	0.5

8.0	3.5		1.5		0.0			2.5	1.0	8.0	1.5	0.5		3.0			4.0	0.0

9.0	4.5		1.0		0.5			4.0	0.0	9.0	1.0	0.0		3.0			4.0	0.0

f ( x )				c x2		c x4 ;				f ( x ) c
f ( x )		c		c x2		c x4 ;		x [0,9].		f ( x ) c				c x c x3		;	x [0,9]
		1		2		3						1		2	3

	вар11		вар12		вар13		вар14		вар15			вар16	вар17		вар18		вар19	вар20

x	yi			yi		yi		yi	yi		x	yi		yi	y		y	yi
i											i				i		i

1.0	0.0			3.0	4.0			3.0	0.0		1.0	3.0	1.0		2.5		1.0	4.0

2.0	0.0			3.0	4.0			3.0	0.0		2.0	3.0	2.0		3.0		1.0	3.0

3.0	1.0			2.0	3.5			2.0	0.5		3.0	3.0	2.5		3.0		1.5	2.0

4.0	1.5			2.0	3.5			2.0	1.0		4.0	2.5	3.0		2.5		2.0	1.5

5.0	2.0			1.5	3.0			1.0	1.0		5.0	2.0	2.5		2.0		2.0	2.0

6.0	1.5			1.0	3.0			0.5	1.5		6.0	2.0	2.0		2.0		3.0	2.0

7.0	1.5			1.0	2.0			0.5	2.5		7.0	1.0	1.5		1.5		4.0	2.5

8.0	0.5			2.0	1.0			0.0	3.0		8.0	1.0	1.0		1.5		4.5	3.0

9.0	0.0			2.5	1.0			-0.5	3.0		9.0	0.0	0.0		1.0		5.5	3.5

									f ( x ) c x						; x [1,9]
f ( x ) c x			c x2		c / x		;	x [1,9].	f ( x ) c x			c x3 c / x			; x [1,9]
		1	2			3					1		2	3

	вар21	вар22		вар23		вар24		вар25		вар26	вар27		вар28	вар29		вар30

xi	yi	yi		yi		yi		yi	xi	yi	yi		yi	yi		yi
1.0	4.0	4.0		0.0		0.0		4.0	1.0	4.0	4.0		3.0	1.5		1.0

2.0	3.0	2.5		2.0		1.5		2.5	2.0	2.5	1.5		2.0	2.0		1.0

3.0	2.0	2.0		3.0		1.5		2.0	3.0	2.0	2.0		2.0	2.0		2.0

4.0	1.5	1.5		3.0		2.0		1.5	4.0	2.0	1.0		2.5	3.0		1.5

5.0	2.0	1.0		2.5		2.0		1.5	5.0	2.5	2.0		2.5	3.0		2.0

6.0	2.0	1.0		2.5		2.5		1.0	6.0	2.0	2.0		2.0	3.0		2.5

7.0	2.5	0.5		2.0		3.0		1.0	7.0	2.0	3.0		2.0	1.5		3.5

8.0	3.0	0.0		1.0		4.0		1.5	8.0	1.5	4.0		1.0	1.0		4.0

9.0	3.5	-1.0		0.0		4.5		1.0	9.0	1.0	4.0		0.5	0.0		5.0

<<< < Предыдущая 1 23 / 63 4 5 6 > Следующая >>>

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

#
29.09.2019832.41 Кб5valeckii_oleg_yugoslavskaya_voina.rtf
#
29.08.2019544.77 Кб2val_kursach (1).doc
#
18.03.201662.97 Кб8VARIANT_1 (2).docx
#
18.03.201629.81 Кб19variant_6 (3).docx
#
17.05.2015210.94 Кб44Vasimov_otchet DODELKA.doc
#
17.05.20151.73 Mб33Vba_расчеты.pdf
#
17.05.20152.11 Mб56Vba_формы.pdf
#
18.03.2016407.52 Кб6VDV-0652.DOC
#
17.05.2015821.76 Кб62Vint (1).doc
#
18.03.2016742.91 Кб8VKR_Gabitova_Elvina_E41s.doc
#
18.03.20161.15 Mб27VKR_Gabitova_peredelanny.doc

в)		6	0	2	0	1	0		2	г)
		6	0	2	0	1	0		2

		0	6	0	2	0	1		1
		2	0	6	0	2	0		1
	A							b
		0	2	0	6	0	2		1

		1	0	2	0	6	0		1
		0	1	0	2	0
		0	1	0	2	0	6		2

	6	2	0	0	1			1

	2	6	2	0	1			2
A	0	2	6	2	1	b		3

	0	0	2	6	2			2
	1	1	1	2	6
	1	1	1	2	6			1
	6	2	1	0	0	0			1

	2 6		2	1	0	0			2
	0	2	6	2	1	0			2
A							b
	0	0	2	6	2	1			2

	0	0	0	2	6	2			2
	1	1	1	1	2
	1	1	1	1	2	6			1

в)		6	0	2	0	1	0		2	г)
		6	0	2	0	1	0		2

		0	6	0	2	0	1		1
		2	0	6	0	2	0		1
	A							b
		0	2	0	6	0	2		1

		1	0	2	0	6	0		1
		0	1	0	2	0
		0	1	0	2	0	6		2

	6	2	0	0	1			1

	2	6	2	0	1			2
A	0	2	6	2	1	b		3

	0	0	2	6	2			2
	1	1	1	2	6
	1	1	1	2	6			1
	6	2	1	0	0	0			1

	2 6		2	1	0	0			2
	0	2	6	2	1	0			2
A							b
	0	0	2	6	2	1			2

	0	0	0	2	6	2			2
	1	1	1	1	2
	1	1	1	1	2	6			1

в)		6	0	2	0	1	0		2	г)
		6	0	2	0	1	0		2

		0	6	0	2	0	1		1
		2	0	6	0	2	0		1
	A							b
		0	2	0	6	0	2		1

		1	0	2	0	6	0		1
		0	1	0	2	0
		0	1	0	2	0	6		2

	6	2	0	0	1			1

	2	6	2	0	1			2
A	0	2	6	2	1	b		3

	0	0	2	6	2			2
	1	1	1	2	6
	1	1	1	2	6			1
	6	2	1	0	0	0			1

	2 6		2	1	0	0			2
	0	2	6	2	1	0			2
A							b
	0	0	2	6	2	1			2

	0	0	0	2	6	2			2
	1	1	1	1	2
	1	1	1	1	2	6			1