模糊 匹配 近似 查找 中文 模糊 匹配 excel 函数

模糊 匹配 近似 查找 中文 模糊 匹配 excel 函数 模糊 匹配 近似 查找 中文 模糊 匹配 excel 函数 此网上看到的，不会用，学习一下: matchorvlookup不能用例如匹配 山东大学(兴隆校区)山东大学兴隆校区 (vlookup/match/hlookup/lookup等查找函数的近似匹配其实只是按找类 似排序功能查找就近的匹配，并不是真的去近似模糊匹配) 第一步截取字符串例如A2=sheet1 一最省事的 {"s","h","e","e","t","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""} mid($A2,column(A:HZ),1)很不准确 二{"s","h","e","e","t","1"}嵌套已经很多了，一般需要定义名称 fxa=MID($A1,TRANSPOSE(ROW(INDIRECT("1:"&LEN($A1)))),1) 三:={"s","h","e","e","t","1","$s","sh","he","ee","et","t1","1$","$sh","she","hee","eet","et1","t1$","1$s","$she","shee","heet","eet1","et1$","t1$s","1$sh","$shee","sheet","heet1","eet1$","et1$s","t1$sh","1$she","$sheet","sheet1","heet1$","eet1$s","et1$sh","t1$she","1$shee","$sheet1","sheet1$","heet1$","eet1$","et1$","t1$","1$","$"}会比较慢 其中$符号示没用，目的是将原字符串分割成不同的长度。 fxb=MID(REPT(!$A1&"$",LEN(!$A1&"$")),TRANSPOSE(ROW(INDIRECT("1:"&LEN(REPT(!$A1&"$",LEN(!$A1&"$")))))),INT(TRANSPOSE(ROW(INDIRECT("1:"&LEN(REPT(!$A1&"$",LEN(!$A1&"$"))))))/LEN(!$A1&"$"))+1) 第二步主要运用mode函数 =INDEX(Sheet1!A:A,MODE(IF(ISNUMBER(FIND(fx,Sheet1!$A:$A3)),ROW($A:$A3)))) fx(分割字)一般采用第二种，如果数量较少的话可以采用第三种更精确 第三部查找匹配程度，主要就是计算查找结果与要查找的有多少匹配 =count(find(fx,$a1))按降序排列一般会基本得到较好的结果 MSN空间完美搬家到新浪博客!