Question

EXCEL如何统计连续多次小于某数的个数？

Answer 1

如图，公式分别为：

C3 =IF((COUNTIF($A2:$A3,"<1.66")=2)*(COUNT(C2:C2)=0),1,"")

D4 =IF((COUNTIF($A2:$A4,"<1.66")=3)*(COUNT(D2:D3)=0),1,"")

E5 =IF((COUNTIF($A2:$A5,"<1.66")=4)*(COUNT(E2:E4)=0),1,"")

F6 =IF((COUNTIF($A2:$A6,"<1.66")=5)*(COUNT(F2:F5)=0),1,"")

G7 =IF((COUNTIF($A2:$A7,"<1.66")=6)*(COUNT(G2:G6)=0),1,"")

H8 =IF((COUNTIF($A2:$A8,"<1.66")=7)*(COUNT(H2:H7)=0),1,"")

I9 =IF((COUNTIF($A2:$A9,"<1.66")=8)*(COUNT(I2:I8)=0),1,"")

J10 =IF((COUNTIF($A2:$A10,"<1.66")=9)*(COUNT(J2:J9)=0),1,"")

K11 =IF((COUNTIF($A2:$A11,"<1.66")=10)*(COUNT(K2:K10)=0),1,"")

以上全部下拉。

Answer 2

点击数据，点击查看就会知道结果