excel中统计某一个数字连续出现的次数

2025-01-05 09:20:25
推荐回答(5个)
回答(1):

得做个VBA程序才行. 公式做不到的. 闲着没事做着玩, 测试没问题.
Sub Macro1()
Dim a(100) As Integer '最多100个1
For i = 1 To 100
a(i) = 0
Next
onec = 0
For i = 1 To 65535
s = Sheet1.Cells(i, 1)
If s = Empty Then Exit For
If s = "1" Then
onec = 1
j = i + 100
For k = i + 1 To j
If Sheet1.Cells(k, 1) = "1" Then onec = onec + 1 Else Exit For
Next
i = k
a(onec) = a(onec) + 1
End If
Next i
j = 1
For i = 1 To 100
If a(i) > 0 Then
Sheet1.Cells(j, 3) = i & "个1的次数:"
Sheet1.Cells(j, 4) = a(i)
j = j + 1
End If
Next

End Sub

回答(2):

假设要标记的单元格为C1到C6556,则C1=IF(A1="1","1",""),然后将鼠标放在C1单元格的右下角出现实心十字光标向下拉,则所有的C列都有公式,最后再用求和就行。

回答(3):

=IF(A3=A2,"",COUNTA($A$1:A1)-SUM($E$1:E1))

在B2输入公式下拉

回答(4):

=SUMPRODUCT((A1:A10=1)*(A2:A11=1))

回答(5):

筛选后在全部选择,右下角有详细信息