Vba_расчеты

x(t), y(t)

y(x(t))

1.Записать макрос, который по заданным соответственно в ячейках A2, B2 и D2 значениям a, b и, набитой по правилам Excel, функции

f (x) (C2 - ячейка с аргументом x ) строит график этой функции на отрезке a x b . Указание. График строить по N+1=101 точке (N задать в макросе константой).

2.Записать макрос, который по заданным в ячейках A2, B2, C2 значениям соответственно ax, bx, Nx для аргумента x (его значения сохраняются в ячейке D2) и заданным в ячейках A4, B4 , C4 значениям соответственно ap, bp, Np для параметра p (его значения сохраняются в ячейке D4) строит Np графиков y(x) (формула задаѐтся в ячейке

E5) в декартовых координатах. Причѐм для каждого графика аргумент x рассчитывается в Nx+1 точке отрезка ax x bx.

3.Записать макрос, который по заданным соответственно в ячейках A2, B2 и D2, E2 значениям a, b и, набитым по правилам Excel, функциям x(t), y(t) (C2 - ячейка с аргументом t ) строит график пара-

метрической кривой на отрезке a t b . Указание. График строить на точной диаграмме в виде гладкой кривой по 101 точке.

4.Записать макрос, который по заданным в ячейках A2, B2 , C2 значениям соответственно at, bt, Nt для аргумента t (его значения сохраняются в ячейке D2) и заданным в ячейках A4, B4, C4 значениям соответственно ap, bp, Np для параметра p (его значения сохраняются в ячейке D4) строит Np параметрических кривых y(x(t)) (формулы

задаются в ячейке E5, F5). Причѐм для каждой кривой аргумент t рассчитывается в Nt+1 точке отрезка at t bt . Указание.

Кривые строить на точной диаграмме в виде гладких кривых.

5.Записать макрос, который по заданным соответственно в ячейках A2, B2 и D2 значениям a, b и, набитой по правилам Excel, функции

( ) (C2 - ячейка с аргументом ) строит график кривой				( ) в по-
лярных	координатах	на	отрезке	a b .
Указание.	График строить на точной диаграмме в виде гладкой кри-

вой по N+1=101 точке (N задать в макросе константой).

6.Записать макрос, который по заданным в ячейках A2, B2 , C2 значениям соответственно af, bf, Nf для аргумента f (его значения сохра-

41

няются в ячейке D2) и заданным в ячейках A4, B4 , C4 значениям соответственно ap, bp, Np для параметра p (его значения сохраняются в ячейке D4) строит Np кривых ( f ) (формула задаѐтся в ячейке E5) в полярных координатах. Причѐм для каждой кривой аргумент f рассчитывается в Nf+1 точке отрезка af f bf . Указание. График строить на точной диаграмме в виде гладкой кривой.

3.4. Использование сплайн-функций и сплайн-кривых

При построении на точечных диаграммах плавных кривых по неболь-

шому числу точек стандартные
средства Excel могут не дать
подходящего результата. Кроме
того, стандартные гладкие кривые
Excel, приближающие (интерпо-
лирующие) исходные точки дан-
ных, не дают возможности рас-
считать координаты	промежу-
точных точек и вычислить по
ним (иногда крайне необходи-
мые) геометрические характери-		Рис. 3.3.
стики. В таких случаях	полезно

подключить и использовать через VBA нестандартные средства, например, функции, выполняющие приближение (интерполяцию) кубическими сплайнами. На рис. 3.3 показаны две кривые, приближающие (интерполирующие) окружность, по трѐм равномерно расположенным на ней точкам, также показанным на рис. 3.3. Стандартными средствами не удаѐтся приблизить окружность - получается «треугольник», а сплайн-кривая при подходящем выборе еѐ параметров даѐт хорошую «окружность».

Кубическая сплайн-функция S(x) (или кубический сплайн S(x) ) на сетке узлов x1 xi 1 xi xn - непрерывная кусочная функция, задаваемая на интервалах [xi 1, xi ] между узлами кубическими полиномами si (x) a0i a1i x a2i x2 a3i x3 , которые гладко (с совпадением первой и вторых производных) стыкуются друг с другом в узловых точках. Такие сплайны имеет разрывы третьей производной (сплайны дефекта 1 =3-2).

