excel突破结构限制的“数据透视表”

Excel函数 2023-01-08

出个题：根据A2:C16生成E2:G18的汇总结果。这里的汇总是求和。

excel突破结构限制的“数据透视表”

遇到这种情况，首选肯定是函数公式，虽然数据源表是个很规范的一维表，可以用数据透视表，可是想建构到上图那么奇葩，数据透视表无此异能。

求和公式是很容易写，但是换个呢？乘积？方差？不重复个数……

============================================================

其实，上面这个计算用的真的是数据透视表，整个操作从头到尾没有在键盘上敲过一个字母。

过程是这样几滴：

先【插入】一个【数据透视表】，这个透视表放哪儿无所谓，重要的是要添加到【数据模型】。

excel突破结构限制的“数据透视表”

然后就是添加字段：
行字段：表头1
列字段：表头2
值字段：表头3
值字段默认求和，可以根据实际需要修改

excel突破结构限制的“数据透视表”

数据透视表统计虽然方便快捷，但是成型的透视表无法随意变动，比如像下图那样，把F5里的数据移动到E9，透视表就甩脸子了。

excel突破结构限制的“数据透视表”

从前遇到这问题，我一般会把透视表粘贴成值，再各种折腾，但这样做有个坏处，因为数据透视表与数据源表之间的关联断开了，一旦数据源修改，整个操作要重做一遍。

但是加入了数据模型的数据所生成的透视表，有个奇特的功能，只要选取数据透视表中的任意单元格，再找到【数据透视表分析】选项卡下的【OLAP工具】，里面有个【转换为公式】，数据透视表就秒变公式了。

excel突破结构限制的“数据透视表”

转换好的公式用的全部是【多维数据集】函数：

excel突破结构限制的“数据透视表”

既然是转成公式了，接下来汇总结果的表格结构想要怎么折腾还不是由得我们为所欲为么？

只是这类公式的更新十分古怪，数据源发生变化以后，必须像在数据透视表里的操作一样【刷新】数据才行。

虽然那些多维数据集函数看得我一头雾水，但是第一眼看到转换为公式这个功能时，我还是欢欣鼓舞的，毕竟他帮助我们打破了数据透视表最大的限制：表格结构。