|
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()))&'' 到此，公式设置完成。 |
|
|
