首先,您需要做一些准备工作。参见 J:O :
列
然后您可以执行简单的HLOOKUP
:
=ARRAYFORMULA(IFERROR(HLOOKUP(A24:24,QUERY(QUERY({Sheet2!J1:O},"select Col1,day(Col1),sum(Col5),sum(Col6),Col4
where month(Col1)+1="&B23&"
group by Col1,Col4
format Col1'd'",0),"select Col5,"&IF(D23="Break","sum(Col4)","sum(Col3)")&"
where Col5 is not null
group by Col5
pivot Col2
label Col5'Employee names'",1),ROW(A2:A20),0)))
更新:
要解决缺失的打孔问题,您可以添加支票:
={"punch-out check";
ARRAYFORMULA(IFNA(VLOOKUP(A2:A&C2:C,QUERY(QUERY({A2:A&C2:C,COUNTIFS(A2:A&C2:C,A2:A&C2:C,ROW(A2:A),"<="&ROW(A2:A))},sum(Col2) group by Col1"),"where not Col2 >1 offset 1",2,0)))}
那么公式将是:
=ARRAYFORMULA({"Date","Start","End","Name";
{ARRAY_CONSTRAIN(FILTER(SORT(FILTER({timesheet!A2:B,timesheet!E2:E},timesheet!F2:F<>1),1,3,MOD(ROW(INDIRECT("timesheet!A1:A"&ROWS(timesheet!A2:A)-COUNTIF(timesheet!F2:F,1))),2)),ROWS(timesheet!A2:A)-COUNTIF(timesheet!F2:F,2)},QUERY(FILTER(SORT(FILTER({timesheet!A2:B,MOD(ROW(INDIRECT("timesheet!A2:A"&ROWS(timesheet!A2:A)-COUNTIF(timesheet!F2:F,1)+1)),"select Col2,Col3",0)})
并且仪表板将其列出为:
={"Didn't punched out"; FILTER(timesheet!E2:E,timesheet!A2:A=C2,timesheet!F2:F=1)}
,
我认为您可以做到
Sum of even times - Sum of odd times
=ArrayFormula(sumif(iferror(iseven(rank(if((timesheet!$E2:$E=$A3)*(timesheet!$A2:$A=date(2019,$B$1,O$2)),timesheet!$B2:$B),if((timesheet!$E2:$E=$A3)*(timesheet!$A2:$A=date(2019,1)),false),true,timesheet!$B2:$B)-
sumif(iferror(isodd(rank(if((timesheet!$E2:$E=$A3)*(timesheet!$A2:$A=date(2019,timesheet!$B2:$B))
这是整张纸上的样子:
编辑
检查一下打孔次数是否是偶数:
=ArrayFormula(if(isodd(countifs(timesheet!$E$2:$E,$A3,timesheet!$A$2:$A,date(2019,O$2))),"Error",sumif(iferror(iseven(rank(if((timesheet!$E$2:$E=$A3)*(timesheet!$A$2:$A=date(2019,timesheet!$B$2:$B),if((timesheet!$E$2:$E=$A3)*(timesheet!$A$2:$A=date(2019,timesheet!$B$2:$B)-
sumif(iferror(isodd(rank(if((timesheet!$E$2:$E=$A3)*(timesheet!$A$2:$A=date(2019,timesheet!$B$2:$B)))
本文链接:https://www.f2er.com/3014715.html