回答編集履歴

2

修正

2021/06/12 07:36

投稿

jinoji
jinoji

スコア4592

test CHANGED
@@ -1,6 +1,88 @@
1
1
  ```VBA
2
2
 
3
3
  Sub sample()
4
+
5
+
6
+
7
+ Dim score(1 To 5, 1 To 3)
8
+
9
+ Dim avgAry(1 To 5)
10
+
11
+ Dim sdAry(1 To 5)
12
+
13
+ Dim minAry(1 To 5)
14
+
15
+ Dim maxAry(1 To 5)
16
+
17
+
18
+
19
+ Dim i, j
20
+
21
+ For i = 1 To 5
22
+
23
+ For j = 1 To 3
24
+
25
+ score(i, j) = Cells(i + 1, j + 1).Value
26
+
27
+ Next j, i
28
+
29
+
30
+
31
+ For i = 1 To 5
32
+
33
+ Dim sm, mn, mx, sd
34
+
35
+ sm = 0
36
+
37
+ mn = 100
38
+
39
+ mx = 0
40
+
41
+ sd = 0
42
+
43
+
44
+
45
+ For j = 1 To 3
46
+
47
+ sm = sm + score(i, j)
48
+
49
+ If score(i, j) < mn Then mn = score(i, j)
50
+
51
+ If score(i, j) > mx Then mx = score(i, j)
52
+
53
+ Next
54
+
55
+
56
+
57
+ avgAry(i) = sm / 3
58
+
59
+ minAry(i) = mn
60
+
61
+ maxAry(i) = mx
62
+
63
+
64
+
65
+ For j = 1 To 3
66
+
67
+ sd = sd + (score(i, j) - avgAry(i)) ^ 2
68
+
69
+ Next
70
+
71
+ sdAry(i) = Sqr(sd / 3)
72
+
73
+ Next
74
+
75
+ End Sub
76
+
77
+
78
+
79
+ ```
80
+
81
+
82
+
83
+ ```VBA
84
+
85
+ Sub sample2()
4
86
 
5
87
  Dim arr As Variant
6
88
 
@@ -26,6 +108,8 @@
26
108
 
27
109
  avgAry(i) = .Average(.Index(arr, i, 0))
28
110
 
111
+ sdAry(i) = .StDev_P(.Index(arr, i, 0))
112
+
29
113
  minAry(i) = .Min(.Index(arr, i, 0))
30
114
 
31
115
  maxAry(i) = .Max(.Index(arr, i, 0))

1

追記

2021/06/12 07:36

投稿

jinoji
jinoji

スコア4592

test CHANGED
@@ -6,15 +6,33 @@
6
6
 
7
7
  arr = Range("B2:D6").Value
8
8
 
9
- Dim i, j
9
+
10
10
 
11
- For i = 1 To 5
11
+ Dim avgAry(1 To 5)
12
12
 
13
- For j = 1 To 3
13
+ Dim sdAry(1 To 5)
14
14
 
15
- Debug.Print i, j, arr(i, j)
15
+ Dim minAry(1 To 5)
16
16
 
17
+ Dim maxAry(1 To 5)
18
+
19
+
20
+
21
+ With WorksheetFunction
22
+
23
+ Dim i
24
+
25
+ For i = 1 To 5
26
+
27
+ avgAry(i) = .Average(.Index(arr, i, 0))
28
+
29
+ minAry(i) = .Min(.Index(arr, i, 0))
30
+
31
+ maxAry(i) = .Max(.Index(arr, i, 0))
32
+
17
- Next j, i
33
+ Next
34
+
35
+ End With
18
36
 
19
37
  End Sub
20
38