ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 13637|回复: 14

[分享] 菜鸟PowerPivot周记(三)销售每平米KPI计算(SUMX)—— 内藏挑战题目!

[复制链接]

TA的精华主题

TA的得分主题

发表于 2013-8-1 17:08 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:Powerpivot
本帖最后由 tc0098 于 2013-8-2 11:09 编辑

呼,犯懒了快一个月没有更新学习记录了,想到八月都快到了,赶紧来写一篇吧!(写完都8月了。。。)

这次的内容是关于一类经常用的KPI的,我用的例子就是店铺销售每平米(Sales per m2
大家可能要问了,这也能算是难题吗?Sales/store size,小学二年级题目啊!
嘿嘿,大家觉得不难的话,请接招来挑战一下吧!(用公式或者Pivot做都可以!)

题目如下(附件可以下载):
一个公司(简化的)有一个Sales的表格如下,记录了每一笔交易的日期、店铺、货品和金额;还有一个Store的表格记录了每个店铺的面积、开业时间和结业时间。求某个时间(年//月)某个店铺(或者所有店铺)的每平米销售金额。
1.png 2.png 3.png

题目解析:此题的难点在于如何确定某个时间段其中一个店铺的面积,比如一个店铺是在年中3月开始营业的,那计算本年的面积的话就要加一个系数,10/12,来换算这个店铺的等效面积;但如果是计算本年第二季度的话,这个系数就不需要了。

怎么样,还觉得简单吗?

不过想必各位大神用公式,数组,VBA一定做得出来,但对于一个零售公司,全年上百万条的交易记录,恐怕庞大的计算量会令Excel文件直接崩溃吧?而普通的Pivot,肯定有SQL高手藏龙卧虎,不过本菜鸟没有什么研究,完全不会啊。。。

好,言归正传,看PP是如何简单解决这个问题的!

首先,PP可以像Access一样表和表之间的关系,SalesStore当然和StoreStore连接啦,SalesDateDateDate连接啦。(具体步骤就不写啦)。

然后,养成一个好习惯,就像上学考试的时候,先把能写Measure的先写上,能得一分是一分!
Sale:=SUM(Sales[Sales]) 求所有销售的和
Store Size 0:=SUM(Store[size]) 求所有店铺面积

然后我们想知道的Sales per M2当然不是Sale/Store Size 0啦:

把Store Size 0搬进Pivot:
4.png
因为我们没有做任何的定义,所以,比如,即便Store CD2010年还有开业,面积也被算进去了,这样2010年的总面积就是670,显然不对喽。

来让我们这么想:
比如我们选取一个时间段,比如2011年,想知道这个时间段的店铺等效面积,以C店铺为例,612011年开业,212012年结业,所以在2011年,C店铺从61日,开到了1231日,共7个月。等效面积就是150×7/12=87.5.
逻辑如下图:
5.png
我们定义我们选择的这个时间段,第一天是First day,最后一天是Last day
我们选的这段时间2011年的长度Period Length = Last day – First day +1

店铺的开业时间是Open day,结业时间是Close day

店铺在我们选择的这段时间开业的第一天是Start day = MAX(First day, Open day),最后一天End Day = MIN(Last day, Close day)
所以店铺这段时间开业的时间Duration = End Day – Start Day +1

最后WA ratio = Duration / Period Length 就是这段时间店铺C的等效面积的系数了
最后,Store Size = Store Size 0 * WA ratio

逻辑简单明了吧?

接下来就是简单的写公式了:
First day:=MIN([Date])
Last day:=EOMONTH(MAX([Date]),0)
EMONTH大家都熟悉吧,就是求某段时间月底的最后一天,因为我的懒得把所有日期都放在Date表里边了,所以用个函数得到最后一天;如果Date表完整的话,MAX([date])就可以了)
Period Length:=VALUE([Last day]-[First day]+1)
别忘加1哦!为什么要加个VALUE呢?因为PP设定有问题,时间-时间,格式不能变成数字,有待改进啊。。。。

Open day:=MIN([Open Date])
Close day:=MIN([Close date])
为什么要写MIN呢?因为有4Store[Opendate]4个值,measure是不能等于一个列的,所以必须加一个函数返回一个其中的值。MAXSUM的效果也是一样的效果。
Pivot返回的Open day值如下,完全正确!(Total先不要看哦,目前Total是没有意义的!Total是SUM下的效果,其实是什么都无所谓)
6.png

Start day:=if([first day]>=[Open day],[Firstday],[open day])
End Day:=if([Last day]>=[close day],[close day],[lastday])
可惜PP里边不能写MAX([Firs day], [Openday])这种格式,里边只能有一个量,还必须是Column,目前还不知道有什么特殊考虑,并且没有LARGE或者SMALL,所以勉强用IF代替一下吧。

Duration:=IF(VALUE([End Day]-[Startday]+1)<=0,0,VALUE([End Day]-[Start day]+1))
这里有个判断,为了避免有时候会出现的错误:End Day<Start day,这只会发生在店铺完全没在这段时间营业的情况下,比如这样:
7.png
当然等效时间就是0啦。

WA ratio:=[Duration]/[period length]

Store Size 3:=[Store Size 0]*[WA ratio]

拿店铺C举例,就可以看到是这样啦:
8.png
把季度放进去,可以看得更清楚一些:
9.png

这样就结束了吗?请看这个问题,老板说不想单个单个店铺看了,我们想看整个公司的销售每平米!




华为网盘附件:
【华为网盘】 3 Advanced Average.rar
3 Advanced Average.rar (117.14 KB, 下载次数: 294)

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-8-1 17:09 | 显示全部楼层
本帖最后由 tc0098 于 2013-8-1 17:27 编辑

于是你把Store Size 3按这样的方式放进Pivot中:
10.png
坑爹呢这是?!为什么A+B+C+D不等于Total???PP脑残了???

哈哈,这个就有意思了!试想一下,如果你在计算平均值的时候,每个产品分别的平均值加起来是否等于全部产品的平均值呢?显然不是吧。这里是一样的道理,刚刚我们做的那些步骤,仅仅是对某一个店铺有效的,合起来的话,必然出乱子。

错误就在这两个Measure上边:
Open day:=MIN([Open Date])
Close day:=MIN([Close date])

比如把Open day放进Pivot里去,每个店铺的Open Day都是对的,但Total的话,就全是最小那个OpenDay了,因为我们用了MIN。好记性的读者一定记得这里用MAXSUN都是可以的,那TOTAL的结果又会不同了。所以乱子就出来了。
11.png

当然,强大的PP是不会让我们失望的,面对这种死局早有对策!
下面隆重推出X系列DAXX系列就是专门针对这种情况,管它平均还是什么的,强行把所有值加起来!它们有SUMXMAXXMINXRANKXCOUNTX。。。
举个例子:
Price:=AVERAGE([Sales])  
Ave Price:=SUMX(VALUES(sales[Product]),[Price])
12.png
SUMX把所有的价钱直接加了起来!

X系列的原理我还不是那么清楚,只知道这一个用法,用VALUES返回唯一值,然后SUMX就会把这个几个唯一值对应的Measure,比如[price]加起来)
平时这个函数看起来没什么用,但此时此刻,就是它大放光彩之时!
Store size 4:=SUMX(values(Store[Store]),[Store Size 3])

