Excel/VBA 检查一行是否存在

Excel/VBA to check if a row exists

提问by Robin Day

I have a sheet full of some raw data, about 20,000 rows and 50 columns. (Number of rows expected to increase, probably double if not triple)

我有一张包含一些原始数据的工作表，大约有 20,000 行和 50 列。（预计行数会增加，如果不是三倍，可能会增加一倍）

I need a formula to look at this data and determine if a row exists for data in two specified columns. My current formula is as follows.

我需要一个公式来查看此数据并确定两个指定列中的数据是否存在一行。我目前的公式如下。

Function CheckExists(Table As Range, SearchCol1 As Integer, SearchVal1 As Variant, SearchCol2 As Integer, SearchVal2 As Variant)

    Dim i As Long
    Dim exists As Boolean

    exists = False

    For i = 1 To Table.Rows.Count
        If Table.Cells(i, SearchCol1) = SearchVal1 Then
            If Table.Cells(i, SearchCol2) = SearchVal2 Then
                exists = True
                Exit For
            End If
        End If
    Next i

    CheckExists = exists

End Function

I run this formula from another sheet, with about 5000 rows.

我从另一个工作表运行这个公式，大约有 5000 行。

My problem is, this kills my pc, it takes ages to calculate the cells. I'm hoping someone can offer some suggestions on how to make this faster or even better, a built in formula that can do what I'm after.

我的问题是，这会杀死我的电脑，计算单元格需要很长时间。我希望有人可以提供一些关于如何使这个更快甚至更好的建议，一个可以完成我所追求的内置公式。

回答by Macros

You could add a column to the end of the raw data with a simple if formula e.g.

您可以使用简单的 if 公式在原始数据的末尾添加一列，例如

if(AND(A1=4, B1=6), 1, 0)

Where A1 / B1 are the columns to check and 4 and 6 would be replaced by the actual values. The check if a row exists is then just checking the sum of this column:

其中 A1 / B1 是要检查的列，4 和 6 将替换为实际值。检查一行是否存在然后只是检查此列的总和：

if(sum(C:C) > 0, TRUE, FALSE)

where C is the column with the formula. On large Excel docs I generally find a composite approach like this to be most efficient. If you post a sample of the data I may be able to refine further.

其中 C 是带有公式的列。在大型 Excel 文档中，我通常发现像这样的复合方法是最有效的。如果您发布数据样本，我可能会进一步完善。

回答by Graham

I suggest adding a column to concatenate the values in the two columns of interest then using the MATCH worksheet function to search the new column.

我建议添加一列来连接感兴趣的两列中的值，然后使用 MATCH 工作表函数来搜索新列。

回答by Andy

If you use Cells.Find it'll save you a lot of iterating over cells that never contain the value you want.

如果您使用 Cells.Find，它将为您节省大量迭代从未包含您想要的值的单元格。

e.g.

例如

Cells.Find(What:="-1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
      xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
      , SearchFormat:=False).Activate

will activate the cell that it finds the value in. You can then check to see if the other cell you're comparing has the correct value (if the cell was located).

将激活它在其中找到值的单元格。然后您可以检查您正在比较的另一个单元格是否具有正确的值（如果该单元格已找到）。

You may also have to check that the row/column you're in is the correct one for the value you're looking for, especially if you're searching for something like the number 20, it will locate the number 20 in the wrong row.

您可能还需要检查您所在的行/列是否与您要查找的值相符，特别是如果您正在搜索数字 20 之类的内容，它会在错误的位置找到数字 20排。

Have a play, I think you'll find it's quite a bit faster than normal iteration.

玩一玩，我想你会发现它比正常迭代快很多。

Additional, possibly unwarranted advice: with that much data, would a database not be useful?

另外，可能没有根据的建议：有这么多数据，数据库会不会没用？

Update:
I've had a quick play - this function can replace the existing one you're using above, let me know if it's any faster.

更新：
我有一个快速播放 - 此功能可以替换您在上面使用的现有功能，如果它更快，请告诉我。

Function FindCheckExists(Table As Range, SearchCol1 As Integer, _
                         SearchVal1 As Variant, SearchCol2 As Integer, _
                         SearchVal2 As Variant)

    Dim i As Long
    Dim exists As Boolean
    Dim result As Range

    exists = False
    Do While (Not exists)

        Set result = Cells.Find(What:=SearchVal1, After:=Cells(1, SearchCol1), _
          LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        If (result Is Nothing) Then Exit Function

        If result.Offset(0, (SearchCol2 - SearchCol1)).Value = SearchVal2 Then _
            exists = True

    Loop

    FindCheckExists = exists

End Function

The above has been reformatted with _'s to fit neater on the stackoverflow page, it'll probably look neater in Excel without them.

上面已经用 _ 重新格式化以更适合 stackoverflow 页面，如果没有它们，它在 Excel 中可能看起来更整洁。

回答by SqlRyan

It's not necessary to use VBA to accomplish this - you can do it with just a regular front-end formula. The formula below (the first just does rows 1-5000, but the second will do the entire column) will determine if the combination in question exists:

没有必要使用 VBA 来完成此操作 - 您只需使用常规前端公式即可完成。下面的公式（第一个只处理 1-5000 行，但第二个将处理整列）将确定有问题的组合是否存在：

=SUMPRODUCT(--(A1:A5000="SearchValue1"),--(B1:B5000="SearchValue2"))

=SUMPRODUCT(--(A:A="SearchValue1"),--(B:B="SearchValue2"))

To use it, you'd just fix "SearchValue1" and "SearchValue2" to be the values you're looking for, and change the column letters if you're not using A and B.

要使用它，您只需将“SearchValue1”和“SearchValue2”固定为您要查找的值，如果您不使用 A 和 B，则更改列字母。

回答by Patrick Honorez

I'd say much like rwmnau, except I generally use:

我会说很像 rwmnau，除了我通常使用：

=SUMPRODUCT((A1:A5000="SearchValue1")*(B1:B5000="SearchValue2"))  

EDITIf you use Excel 2007, you may use COUNTIFS function (note the S at the end).

编辑如果您使用 Excel 2007，您可以使用 COUNTIFS 函数（注意末尾的 S）。

回答by Patrick Honorez

I've found it quickest to use autofilter. Filter it based on your criteria, then delete the remaining visible rows.

我发现使用自动过滤器最快。根据您的条件对其进行过滤，然后删除剩余的可见行。

e.g. Ws.Rows(1).Insert 'Filter does not check first row Ws.AutoFilterMode = False Ws.Cells.AutoFilter searchCol1, "=*" & searchVal1 & "*", , , False Ws.Cells.AutoFilter searchCol2, "=*" & searchVal2 & "*", , , False Ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).EntireRow.Delete ActiveSheet.AutoFilterMode = False

例如 Ws.Rows(1).Insert 'Filter does not check first row Ws.AutoFilterMode = False Ws.Cells.AutoFilter searchCol1, "=*" & searchVal1 & "*", , , False Ws.Cells.AutoFilter searchCol2, "=*" & searchVal2 & "*", , , False Ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).EntireRow.Delete ActiveSheet.AutoFilterMode = False

Hope that helps.

希望有帮助。