42

Сплайн-функции получили своѐ название от гибких деревянных или металлических реек, которые использовались чертѐжниками при проведении плавных линий через заданные точки при разработке (на плазе) чертежей обводов судов или автомобильных кузовов. Существуют различны формы записи сплайн-функций. Впервые запись сплайнов в виде

EJ y(x) С		С x M		x2	V	x3	P |	(x x )3	V |	(x x	)3
								1		2
	2	1	a 2		a	6	x x2	6	b x x3	6

как уравнение упругой линии нагруженной балки (рис. 3.4.) предложена профессором И.Г. Бубновым в 1914 г. в книге «Строительная механика».

Прерыватели

И.Г.Бубнова

|

x x ука-

i

зывают, что следующее за ним выра-

жение добавляется лишь при

x xi ,

а

Рис. 3.4.

иначе оно равно нулю.

При

EJ 1

и

большом числе n точек

xi приложения сил структура аналитической

зависимости y(x) упругой линии такова:

y(x) a a x a x2 a x3

n

a

| (x x )3

3

a xi n

a

|

(x x )3

0 1

2

3

i 1

i 2 x xi

i

i 0

i

i 2

i 2

x xi

i

Это и есть уравнение сплайна с прерывателями И.Г. Бубнова. В «современной» записи сплайна И.Г. Бубнова

s(x) 3	0	a x3	n	1	a	(x x )3
k		k	k		k 3		k
просто обозначают прерыватель			| (x xi )			как	(x x )		с подстроч-
		x x1					k

ным знаком "+", который имеет точно такой же смысл, что и прерыватель: выражение (x xi ) действует, когда оно положительно, и не действует (равно нулю) при x xi . Это выражается и по другому

| (x xi ) (x xi ) max(0, x xi ).

x xi

Кубический сплайн однозначно определяется заданием ординат yi в узловых точках xi и условиями на концах: заданием первых или вторых

43

xi 1, xi

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

При вычислениях значений S(x) вместе с ординатами yi используются, например, первые производные pi в узловых точках, которые предварительно вычисляются решением СЛАУ с трѐхдиагональной матрицей. Вычисление недостающих производных pi в узловых точках и расчѐт ординат v S(u) сплайна в точках u выполняется обращением к функции HS, которая вызывается из библиотеки Math32.dll и должна декларироваться в VBA (см. в модуле Math.bas) оператором:

Declare Function HS Lib "C:\Путь\Math32.dll" _

(ByRef u As Double, ByVal n As Long, ByRef X As Double, ByRef Y As Double, _ ByRef p As Double, ByRef Kod As Long, ByRef KodErr As Long) As Double

Обращаются из VBA к функции HS следующим образом:

v = HS(u, n, x(1), y(1), p(1), Kod, KodErr),

где HS, u, x, y, p типа Double; n, Kod, KodErr типа Long (4 байта).

Функция при Kod 0 вычисляет массив p неизвестных производных pi в узлах, а затем устанавливает Kod в 1 и переходит к следующему пункту. При Kod 0 узлы сетки сплайна должны строго возрастать.

При Kod>0 функция вычисляет значение v S(u) сплайна в точке u . Если при расчете узлы совпадают, то принимают vi (yi 1 yi ) / 2 .

Значения входных аргументов:

u - абсцисса точки для вычисления ординаты, n - число узлов сплайна,

x(1), ..., x(n) - абсциссы узлов сплайна, y(1), ..., y(n) - ординаты узлов сплайна,

p(1), p(n) - производные в граничных узлах сплайна, Kod - задает способ использования подпрограммы:

Kod={-22, -21, -12, -20, -02, -11, -10, -01, 00, -01, -02,..} 44

