今天跟大家分享的是Excel中6个使用频率极高的函数,公式可以直接套用,快速提高工作效率,新手必学,职场必备!(篇幅有点长,大家可以收藏备用)

一、SUMPRODUCT函数

SUMPRODUCT函数介绍

功能:SUMPRODUCT函数主要功能是返回相应的数据或区域乘积的和。

语法:=SUMPRODUCT(数组1,数组2,数组3, ...)。

解读:

①、数组1,数组2,数组3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。

②、数组参数必须具有相同的维数,否则,函数 SUMPRODUCT 将返回错误值 #VALUE!。

③、函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

1、基本用法,求乘积之和

如下图所示,这是一个商品单价和数量信息表,需要计算总价格。

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

=SUMPRODUCT(B2:B7,C2:C7)

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

2、单条件求和

如下图所示,对性别为“女”的员工销售业绩求和

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

=SUMPRODUCT((D2:D6="女")*F2:F6)

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

3、多条件求和

如下图所示,对“业务部”考核成绩大于85的员工销售业绩求和

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

=SUMPRODUCT((C2:C6="业务部")*(E2:E6>80)*F2:F6)

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

二、FILTER函数

FILTER函数介绍

功能:FILTER是基于定义的条件筛选一系列数据的函数,它由数组,包括,空值三个参数所构成。

语法:=FILTER(数组,包括,空值)

第1个参数【数组】:就是筛选区域

第2个参数【包括】:就是筛选列=筛选条件

第3个参数【空值】:可以忽略,这个参数就是如果出现错误值可以设置返回信息

备注:FILTER函数需更新至WPS Office最新版本使用

1、单条件查找

如下图所示,我们根据左侧表格数据,在右侧表格根据销售员姓名查找对应的销售金额。

在目标单元格输入公式:

=FILTER(C4:C12,B4:B12=F4)

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

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

2、多条件查询

如下图所示,还是用上面的实例,只是右侧查询表格是根据“姓名”和“部门”两个条件查询,横向返回查询结果。

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

在目标单元格输入公式:

=FILTER(B2:C10,(A2:A10=F3)*(D2:D10=G3),"无数据")

然后点击回车即可

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

三、SORT函数

SORT函数介绍

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

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

第1参数:「数组」指的是要排序的区域或数组

第2参数:「排序依据」为以某行或列为依据进行排序

第3参数:「排序顺序」指的是所需的排序顺序,1表示升序排序,-1表示降序排序

第4参数:「按列」是一个逻辑值,输入True表示按列排序,输入False表示按行排序,默认按行排序。

1、按条件排序

如下图所示,我们想对左侧表格数据以成绩来排序,按成绩从高到低排序。

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

在目标单元格输入公式:

=SORT(A1:E10,2,-1)

点击回车即可获取排序数据。

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

四、XLOOKUP函数

XLOOKUP函数介绍

功能:XLOOKUP函数是一个查找函数,在某个范围或数组中搜索匹配项,并通过第二个范围或数组返回相应的项,默认情况下使用精准匹配。

语法:=XLOOKUP(查找值,查找数组,返回数组,未找到值,匹配模式,搜索模式)。

第一参数:想要查找值,可以是单个值或者数组值

第二参数:想要在那个数据区域中查找

第三参数:要返回的数据区域

第四参数(可选):未找到值,就返回第四参数,省略它函数默认返回#N/A这个错误值

第五参数(可选):匹配模式,可填0、1、-1、2

参数为:0 ,精确匹配,找不到结果,返回 #N/A这个错误值,这是默认选项。

参数为:-1,精确匹配找不到结果,返回下一个较小的项。

参数为:1,精确匹配,找不到结果,返回下一个较大的项。

参数为:2 ,通配符匹配

第六参数(可选):指定匹配模式,可填1、-1、2、-2

参数为:1,从上到下进行数据查询, 这是默认选项。

参数为:-1,从最后一项到第一项进行搜索。

