登录
首页精彩阅读Excel商业智能最常用的3大类分析方法,你会几种?
Excel商业智能最常用的3大类分析方法,你会几种?
2016-09-13
收藏
一般我都先讲Power BI,今天被前面老师讲了,我想了半天,该讲什么好呢,最后决定给大家先讲一个我自身的故事,跟大家分享一下我是如何接触到Power BI以及Excel商业智能的吧。
 
很多人都问我专业不对口能否做数据分析,其实我想跟大家说,我是学考古的,所以大家只要想干一切皆有可能。
 
2011年以前我都在日本,在日本待了11年,在日本做过程序员,也做过开发工程师,也给日本那边失业的人进行Excel培训。2011年回国之后,我到了IBM,做销售运营管理数据分析。做到第二年的时候,日本那边有一个亚洲数据分析大会,日本人能干我们也能干,当时跟老板申请带了一个团队出来,做一下试试。做的结果就是为国争光了,带回来一个奖状,参加他们大会之后获得了第一名。我们只要想干都是能干得很好的。
 
在许多行业的数据分析中,商业智能分析方法是最直接有效,也最好解释的,能切实解决实际问题。如果跟老板汇报一个他急切想知道的情况,然后你用两个小时才做出来,最后还没跟老板说明白数字代表什么意思,老板会急的。之后我就在想,是不是能用Excel做商业智能分析?快速整理加工数据,在交互界面上展现可视化信息。我一直带着这个疑问开始研究Excel到底能不能做。
 
当时在日本正好有一场IBM推的BI产品线说明会,正好提到商业智能到底是什么,具备哪些特点才叫商业智能解决方案。我回来以后按照解决条件去找,应该怎么去做,才能让Excel发挥出商业智能分析的能力。正好当时微软发布了Power BI插件,后来我发现用Power BI插件和Excel的基本功能相结合,能够满足商业智能分析需要。
 
Power BI中有四款插件,分别是Power View、Power Map、Power Query、Power Pivot。
 
其中Power View、Power Map大家稍用功夫,理解了就能用了,核心在Power Query、Power Pivot这两个插件上,你掌握了这两个插件,就可以在Excel上操作,直接用Excel做商业智能报表了。
 
Excel是一个表格工具,我们刚才讲的Power BI是基于表,处理的是字段。Excel能对某一个单元格数据进行处理,如果我只是把表的数据、字段数据进行处理,那么用Power BI做出来的东西和其他商业智能软件是没有区分的,大家做的都是一个意思的东西,都是基于表的结果。

但如果能把Excel单元格数据和我加工过的表数据结合起来,这个平台是Excel专有的,只有Excel能实现,这也是微软布局,把插件配套在Excel上的意义所在,他把分析的可能性一下扩大了。还有一点,Excel可以用VBA,有了VBA可以把我们的自定义化无限放大,不只分析数据用,可以当作工具用,可以把分析结果嵌入在某个工具里面自动匹配。
 
从日本回来之后,我遇到了和文超老师同样的问题,别人问你干什么,我说数据分析,你用什么?Excel。说不出去。
 
当时是这样的,Excel是一个表工具,跟商业智能和数据分析还是有一定差异的,有一个明显的界限,Excel是Excel,数据分析是数据分析。

带着疑问我在研究,如何在Excel平台上做商业报表,做完之后发现,光我自己认可还不行,看看网上有没有别人认可。于是就查到了文超老师的网页,不光我一个人这么认为,也有同僚跟我怀有同样的想法,但这不足以说明Excel有市场,只能说我们大家对这个事都有共同的认知。

于是正好有一个机会让我跟CDA共同合作,推出Excel商业智能的课,如果没有市场的话肯定没有学员来听,但是开了几期之后,课程的反响是非常热烈的。一是Excel用户群非常大,二是方法确实能解决实际的问题,抛开BI的概念不提,我用Excel做了商业智能报表,确实能解决一线业务人员的实际问题,市场相当大。所以更加坚定了我对Excel将来BI发展趋势的认可,所以现在也是在跟电子工业出版社合作,写一本书,还有几个课,都是围绕这个主题的。
 
