遇到零数据要往前移位公式

jylai

黄色的结果就是蓝色是最终结果

黄色是有公式的，虽然得到结果，想遇到零数据要往前移位公式

leepoison

=1/TOROW(1/FREQUENCY(A2:F2+1%%%,{10,20,30,40}),3)

袁勇

=IFERROR(MOD(AGGREGATE(15,6,({1;2;3;4;5}*10+FREQUENCY($A2:$F2+1%%%,{10,20,30,40}))/(FREQUENCY($A2:$F2+1%%%,{10,20,30,40})>0),COLUMN(A1)),10),"")

jylai

两位老师您们太厉害了，感谢

另外再问一个问题，这一步列位怎么出问题

=BYROW(A2:F11,LAMBDA(x,1/TOROW(1/FREQUENCY(x+1%%%,{10,20,30,40}),3)))

丢丢表格

袁勇 发表于 2024-8-13 10:10
=IFERROR(MOD(AGGREGATE(15,6,({1;2;3;4;5}*10+FREQUENCY($A2:$F2+1%%%,{10,20,30,40}))/(FREQUENCY($A2:$F ...

你喜欢的现金流函数 NPV, ， 我学着写一个

=MID(SUBSTITUTE(NPV(9,FREQUENCY(A2:F2+1%%%,{10,20,30,40}),0,0),0,""),{2,3,4,5,6},1)


=MID(SUBSTITUTE(NPV(9,FREQUENCY(A2:F2+1%%%,{10,20,30,40})),0,""),{2,3,4,5,6},1)

无关岁月

一步到位

1. =DROP(REDUCE("",A2:A11,LAMBDA(_a,_b,IFNA(VSTACK(_a,1/TOROW(1/FREQUENCY(OFFSET(_b,,,,6)+1%%%,{10,20,30,40}),3)),""))),1)

复制代码

jylai

两位丢丢表格，无关岁月，老师您们太厉害了，感谢