如何在Excel列中查找完全及相似重复数据并扩展COUNTIF公式？

Got it, let's tackle this problem step by step. Your existing formula works great for exact duplicates, but extending it to catch similar entries (with 3+ shared keywords) requires a bit more work since we need to break down each title into keywords and compare overlaps. Here's how to do it:

首先：理解现有公式的局限 #

你的现有公式 =IF(COUNTIF($C$2:C2,C2)>1, "Duplicate!","Original") 完美捕捉完全匹配的重复项，但它没法识别关键词重叠的相似条目——我们需要先拆分每个标题的关键词，再统计交叉匹配的数量。

方案1：适用于Excel 365/2021（支持动态数组函数） #

用LET、TEXTSPLIT和BYROW这些现代函数，公式会更清晰易读。把下面的公式放到D2单元格（假设你的标题在C列），然后下拉填充：

=LET(
    currentKeywords, TEXTSPLIT(C2, " "),
    // 拆分当前标题为关键词数组（按空格分隔）
    prevEntries, $C$2:C1,
    // 检查当前行以上的所有历史条目
    exactDuplicate, COUNTIF(prevEntries, C2) > 0,
    // 判断是否有完全重复
    similarMatchCount, MAX(BYROW(prevEntries, LAMBDA(entry, 
        SUM(--ISNUMBER(MATCH(TEXTSPLIT(entry, " "), currentKeywords, 0)))
    ))),
    // 计算当前标题与每个历史条目的共同关键词数量，取最大值
    IF(exactDuplicate, "Duplicate!", 
       IF(similarMatchCount >= 3, "Similar Duplicate!", "Original"))
)

额外优化：排除停用词（可选） #

如果像"the"、"by"、"a"这类无意义的词不想算入关键词统计，可以添加FILTER过滤掉它们：
修改currentKeywords这一行：

currentKeywords, FILTER(TEXTSPLIT(C2, " "), NOT(TEXTSPLIT(C2, " ")={"the","by","a","an","of","and"})),

你可以根据自己的需求扩展停用词列表。

方案2：适用于旧版Excel（无动态数组支持） #

如果用的是Excel 2019或更早版本，需要用FILTERXML替代TEXTSPLIT，用SUMPRODUCT替代BYROW。公式如下：

=IF(COUNTIF($C$2:C1,C2)>0,"Duplicate!",
    IF(MAX(
        SUMPRODUCT(
            --ISNUMBER(MATCH(
                FILTERXML("<t><s>"&SUBSTITUTE($C$2:C1," ","</s><s>")&"</s></t>","//s"),
                FILTERXML("<t><s>"&SUBSTITUTE(C2," ","</s><s>")&"</s></t>","//s"),
                0
            ))
        )
    )>=3,"Similar Duplicate!","Original")
)

注意事项 #

• 大小写敏感：如果想忽略大小写（比如"The"和"the"视为同一个关键词），给所有关键词套上UPPER函数，比如把FILTERXML(...)改成UPPER(FILTERXML(...))
• 标点处理：如果标题里有标点（比如逗号、冒号），先在SUBSTITUTE里去掉，比如SUBSTITUTE(C2, ",", "")再拆分关键词，避免标点干扰匹配

内容的提问来源于stack exchange，提问作者sam s