这个问题说难不难,说简单不简单。正所谓会者不难,难者不会。下面我就来分享3种方法。
多维引用法
=SUM(N(OFFSET(A1,SMALL(IF(ISEVEN(ROW(2:11))*ISNUMBER(A2:A11),ROW(2:11)),ROW(1:3))-1,)))
从图中可以看到,黄色行都在偶数行,所以用iseven函数判断数据所在的行是否为偶数,也就是ISEVEN(ROW(2:11))这部分。
我们不仅要黄色行的数据,还要它是非空的,也就是有数字的。空的用短横线"-"表示。ISNUMBER(A2:A11)这部分就是用isnumber函数判断A2:A11的数据是否为数字。
ISEVEN(ROW(2:11))*ISNUMBER(A2:A11)这两部分相乘,判断A2:A11中的数据是否为数字,并且所在行是否为黄色行。如果这两个条件同时满足的,返回1;否则返回0。结果为{1;0;1;0;1;0;1;0;1;0}。
IF(ISEVEN(ROW(2:11))*ISNUMBER(A2:A11),ROW(2:11))这部分用if函数判断,如果上面两个条件同时满足的,就返回相应的行号;否则返回false。结果为{2;FALSE;4;FALSE;6;FALSE;8;FALSE;10;FALSE}。
上一步中,我们已经得到了目标数字的行号,但是只需要前三个数字,所以用small函数从if函数的结果中取出前三个行号。也就是这部分SMALL(IF(ISEVEN(ROW(2:11))*ISNUMBER(A2:A11),ROW(2:11)),ROW(1:3)),它的结果为{2;4;6}。
得到了黄色行中非空单元格的前三个数字的行号后,有些小伙伴可能就不知道该如何返回相应的数据了。这时就可以用多维引用了。
也就是offset那部分,以A1单元格为起点,分别向下偏移1,3,5行,得到了由A2、A4、A6这三个单元格形成的多维引用。然后用n函数降维,得到了A2、A4、A6这三个单元格的数字。最后用sum求和。
加权法
在A12单元格输入下面的公式,按ctrl+shift+enter三键结束,右拉填充。
=SUM(MOD(SMALL(IF(ISEVEN(ROW(2:11))*ISNUMBER(A2:A11),ROW(2:11)/1%+A2:A11),ROW(1:3)),100))
这个公式的条件判断部分和第1种方法是完全一样的,也就是ISEVEN(ROW(2:11))*ISNUMBER(A2:A11)这部分。
现在它作为if函数的第一参数,如果两个条件同时满足的,不是返回相应的行号,而是返回ROW(2:11)/1%+A2:A11这部分,也就是用行号乘以100再加上A2:A11。这部分就是加权的思想。否则返回false。
if函数返回的结果为{233;FALSE;411;FALSE;620;FALSE;814;FALSE;1064;FALSE}。
然后用small函数从if函数的结果中取出前三个最小的,返回的结果为{233;411;620}。其中233表示第2行的33。
下一步就要得到33;11;20这三个数。如何得到呢?用mod函数除以100取余数就可以得到。最后用sum求和。
=SUM(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,A2:A11)&"</b></a>","a/b[position() mod 2=1][.*0=0][position()<4]"))
公式中红色部分其实就是filterxml的分列用法。首先利用textjoin将A2:A11的数据连接起来,分隔符为"</b><b>"。然后首位再连接上标签名称,构成xml格式的字符串。最后用"a/b"这个xpath返回所有b元素的文本内容。
但是现在有蓝色了[position() mod 2=1][.*0=0][position()<4]这部分,就相当于多加了三个筛选的条件。
第一个条件[position() mod 2=1]是筛选出黄色行的数据,第二个条件[.*0=0]是从黄色行的数据中筛选出数字的那些,第三个条件[position()<4]是从黄色行的数字中筛选出前3个数字。
filterxml虽然平时用的比较少,但在有些情况下还是非常有用的,尤其是它的筛选功能。而且它的用法有很多,论坛的海鲜老师有详细的教程,感兴趣的小伙伴可以去搜索下。
https://pan.baidu.com/s/1CrDF2v7MSq3BBL8NASerWw
联系客服