商业智能报表到底是什么?商业智能是实现商业洞察的手段,把数据转换为智慧的过程。商业智能报表有商业洞察形式的报表,它需要几个步骤:业务理解、数据收集、数据处理、数据展现,这是传统解释。
 

再往深入一些来看,Excel商业智能应该具备最基本的功能,首先是技能上,懂得分析方法、业务知识,还要有实践技能。分析方法,商业智能最主要的分析方法有两块:透视分析,可视化分析。在Excel上把这个东西做出来所需要的技能包括Excel BI技能,Excel基本功能,Power插件等,你如果想用Excel表格功能就必须要牺牲两个插件:Power View、Power Map,因为它们没办法和Excel单元格数据进行交互。
 
技能中涉及到Excel高级图表的制作技巧,一个是专业图表,一个是交互性图表。Excel每个图都是由多个小组件组成的,到底以什么形态组合你的图,你的想象力有多远就能做出多酷炫的图,公式功能,你在使表格工具时候必须要会的。还有少量的VBA技能。
 
一个报表至少具备以下几点:一个是批量处理数据,这点是Power Query和Power Pivot共同解决的。我们做数据分析的时候,第一步是业务理解,它要求你去创建一个完全符合你业务形态的数据模型,这里任何公司任何业务都不是单一存在的,不是一个数据源能够描述全面的,你需要把所有涉及当前业务分析的相关数据都在一个项下,才能用数据描述你的业务,才能做有效的数据分析,不同数据源的数据搭建在一起,形成立方体。这是Power Pivot实现的,用视图模式,利用拖拽,可以非常方便的搭载。
 
接下来和大家分享干货。商业智能报表中用到的主要分析方法给大家列出来了,主要有三类,对比分析,结构分析,透视分析,以及其他分析。
 

透视分析,透视表,透视图,不同维度筛选,使用Power Pivot的DAX表达式可以自定义大量复杂的汇总规则。

 
对比分析,纵向对比,动态图表是这样的效果。
 
时间序列条,显示不同时间点区位图,还可以把显示区段改了,改成5天、半天、全月,还有进度日期。当然,我如果高兴的话还可以加入星期,餐饮业对星期敏感性远比日期要高,做同比的时候不能拿日期做同比,要拿第几个月第几个星期做对比,这才有意义。
 
对比分析,这里为大家简单列了几个,一个是仪表盘,这是我们最熟悉的表达形式,主要用于体现最为关键的数字指标,能够起到让阅读者马上抓住重点,增强印象的作用。但也有面积大,信息量少的去点,所以一般在传递最为重要信息的时候使用仪表盘。还有图标集,图标集包括五星图标、红绿灯这些,还有温度图、方块图等等,都是表达方式。这都是通过条件格式与公式的组合应用完成的,掌握技巧后制作非常简单。 

接下来横向对比指的是部分与总体,或者部分与部分、对象与对象之间的对比,横向值对比,横向趋势对比,横向占比对比。

差异分析,雷达图是分析比较有效的一种手段。

还有各种对比,包括标准值、平均值、计划值,标准对比,你是及格还是优秀。


再往下是最经典的同比环比,本期值与同期值之间的对比/环比,本期值与上期值之间的对比,最主要是关注同比增长率和环比增长率,我们一般用各种预警图标,箭头、三角的形式来体现。
 

预警分析是KPI分析,有一个预警色填充单元格,然后进行对比。

还有Excel里新加的图标集、红绿灯可以完成。Excel最终设了五个图标集,最小的是红绿灯那个。透视分析,使用数据透视图表功能进行多维度、多层次、多规则的透视分析。结构分析,有漏斗图,瀑布图,成本构成占比图。大家可能会做图,但你知道在哪用这个图吗,所以这里给大家展示的是该在什么情况下用这个图,这点非常重要。
 
还有结构分析,杜邦分析,我花了半天制作的杜邦分析,主要用于财务分析。


