建立自己的自动化图表模板

VBA是一般读者不愿过多接触和学习的内容,因为这个部分总是给人高深莫测之感。若要摆脱Excel图表制作中那些低效的大量重复作业,学习VBA宏代码就是一个必须要面对的问题。

办公室人员可能需要每周、每月都重复制作相同类型的图表,虽然Excel提供了图表的自定义模板和工作簿模板,但有时直接使用这些方法只能减少部分重复作业,通过使用VBA则可以大大简化这样的操作,我们需要的仅仅是用鼠标轻轻点击一下相应按钮即可。

我们是否对Excel图表制作中简单机械的重复工作痛恨不已?这些繁复的劳作不但消耗作图时间,而且会使我们对Excel图表制作的兴趣消耗殆尽。好了,如果这已经是一个必须要认真对待的问题,那么使用VBA来定制适合自己的工具集就变得再实用不过了。

除了自己动手来编写相应的VBA宏代码,其实我们也可利用Excel自带的分析加载项来制作相应的图表。此外,借助使用第三方工具集也是一个非常不错的方法。

:虽然掌握VBA这个工具对于学习图表大有裨益,但并非必须。如果我们不打算对VBA进行更深入学习,本章的工具请直接拿来套用即可。

建立自己的自动化图表模板:面对周报、月报、年报中如出一辙的图表,我们还在日复一日、年复一年重复着这种燃烧激情和岁月的作业吗?如果回答是肯定的,我们是否想过,并且尝试去使用更加高效的方法,来实现这些简单、重复、低效的工作?如果没有,请不要再犹豫,马上行动起来,建立属于自己的自动化图表模板吧!

需求分析

Excel提供了自定义图表模板和工作簿模板,这样的操作虽然可以简化作业,但是在某些方面,依然不能有效地解决问题。如图13.1-1案例图表是非常典型的帕累托图表,如果仅是更新图表的数据,来重复使用这个图表,每次都必须要面对如下问题:

需重复使用的帕累托图

图13.1-1 需重复使用的帕累托图

  1. 将数据从大到小排列;
  2. 重新设置“辅助”列的函数公式;
  3. 修改两个系列的引用数据区域大小;
  4. 调整数值纵轴的最大刻度和“数值”列的合计相同,计算并设置相应的主要刻度单位。

上述4项中,尤其第4项,使用VBA基本是最佳的选择,除此尚无最佳方案。作为模板应该可以直接另存为副本,且不包含任何多余的作图辅助内容。

使用VBA来演练

通过13.1.1节的分析,要完成图13.1-1的案例的VBA自动化图表更新,我们将通过以下步骤来进行学习:

数据排序

一般而言,我们制作帕累托图表的首要工作就是将数据从大到小排列,在图13.1-1的案例中使用的是自动筛选功能,其实也可直接使用按钮来完成。以下是这个部分的宏代码:


程序代码 片段:13.1-1a ChartUpdate图表更新-排序

ChartUpdate图表更新-排序


排序的部分完全来自录制宏,然后修改排序区域获得,并不复杂,需注意Key1:=Range(“C6”),此处是为首要排序基准。唯一有难度的是xRow变量,在此使用了[B65536].End(3).Row来赋值,这是为了和Excel 2003兼容,在Excel 2007/2010中,可以使用[B1048576].End(3).Row

:下述代码中凡涉及xRow变量的部分,一律使用红色字进行了高亮标注,请读者参考录制获得的代码进一步修改即可。

重置函数公式

接下来由于受到数据的最大行影响,“辅助”列的函数公式所在的单元格,及公式中的单元格引用均会不同,因此重置“辅助”列的函数公式便是需要考虑的内容。录制宏:选中“辅助”列的函数公式所在第一个单元格,鼠标指向编辑栏结尾处,按下键盘Ctrl+Enter组合键,向下拖曳复制函数到最大行。最终修改后的宏代码如下所示。


程序代码 片段:13.1-1b ChartUpdate图表更新-重置函数公式

 ChartUpdate图表更新-重置函数公式


图表设置及数据引用更新

由于在VBA代码的执行中,需要反复调用图表对象,所以在此处图表的设置和数据引用更新一并进行讲解。录制并修改后的宏代码如下所示。


程序代码 片段:13.1-1c ChartUpdate图表更新-图表设置及数据引用更新

ChartUpdate图表更新-图表设置及数据引用更新


:在录制宏时,修改系列引用请使用[源数据]对话框,请勿直接修改SERIES公式,这会导致后期宏代码修改的难度增加。

后期整理

完成第3步操作后,就已经完成了13.1.1节分析的4个要点,但工作表上的数据表依然需要设置统一的外观样式,否则由于每次数据有多有少,这将严重影响美观。以下是录制并修改后的宏代码:


程序代码 片段:13.1-1d ChartUpdate图表更新-后期整理


复制工作表

接着新建一个工作簿,并将上述步骤处理的工作表复制进去,并删除表格上的辅助部分。以下是录制并修改后的宏代码:


程序代码 片段:13.1-2a SaveAs图表更新-复制工作表


保存新工作簿

最后是将新工作簿保存到指定位置,完成最终宏代码的编写。以下是录制并修改后的宏代码:


程序代码 片段:13.1-2b SaveAs图表更新-保存新工作簿


其他

最后是向工作表添加触发器,来触发VBA宏代码的执行,如图13.1-2所示。推荐的做法是使用工作表控件按钮,切勿使用窗体控件,窗体控件需要在工作表代码区编写相应代码,这会给新建工作表带来麻烦,因为使用VBA来删除这些代码相对较为复杂,这也是代码写在模块,而非工作表代码区的原因。

图13.1-2 使用工作表按钮驱动VBA宏代码

