识别重复项或计算超过255个字符（长文本）的数量

Question

识别重复项或计算超过255个字符（长文本）的数量

excelexcel-2010conditional-formattingcountif

5

我有一个奇怪的问题，我有一个包含两列的Excel文档，A列包含关键词，这些关键词并不唯一，B列包含长度通常超过255个字符的各种文本。

我想对A列应用筛选器以选择两个关键词，这没问题，然后我想在B列中标识任何重复项 - 仅适用于可见字段，即对A列进行筛选的结果。

这应该很简单; 然而，条件格式无法识别重复项，我猜测是因为它不喜欢文本的长度。

即使尝试使用mandrolic方法也会失败，因为搜索框似乎只能接受特定长度的搜索字符串。

- John

因此，如果您在B列中有两个字符串值，每个字符串值长度为550个字符，并且具有相同的“键”，但一个以问号结尾，另一个以感叹号结尾，则它们不是重复项。但是，如果第三个具有相同“键”的相同字符串以问号结尾，则将“识别”两行。（仍然不知道“识别”确切意味着什么） - user4039065

它对于较短的字符串是否也能成功？ - QHarr

列B中有多少行的数据？这可能是决定是否适合使用数组公式的因素之一。 - user4039065

不太对，我在A列应用的筛选器只会显示key1和key3的值。这就是我需要突出显示重复项的结果子集数据。 - John

大约500行 - John

是的，在其他字符串上应用条件格式化工作正常。可能是这些字符串从PDF中复制而来，格式可能有些奇怪？但是那么两个字符串都会有问题。 - John

2个回答

1

虽然来晚了，但是如果这个内置的解决方案可以帮助其他人解决计数if函数字符限制在255个字符的问题。您可以使用此替换选项，该选项搜索给定REF_TO_CELL_WITH_VALUE中的特定文本字符串，并搜索REF_TO_YOUR_LIST中引用的列表。它通过尝试替换列表中每个项目中的文本字符串实例来工作。

=SUMPRODUCT((LEN(REF_TO_YOUR_LIST)-LEN(SUBSTITUTE(REF_TO_YOUR_LIST,REF_TO_CELL_WITH_VALUE,"")))/LEN(REF_TO_CELL_WITH_VALUE))

- Eeeeed

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user4039065 · Accepted Answer

COUNTIF函数的字符串条件限制为255个字符。

^{来自support.office.com}
_{长字符串返回错误值。当您使用COUNTIF函数匹配超过255个字符的字符串时，它会返回不正确的结果。}

COUNTIF支持页面上提供了一种解决方法，但我无法使其工作，所以我编写了一个用户定义的函数，并添加了隐藏/可见和区分大小写选项。

COUNTIFSBIGTXT函数 - 用于超过255个字符的条件字符串的CountIfs功能

将此放入公共模块代码表中（alt+F11，插入，模块）。

Option Explicit

Function COUNTIFSBIGTXT(iOptions As Long, ParamArray pairs()) As Long
    'COUNTIFSBIGTXT - CountIfs functionality for criteria strings longer than 255 characters
    ' https://dev59.com/J63la4cB1Zd3GeqPMWgF
    '
    ' =COUNTIFSBIGTXT(<options>, <string_range1>, <criteria1>, [string_range2], [criteria2], …)
    '        OPTIONS
    '      0 No options
    '     +1 Include hidden cells in <string_range1>, [string_range2], etc
    '     +2 Case sensitive comparison

    'throw error if string_range and criteria do not come in pairs
    If Not CBool(UBound(pairs) Mod 2) Then
        COUNTIFSBIGTXT = CVErr(xlErrValue)
        Exit Function
    End If

    'declare variables
    Dim i As Long, j As Long
    Dim bIncludeHidden As Boolean, bCaseSensitive As Boolean

    'set optional booleans
    bIncludeHidden = CBool(1 And iOptions)
    bCaseSensitive = CBool(2 And iOptions)

    'restrict full column references to the parent worksheet's UsedRange
    Set pairs(LBound(pairs)) = Intersect(pairs(LBound(pairs)), pairs(LBound(pairs)).Parent.UsedRange)

    'resize all <string_range> to the same dimensions
    With pairs(LBound(pairs))
        For i = LBound(pairs) + 2 To UBound(pairs) Step 2
            Set pairs(i) = pairs(i).Resize(.Rows.Count, .Columns.Count)
            'Debug.Print pairs(i).Address(0, 0)
        Next i
    End With

    'loop cell count in pairs(LBound(pairs)) for relative ordinal
    For i = 1 To pairs(LBound(pairs)).Cells.Count
        'loop through each pair of <string_range> and <criteria>
        For j = LBound(pairs) To UBound(pairs) Step 2
            'exit for if any argument pair does not meet criteria
            With pairs(j).Cells(i)
                'throw out worksheet error codes
                If IsError(.Value) Then Exit For
                'do the pair(s) meet a case insensitive match
                If LCase(.Value2) <> LCase(pairs(j + 1)) Then Exit For
                'do the pair(s) meet a case sensitive match with option
                If .Value2 <> pairs(j + 1) And LCase(.Value2) = LCase(pairs(j + 1)) And bCaseSensitive Then Exit For
                'are the cells visible or hidden with include option
                If (.EntireRow.Hidden Or .EntireColumn.Hidden) And Not bIncludeHidden Then Exit For
            End With
        Next j

        'determine if all argument pairs matched
        If j > UBound(pairs) Then _
            COUNTIFSBIGTXT = COUNTIFSBIGTXT + 1
    Next i

End Function

语法:

=COUNTIFSBIGTXT(<options>, <string_range1>, <criteria1>, [optional string_range2], [optional criteria2], …)

文档

示例数据²

下面的示例是基于一个相同的600个字符字符串的9行，其中有三行被强制转换为大写。

示例1

简单的COUNTIF操作，可以丢弃过滤/隐藏的行，并且不区分大小写。

=COUNTIFSBIGTXT(0, B:B, B2)

示例2

扩展的COUNTIFS¹操作，可以丢弃筛选/隐藏的行，但是字符串比较区分大小写。第二个条件为A:A等于A2中的值。

=COUNTIFSBIGTXT(2, B:B, B2, A:A, A2)

"The logic flow 'short-circuits' on fail. With multiple string/criteria pairs, you can enhance performance by listing the least likely string_range/criteria matches first. You might see similar increases in calculation efficiency for special circumstances by reordering the latter three 'kick-out' Exit For statements for your particular requirements but I recommend keeping the check for worksheet errors as the primary check. For example, if you have a lot of potential string matches but very few visible rows, moving the check for visible cells above the check for string matches would cut down on the criteria checks."

"Many thanks to the Lorem Ipsum Generator for the sample string content."