|
Helen来信提出了一个有关Vlookup的问题——
我觉得这个问题很有代表性，也很有趣，因为这对于函数的运用是一次很好的思维锻炼。于是，发了一条微博让大家参与讨论http://weibo.com/1426389024/zDpBkEKrz。
得到了一些想法，如透视表、数组公式、BI、先排序……
我的想法却有些不同，其实，只要换一种思维，就能抓住这题的小尾巴。那些技巧你可能都会，但能不能想得到，串得起来，就靠经验和创新了。
核心思路有三点：
1、得把每个介绍人对应的业务员匹配出来，由于Vlookup只能一对一匹配，在天然不存在唯一的情况下，需要制造唯一；
2、匹配出来的业务员应该放在同一行，而不是同一列，这样，才能方便用公式合并单元格；
3、以特制的序号标签帮助Vlookup实现匹配。
最后，合并单元格就能得到最终效果。
第一步——制造唯一
制造唯一是Vlookup的经典扩展用法，主要会用到countif动态计数以及&符号。这在前面的博文中已经分享过，详见《Vlookup一对多匹配》：http://blog.sina.com.cn/s/blog_5504f82001016lo1.html
第二步——设计表格样式（同一行与序号标签）
设计出同一介绍人对应的不同业务员在同一行的表格样式，并特制序号标签，用于与“介绍人”合并成为唯一的查找条件，如“许新民1”、“程红艺9”……
在用于查找的数据区域中添加辅助列，将A列“介绍人”与C列“序号”合并起来作为第二参数首列。公式为=B1&D1。在H3单元格写公式=VLOOKUP($G3&H$2,$A:$C,3,0)，需要注意Vlookup函数第一参数的引用类型，公式向右向下复制后，“介绍人”应该行变列不变，所以锁定列，“序号”应该列变行不变，所以锁定行。而第二参数的引用规范是，在99.9999%的情况下，Vlookup引用的用于查找的数据区域，也就是第二参数，全都应该是“绝对引用”。因为，无论公式复制到哪里，都在这个固定的数据区域中进行查找。
第三步——公式升级（去除错误值）
由于不知道同一个介绍人会介绍几个业务员，所以，特制的序号标签有可能预设10个以上，甚至更多。而每个介绍人介绍的业务员数量又是不同的，就会出现经典的Vlookup错误值#N/A（如上图）。有它存在，在单元格合并时就将合并到错误值，而偏离预想的呈现效果。幸好，2010版出现了一个新函数IFERROR，可以轻松的替换Vlookup错误值，而不用像以前那般需要写很长的IF函数。IFERROR的公式是=IFERROR(VLOOKUP($G3&H$2,$A:$C,3,0),"")，第一参数代表一个运算，第二参数代表“如果第一参数的运算结果为错误值时所显示的由我们自己设定的值”。这里，可以设定为""，也就是“空”。
第四步——合并单元格
神奇的&符号又来了，用它就能将单元格合并成各种想要的样子，例如在两段内容之间用顿号隔开=I3&"、"&J3。
好了，完成了。当G列有介绍人时，H列就会将属于他的业务员全体呈现。
不过，如果仔细观察，合并单元格这个环节是有问题的，关键就是多出来的那些顿号。正好，这就变成一道新的题目。
|