:为了保证代码的兼容性,此处使用的按钮名称被重命名为“按钮1”。方法是单击鼠标右键,选中按钮,在编辑栏左侧编辑框中修改,见。图13.1-2右侧的Test Data数据区为调试代码的数据,因为函数公式会影响排序结果,所以请直接选取必要数据区域,复制并“选择性粘贴:值”到Chart Data数据区进行测试。

基于隔离的兼容

自Excel 2007版本起,Excel不但菜单系统发生了翻天覆地的革命性变化,同时也完全颠覆了以往Excel版本赖以继承的基本结构,尤其是图表引擎更是一个和之前版本没有太多关联的全新版本。当职场办公环境可能存在多个Excel版本混用的情况时,兼容性就成了一个必须要正视的问题。

传统认知是:软件的版本升级,意味着其可以很好地进行向下兼容,即2003的文档,使用2007或2010打开可以进行很好的解释。但是在此次的Excel版本交替中,这是一个必须审视的问题,尤其是当文档中包含了图表,有时会因为兼容性的问题使图表显示不正常,Excel 2007甚至会出现宕机。Excel使用一种称为“兼容模式”的方式,来进行Excel 2003和更高版本的相互解释,这种解释方式采用隔离的模拟环境来实现。当理解了不同版本图表引擎可能完全是两个不同的概念时,这个问题的解决办法就是找到Excel 2003和更高版本之间的交集。

向下兼容

苛求Excel 2007这个全新的图表引擎,在第一版会有怎样优秀的表现显然不切实际。所以使用Excel 2007的读者,一定需要及时打上微软最新的补丁,这些补丁是对相应版本的修正和完善。即便是Excel 2010也应及时打上这些补丁,这有益于使我们日常工作更有效率。

知识扩展


最新Office补丁下载地址:

Office 2007 SP3补丁office2007sp3-kb2526086-fullfile-zh-cn.exe:

http://download.microsoft.com/download/B/E/E/BEE808C7-AE99-49D3-B5CB-1FE4BE54EF4A/office2007sp3-kb2526086-fullfile-zh-cn.exe

Office 2010 SP1补丁officesuite2010sp1-kb2460049-x86-fullfile-zh-cn.exe:

http://download.microsoft.com/download/B/B/D/BBD98807-801E-4286-9D90-DBEBFA6809B9/officesuite2010sp1-kb2460049-x86-fullfile-zh-cn.exe


Excel 2007的兼容非常糟糕,甚至有些问题显得很莫名其妙。经笔者的测试,某些特殊情况下Excel 2007无法有效兼容互补色的设置,以及数值次序反转下误差线的绘制,同时对于使用多个XY散点的组合图表支持更是糟糕,严重情况下会导致Excel 2007长时间没有反应,即便是打上了补丁也依旧如此。

知识扩展


本章图11.1-4、图12.3-4案例的互补色在Excel 2007中会显示不正常,请参阅:

1)本书“4.3.4 Excel的‘前景色’和‘背景色’”章节内容;

2)“8.1强烈表达差异”章节内容。


Excel 2007和2010对网格线的设置,相较2003完全不同,这使得在多层坐标标签中,使用网格线来进行分割布局已经不被支持,要考虑到兼容的问题,在Excel 2003中就必须使用XY散点图来实现。如本章图12.2-1中的案例❸,在Excel 2007和2010中的显示将变得非常糟糕。

向上兼容

Excel 2007和2010使用了基于XML格式的新类型文档格式,由于Excel 2003和更高版本文档存储的不同,微软提供了隔离环境下,Excel 2003可以打开、编辑、保存Excel 2007和2010新格式文档的兼容性补丁。使用Excel 2003的读者,这个补丁必须要安装。

知识扩展


补丁名为FileFormatConverters.exe,下载地址为:

http://download.microsoft.com/download/6/5/6/6568c67b-822d-4c51-bf3f-c6cabb99ec02/FileFormatConverters.exe

前提是:在安装这个兼容性补丁包前需要通过Microsoft Update安装所有高优先级的升级。

附:Office 2003 SP3补丁Office2003SP3-KB923618-FullFile-CHS.exe的下载地址为:

http://download.microsoft.com/download/9/b/f/9bf7e3b9-0a8e-4dc8-bd14-f157d8e908c4/Office2003SP3-KB923618-FullFile-CHS.exe


Excel 2003并不具备更高版本中的各类视觉渲染,比如光圈、透明度、倒影、三维等效果,所以当要考虑兼容性的问题时,图表中请勿使用这些效果,这些效果会在Excel 2003中变得很糟糕,但图表的颜色包括透明度却可以很好地被保留。

从向上兼容来看,Excel 2003兼容Excel 2007和2010的图表要相对好些。极坐标类型的图表:在Excel 2007和2010中可支持环形、饼图、雷达图同时组合到一个图表中,虽然操作上Excel 2003只支持任意两种图表类型的组合,但Excel 2003支持显示Excel 2007和2010的这种三合一类型图表。

考虑到兼容问题,在Excel 2007/2010中制作的图表,切勿出现在同一图表的数据源中既包含行引用,又包括列引用,否则在Excel 2003中打开后,可能看到糟糕的状况,如本书图7.4-6案例所示。

版本转换

安装兼容补丁后的Excel 2003支持将文档另存为Excel 2007和2010的新格式文档,当这些文档中包含了图表,若图表的设置相对复杂,则保存后的文档将可能丢失相关的格式设置,尤其是Excel 2007。当将Excel 2007和2010文档格式另存为Excel 2003格式,同样也是格式设置的丢失较严重。

Excel 2007和2010支持使用文档格式转换功能,即时将Excel 2003文档转换为Excel 2007和2010的新格式文档,原始Excel 2003文档将会丢失,取而代之的是新格式文档。文档版本的升级并非使用一个“转换”命令那么简单,在转换之前还需确认是否有这个必要,这不光涉及图表,也涉及各类文档间的链接关系。新版本的文档比较小,可以通过该方法来为Excel 2003文档减肥。

