用excel制作考勤表,星期随着时间自动变化。有需要的可以收藏一下
效果动图
1、首先插入两个表单控件,用来控制年份及月份
开发工具→插入→选择相应控件,设置控件格式,最大值于最小值就是年份区间,单元格链接选取一个空单元格,后面公式要使用到这个单元格,这边选的是$C$1单元格。用同样的方法用制作一个月份的控件,然后排一下版。
标题可以用公式显示:=TEXT(DATE(C1,F1,1),'yyyy年m月员工考考勤表')
C1为年份控件单元格链接,F1为月份控件单元格链接
2、设置日期跟星期
在日期相应单元格中输入公式:=TEXT(IF(MONTH(DATE($C$1,$F$1,COLUMN(A1)))=$F$1,DATE($C$1,$F$1,COLUMN(A1)),''),'D')
解析:DATE($C$1,$F$1,COLUMN(A1),用C1,F1,A1构成一个新的日期
MONTH(DATE($C$1,$F$1,COLUMN(A1)),提取出新的日期的月份
如果提取的月份等于F1单元格,则等于提取的月份,否则就等于空值
用TEXT函数将数值转化为自己想要的文本格式,D表示为将日期显示为不带前导零的数字
同样的方式在星期相应单元格中输入公式:=TEXT(IF(MONTH(DATE($C$1,$F$1,COLUMN(A1)))=$F$1,DATE($C$1,$F$1,COLUMN(A1)),''),'AAA')
解析:公式于日期公式一样,TEXT函数,AAA将日期转为中文星期的格式
3、突出星期六及星期天在表中的位置,用条件格式
选中整个日期区域→条件格式→新建条件格式→使用公式确定要设置格式的单元格,在单元格中输入公式=OR(C$7='六',C$7='日'),此处一定要绝对引用行。然后选择相应的填充格式。
4、在考情表后面加上一个简单的统计表,数字形式的表示出员工的出勤情况
用countif函数统计出相依状态在整个月的出现的次数。
5、根据统计的数据最一个简单的图表
新建一个工作簿,C3单元格设置数据有效性,为前面考勤表的考勤人员,运用公式制作一个动态下拉菜单,公式为=OFFSET(主页!$B$8,,,COUNTA(主页!$B$8:$B$27),) 前几篇有讲过动态二级下来菜单的教程,这边就不细讲。
通过C3单元格关键字vlookup相应数据到表中,通过表中的数据新建一张简单的图表。
6、表格整体就这样,然后根据自己的审美,通过排版表格,让表格别的更美。
差不多就这些,如果喜欢的话可以关注我,要表格的话可以私信我。期待你的关注!谢谢!
联系客服