快来试试excel这个“万能”公式

我们平时使用Excel，除了记录数据、分析和统计数据之外，有时还需要根据领导的要求，将一种数据结构转换成另外一种数据显示方式。

我们介绍了如何使用超级透视表（PowerPivot）和PowerQuery来进行下面的数据结构转换。

但是，小伙伴们，如果你用的是WPS，或者微软2013版本以下的的Office，就没办法使用上面那些先进的方法了。

怎么办？

别急~~~

今天，我就给大家介绍下，在所有的版本中都能使用的方法。

数据转换A

如下图，现在我们需要将左边的数据区域1转换成右边的数据区域2。

我们可以利用高级筛选+万金油公式完成转换。

操作步骤如下：

❶提取大类中的不重复项。

点击【数据】选项卡==》【高级】==》列表区域选中A列==》勾选【选择不重复的记录】，最后点击【确定】。

结果如下图所示：

最后，复制【A】列大类中的不重复项内容，并转置即可。

以下是参考动图：

❷利用万金油公式。

万金油公式是一个非常好用的公式套路，学好它，万事不在话下！

在【D3】单元格输入如下公式：=INDEX($B$1:$B$12,SMALL(IF($A$1:$A$12=D$2,ROW($1:$12)),ROW(A1)))

这个公式可以拆分成三个部分：

第一：IF条件函数

❶IF($A$1:$A$12=D$2,ROW($1:$12))

如果【A1：A12】中的内容等于【D2】中的内容，就返回它的行号，否则返回FALSE。

结果如下：

{FALSE;2;3;4;5;6;7;FALSE;FALSE;FALSE;FALSE;FALSE}

第二：SMALL函数

❷SMALL(❶,ROW(A1))

这步是将第一步中得到的结果值，取出其中第一个最小值，这里的ROW(A1)返回结果为1，即：第一个最小值为：2。

PS:SMALL函数会忽略FALSE逻辑值。只取数字的最小值。

第三：INDEX函数

❸INDEX($B$1:$B$12,❷)

这步是将第二步中的结果值：2，作为INDEX函数的第二参数，意思是：返回第一参数中，第二个值。即：【B2】单元格中的值（图表）。

至此这个公式就分解完成了，不是很难吧！

注意：

如果公式向下复制拖动，变化的只有SMALL函数的第二参数：ROW函数。

由ROW(A1)变成了ROW(A2)，即由数字：1变成数字：2。返回SMALL函数的第二个最小值：3

INDEX函数返回【B3】单元格中的值（数据透视表）。

如果公式向右复制拖动。只有IF条件区域中第一参数有变动。

由原来的【D2】变成了【E2】。其他内容完全一样。

另外：请小伙伴们注意公式中的绝对引用和相对引用的使用！

上图中公式中出现的错误值，可以使用IFERROR或者IF+ISERROR函数屏蔽。

公式如下：=IFERROR(INDEX($B$1:$B$12,SMALL(IF($A$1:$A$12=D$2,ROW($1:$12)),ROW(A1))),)

公式套路就是在原公式外面套一个IFERROR函数。即：IFERROR（原公式，""）。第二参数，输入两个英文双引号，表示显示为：空文本。

这样我们就完成了第一种数据结构形式的转换了。

另外提醒下大家：

这个公式是一个数组公式，在低版本中需要按三键【Ctrl+Shift+Enter】结束公式输入哦~

数据转换B

由右边的数据结构2转换成左边的数据结构1。

这种数据结构转换，是由列方向转成行方向。

我们可以使用【数据透视表】的数据转换功能来实现。

步骤如下：

❶选中【B2】单元格，然后依次按【Alt】+【D】+【P】,调出【数据透视表和数据透视图向导】。

选中【多重合并计算数据区域】。点击【下一步】

❷保持默认勾选的【创建单页字段】。点击【下一步】。

❸在【第2b步，共3步】中，选定区域【A2:D8】,点击【添加】，最后点【完成】。

❹此时将会新建一张工作表，用于显示数据透视表的结果。

用鼠标双击【E6】单元格，也就是总计行的最后一个单元格。

此时又会新建一张工作表，结果如下：

这时就会将原来的列方向上的数据结构显示为行方向了！

最后筛选去掉【值】列中的空白，结果就出来了。

写在最后

今天，我们学习了两种不同的数据结构之间的转换方法：

❶高级筛选+万金油公式法

❷数据透视表法

在实际工作中，可能还存在其他形式的数据结构转换，运用上面的方法再结合自己平时不断的学习积累，相信你一定能轻松解决掉这类问题。