笔者建议:若非必要,勿使用转换方法来解决兼容性问题,这不是一个好方法。旧格式的文档,还是以旧格式来使用更好,毕竟Excel可以使用兼容模式来打开并修改。当然随着Excel版本的升级,使用新格式是必然趋势,所以使用新版的读者还是以新格式保存为佳。

纠结的视觉表达

今天,我们有没有迷失在眼花缭乱的数据与图形世界?为了吸引更多的人来关注这些信息,从而兜售隐藏在数据与图形之后的诉求,人们极尽视觉勾引之能事,尤其是基于互联网的应用,更是乱花渐欲迷人眼。美丽常使我们沉迷于表面,而忘记事物的本质,所以有必要重新来定义我们今天所看到的图表。

形式与内容

在当今互联网,随处可见我们称为“信息图表”的一类信息载体。这类东西往往色彩搭配合理,且图形新颖,非常吸引眼球,时不时还要挑战一下读者传统的编码知识结构。有网友针对图表给出了一个有趣的评价指标:吸睛指数,不得不讲这个指数很形象。不论我们所见的图表美丽与否,其实都是一种形式,形式很重要,但比形式更重要的是图表阐述的信息和表达的诉求。请注意,这些东西虽然距离我们那样近,但离我们日常工作却是那样遥远。

图12.4-1左侧所示的图表告诉我们:《财富》世界500强中每100个CEO,只有3个是女性。问题是这只鞋子虽然极大的吸引了读者的关注,但同时也使诉求表达被弱化了,如果不是“3 out of 100”,估计很多人会不知所云。当将图表变换为右侧图表时,我想这将不会使人对诉求产生疑惑。

图12.4-1 有趣的鞋子[资料来源自互联网]

图12.4-2左侧所示的图表使用了一张百元大钞,来说明女性收入相较男性仍不公平,我们还需要努力去改善这种差别。相比右侧的图表,钞票非常吸引人的眼球,可是诉求的表达却并不尽如人意。

图12.4-2 被剪的百元大钞[资料来源自互联网]

 

图12.4-1和图12.4-2左侧所示图表其实都是趣味性极大化的应用案例,笔者只是告诉读者,不要迷恋此类图表的制作,此类图表并不适宜使用在相对严肃的办公职场环境。


图示与图表的边界

一般而言图示用来解构思维活动,比如维恩图或自选图形的流程图,图示一般和数据关联不大,且多以抽象方式来实现;而图表则以解读数据为主,用来分析和说明数据,并表达相应的结论诉求为主,相对而言较为具象,因为图形的表达可以被有效度量。但两者的区分在当前已经越来越有些模糊,往往是你中有我,我中有你。

图12.4-3左侧所示的图表告诉我们人们使用平板电脑或电子阅读器在干什么。图表并不复杂,可以轻易阅读,由于使用了图示,这个图表看上去更像图示,而非图表。在该案例中,代表10%,所以在表达1~9%之间的数字时,效果差强人意。使用右侧的图表,则更加直接地表达了数据,同时减少了人对1~9%之间数字的判读。

图12.4-3 有趣的人形图示[资料来源自互联网]

在Excel中,要实现图12.4-3左侧所示的图表也非常简单,只要将图形粘贴进条形系列之中即可,笔者并不建议读者去进行这样的实践,其看上去视觉效果良好,但数据解读效果却并不尽如人意。

图12.4-4左侧所示图表解释了美国在线的用户,根据年龄的不同,使用美国在线服务的不同。和图12.4-3左侧所示的图表类似,该图使用小方格将0~100%设置为10×10的小格子来表示百分数。由于格子数的限制,此图只能使用百分比整数方式表达,不适合小数点,而且所示图示干扰了面积相仿数值间的视觉比较。使用右侧图表则可以使读者更快聚焦到读者感兴趣的数值结果上,该图由于涉及横比和纵比两种比较方式,亦可使用Excel 2007/2010条件格式和/或迷你图。

图12.4-4 有趣的小格子[资料来源自互联网]

笔者并不建议在图表中为了视觉观感,而将图表以图示的方式来制作。除非使用图示可以更好地表达诉求,否则请切勿刻意使用。存在即是合理,此类应用有其特定使用目的,但身处职场的你我,切勿以身犯险,毕竟传统教育所灌输给我们的编码知识与此完全不同,使用此类图表基本不会得到认同,相反十有八九会得到不务正业的评判结果。

小技巧


由于微软雅黑字体当字号小于8号时显示效果不佳,故在小字号状态下,笔者推荐使用如下字体[本章的范例使用的就是这些字体]:

  1. Meiryo UI、Segoe UI、MS UI Gothic字体:适合英文及数字显示,中文字库不全;
  2. MingLiU和PMingLiU字体:仅适合中文显示,包括简体和繁体,显示英文及数字效果不佳;
  3. Zfull-GB和Zfull-BIG5字体分别对应简体和繁体,适合中英文显示。

上述字体在100%显示模式下,可支持到6.5号字的显示。中文的宋体也可,但效果不佳。本书不附带这些字体,如果读者的电脑并没有请通过互联网下载使用。

此外字体适合使用在打印环境下的英文标题,效果较好,注意:不适合使用粗体。

跃然纸上

必要时通过强调的方式,将读者的视觉进行聚焦,这样可以更好地烘托出图表的诉求,并减少读者寻找关键信息的时间。强调的方式非常简单,将图表的局部与整体通过色彩和外观的明显差异化来实现,当然差异看上去应该仍是整体的一部分。

点睛之笔

让图表的某些数据点变成读者的视觉关注焦点,非常有利于读者对更加感兴趣的局部数据点进行聚焦和对比。一般而言此类应用主要用于如当前的行业地位、最大值与最小值等的强调,诉求表达重点是强化这些数据点的位置与数值。该方法的好处是将诉求表达层次提升,而降低图表整体的其他诉求表达的层次。

