Excel公式技巧107：将表数据转换成列数据（续）

在《Excel公式技巧106：将表数据转换成列数据》中，详细解析了一位网友问我的问题的解答过程。然而，事情并没有完。上次提供的示例数据太完美了，所以实现起来相对简单。在上次的解答之后，该名网友又提出了一个比较棘手的问题。

如下图1和图2所示，需要将工作表Sheet1中的数据转换成工作表Sheet2中的数据。

图1

图2

由于在单元格区域B2:E6中每行的数据不一，这给编写公式带来了难度。我的思路是，对于工作表Sheet1中列A的数据，根据同一行在单元格区域B2:E6中数据的数量，计算出共有多少个数据要重复，如下图3所示，这是一个二维数组。

图3

然后，利用降维技术（该技术详见《Excel公式技巧24：Excel公式中的降维技术》），将这个二维数组变成一维数组，公式如下：

=INDEX(IF((B2:E6<>""),A2:A6,""),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1)/COLUMNS(B2:E6))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1),COLUMNS(B2:E6))))))

结果如下图4所示。

图4

去掉其中的空单元格，使其成为连续包含数据的单元格，使用公式：

=IFERROR(INDEX(INDEX(IF((B2:E6<>""),A2:A6,""),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1)/COLUMNS(B2:E6))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1),COLUMNS(B2:E6)))))),SMALL(IF(INDEX(IF((B2:E6<>""),A2:A6,""),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1)/COLUMNS(B2:E6))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1),COLUMNS(B2:E6))))))<>"",ROW(A1:A20)),ROW(A1:A20))),"")

结果如下图5所示。

图5

这就是将矩形块数据转换成单列数据的原理展示过程。同样，可以将单元格区域B2:E6转换为单列数据。

咋一看，可能被这么复杂的公式吓倒了。其实，公式里面有很多部分都是重复的，我们可以使用名称来将公式进行简化。

单击功能区“公式”选项卡中的“定义名称”来创建名称。

名称：Pos

引用位置：=Sheet1!A2:A6

名称：Data

引用位置：=Sheet1!B2:E6

名称：midArr

引用位置：

=INDEX(IF((Data<>""),Pos,""),N(IF(1,1+(INT((ROW(INDEX(A:A,1):

INDEX(A:A,ROWS(Data)*COLUMNS(Data)))-1)/COLUMNS(Data))))),

N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*

COLUMNS(Data)))-1),COLUMNS(Data))))))

名称：midArr2

引用位置：

=INDEX(Data,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,

ROWS(Data)*COLUMNS(Data)))-1)/COLUMNS(Data))))),

N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*

COLUMNS(Data)))-1),COLUMNS(Data))))))

所有定义的名称如下图6所示。

图6

这样，在上图2所示的工作表Sheet2中，选择单元格区域A2:A21，输入数组公式：

=IFERROR(INDEX(midArr,SMALL(IF(midArr<>"",ROW(A1:A20)),ROW(A1:A20))),"")

选择单元格区域B2:B21，输入数组公式：

=IFERROR(INDEX(midArr2,SMALL(IF(midArr2<>0,ROW(A1:A20)),ROW(A1:A20))),"")

结果如上图2所示。

此时，当你更新工作表Sheet1单元格区域B2:E6中的数据时，工作表Sheet2会自动更新。