快看点丨实用Excel技巧分享：怎么多条件查找排名第一人

排名，简单；但如果有多个项目类别，并且可能存在业绩相同，怎么快速找出各个分享排名第一的人物呢？这就要通过多条件去匹配，才能找出需要的排名第一者。这里提供了两个方案，但都不够完美，你能把它们完善吗？

一年一度的表彰大会马上就要开始了，今年又是哪些同事成为了销售冠军呢？让我们一起来把他们找出来吧！

(资料图片)

某公司的电商平台各类电器销售数据如图：

数据只有销售单号、产品名称、业务人员姓名和销售额，现在需要按下图的格式统计每类产品的销售冠军。

看到这个问题，不知道大家想到哪些方法？透视表、MAX函数、还是VLOOKUP……

老菜鸟给大家推荐两种方法：第一种辅助列+公式；第二种透视表+公式。

方法1：辅助列+公式

第1步：添加辅助列

首先将每个人的销售额按照产品名称进行汇总。按条件求和，这里用SUMIFS函数来进行统计。虽说可以使用透视表完成同样的结果，但是透视表并不能一次就得到最终需要的效果，因此用辅助列会更方便。

公式：

=SUMIFS(D:D,C:C,C2,B:B,B2)

公式格式：=SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2……)

SUMIFS是一个多条件求和函数，第一参数是要求和的数据所在的列，后面的参数两个一组，构成一组条件。在这个例子中，第一组条件是业务人员，因此条件区域1就是C列，条件1是C2；第二组条件是产品名称，条件区域2就是B列，条件2是B2。

有了辅助列，下一步就可以找到每个品类中最高的销售额是多少了。这里需要注意的是，统计结果表里销售冠军姓名在前销售额在后。实际统计时并非必须按这样的先后顺序统计，哪个方便我们就先统计哪个。

第2步：统计最高销售额

通常一说最大值，首先想到的就是MAX函数。这个函数的用法和SUM很像，只需要给出一组数或者一个数据区域，就能得到这一组数中最大的值。

在今天这个例子中，因为我们要得到的是同一个品类中的最大值，也就是按条件统计最大值，所以无法直接用MAX函数得到结果，

这类按条件统计最大值的有固定的套路公式：

=MAX(数据区域*（条件区域1=条件1）*（条件区域2=条件2）……)

本例只有一个条件，就是产品名称，因此公式为：=MAX($E$2:$E$750*($B$2:$B$750=G2))

使用这个公式套路需要注意三个地方：

（1）范围要准确，不建议选择整列作为计算区域；

（2）公式涉及数组运算，在输入公式后需要按Ctrl+Shift+Enter键，按键后会自动在公式中添加一对大括号；

（3）因为公式要下拉，为了避免计算区域发生改变，所以涉及到的范围需要使用绝对引用。

这个公式具体原理涉及到逻辑值和数组的计算原理，以后我们会专门进行讲解。

到这一步，再找出每类产品下最高销售额对应的业务人员就完成了全部的统计。

第3步：找出冠军人员

根据销售额查人员，这实际上就是一个查找引用，使用VLOOKUP或者INDEX等引用函数都可以完成。

接近成功，现在要削苹果了。削苹果的特点就是细、准。

第一个细节：数据源中的累计销售额位于业务人员的右侧。

如果用VLOOKUP，我们就得使用反向查找的套路，公式相对还是比较复杂。如果用INDEX与MATCH组合倒是可以，公式也不难：

=INDEX($C$2:$C$750,MATCH(I2,$E$2:$E$750,0))

第二个细节：最高销售额可能存在相同。

这两个函数组合堪称经典搭档。但是还有一个细节问题：我们不能排除两类产品的最高销售额存在相同的情况。为了避免可能存在的不同品类最高销售额相同的查找失误，我们就必须要按产品名称和销售额两个条件去匹配，公式就变成：

=INDEX($C$2:$C$750,MATCH(G2&I2,$B$2:$B$750&$E$2:$E$750,0))

多条件匹配常用套路之一就是用连接符号&把多个条件串在一起组成一个新的条件来查询，当然查询区域也需要用&串在一起。

当然，像这种多条件查找，并且不愿意利用Vlookup反相查找的话，也可以用LOOKUP函数来完成：

=LOOKUP(1,0/(($E$2:$E$750=I2)*($B$2:$B$750=G2)),$C$2:$C$750)

多条件匹配常用套路之二就是把多个条件各自用等号＝与查找区域建立起表达式，然后把表达式进行相乘。

公式的套路是：=LOOKUP(1,0/(条件区域=条件),目标区域)，如果是多个条件的话，可以直接将套路升级为：=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)*(条件区域3=条件3)……,目标区域)

方法二：透视表+公式

第1步：统计业绩并排名

将产品名称和业务人员拖入行区域，销售额拖两次到值区域，然后按照部落窝教育去年的教程《嘿，鼠标拖两下一次搞定业绩统计和排名！》设置销售额2的值显示方式为“降序排列”，基本字段为“业务人员”获得按产品分类的销售业绩统计和排名。

第2步，整理透视表

单击透视表，点击“设计”选项卡“布局”选项组“报表布局”下拉菜单中的“以表格形式显示”和“重复所有项目标签”命令。接着在透视表上右击，选择“分类汇总“业务人员””，取消表格中的分类汇总项。表格变成下方模样：

第３步，输入公式获取冠军姓名和业绩

在G2单元格中输入公式：

=INDEX(L$2:L$200,MATCH($G2&1,$K$2:$K$200&$N$2:$N$200,0))

输入完毕按Ctrl+Shift+Enter三键结束。

然后右拉、下拉公式即可。

在今天的教程中我们学习了几个函数，分别是SUMIFS、MAX、INDEX、MATCH、LOOKUP，还学习了多条件匹配的两种套路，在遇到类似的问题时，可以直接使用。

不过，今天的解决是不完善的。虽然教程中我们要求自己“削苹果”关注细节，但我们还是遗漏了一个很重要的细节——同类产品最高销售额可能出现相同。