谷歌表格上的模糊匹配 [英] Fuzzy match on google sheets

问题描述

我试图模糊匹配谷歌表格中的两列，我尝试了很多公式，但我认为这将归结为一个脚本来提供帮助.

我有一列包含产品 ID，例如

• E20067

然后我有另一张表，其中包含与此产品代码相关的图像网址，例如

• 有没有办法在谷歌表格中使用脚本或公式来做到这一点?

  解决方案

  旧线程，但如果您发现自己在这里，请搜索我的名为 Flookup 的 Google 表格插件.它应该完全符合您的要求.

  对于这种情况，你可以使用这个函数:

  Flookup (lookupValue, tableArray, lookupCol, indexNum, threshold, [rank])

  参数详情为:

  1. lookupValue:您要查找的值
  2. tableArray:要搜索的表
  3. lookupCol:要搜索的列
  4. indexNum:要返回数据的列
  5. threshold:低于该百分比的相似度，不应返回数据
  6. rank:第 n 个最佳匹配(即如果第一个不符合您的喜好)

  *您可以在官方网站(示例等)上了解更多信息.

  请注意，虽然 OP 似乎想要完整的可能匹配列表，但 Flookup 一次只会返回一个结果.

  I'm trying to fuzzy match two columns in google sheets, i've tried numerous formulas but I think it's going to come down to a script to help out.

  I have a column with product ID's e.g.

  • E20067

  and then I have another sheet with another column which has image url's relating to this product code such as

  • http://wholesale.test.com/product/E20067/web_images/E20067.jpg
  • http://wholesale.test.com/product/E20067/high_res/E20067.jpg
  • http://wholesale.test.com/product/E20067/high_res/E20067-2.jpg

  What I'm wanting to do is "fuzzy" match both of these columns for their product ID, and then create a new column for each match. So it would have the product ID then on the same row in multiple columns each product image URL - like the image below:

  Is there a way to do this in google sheets using a script or a formula?

  解决方案

  Old thread but, in case you find yourself here, search for my Google Sheets add-on called Flookup. It should do exactly what you want.

  For this case, you can use this function:

  Flookup (lookupValue, tableArray, lookupCol, indexNum, threshold, [rank])
  

  The parameter details are:

  1. lookupValue: the value you're looking up
  2. tableArray: the table you want to search
  3. lookupCol: the column you want to search
  4. indexNum: the column you want data to be returned from
  5. threshold: the percentage similarity below which data shouldn't be returned
  6. rank: the nth best match (i.e. if the first one isn't to your liking)

  *You can find out more at the official website (examples and such).

  Please note that, whereas the OP appears to want the whole list of possible matches, Flookup will only return one result at a time.

  这篇关于谷歌表格上的模糊匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！