头部广告

Excel这3个短小精悍超好用的函数公式,你都会用吗?

在职场工作中,对Excel表格数据进行处理分析是必备技能。如果我们学会熟练使用函数公式,可以快速提高工作效率,达到事半功倍的效果。今天就跟大家分享一下WPS中3个超好用的函数公式,建议收藏备用哦。

一、FREQUENCY函数:分段统计好帮手

FREQUENCY函数主要是计算值在某个范围内出现的频率, 然后返回一个垂直的数字数组。

语法结构=FREQUENCY(一组数值,一组间隔值)

应用实例:

如下图所示,这个表格是学员信息表格,我们根据年龄来统计不同年龄段的人数,分三个年龄段来统计人数“18-26”、“27-35”、“36以上”,这三个年龄段的分界点是26和35。

1.jpg

1、先选中存放返回值的单元格区域G2:G4单元格,然后在编辑栏输入公式=FREQUENCY(B2:B8,{26,35}),如下图所示

2.gif

2、最后按「Ctrl+Shift+Enter」键快捷即可获取数值,如下图所示

3.gif

公式解释:

公式=FREQUENCY(B2:B8,{26,35})

1、公式就是将B2:B8内的数字分为三段并统计个数:年龄“<=26”的有3人;年龄“>26”且“<=35”的有1人;年龄“>35”的有3人。

2、FREQUENCY函数返回的是一个垂直数组,所以我们需要先选中存放FREQUENCY函数返回值的单元格区域G2:G4单元格。

3、因为WPS Office暂不支持动态数组,故必须使用「Ctrl+Shift+Enter」键快捷设置为数组形式,其他方式均仅返回为单个数值。

二、TEXTJOIN函数:实现多对多查询

提到TEXTJOIN函数到家都会文本链接函数,使用分隔符链接列表或文本字符串区域。但是我们可以使用这个函数实现合并同类项多对多查询。

语法结构=TEXTJOIN(分隔符, 忽略空白单元格, 字符串1…)

应用实例:

如下图所示,这是一个学生所属班级信息表格,我们要根据左侧的表格数据找到【一班、二班、三班】所对应的学生名称,并且把这些名称在右侧表格中统计出来,并且每个学生名称之间用逗号隔开。要想实现这个实现合并同类项多对多查询我们需要借助IF函数,先使用IF函数从左侧表格中找到【一班、二班、三班】对应的所有学生名称,然后将其放在TEXTJOIN的第三参数中即可,具体操作如下:

3-1.jpg

1、先选中右侧表格“学生姓名”下面的第一个单元格,如下图所示

4.jpg

2、然后输入公式

=TEXTJOIN(",",TRUE,IF(A2:$A$11=D2,B2:$B$11,"")),如下图所示

5.jpg

3、最后按「Ctrl+Shift+Enter」键快捷即可获取数值,然后下拉填充其它数据即可,如下图所示

6.gif

公式解释:

公式=TEXTJOIN(",",TRUE,IF(A2:$A$11=D2,B2:$B$11,""))

1、其实,这个公式的关键就是IF函数,通过IF函数判断所属班级,如果条件成立就返回对应的学生名称,如果条件不成立就返回空值,并且IF函数中的判断条件和返回真值引用的单元格都是要绝对引用,就是选择单元格后按一次F4键。

2、在WPS中输入公式后,一定要按Ctrl+Shift+回车键才可以,如果只按回车键会返回所有的员工名称。我们可以看到这个公式跟以往的公式有所不同,公式外面有{},这个可不是手工写的,而是输入公式后,按Ctrl+Shift+Enter三个组合键结束,这就是传说中的数组公式。

三、SUMPRODUCT函数:多产品合计金额计算

SUMPRODUCT函数主要是将数组中对应的元素相乘,再求和。

语法结果=SUMPRODUCT(array1,array2,array3, ...)

Array1,array2,array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。

应用实例:

如下图所示,每天根据商品单价和销量,统计出合计金额。

1、首先,选中销售额下面的第一个单页格,然后输入公式=SUMPRODUCT(),如下图所示

7.jpg

2、为SUMPRODUCT函数选择第一个数组参数,就是所有商品单价,现在公式是=SUMPRODUCT(B2:F2),如下图所示

8.jpg

3、用英文状态下的逗号隔开,选择第二个数组参数,就是每天商品销售数量,现在公式是=SUMPRODUCT(B2:F2,B3:F3),如下图所示

9.jpg

10.jpg

4、对公式第一个参数就是商品单价需要进行绝对引用,选中B2:F2按一次F4键进行绝对引用,最终公式=SUMPRODUCT($B$2:$F$2,B3:F3),如下图所示

11.gif

5、最后下拉公式,填充下面的数据即可,如下图所示

12.gif

底部广告