不用 PQ!四大函数组合,纯公式实现 Excel 表格逆透视

大家好，我是孙斌，平时整理 Excel 数据常会碰到：左边是汇总样式的二维报表，需要拆解成右侧规范的一维明细表，这项转换操作就是表格逆透视

我们常用的数据透视，是拿原始明细汇总生成统计报表，例如，用透视表得到如下报表。

而逆透视是反向操作，把已经汇总完成的报表，还原成原始明细。

例如，有以下报表，有两个维度，城市和年份。

首先将数值转为一列，很简单，直接用tocol就行，忽略空白单元格。

接着考虑将城市转为一列，此时需要if搭配tocol函数，先用if函数判断，将城市转为一个矩形区域，空值显示为NA错误值，方便tocol函数处理。

再用tocol函数将矩形区域转为一列，忽略错误值。

再用同样的套路if+tocol函数组合，将年份转为一列，先用if函数得到一个矩形区域。

再用tocol函数将矩形区域转为一列，忽略错误值。

最终得到三列，就是所谓的明细表。

最后，可以将这三列的公式统一一下，变量只有第二个参数。

=TOCOL(IF(B2:D12<>"",A2:A12,NA()),2) =TOCOL(IF(B2:D12<>"",B1:D1,NA()),2) =TOCOL(IF(B2:D12<>"",B2:D12,NA()),2)

用let函数定义名称，再用hstack拼接。

还可以用vstack函数拼接上表头，公式如下。

这也是我们课程中的一个案例，案例中还讲解了带有双层表头的表格逆透视。