当然,它还可以扩展到财务以外,所有有关系的都可以用到这种方法。然后是展开的其他的分析方法,比如变化分析,分析不同的商业阶段,一般我们都说怎么不用漏斗图,其实用漏斗图什么都看不到,只能看到上面长底下短,更主要的进行漏斗分析是要体现变化,上周和当周对比,看变化程度是什么。你用标准模板是做不出来这个图的,图表可以很好的体现静态,你要把动态业务环境用图表表现出来有难度,这是给大家一种思路,用这种方法可以展现动态变化趋势。这是财务指标构成图。

然后是分组分析,更复杂的统计方法我们也用不到,一般商业智能很难用到。
 

下面这个图是我发明的,叫折现图环形图的嵌套图表,在一张图表里就能体现分析结果。

这个需要自己去定制。然后是矩阵分析,这里用的比较多,也是比较传统的分析方法,以上讲的是可视化图表分析方法。

接下来我举两个业务分析。
 
· 第一个,财务杜邦分析仪

洞察构成及占比,杜邦分析仪用刚才的数据,制作时长一天,做出来的效果完全不一样了。

不只看到关键指标什么样的情况,而且还知道它的详细构成是什么样的情况,是哪个指标最大程度影响了它的变化。到这一步,我制作的报表是有洞察力的,领导从这个报表中可以得到更多的分析。
 

我如果是总公司,把分公司数据汇总到Power Query,Power Query里把数据进行加工,然后进行汇总,最后用公式引用出来。我只需要做一次,以后次次把数据更新就能显示当前的情况。老板很高兴,比如你是入职第二年的财务人员,你旁边也是入职第二年的财务人员,第三年你们俩拿的工资就不一样了,所以Excel直接影响你的个人收入,你不仅仅是单纯的制表,你还要考虑怎么提高自身价值,考虑把原先的数据进行分析和有效利用起来,这是业务人员完全可以独立完成的。
 
· 第二是销售漏斗分析方法。

销售漏斗是由每一个量化好的销售阶段来完成的,怎么样才能提高销售收入,最有效的方法是把每一个销售阶段商机都管理好,所以销售运营的核心本质它是一个风险管理,我需要把每一个销售阶段的商机风险降到最低,这是核心思想。这里边运用到数据分析,整套销售运营业绩图里边,对销售管理的数据分析这块起到的作用非常多,在很多环节里都发挥重要作用。
 

讲到销售运营,销售运营也是最复杂的,是事中环节,也就是O2C,它是企业销售的生命线,它涉及的维度也最多,数据质量最差,你让我拿spss做建模分析怎么做呢,所以商业智能分析最有效。
 

这里有几个维度,这是某大公司用的销售运营报表,数据罗列的非常整齐,标了不同的颜色,但也不知道看哪,这就是没有商业洞察力的普通报表。整理之后,这是我做的商业智能销售管理分析,非常简单,数据还是之前的数据,但发挥作用非常大。

 




从上面这层能够知道到底哪个销售环节出了问题,当前销售人员是在哪个销售商机内出现了最大的问题,再往右看,构成商机的转换是不是正常,也就是说,当商机达不到目标值的时候,我们可以做出两种判断。第一种判断是让我的销售人员推进商机,把低层级商机往高层级商机推进,第二种方法是添加商机总量。如果你选错了,就像炒股一样,危及到整个企业生命。本来销售资源有限,你把有限资源分散开,该找新商机的不找新商机,导致你就是崩溃季度,什么商机也得不到。所以我们先看问题点在哪,看销售阶段怎么构成的,然后是细节。这只是整套的分析的其中一部分,单独拿出来跟大家进行讲解。
 
刚才讲的这两个分析都是基于你对业务的理解制定的分析方法。


这个是房屋查询仪表盘,不仅分析数据走向,还可以用VBA进行筛选。餐饮业,餐饮业是快消行业,讲究快速,当前数据马上要知道结果,这个时候需要用Power Query、Power Pivot。


