Excel商业智能分析报表「玩」法解析

本文为CDA金牌讲师李奇原创,转载请在本平台申请授权

随着大数据时代的到来,企业管理者对数据价值的重视度越来越高,他们渴望从企业内外部数据中获得更多的信息财富,并以此为依据,帮助自己做出正确的战略决策。在此种大环境下,缺乏洞察力的传统业务报表已经开始无法满足复杂市场环境中的企业决策需求,在很多企业中,“能否基于业务分析提供更具商业洞察力的数据信息”正在逐步取代“能否准确、及时地提供业务报表”成为考核业务人员能力的重要参考指标。为了能够提供更具洞察力的信息,需要业务人员强化以下两类能力:

  • 强化所从事业务工作中的相关知识以及与该业务知识相关的其他扩展知识
  • 强化对工作中使用工具的驾驭能力:考虑到大部分业务人员在业务分析中所使用的工具都是Excel,此项要求简单来说就是,不只要会用Excel,还要能把Excel“玩”出水平

本文将为大家介绍的主要内容就是关于以上第二项能力,把Excel“玩”出水平的方法,此类方法称之为“Excel商业智能分析报表”的制作方法。

在数据分析领域中,自古以来,“商业智能”就是提高企业商业洞察力的重要方法,英文缩写为“BI”,我们先来通过下边两个例子快速了解一下“商业智能报表(以下简称BI报表)”与传统业务报表的区别:

案例1: 某份传统财务报表是这样的:

而对其稍作加工后,变为足具洞察力的杜邦分析仪(BI报表):

我们可以通过杜邦分析仪快速了解各财务指标间的构成及占比关系,并从中快速发现造成关键指标同比上升或下降的原因出在哪些相关指标上。

案例2:一份传统的销售管理报表可能是这样的:

而稍作加工后就会变为会讲故事的BI报表,像这样:

销售经理可以从上边BI报表中快速了解到销售商机中的风险点在哪个销售角色中的哪些销售阶段上,从而做出快速、准确的销售决策,以确保销售周期结束前可以成功达成销售目标。

一套完整的BI报表应该至少具备以下四个条件:

  • 条件一:能够批量处理有一定规模的数据
  • 条件二:能够保证数据的时效性及准确性
  • 条件三:能够将实际业务中所涉及的所有相关数据整合到一起,搭建统一的多维数据分析环境(多维数据集)
  • 条件四:能够实现交互式操作

下边内容将逐一为大家介绍在Excel上达成上述条件的方法。

想用Excel制作满足所有条件的合格的BI报表我们需要掌握以下技能树中的相关技能:

上述技能中的Power BI插件可以帮助我们达成在Excel中制作BI报表的前三项条件。Power BI是微软为强化自身产品商业智能功能而开发的工具集。其中供Excel使用的主要插件工具包括Power Query、Power Pivot、Power View及Power Map这四款插件。这些插件工具均由微软免费提供下载,适用于Excel 2010以上版本。根据Excel版本的不同,有些工具已经预先安装在Excel中,可以在加载项中直接激活使用。而有些则需要先通过网站下载后再激活使用。

这些BI插件大幅扩展了Excel在数据处理、数据分析及结果展现方面的能力,使Excel从一个传统的表格工具华丽变身为集表格与BI功能为一身的综合数据分析、处理及展现平台。

这里主要为大家介绍Power Query及Power Pivot这两个最为重要的插件工具。省略Power View与Power Map的理由不是因为它们不重要,而是因为这两个插件更像两个独立于Excel之外的工具,它们虽然需要通过Excel启动,但启动后它们会在Excel工作表中生成自己独立的操作及展现界面,无法与Excel的其他功能结合使用,不适用于在Excel界面中创建完整的BI报表。

而Power Query与Power Pivot这两款插件虽然操作界面独立于Excel表格界面之外,但与表格界面共享同一数据源,展现界面也是Excel的表格界面,所以这两款插件是最适合Excel BI报表的插件工具。

