Excel中6个使用频率极高的函数，新手必学，职场必备！

一、SUMPRODUCT函数

SUMPRODUCT函数介绍

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

语法：=SUMPRODUCT（数组1,数组2,数组3, ...）。

解读：

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

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

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

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

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

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

=SUMPRODUCT(B2:B7,C2:C7)

2、单条件求和

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

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

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

3、多条件求和

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

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

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

二、FILTER函数

FILTER函数介绍

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

语法：=FILTER(数组，包括，空值)

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

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

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

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

1、单条件查找

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

在目标单元格输入公式：

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

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

2、多条件查询

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

在目标单元格输入公式：

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

然后点击回车即可

三、SORT函数

SORT函数介绍

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

语法：=SORT(数组，排序依据，排序顺序，按列）

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

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

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

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

1、按条件排序

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

在目标单元格输入公式：

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

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

四、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)

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

2、逆向查找匹配

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

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

3、多条件查询

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

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

4、通配符模糊查找

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

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

解读：

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

5、根据区间查找数据

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

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

0<成绩<60，这个范围的最小值是0；

60<=成绩<70，这个范围的最小值是60；

70<=成绩<90，这个范围的最小值是70；

90<=成绩<100，这个范围的最小值是90；

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

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

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

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

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

五、UNIQUE函数

UNIQUE函数介绍

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

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

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

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

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

1、基本用法

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

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

=UNIQUE(A1:A8)

然后点击回车，即可

2、计数不重复人数

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

=COUNTA(UNIQUE(A2:A8))

然后点击回车即可

六、SUMIFS函数

SUMIFS函数介绍

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

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

1、单条件求和

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

在目标单元格输入公式：

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

然后点击回车即可

2、多条件求和

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

在目标单元格输入公式：

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

然后点击回车即可