如何使用 Excel 中的公式将一个表中的文本字符串变体与另一个表中这些文本字符串的标准化版本进行匹配?

如何使用 Excel 中的公式将一个表中的文本字符串变体与另一个表中这些文本字符串的标准化版本进行匹配?

我有一张包含课程名称的表格;但是,这些名称的输入并不一致。这导致同一课程名称有多种变体(请参阅下面的变体表格)。例如,“Learning to Sew”、“Learning Sewing”和“Learn to Sew”。我还有一个包含这些课程标准名称的表格(请参阅下面的标准名称)。我想将变体与标准名称匹配,因此标准名称出现在变体名称旁边的列中(请参阅结果表格)。更复杂的是,一个特定的课程名称已更改,因此任何搜索都需要查找两个版本并返回当前版本(请参阅下面的体验生物学)。我能够使用 COUNTIF 来计算每个标准名称在变体中的出现次数,搜索词包含名称的关键词,并在需要时结合通配符和多个条件。但是,我无法弄清楚如何进行匹配。Excel 中是否有可以实现此目的的公式?

笔记:

  • 拼写错误并不值得担心。
  • 有些类没有标准名称,可以忽略。

变化

Class
Learning to Sew
Learning Sewing
Learn to Sew
Experiencing Biology
Cycles of Biology   

标准名称

Class                   Search Term 1             Search Term 2        Count
Learning to Sew         *Learn*Sew*                                    3
Experiencing Biology    *Experiencing*Biology*    *Cycles*Biology*     2

结果

Class                  Standard Title
Learning to Sew        Learning to Sew
Learning Sewing        Learning to Sew
Learn to Sew           Learning to Sew
Experiencing Biology   Experiencing Biology 
Cycles of Biology      Experiencing Biology

答案1

我创建了两个命名表(按 Ctrl+T 然后在“表设计”选项卡上命名它们):

  1. 变体(包含变体列表)
  2. 标准(包含两个标准名称)

在此处输入图片描述

在每个表上,我使用“数据”>“获取和转换数据”>“从表/范围”来创建查询。在 Power Query 编辑器中,我使用“主页”>“关闭和加载”>“关闭和加载到”>“仅创建连接”,这样我就有两个查询连接,每个表一个:

在此处输入图片描述

然后我使用数据>获取和转换数据>获取数据>合并查询>合并并配置合并对话框如下:

在此处输入图片描述

根据其余数据,您可能需要试验相似度阈值的最佳值。如果您发现得到的是误报,您还可以尝试使用转换表来简化工作。

在合并时单击“确定”后,使用列顶部的双箭头(每行带有单词“表格”)并展开合并的数据,我得到了以下结果:

在此处输入图片描述

要将这个结果数据集放回工作簿,我只需使用“主页”>“关闭并加载”。

这并非万无一失,但使用模糊匹配中的正确参数,您可能会找到适用于整个数据集的方法。

相关内容