【图表攻略】大咖作品研习：高级数据分析看板（5000字长文）

创作者俱乐部成员

困难如山，我有金山。

欢迎收看本期【图表攻略】新专题的第一篇文章，我是学弟。

【作者简介】
社区ID：清华学弟任泽岩
WPS办公软件商业讲师
- 金山办公最有价值专家（KVP）、金山办公认证讲师（KCT）、金山WPS社区创作者俱乐部成员、金山文档行业金鹰社社员、微博认证职场博主、中国管理科学学会-高级企业培训师、表格菁培班-函高一期毕业生；
- 中国南方航空公司十佳教员、金牌微课制作师，全国培联/培协推优大赛金奖获得者，《培训》杂志“师道匠心”全国金牌内训师称号获得者，两度获得Toastmasters国际演讲会International Speech Contest赛事冠军。

学弟近日在刷朋友圈时，刷到了前辈@会飞的鱼（吕洪飞）老师的一条消息：

没错，这并不是Python的作品，而是实打实的用WPS表格制作的数据分析看板哦！于是学弟马上联系了吕老师，将这一套作品要过来，进行研读学习。

在本文中，学弟挑选一份数据分析看板，进行部分项目的复刻，并把学习过程记录，分享给大家，以帮助更多伙伴更好地向前辈学习！

接下来学弟按照“从0到1”绘制的顺序进行拆解。

策划篇

对于制作数据分析看板这样一个大工程来说，“策划篇”是学弟觉得最最重要的部分。这里包括了以下几个方面：布局策划、源数据策划、图表类型策划、排版策划、视觉策划等等。

① 布局策划即为读者们看到的，至少分成【首页】、【数据源】、【公式】、【版权说明】四个工作表，并且需要明确几个工作表需要重点展现什么内容。

这里面还是基于规范制表的“三表结构”（学弟其他文章中已多次提到这个概念，再此不再赘述），也是便于制作者和使用者都方便的原则，明确区分：哪部分是读者能编辑的（源数据录入）、哪部分是读者阅览的（只读）、哪部分是后台计算的（尽量不要随意修改）、以及辅助说明的（帮助类文档，不需要时可以删去的）等等。

正是事先规划了这些功能，才使得后面的制作过程异常的顺利。

当然，我们自制数据分析看板时，不一定正好四个工作表，但一定要考虑到以上几类功能的体现，都可以在相应的位置找到符合他们定位的布局。

② 源数据策划主要根据企业组织的要求，看“需要展示多少数据”，一般是一些关键的指标。在本例中，企业组织选择了“销量分析、销售金额、渠道分析、产品分析、性别分析”等指标，并以“N个汇总表”的形式放在了【数据源】工作表中。

*学弟备注：因为【数据源】工作表是给用户录入数据用的，因此这个表最好简单做一下排版设计，能让不同水平基础的用户准确、快速、方便地找到录入方法，不在这个上面浪费时间。

③图表类型策划主要是基于数据源的种类，匹配合适的图表类型。这一部分非常考验制作者对图表类型的掌握程度和数据视觉化的水平。

在本例中，订单销售、销量分析这类简单的图表，可以以柱形图、折线图等方式展现；渠道分析可以以饼图方式展现；产品分析使用了条形图进行展示；性别分析用饼图也行，但考虑只有两种类别，因此选择条形图也可以。

再往前进一步，为了保证整体页面的观感更为“灵动”，折线图可以选择平滑的方式；产品分析和性别分析选择了堆积条形图；渠道分析选择了圆环饼图；季度分析选择了柱形图的变体形式。

而最复杂、拥有4个渠道的2维月度渠道分析，使用了平滑折线图、结合窗体和条件格式等复杂形式进行了制作。

*学弟备注：以上选择方式不唯一，可以根据行业样态、结合制作者个人审美做一定调整。但大体思路要正确。

④排版策划即制作者需要考虑每一个工作表（当然重点是【首页】工作表）的内容排版情况。

一般的数据分析看板都是长方形形式的矩形，因此需要考虑大小标题、每个图表的“占地面积”，同时需要考虑排布的逻辑，类似的基本按照上总下分的形式；在满足整体阅读逻辑的基础上，对于一些并列属性的图表，尽量做到同一尺寸的同一类型图表，放在一起。

整体设计排版的难度不亚于做一张完美的PPT。

我们再来欣赏一下本例的精彩排版：

⑤视觉策划即需要考虑整体配色的样态，类似于WPS演示里的“主题”。在黑白灰百搭色的基础上，可以结合行业样态和logo色，采取1-2种主题色（一定不能太多），进行视觉策划。而背景色一般采用黑色、蓝色、紫色等深色。

在本例中，采用黑色作为底色，选择浅绿和浅蓝为主体颜色进行了搭配。部分图表由于元素较多，也使用了其他颜色作为辅助，但都没有逃离浅绿和浅蓝的大类色系。

公式及图表绘制篇

