复杂表源的清洗方法

上篇推文《从【中国式复杂报表】谈设计逻辑》中我们提到，中国式复杂报表作为高度复杂化的产物，不适合进一步用作数据源。但实际工作中，难免遇到以类似复杂表格作为数据源的情况。比如从国家统计局下载数据的表单，就是一个初级版的复杂报表。我们可以看到，表头分了两个层级，且子层级含有合计数。列方向上也有合计（全国）。本文将来一步一步介绍，如何清洗复杂报表数据源。

01 基础：从一维表、二维表谈起

下面两张表，哪张更适合作为数据源？同样的内容，第一张表（除去表头，下同）只有6行，而第二张表则多达15行。稍有Excel分析经验的读者都知道，前者是数据透视表（Pivot Table）形式的。这类表的数据（B2:E7），是横纵两个维度交叉的结果，因此是一张二维表。横向是产品类别，纵向是公司。正是因为有这种交叉叠加属性，表的信息密集度较高，可以节省界面的展示空间。第二张表，只有单一的维度，是为一维表。每一行即代表了一家公司一种产品的金额，行与行之间相互独立没有纵向的关系。

​ 回顾上篇推文我们详细提到，数据复合交织的表格，复用性差，不适合作为数据源。因此，只有横向维度，纵向独立的一维表是理想的数据源形态。二维表则方便数据可视化展示，除了节省空间外，呈现数据间的关系是数据分析追求的目标之一。

​

​ 将一维表转化为二维表，叫做透视（Pivot）。在Excel、Power Query、SQL和Python里，都有同样叫法的功能。个人认为，中文“透视”过于意译，并没能很形象地传达出英文Pivot的本意：围绕枢轴旋转。我们可以把一维表是做两条重合的直线，转化为二维表就是两端节点固定相连，中间节点往外扩，形成一个正方形。这就是Excel上透视表的生成过程。那么如果想把透视表转为方便存储和复用的数据源，就需要反过来将二维表转为一维表，即为“逆透视”（Unpivot）。

02 核心技能：逆透视

​ 所有复杂报表，本质上都是不同维度的叠加。因此掌握好二维转一维（逆透视，Unpivot）就是基础和关键。先来看看逆透视的原理图解。逆透视的基本思路是：首先锚定纵向的维度，然后把横向的维度，分别转置过去。

​ 在Power Query 中，关键是选中锚定的维度（列），本例为公司列。然后选择【转换】——【逆透视列】——【逆透视其他列】即可。

03进阶：2*2维表

​ 由于同一平面是个二维空间，所以我们常接触到的比二维表更复杂的多维表，实际是在横纵两个方向上，分别添加多个维度。比如横纵各有2个维度，就是一个22维表。本文开头年鉴的表就是一个21维的表，而下图案例是2*2维表。横向上有公司类型、公司名称2个维度，且有合计；纵向上有年、月2个维度，也有合计。

​ 解决方式的核心仍然是围绕透视（Pivot）和逆透视（Unpivot）。初始数据加载后我们会发现，第一行和第一列有很多空值。而直接逆透视会把空值开头的行或列给删除过滤掉。

​ 首先我们来解决纵向的维度。根据上文我们可知，逆透视要先锚定维度。当前纵向有2个维度。我们可以先把两个维度合成一个，在后续再拆开。因此纵向的解决步骤有两步：

1. 最左边的列向下填充，解决空值问题；

   

2. 合并最左边两列，成为一列单一的维度。

   

   经过这两步之后，我们成功将22维表转为21维表。那么横向上跟的维度怎么解决呢？Power Query只有向上向下填充，没有横向填充，怎么处理空值？我们不妨把表倒转过来——转置，那么横向的问题就转化为纵向问题，就可以重复上述步骤解决了。转置、填充并合并列后如下图所示。

​ 经过这几步，复杂的2*2维表已经转为普通的二维表了！我们就可以愉快地通过逆透视得到下图所示的一维表。

最后一步，分别把上图前两列分列即可大功告成！