求excel公式 一列数字里有正数和负数 我想要这一列连续出现正数最多的数目和他们的和 负数也是一样

求excel公式 一列数字里有正数和负数 我想要这一列连续出现正数最多的数目和他们的和 负数也是一样
就像这样
A列
-2
5
-5
-10
-5
-5
2
3
-5
-5
5
6
10
11
自己算的话
最多连续出现正数4个 32
最多连续出现负数4个 -25
要求：用数组公式或者是VBA直接算出以上结果 最主要的是能求出他们的和 也就是32和-25

给你做了一段宏命令,代码如下,拷进去试一下,在运行前,做好备份并确保欲计算的表格处于活动状态：
Sub JianCe()
Dim SUMz As Double
Dim SUMf As Double
Dim K As Single
Dim KZmax As Single
Dim KFmax As Single
Dim KZ As Single
Dim KF As Single
Dim SUMzEND As Single
Dim SUMfEND As Single
K = 2
Do While ActiveSheet.Cells(K - 1,1) ""
If Val(ActiveSheet.Cells(K - 1,1)) > 0 Then
SUMz = SUMz + Val(ActiveSheet.Cells(K - 1,1))
KZ = KZ + 1
ElseIf Val(ActiveSheet.Cells(K - 1,1)) < 0 Then
SUMf = SUMf + Val(ActiveSheet.Cells(K - 1,1))
KF = KF + 1
End If
If Val(ActiveSheet.Cells(K - 1,1)) * Val(ActiveSheet.Cells(K,1)) KZmax Then
KZmax = KZ
SUMzEND = SUMz
Else
KZ = 0
SUMz = 0
End If
If KF > KFmax Then
KFmax = KF
SUMfEND = SUMf
Else
KF = 0
SUMf = 0
End If
End If
K = K + 1
Loop
ActiveSheet.Cells(14,2) = "连续正数个数"
ActiveSheet.Cells(14,3) = "连续正数合"
ActiveSheet.Cells(14,4) = "连续负数个数"
ActiveSheet.Cells(14,5) = "连续负数合"
ActiveSheet.Cells(15,2) = KZmax
ActiveSheet.Cells(15,3) = SUMzEND
ActiveSheet.Cells(15,4) = KFmax
ActiveSheet.Cells(15,5) = SUMfEND
End Sub
以上代码比较啰嗦,没想出更好的办法,期待高手有更好的办法.最后结果放在第14、15行中的第2到第五列中,通过调整代码,可以调整位置.如果有要求,也可以对出现频次高的位置进行高亮显示,需要加入相关代码,当然也可以通过增加代码对任意列进行类似运算,该段命令运算截至是以最末一行出现的空单元格为标志的,请确保最后一行为空单元格,或修改相关代码避免此类问题出现.另外,此代码未解决相同频率的数值的选择问题,不知道你准备如何取舍,还是都要求显现出来.
再问： 太感谢了，不过还需要对任意列进行运算，应该怎么弄啊，比如说F列
再答： 如果要对任意列进行上述运算，需要调整代码段，只需要将代码里的相关列号，如代码cells（K，1）中的1调整为列号即可，不过可以在运行代码前，先选择一下欲排序的列，让代码自动获得这个参数。不知道你这样做是干嘛用？我为你调整一下代码，在运行下列代码前，请先点击一下欲排序列中的任意单元格。代码修正如下： Sub JianCe() Dim SUMz As Double Dim SUMf As Double Dim K As Single Dim KZmax As Single Dim KFmax As Single Dim KZ As Single Dim KF As Single Dim SUMzEND As Single Dim SUMfEND As Single Dim N As Single N= ActiveCell.Column K = 2 Do While ActiveSheet.Cells(K - 1, N) "" If Val(ActiveSheet.Cells(K - 1, N)) > 0 Then SUMz = SUMz + Val(ActiveSheet.Cells(K - 1, N)) KZ = KZ + 1 ElseIf Val(ActiveSheet.Cells(K - 1, N)) < 0 Then SUMf = SUMf + Val(ActiveSheet.Cells(K - 1, N)) KF = KF + 1 End If If Val(ActiveSheet.Cells(K - 1, N)) * Val(ActiveSheet.Cells(K, N)) KZmax Then KZmax = KZ SUMzEND = SUMz Else KZ = 0 SUMz = 0 End If If KF > KFmax Then KFmax = KF SUMfEND = SUMf Else KF = 0 SUMf = 0 End If End If K = K + 1 Loop ActiveSheet.Cells(14, 2) = "连续正数个数" ActiveSheet.Cells(14, 3) = "连续正数合" ActiveSheet.Cells(14, 4) = "连续负数个数" ActiveSheet.Cells(14, 5) = "连续负数合" ActiveSheet.Cells(15, 2) = KZmax ActiveSheet.Cells(15, 3) = SUMzEND ActiveSheet.Cells(15, 4) = KFmax ActiveSheet.Cells(15, 5) = SUMfEND End Sub 注意：loop后面的语句是调整输出位置的语句，自己可以调整的。