在Excel中使用模糊查找进行字符串匹配

String Match using Fuzzy Lookup in Excel

提问人:Chitwan 提问时间:7/12/2023 最后编辑:Chitwan 更新时间:7/13/2023 访问量:38

问:

我正在尝试使用模糊查找来匹配如下所示的表的两列中的两个字符串。 表1 表2

| Column A     | Column B |
| --------     | -------- | 
| Flower.com   | Flower   | 
| Flower.com   | snacks   | 
| Flower.com   | food     |

When I apply Fuzzy lookup to the above tables I get false positive. In the sense I get a similarity score of 0.987 for all three rows.
Table1             Table2  

| Column A     | Column B | Similarity Score | 
| --------     | -------- | ---------------| 
| Flower.com   | Flower   | 0.987 | 
| Flower.com   | snacks   | 0.987 | 
| Flower.com   | food     | 0.987 |

Which is not making any sense. I have tried applying threshold between 0 to 0.8 but the similarity score remains same. Why is that so? I want it to look like below
Table1             Table2  

| Column A     | Column B | Similarity Score | 
| --------     | -------- | ---------------| 
| Flower.com   | Flower   | 0.987 | 
| Flower.com   | snacks   | 0 | 
| Flower.com   | food     | 0.0 |

Is there another way to match the two strings in excel?

I have tried fuzzy lookup addin provided by Excel
excel nlp 搜索模糊 比较 句子相似度

评论

2赞 JvdV 7/12/2023
您能更深入地解释一下您希望如何实际计算这个相似度分数吗?我的意思是,你是怎么得到 0.987 的?
1赞 Skin 7/12/2023
在我看来,使用 Levenshtein 距离算法对您来说是有益的,但您不会在 Excel 中找到一段可以为您做到这一点的代码。您可以从 Excel 调用 API,但对您来说可能太多了,不确定。

答: 暂无答案