这个考勤表太牛了!有自动变色和查询功能(附下载链接) | 您所在的位置:网站首页 › 动态考勤表格制作excel教程如何突出节假日 › 这个考勤表太牛了!有自动变色和查询功能(附下载链接) |
再选中日期行,打开单元格设置窗口 - 数字 - 自定义,右侧框中输入字母 d ( Day的缩写,日期的自定义代码,代表天数) 小编已经做好了一份供大家下载,请扫码下方公众号关注后回复“考勤记录表”下载学习。 智能Excel考勤表带公式免费下载 (无套路,无需分享,转发) 扫码回复“ 考勤记录表 ” 即可下载 智能Excel考勤表 第2步设置星期 在D5中输入公式=D3,向右复制公式。然后输入自定义格式代码 AAA (日期显示为星期的自定义代码) 第3步:屏蔽下月的日期 当本月天数小于31天时,最后几列为显示下月的日期。 屏蔽方法:选取后3列,条件格式 - 新建规则 - 使用公式... - 输入公式 =MONTH(AF3)>MONTH(AC3) 公式说明:用最后三列的月份(month函数可以取得日期所在月份)和前面3列日期的月份进行对比作为条件。 再点【格式】按钮打开单元格格式设置窗口 - 数字 - 自定义 - 在右侧框中输入3个分号 ;;; ( 英文状态) 可以自动更新的日期和星期设置完成。 小编已经做好了一份供大家下载,请扫码下方公众号关注后回复“考勤记录表”下载学习。 智能Excel考勤表带公式免费下载 (无套路,无需分享,转发) 扫码回复“ 考勤记录表 ” 即可下载 智能Excel考勤表 0 2 考勤表的自动变色功能 如果遇到节假日,颜色能否自动显示,就如日历中看到的: 于是,兰色就对条件格式进行了调整,结果显示帅极了:不但可以变色,而且可以在日期左上角显示“休”和“班”字。 是不是很想知道是怎么实现的,下面就兰色一起做吧。 制作步骤: 1、在考勤表旁输入节假日和加班日期 2、在考勤表添加一空行,并输入公式,然后把字体设置为红色。 =IFERROR(VLOOKUP(B5,$AK:$AL,2,),"") 2、选取考勤表,开始 - 条件格式 - 新建规则 - 添加公式: =OR(B$4="休",AND(WEEKDAY(B$5,2)>5,B$4"班")) 公式原理:根据第4行的文字和用weekday提取的星期数,判断是否为休息日。 完成! 小编已经做好了一份供大家下载,请扫码下方公众号关注后回复“考勤记录表”下载学习。 智能Excel考勤表带公式免费下载 (无套路,无需分享,转发) 扫码回复“ 考勤记录表 ” 即可下载 智能Excel考勤表 0 3 考勤表的查询功能 先看查询效果:根据选择的月份不同,生成对应月份的考勤表: 其实有很多Excel用户都想实现这样的查询功能,只要变换查询的关键信息,就可以生成对应的表格。 做这样的表是不是很复杂?需要用到很高深的Excel功能,难道是传说中的VBA功能? 你想多了,做这样的查询表其实只需要一个公式。比如今天的考勤表,它的查询公式为: 虽然只是一个公式,但看起来有些复杂,大部分新手估计看不太懂。所以兰色有必要剖析一下这个它。 我们要想根据G3单元格的日期从对应月份的工作表中返回考勤信息,就需要把 日期和工作表名关联起来。所以公式用 Text函数从G3中提取年月( G3中看似是年月格式,其实是包含日的),以和工作表名称保持一致。 工作表名有了,接下来生成单元格地址。由于所有考勤表格式完成一致,所以总表的单元格(如A7)要提取的也是各个表A7的内容。也就是说接下来要自动生成公式所在单元格的地址( 如A7中生成地址A7),所以兰色用了: =ADDRESS(ROW, COLUMN) row和Column分别返回公式所在单元格的行、列数,然后用Address(行数,列数)生成单元格地址。 它和已生成的工作表名连在一起,正好生成了完成的引用“字符串” 公式生成的字符串只是“字符串”,并不能从对应表中提取数据,所以用Indirect函数把它转换为可以提取值的引用。 好象公式设置好了,但当向下复制公式时,你就会发现当被提取的值为空时显示0,这显示不是我们想要的。 其实我们用Vlookup函数提取时也遇到这样的问题。怎么把0值转换为空白,高手们是这样做的,在公式后面添加 &"",即: = INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW,COLUMN)) &"" 到此,公式设置完成。 小编已经做好了一份供大家下载,请扫码下方公众号关注后回复“考勤记录表”下载学习。 智能Excel考勤表带公式免费下载 (无套路,无需分享,转发) 扫码回复“ 考勤记录表 ” 即可下载 智能Excel考勤表 VIPCMA ©版权归原作者所有,由于部分文章无法鉴别有真实法定的原创者,如对版权有异议,请联系后台议定删除。 ▨ 本文来源:作者:赵志东、李杰,由“管理会计知识汇”编辑整理 点击返回搜狐,查看更多 |
CopyRight 2018-2019 实验室设备网 版权所有 |