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步：统计业绩并排名

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

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

在G2单元格中输入公式：

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

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

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