Excel考勤表太牛了！有自动变色和查询功能！

01

日期和星期的填充

制作考勤表说容易也容易，随便画个表格，输入年份日期、星期，然后再手工划勾就可以用了。但要介绍的是更智能的考勤表，如修改考勤表月份，下面的日期和星期都会自动变化：

实现日期和星期自动更新有很多种方法，大多是用复杂的函数公式。许多同学看到长公式就头晕。所以先介绍一个简单公式+技巧的方法。

第1步 设置可以自动变化的日期

在D3中输入公式=A2， E3中输入公式= D3+1，然后复制E3公式到最右。

财务工作中，你是否遇到焦头烂额不知如何解决的问题呢？

再选中日期行，打开单元格设置窗口 - 数字 - 自定义，右侧框中输入字母 d ( Day的缩写，日期的自定义代码，代表天数)

第2步 设置星期

在D5中输入公式=D3,向右复制公式。然后输入自定义格式代码 AAA （日期显示为星期的自定义代码)

第3步：屏蔽下月的日期

当本月天数小于31天时，最后几列为显示下月的日期。

屏蔽方法：选取后3列，条件格式 - 新建规则 - 使用公式... - 输入公式

=MONTH(AF3)>MONTH(AC3)

公式说明：用最后三列的月份（month函数可以取得日期所在月份）和前面3列日期的月份进行对比作为条件。

再点【格式】按钮打开单元格格式设置窗口 - 数字 - 自定义 - 在右侧框中输入3个分号 ;;; （英文状态)

可以自动更新的日期和星期设置完成。

考勤表的自动变色功能

如果遇到节假日，颜色能否自动显示，就如日历中看到的：

于是，兰色就对条件格式进行了调整，结果显示帅极了：不但可以变色，而且可以在日期左上角显示“休”和“班”字。

是不是很想知道是怎么实现的，下面就兰色一起做吧。

制作步骤：

1、在考勤表旁输入节假日和加班日期

2、在考勤表添加一空行，并输入公式，然后把字体设置为红色。

=IFERROR(VLOOKUP(B5,$AK:$AL,2,),"")

2、选取考勤表，开始 - 条件格式 - 新建规则 - 添加公式：

=OR(B$4="休",AND(WEEKDAY(B$5,2)>5,B$4<>"班"))

公式原理：根据第4行的文字和用weekday提取的星期数，判断是否为休息日。

完成！

03

考勤表的查询功能

先看查询效果：根据选择的月份不同，生成对应月份的考勤表：

其实有很多Excel用户都想实现这样的查询功能，只要变换查询的关键信息，就可以生成对应的表格。

做这样的表是不是很复杂？需要用到很高深的Excel功能，难道是传说中的VBA功能？

你想多了，做这样的查询表其实只需要一个公式。比如今天的考勤表，它的查询公式为：

=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))&""

虽然只是一个公式，但看起来有些复杂，大部分新手估计看不太懂。所以兰色有必要剖析一下这个它。

我们要想根据G3单元格的日期从对应月份的工作表中返回考勤信息，就需要把日期和工作表名关联起来。所以公式用Text函数从G3中提取年月（G3中看似是年月格式，其实是包含日的），以和工作表名称保持一致。

=TEXT($F$3,"yyyy年m月")

工作表名有了，接下来生成单元格地址。由于所有考勤表格式完成一致，所以总表的单元格（如A7）要提取的也是各个表A7的内容。也就是说接下来要自动生成公式所在单元格的地址（如A7中生成地址A7），所以兰色用了：

=ADDRESS(ROW(),COLUMN())

row（）和Column（）分别返回公式所在单元格的行、列数，然后用Address（行数,列数）生成单元格地址。

它和已生成的工作表名连在一起，正好生成了完成的引用“字符串”

=TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN())

公式生成的字符串只是“字符串”，并不能从对应表中提取数据，所以用Indirect函数把它转换为可以提取值的引用。

=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))

好象公式设置好了，但当向下复制公式时，你就会发现当被提取的值为空时显示0，这显示不是我们想要的。

其实我们用Vlookup函数提取时也遇到这样的问题。怎么把0值转换为空白，高手们是这样做的，在公式后面添加 &""，即：

=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))&""

到此，公式设置完成。

来源：作者：赵志东、李杰

在这里相信有许多想要学习会计的同学，大家可以关注小编头条号，

下方评论区留言：想要学习，并收藏本文；私信小编：学习

即可领取一整套系统的会计学习资料！还可以免费试学会计课程15天！

因领取人数太多，不能及时回复，请大家耐心等待。。。。