vba 如何在Excel中找到匹配的行？

How to find a matching row in Excel?

提问by Lernkurve

Given

给定的

A search key

一个搜索键

---------------------------
|   |  A  | B | C |   D   |
---------------------------
| 1 | 01  | 2 | 4 | TextA |
---------------------------

An Excel sheet

一张 Excel 表格

------------------------------
|   |  A | B | C |   D   | E |
------------------------------
| 1 | 03 | 5 | C | TextZ |   |
------------------------------
| 2 | 01 | 2 | 4 | TextN |   |
------------------------------
| 3 | 01 | 2 | 4 | TextA |   |
------------------------------
| 4 | 22 | T | N | TextX |   |
------------------------------

Question

题

I would like to have a function like this: f(key) -> result_row.

我想要一个这样的函数：f(key) -> result_row。

That means: given a search key (01, 2, 4, TextA), the function should tell me that the matching row is 3 in the example above.

这意味着：给定一个搜索键 (01, 2, 4, TextA)，函数应该告诉我匹配的行是上面例子中的 3。

The values in the search key (A, B, C, D) are a unique identifier.

搜索关键字（A、B、C、D）中的值是唯一标识符。

How do I get the row number of the matching row?

如何获取匹配行的行号？

One solution

一种解决方案

The solution that comes first to my mind is to use Visual Basic for Application (VBA) to scan column A for "01". Once I've found a cell containing "01", I'd check the adjacent cells in columns B, C and D whether they match my search criteria.

我首先想到的解决方案是使用 Visual Basic for Application (VBA) 来扫描 A 列中的“01”。找到包含“01”的单元格后，我将检查 B、C 和 D 列中的相邻单元格是否符合我的搜索条件。

I guess that algorithm will work. But: is there a better solution?

我想那个算法会起作用。但是：有更好的解决方案吗？

Version

版本

• Excel 2000 9.0.8961 SP3
• VBA 6.5

• Excel 2000 9.0.8961 SP3
• VBA 6.5

However, if you happen to know a solution in any higher versions of Excel or VBA, I am curious to know as well.

但是，如果您碰巧知道任何更高版本的 Excel 或 VBA 中的解决方案，我也很想知道。

Edit: 22.09.2010

编辑：22.09.2010

Thank you all for your answers. @MikeD: very nice function, thank you!

谢谢大家的答案。@MikeD：非常好的功能，谢谢！

My solution

我的解决方案

Here is the solution I prototyped. It's all hard-coded, too verbose and not a function as in MikeD's solution. But I'll rewrite it in my actual application to take parameters and to return a result value.

这是我原型化的解决方案。它都是硬编码的，过于冗长，而不是 MikeD 解决方案中的功能。但我会在我的实际应用程序中重写它以获取参数并返回结果值。

Sub FindMatchingRow()
    Dim searchKeyD As Variant
    Dim searchKeyE As Variant
    Dim searchKeyF As Variant
    Dim searchKeyG As Variant

    Const indexStartOfRange As String = "D6"
    Const indexEndOfRange As String = "D9"

    ' Initialize search key
    searchKeyD = Range("D2").Value
    searchKeyE = Range("E2").Value
    searchKeyF = Range("F2").Value
    searchKeyG = Range("G2").Value


    ' Initialize search range
    myRange = indexStartOfRange + ":" + indexEndOfRange

    ' Iterate over given Excel range
    For Each myCell In Range(myRange)

        foundValueInD = myCell.Offset(0, 0).Value
        foundValueInE = myCell.Offset(0, 1).Value
        foundValueInF = myCell.Offset(0, 2).Value
        foundValueInG = myCell.Offset(0, 3).Value

        isUnitMatching = (searchKeyD = foundValueInD)
        isGroupMatching = (searchKeyE = foundValueInE)
        isPortionMatching = (searchKeyF = foundValueInF)
        isDesignationMatching = (searchKeyG = foundValueInG)
        isRowMatching = isUnitMatching And isGroupMatching And isPortionMatching And isDesignationMatching

        If (isRowMatching) Then
            Range("D21").Value = myCell.Row
            Exit For
        End If

    Next myCell
End Sub

This is the Excel sheet that goes with the above code:

这是与上述代码配套的 Excel 表：

回答by MikeD

here's a small VBA function I often use for that kind of purpose

这是我经常用于这种目的的一个小的 VBA 函数

Function FindInRange(InRange As Range, Arg As Range) As Integer
Dim Idx As Integer, Jdx As Integer, IsFound As Boolean

    FindInRange = 0
    IsFound = False

    For Idx = 1 To InRange.Rows.Count
        IsFound = True
        For Jdx = 1 To InRange.Columns.Count
            If InRange(Idx, Jdx) <> Arg(1, Jdx) Then
                IsFound = False
                Exit For
            End If
        Next Jdx

        If IsFound Then
            FindInRange = Idx
            Exit For
        End If
    Next Idx

End Function

InRangemust be same width or wider than Arg, but can be of course larger or smaller than your A:D

InRange必须等于或宽于Arg，但当然可以大于或小于您的 A:D

In your Sample sheet enter in an empty cell "=findinrange(A1:D4,A3:D3)"

在您的样本表中输入一个空单元格“=findinrange(A1:D4,A3:D3)”

The formula will return "0" if nothing found, else the row #

如果没有找到，公式将返回“0”，否则行#

Good luck - MikeD

祝你好运 - MikeD

回答by Dick Kusleika

Assume your search key starts in A1 and your data starts in A3. This array formula will return the row number where all the data matches the search key

假设您的搜索键从 A1 开始，您的数据从 A3 开始。此数组公式将返回所有数据与搜索键匹配的行号

=SUM(($A:$A=A1)*($B:$B=B1)*($C:$C=C1)*($D:$D=D1)*(ROW($A:$A)))

Enter with Control+Shift+Enter, not just Enter. Note that it returns the worksheet row, not the position in the table. If you want the position in the table, you could subtract one less than the starting row of the table from the result (2 in this case because the table starts on row 3).

使用 Control+Shift+Enter 输入，而不仅仅是 Enter。请注意，它返回工作表行，而不是表中的位置。如果您想要在表中的位置，您可以从结果中减去比表的起始行少 1（在本例中为 2，因为表从第 3 行开始）。

If there are more than one rows that match, this will return the sum of all the rows (not very helpful). But I assumed there was only one matching row.

如果匹配的行不止一行，这将返回所有行的总和（不是很有帮助）。但我认为只有一个匹配的行。

回答by Karsten W.

Another option is to add a new column to your excel sheet where the columns A:D are concatenated, and then use the lookup/sverweisfunction. This would be probably faster than a VBA solution.

另一种选择是向 Excel 工作表中添加一个新列，其中 A:D 列连接在一起，然后使用该lookup/sverweis函数。这可能比 VBA 解决方案更快。