|
原创作者 | 李锐 微信公众号 | Excel函数与公式（ID：ExcelLiRui） 个人微信号 | （ID：ExcelLiRui520） VLOOKUP按职位排序 今天再来讲个VLOOKUP函数的实战应用案例，除了数据查找，VLOOKUP还可以帮助我们实现数据自定义排序的问题。 比如按职位排序，按产品名称排序，或者按区域位置排序等Excel内置功能无法实现的功能。 今天要讲的就是VLOOKUP按职位排序的案例，看完觉得好的，记得去底部点个好看再分享给朋友，我会根据大家的反馈调整发文内容及写法。 案例描述 下图所示为某企业的会议记录表，由于每次参会人员的通知和登记环节都是乱序排列，如下图左侧报表所示，包含参会人员姓名及职级。 而在会议的参会名单确定后，发布公告时需要按照职级从高到低的顺序展示参会人员姓名及职级，如下图右侧黄色区域所示。 下图表格中J列为该企业的职级顺序以及职级名称。 当参会人员较多时，手动整理参会人员职级顺序和名称非常麻烦，需要先将所有人员按照职级排列，这个职级顺序本就不在Excel内置功能，还需要用到自定义序列编辑，然后同一个职级的人员再按报名先后顺序排列...... 明确了计算规则，现在企业召开的会议十分繁多，需要整理和计算量很大，不可能再按以往的笨办法一次次手动计算，现在要求你根据左侧的原始表格以及企业规定的职级顺序，在黄色区域批量实现自动排列，这应该怎么做呢？ 为了方便你理解效果，并梳理思路，下面先看下效果演示。 效果演示 实际工作中左侧的数据源中，无论是姓名还是职级都可能变动。 无论数据源怎样变动，右侧的黄色公式区域返回的结果，都要符合上一小节中要求的所有算法。 下图是写好公式以后的演示效果 （下图为数据准备完毕后效果） 可见数据源变动后，参会姓名和职级名称都随之同步更新，职级高的往上排，职级低的往下排，同一职级的多名参会人员按报名先后顺序排列。 在看下面的解决方案之前，请你先独立思考，带着思路和问题继续向下看。 解决方案 思路提示：解决这个问题的关键点有两个，一是将职级按高低顺序排序，二是在VLOOKUP的参数中构建条件区域按要求查找对应的数据。 第一个关键点的目的是将最高职级所在行号放置在最前面，职级越低行号放置越靠后，要用到多权重关键字排序技术；第二个关键点目的是能按职级高低顺序排列好的行号提取姓名，这样姓名也就按职级高低排好序了，这里要用到内存数组构建技术。 根据思路构建选择合适方法，在Excel中落地执行计算，可以用下面一个公式搞定上述所有要求。 G2单元格输入如下数组公式，按ctrl+shift+enter三键输入： =VLOOKUP(MOD(SMALL(MATCH($D$2:$D$19,$J$2:$J$13,)*1000+ROW($D$2:$D$19),ROW(1:1)),100),IF({1,0},ROW($D$2:$D$19),$C$2:$C$19),2,) 如下图所示。 （下图为公式示意图） 一句话解析： 先利用MATCH函数和ROW函数组合将左侧参会人员按照J列的职级顺序加权，然后用SMALL函数将其从小到大排列，再用MOD函数除权，提取到数据所在的行号，这样完成了第一个关键点的实现，这也是VLOOKUP函数的第一参数，按此查找； 再用IF函数构建内存数组，作为VLOOKUP函数的第二参数，在此内存数组中查找按职级排好序的行号，返回对应职级从高到低的姓名。 这里用到的所有思路和组合技术，在我已经开课的Excel特训营中都有超清视频讲解，这个数组公式需按下ctrl+shift+enter三键输入，关于数组公式和内存数组的系统讲解在函数中级班。 |
|
|
