涨知识了，还能这样条件求最大值！

下图A2:C6是一个二维区域，这个区域中有正数、0、负数，还有文本。求这个区域中小于等于100的最大值是多少。结果是99.85，如E2单元格所示。

这是个条件求最大值的问题，你会怎么解呢？我相信很多小伙伴想到的是下面第1种解法。

第1种，max+if

在E2单元格输入公式=MAX(IF(A2:C6<=100,A2:C6))，按ctrl+shift+enter三键。

IF(A2:C6<=100,A2:C6)这部分的意思是如果A2:C6中的值小于等于100，返回它本身，否则大于100，返回false。

对应关系如下图所示，其中①是if函数的条件部分，也就是第1参数返回的结果。②是if函数最后返回的结果。

excel中，文本是大于数字的，比如="1">9E+307是成立的。所以文本小于等于100肯定是不成立的，返回false。

最终if函数将文本和大于100的数字返回false，将小于等于100的数字返回原来的值，如对应图②所示。最后用max从if函数返回的结果中取出最大值。

第2种，small+countif

在E2单元格输入公式=SMALL(A2:C6,COUNTIF(A2:C6,"<=100"))。

COUNTIF(A2:C6,"<=100")这部分用countif统计出A2:C6这个区域中小于等于100的单元格个数，结果为9。也就是小于等于100的有9个数字。

SMALL(A2:C6,COUNTIF(A2:C6,"<=100"))这部分用small从A2:C6这个区域中提取第9个最小值，刚好就是小于等于100的最大值。可以想一下这个原理。

第3种，lookup+small+row

这种方法是今天最想要分享的，在E2单元格输入公式=LOOKUP(100,SMALL(A2:C6,ROW(1:15)))。

SMALL(A2:C6,ROW(1:15))这部分用small从A2:C6这个区域中分别提取第1,2,3,···，一直到15个最小值，这样就给数字按升序排序了。结果为{-65.2;-32.5;-23;0;12.4;23.5;24.3;35.5;99.85;102;201;#NUM!;#NUM!;#NUM!;#NUM!}。

这里用ROW(1:15)是因为这个区域有5行3列，共15个单元格。当然你用更大的数字也没关系，比如ROW(1:99)也是可以的。

small返回的结果中会有错误值#NUM!，是因为这个区域中还包含文本，数字的个数只有11个。当提取第12个最小值时，找不到了，所以返回#NUM!

LOOKUP(100,SMALL(A2:C6,ROW(1:15)))这部分就是用lookup在排好序的数组中查找100，并返回对应的值99.85。

lookup的第2参数是按升序排序的，并且可以忽略错误值，会找到小于等于查找值100的最大值。

第4种，max+text

text简直无处不在，用法太多了。在E2单元格输入公式=MAX(--TEXT(A2:C6,"[<=100];;!0;!0"))，按ctrl+shift+enter三键。

TEXT(A2:C6,"[<=100];;!0;!0")这部分用来作条件判断，如果A2:C6的值小于等于100，那还是显示为原来的值；如果大于100，强制显示为0，如果是文本，也强制显示为0。结果如下图所示。

因为text返回的结果是文本，所以要通过减负运算，也就是--TEXT(A2:C6,"[<=100];;!0;!0")将文本型数字变为数值，最后用max求最大值。

如果A2:C6这个区域中的数字全是负数，那么这个公式的结果就不对了。

第5种，aggregate

在E2单元格输入公式=AGGREGATE(14,6,A2:C6/(A2:C6<=100),1)。

链接：

https://pan.baidu.com/s/14jRQEM2UOyW_OBmhYEpjrA

提取码：etxy