|
每天一点小技能 职场打怪不得怂  编按：一说到数据分析，很多人第一时间想到的就是数据透视表。而今天，小E给大家讲的不是数据透视表，而是一个比数据透视表强大的多的，被称为数据建模、微软20年来最伟大发明的Excel功能——power pivot！下面，大家就一起来看看这个最强数据分析利器怎么使用吧！  今天boss突然想知道公司的每个销售团队中的销售尖子的业绩情况。经过一番思考，我决定给他展示下每个团队的业绩前三名的销售人员的总业绩。目的有了，接下来就是怎么实现的问题。 笔者也借这篇文章给大家分享下函数和excel中的数据分析利器——power pivot！它们是两种截然不同的问题处理思路。 销售数据截图： 1 函数法 逻辑思路： ①首先，明确写公式的目的。 目的越详细，思路越清晰。这里的目的是求和，更加精准的说是区域求和，区域就是团队中的业绩第一名到第三名对应的销售金额所在单元格区域。 ②其次，联系目的思考实现过程中的困难点，明确了困难点，才好针对性的用公式。 难点1：数据源中需要求和的区域不连续。既然我的目的是对区域进行求和，那么写公式前最好让需要求和的区域是连续的，解决办法是用排序功能重新排列数据源即可。 难点2：明确区域的起点，起点一旦明确，区域长度就确定了，即起点及其之后的三个单元格。确定起点位置可以用三个函数来嵌套，分别是：indirect、address、match；再在嵌套函数的外层套一个offset，即可确定区域。最后用sum函数对区域求和。 以上就是笔者在写公式前的逻辑思路。逻辑思路有了，公式就水到渠成了。 Step.01 整理数据源，用鼠标点击“开始”，“排序和筛选”，“自定义排序”，然后以“销售团队”为主要关键字升序，“销售金额”为次要关键字降序。 公众号回复：入群，下载课件 Step.02 将销售团队复制粘贴到F列，然后在“数据”选项卡下单击“删除重复值”，实现去重。 在G2中写下公式 =IF(COUNTIF(A:A,F2)>3,SUM(OFFSET(INDIRECT(ADDRESS(MATCH(F2,A:A,0),3,,)),0,0,3,1)),SUM(OFFSET(INDIRECT(ADDRESS(MATCH(F2,A:A,0),3,1,1)),0,0,COUNTIF(A:A,F2),1))) 写好后，按回车键完成公式输入。然后用鼠标下拉单元格右下角小黑定至G6即可。 函数解析（为了方便大家阅读，公式中的圆圈序号即为上一步的公式）： 公式的主体部分就是【SUM(OFFSET(INDIRECT(ADDRESS(MATCH(F2,A:A,0),3,,)),0,0,3,1))】，分成四步讲解。 ①MATCH(F2,A:A,0)部分是找到F列的销售团队在A列中第一次出现的位置对应的行数 ②ADDRESS(①,3,,)返回的是$C$27，确定了团队名称第一次出现时对应的销售金额的位置，这个位置就是接下来要求和的区域起点。 ③OFFSET(INDIRECT(②),0,0,3,1)返回的是{85;63;53}，对应的就是野狼团队前三名的销售金额。注意，如果不加indirect函数会返回错误，在公式中如果要引用位置，一般都要加indirect函数作为桥梁。 ④SUM(③)这一步即对OFFSET返回的3个值{85;63;53}进行求和，返回的便是201。 以上四步就是公式主体部分的解释。 外层嵌套的if判断，作用是当团队销售明细不足3个时，让公式计算它本身所有的销售业绩之和。如果没有这个判断，当销售明细不足3个时，会将别的团队的业绩算在它身上，造成不公平。 以上是公式法的思路逻辑解释。 2 power pivot 法 很多小伙伴对power pivot比较陌生，大家可以简单认为是Excel数据透视表（Pivot Table）的升级。其功能比数据透视表强大很多，所以也被人们称作数据建模，名字很高大上有木有？更高大上的是它被称作是微软20年来最伟大的发明。而她使用的语言就是DAX 。 下面，就以今天的案例开启“从工作表函数到DAX函数之旅”吧。文中把power pivot简称PP。 用PP解决这个问题不需要将数据源排序！ Step.01 将数据源添加到数据模型。 如果你找不到PP选项卡。就在开发工具下面的“COM加载项”中勾选“Microsoft Power Pivot for Excel ”。 Step.02 进入了PP的数据模型编辑器，首先将光标放在“销售金额”下的空白处，点击“主页”下的“自动汇总”，就自动生产了DAX表达式。 销售金额的总和:=SUM([销售金额]) Step.03 然后在下面的空白处写上下面这段DAX函数： 销售量前3:=CALCULATE([销售金额的总和],TOPN(3,'表1',[销售金额的总和],0) ) 函数解析： ①“销售量前3:”这个前缀是自定义的，不同于excel中工作表的函数，DAX函数需要先自定义一个前缀。 ②[销售金额的总和]是引用上面的销售金额求和表达式。 ③TOPN(3,'表1',[销售金额的总和],0)返回的是销售金额最多的三个明细，它返回的内容实质是一长表。第一参数是限定返回的数量；第二参数是表的名称，相当于工作表中的sheet名称；第三参数0表示降序。 ④CALCULATE([销售金额的总和],③），代表用③返回的表来筛选[销售金额的总和]，其返回的就是最大的三个销售金额相加的值。 Step.04 DAX函数写完之后，用鼠标点击“主页”，“数据透视表”，将内容加载到透视表中进行分析。 将“销售团队”拉到行，然后找到大家刚刚写的DAX函数“销售量前3”，把“销售量前3”拉到“值”。（在PP中生成的透视表都可以在字段中找到大家写过的表达式，其以大家设置过的前缀显示）。 |
|
|
