Excel028-快速找到第N个最大值就这么简单-LARGE_※布衣公子

http://blog.sina.com.cn/u/1093804793

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

Excel028-快速找到第N个最大值就这么简单-LARGE

(2017-07-12 14:42:48)

标签：

布衣公子

职场提升

excel

https://v.qq.com/x/page/c0524ugomht.html

▲本期分享技能请观看

http://s5/mw690/001c1uANzy7cAegdcmE84&690

前两天有个做HR的朋友紧急求助问我怎么将员工考核成绩表快速变形成下图的格式（为了方便打印公示）。要求按照成绩从大到小的顺序列出各部门员工姓名+成绩。

http://s7/mw690/001c1uANzy7cAegbQXk16&690

我一开始想到的是先按部门排序，再按成绩排序，然后手动粘贴对应部门的员工姓名和成绩，可是这样有点太劳师动众了。而且一旦成绩表有所调整，这项工作又要重新来一遍，岂不是要吐血。

后来我脑洞一开，尝试了一下LARGE函数，还真让我瞎猫碰上死耗子给折腾出来了，需要的小伙伴赶紧来围观。

http://s1/mw690/001c1uANzy7cAegcBqwe0&690

先看看LARGE函数的解析，2个参数：Large(array,k)

① Array：需要找到第 k 个最大值的数组或单元格区域。

② k：要返回的数据在数组或单元格区域中的位置（从大到小）。

友情提示：（详细解释请观看本期视频）

① 若 array 为空，则 LARGE 返回错误值 #NUM!。

② 若k ≤ 0 或 k 超过了数据点个数，则 LARGE 返回错误值 #NUM!。

③ 若 n 为数组中的数据点个数，则 LARGE(array,1) 等于最大值，LARGE(array,n) 等于最小值。

http://s2/mw690/001c1uANzy7cAegic9P41&690

抓紧来看看怎样实现成绩表的变形吧。

案例：按照部门将员工姓名和成绩按照从大到小的顺序依次排列。（将左边的表格变形为右侧的格式）

http://s8/mw690/001c1uANzy7cAelMw1Ff7&690

看起来很复杂的事情其实一个公式就解能解决，具体方法如下：

① 在I1:M1区域录入所需部门名称。

② 添加辅助列E列，命名为成绩2（辅助列）。

③ 添加辅助列F列，命名为姓名2（辅助列）。

④ 在E2单元格录入公式：=C2*10^4+ROW()，下拉填充公式。这里将原来的成绩扩大1万倍并加上行号，确保每个成绩都是独一无二的，并且不会影响各成绩之间的大小关系，这样我们通过成绩用VLOOKUP函数查询姓名的时候才能正确找出其对应的姓名。

⑤ 在F2单元格录入公式：=A2，下拉填充公式。F列的员工姓名等于A列的员工姓名。由于我们还没有学过VLOOKUP的逆向查询，在此添加姓名的辅助列通过E列的成绩用VLOOKUP函数获得F列的员工姓名。

如下图所示：

http://s12/mw690/001c1uANzy7cAelL0Cv6b&690

⑥ 在I2单元格录入公式：=IFERROR(VLOOKUP(LARGE(IF($B$2:$B$47=I$1,$E$2:$E$47,""),ROW(1:1)),$E:$F,2,0)&" ("&LARGE(IF($B$2:$B$47=I$1,$C$2:$C$47,""),ROW(1:1))&") ","")，CTRL+SHIFT+ENTER三键结束，向下向右填充公式即可。

公式解析：（文字描述毕竟有限，视频解释更加详尽哦！）

① IF($B$2:$B$47=I$1,$E$2:$E$47,"")用来表示如果$B$2:$B$47区域有等于监察部的，就显示$E$2:$E$47的成绩，否则就显示为空。结果如下：

{960002;"";"";"";"";"";"";560009;"";"";"";590013;"";970015;620016;"";"";"";"";710021;"";730023;"";710025;790026;"";"";"";790030;"";"";"";"";510035;"";"";"";530039;"";"";"";"";"";"";"";""}

② ROW(1:1)用来创建动态的数组，公式下拉后会动态显示1,2,3,4,5……用来作为LARGE函数的第二参数。

③ LARGE(IF($B$2:$B$47=I$1,$E$2:$E$47,""),ROW(1:1))用来找到监察部【成绩2】那一列第1个、第2个……第N个最大值。

④ VLOOKUP 查询函数通过E列的成绩，查询F列的姓名。想了解VLOOKUP的使用方法请参见往期教程：Excel012-Vlookup不为人知的秘密。

⑤ LARGE(IF($B$2:$B$47=I$1,$c$2:$c$47,""),ROW(1:1))用来找到监察部【成绩】那一列第1个、第2个……第N个最大值。

⑥ & 是连接符号，用来连接员工姓名与员工成绩。

⑦ iferror是容错函数，当LARGE函数的第二参数超过了各部门员工个数，则 LARGE返回错误值 #NUM!，此时我们将错误值处理成为空。

http://s4/mw690/001c1uANzy7cAelG5gL63&690

今天的内容就分享到这里，非常感谢大家一直以来对布衣公子的关注和支持！如果您有任何PPT和Excel技能方面的问题，您可以给公子留言，有可能您的问题就会在下一个视频中出现！

本篇文章匹配的相关PPT、视频、Excel演示课件及WORD教程下载：

https://pan.baidu.com/s/1o8Bf7my

往期教程：

Excel027-快速找到第N个最小值就这么容易-SMALL