Friday BI Fly | 2016年11月18日晚8点半微信直播交流Excel商业智能案例分享第40场 图文记录

浏览: 3382

公告

周五BI飞起来,每周一个主题,一场跟数据有关的行业、工具、技术的交流盛宴,锁定在每周五晚20:30,不见不散!

未来几期的微信直播活动分享主题将包括在房地产行业如何做数据分析地理大数据如何驱动智慧选址

具体日期安排请关注天善智能问答社区活动版块
https://www.hellobi.com/events


本期分享内容

1、 Excel商业智能分析报表“玩”法介绍

2、如何应用Excel创建会讲故事的销售漏斗分析仪

3、教你掌握Excel中最为重要的逻辑 —“链接”应用

用Excel对话数据-零基础Excel商业智能分析 

https://edu.hellobi.com/course/118


本期嘉宾介绍

李奇,特邀社区专家,曾任职于IBM及德勤会计师事务所,从事业务分析及数据分析咨询工作,擅长用Excel创建商业智能报表。


主持人
大家好,我是微信直播活动的主持人悟,每周一个主题,一场跟数据有关的行业、工具、技术的交流盛宴。我们的口号是“Friday BI Fly 周五BI飞起来”。

下面我们就正式开始今天的活动了,关于我们的微信直播活动,新来的朋友可能还有各种疑问,下面我就针对常见问题给大家做个解答。


Excel 商业智能案例分享

大家好,我是天善智能签约讲师以及电子表格应用大会主席李奇,很高兴今天能够在这里跟大家分享一些Excel商业智能方面的东西。大家可以通过以下网址阅读或观看我发在天善博客中的一些文章以及天善学院中的视频

博客: https://ask.hellobi.com/people/blogs/LiQi

视频: https://edu.hellobi.com/course/118

今晚,我将围绕“什么是Excel BI”、“Excel BI应如何实现”以及“Excel BI的应用案例”三方面来进行讨论。

第一部分: Excel BI是什么

         这个话题我们分为两个阶段进行讨论,第一阶段先来讨论什么是“BI”。第二阶段再来讨论什么是“Excel BI”。关于“BI”,已经有无数先贤为其定过义,也深入讨论过它的应用价值,所以这里论述的内容只代表我个人对“BI”的观点,如有不妥之处还望见谅。我认为“BI”有两个侧面,一个是“业务”、一个是“技术”。“BI”源于业务需求、终于技术实现。“BI”的核心价值在于为决策者解决某个业务问题提供数据参考信息,

blob.png

而为了达到此目的,“BI”至少需要满足以下四点技术要求:

1.能够批量处理有一定规模的数据。

2.要能够整合并处理关于所要分析业务的所有相关数据,也就是搭建统一的数据分析环境(多维数据集)解决企业的信息孤岛问题。

3.要能够保证数据的时效性及准确性,也就是用来分析的数据集要能够与外部数据进行同步更新,当外部数据源发生变化时分析用数据集中数据也应及时更新到最新状态。

4.要能够实现交互式可视化数据展现。交互式代表将更多的观察自由度交由“BI”报表的阅读者支配,让阅读者在不依赖别人辅助的情况下也能够快速获得所要的数据信息。而可视化展现则是让阅读者能够简单直观地看懂数据背后的信息。“BI”报表通过交互式与可视化两大武器搭建出人与数据间的桥梁,让二者可以顺畅地进行对话。

讨论过“BI”之后,接下来要讨论的内容就是“Excel BI”是什么。从结果而论,“Excel BI”就是用Excel创建的BI分析报表(BI仪表板、分析仪、监控仪等)。阅读者可以通过Excel文件获得对所需数据信息的洞察能力。Excel自1987年登录Windows平台以来,已有近30年的发展历史。Excel已经从最初只能处理几万行数据的制表、计算工具逐步演变为不设数据处理上限的综合数据处理、分析及展现平台,结合微软为Excel开发的Power BI系列插件功能,在Excel平台上已经可以毫不费力地创建出满足前文提到的四大基本技术要求的BI报表了。因为Excel是最为普及的办公工具,所以“Excel BI”有技术准入门槛低、通用性强、实施成本低、可以由非技术人员独立制作完成等优势。但是与其他BI工具比较,Excel也有针对大数据处理能力不足、多角色连线分享操作功能有限、图表展现效果不足等缺陷。所以还是需要结合实际业务需求来选择是否需要在Excel平台上创建BI报表。个人认为Excel BI比较适合数据处理量级在千万行以下并且对联机处理需求不高的中小企业、或大企业的某些独立部门使用。因为Excel BI有贴近业务人员、技术要求若的特点,所以如果要为Excel BI定义一个概念的话,可以将Excel BI定义为“办公型BI”。

