主机参考:VPS测评参考推荐/专注分享VPS服务器优惠信息!若您是商家可以在本站进行投稿,查看详情!此外我们还提供软文收录、PayPal代付、广告赞助等服务,查看详情! |
我们发布的部分优惠活动文章可能存在时效性,购买时建议在本站搜索商家名称可查看相关文章充分了解该商家!若非中文页面可使用Edge浏览器同步翻译!PayPal代付/收录合作 |
排名,简单;但是,如果有多个项目类别,并且可能有相同的业绩,如何快速找出共享第一名的人员?这需要通过多个条件进行匹配,以便首先找出所需的排名。这里有两个方案,但都不完美。你能完善它们吗?
一年一度的表彰大会即将开始。哪些同事成为了今年的销售冠军?让我们一起找到他们!
某公司电商平台上各类电器的销售数据如下图所示:
数据只包括销售订单号、产品名称、业务人员姓名和销售金额。现在,我们需要按照下面的格式统计每种产品的销售冠军。
看到这个问题,不知道大家想到了哪些方法呢?数据透视表、MAX函数或VLOOKUP……...
老菜鸟推荐两种方法:第一种辅助柱+公式;第二张透视表+公式。
方法一:辅助列+公式第一步:添加辅助列。
首先,按产品名称汇总每个人的销售额。按条件求和,这里我们使用SUMIFS函数进行统计。虽然可以使用数据透视表达到相同的效果,但数据透视表无法一次性获得最终所需的效果,因此使用辅助列更方便。
公式:
= SUMIFS(:D,C2,B2 B:B)
公式格式:= SUMIFS(sum区域,条件区域1,条件区域1,条件区域2,条件2...)
SUMIFS是一个多条件求和函数。第一个参数是要求和的数据所在的列,下面的参数成对分组以形成一组条件。在本例中,第一组条件是业务人员,因此条件区域1是列C,条件1是C2;第二组条件是产品名称,条件区域2是B列,条件2是B2。
有了辅助列,下一步是找出每个类别中最高的销量是多少。这里需要注意的是,统计结果表中销售冠军的名字出现在上一次销售之后。在实际统计中,我们不必按照这个顺序来计数。我们先数一下哪个方便。
第二步:统计最高销售额。
通常当你说最大值时,首先想到的是max函数。该函数的用法与SUM非常相似。您只需要给出一组数字或一个数据区域,就可以获得这组数字中的最大值。
在今天的例子中,因为我们希望获得同一类别中的最大值,也就是说,我们不能直接用max函数获得结果。
这种条件统计最大值有一个固定的公式:
=MAX(数据范围*(条件范围1=条件1)*(条件范围2=条件2)...)
本例中只有一个条件,即产品名称,因此公式为:= max($ E $ 2:$ E $ 750 *($ B $ 2:$ B $ 750 = G2)。
使用此公式例程时需要注意三点:
(1)范围要准确,不建议选择整列作为计算区域;
(2)公式涉及数组运算。输入公式后需要按Ctrl+Shift+Enter,按键后公式中会自动添加一对大括号。
(3)由于公式需要下拉,为了避免计算区域的变化,所涉及的范围应使用绝对引用。
这个公式的具体原理涉及到逻辑值和数组的计算原理,后面我们会具体讲解。
此时,通过找出每种类型产品下销售额最高的业务人员来完成所有统计。
第三步:找到冠军。
根据销售金额,这实际上是一个参考搜索,可以使用VLOOKUP或INDEX等参考函数来完成。
接近成功了,现在该削苹果皮了。削苹果的特点是又细又准。
第一个细节:数据源中的累计销售额位于业务人员的右侧。
如果我们使用VLOOKUP,我们必须使用反向查找例程,并且公式相对复杂。如果使用INDEX和MATCH的组合,就可以了,公式也不难:
= INDEX($ 2:750加元,MATCH(I2,$ 2:750.0加元))
第二个细节:最高销量可能是一样的。
这两种功能的结合堪称经典搭档。但还有一个细节:我们不能排除两类产品的最高销量相同。为了避免在寻找不同类别的相同最大销售额时可能出现的错误,我们必须根据产品名称和销售额对它们进行匹配,公式变为:
= INDEX($2加元:750加元,MATCH(G2和I2,$2加元:750加元和$ 2加元:750加元))
多条件匹配的常用套路之一是使用连接符号&将多个条件串在一起形成新的条件进行查询,当然查询区域也需要用&串在一起。
当然,如果你不想使用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)....
方法二:透视表+公式第一步:统计排名。
将产品名称和业务人员拖到行区域,并将销售额拖到值区域两次。然后,根据去年的教程“嘿,拖动鼠标两次以获得性能统计和排名!”将销量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,还学习了多条件匹配的两个套路,在遇到类似问题时可以直接使用。
然而,今天的解决方案并不完美。尽管我们在教程中要求自己“削苹果”并注意细节,但我们仍然错过了一个非常重要的细节——同类产品的最高销售额可能是相同的。
相关学习推荐:excel教程
以上就是实用Excel技巧的分享:如何在众多条件中找到头号人物的详细信息,更多请关注主机参考其他相关文章!
这几篇文章你可能也喜欢:
- 如何使用Excel控件(Excel控件)
- 在Excel中按名称查询照片很简单(如何在Excel中按名称搜索照片)
- 使用Excel网格实现项目进度图表(如何使用Excel创建项目进度条)
- 在Excel中轻松查看任意条件下的数据(如何在Excel中根据条件搜索数据)
- 下面讲一下Excel函数学习中的多重求和函数(Excel中如何对多个数据求和)
本文由主机参考刊发,转载请注明:实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人) https://zhujicankao.com/105748.html
评论前必须登录!
注册