VPS参考测评推荐
专注分享VPS主机优惠信息
衡天云优惠活动
华纳云优惠活动

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

主机参考:VPS测评参考推荐/专注分享VPS服务器优惠信息!若您是商家可以在本站进行投稿,查看详情!此外我们还提供软文收录、PayPal代付、广告赞助等服务,查看详情!
我们发布的部分优惠活动文章可能存在时效性,购买时建议在本站搜索商家名称可查看相关文章充分了解该商家!若非中文页面可使用Edge浏览器同步翻译!PayPal代付/收录合作

排名,简单;但是,如果有多个项目类别,并且可能有相同的业绩,如何快速找出共享第一名的人员?这需要通过多个条件进行匹配,以便首先找出所需的排名。这里有两个方案,但都不完美。你能完善它们吗?

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

一年一度的表彰大会即将开始。哪些同事成为了今年的销售冠军?让我们一起找到他们!

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

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

数据只包括销售订单号、产品名称、业务人员姓名和销售金额。现在,我们需要按照下面的格式统计每种产品的销售冠军。

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

看到这个问题,不知道大家想到了哪些方法呢?数据透视表、MAX函数或VLOOKUP……...

老菜鸟推荐两种方法:第一种辅助柱+公式;第二张透视表+公式。

方法一:辅助列+公式第一步:添加辅助列。

首先,按产品名称汇总每个人的销售额。按条件求和,这里我们使用SUMIFS函数进行统计。虽然可以使用数据透视表达到相同的效果,但数据透视表无法一次性获得最终所需的效果,因此使用辅助列更方便。

公式:

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

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

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

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

使用此公式例程时需要注意三点:

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

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

(3)由于公式需要下拉,为了避免计算区域的变化,所涉及的范围应使用绝对引用。

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

此时,通过找出每种类型产品下销售额最高的业务人员来完成所有统计。

第三步:找到冠军。

根据销售金额,这实际上是一个参考搜索,可以使用VLOOKUP或INDEX等参考函数来完成。

接近成功了,现在该削苹果皮了。削苹果的特点是又细又准。

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

如果我们使用VLOOKUP,我们必须使用反向查找例程,并且公式相对复杂。如果使用INDEX和MATCH的组合,就可以了,公式也不难:

= INDEX($ 2:750加元,MATCH(I2,$ 2:750.0加元))

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

第二个细节:最高销量可能是一样的。

这两种功能的结合堪称经典搭档。但还有一个细节:我们不能排除两类产品的最高销量相同。为了避免在寻找不同类别的相同最大销售额时可能出现的错误,我们必须根据产品名称和销售额对它们进行匹配,公式变为:

= INDEX($2加元:750加元,MATCH(G2和I2,$2加元:750加元和$ 2加元:750加元))

多条件匹配的常用套路之一是使用连接符号&将多个条件串在一起形成新的条件进行查询,当然查询区域也需要用&串在一起。

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

当然,如果你不想使用Vlookup进行反向搜索,你也可以使用lookup函数来实现:

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

多条件匹配的第二个常见套路是在多个条件和等号=的搜索区域之间建立一个表达式,然后将该表达式相乘。

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

公式的套路是:= LOOKUP(1,0/(条件面积=条件),目标面积)。如果有多个条件,例程可以直接升级为:= LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)*(条件区域3=条件3)....

方法二:透视表+公式第一步:统计排名。

将产品名称和业务人员拖到行区域,并将销售额拖到值区域两次。然后,根据去年的教程“嘿,拖动鼠标两次以获得性能统计和排名!”将销量2的显示方式设置为“降序”,将基本字段设置为“业务人员”,按产品分类进行销售业绩统计和排名。

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

第二步,整理数据透视表。

单击数据透视表,然后在“设计”选项卡的“布局”选项组的下拉菜单中单击“在表格中显示”和“重复所有项目标签”命令。然后在透视图表格上单击鼠标右键,选择“分类汇总业务人员”以取消表格中的分类汇总项目。该表如下所示:

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

第三步,输入公式得到冠军的姓名和成绩。

在G2单元格中输入公式:

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

按Ctrl+Shift+Enter完成。

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

然后向右下拉公式。

实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)

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

然而,今天的解决方案并不完美。尽管我们在教程中要求自己“削苹果”并注意细节,但我们仍然错过了一个非常重要的细节——同类产品的最高销售额可能是相同的。

相关学习推荐:excel教程

以上就是实用Excel技巧的分享:如何在众多条件中找到头号人物的详细信息,更多请关注主机参考其他相关文章!

这几篇文章你可能也喜欢:

本文由主机参考刊发,转载请注明:实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人) https://zhujicankao.com/105748.html

【腾讯云】领8888元采购礼包,抢爆款云服务器 每月 9元起,个人开发者加享折上折!
打赏
转载请注明原文链接:主机参考 » 实用excel技巧分享:如何通过多个条件找到第一个人(Excel找到第一个人)
主机参考仅做资料收集,不对商家任何信息及交易做信用担保,购买前请注意风险,有交易纠纷请自行解决!请查阅:特别声明

评论 抢沙发

评论前必须登录!