这组Excel函数你再爱答不理，可能就高攀不起了​

---

大家都知道极值函数MAX和MIN以及大小函数LARGE和SMALL，可是竟然很少有人使用。

这两组能量满满的函数，你要是再爱答不理，很有可能就高攀不起啦。

最值与大小

一

在浮动分值有上下限的条件下计算得分

现在一组销售人员业务完成率的数据，需要根据公司的政策来计算各销售人员销售KPI考核得分。

公司政策：基准分5分，完成率每超过目标1%加0.15分，每相差1%减0.12分，销售KPI最高得6分，最低得4分。

计算方式：

①利用IF函数根据目标完成率和实际完成率的差距计算销售得分，在D2单元格输入公式=IF(B2>=C2,5+(B2-C2)/1%*0.15,5+(B2-C2)/1%*0.12)；

②利用MAX和MIN函数的特点在E2单元格输入公式=MAX(MIN(D2,6),4)，即可完成销售KPI的测算。

图：最值函数的应用

二

计算前三名总销售额

现在一组销售数据，需要计算前三名销售人员的总销售额。

计算方法：在D3单元格输入公式=SUM(LARGE(B2:B10,{1,2,3}))，按Ctrl+Sheet+Enter三键结束运算。

其中，LARGE(B2:B10,{1,2,3})表示在数据区域B2:B10中找出第1、第2、第3的三个数字。

图：前三名求和

三

按条件排序

现在一组销售数据，需要将100以上的销售额降序排序，其余不显示。

计算方法：

①在C2单元格输=LARGE(IF($B$2:$B$10>100,$B$2:$B$10),ROW(A1))，按Ctrl+Sheet+Enter三键结束运算。

②在D2输入公式=IFERROR(C2,'')屏蔽C列中的错误值。

图：大于100的销售额降序排列

四

一对多查找

在4.4章节我们掌握了COUNTIF帮助VLOOKUP实现一对多查找的方法，在这一小节内容中我们学习如何使用INDEX和SMALL函数嵌套来完成一对多查找。

现在一组生源地和学生姓名数据，需要根据提供的生源地提取学生名单。

计算方式：在D5单元格输入公式=INDEX($B:$B,SMALL(IF($A$2:$A$10=$D$2,

ROW($A$2:$A$10),4^8),COLUMN(A1)))&''，按Ctrl+Sheet+Enter三键结束运算，向右拖动公式即可完成同一生源地学生名单的查找。

其中，利用SMALL函数来定位所有D2在第一列的位置，COLUMN(A1)用来显示第几个D2，这样在拖动D5单元格填充柄往右填充公式时，在D5时为COLUMN(A1)即1，第一个D2；E2时为COLUMN(B1)即2，第二个D2，以此类推。

在这个公式末尾，添加&''，是为了实现公式在向右拖动的过程中如果没有匹配值就用空格代替。

图：INDEX与SMALL函数嵌套实现一对多查找

如果觉得文章对您有用，请记得随意赞赏或者随手点广告支持我们哦。