若有一数组arr=array(0,1,1,1,0,0,2,2,1,3,3,0,0,1,1,1,2,3,0,2),求0,1,2,3连续的最大值及个数代码。
结果为0的连续最大值为2,出现次数2次
1的连续最大值为3,出现次数2次
2的连续最大值为2,出现次数1次
3的连续最大值为2,出现次数1次。
若是借助单元格,用Application.Evaluate("MAX(FREQUENCY(IF(A1:A20=0,ROW(A1:A20)),IF(A1:A20<>0,ROW(A1:A20))))")可以求连续最大值;用Application.Evaluate(“SUM((FREQUENCY(IF(A1:A20=3,ROW(A1:A20)),IF(A1:A20<>3,ROW(A1:A20)))=MAX(FREQUENCY(IF(A1:A20=3,ROW(A1:A20)),IF(A1:A20<>3,ROW(A1:A20)))))*1)”)可以求个数。但数据越多,感觉运行速度越慢,有没有更好的办法,不用借助单元格套用公式?
- 'arr=array(0,1,1,1,0,0,2,2,1,3,3,0,0,1,1,1,2,3,0,2),
- '0的连续最大值为2,出现次数2次
- Sub JustTest()
- Dim Arr, i&, k&, d, str, ar
- Set d = CreateObject("scripting.dictionary")
- Arr = Array(0, 1, 1, 1, 0, 0, 2, 2, 1, 3, 3, 0, 0, 1, 1, 1, 2, 3, 0, 2)
- k = 1
- For i = LBound(Arr) + 1 To UBound(Arr)
- If Arr(i) = Arr(i - 1) Then
- k = k + 1
- Else
- If d.exists(Arr(i - 1)) Then
- ar = d(Arr(i - 1))
- If ar(0) = k Then
- ar(1) = ar(1) + 1
- ElseIf ar(0) < k Then
- ar(0) = k: ar(1) = 1
- End If
- d(Arr(i - 1)) = ar
- Else: d.Add Arr(i - 1), Array(k, 1)
- End If
- k = 1
- End If
- Next
- Arr = d.keys
- For i = LBound(Arr) To UBound(Arr)
- str = str & vbCrLf & i & "的连续最大值为" & d(i)(0) & ",出现次数为" & d(i)(1) & "次"
- Next i
- MsgBox "数组中的元素连续情况为:" & str
- Set d = Nothing
- End Sub
复制代码
运行结果 :
示例文件如下:
新建 Microsoft Excel 97-2003 工作表.rar
(9.53 KB, 下载次数: 62)
|