Excel中快速查找列内模糊重复项并标记的方法问询

一、先做文本预处理（关键前提） #

长文本模糊匹配的核心是先消除噪音，否则任何相似度算法都会失效：

• 清理特殊字符：用公式=CLEAN(SUBSTITUTE(SUBSTITUTE(B2,"%&",""),"COMPANY DESCRIPTION:",""))去除示例中的特殊符号和无关前缀，可根据实际情况扩展替换规则
• 统一格式：=LOWER(TRIM(B2))转小写并去除首尾空格
• 移除停用词：可创建停用词列表（如"the","is","an","and"等），用SUBSTITUTE()批量替换为空，或用VBA/Python批量处理

二、VBA自定义Jaccard相似度函数（适合中等规模数据集） #

Levenshtein距离更适合短文本编辑距离计算，长文本推荐用Jaccard相似度（计算两个文本的词交集与并集的比例），匹配度阈值设为0.8即可：

1. 按Alt+F11打开VBA编辑器，插入新模块，粘贴以下代码：

Function JaccardSimilarity(text1 As String, text2 As String) As Double
    Dim words1 As Collection, words2 As Collection
    Dim word As Variant, intersection As Integer, union As Integer
    Set words1 = New Collection
    Set words2 = New Collection
    
    ' 分割文本为单词（按空格分割，可扩展处理标点）
    For Each word In Split(Trim(text1), " ")
        If word <> "" Then
            On Error Resume Next
            words1.Add word, Key:=word
            On Error GoTo 0
        End If
    Next
    
    For Each word In Split(Trim(text2), " ")
        If word <> "" Then
            On Error Resume Next
            words2.Add word, Key:=word
            On Error GoTo 0
        End If
    Next
    
    ' 计算交集
    intersection = 0
    For Each word In words1
        On Error Resume Next
        words2.Item(word)
        If Err.Number = 0 Then intersection = intersection + 1
        On Error GoTo 0
    Next
    
    ' 计算并集
    union = words1.Count + words2.Count - intersection
    
    ' 返回相似度
    If union = 0 Then
        JaccardSimilarity = 0
    Else
        JaccardSimilarity = intersection / union
    End If
End Function

2. 返回Excel，在C2单元格输入公式：
   =MAX(IF(JaccardSimilarity(B2,$B$2:$B$1000)>=0.8, JaccardSimilarity(B2,$B$2:$B$1000), 0))
   按Ctrl+Shift+Enter作为数组公式输入（Excel 365可直接回车），下拉填充。

3. 筛选C列值≥0.8的行，在对应B列单元格标记：
   =IF(C2>=0.8, "【模糊重复】"&B2, B2)

三、Power Query批量处理（适合Excel 365/2021） #

1. 数据导入Power Query：选中数据区域 → 数据选项卡 → 从表格/区域
2. 文本清洗：添加自定义列，用Power Query函数清理文本，示例：
   Text.Lower(Text.Trim(Text.Remove([公司描述], {"%&", "COMPANY DESCRIPTION:"})))
3. 添加自定义函数计算Jaccard相似度：
   在Power Query编辑器中，新建空白查询并命名为JaccardSimilarity，粘贴以下M代码：

(text1 as text, text2 as text) as number =>
let
    Split1 = List.Distinct(Splitter.SplitTextByWhitespace()(Text.Trim(text1))),
    Split2 = List.Distinct(Splitter.SplitTextByWhitespace()(Text.Trim(text2))),
    Intersection = List.Intersect({Split1, Split2}),
    Union = List.Union({Split1, Split2}),
    Similarity = if List.Count(Union) = 0 then 0 else List.Count(Intersection)/List.Count(Union)
in
    Similarity

4. 回到主查询，添加自定义列遍历所有行计算相似度，筛选相似度≥0.8的项，标记原数据后加载回Excel。

四、Python批量处理（适合10万行以上超大型数据集） #

用pandas和sklearn处理效率更高：

import pandas as pd
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity

# 读取Excel数据
df = pd.read_excel("your_data.xlsx")

# 文本预处理函数
def preprocess_text(text):
    text = str(text).lower().strip()
    text = text.replace("%&", "").replace("company description:", "")
    # 可添加更多停用词移除逻辑
    return text

df["clean_desc"] = df["公司描述"].apply(preprocess_text)

# 用TF-IDF向量化文本，计算余弦相似度
tfidf = TfidfVectorizer(stop_words="english")
tfidf_matrix = tfidf.fit_transform(df["clean_desc"])
similarity_matrix = cosine_similarity(tfidf_matrix)

# 标记相似度≥0.8的模糊重复项
df["is_duplicate"] = False
for i in range(len(df)):
    # 排除自身匹配，找其他行中相似度≥0.8的
    duplicates = [j for j in range(len(df)) if j != i and similarity_matrix[i][j] >= 0.8]
    if duplicates:
        df.loc[i, "is_duplicate"] = True

# 标记原描述列
df["公司描述"] = df.apply(lambda x: "【模糊重复】" + x["公司描述"] if x["is_duplicate"] else x["公司描述"], axis=1)

# 保存回Excel
df.to_excel("marked_data.xlsx", index=False)

注意事项 #

• 预处理步骤可根据实际数据调整（比如不同的特殊字符、前缀）
• 阈值0.8可按需微调，误判多可提高到0.85，漏判多可降到0.75
• 超大型数据集优先用Python方法，Excel公式/VBA会卡顿

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