头部广告

4个经典又实用的Excel函数公式,动图演示,一学就会!

我是【桃大喵学习记】,欢迎大家关注哟~,每天为你分享职场办公软件使用技巧干货!

今天跟大家分享的是4个经典又实用的Excel函数公式,动图演示,一学就会,可以直接套用,建议收藏备用!

1、计算休假天数

如下图,我们需要根据上班天数算出可休假天数,规则是:上班达到5天休息1天,上班10天休息2天,上班15天休息3天,也就是连续上班5天可以休息一天。

在目标单元格中输入公式:

=INT(B2/5)

点击回车,下拉填充数据即可

1.gif

解读:

使用INT函数可以舍去小数,上班天数÷5,舍去小数就是可以休息的天数。当然如果上班天数和休息天数有多个规则,可以再结合IF函数或者IFS函数进行判断即可。

2、根据身份证号提取生肖

如下图所示,A列是身份证号,在B列提取生肖。

在目标单元格中输入公式:

=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(B3,7,4),12)+1,1)

点击回车,下拉填充数据即可

2.gif

解读:

公式主要是巧用利用了MID函数根据年份来提取对应的生肖。

第1参数:字符串就是"猴鸡狗猪鼠牛虎兔龙蛇马羊",这12生肖;

第2参数:开始位置MOD(MID(B3,7,4),12)+1,首先用MID函数从身份证中提前出生年份,然后再使用MOD函数÷12取余数。因为12生肖是循环的,如果余数为零,那就从第一个生肖位置开始取值,所有后面要+1,后面的以此类推;

第3参数:字符个数就是取1个。

当然,上面的公式大家可以自己套用,只需把里面参数改成自己的即可。

3、批量计算男女退休日期

如下图所示,这一个员工信息表,我们根据出生日期计算每个人退休日期(备注:男60岁退休,女55岁退休)

在目标单元格中输入公式:

=EDATE(C3,IF(B3="男",60,55)*12)

然后点击回车,下拉填充数据即可

3.gif

解读:

①公式中首先通过IF(B3="男",60,55)*12判断根据不同性别返回不同的月数,如果是男返回60,否则返回55,最后再乘以12就是对应的退休月份数。

②然后利用EDATE函数把出生日期作为开始日期,把退休年龄转换成月份做为第二参数,这样就能计算出退休的日期。

4、对数据随机分组

如下图所示,左侧是人员名单,一共18个人,我们随机分成3组,每组6人。

直接在目标单元格中输入公式:

=WRAPROWS(SORTBY(A2:A19,RANDARRAY(ROWS(A2:A19),1)),6,"")

然后点击回车,即可获取分组数据,按快捷键【Ctrl+Alt+F5】或者F9键可以刷新,重新随机获取分组信息。

4.gif

解读:

上面公式中分别使用到了ROWS函数、RANDARRAY函数、SORTBY函数、WRAPROWS函数,下面我们一一解读。

①公式中ROWS(A2:A19)就是获取名单数据区域一共有多少行,就是有多少个姓名,获取结果为18。

5.gif

ROWS函数介绍

功能:主要是计算单元格行数。

语法:=ROWS(数组)

②然后使用RANDARRAY(ROWS(A2:A19))生成由18个随机数组成的数组。

6.gif

RANDARRAY函数介绍

功能:RANDARRAY函数主要是随机返回一个数组的函数。

语法:=RANDARRAY([行数],[列数],[最小数],[最大数],[整数])

③接着利用

SORTBY(A2:A19,RANDARRAY(ROWS(A2:A19),1))

再对名单数据区域进行升序排序,第一参数是A2:A19名单区域作为排序的区域;第二参数是以RANDARRAY(ROWS(A2:A19)生成的18个随机数为排序依据,第三参数是1,表示是按升序排序。

7.gif

SORT函数功能介绍

功能:SORT函数主要用来对某个区域或数组的内容进行排序。

语法:=SORT(数组,排序依据,排序顺序,按列)

④最后使用WRAPROWS函数,再把重新排序后的一列名单数据区域,转成6列,也就是3行6列。

WRAPROWS函数介绍

功能:将一行或者一列数据转换为多行数据

语法:=WRAPROWS(向量,列数,[异常返回值])

第一参数:向量就是要转换的将一行或者一列数据

第二参数:列数就是要把数据转换成几列显示

第三参数:就是出现异常时返回的值,默认可以忽略

因为使用随机数排序,每次刷新Excel表格或者重新打开Excel文件生成的分组顺序都是不一样的。如果我们想固定分组顺序,我可以使用选择性粘贴,把生成的分组信息转换成数值固定下来。

操作方法:

首先选中生成的名单信息,按【Ctrl+C】复制→然后点击右键,选择粘贴为数值即可,这样分组顺序就固定下来了。

8.gif

以上就是【桃大喵学习记】今天的干货分享~觉得内容对你有帮助,记得顺手点个赞哟~。我会经常分享职场办公软件使用技巧干货!大家有什么问题欢迎留言关注!


底部广告