图12.3-1所示的案例通过将Item 2的颜色由土红色变更为绿色,使读者的视觉马上被Item 2所吸引,并通过比较获得其所在排序位置和数值等信息。图12.3-2所示的案例则是将上升和下降两种不同的差异表达,使用两种颜色的方向箭头来进行表达,直接而形象生动。

图12.3-1 单个数据点的强调

图12.3-2 局部数据的强调

使用这种方法会使读者视觉被强调的部分受到干扰,并降低对其他信息的获取。所以使用时需要明确图表诉求的重心在何处,当然这个方法在有些场合会使读者更易接受和认同,因为这个小小的设置,强烈地表达了:我们站在读者立场上考虑问题。

楚河汉界

基于非常明确的视觉参考基准,使图表的一个部分和另一部分在视觉中产生强烈的视觉反差,可以帮助读者快速获取诸如良与差、涨与跌、达标与未达标的这类信息。此类应用主要用于如绩效考核、业绩报告等的应用,参考基准一般为目标线、控制界限、零值刻度等。

图12.3-3的案例以1000为目标,将项目分为了达标和未达标两个部分,相当醒目。图12.3-4的案例则是将超额完成和未完成数值通过零值刻度线一分为二,使用两种颜色的方式来区分,很直观。一般而言,使用红色来代表不理想的部分,使用绿色来代表理想的部分。

图12.3-3 以目标线为分界的强调

图12.3-4 以零值为分界的强调

此方法,并不局限使用一个分割线,根据应用的不同可以使用多个,比如SPC统计过程控制的控制图。不使用视觉反差的方式,很难使读者在第一时间将多个不同类型的数据进行分组筛选对比。这种方法的使用一般在定性分析场合多见,使用时需要根据表达诉求来判断是否适合。

简繁之间

我们面对的数据越来越海量而且繁杂,这是一个数据爆炸的时代,大量的数据和繁杂的数据关系,给图表带来不小的挑战。一方面读者没有时间去研究繁杂的图表,另一方面图表却又不得不变得繁杂。因此我们必须要在简单和繁杂之间进行取舍,这是一个痛苦的过程,却必须要面对。

由简至繁

在我们的日常工作生活中,所见的图表已经和以往完全不同,到处充斥着各式各样形形色色的图表。这些图表不再是我们传统认知中固有的柱、条、折线,而更多采用它们的集合体。在Excel中要实现此类应用基本无法直接获得,这需要我们具有一定的作图技巧。现实却往往是我们掌握的图表技法越多,就会使所作图表越繁杂,读者在看完这样的图表后常常感叹作图者的娴熟技法,而对图表本身的诉求茫然不知。

对于图表,简单意味着表达方式的简单直接,并使读者更易进行视觉比较。其实娴熟的图表技法是将图表变得简单的根本,大多数状况下,简单的图表视觉呈现需要使用相对繁杂的技巧来实现。如图12.2-1所示是作图技法由简至繁的过程,这个案例分别使用了三种不同表达形式:❶的制作过程最为简单,但表达并不见得简单和直接;❷的制作过程最为烦琐,虽然表达方式较❶简单和直接,但这种上下的纵向比较,很难获得最佳的比较效果;❸的制作过程相对复杂,却是3个图表中最好的一个,既方便进行趋势比较,又方便进行数据点间比较。

图12.2-1 不同视觉参考基准的折线图

图12.2-1所示是一个折线图案例,如果将图表统统变更为柱形,则使用❷的方式效果最佳。若❶的图表系列仅有两个时,则无须使用❷和❸即是最好的表达,❷和❸也不适合使用在图表系列超过5个的状况下。

化繁为简

每每问问自己,所作图表是否最简单,且最能表达诉求。其实任何一个繁杂的图表都可以转化为简单的图表,即便是这种转化看似非常困难,我们也可选择将图表变成多个来实现,甚至是直接使用表格。如果所幸我们使用的Excel版本是2007或2010,我们也可以使用多样的条件格式或迷你图。记住:方法永远不止一种。

如图12.2-2所示是《图表说话》一书中的一个案例,笔者完全使用Excel图表元素实现了这个案例。案例数据不多,但涉及比较的数据维度、量纲却很多。在这个案例中,共使用了6个图表系列,数据源的引用使用了13列数据。图表涉及的内容虽然很丰富,但表达却并不繁杂。

图12.2-2 Excel实现的《图表说话》书中的一个案例

图12.2-2案例图表究其实质,仅是一个百分比的比较。该图有些挑战读者对数据的解读,尤其在视觉中$60居然大于$300,好在图表并不复杂,所以相对而言容易理解。每个图表都或多或少地存在这样或那样的问题,所以制作图表时一定要考虑这些问题的存在,将图表变得简单是减少此类问题的一个好方法。

用心,而非用技

沟通是交互的过程,作为沟通语言的图表,是否体现了尊重读者,并理解读者感受,反映了作图者的用心程度。使用技巧将图表展示内容变得充实和丰富,才是真正的用心。没有修饰整理的图表无法体现用心,将图表修饰得花枝招展,或高深莫测只是用技而已。

面对海量数据和复杂的数据关系,需要通过繁杂的图表来应对,但繁杂的图表并不意味着要将图表变成数据杂货铺。繁杂应该体现在如何理解读者读图的过程,简单应该集中在将图表变得清爽易读上。

通过强调图表上的一点或是一个部分,此时图表的诉求就被鲜活灵动地表达了出来。这样可以使读者更快地去关注和图表诉求相关的内容,非聚焦内容将被视为辅助性说明。

过分强调视觉观感,并不会带来良好的视觉交流。抛开不必要的形式表达,图表的表达会更加流畅;尽可能减少图表绘图区的图示化内容,可以减少读者去关注无关紧要的细节。

