|
小伙伴们，你们还好吗？我听有些人说这段时间在家呆的像坐牢一样，不知道你是什么感觉。这几天家里没网，我也没有准备一些好的问题。那就来讲一些基础函数的用法吧，今天要说的是small函数，它是返回数组中的第k个最小值。 -01- 函数说明 下面来看下它的语法结构，有2个参数。 SMALL(array,k)
注意事项： 1.如果array为空，则SMALL返回错误值#NUM!。 2.如果k≤0或k超过了数据点个数，则SMALL返回错误值 #NUM!。 3.如果n为数组中的数据点个数，则SMALL(array,1)等于最小值，SMALL(array,n) 等于最大值。所以small既能取最小值，也能取最大值。 -02- 示例解释 如下图所示，在D2单元格输入公式=SMALL(A2:A7,2)，返回的结果是3。也就是返回A2:A7这个区域中的第2个最小值。 输入下图所示的公式，结果为错误值。为什么？此时是返回A2:A7这个区域中的第8个最小值，但是这个区域中一共才6个数字，所以返回错误值。这也符合注意事项的第2点。 输入下图所示的公式，结果返回9。此时是返回A2:A7中的第6个最小值，而A2:A7中数字的个数也是6，其实就返回了这个区域的最大值。符合注意事项的第3点。利用这一特点，可以用small实现条件求最大值。 输入下图所示的公式，返回的结果为2。也就是返回B2:B6中第1个最小值。说明small可以忽略文本，逻辑值和空单元格，只对数值起作用。 输入下图所示的公式，small返回的结果为错误值，说明它不能忽略错误值。 通过这几个示例说明，相信大家对这个函数有一定的了解了。下面就来说几个具体的应用。 -03- 具体应用 1.求最后3名的总分 下图是一个成绩表，求最后3名的总分，这里的分数没有相同的。其实就是用small取出第1，2，3个最小值，然后用sum求和。在E10单元格输入公式=SUM(SMALL(B11:B20,ROW(1:3)))，按ctrl+shift+enter三键完成。 除了这种方法，也可以用sumif来完成，在E11单元格输入公式=SUMIF(B11:B20,"<="&SMALL(B11:B20,3))，完成。首先用small求出第3个最小值19，然后用sumif求出小于等于19的总和。 2.求小于60分的最大值 在小于60分的那些分数中，最大的是多少？这是个条件求最大值的问题，通常的做法是用max+if，如下图所示，公式为=MAX(IF(B24:B33<60,B24:B33))，按三键完成。 IF(B24:B33<60,B24:B33)这部分返回的结果如下图C列所示，也就是判断B列的分数是否小于60，如果成立，还是返回它本身，比如9小于60，还是返回9；如果不成立返回false，比如92不小于60，返回false。这样的话就相当于把大于等于60分的排除出去了，然后用max取最大值。这种方法不是我们今天主要讨论的。 来看下small函数的用法，公式为=SMALL(B24:B33,COUNTIF(B24:B33,"<60"))。首先用countif计算出小于60分的有7个，然后用small取第7个最小值就是小于60分的最大值。大家可以参考上图理解一下。上图绿色标记的就是小于60分的，共有7个。用small取出第7个最小值正好就是小于60分的最大值。 3.求销售部员工的最大年龄 下图是一个员工信息表，在销售部员工的年龄中，最大年龄是多少。同样是个条件求最大值的问题，用max来完成的话，公式为=MAX(IF(B37:B46="销售部",C37:C46))，按三键结束。如果用small来完成，公式为=SMALL(IF(B37:B46="销售部",C37:C46),COUNTIF(B37:B46,"销售部"))，按三键完成。 small的第1参数IF(B37:B46="销售部",C37:C46)返回的结果如下图D列所示，判断B列的部门是否为销售部，是的话返回C列对应的年龄；不是的话返回false。这样的话就把不是销售部的排除出去。第2参数COUNTIF(B37:B46,"销售部")是统计B列的部门中销售部的个数，结果是4。然后用small返回第1参数中第4个最小值32，就是销售部员工的最大年龄。 其实，能实现条件求最大值/最小值的函数有很多，比如max，min，small，large，maxifs，minifs，aggregate，frequency。有机会的话，可以做一期汇总。大家对这个问题感兴趣的话，也可以下载文件查看其中的函数公式。 文件链接： https://pan.baidu.com/s/1hzRsmIRU3Y5LUuOBlRG4AA 提取码：dbe2 |
|
|