blob.png

第二部分 Excel BI应如何实现

用Excel制作商业智能报表主要有以下四个步骤:

blob.png

所需要的相关Excel共有五大类,内容如下:

blob.png

在整个Excel BI报表的制作流程中,从导入外部数据到搭建多维度数据分析平台都是由Power BI系列插件中的Power Query以及Power Pivot两款插件工具完成的,而最终的交互式展示界面可以在Power View界面或者Excel表格界面中制作完成。

blob.png

例如上图是一个Excel商业智能分析报表的制作流程图,报表参照的外部数据可以是单个文件,可以是数据库也可以是某个文件夹下的多个文件。使用Power Query插件链接到外部数据源并将数据源中的数据以表的形式导入到Power Query中,导入后的数据称为原始数据,再接下来继续使用Power Query对原始数据进行整理加工,加工整理好的数据可以直接用来搭建多维数据集或是分析使用,所以称为有效数据。再接下来使用Power Pivot插件搭建多维数据集以及创建观测值字段的汇总规则,最后是在多维数据集的基础上进行数据的交互式可视化展现,在Excel中,可以在Power View界面或者Excel表格界面中制作交互式可视化仪表板。

以下是Power View界面的展示案例和Excel表格界面的展示案例供大家对比:

Power View界面展示效果

blob.png

Excel表格界面展示效果

blob.png

知识扩充

Excel的Power BI系列插件是微软为强化Excel产品商业智能分析功能而开发的工具集。Power BI插件工具集有四款主要插件,分别是Power Query、Power Pivot、Power View以及Power Map。这些插件工具均由微软免费提供下载,适用于Excel 2010以上版本。根据Excel版本的不同,有些工具已经预先安装在Excel中,可以在加载项中直接激活使用。而有些则需要先通过网站下载后再激活使用。这些BI插件大幅扩展了Excel在数据处理、数据分析及结果展现方面的能力,使Excel从一个传统的表格工具华丽变身为集表格与BI功能为一身的综合数据分析、处理及展现平台。

blob.png

第三部分 Excel BI的应用案例(基于Excel制作销售漏斗管理分析仪)

接下来,将为大家一个基于销售漏斗模型的Excel BI应用案例。内容将从销售漏斗管理分析的业务背景、Excel平台上的实施方式以及销售漏斗管理分析仪的实现过程三方面进行说明。

1.销售漏斗模型是什么

销售漏斗指的是将从发现商业机会开始到最终与客户成交为止的销售过程按照不同的销售进度分为几个不同的销售阶段,每个销售阶段代表一个已经达成的销售进程。在销售漏斗中销售阶段的数量及设定方法依据企业的经营方式不同而不同。比如,下图是某企业在销售管理业务中使用的销售漏斗模型。此销售漏斗将从发现潜在商机到最后与客户成交的整个销售流程分为七种不同的销售阶段,商机数由上至下越来越少,但与此同时,商机的成功率却由上至下越来越高,就好像一个生活中使用的漏斗,当我们为漏斗注满水后,处在漏斗上部的水量最多,而越往漏斗下部则水量越少,但真正能供我们使用的只有从漏斗最底部漏出的少量的水。销售漏斗的管理目的就是让每一个销售阶段的商机都尽可能顺利地过渡到下一个销售阶段,只有将每一个销售阶段的商机全部管理好才能最终实现从销售漏斗最底层获得销售收入最大化的目的。销售漏斗管理的本质是企业经营方面的风险管理,它直接关系企业的盈亏状况,是企业销售环节的生命线。

blob.png

2.销售漏斗分析的目的

了解了销售漏斗模型后,我们再来了解一下针对销售漏斗模型进行数据分析的目的。针对销售漏斗模型的分析目的在于提前发现各销售阶段中潜在的商机风险,并通过分析结果指导销售管理人员及时做出销售策略上的调整,以期达到销售资源最优化、销售效率与商机转换率最大化的目的。销售管理分析在整个销售运营业务的多个环节中都发挥着重要作用,请参照下图了解销售管理分析所涉及到的销售运营业务环节(粉色圆圈标出的销售运营环节)。

