☰

查找符合两个条件的行

tag-icon • tag-icon microsoft-excel
查找符合两个条件的行

我有一张包含类似以下数据的工作表：

P1_ShipType         P2_ShipType         RoundTime
Kus_AssaultFrigate  Tai_AssaultFrigate  117
Kus_AssaultFrigate  Tai_AttackBomber    17
Kus_AssaultFrigate  Tai_Carrier         1191
Kus_AttackBomber    Tai_AssaultFrigate  2775
Kus_AttackBomber    Tai_AttackBomber    18
Kus_AttackBomber    Tai_Carrier         0
Kus_Carrier         Tai_AssaultFrigate  1354
Kus_Carrier         Tai_AttackBomber    8
Kus_Carrier         Tai_Carrier         0

当 P1_ShipType 为 Kus_AttackBomber 且 P2_ShipType 为 Tai_Carrier 时，如何检索“RoundTime”？

在 SQL 中这很容易，但在 Excel 中如何做到这一点？

我找到了一种使用公式来实现的方法：https://support.microsoft.com/en-us/kb/214142

但我很好奇如何使用数据透视表或 VBA 实现这一点。我还需要能够对返回的值执行一些数学运算。

这是我根据下面给出的答案设计的 UDF。谢谢！

Function GetRoundTime(s1 As String, s2 As String, s3 As String) As String
    Dim i As Long
    'Need to find out what the last row is dynamically instead of hardcoding it at 1000
    For i = 2 To 1000
        If Worksheets(s3).Cells(i, "D").Value = s1 And Worksheets(s3).Cells(i, "I").Value = s2 Then
            GetRoundTime = CStr(Worksheets(s3).Cells(i, "K").Value)
            Exit Function
        End If
    Next i
    GetRoundTime = "Failed"
End Function

答案1

尝试一下这个小插件：

Sub FindFirstMatch()
    Dim i As Long
    Dim rw As Long, rt As Long
    Dim s1 As String, s2 As String
    s1 = "Kus_AttackBomber"
    s2 = "Tai_Carrier"

    For i = 2 To 10
        If Cells(i, "A") = s1 And Cells(i, "B") = s2 Then
            MsgBox "first match found on row # " & i & " with Round Time = " & Cells(i, "C").Value
            Exit For
        End If
    Next i
End Sub

在此处输入图片描述

答案2

您还可以使用数组公式：

{=INDEX(C:C,MATCH("Kus_AttackBomber Tai_Carrier",A:A&" "&B:B,0))}

应该可以做你想做的事，而不必使用数据透视表或宏

相关内容