讲到这里,我们提一下Excel BI的优缺点。优点是技术门槛低,业务人员可以直接参与进来。二是小巧灵活,更改方便,可应对多变的业务环境。三是成本低,中小企业也可轻易入手。四是可最快速的响应数据更新,保证信息时效性。缺点也是四点:可处理数据量上比商业软件少,它毕竟是Excel,像BAT那种公司数据量导到Excel里不可能,不现实。不易进行联机处理,处理速度比商业软件慢,可视化上需下功夫。结合实际需要选择你最为理想的商业智能解决方案。


我今天介绍内容到这里就结束了,感谢大家!
 
Q:因为我之前关注过矩阵分析那块,我在Tableau里也了解过,假如我要关注很多公司的指标,我要关注很多年,在咱们Excel BI里边能不能实现呢?谢谢。
 
李奇:回答是肯定的,肯定能,就看你怎么把这些数据利用起来,如果把很多公司按照横向排序,纵向排序是时间轴,整理成标准数据源,就可以放在Power Query里汇总。然后选你想关注的公司和时间点,用这个就可以做。你要把所有的指标全都放在同一个矩阵图里体现的话也可以,做出来之后把关键指标单列一个表,把它做成图放在里边就可以。
 
Q:我想问一下,在Excel Query之类的,能不能实现偏向统计分析的?
 
李奇:这个是不行的,Excel是有极限的,一般CDA课程里边把数据分析分成三层,商业智能统计分析数据挖掘。Excel能做的是全部商业智能分析以及最简单的统计分析,比如说线性回归,再往上做不了了。
 
Q:老师您好,我想请教一个问题。我们知道,像万德,他里面做一些接口软件,或者模块,您对这块有没有了解?能不能介绍一下?
 
李奇:Excel生态实在太大了,说实话,了解的不是很多,但是任何人都有权力做Excel插件,Excel底层,大家如果是做开发的可能理解,你如果用VC++打开Excel列,把Excel文件打开,看里面代码,是非常复杂的C++程序,涉及无数类,这无数类全是windows本身资源,Excel在用,其他软件也在用。你只要是基于windows开发的东西,原则上都可以作为Excel扩展插件去用。
 
Q:我问一个特别简单的问题。刚才你给我们看了一个你自己做出来的散点图、流线图展示的东西,这里是不是可以允许用户自己定义我展示的这些东西的界面,或者有什么方法能自定义?
 
李奇:这个图是先有一个折现图,然后有环形图,这是一种思维方式,用这种思维方式可以把它改成任意一种图表,在什么样的程度上自定义,在课程里都会详细解释。大家可以试一下,非常简单,你做一个环形图,把它替换到散点里边就会生成代码,把代码放到循环里就会生成这样的效果,非常简单。
 
Q:老师,我经验不是很丰富,我想问您一个关于经验的问题。在销售商机转化过程中,有的商机转化时间非常长,在建模型的时候怎么样调和这样的问题呢。而且可能我数据记录完之后,我有的商机还没有达成转化,也没有确定它是否转化了。
 
李奇:这涉及到我刚才讲的,商机是最难的数据,它质量极差。你刚才提到的这一点,我增加一个列,超过90天,或者超过180天,仍然没有动的,这样的商机我标注为可能是风险商机,需要销售人员确认。如果不是有效的就把它作为垃圾商机处理。
 
Q:我只是一个财务人员,非IT人士。我看到你介绍BI的学习,也介绍到VBA,我想问的是,它的学习顺序是什么。因为我现在涉及的工作是每天特别重复要做很多翻译,还要做很多数据的合并,这个工作可能每个月都要重复一次,但涉及的表格可能有二三十个。对于我这种非IT人士我应该怎么学习?
 

李奇:其实这种情况传统来讲我们只能用VBA,但是我建议用Power Query,建模一次可以跑无数次,只要你的数据源和你文件里的格式没有改变,有效分析就可以了。所以可以先了解一下Power Query怎么用,我的课程里面都是最基础的,帮助大家入门,如果大家在某方面感兴趣的话还需要深入学习,谢谢大家!


对李奇老师Excel商业BI感兴趣的同学可以参加CDA数据分析员课程,让你点满excel那些炫酷的技能点,微信扫码,直达课程:



数据分析咨询请扫描二维码

客服在线
立即咨询