安之若素藏书阁 / 电脑科技知识 / Excel考勤表,有自动变色和查询功能

0 0

   

Excel考勤表,有自动变色和查询功能

2020-03-28  安之若素...

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

01

日期和星期的填充

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

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

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

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

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

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

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

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

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

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

第2步 设置星期

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

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

第3步:屏蔽下月的日期

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

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

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

=MONTH(AF3)>MONTH(AC3)

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

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

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

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

考勤表的自动变色功能

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

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

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

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

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

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

制作步骤:

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

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

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

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

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

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

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

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

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

完成!

03

考勤表的查询功能

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

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

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

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

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

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

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

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

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

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

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

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

=ADDRESS(ROW(),COLUMN())

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

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

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

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

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

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

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

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

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

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

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

到此,公式设置完成。

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。如发现有害或侵权内容,请点击这里 或 拨打24小时举报电话:4000070609 与我们联系。

    猜你喜欢

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多