请教高人关于Excel表考勤表公式统计的5个问题

idiocy

请教高人关于Excel表考勤表公式统计的5个问题:




高人好,
小弟遇到了个关于Excel表考勤表公式统计的5个问题需要虚心请教一下高人,
望高人能够帮助指教一二.




现使用操作系统：Windows 10 64位
现使用Microsoft Office 软件：Microsoft Office Mondo 2016




问题01:
已知公式:
出勤AJ9=SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"A","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"B","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"C","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"D","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"E","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"F","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"G","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"H","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"I","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"J","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"K","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"L","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"M","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"N","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"O","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"P","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"Q","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"R","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"S","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"T","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"U","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"V","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"W","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"X","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"Y","")))+SUM(LEN(D9:AH9)-LEN(SUBSTITUTE(D9:AH9,"Z","")))


求值需求:
每人每月统计求和从A到Z的凡纯英文字母出现次数.
英文字母半补休
英文字母半公休
需要除以2,
因为是上半天班,
只能算是0.5的出勤,
2个英文字母半补休或英文字母半公休才能算是1个完整的出勤.


这个公式太长了,
而且计算结果不是预期的那样.
请高人帮忙优化修正一下可以吗?








问题02:
已知公式:
公休AK9=COUNTIFS($D9:$AH9, 数据源!$H$14)+COUNTIFS($D9:$AH9, "*"&数据源!$H$16)/2


求值需求:
在“数据源”表里面查找对应的“公休”单元格,
统计这个对应的“公休”出现的次数,
在“数据源”表里面查找对应的“英文字母半公休”,
统计这个对应的“英文字母半公休”出现的次数,
因为2个英文字母半公休才能算是1个完整的公休.
两者的和.


不知道可不可以请高人帮忙检查一下这个公式,
可不可以再优化一下呢?








问题03:
已知每月最后一天的公式:
检查AT9=DATE(AA5, AD5+1, 1)-1


求值需求:
想用条件格式公式去检查每人每月的排班情况是否会超出每月的最大天数,
超出的话就用黄颜色充填单元格,
因为再怎么排班再怎么补休放假等都不会超出每月的最大天数的,
小弟写的这个公式计算的有错误,
想请高人帮忙重新修正一下可以吗?








问题04:
“排班表”表的D6:AH6范围内对应月份和日期在“节假日”表里面查找自动充填对应的节假日并用绿颜色充填单元格标记出来.
不知道怎么写这种公式.
譬如每年的01月01日元旦节,
排班表选择了某年01月份,
就在D6单元格显示出“元旦节”并用绿颜色充填标记,
每年的05月01日劳动节,
排班表选择了某年05月份,
就在D6单元格显示出“劳动节”并用绿颜色充填标记,
每年的10月01日国庆节,
排班表选择了某年10月份,
就在D6单元格显示出“国庆节”并用绿颜色充填标记,
之类的,
请高人帮帮忙写一写这种实现效果的公式好吗?








问题05:
需要把用英文字母和英文字母中文结合的“排班表”表用“数据源”表里面的“考勤符号”列里面的定义符号壹壹对应转换一下.
这个小弟写不出来,
想用Filter函数写又写不会.
请高人帮忙写一写好吗?


小弟的思路是这样的:
“考勤表”表D6,
“排班表”表的D9:AH9范围内的英文字母在“数据源”表F列查找如果有则返回K列的指定单元格K6的“√”.
大概意思就是
从A到Z的凡纯英文字母就返回K列的指定单元格K6的“√”.
英文字母中文结合的在H列里面有对应中文的就返回H列后面对应K列单元格的单元格内容.


不知道小弟是否有表达清楚意思,
已经上传附件图片,
可以参考小弟手动一个一个转换的“考勤表”表.




万分感谢啊```




2024年10月20日 星期日 笔

wj65134

问题1，但和你的结果有出入
改了

1. =SUM((D9:AG9&"吖"<"吖")-(MID(D9:AG9,2,1)="半")/2)

复制代码

wj65134

wj65134 发表于 2024-10-20 19:15
问题1，但和你的结果有出入

我手动数好像我这公式是对的

idiocy

wj65134 发表于 2024-10-20 19:15
问题1，但和你的结果有出入

非常感谢高人的公式指教啊,
果然优化了很多很精简了啊,
真是厉害.


请问高人公式里面的
吖
是为了提取英文字母和汉字的目的是吗?
向高人学习了.


小弟的那个公式求出来的数值是不对的.
举个例子,
贾迎春
的正确出勤天数应该是12.5天,
但是公式求出来的天数是整数13天,
看了高人的公式好像没有错误,
请高人帮帮忙检查一下是哪里求的不对好吗?


有10个纯英文字母是出勤10天,
B中秋节 是出勤1天,
D临班 是出勤1天,
A半补休 是出勤0.5天,
所以是10+1+1+0.5=12.5


奇怪啊,
公式哪里不对呢?

idiocy

wj65134 发表于 2024-10-20 19:33
我手动数好像我这公式是对的

原来如此,
小弟也纳闷,
小弟可能没有描述清楚,
给高人带来了困惑了.


贾迎春
有10个纯英文字母是出勤10天,
B中秋节：是中秋节这天上班出勤1天,
D临班：是临时安排来上班出勤1天,
A半补休：是只上班半天出勤0.5天,剩下的半天是补的加班补休,因为还要区分补半天公休.
所以正确出勤是：10+1+1+0.5=12.5


不知道小弟这样子的描述高人是否清楚了一些.

wj65134

idiocy 发表于 2024-10-20 20:16
非常感谢高人的公式指教啊,
果然优化了很多很精简了啊,
真是厉害.

=SUM((D9:AG9&"吖"<"吖")-(MID(D9:AG9,2,1)="半")/2)
我以为你要4舍5入

mykitami

问题1
=SUM(ISNUMBER(FIND(CHAR(ROW(INDIRECT("65:90"))),D9:AH9))*1)
如果需要除以2，就后面除以2
=SUM(ISNUMBER(FIND(CHAR(ROW(INDIRECT("65:90"))),D9:AH9))*1)/2

wj65134

具体我也不清楚，只晓得如果比大小，字母小于汉字，吖比一般常用汉字都小，你有空行所以连接一个汉字，再比较

mykitami

问题2
=IFERROR(1/(COUNTIFS($D9:$AH9, 数据源!$H$14)+COUNTIFS($D9:$AH9, "*"&数据源!$H$16)/2)^-1,"")

mykitami

问题3
=AT9=DAY(DATE($AA$5,$AD$5+1,1)-1)