Power Query及Power Pivot联手可以帮助Excel完成很多BI功能上的突破:

  • 提取整合多数据源数据(如各种关系型数据库、Excel文件、txt格式及csv格式等文本文件、Web页面、Hadoop的HDFS等等)
  • 关联多个数据源数据,建立统一的多维数据模型
  • 突破Excel表格的数据限制(它们可快速处理几百万甚至上千万行的数据)
  • 可通过插件自带的函数公式灵活创建自定义数据处理及计算规则

了解了Power BI是什么之后,我们再回到制作BI报表的四个条件上,为了满足“条件一:能够批量处理有一定规模的数据”,就需要Excel能够拥有类似数据库的处理“表”结构数据的方法。“表”结构数据与Excel的“表格”数据最大的不同就是“表”结构数据的最基本处理单位是“列”而不是“单元格”,“列”在“表”中又被称为“字段”,对“表”中某个字段进行计算后所有该字段行中的数值都将发生变化,只有具备了对“表”进行操作的能力,才有可能快速批量处理大量数据以及在不同表间建立联接关系,对“表”的操作是BI以及其他数据分析方法(预测分析、数据挖掘等)的基础,在Excel中,Power Query以及Power Pivot正是以“表”结构方式对数据进行导入、存储以及操作的。

条件二是“能够保证数据的时效性及准确性”,为了满足此条件,Excel必需具备能够导入不同数据源的外部数据并且能够随时与这些数据源进行数据同步的能力,利用Power Query以及Power Pivot可以简单快速地对多种数据源数据进行导入及同步更新。

条件三是“能够将实际业务中所涉及的所有相关数据整合到一起,搭建统一的多维数据分析环境(多维数据集)”,多维数据集是相互间通过某种联系被关联在一起的不同类别的数据集合。多维数据集在咨询公司以及BI工具厂商的介绍性资料中又被称为“立方体(Cube)”或“多维数据模型”,在这些资料中常以一个立体正方形的形式出现。

多维数据集可以从多角度用数据全面映射某种业务的实际状况。因为在企业运作中,任何业务都不是孤立存在的,只有多方考虑各种关联因素才能掌握业务全貌,做出正确决策。比如当出现上季度业绩不佳的情况时,其原因可能来自于产品的渠道商不给力,或是产品竞争力下降,或是本公司销售人员的能力所致,还有可能是这些原因共同作用的结果等等……市场业务人员只有将所有相关因素放在一起综合考虑才有可能正确把握发生的情况,而多维数据集正是为满足这样的业务要求而产生的。所以创建全面的多维数据集是制作BI报表的关键,利用Power Pivot的关系图视图模式可以非常方便地快速搭建多维数据集。

条件四是“能够实现交互式操作”,交互式操作在BI报表中一般指动态图表,动态图表是能够随时响应用户操作指令改变展现结果的图表。动态图表是Excel中较为高级的图表应用形式,一旦图表从静态变为动态后,分析的深度及广度都将得到质的改变。一个专业的BI报表必然不能缺少优秀的动态图表元素。在不使用VBA的情况下,一般有两种方法可以让图表动起来。第一种方法比较简单,就是用切片器直接控制数据透视图表的方法,这种方法只适用于有切片器功能的Excel 2010以上版本。

第二种方法是使用控件、公式改变静态图表数据源的方法。这种方法是在切片器出现前就存在的传统方法,缺点是制作起来比较麻烦,而且要保证公式引用区域及控件链接区域始终正确有效,限制条件较多。优点是适用的图表类型广泛,不仅适用Excel自带的传统图表,就连需要特别制作的自定义图表(比如地图)也同样适用。

在满足以上四个条件后我们基本上就可以在Excel中制作BI报表了,不过为了使制作的BI报表在展现形式上更为美观,在使用感受上更为亲切、方便,我们还需要学会专业商务图表的制作技巧以及一些简单VBA程序的编写方法。

想要在Excel平台上制作出“好看”的专业图表,除了要熟练掌握Excel的基本制图功能外,还要了解一些制作自定义图表的重要技巧,这些技巧能够帮助我们在图表表现形式及图表呈现方法上实现创新,只有摆脱Excel基本制图功能的限制,不断对图表进行改进创新,才能制作出时尚美观、能够满足实际分析需求的图表。