工作中,注意文档在不同Excel版本中的兼容性,会使工作更有效率,且不会因为编辑和打开这些文档的Excel版本不同,而带来糟糕的后果。

用心,而非用技:让读者在第一眼看过图表后,感觉到我们有站在读者的立场考虑问题,这是沟通的前提。只为完成工作和只顾自我表演式的图表,不要期望可以通过沟通建立共识。图表只是一个展示数据的窗口,技巧只为使我们可以看到更广阔的天空而服务。

站在读者的立场

相同的图表,展示给不同的读者时,会因为每个读者不同的关注焦点而有不同的期望,满足这种期望的能力体现了我们的用心程度。图表一般而言,基本可以分为发现问题和报告业绩两种。以发现问题为目的的图表,读者一般会非常关注这个问题的具体细节,包括数据,因为这将影响改善的方向,以及改善的投入,职场中我们所做的图表大多是此类;而对于以报告业绩为目的的图表,读者则不会太过关注,一般采用扫视方式阅读。

在多个和图表相关的书籍中,均会提及如图12.1-1所示三种外观尺寸的图表,一般的观点是:❶的尺寸才是最佳的选择。在图表信息并不失真的前提下,笔者却不这样看待,以发现问题为目的的图表,既然要放在放大镜下审视,使用❸的尺寸更好;以报告业绩为目的的图表,一般而言,读者都喜好眼睛一扫而过,使用❷的尺寸,可以在报告中容纳多个图表,毕竟没人喜欢絮絮叨叨,浪费他人时间和精力的沟通。当然视具体应用,❶的尺寸显得中庸而不犯错,较适合严谨的学术类报告,统一报告中图表的外观尺寸,有助于读者建立良好的视觉体验。

不同尺寸大小的图表给人的视觉感受

图12.1-1 不同尺寸大小的图表给人的视觉感受

图表的诉求表达来自视觉比较,哪怕仅是表达单一数据点的仪表板类图表。明确地告诉读者,比较的参考基准非常重要。让读者在图表中满世界寻找视觉比较基准,说明我们根本没有用心考虑这个问题。就如图12.1-1所示,直觉告诉我们:他们完全不同,实际却是:这三个图表完全是同一个图表,产生这个问题的根源是这个三个图表没有置于同一视觉参考基准之下。

如图12.1-2所示的三个图表:❶虽然没有给出视觉参考基准,但读者会非常自然地以柱形底部为参考基准;❷则由于柱形底部使用了一条明显的黑色线条,明确而不含暗示地告诉读者以此为参考基准;❸使用红色线条将图表中的3个簇状柱形分组拦腰斩断,使读者更加关注超出该线条的那部分柱形。同样的图表,视觉参考的不同,给读者的心理暗示完全不同,虽然在诉求表达上❶和❷没有差别,但导致读者阅读时必须进行思考,一般建议使用❷,❶会给人较明显的美术气息。

不同视觉参考基准的柱形图

图12.1-2 不同视觉参考基准的柱形图

从窗户所见的世界

图表就如同一扇窗户,更多时候,我们所见只是通过这个窗口看到的数据局部,甚至有时,图表会使我们迷失在这些局部而不能自拔。我相信绝大部分的图表都已经涵盖了作图者手头的数据,除非是为了作弊,才有意截取部分数据,断章取义来完成图表。但不论图表中的数据是否完整,它都具有强烈的视觉思维的趋势引导,并会掩盖部分数据的真相。

视觉引导与暗示

如图12.1-3所示的案例,左侧和右侧的图表所使用数据完全没有差异,但读者所见的视觉感知却完全不同。柱形图相对而言强调数值表达,左侧的图表即是如此;然而右侧的图表仅仅是将系列的重叠修改为60,在视觉中便有了引导读者关注差异表达的倾向。

不同的图表设置带来的视觉引导

图12.1-3 不同的图表设置带来的视觉引导

这种引导,会给读者强烈的心理暗示。这些暗示有些来自于有意为之,有些则是无心之举。这类引导可能会对诉求表达起到积极作用,也有可能会很糟糕,基于此,需要作图者有非常清晰的理解。

真相远不止这些

如图12.1-3所示的案例,如果单纯将每月数据转换为全年占比的百分比数据,则图表将完全变成两个不同的模样;不同的横向比较和纵向比较也意味着会出现不同的数据结论。如果图表对读者的引导非客观,并且数据和诉求的把握没有恰到好处,这将会湮灭数据真相。不要期望使用一个图表,可将数据的多个维度都能阐释清楚,尤其当图表是用作发现和改善问题的状态下,使用多个图表,从不同角度去揭示数据显得至关重要。

图12.1-4 图12.1-3案例的另类二合一

图12.1-4是图12.1-3案例数值和差异对比的整合。在视觉中,差异对比部分是数值比较的进一步说明,清晰而直观。这个案例的实现采用两图合一方式,当然也可使用两个图表来实现。笔者并不建议读者花时间去学习这个案例,虽然图表并不复杂,但制作出如下所示的效果却并不容易,尤其是主次数值刻度的设置。在Excel 2010中,使用迷你图来实现则更加简单和方便。

纠结不如放弃[走为上]

受到数据图视化相较表格更易被人理解的惯性思维影响,当我们面对一堆数据时,第一反应都是如何将其变成图表。但我们常常纠结在如何将数据变为图形,因为并不是所有数据都可以使用图表表达清楚,同时每个图表都有其局限和缺点。从诉求表达角度审视图表,如果其负面影响远远大于诉求表达能力,就请放弃这个想法,退一步海阔天空。

请勿局限在图表

本书自第6章起,笔者一直在花大量笔墨讲解Excel图表功能,其实抛开图表本身,Excel的条件格式、迷你图、函数公式皆可用来完成数据的可视化。

