求助~~如何通过数据表统计人员请假情况

Anyiulam

各位大神，求助能否在图二设定固定公式，后续只要更新导入图一人事提供的数据表后，图二就能自动生成（包括姓名、请假类型、总时长）？

edwin11891

各种请假时长统一转换为小时。

1. =GROUPBY(Sheet1!A2:B9,EVALUATE(SUBSTITUTES(Sheet1!E2:E9,{"小时","天"},{"","*24"})),SUM,0,0)

复制代码

如果需要特定某个人，在函数后面直接增加筛选即可。

1. =GROUPBY(Sheet1!A2:B9,EVALUATE(SUBSTITUTES(Sheet1!E2:E9,{"小时","天"},{"","*24"})),SUM,0,0,,Sheet1!A2:A9="张三")

复制代码

Anyiulam

Anyiulam

edwin11891 发表于 2024-11-7 17:25
各种请假时长统一转换为小时。

感谢解答~但根据要求不允许修改请假时长单位，年假必须以“天”为单位

lanjian229

IF(B2="年假",SUM(IFERROR(--SUBSTITUTE(Sheet1!$E$2:$E$9,"天",""),0)*(Sheet1!$A$2:$A$9=A2)*(Sheet1!$B$2:$B$9=B2))&"天",SUM(IFERROR(--SUBSTITUTE(Sheet1!$E$2:$E$9,"小时",""),0)*(Sheet1!$A$2:$A$9=A2)*(Sheet1!$B$2:$B$9=B2))&"小时")

gadxjyang

表1请假单位统一用小时
A2 =GROUPBY(Sheet1!A2:B9,--REGEXP(Sheet1!E2:E9,"[0-9.]+"),SUM,,0)

Anyiulam

lanjian229 发表于 2024-11-7 17:36
SUM(IFERROR(--SUBSTITUTE(Sheet1!$E$2:$E$9,"小时",""),SUBSTITUTE(Sheet1!$E$2:$E$9,"天","")*24)*(Sheet ...

感谢解答~

Anyiulam

图二的姓名、请假类型、总时长都需要公式自动统计出。
抱歉~~~可能是我没表达清楚，导致帖子里大神们的答复都是只计算公式的

Anyiulam

gadxjyang 发表于 2024-11-7 17:39
表1请假单位统一用小时
A2 =GROUPBY(Sheet1!A2:B9,--REGEXP(Sheet1!E2:E9,"[0-9.]+"),SUM,,0)

感谢解答~

lanjian229

IF(B2="年假",SUM(IFERROR(--SUBSTITUTE(Sheet1!$E$2:$E$9,"天",""),0)*(Sheet1!$A$2:$A$9=A2)*(Sheet1!$B$2:$B$9=B2))&"天",SUM(IFERROR(--SUBSTITUTE(Sheet1!$E$2:$E$9,"小时",""),0)*(Sheet1!$A$2:$A$9=A2)*(Sheet1!$B$2:$B$9=B2))&"小时")