Если Kod 0, то первая слева цифра указывает, что на левой границе ( x1 ) заданы: 2 - "без узла", 1 - в p(1) задана 1-ая производная, 0 - нулевая 2-ая производная. Вторая слева цифра указывает, что на правой границе ( xn ) заданы: 2 - "без узла", 1 - в p(N) задана 1-ая производная, 0 - нулевая 2-ая производная.

Если Kod>0, то массив p(1),...,p(N) считается заданным, он не изменяется, а используется для вычисления значения сплайна. Kod при этом указывает номер обращения к данной функции.

Выходные аргументы:

HS - ордината сплайна в точке u,

Если на входе задано Kod 0, то предварительно вычисляются элементы массива производных p(2),...,p(N-1) и устанавливается Kod=1. Если же на входе Kod>0, то при выходе Kod:=Kod+1.

KodErr – код завершения: KodErr = 0 - штатное завершение, KodErr = №>0 - аварийное завершение, № - код ошибки. KodErr = 1 - Ошибочное задание сетки узлов (N<2)!

KodErr = 20 - Узлы сетки не упорядочены по возрастанию! KodErr = 21 - Узлы сетки не упорядочены строго по возрастанию! KodErr = 3 - Ошибочное задание параметров сплайна!

KodErr = {4 | 5 | 6 | 7} - Ошибочное задание граничных условий! KodErr = {8 | 9} - Ошибочное задание параметра Kod !

Кубическая сплайн-кривая (на плоскости) представляет собой параметрический сплайн V (t) X (t),Y(t) , составленный из двух кубических сплайнов X (t) Sx(t),Y(t) Sy(t) , зависящих от параметра t и заданного

на сетке t0 t1 tn , в качестве узла	ti которой принята длина лома-
ной (с изломами в заданных точках)	от начальной точки x0 , y0 до

точки xi , yi . В каждом узле xi , yi сплайн-кривой определяются две первые производные pxi , pyi соответственно от X и Y по параметру t , вместе они задают вектор касательной к сплайн-кривой. Процедура VSpline, вычисляющая недостающие производные pxi , pyi , а также рассчитывающая Xv, Yv - координаты сплайн-кривой, написана на VBA и имеет вид:

45

Sub VSpline(t As Double, ByVal n As Long, X() As Double, Y() _

As Double, tPxy() As Double, Kod As Long, Vx As Double, Vy As Double)

'Процедура, использующая функцию HS для расчѐта

'параметрического кубического сплайна двух переменных

'x(1 To n), y(1 To n), Kod - описаны в HS, кроме того,

'если Kod <=1, то вычисляются узлы параметрической сетки.

'Массив узлов сетки и производных tPxy(1 to n, 0 To 2):

'tPxy(1 To n, 0) - длина ломанной до i-ой точки, т.е. узлы ti

' tPxy(1 To n, 1) – производные	dX/dt	по t	в узлах ti
' tPxy(1 To n, 2) – производные	dY/dt	по t	в узлах ti

' Vx,Vy – вычисленные координаты сплайн-кривой для значения t

Dim KodErr As Long, KD As Long If Kod <= 1 Then

tPxy(1, 0) = 0#
For i = 2 To N	' Расчѐт узлов параметрической сетки

tPxy(i, 0) = Sqr((X(i) - X(i - 1)) ^ 2+(Y(i) - Y(i - 1)) ^ 2)+tPxy(i - 1, 0)

Next i

End If

KD = Kod

Vx = HS(t, N, tPxy(1, 0), X(1), tPxy(1, 1), KD, KodErr)

If KodErr <> 0 Then Stop

Vy = HS(t, N, tPxy(1, 0), Y(1), tPxy(1, 2), Kod, KodErr)

	If KodErr <> 0 Then Stop
	End Sub
	Если задать Kod=1 (или
	Kod<1) и вызвать проце-
	дуру VSpline, то по X,Y-
	координатам		исходных
	точек для рис. 3.3 и 3.5
	происходит	вычисление		Рис. 3.5.
	параметров	t	узлов

сплайн-кривой (см. рис. 3.5). Если для точек с рис. 3.3 задана длина

46

