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

idiocy

mykitami 发表于 2024-10-21 08:23
哦，问题1我明白你的意思了，这样改一下
=SUM(ISNUMBER(FIND(CHAR(ROW(INDIRECT("65:90"))),D16:AH16))* ...

高人厉害啊,
修改过的公式现在求值都是对的了,
太厉害了,
向高人学习了啊.


让高人感到理解上的困惑小弟向高人道歉啊,
对不起.

idiocy

mykitami 发表于 2024-10-20 21:50
问题4
需要把你想显示出来的节假日全部列个清单出来
问题5没有看明白你的意思

你好高人,
不用全部显示出来,
是小弟没有表达清楚,
不好意思啊,
显示出来几个常用的法定假日就可以了,
元旦节
春节
清明节
劳动节
端午节
中秋节
国庆节
公历的节假日比较好算些,
因为日期都是固定的,
可以根据关键字在区域内查找在对应到日期单元格显示出来,
农历的节假日就不好算了,
像是
春节
端午节
中秋节
小弟不会这个,
每年农历的日期表小弟也做不出来,
我看到有的高人做的有万年历的表,
连每年的农历都做的出来,
只要选择年份和月份,
对应的农历表就自动的变化,
太厉害了这个,
小弟做不出来这个,
所以只能把公历的常用的节假日显示出来.

idiocy

mykitami 发表于 2024-10-21 09:06
问题四
我看到了你有一个节假日表，难道要把所有的这些节假日都显示出来？
问题五

小弟重做了一个
考勤表 对比参考用
不知道高人看的是否明白意思?


结合“数据源”表里面的“考勤符号”,
把对应的排班变成对应的符号表示,
如果没有的就用指定单元格内的内容表示.


考勤表D6=IFERROR(LOOKUP(9^9,FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","*节","法补","出勤半天数","出勤天数","迟到早退","旷工","加班","法假加班","公休","公休假","半公休假","调休假","加班补休","加班补半休","加班补欠休","加班补半欠休","加班法补休","出勤临班","出勤半班","法假","出差","事假","病假","工伤假","婚假","产假","陪护假","丧假","年假"},排班表!D9),{"√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","√","法休T","法休L","V半","DS","LE","AW","OT","法休T","休","O","√O","OLT","休L","半休L","OL","半OL","法休L","临班√","半班√","PH","BL","CL","SL","IL","WL","ML","PL","FL","AL"}),"")


小弟写的这个公式太长了,
而且有些还计算错误,
对不上手动填写的考勤符号,
所以想请高人帮忙优化修正一下公式,
请问可以吗?

mykitami

idiocy 发表于 2024-10-21 11:53
你好高人,
不用全部显示出来,
是小弟没有表达清楚,

排班表的D6单元格公式（先增加一个工作表“农阳历对照表”）
条件格式也设置好了，见附件
=IFERROR(LOOKUP(1,0/MMULT(1*ISNUMBER(FIND({"元旦节";"春节";"清明节";"劳动节";"端午节";"中秋节";"国庆节"},TRANSPOSE(FILTER(节假日!$F$6:$F$600,节假日!$D$6:$D$600=D7)))),ROW(INDIRECT("1:"&ROWS(FILTER(节假日!$F$6:$F$600,节假日!$D$6:$D$600=D7))))^0),{"元旦节";"春节";"清明节";"劳动节";"端午节";"中秋节";"国庆节"}),"")&" "&IFERROR(INDEX(节假日!$L$6:$L$15,MATCH(D7,INDEX(农阳历对照表!$A:$A,MATCH(SUBSTITUTE(节假日!$K$6:$K$15,"农历","二〇二四年"),农阳历对照表!$C:$C,)),)),"")

mykitami

idiocy 发表于 2024-10-21 11:54
小弟重做了一个
考勤表 对比参考用
不知道高人看的是否明白意思?

你都说了，你写的这个公式结果是错的，没有参考价值。
别人要修改的话，也要知道你的规则是什么，你要一条一条的全部写明白了，那怕写几十条规则，要写全了。

idiocy

mykitami 发表于 2024-10-21 17:40
排班表的D6单元格公式（先增加一个工作表“农阳历对照表”）
条件格式也设置好了，见附件
=IFERROR(LOO ...

原来如此原来如此,
就是高人写的这个农阳历对照表,
小弟在B站上面看到高人写的万年历也有这个对照表,
不过不知道是从哪里找来的,
高人的这个农阳历对照表写的很全,
有了这个就好写公式显示对应的节假日出来了,
佩服啊```
高人太厉害了.


弱弱地请问一下,
当小弟的“排班日期”选择了2025年或者是之后的年份,
对应的月份公历的节假日没有显示出来请问是怎么回事呢?
小弟需要修改公式的什么地方才可以正确对应节假日显示出来呢?


请问高人可以直接从选择的这个“排班日期”和“月份”关联公式的节假日是哪一年哪一月的吗?

idiocy

mykitami 发表于 2024-10-21 17:46
你都说了，你写的这个公式结果是错的，没有参考价值。
别人要修改的话，也要知道你的规则是什么，你要一 ...

太对不起高人了啊,
收到高人的消息后小弟又立马一个一个的修改了公式,
这下子全对了,
请高人参考优化修正啊.


先说明一下,
因为临时上班的排班
英文字母临班
也是属于出勤,
所以小弟的公式里面就用V替代符号了,
因为公式太长了改起来好麻烦好难得找对应关系,
所以就不改了.
“数据源”表里面的“英文字母临班”对应“临班√”.


小弟写的这个公式因为是用关键字去对应替代符号的方式,
缺点太多了,
公式太长了,
容易看花眼和不理解.
而且一个关键字对应一个替代的符号,
当关键字有变化时就替代不了符号了.
所以小弟想着用“数据源”表里面的“特殊日”对应“考勤符号”这种方式,
模糊的“特殊日”名称去对应后面的“考勤符号”或者是指定的“考勤符号”.


从A到Z的凡纯英文字母  对应  V
英文字母中秋节  对应  V
英文字母**节  对应  V
出勤天数  对应  DS
迟到早退  对应  LE
旷工  对应  AW
英文字母平时加班  对应  OT
英文字母法假加班  对应  法休T
公休  对应  休
公休假  对应  O
英文字母半公休  对应  √O
调休假  对应  OLT
补休  对应  休L
英文字母半补休  对应  半休L
英文字母加班补欠休  对应  OL
英文字母半补欠休  对应  半OL
法补  对应  法休L
英文字母临班  对应  临班√
英文字母半班  对应  半班√
法假  对应  PH
出差  对应  BL
事假  对应  CL
病假  对应  SL
工伤假  对应  IL
婚假  对应  WL
产假  对应  ML
陪护假  对应  PL
丧假  对应  FL
年假  对应  AL

mykitami

idiocy 发表于 2024-10-21 20:33
原来如此原来如此,
就是高人写的这个农阳历对照表,
小弟在B站上面看到高人写的万年历也有这个对照 ...

这几个阳历的节假日与年份无关，我直接把年份去掉了。但公式最后农历的"二〇二四年"是公式中输入的，也可以改为根据单元格的年份自动转化，但又要写一段代码，就更长了。
看到你用的是office2016版，所以高版本的函数没有用，实际上是可以简化的，比如用let函数等，这样可以简化代码，也可以将农历的年份自动随单元格年份的变化而变化。

=IFERROR(LOOKUP(1,0/MMULT(1*ISNUMBER(FIND({"元旦节";"春节";"清明节";"劳动节";"端午节";"中秋节";"国庆节"},TRANSPOSE(FILTER(节假日!$F$6:$F$600,TEXT(节假日!$D$6:$D$600,"mmdd")=TEXT(D7,"mmdd"))))),ROW(INDIRECT("1:"&ROWS(FILTER(节假日!$F$6:$F$600,TEXT(节假日!$D$6:$D$600,"mmdd")=TEXT(D7,"mmdd")))))^0),{"元旦节";"春节";"清明节";"劳动节";"端午节";"中秋节";"国庆节"}),"")&" "&IFERROR(INDEX(节假日!$L$6:$L$15,MATCH(D7,INDEX(农阳历对照表!$A:$A,MATCH(SUBSTITUTE(节假日!$K$6:$K$15,"农历","二〇二四年"),农阳历对照表!$C:$C,)),)),"")

高版本的
=LET(a,{"元旦节";"春节";"清明节";"劳动节";"端午节";"中秋节";"国庆节"},IFERROR(LOOKUP(1,0/BYROW(1*ISNUMBER(FIND(a,TRANSPOSE(FILTER(节假日!$F$6:$F$600,TEXT(节假日!$D$6:$D$600,"mmdd")=TEXT(D7,"mmdd"))))),SUM),a),"")&" "&IFERROR(INDEX(节假日!$L$6:$L$15,MATCH(D7,INDEX(农阳历对照表!$A:$A,MATCH(SUBSTITUTE(节假日!$K$6:$K$15,"农历","二〇"&CONCAT(TEXT(MID(RIGHT($AA$5,2),SEQUENCE(2),1),"[DBNum1][$-zh-CN]G/通用格式"))&"年"),农阳历对照表!$C:$C,)),)),""))

idiocy

mykitami 发表于 2024-10-21 22:12
这几个阳历的节假日与年份无关，我直接把年份去掉了。但公式最后农历的"二〇二四年"是公式中输入的，也可 ...

非常非常感谢高人的公式帮助,
这两天真的辛苦高人了,
为了小弟的这份考勤表来来回回不知道修改了多少次了,
而且还要去理解小弟的描述表达,
然后再转换自己的思维去实现小弟的求值效果,
小弟已经无法用言语来表达对高人的感激之情,
心里一直有股暖洋洋的感觉.


高人写的这两个公式非常的专业,
这得要积累多少知识与经验才能写得出啊,
小弟愚钝,
真是写不出这样思维的解题公式,
深感佩服,
也深感惭愧.


这两个公式小弟都试了一下,
=IFERROR
的公式需要修改末尾处中文年份"二〇二四年"就可以对应节假日显示出来了.


而
=LET
的公式就自动全部对应显示出来了,
实在是太厉害了,
小弟看到实现的效果心里一阵一阵的激动与欢喜,
真的非常非常感谢高人的公式指教与帮助,
这个帮助是莫大的.


不知道这个
Microsoft Office 软件：Microsoft Office Mondo 2016
版本算不算是Microsoft Office 365版本?

mykitami

idiocy 发表于 2024-10-22 10:47
非常非常感谢高人的公式帮助,
这两天真的辛苦高人了,
为了小弟的这份考勤表来来回回不知道修改了多 ...

有可能你有好几个版本的授权，最示的是2016，实际上是365
因为有些高级的函数在2016版是没有的，但你却能使用，说明你实际上用的是365版的功能
如：let  byrow等
以后提高问题，别在说2016了，直接在论坛你的信息中改为M365，这样方便大家解答