blob.png

3.如何做好销售漏斗分析

想要做好销售漏斗管理分析我们需要在以下几个分析环节中分别解决一些针对该环节的关键问题:

分析环节1 数据获取: 销售漏斗管理业务涉及的相关数据往往来自于多个不同的数据源(亦称为不同的数据维度),企业销售业务越复杂,参与协作的部门及关联企业就越多,所需的业务维度也就越复杂。在数据获取环节中,我们需要一种能够快速与不同外部数据源创建链接并保证当这些数据源的数据发生变化时能够随时与之同步更新的数据获取方式。除此之外,因为需要从不同数据源导入大量数据,所以需要具备能够获取百万行甚至千万行量级数据的能力。在Excel平台中,可以应用Power Query插件来实现数据获取需求。

blob.png

Power Query操作界面

分析环节2 数据处理及整合: 如果想要洞察到涉及多个不同数据维度的商机风险,就需要我们将获取到的多种不同维度的数据汇总到同一个多维数据模型中,并以此模型作为分析环境以便开展接下来的数据分析工作。除了创建多维数据模型外,我们还需要一种类似数据库一样的“表”数据批量处理方式,这种方式应以字段而非单元格作为数据的基本处理单位,应用此方式能够帮助我们实现灵活、高效且迅捷地处理海量数据的分析需求。在Excel平台上,应用Power Pivot插件可以满足创建多维数据模型的需求,应用Power Query插件可以满足灵活、高效地批量处理表结构数据的需求。

blob.png

Power Pivot操作界面

blob.png

搭建多维数据模型

分析环节3 数据分析: 在此分析环节中,我们需要紧密结合业务需求将透视分析、多维分析运用到极致,从多维环境下深度透视关键指标,以完成及时洞察风险商机的目的。此时Excel提供的基本数据透视功能已经无法满足我们的业务分析需求,所以需要运用到更为强力的透视分析工具Power Pivot来实现上述两项分析需求。

blob.png

应用Power Pivot实现关键指标透视分析

分析环节4 结果展现: 在此分析环节中,我们需要应用到Excel基础制图以上的功能,根据业务分析实际需要,制作具有针对性的简洁、直观的自定义图表以及具备交互效果的动态图表。必要情况下,我们还可以用到VBA宏程序来解决实际问题。

blob.png

用具备升维效果的折线图与饼图的嵌套图表来展现各时点商机的构成变化情况

blob.png

用动态嵌套柱形图来展现销售阶段的周变化情况

在满足以上所有分析需求后,我们就可以在Excel平台上制作会讲故事的销售管理分析仪了,制作分析仪的目的在于有条理的、简单易懂地告诉销售管理者你手中的商机处在何种情况下、它们可能正面临何种问题、需要管理者及时做出何种响应来回避风险,达成销售目标。其实对于销售管理者而言,面对现有商机可能无法完成销售业绩的问题一般只能做出两大类举措来改善现状。第一类是命令手下销售人员找新的商机进来提高商机总量,下这种命令一般是出现了商机总量不足的情况,即便现有商机都在有条不紊的向前推进,在销售周期内也还是很难达到销售目标值时便只能通过补充新的商机进来提高商机总量的方式来应对了。还有一类举措是命令手下销售人员尽可能快地将手头商机向前推进,下达这种命令主要是因为虽然商机总量足够大,但其中很多商机在低销售阶段停留过久,出现了商机无法在销售周期内完成的风险。在这种情况下,如果做出补充新商机的错误判断,就会更加分散销售资源,降低销售效率,造成本来能在销售周期内完成的商机也无法完成的严重后果。摆在销售管理人员面前的就像是一道二选一的选择题,正确率是50%,但一旦错误,就会造成无法挽回的毁灭性后果。所以销售管理分析仪正是帮助销售管理者提高选择题正确率而存在的,众多指标都只在为销售管理者标明一条宽广且正确的销售决策之路。

1.Excel销售管理分析仪的诞生

在使用BI销售管理分析仪之前,很多销售管理分析报表是这样的:

blob.png

