1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
| Private Sub Button1_Click()
Dim mean As Double
Dim stdev As Double
Dim N As Double
Dim S As Double
Dim K As Double
Dim Normalized As Double
Dim pValue As Double
Dim JB As Double
N = ActiveSheet.UsedRange.Rows.Count - 1
Cells(7, 5) = WorksheetFunction.Average(Range(Cells(2, 2), Cells(N, 2)))
mean = Cells(7, 5)
Cells(8, 5) = WorksheetFunction.stdev(Range(Cells(2, 2), Cells(N, 2)))
stdev = Cells(7, 5)
For i = 2 To N
Cells(i, 1) = (Cells(i, 2) - mean) / stdev
Next
Cells(9, 5) = WorksheetFunction.Average(Range(Cells(2, 1), Cells(N, 1)))
mean = Cells(9, 5)
Cells(10, 5) = WorksheetFunction.stdev(Range(Cells(2, 1), Cells(N, 1)))
stdev = Cells(10, 5)
For i = 1 To N
i = i + 1
Cells(11, 5) = ((Cells(i, 1) - mean) ^ 3) / (stdev) ^ 3
S = Cells(11, 5)
Cells(12, 5) = ((Cells(i, 1) - mean) ^ 4) / (stdev) ^ 4
K = Cells(12, 5)
Next
Cells(3, 5) = (N / 6) * ((S ^ 2) + (K ^ 2) / 4) ' Equation de jarqueberra '
JB = Cells(3, 5) ' on colle la valeur de la statistique JB dans la case corresponsante '
Cells(4, 5) = WorksheetFunction.ChiDist(JB, 2) ' equation pvalue '
pValue = Cells(5, 5) ' on colle la de la pvalue dans la case corresponsante '
If (pValue < 0.05) Then ' Les rendements suient-ils une loi normale ? '
Cells(2, 5).Value = " No "
Else: Cells(2, 5).Value = " Yes "
End If
End Sub
Function JBTest(ReturnVector As Range, SignificanceLevel As Double) As Variant
JBTest = (N / 6) * ((S ^ 2) + (K ^ 2) / 4)
End Function
Function JBCriticalValue(SignificanceLevel As Double) As Double
JBCriticalValue = WorksheetFunction.ChiDist(JB, 2)
Cells(4, 5) = JBTest
End Function |
