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

mykitami

问题五
先对“数据源”表的H列进行了微小的改造（见附件），严格按照你的规则，在可能有英文单字母的地方用β进行标识。但H6单元格出勤日对应K6单元格√，这一对组合我公式中没有用到，因为你所有的数据中，都没有"出勤日"这三个字。（如果后期要用到，你可以在公式对应范围中包含进去即可）
考勤表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,),"")))
进行右拉，下拉或复制都可以

idiocy

mykitami 发表于 2024-10-22 10:56
有可能你有好几个版本的授权，最示的是2016，实际上是365
因为有些高级的函数在2016版是没有的，但你却 ...

好的,
感谢高人的指导,
我是照着版本号抄下来的,
我以为这有区别,
但是感觉上好像就是Microsoft Office 365版本,
但是为了慎重,
还是照抄版本号.


下次请教问题时小弟就写Microsoft Office 365版本.

idiocy

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

非常非常感谢高人的公式帮助啊,
小弟都不知道说什么好了,
高人写的公式和解题的思路小弟差的都不知远到哪里去了,
级别相差太大了.


原来还可以在“数据源”表里面的“特殊日”有英文字母的前面加个“β”,
用来搜索查找“排班表”表里面的排班文字,
这个思路太了不起,
向高人学习了啊.


出勤日 √
暂时还没有用到,
这个做备用用的,
感谢高人在公式里面给小弟预留了位置啊.


高人写的公式太厉害了,
右拉,下拉,复制都可以,
不像小弟写的这个公式,
还需要在公式里面不同的地方去一个一个的改,
真麻烦啊,
要是考勤的人再多点就更麻烦了,
高人的公式效率提升了好多倍了.




请问小弟再请教高人一个小问题帮忙想想思路可以吗?
“排班表”AK列的“公休”,
这个是为了检查是否每人每月有固定的6天公休,
因为多了的话就需要重新排班,
如果少了的话就需要下个月补回来,
所以小弟想在“条件格式”里面去实现这个效果,
小于6,大于6,就用黄颜色充填单元格标示出来.


单元格看上去是空值,
但实际上单元格内是有公式的,
小弟设置的“条件格式”“不等于6”的话这个有公式但看上去是空值的单元格也会充填黄颜色,
请问高人有什么方法可以实现小于6,大于6,就用黄颜色充填单元格标示出来的同时,
这个有公式但看上去空值的单元格也不会充填黄颜色标示出来呢?


小弟目前用的是“条件格式”“小于6”.

idiocy

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

高人你好,
关于单元格有公式但是看上去是空值想要条件格式去充填颜色要显示为无色的问题,
在翻看了2019年的帖子之后,
得到了“刀羊”高人指点其他求助者的解答,
小弟的这个问题也解决好了.


条件格式AL9=ABS(AL9)<>6


非常感谢高人的公式指教.


2024年10月22日 星期二

mykitami

idiocy 发表于 2024-10-22 14:42
非常非常感谢高人的公式帮助啊,
小弟都不知道说什么好了,
高人写的公式和解题的思路小弟差的都不知 ...

可以用条件格式公式
=--AK9<>6

mykitami

idiocy 发表于 2024-10-22 19:46
高人你好,
关于单元格有公式但是看上去是空值想要条件格式去充填颜色要显示为无色的问题,
在翻看了 ...

对，对
这个可以
但是不是应该是AK9

idiocy

mykitami 发表于 2024-10-22 21:34
对，对
这个可以
但是不是应该是AK9

非常感谢高人的纠正啊,
确实是AK9,
看花眼了,
数错列了,
不好意啊.




请问小弟还可以再请教高人考勤表后面统计考勤符号出现次数的公式问题吗?
不能右拉不能下拉.


问题06:
已知根据“数据源”表里面对应的“考勤符号”出现次数记数统计的公式:
考勤表AI6=COUNTIF($D6:$AH6,数据源!$K$6)