这份报表只是将关键指标进行了罗列,虽然用不同颜色标注了重点值,但离一目了然的发现问题,并洞察问题原因所在的目的还相去甚远。为了解决这样的业务痛点,我们将原始数据进行再加工整理,制作出以下基于Excel的销售管理分析仪:

blob.png

这份分析仪的设计着眼点在故事性上,我试图用自上而下的图表排列顺序来展现由大到小的业务问题,当阅读者从上至下将所有图表读下来后,在脑中可以形成一个由现象到原因、由整体到细节的完整故事。故事脉络清晰完整,读完后阅读者便能快速理解其内容并做出响应。那么这个故事究竟是怎样的呢,接下来让我们逐章阅读一下:

序章:仪表盘展现总体趋势问题

blob.png

通过分析仪上方的六张仪表盘,可以快速发现在大趋势中哪类销售角色管理的商机出现问题最大。在很多复合型销售业务企业,销售任务并不是由单一角色完成的,而是由不同维度的不同销售角色共同完成的。比如地域维度的销售人员主要负责按地区划分的销售片区,产品维度的销售人员主要负责自己手头负责的单类产品,而行业\领域维度的销售人员主要负责自己精通的行业或者领域中的企业客户。然后阶段4以上和阶段5以上的商机总金额一般是最为关注的指标。在案例中阶段4为“明确”,即已经明确了客户的购买意向,而阶段5为投入,既公司已经决定为了能够成功与客户成交,需要投入一定的销售成本。假设在案例中,销售阶段4以上的商机总金额需达到销售目标值的1.5倍以上,而销售5以上的销售目标值需达到销售目标值的1.2倍以上,才能预测在销售周期内有可能完成销售目标。所以六个仪表盘就分别代表了在不同销售维度中销售阶段4以及销售阶段5以上商机总金额与目标值之间的占比情况。我们从中可以迅速发现最有问题的销售维度出在哪里。发现大问题后,故事就算拉开了序章,接下来就到了要找出问题原因,制定解决方案环节了。在序章中应用到的Excel技巧为仪表盘的制作技巧及动态图表展现技巧。仪表盘是用环形图与饼图嵌套而来,动态图表是由下拉列表控件与offset公式组合应用实现的。

开展:了解各阶段商机占比

blob.png

故事的开展是由表现各销售阶段商机占比情况的瀑布图展现的,通过瀑布图我们可以了解到低阶段商机与高阶段商机的占比情况,以及商机总量的数值情况。如果低阶段商机多,说明商机向高阶段转变乏力,需要销售人员积极推进,如果商机总值低,则需要补充新商机进来。此阶段的瀑布图是通过excel基本函数rept得来的。

高潮:了解单个销售阶段商机构成

blob.png

故事的高潮部分是用一个动态的组合柱形图展现的。通过此图我们可以清楚了解到需要重点关注的销售阶段是由上周的哪些销售阶段转变而来的。比如上图反映的就是当周“投入”阶段的商机构成。当周“投入”阶段商机是由上周“潜在”阶段商机转换81、“明确”阶段商机转换91……而来的。了解了商机的周变化情况,就可以判断销售人员对手头商机的推进力度情况,为是否应该做出让销售人员积极推动商机的决策提供了更深层的数据依据。

尾声:了解细节

blob.png

故事的尾声结束在对不同销售维度的细节展现上,通过前边的内容,精明的销售管理人员已经可以大致做出大方向上的决策判断了,再通过阅读尾声的细节图表信息,销售人员便可以对某个具体销售人员做出更细致的销售指导,以便整个销售策略能够得到顺利开展。尾声部分的内容是通过切片器、Power Pivot数据透视图表、KPI指标设定等功能实现的。

到这里一个会讲故事的销售管理分析仪就做完了,在前台展现界面的背后是应用Power Query与Power Pivot加工处理过的数据模型,它们提供了前台分析所需的底层数据分析环境,它们还保证了每次外部数据更新后分析仪中使用的数据都能得到快速地同步更新。在整个分析仪的制作过程中分别应用到了Power Query、Power Pivot、Excel 基本功能、动态及自定义图表的制作技巧等相关功能。整个制作过程共用两天时间(不包括设计时间)。讲到这里今天分享内容就全部结束了,不知道朋友们是否也想快点尝试结合自己业务需要制作有洞察力的BI报表了。

如果你也对Excel BI感兴趣,欢迎关注以下网址中我的

