Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 1981|回复: 9

[已解决]LOOKUP关键字求助

[复制链接]
发表于 2017-8-10 18:03 | 显示全部楼层 |阅读模式
各位大神,A列是我用LOOKUP公式编制的查找B列含有关键字提取,但是如果B列包含A列2个以上关键字只会提取其中一个,请问怎么样能够逐一把所有的关键字进行提取?万分感谢各位。
已上附件。
最佳答案
2017-8-11 05:38
函数方法,供参考
D2=IFERROR(MID($B2,SMALL(IF(NOT(ISERROR(FIND({"环境","气氛","干净","整洁","舒适","宽敞","照明","友好","态度","耐心","积极","礼貌","放松","热情","诚实","信任","责任","微笑","专业","温柔","印象","服务"},$B2))),FIND({"环境","气氛","干净","整洁","舒适","宽敞","照明","友好","态度","耐心","积极","礼貌","放松","热情","诚实","信任","责任","微笑","专业","温柔","印象","服务"},$B2),"/"),COLUMN(A1)),2),"")   数组执行后向右向下复制到Y1968


D:Y列表示在B列内包含的去除重复后的字段。我故意在B2添加了5次 “微笑”,D:Y只显示一次。

求助.rar

160.59 KB, 下载次数: 6

发表于 2017-8-10 21:39 | 显示全部楼层
  1. Function test(str As String) As String
  2.     Dim arr, i As Integer, smStr As String
  3.     arr = Array("环境", "气氛", "干净", "整洁", "舒适", "宽敞", "照明", "友好", "态度", "耐心", "积极", "礼貌", "放松", "热情", "诚实", "信任", "责任", "微笑", "专业", "温柔", "印象", "服务")
  4.     For i = 0 To UBound(arr)
  5.         If InStr(str, arr(i)) Then smStr = smStr & "、" & arr(i)
  6.     Next
  7.     test = Mid(smStr, 2)
  8. End Function
复制代码

用自定义函数吧
回复

使用道具 举报

发表于 2017-8-11 05:38 | 显示全部楼层    本楼为最佳答案   
函数方法,供参考
D2=IFERROR(MID($B2,SMALL(IF(NOT(ISERROR(FIND({"环境","气氛","干净","整洁","舒适","宽敞","照明","友好","态度","耐心","积极","礼貌","放松","热情","诚实","信任","责任","微笑","专业","温柔","印象","服务"},$B2))),FIND({"环境","气氛","干净","整洁","舒适","宽敞","照明","友好","态度","耐心","积极","礼貌","放松","热情","诚实","信任","责任","微笑","专业","温柔","印象","服务"},$B2),"/"),COLUMN(A1)),2),"")   数组执行后向右向下复制到Y1968


D:Y列表示在B列内包含的去除重复后的字段。我故意在B2添加了5次 “微笑”,D:Y只显示一次。
回复

使用道具 举报

 楼主| 发表于 2017-8-11 10:43 | 显示全部楼层
