EXCEL统计数字大于0在一行中连续出现的最大次数，用数组公式求的，怎么求最大次数的各个数值的和？
用的这个公式求的最大连续次数=MAX(FREQUENCY(IF(A1:A12>0,ROW(A1:A12)),IF(A1:A12>0,,ROW(A1:A12))))，比如求出最大次数是5，对应的数值是3,5,6,8,2怎么求这些数值的和？谢谢
=SUM(OFFSET(A1,MATCH(MAX(FREQUENCY(IF(A1:A12>0,ROW(A1:A12)),IF(A1:A12>0,,ROW(A1:A12)))),FREQUENCY(IF(A1:A12>0,ROW(A1:A12)),IF(A1:A12>0,,ROW(A1:A12))),)-MAX(FREQUENCY(IF(A1:A12>0,ROW(A1:A12)),IF(A1:A12>0,,ROW(A1:A12))))-1,,MAX(FREQUENCY(IF(A1:A12>0,ROW(A1:A12)),IF(A1:A12>0,,ROW(A1:A12))))))
温馨提示：内容为网友见解，仅供参考
第1个回答 2012-12-30
=SUM(OFFSET($A$1,LOOKUP(,0/(FREQUENCY(IF(A1:A12>0,ROW(A1:A12)),IF(A1:A12>0,,ROW(A1:A12)))=MAX(FREQUENCY(IF(A1:A12>0,ROW(A1:A12)),IF(A1:A12>0,,ROW(A1:A12))))),ROW($1:$13))-MAX(FREQUENCY(IF(A1:A12>0,ROW(A1:A12)),IF(A1:A12>0,,ROW(A1:A12))))-1,,MAX(FREQUENCY(IF(A1:A12>0,ROW(A1:A12)),IF(A1:A12>0,,ROW(A1:A12))))))
数组公式,按ctrl+shift+enter三键结束
数组公式,按ctrl+shift+enter三键结束
相似回答
