07

2023-11

从零搭建微信公众号数据分析体系：如何用纯Excel搭建一个视觉效果堪比BI看板的大屏

如题，不过在这里主要讲方法论，而不是手把手的带着大家做一个看板出来~

LEARN MORE

图片由海艺AI绘制

关于系列

《从零搭建微信公众号数据分析体系》是一个以本微信公众号的数据（毕竟其他更真实的业务数据我不敢拿出来写帖子）为例子的技术帖子系列。是一个“边写边做”的“有趣”的系列。基本按照企业做需求的模式，从项目立项、需求调研、数据建模落库、可视化看板预搭建、模型调优，一路到看板美化。

对之前内容感兴趣的朋友们可以通过点击文章最上面的合集标签查看前面的内容。

也可以直接拉到文章的最后，通过上一篇/下一篇的方式来查看更多的内容。

对这个话题的后续内容有兴趣的朋友们可以关注一下，这个系列预计每周二更新。

此外，对于内容有什么建议或者想来唠嗑群里唠嗑吹水的朋友们，可以关注，点击菜单中的“来唠嗑”加我微信，备注一下想来唠嗑，我拉你进群。至于群二维码，就不放了，之前放的二维码进来太多发广告的人了，太破坏体验了。至于留言嘛，公众号没有留言的功能，单纯想说两句的朋友们可以发快捷私信，看到的话基本都会回复的~

​

Excel也能做看板？

一般来说，我们提到看板，直觉上都会想到下面这种看起来非常帅气的大盘：

图片来自网络，如侵删

图片来自网络，如侵删

图片来自网络，如侵删

不少做数据的同学，看到这种图的第一反应就是，这玩意不就是BI么？如果是开发的同学，看到这种东西，可能第一反应是，这不是JS做可视化么？

咱们都是专业做数据的朋友，把看板大屏理解为BI可就肤浅了。应该这么说：BI工具可以做大屏看板，但是能做大屏看板的东西不止BI工具。不过我们今天不是来探讨BI和可视化的区别与联系的，这里我们只聊能够实现大屏可视化的方法。后续如果有机会，倒是可以作为一个独立的选题来讲一讲。

除了BI工具之外，可以做大屏数据可视化的工具非常多，比如后端开发的同学最熟悉的echarts，Pthon/r中如果写可以写几万字的各种可视化包等等。不过做可视化大屏看板的门槛其实是非常低的，纯Excel也可能做出视觉效果惊艳的看板。

对此不能理解的同学，请仔细观察一下上面找来的网图，其实几乎所有的图表要素都是能在Excel中以各种各样的方式实现的。只不过有的图表可以通过默认的图表展示，有的图表则需要通过特定的操作（比如图片叠加、插件导入等）才能呈现了。

会想到这个主题，是因为最近在看一些培训机构的课程。发现不少培训Excel课程的机构，会以Excel看板大屏模板作为一个噱头，就动了拆解一下这个逻辑的心思。

Excel要讲基本法

如果要用Excel来搭建可视化大屏，那么整个Excel表的设计就得有点讲究了。

从方法论上来说，做一个好的excel表至少需要遵守下面几个原则：

①原始数据和处理逻辑可以分离；

②可视化呈现和处理逻辑可以分离；

③数据异常、公式异常可以被检测；

④能用公式写的地方就用公式写。

乍一看是不是觉得很懵？这不是我的原创，是很久以前在一个公益咨询项目的培训中听老师讲的逻辑。我刚看到这个逻辑的时候也是觉得很懵，就做一个Excel表而已，怎么还能搞出这么多讲究来?实际上后来做项目做需求做多了之后，才能切身体会到老师说得逻辑有多么实用——一看就是老江湖了，没返工过800次需求总结不出来这么实用的办法。

其实空说逻辑没什么意思，我们先来一个实操的案例看看如果要做一个基于Excel的看板都要怎么操作。

回到我们上周做好的Excel表中（有兴趣的朋友可以从这里快速跳转到上一篇进行查看哈）。其实已经通过Power Query做好了一些数据处理的处理。之后将数据加载到excel表中，并没有做更近一步的处理。