cabcyvr 发表于 2017-8-11 05:38
函数方法,供参考
D2=IFERROR(MID($B2,SMALL(IF(NOT(ISERROR(FIND({"环境","气氛","干净","整洁","舒适"," ...

这个冒似可行,我试试看。谢谢!
回复

使用道具 举报

 楼主| 发表于 2017-8-11 11:13 | 显示全部楼层
cabcyvr 发表于 2017-8-11 05:38
函数方法,供参考
D2=IFERROR(MID($B2,SMALL(IF(NOT(ISERROR(FIND({"环境","气氛","干净","整洁","舒适"," ...

还有一个疑问,假如我的关键字是3-6个字,但是Mid截取6位会出现截取多了的情况,请问怎么解决?
回复

使用道具 举报

 楼主| 发表于 2017-8-11 11:16 | 显示全部楼层
Wynne1986 发表于 2017-8-11 11:13
还有一个疑问,假如我的关键字是3-6个字,但是Mid截取6位会出现截取多了的情况,请问怎么解决?

如附件。

求助.zip

241.13 KB, 下载次数: 5

回复

使用道具 举报

发表于 2017-8-11 13:36 | 显示全部楼层

因为之前的评语关键字全部为2位,所以简单一些,如果出现长度不一致的情况,稍微麻烦一些,因为你排在条件里面的前面的关键字可能会出现在最后,并且长度不一致。下面的公式已经修修改过。评语长度可以不一致,出现的顺序可以和条件中的顺序不一致。

原理是先按照原先关键字描述中的所有字节进行搜索,这时候即使找到了也不能确认出现的先后顺序,只是临时截取2位作为第二次的搜索条件,再将关键字描述作为第二次的搜索基准用MATCH将第一次截取的2位字符加通配符在关键字描述中进行第二次模糊搜索,找到了就把所有长度的描述提取出来 (我都不知道我在说什么了,,,,希望你能看明白)
在你的新文件的E2复制下列函数,数组执行后向右向下复制

E2=IFERROR(INDEX({"环境好","氛围好","气氛好","干净","整洁","舒适","安静","宽敞","明亮","服务好","态度好","位置舒适","试衣间舒适","友好","细心","耐心","礼貌","放松","平静","积极","热情","激情","有责任心","微笑真诚","不出风头","声音温柔","制服干净","充满能量","印象好","满足我的需求","了解我的需求"},MATCH(MID($D2,SMALL(IF(ISERROR(FIND({"环境好","氛围好","气氛好","干净","整洁","舒适","安静","宽敞","明亮","服务好","态度好","位置舒适","试衣间舒适","友好","细心","耐心","礼貌","放松","平静","积极","热情","激情","有责任心","微笑真诚","不出风头","声音温柔","制服干净","充满能量","印象好","满足我的需求","了解我的需求"},$D2)),"/",FIND({"环境好","氛围好","气氛好","干净","整洁","舒适","安静","宽敞","明亮","服务好","态度好","位置舒适","试衣间舒适","友好","细心","耐心","礼貌","放松","平静","积极","热情","激情","有责任心","微笑真诚","不出风头","声音温柔","制服干净","充满能量","印象好","满足我的需求","了解我的需求"},$D2)),COLUMN(A1)),2)&"*",{"环境好","氛围好","气氛好","干净","整洁","舒适","安静","宽敞","明亮","服务好","态度好","位置舒适","试衣间舒适","友好","细心","耐心","礼貌","放松","平静","积极","热情","激情","有责任心","微笑真诚","不出风头","声音温柔","制服干净","充满能量","印象好","满足我的需求","了解我的需求"},)),"")
回复

使用道具 举报

发表于 2017-8-11 15:02 | 显示全部楼层
才看到你的文件中的第二个工作簿”sheet1",如果你之前的公式中的关键字描述来自sheet1的B1:B31,你完全没有必要去通过选择范围再用F9 之类的手动制作一个数组关键字描述范围。之前以为你是手动打进去的关键字。

在第一个工作簿的E2直接用下面的公式就好了,否则那个公式的长把周围的蚊子都吓跑了。。。。
"GZ-HK"工作簿
E2=IFERROR(INDEX(Sheet1!$B:$B,SMALL(IF(ISERROR(SEARCH(Sheet1!$B$1:$B$31,$D2)),"/",ROW(Sheet1!$B$1:$B$31)),COLUMN(A1))),"")    数组执行后向右向下复制,

效果和之前那个一样。有重复的只会显示一次。
回复

使用道具 举报

 楼主| 发表于 2017-8-11 22:28 | 显示全部楼层
cabcyvr 发表于 2017-8-11 15:02
才看到你的文件中的第二个工作簿”sheet1",如果你之前的公式中的关键字描述来自sheet1的B1:B31,你完全没 ...

明白,不过好像我复制你的公式上去有一些问题,导致显示不出任何东西。

求助.zip

183.98 KB, 下载次数: 0

回复

使用道具 举报

 楼主| 发表于 2017-8-11 22:29 | 显示全部楼层
cabcyvr 发表于 2017-8-11 15:02
才看到你的文件中的第二个工作簿”sheet1",如果你之前的公式中的关键字描述来自sheet1的B1:B31,你完全没 ...

我知道了,原来我没有按三键。谢谢了
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 23:28 , Processed in 0.486253 second(s), 10 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表