如何在Excel中计算两个逗号分隔字符串的位置匹配相似度百分比？

问题描述 #

需要比较两个逗号分隔的字符串，基于相同索引位置的匹配值计算相似度百分比：

• 单元格A2内容："1,E,E,E,E,E"
• 单元格B1内容："1,A,A,A,A,A,A,B"
• 匹配情况：仅第一个值"1"匹配
• 总位置数：8
• 预期结果：12.5%

尝试使用TEXTSPLIT、LET和SEQUENCE函数编写公式，但出现#REF!错误，原公式如下：

=LET(
  a, TEXTSPLIT(A2, ","),
  b, TEXTSPLIT(B1, ","),
  len, MAX(COUNTA(a), COUNTA(b)),
  matches, SUM(--(INDEX(a, SEQUENCE(len)) = INDEX(b, SEQUENCE(len)))),
  ROUND(matches / len * 100, 2)
)

错误原因 #

当其中一个字符串拆分后的数组长度小于计算出的最大长度时，INDEX(a, SEQUENCE(len))会引用超出数组范围的位置，直接返回#REF!错误，导致整个公式失效。

修正后的公式 #

通过IFERROR将超出数组范围的引用转为空字符串，避免报错，同时保证仅相同索引位置的有效值匹配才被计数：

=LET(
  a, TEXTSPLIT(A2, ","),
  b, TEXTSPLIT(B1, ","),
  max_len, MAX(ROWS(a), ROWS(b)),
  arr_a, IFERROR(INDEX(a, SEQUENCE(max_len)), ""),
  arr_b, IFERROR(INDEX(b, SEQUENCE(max_len)), ""),
  matches, SUM(--(arr_a = arr_b)),
  ROUND(matches / max_len * 100, 2) & "%"
)

公式说明 #

1. TEXTSPLIT(A2, ",")/TEXTSPLIT(B1, ",")：将两个逗号分隔的字符串拆分为数组
2. MAX(ROWS(a), ROWS(b))：获取两个数组的最大长度作为总位置数
3. IFERROR(INDEX(a, SEQUENCE(max_len)), "")：将数组扩展到最大长度，超出原数组的位置用空字符串填充
4. SUM(--(arr_a = arr_b))：统计相同索引位置值匹配的次数（--将布尔值转为1/0）
5. ROUND(matches / max_len * 100, 2) & "%"：计算相似度百分比并保留两位小数，添加百分号

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