1.Excel 2007和2010还提供了数据条类型的条件格式,通过使用该类条件格式,同样可以生成如图11.6-1所示的图形表达。

Excel 2007和2010的数据条条件格式案例

图11.6-1 Excel 2007和2010的数据条条件格式案例

要实现如图11.6-1所示的数据条条件格式,要将所有数据区域选中,整体来设定该区域的条件格式,这将保证每个单元格的格式设定遵循统一的最小值和最大值。当然这也可以在后期统一进行设置,但比较烦琐。使用该方法的好处是可以进行基于条件的筛选显示,减少视觉干扰,相较图表在表达诉求上要灵活且方便。

除了图11.6-1外,用Excel 2007和2010条件格式实现图示化数据表达的方法远不限于此,使用色阶和图标集同样具有图示化数据表达的功能,这需要读者针对诉求的不同来具体选择。

2.Excel 2010增加了一个在单元格中绘制迷你图的功能,其实这一功能是基于爱德华·塔夫特(Edward Tufte)发明的Sparkline:画在一个表格单元的图表,表达信息简单直白,一个图表仅为少量数据的图示化,只需一句话即可概括和归纳。

根据图表诉求的不同,我们可以选择Excel 2010的折线、柱形、盈亏三类迷你图,其实其所对应的就是本书2.1.1节中所述的趋势、数值、差异。图11.6-2右侧的图形表达即是迷你图的折线和柱形图。

Excel 2010的迷你图案例

图11.6-2 Excel 2010的迷你图案例

图11.6-2右侧迷你图分别由8个单元格构成,要统一这些单元格图形的显示,首先需要进行组合,其次要设置纵坐标轴的最小值和最大值,令其适用于所有迷你图。还可设置高点、低点、负点、首点、尾点这些数据点的强调,折线迷你图额外包括标记选项。

3.不论Excel任何版本都支持使用REPT函数来将字符按规定个数显示,利用这个特点我们可以生成如图11.6-3所示的图示。

利用Excel的REPT函数实现的条形图

图11.6-3 利用Excel的REPT函数实现的条形图

该图示使用的函数公式为:=REPT(“|”,个数),从效果而言,笔者倾向使用“|”字符,“■”字符的宽度太大,不适合在数值很大的表达中使用。总体上使用该方法要受到数值大小和最大值同最小值的影响。

表格也是诉求的表达

将图形搁置到一边,将数据整理得规整,表头和内容布局设置得更易阅读,其实也是表达诉求的一种方式。比如财务的资产负债报表和损益表,直到今天依旧是表格样式,并没有使用图表的方式进行表达。

如表11.6-4所示是个非常简单的交叉表案例,此类表格使用图表来表达时,必须将二维变换为一维格式才方便进一步进行图表制作,且诉求的表达并不一定是最佳方案,尤其是图11.6-1和图11.6-2的原始数据其实就是一个交叉表,无论采用何种方式来进行图示化的诉求表达,都显得繁杂。因为这种方式的数据即包含横向比较,又包含纵向比较。

交叉表案例

表11.6-4 交叉表案例

从信息检索角度考虑,表11.6-4所示的交叉表更适宜进行检索查找,反而使用图示化表达,会使问题复杂化。图表无论其表达形式如何,都包括图形和表格两个部分,我们不应忽略其中的任何部分。

直接使用数字来表达

回归本源,图形和表格都是基于数字的,当信息量不大时,刻意使用图表和表格更是画蛇添足。图11.6-5从“是否节省时间”、“是否提升工作效率”、“是否愿意向朋友推荐”、“文档访问和编辑的便利性”、“企业邮件系统超过500台电脑使用比例”、“福布斯世界500强使用企业整合沟通平台比例”这6个维度出发,说明了Microsoft Office 2010自发售至今的1年中所取得的骄人业绩。该案例完全依赖数字说明一切,简单而直接。

图11.6-5 Office 2010发售1年的用户调查节选[资料来源:微软官方www.office.com]

这个案例如果使用6个维度的雷达图来表达,将会使表达变得复杂和不易理解。当然这种方法仅限于使用在数据个数不超过10个、多维度进行衡量和评价的前提下。

将Excel放到一边

Excel的确非常强大,以至于很多人把Excel作为职场办公软件的不二选择。但是我们也必须要面对这样一个现实,Excel并非万能,即便有些图表在Excel中花费2小时,或者更长的时间被实现了,则需要问问自己这样做有意义吗?

比如:等高线地图,在Excel中基本没有直接实现的方法,即便是最为接近的曲面图,要生成理想的等高线地图也需要海量的交叉表数据。而现实中等高线地图往往可以利用X、Y、Z三列数据,基于不同的数学模型算法,且只有少量数据便可输出图表,这显然是Excel无法完成的,当然笔者也看到基于VBA直接自选图形实现的案例,问题是这对于普通大众而言并不现实。将Excel放到一边,使用SPSS或SigmaPlot这类软件,则非常简单。

放弃不代表放任

本章节的目的仅是为了向大家说明:不要将思维局限在Excel图表,而不能自拔。任何事物都有两面性,没有绝对完美的解决方案。数据的可视化表达也一样,我们要最大化表达诉求,尽可能减少其负面影响。在本书的第四部分亦会讲到相对复杂的图表应用,这些应用也是解决问题的方法之一。具体到实际应用当中,具体情况需要具体对待,切莫生搬硬套,拳头收回的目的是为了能更好地命中要害。

作图需要多技巧[连环]

使用Excel制作图表,往往是多个技巧共同作用的结果。尤其是相对复杂的图表,更是需要多个技巧的配合才可以完成。图11.5-1就是这样一个案例。

图11.5-1 作图需要多技巧

案例介绍

案例11.5-1图表是某公司7类产品,共25组方案,每组3个不同试验条件下的某特性参数分布状态,目的是筛选出整体数据离散度最小的方案。

