11. 自动标记迟到、早退公式:=IF (B2>TIME (8,0,0),"迟到","") 和 =IF (C2<time (18,0,0)," 早退 ","")< p="">
首先在表格后建立一个“迟到”列,输入公式“=IF (B2>TIME (8,0,0),"迟到","")”,再建立一个“早退”列,输入公式“=IF (C2<time (18,0,0)," 早退 ","")”。这里简单说明一下,time () 函数主要用于约束最终时间,当上班签到时间晚于约束值,或者下班签到时间早于约束值时,便会触发文字提示。< p="">
▲自动标记迟到、早退
12. 自动打序号公式:=ROW ()-1
首先点击序号列第一组单元格,输入公式 = ROW (),此时函数返回的是当前行数,根据实际行数计算(比如本文为“1”),发现两者差值为“1”。接下来对公式进行修改,将计算后的差值减到公式后方(),填充后即可实现自动打序号效果。
尽管自动打序号已经实现,但此时仍然不够智能。可以在此基础上再嵌套一个 IF () 函数,只有当右侧有数据时才会显示序号,没有的话直接显示空白。
▲自动序列
13. 去除数据有效性列表里的空值公式:=OFFSET ($O$6,,,COUNTA ($O$6:$O$19))
如果你经常使用“数据有效性”制作下拉列表,就会发现这样一个尴尬,当我们前期为序列留出大量空白时,下拉列表也会出现大量空白,日常操作很不方便。其实这个问题可以这样解决,首先点击“数据”→“数据验证”→“序列”,在“来源”框中填入公式。这里 COUNTA () 函数的作用,是求出当前数据源中的有效记录数,然后通过 OFFSET () 函数确定取值范围。由于公式限定了下拉列表的取值域,因此我们会得到一个完全没有空值的菜单。同时新记录也将自动添加到列表中,不会影响后续操作。
▲清除下拉列表里的空值