两店营业额连续比较 感谢 咔咔乱坠，quqiyuan，橒♂蝣，jisijie630几位老师

cdyiyezhiqiu

如图所示，我想知道两个店的营业额在一段时间内最大连续比较。在不添加辅助列的情况下用公式直接统计到B38和B39。请老师指导，谢谢


营业额比较.rar (7.27 KB, 下载次数: 8)

2023-5-27 22:01 上传

点击文件名下载附件

上面的描述不太准确，应该是最大连续多少天比甲店高或者最大连续多少天比甲店低

咔咔乱坠

连续高于

1. =MAX(SCAN(0,A2:A36>B2:B36,LAMBDA(x,y,IF(y=(1=0),x+1,0))))

复制代码

连续低于

1. =MAX(SCAN(0,A2:A36<B2:B36,LAMBDA(x,y,IF(y=(1=0),x+1,0))))

复制代码

摘自超人连胜次数贴

shaowu459

咔咔乱坠 发表于 2023-5-27 23:46
连续高于
连续低于
摘自超人连胜次数贴

这样写的时候，第二参数判断本身就是true或false啦，if里直接用就成

整整，if也可以不用

quqiyuan

1. =MAX(FREQUENCY(IF(SIGN($B$2:$B$36-$A$2:$A$36)=1,ROW($1:$35)),IF(SIGN($B$2:$B$36-$A$2:$A$36)=1,,ROW($1:$35))))

复制代码

1. =MAX(FREQUENCY(IF(SIGN($B$2:$B$36-$A$2:$A$36)=-1,ROW($1:$35)),IF(SIGN($B$2:$B$36-$A$2:$A$36)=-1,,ROW($1:$35))))

复制代码

橒♂蝣

=LET(x,IF(B2:B36-A2:A36>0,1,""),
z,TRIM(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("a",0,x),"1a",1),"a"," ")),
y,SUBSTITUTE(z," ",REPT(" ",99)),
a,TRIM(MID(y,SEQUENCE(LEN(z)-LEN(SUBSTITUTE(z," ","")))*99-98,99)),
MAX(LEN(a))
)

橒♂蝣

=LET(x,IF(B2:B36-A2:A36>0,"",2),
z,TRIM(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("a",0,x),"2a",2),"a"," ")),
y,SUBSTITUTE(z," ",REPT(" ",99)),
a,TRIM(MID(y,SEQUENCE(LEN(z)-LEN(SUBSTITUTE(z," ","")))*99-98,99)),
rr,MAX(LEN(a)),
rr
)

咔咔乱坠

shaowu459 发表于 2023-5-28 07:19
这样写的时候，第二参数判断本身就是true或false啦，if里直接用就成

整整，if也可以不用

原来如此，感谢超人老师指正

jisijie630

=MAX(FREQUENCY(ROW(2:36),IF(B2:B36<A2:A36,ROW(2:36),"")))-1


=MAX(FREQUENCY(ROW(2:36),IF(B2:B36<A2:A36,"",ROW(2:36))))-1

cdyiyezhiqiu

非常感谢 咔咔乱坠，quqiyuan，橒♂蝣，jisijie630几位老师