这个不走寻常路的技巧，让Excel逆透视变得非常简单

今天继续介绍逆透视技巧。

在很多场景中，我们需要这样的过程：

这个从左表转换到右表的过程，就是逆透视。

传统上，在Excel中进行逆透视的操作比较复杂，稍微长一些时间没有用到就会忘记。想了解的可以看这里。

这种传统方法有较大限制，更重要的是这种操作是纯手工操作，需要人工干预，如果希望做成自动化方案的话，就不能使用这种方案。

也可以使用PQ的逆透视，这是自动化方案。不过，如果我们要将逆透视结果作为中间结果时，这样就多了一个中间表，使后续工作变得更加复杂。

我们这里介绍使用函数进行逆透视的方案。

难点

---

逆透视的难点在于如何从列数组的行索引r对应到多行多列数组的行索引和列索引：

这里有一个公式：

r_m = ROUNDUP(r/cols,0)c_m = ROUNDUP(MOD(r,cols+0.1),0)

这里的cols表示源数据中列标签的个数。

这样，我们就可以创建下面的自定义函数了：

不走寻常路的方案

---

上面的自定义函数是普通的思路，熟悉了之后直接就可以想到。下面介绍的方案就是另一种思路，完全不考虑行标签列标签值字段等等，只考虑两个表格如何从形式上进行互相转换。

第一步，合并首行和首列。

在这一步当中，我们将原表的首行和首列合成下表：

这可以使用一个很简单的公式完成：

=A3:A6 & "," & B2:E2

第二步，继续合并数值区域。

这一步，我们将上面的结果和数组区域合并，得到下面最后一个表格：

这个公式也简单：

=(A3:A6 & "," & B2:E2) & "," & B3:E6

第三步，合并成一个字符串

将上一个结果进一步合并成为一个字符串：

这里可以使用TEXTJOIN函数，以";"为分隔符。

注意合并的结果中有两个分隔符：逗号和分号。

第四步，将字符串拆分为结果。

通过TEXTSPLIT函数，将字符串拆分为最后的结果。

=TEXTSPLIT(TEXTJOIN(";",1,A3:A6&","&B2:E2 & "," &B3:E6),",",";")

根据这个逻辑，创建自定义函数如下：

---

详细解释请看视频