这个图表在读者初看过后,可能会感到非常繁杂,虽然本书中一再强调勿使图表过于复杂,但有时我们又不得不将图表变得复杂,尤其是一些在工程技术中应用的图表。图11.5-1案例的图表在视觉信息表达量上虽然相对庞杂,但从数据诉求而言可以说是箱线图的变形,所以理解起来并没有太大难度。

案例分析

表11.5-2是图11.5-1案例的原始数据,当看到这个数据表后会有些茫然,而不知如何来处理。这个图表的制作首先需要处理好产品、方案、不同试验条件这三个维度的关系。由于图表诉求的焦点集中在最优方案的筛选,所以方案是被优先突出的,就如案例11.5-1图表所示,方案是图表的横轴。

表11.5-2 图11.5-1案例原始数据

案例11.5-1图表在视觉中被竖线分割为25组,由此构成了25个彼此独立的方案,每个方案其实可以看作是一个图表,这个部分的处理可以使用网格线来完成。

基础的图表类型完全采用XY散点,由于非面积类图表,使用XY散点的自由度要相对大很多,尤其是设置图表方案的最大、最小、平均三个划分线非常方便。

案例实现

根据11.5.2节的分析,虽仅使用XY散点图来实现图11.5-1案例,但实现的过程却需要借助辅助列构建、主次坐标系、误差线、模拟坐标轴等诸多技巧。以下是案例实现的具体步骤:

1.添加“X轴”辅助数据列,数值设置参见图11.5-3;然后以“X轴”数据区域为XY散点图的X数值,“A”、“B”、“C”、“D”、“E”、“F”、“G”所在数据区域为XY散点图的Y数值,制作XY散点图;最后设置坐标横轴刻度最小值为0,最大值为75,主要刻度单位为3,次要刻度单位为1,而后设置坐标横轴的主次网格线及相应的格式设置,如图11.5-3所示。

图11.5-3 图11.5-1案例实现第1步

2.依次添加“辅助X”、“MAX”、“MIN”、“AVERAGE”三列辅助数据列,其数值及函数公式设置参见图11.5-4箭头所示;然后以“辅助X”所在数据区域为XY散点图的X数值,“MAX”、“MIN”、“AVERAGE”所在数据区域为XY散点图的Y数值,向图表添加3个XY散点图系列;最后依次将图表中的“MAX”、“MIN”、“AVERAGE”3个图表系列置于图表次坐标系,如图11.5-4所示。

图11.5-4 图11.5-1案例实现第2步

3.依次选中图表的“MAX”、“MIN”、“AVERAGE”3个图表系列,设置误差线X为正负误差,误差值为0.5;然后设置相应的误差线格式,如图11.5-5所示。

图11.5-5 图11.5-1案例实现第3步

4.首先添加“辅助Y”辅助数据列,数值设置参见图11.5-6;然后以“辅助X”所在数据区域为XY散点图的X数值,“辅助Y”所在数据区域为XY散点图的Y数值,向图表添加一个XY散点图系列,并设置其数据标志为X值,无线形\无数据标记;最后勾选显示次纵轴,并将最小刻度设置为-2,并设置无线形、无刻度线、无刻度线标签,如图11.5-6所示。

图11.5-6 图11.5-1案例实现第4步

5.最终修饰整理效果如图11.5-7所示。

图11.5-7 图11.5-1案例最终实现

案例相关

本章节中的图表案例仅仅告诉读者全面系统学习Excel图表的重要性,即便是使用最为基础的图表,我们在进行美化处理等操作上依旧需要使用多个作图技巧。图表的初衷就是为了表达数据诉求,除此外在职场中,不具备其他功能,所以笔者并不建议日常使用该图表,变成为个人高深图表技能的一个炫耀道具。

作图需要耐心[苦肉]

Excel图表自定义设置相对比较自由,这为图表制作提供了很大便利,但并不是所有的图表制作过程都让人感到轻松和愉悦,实现某类图表的过程完全是一个考验耐心和毅力的过程。图11.4-1就是这样一个案例。

图11.4-1 作图需要耐心

案例介绍与分析

图11.4-1是一个典型的热力地图,在本书的图8.2-5案例中,曾使用单元格格式设置实现过,此处这个案例的实现则完全没有借助于任何非图表元素。

在Excel中根本找不到适合实现图11.4-1案例的基础图表类型,这看上去是一个面积填充图表,而Excel的面积类图表完全不支持使用一对数值坐标。具有一对数值坐标的图表仅有XY散点和气泡图,XY散点又没有填充类型图表,气泡图的气泡填充在这个案例实现中根本没有意义。

案例实现剖析

电脑中图片可以被分为两类来处理:点阵和矢量,本书11.3节已经让我们了解到:Excel以矢量方式处理图表对象及图表元素。既然如此,可不可以将图表变成点阵?回答是完全可以!这个想法看起来并没有问题,但要将一幅地图完全按点阵方式绘制到Excel图表中,想象一下十字绣,便知其工作量的巨大。

基于上述考虑,比较适合的基础图表类型仅有XY散点图。为此笔者花费了2天时间构建了该图表的数据表,这完全是一个“海量”的数据表,共有73列,最大12306行数据。好在Excel图表支持如此大量的数据,最终实现如图11.4-2所示。

图11.4-2 图11.4-1案例最终实现

如图11.4-2所示图表中,每个省、直辖市和港澳台各占一个图表系列,省市轮廓和省会城市,共计使用了36个XY散点图系列,没有任何其他类型的图表系列。图例部分使用照相机对单元格拍照后,粘贴到图表。由于每个系列所引用的X和Y值都完全不同,所以将这些图表系列加入到图表中又花去了一个上午的时间,这完全是机械和重复的劳作,枯燥且缺乏效率。

