☰

如何计算两个不同的列？

tag-icon • tag-icon windows microsoft-excel user-accounts formatting format
如何计算两个不同的列？

我确实需要您关于下表的帮助，我需要一个公式来显示每个项目有多少待处理/已关闭。

Project Status                      Project    Pending  Closed
VIVA    closed                      VIVA         1       2
ZAIN    closed                      ZAIN         1       1
VIVA    PENDING                     WATANIA      1       0
WATANIA closed              
ZAIN    PENDING             
VIVA    closed

答案1

我只知道 VBa（我真的不知道工作簿函数），所以既然你没有指定你想要的，这个 VBa 应该可以做你想要的

Sub UpdateStatus()

Dim row As Integer
row = 2 ' sets the starting row    

Dim statisticRow As Integer
statisticRow = 2

Do While (True) ' we must reset everything before we go on our quest. Be gone foul witch

If Range("F" & statisticRow).Value = "" Then
Exit Do
End If

Range("F" & statisticRow).Value = ""
Range("G" & statisticRow).Value = ""
Range("H" & statisticRow).Value = ""
statisticRow = statisticRow + 1
Loop

Do While (True)

Dim currentValue As String
currentValue = Range("A" & row).Value

Dim otherValue As String

    If currentValue = "" Then
        Exit Do
    End If

Dim otherRow As Integer
otherRow = 2 ' sets the starting row where the results are


Do While (True) ' find it or add it        

    otherValue = Range("F" & otherRow).Value
    Dim currentValueStatus As String
    If otherValue = "" Then             

        currentValueStatus = Range("B" & row).Value

        Range("F" & otherRow).Value = currentValue

         If currentValueStatus = "closed" Then
            Range("H" & otherRow).Value = 1
        End If

        If currentValueStatus = "PENDING" Then
            Range("G" & otherRow).Value = 1
        End If

        Exit Do
    End If

    If currentValue = otherValue Then ' Good news sire, I found it

        currentValueStatus = Range("B" & row).Value

        If currentValueStatus = "closed" Then
            Range("H" & otherRow).Value = Range("H" & otherRow).Value + 1
        End If

        If currentValueStatus = "PENDING" Then
            Range("G" & otherRow).Value = Range("G" & otherRow).Value + 1
        End If

    Exit Do

    End If
    otherRow = otherRow + 1
    Loop
    row = row + 1

Loop    

End Sub

前

在此处输入图片描述

在我运行宏之后

在此处输入图片描述

如您所见，它会自动为您输入公司名称，并计算出每个公司有多少家。这意味着，如果您添加了一家新公司并再次运行该宏，它将使用新的详细信息进行更新，而无需对代码进行任何更改。

相关内容