上下班时间分组统计

sxo

上下班开始和结束时间打卡统计。截图中的最后一行错误数据，毋介。

cyh3230

低版本,代码如下

1. SELECT 工号
   
2.         , replace(format(first(上班开始时间)), "2020", "2023") AS 上班
   
3.         , replace(format(last(下班离开时间)), "2020", "2023") AS 下班
   
4. FROM [Sheet1$A:C]
   
5. GROUP BY 工号, format(上班开始时间, "short date")

复制代码

博爱风雨同舟

=INDEX(A:B,SMALL(IF((MINIFS(B2:B11,A2:A11,A2:A11,B2:B11,B2:B11)=B2:B11)*(MATCH(A2:A11&INT(B2:B11),A2:A11&INT(B2:B11),)=ROW(1:10)),ROW(2:11),""),SEQUENCE(4)),SEQUENCE(,2))

mykitami

=LET(a,A2:A11&TEXT(B2:B11,"emmdd"),REDUCE(A1:C1,UNIQUE(a),LAMBDA(x,y,LET(a,FILTER(A2:C11,a=y),VSTACK(x,HSTACK(LEFT(y,3),INDEX(SORT(a,2,1),1,2),INDEX(SORT(a,3,-1),1,3)))))))

改为2023年
=LET(a,A2:A11&TEXT(B2:B11,"emmdd"),REDUCE(A1:C1,UNIQUE(a),LAMBDA(x,y,LET(a,FILTER(A2:C11,a=y),b,INDEX(SORT(a,2,1),1,2),c,INDEX(SORT(a,3,-1),1,3),VSTACK(x,HSTACK(LEFT(y,3),EDATE(b,12*3)+b-INT(b),EDATE(c,12*3)+c-INT(c)))))))

baycide

cyh3230 发表于 2024-4-28 16:55
低版本,代码如下

请教一下 这SQL语句在哪里插入能调出来录入啊

博爱风雨同舟

=FILTER(A2:B12,SIGN(FREQUENCY(ROW(1:10),MATCH(A2:A11&INT(C2:C11),A2:A11&INT(C2:C11),)+B2:B11%%%))*B2:B12)

博爱风雨同舟

=FILTER(HSTACK(A2:A12,C2:C12),SIGN(FREQUENCY(-ROW(1:10)-0.9,-MATCH(A2:A11&INT(C2:C11),A2:A11&INT(C2:C11),)-C2:C11%%%))*C2:C12)
下班时间

博爱风雨同舟

=VSTACK(HSTACK(A1:C1),HSTACK(FILTER(A2:B12,SIGN(FREQUENCY(ROW(1:10),MATCH(A2:A11&INT(C2:C11),A2:A11&INT(C2:C11),)+B2:B11%%%))*B2:B12),FILTER(C2:C12,SIGN(FREQUENCY(-ROW(1:10)-0.9,-MATCH(A2:A11&INT(C2:C11),A2:A11&INT(C2:C11),)-C2:C11%%%))*C2:C12)))
365版本，一步到位