excel 数组公式

问题1：excel中找出一列数的最大值及其行号，

问题2：第一个非0非空的值及其行号，

问题3：最后一个非0非空的值及其行号。

第一个问题比较简单，max函数求最大值，match函数求第一次出现最大值的行号。

还有一种解法是用lookup函数，

LOOKUP(1,0/(B3:B63=MAX(B3:B63)),ROW(3:63))，两者区别是如果存在多个max值，用match函数会匹配第一个最大值，用lookup函数会匹配最后一个最大值。

问题2比较复杂，无法直接利用match函数，因为excel里没有直接找到第一个非0的函数，需要自己写。如果用vba的循环问题很好解决，

现在要求只能用excel中自带的函数。

首先要拿出数组公式这个概念，=sum(A1:A10*2)，按ctrl+shift+enter，自动加{}，会将A1:A10这个数组中每个元素乘以2，在求和。（注意lookup，不需要加{}）

假设我们要找A1:A10这个数组中第一个出现的非0元素，可以先做个变换

(ROUNDUP(A1:A10/SUM(A1:A10),1)*10   这个公式可以将数组中非0数都变成1，为啥要整这么复杂，这样式子里不含IF函数，因为不会嵌套IF：）。。

然后问题就简单了，查找=LOOKUP(1,0/(ROUNDUP(A1:A10/SUM(A1:A10),1)*10>0),$B$1:$B$10)，其中$B$1:$B$10是查找对应的标签名，如果要求行号，改成ROW(1:10)即可。

但是问题来了，这样求得是问题3。不是问题2，那么把A1:A10逆序一下即可。

先求逆序数组： LOOKUP(COUNT(A1:A10)-ROW(1:9),ROW(1:9),A1:A10) ，然后把A1:A10 替换成这个公式即可。与此同时，$B$1:$B$10也需要做逆序处理，

$B$1:$B$10 → LOOKUP(COUNT(A1:A10)-ROW(1:9),ROW(1:9),$B$1:$B$10) ，这里A1:A10的length和$B$1:$B$10相同

=LOOKUP(1,0/(ROUNDUP( LOOKUP(COUNT(A1:A10)-ROW(1:9),ROW(1:9),A1:A10)/SUM(A1:A10),1)*10>0),LOOKUP(COUNT(A1:A10)-ROW(1:9),ROW(1:9),$B$1:$B$10))

Oh，这个公式，so long。。

 补充：如果数值区里空值比较多，可能会出错，解决办法就是自动补0，“开始”-“查找和选择”-“定位条件”-“空值”-输入0-ctrl+enter