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

idiocy

mykitami 发表于 2024-10-23 16:58
问题六
没有必要再去引用数据源中的数据，因为你在AI4:BK5已经引用到考勤表了，在本表中引用不是列方便 ...

高人你好,
小弟刚才发现了个隐藏0值公式计算错误的小问题需要请高人帮忙修正一下,


排班表AL34=IFERROR(1/COUNTIFS($D34:$AH34,"*"&数据源!$L$16)*0.5^-1,"")


隐藏0值的公式计算结果是0.5天,
4个半公休应该=2天.




排班表AN16=IFERROR(1/COUNTIFS($D16:$AH16,"*"&数据源!$L$19)0.5^-1,"")
隐藏0值的公式计算结果是0.67天,
3个半补休应该=1.5天.


请问如何修改这个隐藏0值的公式?
还是隐藏0值的公式还有其他写法?


2024年10月23日 星期三

mykitami

idiocy 发表于 2024-10-23 19:19
高人你好,
小弟刚才发现了个隐藏0值公式计算错误的小问题需要请高人帮忙修正一下,

我上次说的不严谨
=IFERROR(1/COUNTIFS($D34:$AH34,"*"&数据源!$L$16)*0.5^-1,"")
你上面的这个公式，后部的^-1，是指对0.5取倒数
实际上是我们要对COUNTIFS($D34:$AH34,"*"&数据源!$L$16)*0.5这段的结果取倒数
由于乘方的运算级别很高，会优先计算，不加括号的话，会先计算0.5^-1,然后再与前面相乘
所以要对整个这部分加括号
改为：
=IFERROR(1/(COUNTIFS($D34:$AH34,"*"&数据源!$L$16)*0.5)^-1,"")
或者
=IFERROR((1/COUNTIFS($D34:$AH34,"*"&数据源!$L$16)*0.5)^-1,"")

以前的公式没有加括号是因为后面没有*0.5这样的结尾.

idiocy

mykitami 发表于 2024-10-23 20:17
我上次说的不严谨
=IFERROR(1/COUNTIFS($D34:$AH34,"*"&数据源!$L$16)*0.5^-1,"")
你上面的这个公式， ...