由于一个个手工更改图表系列的数据标记颜色,烦琐且没有效率,笔者使用了工作表上的一个按钮来驱动以下VBA代码进行颜色的更新:


程序代码:11.4-1 图11.4-1热力地图颜色更新


为了提高效率,图11.4-2从数据构建到数据加入到图表,整个过程都可以使用VBA代码。从某种意义上来讲,这个案例已经是一个相对完整的图表模板了,读者其实完全可以不去理会这些数据的来龙去脉,直接套用即可,因为大部分的图表设置并不需要每次都发生变动。图11.4-1案例的实现过程,更多的是告诉读者使用Excel制作图表,需要我们耐住性、沉下心。也许有读者会疑问:为何要如此繁杂地去实现这个图表?抛开使用图表元素,本书中图8.2-5案例的实现相对而言就要简单很多,当然也可以使用自选图形来完成,但和图表结合最好的还是使用原生图表元素。

另类实现

图11.4-3右侧图表是使用本书图8.2-5案例的原始数据源,采用三维曲面图的另类实现。这个图表数据引用了248列×202行数据,通过设置数据矩阵中代表每个省、直辖市和港澳台的不同数据点数值,在Excel曲面图三维空间中绘制了类似图11.4-3左侧的效果,然后调整三维视图的上下仰角[旋转:Y值]为90°来实现,即这个图表的俯视图。

图11.4-3 图11.4-1案例的另类实现

小技巧


去除曲面图的曲面网格:

曲面图默认设置中,曲面具有网格,如要设置为图11.4-3右侧图表样式。

  1. 在Excel 2003中需选中单个图例标示,分别单击鼠标右键设置格式,设置边框为无。
  2. 在Excel 2007/2010中需选中单个图例标示,分别单击鼠标右键设置镶边格式,设置边框颜色为“无线条”。

由于曲面图的特点,这个图表并无明显的系列,图表的色阶变化完全依赖数值(Z轴)的刻度最小值、最大值、主要刻度单位的设置调整来实现。和图11.4-2所示图表相同,这个案例的色阶动态变化也完全依赖于VBA来实现。这个图表从实用角度来看,首先比较消耗电脑系统资源,其次是图表的显示效果并不是很好,所以并不建议读者学习,本节目的只是让读者加深对曲面图的理解和学习。

图表是矢量[反间]

Excel图表受软件版本和不同图表类型的影响,一些看似非常简单的功能,却常常难以直接实现,往往需要通过折中的方法实现。图11.3-1即是一个具有代表性的案例。

图11.3-1 图表是矢量

案例介绍

雷达图的使用是一个有趣特例,其适合表达各数据点间非水平和垂直比较的多个维度数值。既可进行单个数据点数值的表达,亦适合成组的多维度数值间比较,图11.3-1案例图表即属后者。

一般而言,除一些使用在工程技术领域的案例(比如声学的指向性)外,笔者一般建议使用填充雷达图,非常有利于视觉记忆活动;不论何种雷达图,成组比较的组数不宜超过3个,如果组数太多将根本无法表达清楚内容。此处之所以“B品牌”图表系列使用半透明,是为了使下方“A品牌”图表系列数据能够看清。

案例分析

图11.3-1案例图表的实现其实并不复杂,使人纠结的关键在于如何使图表系列半透明。在Excel 2007和2010中这并非难事,直接在填充中设置透明度即可,但在Excel 2003中,填充雷达图并不支持设置透明度,亦无法借助其他图表元素来实现。唯一的方法是使用自选图形,这有一个挑战:如何使自选图形形状、大小和图表系列保持一致。

案例实现

根据11.3.2节的分析,图11.3-1案例最终采用自选图形实现,解决自选图形形状、大小和图表系列一致的关键是使用“取消组合”功能。以下是案例实现的具体步骤:

1.选中数据区域,生成填充雷达图,并对“A品牌”和“B品牌”两个系列进行相应的边框和内部填充设置,如图11.3-2所示。

图11.3-2 图11.3-1案例实现第1步

2.选中图表,将其复制,并打开PowerPoint,将图表粘贴到空白幻灯片中;然后将其取消组合,在弹出的警告对话框中直接单击“是”按钮。可能需要多次进行“取消组合”操作,如图11.3-3所示。

图11.3-3 图11.3-1案例实现第2步

3.选中“B品牌”,键盘Ctrl键+鼠标拖曳到空白位置,并设置自选图形填充透明度为50%,如图11.3-4所示。

图11.3-4 图11.3-1案例实现第3步

4.将设置好透明度的自选图形粘贴到Excel的原始图表中,并移动位置使其和图表中的“B品牌”系列完全重合,如图11.3-5所示。

图11.3-5 图11.3-1案例实现第4步

提示


需特别留意Excel 2003的显示比例问题:

不同的显示比例下,所见自选图形和图表系列的比例大小关系完全不同。


5.设置图表“B品牌”系列的数值引用区域为一空白列。最终整理修饰润色后的效果如图11.3-6所示。

图11.3-6 图11.3-1案例最终实现

案例相关

本章节所述方法,仅是一个不得已而为之的办法。当然除使用该方法之外,亦可使用VBA描点来完成自选图形。当然在熟练此操作后,可直接使用“取消组合”将图表转化为矢量图形对象,这是一个另类的选择性粘贴作业。

Excel 2007和2010即便不特意去设置图表元素格式的透明、阴影、发光等渲染,其图形对像使用“取消组合”功能打散后的自选图形锐度并不是很理想,尤其是使用了图表元素格式的渲染,打散后的效果将惨不忍睹;在Excel 2007中复制图表,并选择性粘贴为图片(增强性图元文件)后,对该图元文件的解释有些糟糕,尤其是光圈效果会有相当大的失真,打印预览更是需要非常长的时间进行加载,相比较而言Excel 2010则有非常大的改观。