在《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会自动更新。
