Office学习社区 › 公式和函数 › 数组求和

郑旭辉 发表于 2021-10-29 19:02:36

=MAX(MMULT(--TEXT({4,5,5,3,4,5,5,4,1,2,2,4,3,2,3,4,3,5,4,4,3,3,4,3,5,2,5,5,4,3},"[<3]-1!0!00;0"),N(ROW(1:30)<COLUMN(A:AD))))

用户海角以远 发表于 2021-11-2 22:14:11

-9!e9应该会更通用些

海阔天晴 发表于 2021-11-9 09:08:28

对对！
做复杂了，虽然初衷是好的。

阿德金森 发表于 2021-11-9 20:49:26

我看是小于3 数值出现之前的值 相加
=MAX(,MMULT(--TEXT({4,5,5,3,4,5,5,4,1,2,2,4,3,2,3,4,3,5,4,4,3,3,4,3,5,2,5,5,4,3},"[<3]-9!E9"),N(ROW(1:30)<COLUMN(B:AE))))

敢动中国 发表于 2021-11-10 04:43:59

=SUM(FILTERXML(CONCAT("<a>0","<b>"&{4,5,5,3,4,5,5,4,1,2,2,4,3,2,3,4,3,5,4,4,3,3,4,3,5,2,5,5,4,3}&"</b>","</a>"),"a|//b[.|preceding::*<3=0]"))

冰之诺言 发表于 2021-11-12 05:14:47

=MAX(,MMULT(--TEXT({1,5,5,3,4,5,5,4,1,2,2,4,3,2,3,4,3,5,4,4,3,3,4,3,5,2,5,5,4,3},"[<3]-9!E9"),N(ROW(1:30)<COLUMN(B:AE))))
这个逗号 看起来不能少 。。。
是我看错了，只按我的方法计算， 大佬的公式是正确的！
=MAX(MMULT(--TEXT({1,5,5,3,4,5,5,4,1,2,2,4,3,2,3,4,3,5,4,4,3,3,4,3,5,2,5,5,4,3},"[<3]-9!E9"),N(ROW(1:30)<COLUMN(A:AE))))

辋川河 发表于 2021-11-14 22:01:18

=MAX(MMULT(--TEXT({1,5,5,3,4,5,5,4,1,2,2,4,3,2,3,4,3,5,4,4,3,3,4,3,5,2,5,5,4,3},"[<3]-9!E9"),N(ROW(1:30)<COLUMN(A:AE))))

升起的太阳 发表于 2021-11-16 19:49:53

=SUM(--MID(LEFT(SUBSTITUTE(CONCAT(TEXT({4,5,5,3,4,5,5,4,1,2,2,4,3,2,3,4,3,5,4,4,3,3,4,3,5,2,5,5,4,3},"[<3]j")),"j",REPT(0,22)),22),ROW(1:22),1))

个位数求和

飘忽若月 发表于 2021-11-19 16:01:59

不太理解36是怎么得出的。。。
=SUM(--TEXT({4,5,5,3,4,5,5,4,1,2,2,4,3,2,3,4,3,5,4,4,3,3,4,3,5,2,5,5,4,3},"[<3]0;!0"))

狼崽 发表于 2021-11-23 03:38:46

不太理解36是怎么得出的。。。
=SUM(--TEXT({4,5,5,3,4,5,5,4,1,2,2,4,3,2,3,4,3,5,4,4,3,3,4,3,5,2,5,5,4,3},"[<3]0;!0"))

查看完整版本: 数组求和