如今工作入职场会Excel可能是必不可少一项技能,你是否时长怀疑自己操作Excel效率

为什么会比别人慢那么多呢? 那是人家会灵活运用Excel函数,而你对Excel函数你知多少?

我相信很多小伙伴都是办公的,每当我发布办公相关软件,下载量是最大的,

所以本次给小伙伴们整理必学的几招Excel函数,让你成为同事中最靓的仔!

1. 身份证号提取性别

公式:= IF (MOD (MID (B2,17,1),2)=1,"男","女")

首先通过 MID () 函数提取身份证号中的第 17 位,然后通过 MOD () 函数判断奇偶性,

其中奇数为男,偶数为女。最终在 IF () 函数的配合下,即可得到性别值。


2. 身份证号提取生日

公式:=TEXT (MID (B2,7,8),"0000-00-00") 

首先通过 MID () 函数提取身份证号中的第 7-14 位,然后使用 TEXT () 函数对结果格式化

(即转换成“xxxx-xx-xx”的样式),最终便得到了我们平时习惯的生日信息。 

注:身份证号码中第 7-14 位为出生日期。


3. 计算年龄、司龄

公式:=YEAR (TODAY ())-MID (B2,7,4) 

首先通过 TODAY () 函数获得当前日期,然后使用 YEAR () 函数提取日期的年份值。

接下来通过 MID () 函数获取员工出生年份,两者之差即为年龄。类似的方法

也可用在司龄计算上,只要将生日替换成入职日期即可。


4. 防止身份证号多输少输

做法:通过“数据有效性”限定单元格长度 选中身份证号列,点击“数据”→“数据有效性”,

将“文本长度”指定为“18”位。当录入的身份证号码超过或不足 18 位时,

Excel 便会自动提醒,以降低前端录入可能导致的错误。


注:通过将“数据有效性”→“出错警告”中的样式修改为“警告”,可以实现只提醒不禁止效果。

5. 手机号码自动加“-”

做法:通过“单元格格式”,修改显示格式 右击手机号码字段,按下 Ctrl+1 进入单元格格式面板,

点击最下方“自定义”,输入“000-0000-0000”。确定之后,手机号码即以“XXX-XXXX-XXXX”格式显示。


注:这种方法只是修改了字段显示样式,并没有修改实际数据,

因此后续仍然可以进行统计,这也是和 Replace () 函数最大一个区别。

6. 合同到期前自动提醒

公式:=IF (G7-TODAY ()<30,"合同应续签","") 

首先建立“状态”列,输入公式“=IF (G7-TODAY ()<30,"合同应续签","")”。

当合同到期日低于设定值(本例中为“30”)时,便自动显示“合同应续签”。

此外也可将提醒天数单独设成单元格,通过公式调取单元格数值,以方便用户自行修改提醒期限。


7. 避免录入重复值

公式:=COUNTIF (B:B,B1)=1 

选中数据列,点击“数据”→“数据工具”→“数据验证”,将验证条件改为“自定义”,

然后输入公式“=COUNTIF (B:B,B1)=1”。这里 COUNTIF () 函数的作用,

是判断 B 列中是否有重复记录(如果有则值值一定大于 1),如果有中止录入,如果没有继续录入。


如果录入时出现重复数值将禁止录入

8. 自动标亮周末

公式:=WEEKDAY (E$6,2)>5 首先建立星期行,选中考勤表里的数值区域,

点击“开始”→“样式”→“条件格式”→“新建规则”,然后在选框内输入公式“=WEEKDAY (E6,2)>5”,

最后选中 E6,按两下 F4 键锁定行不锁定列(即转换成 E$6),再设置一个特别的颜色即可。

这里 WEEKDAY () 函数的作用是判断当前列是否大于 5,当 WEEKDAY () 结果 > 5 时(即周六、周日),

条件成立,表格自动为该列刷上颜色,不成立时不处理,从而最终形成周末自动上色效果。


9. 让表格自动拓展

方法:Ctrl+T 转成“超级表” 想让表格自动扩展,可以利用 Excel 里的“超级表”功能。

具体方法是:选中数据区域,按下 Ctrl+T,然后修改“表格工具”→“表格样式”为“无”

(即不使用超级表默认样式)。右击隐藏新弹出的筛选行,即可实现表格的自动扩展。


10. 隔行换色

公式:=MOD (ROW (),2)=0 

隔行换色有两种方法,一是转换成“超级表”(Ctrl+T),二是借助公式与条件格式配合完成。

以公式法为例,首先选中数据区域,点击“开始”→“样式”→“条件格式”→“新建规格”→“使用公式

确定要设置格式的单元格”,然后在选框内输入公式“=MOD (ROW (),2)=0”。这里 ROW () 函数的

作用是获取当前行号,和 2 取余后,便得到了 1、0、1、0、1、0…… 这样的数列。

由于公式整体位于条件格式内,因此当行号取余结果 = 0 时,条件成立,

表格自动为该行刷上颜色,不成立时不处理,最终形成了隔行换色的效果。


公式 + 条件格式实现“隔行换色” 注:与传统的格式刷法相比,

超级表和公式法都可以实现周末自动填色。且后期无论如何添删,都不会导致色条出现混乱。

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 () 函数确定取值范围。

由于公式限定了下拉列表的取值域,因此我们会得到一个完全没有空值的菜单。

同时新记录也将自动添加到列表中,不会影响后续操作。


清除下拉列表里的空值

14. 分级考核统计

公式:=LOOKUP (J6,N6:N9,M6:M9) 传统分级统计是通过 COUNT () 函数与 IF () 函数配合计算,

虽然简单,可一旦条件过多,就会导致公式异常复杂。类似情况,其实也能借助 LOOKUP () 函数解决。 

首先建立一个分级副表,左侧为等级,右侧为达到该等级的考核线。接下来在评级框内输入公式“

=LOOKUP (J6,N6:N9,M6:M9)”,将两组取值域按 F4 键全部转为绝对地址。

这里 LOOKUP () 的作用是通过目标值,直接到副表中查找对应等级,

相比 IF () 函数显得精简很多。而且这样处理后的表格,也方便用户随时调整考核线。


简单的分级考核公式 

注:使用 LOOKUP () 函数时,要注意副表考核值必须由小到大排列,否则将导致结果异常。


评论(0条)

刀客源码 游客评论