需要计算某个单元格上方出现 -1 的次数

Question

我有以下 Excel 电子表格 -

Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     6461735 Khanyile;Florence   Success      1000
Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     6765475 Manana;Witness      Success      1000
Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     6765459 Kubheka;Nomusa      Success      1000
Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     1905465 Nkosi;Xolani        Success      1000

我需要做的是计算每个人上方出现 -1 的次数。我尝试过数据透视，但它会将相同的员工分组在一起，我需要它单独进行计数。因此对于

Kubheka；Nomusa 将是 3，
Khanyile；Florence 将是 1，等等。

这可能吗？

Answer 1

这个 VBa 脚本可以做到这一点

Option Explicit

Sub walkThePlank()

Dim row As Integer
row = 1

Dim total As Integer
total = 0
Do While (Range("D" & row).Value <> "")

    Dim val As String
    val = Range("D" & row).Value

    If (val = "-1") Then
        total = total + 1
    Else
        Range("G" & row).Value = total
        total = 0
    End If


row = row + 1
Loop

End Sub

运行之前

之后

如何在 MS Office 中添加 VBA？

Answer 2

使用 VBA，插入模块在工作区并将其粘贴到右侧：

Public Function countNegatives(name As Range)
    countNegatives = 0
    If name <> "-1" Then
        therow = name.Row
        thecolumn = name.Column
    End If
    endrow = False
    counter = 0
    While endrow = False
        If therow > 1 Then
            therow = therow - 1
            If Cells(therow, thecolumn) = -1 Then
                counter = counter + 1
            Else
                endrow = True
            End If
        Else
            endrow = True
        End If
    Wend
    countNegatives = counter
End Function

在工作表的 G2 单元格中输入=countNegatives(D2)，您将得到结果。

Answer 3

不使用 VBA 的解决方案：

1. 使用以下公式创建一个辅助列： =IF(C2=-1,"",COUNTIF($C1:C$2,-1)-SUM($G1:G$2))
2. 添加带有名称和辅助列的数据透视表（或者您甚至可以过滤原始范围以排除没有名称的行。