用Excel对话数据-零基础Excel商业智能分析》课程。全部内容将在月底更新完成,现在已有6课时免费课程供大家参考学习。

https://edu.hellobi.com/course/118


主持人

悟:

非常感谢李奇老师为我们带来的精彩演讲,同时也感谢我们在场积极提问的朋友们,相信大家对今天的会议内容已经有了更深一步的了解,衷心的希望您通过今天的演讲能有所收获。接下来是自由讨论时间:


自由讨论

问题一:如何使用PowerPivot将多个excel文件合并导入为一个表格? 

李奇老师: 最好先用Power Query导入外部文件数据然后再用Power Pivot来实现多维度建模

问题二:老师推荐本实战类的书或者学习资料吧?

李奇老师: 其实我已经和出版社签约了,不过正在脱稿中,明年会有一本全面介绍Excel商业智能案例及实现方法的书,现在这方面成系统的书还应该没有,都是介绍各个单一功能的比较多。。

问题三:老师 Power Query和Power Pivot在处理数据,分析数据方面都有些什么应用?

李奇老师:Power Query可以导入数据,横向纵向合并数据,可以进行字段处理和计算,还可以利用M公式自定义数据的批处理功能,M公式,或者叫M函数是非常强大的功能,有了这个功能个人感觉可以完全抛开VBA在处理数据时的应用了,然后Power Pivot主要用来创建多维模型,制定值字段汇总规则(就是放在数据透视表值中使用的字段),创建层次结构关系、创建KPI指标等。

问题四:Power BI 如何跟 SAP BO结合使用?

李奇老师:我的理解是SAP BO是个报表工具,但聚合能力不强,可以可视化展现和What-if分析,但不能自由地开展BI多维分析。因此Power BI和 SAP BO更多地是互补关系。Power BI具有很强大的多维建模能力,这是超越SAP BO之处。另外Power BI可以使用SAP导出的数据开展可视化分析,但更多地使用原始明细数据; SAP BO可能用汇总数据多些,而且没有整合多来源数据的在哪里,Power Pivot中的函数叫DAX表达式,和Excel基本公式写法相近,主要用来自定义值汇总计算规则和进行字段介绍。

问题五:是不是Power Pivot是做模型,Power View和Power Map是做展现的呢?

李奇老师:是的,Power Pivot是做模型,Power View是做交互式展现界面而Power Map是做数据地图用的。

问题六:excel是基于文件的 能支撑多大规模的数据量啊?

李奇老师:Power Query和Power Pivot其实是利用到了更多的windows底层资源,他们的数据存储和计算已经超出了Excel文件范围,所以处理千万级数据是可以的。

友情提示天善智能是国内最大的商业智能BI、数据分析、大数据领域社区,欢迎大家登陆天善学院有更多免费行业专家数据库,商业智能BI,数据分析,大数据,数据挖掘视频和干货好文分享。


Friday BI Fly

每周一个主题,一场跟数据有关的行业、工具、技术的交流盛宴。我们的口号是“Friday BI Fly 周五BI飞起来”。 Friday BI Fly 微信直播活动目前已举办39期,往期分享内容包括:【金融行业、零售行业、旅游行业、大数据挖掘、大数据落地、数据分析师、数据治理、大数据征信、风控管理、Python实战、用户画像、数据治理、数据架构、R语言等】。

今天的微信直播活动到这里就结束了,喜欢天善智能的朋友们请继续关注我们,每周五晚8:30,我们不见不散哦!

下期预告

2016年11月25日晚8点半微信直播交流房地产行业如何做数据分析第41场

https://www.hellobi.com/event/135


每周 Friday BI Fly 微信直播参加方式,加个人微信:fridaybifly,并发送微信:公司+行业+姓名,即可参加天善智能微信直播活动。

天善智能 www.hellobi.com 是一个专注于商业智能BI、数据分析、数据挖掘和大数据技术的垂直社区平台(国内唯一的数据类技术问答社区、博客、在线学院)。

天善智能拥有国内最大的数据领域社群组织,每周五晚上 8:30 Friday BI Fly 40+ 微信群同步直播行业和大数据技术交流。

Saturday BI Fly北上广深以及全国其它各个城市 200-500人规模的大数据沙龙活动。

Clipboard Image.png

关注天善智能,关注大家都在关注的大数据社区

推荐 3
本文由 天善智能 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录注册