虽然说PQ中处理了一部分，但这里的处理其实都是一些非常基础的处理，只对一些数据的格式做了简单的处理，比如把小数转成百分数这样的操作。距离分析数据似乎还差得很远呢。所以对于一个分析而言，把这个做过简单整合处理的表，视为原始数据也是没有任何问题的。

依照方法论，在这个Excel表中稍作调整，修改已经汇总好的数据是sheet名称为原始数据汇总，再新建三个独立的sheet页面出来。

接下来就是做一个大屏的图表了，举个例子吧，我们想看一个按月的阅读总人数。

相信我公众号的粉丝基本都知道插入数据透视表这个做法吧？这里就不赘述应该如何插入数据透视表了，我们直接在数据辅助计算页给出结果。

然后选中数据透视表的数据，插入图表。

​接下来就是重点了，把这个图表用剪切的方式挪到最终呈现页那个sheet上去。然后修改一下图表的名字：

​

为了让视觉效果看起来更有大屏看板的感觉，在这里还可以选择关闭网格线的设计：

​

一个图标的感觉不是很明显，所以我如法炮制另一个图表出来，为了视觉效果更突出一些，设置一个单元格背景色出来：

大体的Excel设计大屏看板的操作就是这么简单无脑，唯一的问题是设计各种视觉对象稍微有点麻烦——需要一个个图表慢慢调整颜色，统一颜色什么的。最最关键的是一个好看的配色设计，这东西非常耗人耐心，尤其不适合我这种本来就没有耐心的人。

基于Excel搭建看板的操作就这么简单，没什么可以讲的细节。所以我们把目光拉回到上面讲的原则以及这里为什么要拆分sheet页上来。

首先说原始数据要和处理逻辑分离。

对于任何数据，我们数据分析师最怕的一件事情就是原始数据发生了变化！对于原始数据本身的改动，不管是用什么数据处理逻辑，都有点讨厌。将原始数据和处理逻辑做分离就是为了防止这种原始数据变化数据处理逻辑要大幅度修改的情况。

再说可视化和处理逻辑要分离。

这里的Excel大屏只是一个工作中比较罕见的常见。真正在工作中常见的场合是，要把图表放在PPT里。其实用这种剪切的方法，让图表跨sheet跨文件都是可以保证和原始数据的逻辑关系保持一致。如果发生了袁术数据的修改，只需要一次刷新就可以解决问题，同样的减少了反复查数据修改多处的问题。

然后说数据异常可以被检测的问题。

这都是Excel常见的坑，尤其是数据透视表，这玩意经常会出现一些玄学问题。之所以称之为玄学，是因为这种问题是时有时不有的问题，所以很难给大家截个图演示一下。简单来说，就是数据刷新了，但是透视表的结果没有更新，并且点击刷新有可能是不生效的。这种时候，我们通常有一个简单的小办法来识别这种异常：新建一个sheet页，写一些统计公式，来核对数据是否正常。

最后说为什么能写公式的地方尽量写公式。

这个问题其实和上面说的数据检查是一个道理。如果数据透视表出来的结果是不稳定的，我们就可以想办法规避数据透视表的使用——换成写公式的方法手算这些值，这个是可以保证不变的。我不推荐大家一开始就选择全部公式写以规避麻烦，毕竟数据透视表能实现的这种功能要是写成公式还是很麻烦的。我推荐的做法是先保持透视表+查验逻辑运行，如果有问题再把透视表改成手写公式的办法。

下期预告

关于可能的技术分支（可以参考这一篇文章的技术总结），Excel相关的技术分支就到这里了。

下周，我们来看另一种可能性：用AI辅助的办法实现从拉取数据到落库一气呵成。

除此之外，还有一种技术实现的方案，那就是用低代码数据集成平台，比如腾讯轻联这种实现数据的全路径流程。不出意外的话，会放到

二号姬

半路出家自学成才的文科数据人，看过了大厂的风景也做过了小厂的CDO~目前是闲职，主要是想沉淀自己，自媒体写作是最好的自我总结和自我复盘