作用方法可能是这样的(纯猜测):
= SUMX({A,B,C,D},[Store Size 3])
=SUM([Store Size 3] for A, [Store Size 3] for B, [StoreSize 3] for C, [Store Size 3] for D)

然后我们就得到了这样的Pivot,大家可以验算看一下对不对哦!
14.png
最后水到渠成:
Sales per m2:=[sale]/[Store size 4]
15.png

后记:
这只是个小小的例子,这个小例子用公式甚至用计算器都可以算的出来销售每平米这个KPI,但如果要对一个规模较大的公司,每年有几百万比交易,成百上千的店铺遍布各地,而且不断有新店铺开业旧店铺结业的话,PP的优势就毫无疑问的体现了出来。

Excel肯定无法自动化这样的计算,之前必定要经过一些整理,减小录入数据的大小,再加上很多手工的计算,如果要涉及更多时间段的比较(季度//周),麻烦程度可想而知。
SAP等大型ERP系统自然可以解决,但之前要投入巨大的人力财力,用1个小时可以在PP上解决的事情,找IT编程那可是要给老板写计划书的事情。而且一旦完成在很长时间内都不能改变,非常不灵活。

当然,PP也不是万能的,只不过是在这种情况下比ExcelSAP更加适合而已,所以叫Self-ServedBI System:目的就是让个人具备分析较大数据的能力。(但显然无法像SAP一样掌控全球数据)所以PP和一个良好建立的数据库,比如SAP,联系起来,进行分析,会最大的体现它的价值。

评分

4

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-8-1 17:10 | 显示全部楼层
tc0098 发表于 2013-8-1 17:09
赶紧占楼!!!!!!

再占一个,怕不够用!!!

TA的精华主题

TA的得分主题

发表于 2013-8-2 09:22 | 显示全部楼层
等了三周{:soso_e163:} 终于来了{:soso_e121:}

TA的精华主题

TA的得分主题

发表于 2013-8-2 10:15 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-8-2 11:09 | 显示全部楼层
在和弦处等你 发表于 2013-8-2 10:15
附件下不来,麻烦直接传到EH帖子上

放上去拉!

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-8-2 11:11 | 显示全部楼层
在和弦处等你 发表于 2013-8-2 09:22
等了三周 终于来了

哈哈,好感动啊!下次尽量不偷懒了。。。

TA的精华主题

TA的得分主题

发表于 2013-8-2 11:16 | 显示全部楼层
tc0098 发表于 2013-8-2 11:11
哈哈,好感动啊!下次尽量不偷懒了。。。

谢谢您答复

TA的精华主题

TA的得分主题

发表于 2015-11-21 11:28 | 显示全部楼层
谢谢楼主的分享 详细精彩

TA的精华主题

TA的得分主题

发表于 2016-2-5 16:57 | 显示全部楼层
太强悍了,楼主是通过什么途径学习的,能介绍一下相关的网站、书籍或视频吗?这块内容网上资料很少,我好不容易买到一本书《微软EXCEL2013:用POWER PIVOT建立数据模型》,但是外国人写的,语境不适应,坑爹的出版社还不提供附件,只能单看书,还是太抽象,学起来很吃力啊。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-28 18:24 , Processed in 0.064155 second(s), 14 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表