1、公式可以简化:=CHOOSE(SIGN((N4-N3)*((O4*P4)/(N4*P4+N4*O4+O4*P4)-(O3*P3)/(N3*P3+N3*O3+O3*P3)))+2,"正常","无","反常")
2、代码如下:- Sub tt()
- arr = Range("n3:p20")
- ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
- For i = 2 To UBound(arr)
- s = arr(i, 1) * arr(i, 2) + arr(i, 1) * arr(i, 3) + arr(i, 2) * arr(i, 3)
- t = arr(i, 1) * arr(i, 2) * arr(i, 3)
- s1 = arr(i - 1, 1) * arr(i - 1, 2) + arr(i - 1, 1) * arr(i - 1, 3) + arr(i - 1, 2) * arr(i - 1, 3)
- t1 = arr(i - 1, 1) * arr(i - 1, 2) * arr(i - 1, 3)
- For j = 1 To 3
- x = (arr(i, j) - arr(i - 1, j)) * (t / s / arr(i, j) - t1 / s1 / arr(i - 1, j))
- brr(i, j) = Choose(Sgn(x) + 2, "正常", "无", "反常")
- Next
- Next
- [x3].Resize(UBound(brr), UBound(brr, 2)) = brr
- End Sub
复制代码 |