r p 43 1,333 вектора касательной в ячейке F2, а по ней найдены составляющие px, py этого вектора по формулам px r sin(30o ) r 0,5 , py r sin(60o ) r 3 / 2 и записаны в ячейки диапазона D2:E5, то по данным диапазона A2:E5 можно рассчитать (см. пример 3.3) точки показанной на рис. 3.3 «окружности». Вычисление параметров t узлов сплайн-кривой (см. диапазон C2:C5 на рис. 3.5) происходит при первом вызове процедуры VSpline по X,Y-координатам исходных точек (см. рис. 3.3), когда перед этим обращением задано Kod=1 (или Kod<1).

Пример 3.3. Рассчитать по данным диапазона A2:E5 точки показанной на рис. 3.3 «окружности»; по точкам построить «окружность».

' Процедура расчѐта координат точек «окружности»:

Const n As Long = 4			' Число исходных точек.
Const M As Long = 30		' Мах индекс в массиве рассчитанных точек.
Sub Main()		' Построение «окружности»:
Const pi As Double = 3.14159265
Dim t As Double, Kod As Long, X(1 To n) As Double, Y(1 To n) As Double, _
			tPxy(1 To n, 0 To 2) As Double
Dim Xs(M) As Double, Ys(M) As Double
For i = 1 To n			' Ввод данных из ячеек:
X(i) = Cells(i + 1, 1): Y(i) = Cells(i + 1, 2)				' диапазона A2:B5,
tPxy(i, 1) = Cells(i + 1, 4): tPxy(i, 2) = Cells(i + 1, 5)				' диапазона D2:E5.
Next i
Kod = 1	' Kod > 0 - выполнять расчѐт по заданным коэфф-там сплайна!
For j = 0 To M		' Цикл по рассчитываемым точкам сплайн-кривой
t = dt * j
Call VSpline(t, N, X(), Y(), tPxy(), Kod, Xs(j), Ys(j))				' опр. координаты точки
If j = 0 Then			' при j=0 определены все параметры сплайна
dt = tPxy(N, 0) / M
For i = 1 To N
Cells(i + 1, 3) = tPxy(i, 0)			' запись параметра кривой в ячейки С2:С5
Next i

End If

47

Cells(j + 2, 7) = Xs(j): Cells(j + 2, 8) = Ys(j)	' запись координат точек
Next j	' окружности в ячейки G2:H32
End Sub

После выполнения этой процедуры выделяем диапазон G2:H32 c рассчитанными Xs,Ys-координатами параметрического сплайна, выбираем на ленте Вставка, Диаграммы, Точечная с гладкими кривыми и получа-

ем изображение «окружности». Можно на полученную диаграмму выбрать точки исходных данных и, назначив для них точечную диаграмму с маркерами, получить картинку, показанную на рис. 3.3. Для изображѐнных на рис. 3.3 трѐх дуг, опирающихся на центральный угол в 120°, длина p вектора касательной (отнесѐнная к дли-

не хорды) принята

p

4/ 3 1,333. Для углов

90° и 60° рекомендуется

соответственно принимать

p

4 2 2 1,172 и

p

8 4 3 1,072.

Пример 3.4.

Размеры B, R, d контура сечения уголка, изображѐнного на рис. 3.6, заданы в ячейках диапазона A2:С2 (см. рис. 3.7). Пред-

ставить на диаграмме контур заданных размеров и записать в ячейки J2:N2 величины: площадь S сечения, координаты Xcт, Ycт его центра «масс» («тяжести») и главные моменты инерции Jcx, Jcy.

Рис. 3.6.

Решение. Способ 1. Подготовим данные для построения контура с помощью сплайн-кривой. Начнѐм с концов дуги скругле-

ния, и вместе с координатами точек (в ячейках A4:B5) укажем составляющие векторов касательных на концах дуги четверти окружности (в ячейках D4:E5). Затем укажем координаты остальных точек, последовательно обход контур против часовой стрелки. Заметим, что на концах отрезков прямых также должны указываться векторы касательных в данном случае можно указать векторы нулевой длины или, как сделано здесь, в остальных ячейках диапазона D6:E11 ничего не указывать (иметь значение Empty ≡ 0).

