Excel函数也可以做透视 - 有什么用？怎么做？

在Excel中进行数据透视非常简单，

在进行数据分析时，数据透视表工具给我们很大帮助。但是，有些场景下，我们需要这样的数据作为中间结果：

但是并不想中间插入一个透视表来作为辅助表。因为这样的方案在后续工作中会给我们带来持续的折磨。

幸运的是，在新的Excel中，有大量的新函数和动态数组等特性，可以帮我们用函数实现数据透视的功能。尤其是，在Excel函数式编程的方案中，数据透视是一个基本的数据处理技巧。

我们先来看最简单的情形。

1. 直接转换

---

在这种情形中，源数据和结果数据之间不需要任何计算和处理，直接转换即可：

我们需要将左边的第一列放在结果表的标题列（行标签）：即第1步，第二列放在结果表的标题行（列标签）：即第2步，第三列放在结果表的值区域，对应的数值不需要做计算，直接放到结果表的对应位置上即可（比如红色圆圈中的数值）：即第3步。

很简单，第1步和第2步可以通过UNIQUE函数得到，而第3步就是要将一个列数组转换为一个多行多列的数组，最容易想到的方式是通过一个MAKEARRAY函数，其中关键之处，就是要将多行多列数组的索引：r_m，c_m转换为列数组的行索引r：

根据上面的逻辑就可以创建自定义函数：

2. 明细数据的汇总

---

上面的方案中，数值从源数据中直接放到“透视表”的相应位置，并没有进行计算。

但是，加入源数据中包含的是明细数据，比如，Coal和Agro对应数据有多行，在结果“透视表”上需要将它们汇总起来，上面的自定义函数就不适用了，需要修改。

很显然，我们需要用到条件求和，根据对应的行标签和列标签，在源数据中进行求和，但是一定注意不要用到SUMIFS函数（具体原因见这篇文章）：

这个函数的逻辑跟前面的函数基本一致，只是在计算结果表的数组区域时，循环中使用的是SUM函数。

3. 字段排序

---

真正的透视表字段是可以排序的。我们使用透视表做中间结果时，也可能需要进行排序，这时，我们需要对其做排序处理。

方法也很简单，只要在生成行标签和列标签时，使用SORT函数即可：

这里的自定义函数多了两个参数，用来指示是否需要对行标签或列标签进行排序。

4. 自由选择行标签和列标签

---

上面的函数只能固定的将第一列作为行标签，第二列作为列标签，第三列作为值字段，但是实际场景中需要我们灵活选择这几个字段，这时，数据的处理逻辑不需要任何变化，只要在处理时将它们选择为合适的源数据列即可，这可以通过DROP，TAKE，CHOOSECOLS等函数实现：

这个自定义函数基本实现了完全自由的“透视”表

---

详细解释请看视频