学弟本来想把“公式”和“图表绘制”分成两个部分，后来发现这两个内容环环相扣，基本上是“想选用哪种类型的图表，就决定了使用怎样的公式”，因此这两部分内容又密不可分，所幸一起说吧。

太简单的公式图表学弟就略过了，这里重点讲三个地方：

①渠道分析：

在【公式】工作表中，我们可以很容易地用SUM函数计算出各个渠道的总和，然后再选择

圆环图进行绘制。但这样的结果，可能会出现因为几个渠道的相差较大，在圆环上出现“一会大一会小”的情况，因此在几个渠道可以调换位置的情况下，增加两列辅助列：

在P7单元格输入：=LARGE($N$7:$N$10,ROW(A1))

在O7单元格输入：=XLOOKUP(P7,$N$7:$N$10,$M$7:$M$10)

分别下拉拖拽，再用两列辅助列（绿色区域）进行圆环饼图的绘制，即可保证由大到小降序的圆环饼图次序。

②月度产品和性别分析：

这两部分都是通过堆积条形图来制作的，为保证样式美观，最后的“总数”需要固定一个常数，“堆积”的部分，则要通过常数与源数据的差来实现，并用这个数来作图。

在月度产品分析中，我们还要保证“总数”的常数要大于最大值，这样最大值后面也会留出一段阴影，从视觉上十分统一；同时这块阴影的长度还不能特长，因此设定一个最大值的1.2倍，作为常数：

因此这个“堆积”出来的值（辅助列J列），即输入公式：=MAX($I$4:$I$15)*1.2-I4 （均为字母I而非数字1），再用源数据值I列和堆积列J列制作堆积条形图：

并将源数据值系列，填充为（浅绿-浅蓝）主题色的渐变填充；将堆积值系列，填充为深灰色。

在性别分析中，由于只有男和女两个互斥项目，因此选择以“百分比”的相对值形式进行，这样也抹平了因男女绝对值人数增大导致条条过长的情况。这里面还有个很好玩的点，中间那个白色的竖条，如果想做这个，可以多“堆积”一列特别特别窄的条，本例选择0.01 (1%)作为白色条的“堆积宽度”，即：

数据源男性比例=男/(男+女)、女性比例=女/(男+女)，没有问题；

留出一列辅助列O列，输入0.01，作为分割白条；

N列即为堆积列，用1-O列-P列即可。

③月度渠道分析：

学弟觉得这个部分是整个看板里难度最高的部分，也绝对是亮点，会让偏“外行”的用户觉得这个看板十分酷炫、互动性很强的动态图表。

如果用常规方法做，就是横坐标1-12月、纵坐标4条平滑折线放在图表上。

4条平滑曲线不是不可以，只是会显得画面很混乱，不太好找数据。如果只看一种渠道，或者对比两-三种渠道的灵活程度不够。

比较容易想到的方法就是【插入】选项卡→【窗体】功能。

学弟为了复刻这个效果，翻遍了窗体里的功能，似乎只有“复选框”功能支持反复“点选-取消”操作。但是复选框和吕老师制作的按钮样子还不大一样：

于是学弟彻底“破坏”了展板，终于被我发现了端倪：

原来复选框藏在了一块背景色矩形的后面（为方便辨认，把矩形边框涂成了红色，并且把复选框后面的单元格填充颜色涂成了白色）。

（1）这样一来这4个复选框需要完成第一个任务：用4个复选框来分别控制对应4个单元格按钮。采用右键【复选框】→【设置对象格式】→【控制】选项卡，链接到【公式】工作表中的一个单元格，用复选框来控制这个单元格值是TRUE还是FALSE。

（2）这4个【公式】工作表中的单元格需要完成第二个任务：控制是否显示对应的数据折线图。充分利用“单元格#N/A报错情况下，图表中不显示”的特性，输入=IF(C$2,数据源!J4,NA())，向右、向下拖拽，即可通过上方单元格的TRUE和FALSE的情况，TRUE保留源数据值，FALSE则强制#N/A，从而隐藏其折线图。

（3）视觉方面，还要有第三个任务要完成，即4个单元格配合复选框进行颜色的改变，制造“好像选中了、好像取消选中了”的视觉错觉，使用条件格式功能进行实现：

设置“公式”型条件格式，公式栏中输入=公式!$C$2，（与前一步（2）中所引用的4个单元格位置相同），再设置一下选中以后的字体颜色、背景颜色（为了逼真，需要设置选中、未选中的字体、填充颜色互为相反，如上图所示）。

（4）还有一个细节，需要把4个复选框和4个单元格叠放在一起。单独点“条件格式的单元格”是没有用的，需要点到复选框上；但同时还得让用户觉得“好像我是点了单元格以后才发生的变化”的错觉。这就利用了复选框本身的“范围”特性，我们建立一个复选框以后，只要点击虚线方框内的任意位置，都可以选中/取消选中复选框，而不管复选框本身字的大小：