48

Таким образом, мы подготовили все необходимые параметры сплайнкривой, и можно рассчитать все точки контура, задав в программе перед расчѐтом Kod=1.

Число рассчитываемых точек контура M
							A			B			C			D			E
для точности его рисования увеличим до 80
				1			B			R			d
– поскольку весь контур целиком представ-
ляется единой сплайн-кривой. Цикл расчѐта				2		30			5			4
точек сплайн-кривой ничем не отличается
				3			X			Y			t			pX			pY
от приведѐнного в предыдущем примере. И
				4		9			4			0			-1,172			0
также строится по	рассчитанным точкам
				5		4			9						0			1,172
изображение на точечной диаграмме.
				6		4			30
Способ 2. В данном примере трудность вы-
				7		0			30
зывает лишь расчѐт точек внутри дуги
скругления. Поэтому можно сначала по па-				8		0			0
раметрам ячеек A4:E5 рассчитать (напри-				9		30			0
мер, как точки сплайн-кривой) точки дуги
				10		30			4
скругления, а затем присоединить к ним (в
				11		9			4
порядке обхода) координаты остальных
опорных точек контура. Следуя этому в										Рис. 3.7.
программе сначала, назначив N=2 и приняв, например, M=10:
Const N As Long = 2	'	Число исходных точек.
Const M As Long = 10	' Мах индекс в массиве рассчитанных точек.

надо рассчитать точно так же как в предыдущем примере координаты Xs(i), Ys(i) точек сплайн-кривой для дуги скругления и поместить их в ячейки G2:H12. Но для присоединения к контуру ещѐ шести остальных опорных точек в массивах следует предусмотреть дополнительно 6 элементов, то есть так объявить массивы:

Dim Xs(M + 6) As Double, Ys(M + 6) As Double

После выполнения расчѐта точек дуги скругления выполняют следующий программный код:

i = 6: K = M + 6 For j = M + 1 To K

Xs(j) = Cells(i, 1): Cells(j + 2, 7) = Xs(j)

49

Ys(j) = Cells(i, 2): Cells(j + 2, 8) = Ys(j) i = i + 1

Next j

Он обеспечивает пересылку координат шести точек из ячеек диапазона A6:B11 в конец массивов Xs, Ys и в ячейки диапазона G13:H18. Таким образом формируется диапазон G2:H18 с координатами всех точек, но построение диаграммы по для данного случая следует выполнять толь-

ко прямыми отрезками.

Вычисление площади полученного замкнутого многоугольника (кон-

			x						, где K – но-
тура) выполняется по формуле	S 0.5	K		y	x	y
		i 1	i 1	i	i	i 1

мер конечной точки, совпадающей с начальной (i=0 – нулевой) точкой. Для разобранного выше случая 1 принимаем K=M+1=81, а для случая 2 следует принять K=6+M+1=6+10+1=17. Площадь вычисляется по следующему программному коду:

S = 0	' Вычисление площади S замкнутого многоугольника
For i = 1 To K
S = S + Xs(i - 1) * Ys(i) - Xs(i) * Ys(i - 1)
Next i
S = 0.5 * Abs(S):	Cells(2, 9) = S	' Запись значение площади а ячейку J2

Координаты центра «масс» можно найти по формулам Xcm MyS , Ycm MxS через статические моменты My , Mx, которые вычисляются по формулам:

Mx iK1 xi 1 xi yi2 1 yi 1 yi yi2 / 6 ,

My iK1 yi 1 yi xi2 1 xi 1 xi xi2 / 6 .

Главные моменты инерции (моменты инерции относительно осей, проходящих через центр масс) можно рассчитать по формулам

Jcx Jx S (Xcm)2 , Jcy Jy S (Ycm)2 через моменты инерции Jx , Jy (относительно координатных осей), которые вычисляются по форму-

лам:	K	2	2	yi yi 1 xi xi 1	/12,
	Jx i 1	yi	yi 1
	K		2			/12.
		2
	Jy i 1	xi	xi 1 xi xi 1 yi yi 1
				50