原来如此原来如此,
茅塞顿开啊,
原来是有个运算优先的关系哦,
这样子公式计算结果就对的上了,
又向高人学习了一招,
小弟心里偷着乐````
呵呵```




不好意思又要打扰高人一下下了,
小弟有个计数休法定节假日的公式问题想要请教可以吗?


问题08:
排班表AR9=COUNTIFS($D9:$AH9, "*节")


小弟本来是想统计节假日关键字用来看谁休了节假日,
谁在节假日上班的,
但是这个公式统计的不对,
把每人每月所有的节假日计数都统计出来了,


应该是
有英文字母节假日的在“法假”对应单元格里面显示计数数量0,
因为这个人没有休息法定节假日而是在出勤上班,
没有英文字母就是某某节的这种法定节假日在“法假”对应单元格里面显示计数数量1,
因为如果某个月有多个法定节假日就是计数多个,
譬如2025年10月份就又有国庆节又有中秋节又有重阳节,
重阳节不是法定节假日.


这个出勤或休息计数“国庆节”“中秋节”这种法定节假日的公式应该怎么写才统计正确呢?


请问是否可以借鉴统计考勤符号的公式写出来呢?
还是需要再另外写公式才可以呢?
小弟需要高人的公式指点啊.
感激不尽.


2024年10月23日 星期三

mykitami

idiocy 发表于 2024-10-23 20:30
原来如此原来如此,
茅塞顿开啊,
原来是有个运算优先的关系哦,

=IFERROR(1/SUM(ISNUMBER(FIND("节",$D9:$AH9))*(LENB(LEFT($D9:$AH9))=2))^-1,"")
或
=IFERROR(ROWS(FILTER($D9:$AH9,ISNUMBER(FIND("节",$D9:$AH9))*(LENB(LEFT($D9:$AH9))=2))),"")

idiocy

mykitami 发表于 2024-10-23 20:54
=IFERROR(1/SUM(ISNUMBER(FIND("节",$D9:$AH9))*(LENB(LEFT($D9:$AH9))=2))^-1,"")
或
=IFERROR(ROWS(F ...

原来如此,
FIND
FILTER
函数都要用到,
高人写的查找关键字"节"比小弟写的那个真是厉害很多啊.


让高人见笑了啊,
小弟的水平也就这么一点点了,
写出来的公式简单的不能再简单了,
嵌套啊组合啊什么的都不会也不灵活,
还得向高人多多学习多多指教啊.


这几天真的真的辛苦高人了,
问了一个又一个的问题,
小弟非常非常的感谢高人!


2024年10月23日 星期三

mykitami

idiocy 发表于 2024-10-23 21:22
原来如此,
FIND
FILTER

不用客气，尽管问，我最近正好有时间。

idiocy

mykitami 发表于 2024-10-23 21:27
不用客气，尽管问，我最近正好有时间。

非常非常感谢高人啊,


又有一个新的让小弟困惑不解的奇怪的问题需要请教高人.


问题09:
“考勤表”表最后面小弟有个“发薪天数”的列,
想要实现前面根据考勤符号统计出来的数字,
用(事假+病假+旷工)之外的这些总数字减去(事假+病假+旷工),
譬如矿工5天,
数值就会用月最大天数减去5天,
譬如事假3天病假3天,
数值就会用月最大天数减去6天,
就得出了发工资的天数,


和出勤天数是有区别的,
就好像公休、工伤假、婚假、丧假等等,
虽然没有计数出勤,
但是依然是要算计薪天数的.


小弟用IF函数写了个公式:
考勤表BP6=IF((SUM(AI6:AM7,AO6:BC7,BF6:BK7))>=30,30,(SUM(AI6:BK6)))-(BD6+BE6+AN6)
在小弟用“数据源”表引用统计的考勤符号的那个表里面可以使用,
但是在高人用直接引用上面标题的考勤符号的那个表里面就显示报错“#VALUE!”,
不知道为什么,
有点纳闷了.


而且小弟想要用月最大天数的那个公式结合写进去,
因为小弟这个公式月最大天数是30天,
如果是28天,29天,31天就不能用了,
请问高人可以帮忙再指导一下吗?


是不是使用这个IF函数的思路不对呢?


2024年10月23日 星期三

mykitami

idiocy 发表于 2024-10-23 23:03
非常非常感谢高人啊,

=LET(a,EOMONTH(排班表!$AA$5&"-"&排班表!$AD$5,0),IF((SUM(AI6:AM7,AO6:BC7,BF6:BK7))>=a,a,(SUM(AI6:BK6)))-(SUM(BD6,BE6,AN6)))

AI:BK这些列是由公式生成的数据，如果是0，都不显示，实现是一个假空值，假空值虽然看不到，字符长度也是0，但却是一个字符，不是数值0，你进行直接运算时，就会出错，如同计算p+3、“”+“”等，无意义，所以出错。
但把你们包括在sum()之中，就不会出错，因为sum会把这些字符统统都忽略掉，只计算真正的数值。

idiocy

mykitami 发表于 2024-10-22 12:41
问题五
先对“数据源”表的H列进行了微小的改造（见附件），严格按照你的规则，在可能有英文单字母的地方 ...

你好高人,
请教一个小问题可以吗?
这句公式
考勤表D6=LET(j,{"元旦节";"春节";"清明节";"劳动节";"端午节";"中秋节";"国庆节"},a,OFFSET(排班表!$C$8,$A6,COLUMNS($A:A)),b,IF(LENB(LEFT(a))=1,"β"&MID(a,2,9),a),IF($A6="","",IFNA(VLOOKUP(b,HSTACK(VSTACK(数据源!$H$7:$H$34,j,"β"&j),VSTACK(数据源!$K$7:$K$34,EXPAND("PH",7,,"PH"),EXPAND("法休T",7,,"法休T"))),2,),"")))
里面的
IF($A6="",
的这个
$A6
是起到什么作用?
为什么当序号A6的“01”改变了后面就不统计显示出来了呢?
感觉好像是认这个序号的排序来做后面的统计显示是吗?
换成其他单元格就不行了,
换成其他有空值的序号也不行了.


2024年10月27日 星期日

mykitami

idiocy 发表于 2024-10-27 20:36
你好高人,
请教一个小问题可以吗?
这句公式

=LET(j,{"元旦节";"春节";"清明节";"劳动节";"端午节";"中秋节";"国庆节"},a,OFFSET(排班表!$C$8,$A6,COLUMNS($A:A)),b,IF(LENB(LEFT(a))=1,"β"&MID(a,2,9),a),IF($A6="","",IFNA(VLOOKUP(b,HSTACK(VSTACK(数据源!$H$7:$H$34,j,"β"&j),VSTACK(数据源!$K$7:$K$34,EXPAND("PH",7,,"PH"),EXPAND("法休T",7,,"法休T"))),2,),"")))


请注意一下，公式中有两个$A6,分别起到不同的作用：

第一个$A6,是为了匹配“考勤表”与“排班表”中同一个人，也就是你两表中的同一个人的序号是一样的，因为一个表有合并单元格，另一个表没有合并，所以要通过序号来匹配是同一个人。（这就要求要确保两表中同一人的序号的一致性，否则，人就错位了，并且排班表中不能有空行，序号是从1开始的连续编号，也可以没有这样的限制，但公式写的又要长一点点。）
第二个$A6，是为了方便公式的拖拉或复制。如果其他行暂时没有数据时，可以一次性的进行全表拖拉复制。否则的话，要一行一行的进行。或者选中两行，一起向下的拖拉复制。这样做就是为了方便，毕竟前面是合并单元格。