老板分分钟主动加薪!超实用Excel函数你知多少
分类:软件教程
时间:2023-11-21 22:11:52
[PConline 技巧]刚进职场的小伙伴,很多时候都要面临业务与环境的双重考验。如何在众人间脱颖而出,成为夜空中最亮的那个仔?今天这篇文章,将带您快速一览人事岗必学的几招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,然后修改“表格工具”→“表格样式”为“无”(即不使用超级表默认样式)。右击隐躲新弹出的筛选行,即可实现表格的自动扩展。
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),"早退","")
首先在表格后建立一个“迟到”列,输进公式“=IF(B2>TIME(8,0,0),"迟到","")”,再建立一个“早退”列,输进公式“=IF(C2<TIME(18,0,0),"早退","")”。这里简单说明一下,TIME()函数主要用于约束终极时间,当上班签到时间晚于约束值,或者放工签到时间早于约束值时,便会触发文字提示。
自动标记迟到、早退
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()函数时,要留意副表考核值必须由小到大排列,否则将导致结果异常。
写在最后
以上这些,是人事工作者天天都要碰到的,很多复杂的操纵实在往往来源于日常积累。假如以上这些,对您的工作有所帮助的话,就请给小编点个赞吧!
相关应用
相关文章推荐
-
老板分分钟主动加薪!超实用Excel函数你知多少
刚入职场的小伙伴,很多时候都要面临业务与环境的双重考验。如何在众人间脱颖而出,成为夜空中最亮的那个仔?今天这篇文章,将带您快速一览人事岗必学的几招Excel函数。难度不高,却...[详情]
-
excel2019怎么使用函数 操纵方法
Excel2019是一款方便易用的电脑办公软件。该软件界面美观,能够帮助用户轻松进行编辑表格,在使用excel处理文档数据时,我们有时会使用到函数,要是不知道怎么操作的话可以看看下面的...[详情]
- 时隔三年终于更新!一款真正良心的装系统神器
- Excel INFO函数怎么使用?INFO函数使用方法
- Excel ISBLANK函数怎么使用?ISBLANK函数使用方法
- Excel weeknum函数怎么使用?weeknum函数使用方法
- Excel怎么统计相同名字的数目?统计相同名字数目方法
- 学会这个立马加薪!10组最该学会的Excel小技巧
- Excel怎么合并工作表?Excel合并工作表的方法
- Excel核对两列数据是否一致的方法先容
同类排行
最新软件教程
- 免费观看nba直播软件有哪些下载_nba在线直播免费观看不用付费的
- 2023抖音app企业账号怎么开通_抖音企业号有什么好处和坏处
- 百度地图3d实景导航怎么打开哪里设置_百度3d地图实景地图怎么使用教程分享
- 百度网盘AI助手在哪打开怎么用_百度云盘ai助手用法详细介绍
- 书旗小说怎么关闭会员自动续费功能_书旗小说连续包月会员如何解除取消
- 苹果手机快手怎么取消免密支付功能_iphone关闭快手免密支付方式教程
- 手机qq邮箱怎么发文件给别的邮箱_qq邮箱怎么发压缩包给别的邮箱方法
- 上观新闻手机端怎么注销账号_上观新闻注销账号操作步骤一览
- 咪咕视频倍速播放怎么设置_咪咕视频怎样倍速播放方法
- 夸克浏览器怎么开启电脑模式_夸克浏览器把网页设置成电脑版