如何在Excel中衡量文本相关性？如何参照cross-correlation计算文本相似度百分比？

嘿，刚好对文本相似度这块熟得很，来给你唠清楚这俩问题：

Excel本身没有直接的文本相关性函数，但咱们可以用内置函数组合、VBA或者Power Query来实现，分几种场景给你说：

• 基础版：用内置函数快速算词汇重合度
  如果你只需要简单判断两个文本的词汇重叠比例，Excel 365/2021的TEXTSPLIT函数能帮上大忙。比如要算A1和B1的相似度：

  1. 先统计两个文本的共同单词数：=SUMPRODUCT(--(ISNUMBER(MATCH(TEXTSPLIT(A1," "), TEXTSPLIT(B1," "), 0))))
  2. 再除以两个文本中较长的单词总数（避免短文本拉低比例）：=刚才的公式 / MAX(COUNTA(TEXTSPLIT(A1," ")), COUNTA(TEXTSPLIT(B1," ")))
  3. 最后乘以100转成百分比就行。
     注意：这个方法会区分大小写，如果要忽略的话，把TEXTSPLIT嵌套LCase，比如TEXTSPLIT(LCase(A1)," ")。

• 进阶版：用VBA自定义函数（支持复杂逻辑）
  如果要处理标点、重复单词、更灵活的匹配规则，写个VBA函数更靠谱。比如这个自定义函数：

  Function TextSimilarity(text1 As String, text2 As String) As Double
      Dim words1 As Variant, words2 As Variant
      Dim i As Integer, matchCount As Integer
      ' 预处理：转小写、拆分单词、移除标点
      words1 = Split(LCase(Replace(text1, ".", "")), " ") ' 可添加更多标点替换规则
      words2 = Split(LCase(Replace(text2, ".", "")), " ")
      
      ' 统计匹配的单词数（避免重复匹配同一个词）
      For i = LBound(words1) To UBound(words1)
          If words1(i) <> "" Then ' 跳过空字符串
              Dim matchIndex As Integer
              matchIndex = UBound(Filter(words2, words1(i)))
              If matchIndex >= 0 Then
                  matchCount = matchCount + 1
                  words2(matchIndex) = "" ' 标记已匹配，防止重复计数
              End If
          End If
      Next i
      
      ' 计算相似度：匹配数除以较长文本的单词总数
      TextSimilarity = matchCount / WorksheetFunction.Max(UBound(words1) + 1, UBound(words2) + 1)
  End Function
  

  写完后在单元格里直接用=TextSimilarity(A1,B1)*100就能得到百分比结果，比内置函数灵活多了。

• 批量版：用Power Query处理大量数据
  如果要一次性算几十上百组文本的相似度，Power Query更高效。大致步骤是：导入数据→拆分文本为单词列→合并两个文本的单词列表→统计重合数→计算相似度百分比，全程可视化操作，不用写太多公式。

信号的cross-correlation是看两个信号在不同偏移下的匹配程度，但文本是离散的词汇序列，咱们可以把这个思路转化成**「内容重合度」的计算**——毕竟你例子里语序打乱也算100%，重点是词汇有没有完全覆盖。

核心方法：基于词汇集合的相似度计算 #

最适合你例子的是「忽略语序」的计算方式，步骤很简单：

1. 预处理文本：统一大小写、去掉标点，把文本拆成独立单词的集合（集合自动去重，也不考虑顺序）
2. 计算重合比例：用两个集合的交集大小除以某个基准长度（比如较长文本的单词数、或者两个集合的并集大小），再转成百分比。

举你给的例子：

• 例子1：语序不同但内容完全一致
  文本①："The Legend of Awesome Dog" → 预处理后单词集合：{the, legend, of, awesome, dog}
  文本②："Dog Awesome The legend of" → 预处理后单词集合：{dog, awesome, the, legend, of}
  交集大小是5，较长文本的单词数也是5 → 相似度=5/5×100%=100%，和你说的一致。

• 例子2：部分内容重合
  文本③："Dog awesome number 9" → 预处理后单词集合：{dog, awesome, number, 9}
  和文本①的交集是{dog, awesome}，大小为2；文本①的单词数是5 → 相似度=2/5×100%=40%，正好和你给出的结果匹配。

其他可选方法（如果需要考虑语序） #

如果你的场景里语序很重要（比如像信号那样看序列匹配），可以用：

• Levenshtein距离（编辑距离）：计算把一个文本改成另一个需要的最少操作（增、删、改），然后用1 - (编辑距离/最长文本长度)转成相似度百分比。
• 序列对齐算法：类似信号cross-correlation，滑动一个文本和另一个比对，找最大匹配的位置和比例，但这个实现起来复杂一些，一般用Python之类的语言更方便。

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