如何在Excel中基于字符出现顺序判定字符串相似度？

核心思路 #

你的需求本质是评估两个字符串中字符出现顺序的匹配程度，核心是识别原字符串的字符序列（或相邻字符对）是否在模糊字符串中按顺序出现（允许中间插入其他字符）。针对你原公式在字符缺失场景下失效的问题，以下提供两种适配不同需求的Excel公式方案：

方案1：最长公共子序列（LCS）相似度 #

该方案通过计算两个字符串的最长公共子序列长度来衡量顺序匹配度，LCS越长说明顺序一致性越高，完全匹配时得分1，完全不匹配时得分0。

公式实现 #

=LET(
    orig, $A$1,
    fuzz, Table1[@Fuzzstring],
    orig_len, LEN(orig),
    fuzz_len, LEN(fuzz),
    lcs_matrix, MAKEARRAY(orig_len + 1, fuzz_len + 1,
        LAMBDA(i,j,
            IF(OR(i=1,j=1), 0,
                IF(MID(orig, i-1, 1)=MID(fuzz, j-1, 1),
                    INDEX(lcs_matrix, i-1, j-1)+1,
                    MAX(INDEX(lcs_matrix, i-1, j), INDEX(lcs_matrix, i, j-1))
                )
            )
        )
    ),
    lcs_length, INDEX(lcs_matrix, orig_len + 1, fuzz_len + 1),
    similarity_score, lcs_length / orig_len,
    similarity_score
)

公式说明 #

• orig/fuzz：分别引用原字符串（A1）和模糊字符串（Table1的当前行Fuzzstring列）
• lcs_matrix：创建二维数组动态计算每个位置的LCS长度，规则为：
  • 第一行/列设为0（空字符串与任何字符串的LCS长度为0）
  • 若当前字符匹配，取左上角值+1（延续之前的匹配序列）
  • 若不匹配，取上方或左方的最大值（保留最长的匹配序列）
• similarity_score：用LCS长度除以原字符串长度，得到0-1之间的相似度得分

示例验证 #

• 原字符串Paul vs 模糊字符串JoPaul：LCS长度为4，得分1（完全匹配顺序）
• 原字符串Paul vs 模糊字符串JPuao：LCS长度为2，得分0.5（仅部分顺序匹配）

方案2：相邻字符对匹配相似度 #

该方案直接统计原字符串中相邻字符对（如Pa、au、ul）在模糊字符串中按顺序出现的数量，更贴近你例子中"a紧跟P、u紧跟a"的匹配逻辑。

公式实现 #

=LET(
    orig, $A$1,
    fuzz, Table1[@Fuzzstring],
    orig_pairs, MID(orig, SEQUENCE(LEN(orig)-1), 2),
    pair_matches, BYROW(orig_pairs, LAMBDA(pair,
        LET(
            first_char, LEFT(pair,1),
            second_char, RIGHT(pair,1),
            first_pos, SEARCH(first_char, fuzz),
            IFERROR(ISNUMBER(SEARCH(second_char, fuzz, first_pos+1)), FALSE)
        )
    )),
    matched_pairs, SUM(--pair_matches),
    similarity_score, matched_pairs / (LEN(orig)-1),
    similarity_score
)

公式说明 #

• orig_pairs：生成原字符串的所有相邻字符对（如Paul生成{"Pa","au","ul"}）
• pair_matches：对每个字符对，检查第一个字符在模糊字符串中的位置之后是否存在第二个字符（保证顺序）
• similarity_score：用匹配的字符对数量除以总字符对数量，得到相似度得分

示例验证 #

• 原字符串Paul vs 模糊字符串JoPaul：3个字符对全部匹配，得分1
• 原字符串Paul vs 模糊字符串JPuao：仅Pa匹配，得分1/3≈0.33

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