求大师帮帮忙写个公式，跪谢

yecp2011

请教各位大师，请问使用哪个公式可以得出Q列的结果，跪谢

hugeinfo

=TAKE(FREQUENCY(IF(TOCOL(A1:N1,1)>=100,ROW(INDIRECT("1:"&COUNTA(TOCOL(A1:N1,1))))),IF(TOCOL(A1:N1,1)<100,ROW(INDIRECT("1:"&COUNTA(TOCOL(A1:N1,1)))))),-1)

quqiyuan

=INDEX(FREQUENCY(IFERROR(IF(N(OFFSET(INDIRECT("rc"&LOOKUP(1,0/(A1:N1<>""),COLUMN(A:N)),),,-COLUMN(A:N)+1))>=100,COLUMN(A:N)),),IFERROR(IF(N(OFFSET(INDIRECT("rc"&LOOKUP(1,0/(A1:N1<>""),COLUMN(A:N)),),,-COLUMN(A:N)+1))<100,COLUMN(A:N)),)),1)

edwin11891

=if(N1<100,0,TAKE(LEN(TEXTSPLIT(TEXTJOIN(,1,IF(A1:N1>100,1,0)),,0)),-1))

我走我流

=LOOKUP(1,0/LEN(A1:N1),COLUMN(A:N))-IFERROR(LOOKUP(1,0/(A1:N1<100)/LEN(A1:N1),COLUMN(A:N)),)

yellowhawk

=COUNTA(A1:N1)-MAX(IFERROR(IF(N(OFFSET(A1,,,,COUNTA(A1:N1))>=100)=0,COLUMN(A:N),0),0))
经我测试是正确的。

少少飞翔

=LOOKUP(9^9,FREQUENCY(IF(A1:N1="","",IF(A1:N1>=100,COLUMN(A1:N1),0)),IF(A1:N1="","",IF(A1:N1<100,COLUMN(A1:N1),0))))

hxj119

参考公式：
=LOOKUP(1,0/(OFFSET(A1,,,,COUNT(A1:N1))<100),COUNT(OFFSET(A1,,,,COUNT(A1:N1)))-COLUMN(OFFSET(A1,,,,COUNT(A1:N1))))

qydn

Sub tt()
Dim i%, j%, m%
i = Application.WorksheetFunction.Count(Range("a:a"))

我的城市你来过

=LET(x,TEXTSPLIT(TEXTJOIN("@",,TEXTSPLIT(CONCAT(IF(IF(OFFSET($A1,,SUM(--($B1:$N1<>"")))>=100,$A1:$N1,0)>=100,COLUMN($A1:$N1)^0,"@")),"@")),"@"),IFERROR(LENB(INDEX(x,COUNTA(x))),0))