由于想要隐藏数值零方便查看,公式为:
考勤表AI6=IF(COUNTIF($D6:$AH6,数据源!$K$6)=0,"",COUNTIF($D6:$AH6,数据源!$K$6))


但是这样写的公式不能右拉和下拉去充填公式,
因为右拉不能递增竖排列的单元格,
而下拉又对不上有合并的单元格,
请问高人写的这种即使有合并的单元格也可以右拉下拉充填公式的公式是怎么写的可以教一教小弟吗?
小弟看有的高人可以使用ROW的函数来判断是在第几行然后就可以右拉下拉公式用了,
不知道是怎么写的?
请高人指导一下好吗?






问题07:
已知本月加班的公式:
考勤表BL6=SUBTOTAL(109,$D7:$AH7)


这是计算每人每月整个月的包含法定节假日加班的公式,
小弟想要除开法定节假日加班之外的其他所有形式的加班不知道怎么写.
又不想用
考勤表BL6=SUBTOTAL(109,$D7:$AH7)-BM6
这种公式计算出来.


已知本月法定节假日加班的公式:
考勤表BM6=SUMIF($D6:$AH6,数据源!$K$13,$D7:$AH7)*2


乘以2是因为法定节假日加班的计时数是双倍,
计算双倍加班费或者是补休双倍的休息日要用到,
请问是否可以根据这样的公式去统计除开法定节假日加班之外的其他所有形式的加班小时数呢?
也就是除开“数据源”表里面对应的“考勤符号”“法休T”之外其他剩下的加班小时数呢?
如果还有其他除开的譬如“考勤符号”出差“BL”再用数组公式结合加进去计算请问高人这样子可以吗?


是不是要用到
FILTER
SUMPRODUCT
函数?
小弟不懂,
想请教高人帮忙指教一下公式好吗?
感激不尽啊.


2024年10月23日 星期三

idiocy

mykitami 发表于 2024-10-22 21:33
可以用条件格式公式
=--AK96

你好,
高人,
小弟想到
统计除开法定节假日加班之外的其他所有形式的加班小时数的公式:
考勤表BL6=SUMIF($D6:$AH6,"<>"&数据源!$K$13,$D7:$AH7)


请高人帮忙检查公式是否写的正确?
小弟验算了一下答案是对的上的.


但是统计考勤符号出现次数可以右拉下拉的公式还得请高人帮忙写一写,
这个小弟不会,
惭愧啊.

mykitami

idiocy 发表于 2024-10-23 12:50
非常感谢高人的纠正啊,
确实是AK9,
看花眼了,

问题六
没有必要再去引用数据源中的数据，因为你在AI4:BK5已经引用到考勤表了，在本表中引用不是列方便吗？这就也更方便公式的下拉右拉操作。
另外，你引用过来的数据是原顺序的，用一个公式整体就一下子搬过来了，公式仅在一个单元格中设置就行了，因为M365有自动溢出功能。
所以在考勤表AI4的公式是（先把AI4:BK5的所有数据清除）
=TRANSPOSE(HSTACK(数据源!$H$6:$H$34,数据源!$K$6:$K$34))
AI6公式
=IFERROR(1/COUNTIF($D6:$AH6,AI$5)^-1,"")

问题七
BL6公式
=SUM(SUMIFS($D7:$AH7,$D6:$AH6,{"<>","="}&$AP$5)*{1,-1})
如果想不显示0值，可以模仿我AI6公式的做法在公式前面加上     IFERROR(1/     后面加上   ^-1,"")   
即：
=IFERROR(1/SUM(SUMIFS($D7:$AH7,$D6:$AH6,{"<>","="}&$AP$5)*{1,-1})^-1,"")
同理
BM6公式
=IFERROR(1/(SUMIF($D6:$AH6,$AP$5,$D7:$AH7)*2)^-1,"")

公式已经在附件中写好了，请参照
有问题你就直接问，不用客气，很乐意为新手解答，我也正好温故知新。