|
编按：排名，简单；但如果有多个项目类别，并且可能存在业绩相同，怎么快速找出各个分享排名第一的人物呢？这就要通过多条件去匹配，才能找出需要的排名第一者。这里提供了两个方案，但都不够完美，你能把它们完善吗？  一年一度的表彰大会马上就要开始了，今年又是哪些同事成为了销售冠军呢？让我们一起来把他们找出来吧！ 某公司的电商平台各类电器销售数据如图：  数据只有销售单号、产品名称、业务人员姓名和销售额，现在需要按下图的格式统计每类产品的销售冠军。  看到这个问题，不知道大家想到哪些方法？透视表、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)……,目标区域) 看不懂LOOKUP套路公式的请上部落窝教育官网搜索查看文章《LOOKUP函数用法全解（上）——LOOKUP函数的5种用法》。 方法二：透视表+公式 第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，还学习了多条件匹配的两种套路，在遇到类似的问题时，可以直接使用。 不过，今天的解决是不完善的。虽然教程中我们要求自己“削苹果”关注细节，但我们还是遗漏了一个很重要的细节——同类产品最高销售额可能出现相同。 我们把这个问题留给大家思考：如果出现同类产品最高销售额相同，又怎么找出冠军呢？欢迎留言给出您的方法。 ＊说明：本教程主要由老菜鸟编写。小雅按老菜鸟的提示写作了第二种方法。 |
|
|
来自： 部落窝教育BLW > 《部落窝excel/VBA》
