Вопрос №26. Функции рабочего листа, определенные пользователем. VBA – функция для расчета средних: арифметического, геометрического, гармонического. Привести примеры.
1) Найти среднее геометрическое и гармоническое в Excel и составить программу. Массив х считывать с раб.листа Excel.
A B C D
   
2 3 4 5

Среднее геометрическое = (x1*x2*x3*x4*xn)^1/2
Среднее гармоническое = (1/x1+1/x2+1/x3+1/xn)^1/2
2) Найти среднее геометрическое, арифметическое и гармоническое
1) Option Base 1
Const n=4
Dim x(n) As Integer
Function Geom (n as Integer, x()  as Integer) As Single
P=1
For I=1 to n
P=p*x(I)
Next I
SrGeom=p^(1/n)
End Function
Function Garm (n as Integer, x () as Integer) As Single
S=0
For I=1 to n
S=S+1/x(I)
Next I
SrGarm=n/s
End function
Sub MyPr_10()
‘считывание массива
R=Selection. Row
C=Selection. Colomn
K=Selection. Columns. Count
For I=1 to n
X(I)=Cells(R,C+I-1)
MsgBox(«массив=»)&x(I)
Next I
‘вызов 1 функции
SrGeom=Geom(n,x)
‘вызов 2функции
SrGarm=Garm(n,x)
Cells(15,1)=”ср.геом=”&Format(SrGeom, “Standard”)
Cells(16,1)=”ср.гарм=”&Format(SrGarm, “Fixed”)
End Sub 2) Option Base 1
Const n=6
Dim x(n) As Integer
Sub Poisk(n as Integer, x() as Integer, SrAr as Single, SrGeom as Single, SrGarm as Single) As Single
S1=0:S2=0
For I=1 to n
S1=S1+x(I)
S2=S2+1/x(I)
S3=S3+x(I)
Next I
SrAr=S1/n
SrGeom=S3^(1/n)
SrGarm=n/S2
End Sub
Sub MyPr_3()
Dim SrAr as Single, SrGeom as Single, SrGarm As Single
R=Selection. Row
C=Selection. Colomn
K=Selection. Columns. Count
For I=1 to k
X(I)=Cells(R,C+I-1)
MsgBox(«элемент=»)&x(I)
Poisk n, x, SrAr, SrGeom, SrGarm
Cells(10,1)=”ср.ар=”&Format(SrAr, “Fixed”)
Cells(11,1)=”ср.геом=”&Format(SrGeom, “Standard”)
Cells(12,1)=”ср.гарм=”&Format(SrGarm, “0.00”)
End Sub