实用Excel技巧分享:怎么多条件查找排名第一人

时间:2023-03-08
阅读:

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

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

实用Excel技巧分享:怎么多条件查找排名第一人

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

实用Excel技巧分享:怎么多条件查找排名第一人

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

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

方法1:辅助列+公式

第1步:添加辅助列

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

公式:

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

实用Excel技巧分享:怎么多条件查找排名第一人

公式格式:=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))

实用Excel技巧分享:怎么多条件查找排名第一人

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

(1)范围要准确,不建议选择整列作为计算区域;

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

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

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

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

第3步:找出冠军人员

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

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

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

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

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

实用Excel技巧分享:怎么多条件查找排名第一人

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

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

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

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

实用Excel技巧分享:怎么多条件查找排名第一人

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

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

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

实用Excel技巧分享:怎么多条件查找排名第一人

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

方法二:透视表+公式

第1步:统计业绩并排名

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

实用Excel技巧分享:怎么多条件查找排名第一人

第2步,整理透视表

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

实用Excel技巧分享:怎么多条件查找排名第一人

第3步,输入公式获取冠军姓名和业绩

在G2单元格中输入公式:

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

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

实用Excel技巧分享:怎么多条件查找排名第一人

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

实用Excel技巧分享:怎么多条件查找排名第一人

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

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

返回顶部
顶部