比如嵌套多层饼图及环形图制作的半圆形仪表盘:

或者是用Excel公式及条件格式功能制作的MINI图等。

如果把BI报表比作一盘菜,那么VBA程序就是菜中的调味料,有了“调味料”,“菜”才能更有味道。使用VBA程序不仅可以简化报表的制作及操作过程,还能够增强报表的互动性、自动化处理能力、界面及图表的展现效果、数据加工处理能力及数据分析能力,令智能报表更加“智能”

例如可以用VBA将环形图自动填充至折线图中的不同节点处,完成折线环形图的快速嵌套制作:

还可以利用VBA写一段Funcation函数用以返回切片器筛选值,令阅读者一目了然掌握当前筛选项状态:

在掌握了以上Excel应用方法后,再结合自身的业务需求便可以简单地制作出令领导满意的具有商业洞察力的商业智能报表了。

原文发布于微信公众号 - CDA数据分析师(cdacdacda)

原文发表时间:2016-07-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏WeTest质量开放平台团队的专栏

你手机的电都去哪儿了?

与传统的APP相比,手游的耗电量那可不是盖的,手机还有10%的电,玩局游戏吧,正玩着HIGH呢,马上就要破记录了,无情的手机提示电量耗尽30秒后强制关机,欲哭无...

29130
来自专栏企鹅号快讯

2018,如何从小白升级到大牛程序员呢?

关键时刻,第一时间送达! KS Knowledge Sharing 知识分享 现在是资源共享的时代,同样也是知识分享的时代,如果你觉得本文能学到知识,请把知识与...

245100
来自专栏计算机视觉战队

深度学习入门必备的13张小抄(附下载)

机器学习领域的知识太多了,学习的工具包,命令、操作和公式都是数不胜数,让“新军”们理解记住太难了!所以,学生时代的一件利器派上用场了,那就是人见人爱的“小抄”,...

40180
来自专栏SDNLAB

SD-WAN是否会取代边缘路由?

软件定义广域网(SD-WAN)会取代边缘路由吗?随着SD-WAN持续增长,这个问题变得日益突出,答案比想象中更加复杂。造成这种复杂性的原因在于如何定义边缘路由,...

31360
来自专栏机器之心

AI研发者福利!谷歌推出数据集搜索专用引擎Dataset Search

Dataset Search 测试版地址:https://toolbox.google.com/datasetsearch

9720
来自专栏IT大咖说

魅族推荐平台架构

摘要 魅族是一家智能手机研发公司,也是一家互联网公司,拥有超大规模的用户量及海量数据量,魅族推荐平台实现了在海量的数据中对算法模型进行在线及离线训练,在高并发的...

39940
来自专栏量子位

游戏大咖Unity发布机器学习工具,可大幅提高NPC的“智商”

安妮 编译整理 量子位 出品 | 公众号 QbitAI ? 欢迎来到游戏世界。 没听过Unity没关系,你肯定对王者荣耀、纪念碑谷、神庙逃亡2、Pokémon ...

43160
来自专栏机器人网

购买视觉系统:您必须询问的10个问题

在世界各地无数生产和制造环境中,机器视觉系统始终确保数百万计的产品符合严格的质量和安全要求。一个有效的视觉系统可以消除缺陷、验证装配以及跟踪和采集生产流程每个阶...

30490
来自专栏数据派THU

【数据蒋堂】报表应用的三层结构

来源:数据蒋堂 作者:蒋步星 本文长度为1700字,建议阅读3分钟 本文为你分析报表应用的三层结构。 在传统的报表应用结构中,报表工具一般都是与数据源直接连接,...

21750
来自专栏机器之心

业界 | DeepWarp:一款可以玩转所有人眼球的机器学习Demo

选自the verge 作者:Lizzie Plaugic 机器之心编译 参与:黄小天 最近发生了一件互联网趣事:一款可以操控面部表情的神经网络 Demo——D...

378110

扫码关注云+社区

领取腾讯云代金券