参数为:2,二分搜索(升序排序) 。

参数为:-2,二分搜索(降序排序)

以上就是XLOOKUP的所有参数,函数参数虽然比较多,但是我们在平时使用这个函数时一般只需设置前三个函数即可。

1、基本用法

如下图所示,我们想查询赵飞的基本工资,我们可以直接使用公式:

=XLOOKUP(G3,A2:A8,D2:D8)

在这里我们可以把函数的第四、第五、第六参数都省略掉,我们在平时使用这个函数时一般只需设置前三个函数即可。

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

2、逆向查找匹配

如下图所示,我们想通过员工姓名查找到员工的编号,也就是从右往左逆向查询,我们只需要输入公式

=XLOOKUP(H3,B2:B8,A2:A8)

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

3、多条件查询

如下图所示,这是一个学生成绩表,需要根据姓名查询学生成绩,但是姓名有重复。为了避免有重复值我们需要通过【姓名】和【班级】这两个条件来查询成绩,如下图所示

在目标单元格中输入公式:=XLOOKUP(E3&F3,A2:A7&B2:B7,C2:C7)

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

4、通配符模糊查找

如下图所示,我们需要根据公司简称,查找出“公司名称”包含公司简称的信息,然后返回对应的“合同金额”,只需在目标单元格中输入公式:

=XLOOKUP("*"&D2&"*",A2:A7,B2:B7,"",2)

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

解读:

XLOOKUP函数默认是不支持模糊查找匹配的,如果要用通配符,第五参数必须填2,这也是XLOOKUP函数的一个特殊之处。通配符我们一般使用*(代表任意多个字符),只要数据中包含查找关键词,就会返回对应结果。

5、根据区间查找数据

如下图所示,左侧是员工考核成绩表格,我们需要根据右考核成绩区间来评定不同的等级。这时我们完全可以使用XLOOKUP函数来实现。

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

第一步:先创建一个辅助列,把每个成绩考核区间的最低标准列出来,手动输入即可

0<成绩<60,这个范围的最小值是0;

60<=成绩<70,这个范围的最小值是60;

70<=成绩<90,这个范围的最小值是70;

90<=成绩<100,这个范围的最小值是90;

所以,辅助列的数组从上到下分别是90、70、60、0,如下图所示

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

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

=XLOOKUP(C2,G:G,H:H,,-1)

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

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

当XLOOKUP函数第5参数即匹配模式为-1,表示精确匹配,若未找到所查内容返回较较小值。

五、UNIQUE函数

UNIQUE函数介绍

功能:UNIQUE函数可以去除重复值保留唯一值

语法:=UNIQUE(数组,[按列],[仅出现一次])

第1参数:数组就是返回唯一值的数组数据区域;

第2参数:按列是可选参数,指定比较的方式,设置为TRUE将比较列并返回唯一值,设置为FALSE (或省略) 将比较行并返回唯一值;

第3参数:[仅出现一次]可选参数,一般直接省略即可。

1、基本用法

如下图所示,左侧是名单信息,我们需要去掉里面的重复值。

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

=UNIQUE(A1:A8)

然后点击回车,即可

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

2、计数不重复人数

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

=COUNTA(UNIQUE(A2:A8))

然后点击回车即可

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

六、SUMIFS函数

SUMIFS函数介绍

功能:SUMIFS函数主要用于多条件求和,在工作中也是必学的函数公式

语法:=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,...)

1、单条件求和

如下图所示,这是一个不同门店员工的销售数据,计算“市区一店”的销售总额

在目标单元格输入公式:

=SUMIFS(E:E,B:B,G3)

然后点击回车即可

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网

2、多条件求和

如下图所示,这是一个不同门店员工的销售数据,计算“市区一店”、“7月”的销售总额

在目标单元格输入公式:

=SUMIFS(E:E,B:B,G3,D:D,H3)

然后点击回车即可

excel最常用的函数:excel使用频率极高的6个函数用法大全-趣帮office教程网