計算列中的連續數字

我希望計算一列中連續數字的出現次數，但似乎無法找到一種邏輯方法來在回圈中計算它。

我的值列只是 0 或 1 的條目。我想要的是每次有兩個 0 連續、三個 0 連續、四個 0 連續等等時計數。我期望連續數字的最大次數是 15。

理想情況下，我希望將每次出現的輸出輸入到表格中。我在下面提供了相關專欄的快照。

到目前為止，我的嘗試包括回圈檢查列中的兩個 0，從第 2 行開始，但是當我連續有兩個以上的 0 時，這會導致問題。

'Check for 2
Dim TwoCount, RowNo As Integer, LastRow As Long
LastRow = Sheets("Data").Range("A165536").End(xlUp).Row
TwoCount = 0
RowNo = 2
For i = 2 To LastRow
If Sheets("Data").Range("H" & RowNo).Value = 1 Then
RowNo = RowNo   1
Else
If Sheets("Data").Range("H" & RowNo).Value = 0 Then
TwoCount = 1
RowNo = RowNo   1
    If Sheets("Data").Range("H" & RowNo).Value = 0 Then
    TwoCount = 2
    RowNo = RowNo   1
If Sheets("Data").Range("H" & RowNo).Value = 1 Then
End If
End If
End If
End If
Next i

我歡迎任何關于我應該如何處理這個問題的建議？無論是作為公式還是陣列公式更容易。

期望的輸出

uj5u.com熱心網友回復：

計算一列中連續出現 0 的頻率

你也可以試試這個陣列公式，

? L2單元格中使用的公式

=SUMPRODUCT(--(FREQUENCY(
IF($H$2:$H$32=0,ROW($H$2:$H$32)),
IF($H$2:$H$32=1,ROW($H$2:$H$32)))=K2))

并填滿！

注意：陣列公式需要按CTRL SHIFT 輸入ENTER（不僅僅是ENTER）。按住CTRL鍵和SHIFT鍵，然后按ENTER。如果您使用的是Excel 2021或O365，則只能按ENTER。

uj5u.com熱心網友回復：

計算連續出現次數

Option Explicit

Sub CountConsecutive()
    
    ' Source
    Const sName As String = "Data"
    Const sFirstCellAddress As String = "H1"
    Const sCriteria As Variant = 0
    ' Destination
    Const dName As String = "Data"
    Const dFirstCellAddress As String = "J1"
    Dim dHeaders As Variant
    dHeaders = VBA.Array("Occurrences", "Number of Times")
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Write the values from the source column to an array.
    
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    
    Dim Data As Variant
    Dim rCount As Long
    
    With sws.Range(sFirstCellAddress)
        Dim slCell As Range: Set slCell = .Resize(sws.Rows.Count - .Row   1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If slCell Is Nothing Then Exit Sub
        rCount = slCell.Row - .Row   1
        If rCount < 2 Then Exit Sub
        Data = .Resize(rCount).Value
    End With
        
    ' Count the occurrences by using a dictionary.
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim Key As Long
    Dim r As Long
    Dim cCount As Long
    Dim MaxCount As Long
    
    For r = 2 To rCount
        Key = Data(r, 1)
        If IsNumeric(Key) Then
            If Key = sCriteria Then
                cCount = cCount   1
            Else
                If cCount > 0 Then
                    dict(cCount) = dict(cCount)   1
                    If cCount > MaxCount Then MaxCount = cCount
                    cCount = 0
                End If
            End If
        End If
    Next r
    If MaxCount = 0 Then Exit Sub
    
    ' Write the values from the dictionary to the array.
    
    rCount = MaxCount   1
    ReDim Data(1 To rCount, 1 To 2)
    
    Data(1, 1) = dHeaders(0)
    Data(1, 2) = dHeaders(1)
    
    For r = 2 To rCount
        Data(r, 1) = r - 1
        If dict.Exists(r - 1) Then
            Data(r, 2) = dict(r - 1)
        Else
            Data(r, 2) = 0
        End If
    Next r
    
    ' Write the values from the array to the destination range.
    
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    With dws.Range(dFirstCellAddress).Resize(, 2)
        .Resize(rCount).Value = Data
        .Resize(dws.Rows.Count - .Row - rCount   1).Offset(rCount).Clear
        '.Font.Bold = True
        '.EntireColumn.AutoFit
    End With
    
    'wb.save
    
    MsgBox "Consecutive count created.", vbInformation
    
End Sub

uj5u.com熱心網友回復：

想象一下Win/LoseA列中的數字，然后在單元格B3（不是B2，這將保持為空）中添加以下公式并將其復制下來：

=IF(AND(A3=0,A3<>A4),COUNTIF($A$2:A3,A3)-SUM($B$2:B2),"")

然后計算它們，只需=COUNTIF(B:B,E2)在 F2 中使用并將其復制下來。

uj5u.com熱心網友回復：

在我看來，您可以通過兩種方式閱讀此要求：

• 您可以在 4 個零的序列中計算 1、2、3 和 4 的出現次數；
• 您只能計算上述的最大出現次數；

我接受了后者的假設：

中的公式C1：

=LET(X,SEQUENCE(15),Y,LEN(TEXTSPLIT(CONCAT(IF(A2:A32," ",1)),," ",1)),VSTACK({"Occurences of 0","Number of Times"},HSTACK(X,BYROW(X,LAMBDA(a,SUM(--(Y=a)))))))

Important note:

It may not be best to rely on CONCAT() since depending on the amount of rows you want to concatenate, it may strike a character limit. Instead you could try something like:

=LET(X,SEQUENCE(15),Y,LEN(TEXTSPLIT(REDUCE("",A2:A32,LAMBDA(a,b,a&IF(b," ",1))),," ",1)),VSTACK({"Occurences of 0","Number of Times"},HSTACK(X,BYROW(X,LAMBDA(a,SUM(--(Y=a)))))))

Also, please note that ms365 is required for the above functions to run properly (and at time of writing VSTACK(), HSTACK() and TEXTSPLIT() are still in the insider's BETA-channels.

標籤：擅长 vba excel公式

上一篇：兩個日期之間的MM.YYYY（月）串列的VBAUDF

下一篇：從單元格中提取數字的特定結構