统计时间点出现的次数（去除重复值）

zhiwen20190717

如在图所示，统计B列时间点出现的次数（去除重复值）。

hxj119

1. =REDUCE(0,TAKE(UNIQUE($A$2:$B$772),,-1),LAMBDA(x,y,LET(a,TEXTSPLIT(E1,"-")/1,x+(y>=INDEX(a,1)*(y<=INDEX(a,2))))))

复制代码

博爱风雨同舟

=SUM((MATCH(A2:A772&B2:B772,A2:A772&B2:B772,)=ROW(1:771))*(1-ISERR(VDB(,,"9:00","08:30",B2:B772))))

无关岁月

1. =SUM(($B$2:$B$772>=IMREAL(E$1&"j"))*($B$2:$B$772<=-IMAGINARY(E$1&"j"))/COUNTIF($B$2:$B$772,$B$2:$B$772))

复制代码

博爱风雨同舟

=SUM((MATCH(A2:A772&B2:B772,A2:A772&B2:B772,)=ROW(1:771))*(MAP(TEXT($B$2:$B$772*24,"0.00")/1,LAMBDA(X,MEDIAN(IMREAL(IMDIV(E$1&"i",{1,"-i"}))*24,X)=X))))
工程函数更简单

zhiwen20190717

博爱风雨同舟 发表于 2024-9-13 13:44
=SUM((MATCH(A2:A772&B2:B772,A2:A772&B2:B772,)=ROW(1:771))*(MAP(TEXT($B$2:$B$772*24,"0.00")/1,LAMBDA( ...

谢谢，不能去除重复值

博爱风雨同舟

zhiwen20190717 发表于 2024-9-13 14:13
谢谢，不能去除重复值

=SUM((MATCH($A$2:$A$772&$B$2:$B$772,$A$2:$A$772&$B$2:$B$772,)=ROW($1:$771))*(MAP(TEXT($B$2:$B$772*24,"0.0000")/1,LAMBDA(X,MEDIAN(IMREAL(IMDIV(E$1&"i",{1,"-i"}))*24,X)=X))))
公式往右拉，可以的

zhiwen20190717

博爱风雨同舟 发表于 2024-9-13 14:32
=SUM((MATCH($A$2:$A$772&$B$2:$B$772,$A$2:$A$772&$B$2:$B$772,)=ROW($1:$771))*(MAP(TEXT($B$2:$B$772* ...

如果删除A列，只查找B列数据，该如何调整这个函数了，十分感谢

博爱风雨同舟

=SUM((MATCH($B$2:$B$772,$B$2:$B$772,)=ROW($1:$771))*(MAP(TEXT($B$2:$B$772*24,"0.0000")/1,LAMBDA(X,MEDIAN(IMREAL(IMDIV(E$1&"i",{1,"-i"}))*24,X)=X))))

我会修电视

1. =SUM(MAP(UNIQUE($B$2:$B$772),LAMBDA(X,(X>=--TEXTBEFORE(E1,"-"))*(X<=--TEXTAFTER(E1,"-")))))

复制代码

向右复制。